SQL Theroy

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.

SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

• 
  
  SQL Process:   
  
  When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.
  There are various components included in the process. These components are Query Dispatcher, Optimization Engines, Classic Query Engine and SQL Query Engine, etc. Classic query engine handles all non-SQL queries, but SQL query engine won't handle logical files.
  
  
  Following is a simple diagram showing SQL Architecture:
  
  
                                          
  
  
  Tables :
           A table can be thought of as a storage area, like a filing cabinet. You use tables to store information about anything: employees, departments, etc. A database may contain one or more number of tables.
   
   Rows :
  If table has been defined to hold information about (for example) employees, a row is a horizontal cross section into the table which contains the information about a single employee.
  
  
  Columns :
  A column is the vertical cross section of a table, or in other words, a column defines each of the attributes about the data  stored on a particular table. For example, if you have a table which holds employee information, you could have several columns which determine employee number, name, job.etc.
  
  
  Fields :
  A field is where rows and columns intersect. A field points to a particular column on a particular row within a table.
  
  
   Primary Keys : 
  A primary key is a column that defines the uniqueness of a row. For example, with employee number, you would only ever want one employee with a number of 10001.
  
  
  Foreign Keys :
  A foreign key defines how different tables relate to each other. For example, if you have      defined  two tables, one for employees and one for departments, there will be a foreign key column on the employee table which relates to
  the department code column on the department table.

Types of SQL Commands:

  Data Definition Language (DDL):

The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME and DROP statements:

·CREATE creates an object (a table, for example) in the database.

·DROP deletes an object in the database, usually irretrievably.

·ALTER modifies the structure an existing object in various ways for example, adding a column to  an existing table. 

Data Manipulation Language (DML): 

The Data Manipulation Language (DML) is the subset of SQL used to Add, update and delete data.

The acronym CRUD refers to all of the major functions that need to be implemented in a relational database application to consider it complete. Each letter in the acronym can be mapped to a standard SQL statement.

  Data Control Language (DCL): 

·         GRANT - gives user's access privileges to database

·         REVOKE - withdraw access privileges given with the GRANT command.

  Transaction Control Language (TCL):

COMMIT - save work done 

SAVEPOINT - identify a point in a transaction to which you can later roll back 

ROLLBACK - restore database to original since the last COMMIT 

SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use.

SQL Constraints:

Constraints are used to limit the type of data that can go into a table. Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

Following are commonly used constraints available in SQL: 

  NOT NULL Constraint: Ensures that a column cannot have NULL value.

  DEFAULT Constraint: Provides a default value for a column when none is specified.

  UNIQUE Constraint: Ensures that all values in a column are different.

  PRIMARY Key: Uniquely identified each rows/records in a database table.

 FOREIGN Key: Uniquely identified a rows/records in any another database table.

 CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. 

 INDEX: Use to create and retrieve data from the database very quickly.