SQL Tutorial

Introduction

SQL stands for Structured Query Language which is used to retrieve and manipulate data in Relational Database Management System (RDBMS). Relational database stores data in the form of tables. The tables are having columns and rows. SQL query can be used across a variety of databases such as SQL Server, Oracle, DB2, Access, Sybase, My SQL, MongoDB. Initially it was called SEQUEL (Structured English Query Language) and developed at IBM by Donald Chamberlin and Raymond Boyce in 1970s. it became a standard of American National Standard Institute (ANSI) in 1986 and of the International Organisation for Standardization (ISO) in 1987.

SQL Queries or statements are broadly categorised in to 3 types:

  • DDL – Data Definition Language – CREATE, ALTER, DROP, COMMENT, RENAME
  • DML – Data Manipulation Language – SELECT, INSERT, UPDATE, DELETE
  • DCL – Data Control Language – GRANT, REVOKE

DDL Statements

CREATE TABLE

Creates a table

CREATE TABLE AS

Create a table from skeletal definition and data of another table

ALTER TABLE
Add, Changes, Delete columns in a table , rename a table

DROP TABLE
Deletes a Table

COMMENT
Add Comments to the Data Dictionary

RENAME TABLE
Renames a Table
DML Statements
SELECT Statement Fetches Records from a table
INSERT Statement Inserts Records into a table
UPDATE Statement Updates Records in a table
DELETE Statement Delete records from a Table
TRUNCATE Statement Delete all records from a Table
DCL Statements
GRANT Statement Provides access or privileges on database objects to users
REVOKE Statement Removess access or privileges given to user on database objects
SQL Datatypes: SQL Constraints:
Numeric Datatypes NOT NULL
Character Datatypes UNIQUE
Date and Time Datatypes PRIMARY KEY
Binary Datatypes FOREIGN KEY
Miscellaneous Datatypes CHECK
DEFAULT




SQL Transactions:
Transaction Control Commands: Properties of Transaction:
BEGIN TRAN ATOMICITY
SAVE POINT CONSISTENCY
COMMIT TRAN ISOLATION
ROLLBACK TRAN DURABILITY
SQL Operators: SQL Functions:
Relational Operators Aggregate Functions:
Arithmetic Operators Scalar Functions:
Comparison Operators DATE Function:
Logical Operators STRING Function:
Set Operators NUMERIC Function:
SQL Clauses: SQL Conditions:
DISTINCT Clause AND Condition
FROM Clause OR Condition
WHERE Clause IN Condition
ORDER BY Clause LIKE Condition
GROUP BY Clause NOT Condition
HAVING Clause AND & OR Condition
WITH Clause IS NULL Condition
AS Clause IS NOT NULL Condition
TOP Clause EXISTS Condition
BETWEEN Condition

SQL Joins: SQL Keys:
LEFT JOIN Primary Key
RIGHT JOIN Unique Key
FULL JOIN Foreign Key
INNER JOIN Composite/Compound Key
OUTER JOIN Alternate Key
SELF JOIN Super Key
CROSS JOIN Candidate Key
ADVANCED SQL:
SQL VIEWS
SQL INDEXES
SQL SEQUENCES
SQL ALIASES
SQL SUBQUERIES
SQL TUNING
SQL INJECTION