SQL Basics

SQL/SQL Basics

SQL Basics

The SQL language consists of above 40 main body statements, which are summarized in the below tables. Each statement requests a specific action from the database management system (DBMS), such as creating a new table, retrieving data, or inserting new data into the database. All SQL statements have the same basic form.

1)    Data Manipulation

·       SELECT: used to retrieve data from the database.

·       INSERT: used to add new rows of data to the database.

·       DELETE: used to remove rows of data from the database.

·       UPDATE: used to modify existing database data.

2)     Data Definition

·  CREATE TABLE: Used to add a new table to the database.

·   DROP TABLE: Used to remove a table from the database.

·    ALTER TABLE: Used to change the structure of an existing table.

·    CREATE VIEW: Used to add a new view to the database.

·   DROP VIEW: Used to remove a view from the database.

·   CREATE INDEX: Used to build an index for a column.

·   DROP INDEX: Used to remove the index for a column.

·   CREATE SCHEMA: Used to add a new schema to the database.

·    DROP SCHEMA: Used to remove a schema from the database.

·   CREATE DOMAIN: Used to add a new data value domain.

·   ALTER DOMAIN: Used to change a domain definition.

·   DROP DOMAIN: Used to remove a domain from the database.

3)   Access Control

·       GRANT: Used to grant user access privileges.

·       REVOKE: Used to remove user access privileges.

4)   Transaction Control

·  COMMIT: Used to end the current transaction.

·   ROLLBACK: Used to abort the current transaction.

·   SET TRANSACTION: Used to define data access characteristics of the current transaction Programmatic.

·   SQL DECLARE: Used to define a cursor for a query.

·   EXPLAIN: Used to describe the data access plan for a query.

·  OPEN: Used to open a cursor to retrieve query results.

·   FETCH: Used to retrieve a row of query results.

·   CLOSE: Used to close a cursor.

·   PREPARE: Used to prepare a SQL statement for dynamic execution.

·   EXECUTE: Used to execute a SQL statement dynamically.

·   DESCRIBE: Used to describe a prepared query.

Names

The objects in a SQL-based database are identified by assigning them unique names. Names are used in SQL statements to identify the database object on which the statement should act. The most fundamental named objects in a relational database are table names (which identify tables), and column names (which identify columns).

The original ISO standard specified that SQL names must contain 1 to 18 characters, must begin with a letter, and may not contain any spaces or special punctuation characters.

Table Names

When you specify a table name in a SQL statement, SQL assumes that you are referring to one of your tables (that is, a table that you created). Usually, you will want to choose table names that are short but descriptive. The table names in the sample database (Teacher, Student) are a good example. In an individual or departmental database, the choice of table names is usually up to the database developer or designer.

Column Names

When you specify a column name in a SQL statement, SQL can normally determine from the context which column you intend. However, if the statement involves two columns with the same name from two different tables, you must use a qualified column name to unambiguously identify the column you intend. A qualified column name specifies both the name of the table containing the column and the name of the column, separated by a period (.). For example, the column named NAME in the STUDENT table has the qualified column name: STUDENT. NAME

Qualified column names can generally be used in a SQL statement wherever a simple (unqualified) column name can appear; exceptions are noted in the descriptions of the individual SQL statements.