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.
Leave a comment
No Cmomments yet