Sql(Structured Query Language)

It provies the basic and advanced concepts of SQL(Structured Query Language).

SQL is used to perform operations on the records in database such as updating records, deleting, creating and modifying tables, views etc.

Eg:

Table Name: Persons

Table Name: Orders

SQL DML and DDL

DML

1. SELECT - extracts data from a database
2. UPDATE - updates data in a database
3. DELETE - delectes data from a database
4. INSERT INTO - inserts new data into a database

DDL

1. CREATE DATABASE - creates a new database
2. ALTER DATABASE - modifies a database
3. CREATE TABLE - creates a new data
4. ALTER TABLE - modifies a table
5. DROP TABLE - deletes a table
6. CREATE INDEX - create an index(search key)
7. DROP INDEX - delete an index

SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database. 
Syntax:
CREATE DATABASE database_name 

SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database 
Syntax: 
CREATE TABLE table_name 
( 
column_name1 data_type, 
column_name2 data_type, 
column_name3 data_type, 
.... 
)	

SQL INSERT INTO Statement

The INSERT INTO statement is used to insert a new row in a table 
Syntax: 
INSERT INTO table_name VALUES ( value1, value2, value3,...) INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) 

SQL UPDATE Statement

The UPDATE statement is used to update existing records in a table  
Syntax: 
UPDATE table_name SET column1=value, column2=value2,...  
WHERE some_column=some_value 

SQL DELETE Statement

The DELETE statement is used to delete rows in a table  
	
DELETE FROM table_name WHERE some_column=some_value 

Note: If you omit the WHERE clause, all records will be deleted

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table  
Syntax: To Add a column 
ALTER TABLE table_name 
ADD column_name datatype 

Syntax: To Delete a column 
ALTER TABLE table_name 
DROP COLUMN column_name 

Syntax: To Change the Data Type 
ALTER TABLE table_name
ALTER COLUMN column_name datatype 

Example:

ALTER TABLE Persons
ADD DateOfBirth date 

Drop Column

ALTER TABLE Persons 
DROP COLUMN DateOfBirth 

SQL SELECT Syntax

SELECT column_name(s) 
FROM table_name 
or 
SELECT * FROM table_name 

Example: SELECT LastName,FirstName FROM Persons

SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table

Syntax: 
SELECT DISTINCT column_name(s)
FROM table_name  

SQL WHERE Clause

The where clause is used to filter the records

Syntax: 
SELECT column_name(s) 
FROM table_name 
WHERE column_name operator value 

Exercise: select only the persons living in the city "Hyderabad" from the table above

Ans: SELECT * FROM Persons WHERE City='Hyderabad'

Quotes around Text Fields

SQL uses single quotes around text values (most database systems will also accept double quotes Although, numeric values should not be enclosed in quotes

Example:
SELECT * FROM Persons WHERE FirstName='Tove' 
SELECT * FROM Persons WHERE Year=1965 
Operators Allowed in the WHERE Clause 
With the WHERE clause, the following operators can be used: 

SQL AND & OR Operators

The AND & OR operators are used to filter records based on more than one condition Example:

select only the persons with the first name equal to "Tove" AND the last name equal to "John":

SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='John'

Example for Combination of AND and OR

SELECT * FROM Persons WHERE LastName='John' AND (FirstName='Tove' OR FirstName='Ola')

SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column

The ORDER BY keyword sort the records in ascending order by default

Systax:
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC 	

Delete All Rows

DELETE FROM table_name 
or 
DELETE * FROM table_name 

Note: Be very careful when deleting records. You cannot undo this statement!

SQL TOP Clause

The TOP clause is used to specify the number of records to return

Syntax:	
SELECT TOP number|percent column_name(s) 
FROM table_name 		

SQL Wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

SELECT * FROM Persons WHERE FirstName LIKE '_la'

SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'

SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SELECT column_name(s) 
FROM table_name
WHERE column_name LIKE pattern 

SELECT * FROM Persons 
WHERE City LIKE 's%' 	

SELECT * FROM Persons 
WHERE City LIKE '%tav%' 

SELECT * FROM Persons
WHERE City NOT LIKE '%tav%' 

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause

Syntax: 
SELECT column_name(s)  FROM table_name  WHERE column_name IN (value1,value2,...) 
Example: SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen') 

SQL BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates

Syntax: 
SELECT column_name(s) 
FROM table_name 
WHERE column_name 
BETWEEN value1 AND value2 

 SELECT * FROM Persons 
WHERE LastName 
BETWEEN 'Hansen' AND 'Pettersen' 
SELECT * FROM Persons 
WHERE LastName 
NOT BETWEEN 'Hansen' AND 'Pettersen' 

SQL Alias

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names

An alias name could be anything, but usually it is short. Syntax For Tables: SELECT column_name(s) FROM table_name AS alias_name

Syntax For Columns: 
SELECT column_name AS alias_name 
FROM table_name 

Example
SELECT po.OrderID, p.LastName, p.FirstName 
FROM Persons AS p, 
Product_Orders AS po 
WHERE p.LastName='Hansen' AND p.FirstName='Ola' 

SQL UNION Operator

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must

also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Syntax: 
SELECT column_name(s) FROM table_name1 
UNION 
SELECT column_name(s) FROM table_name2 

Note:The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT

statement in the UNION.

"Employees_Norway":

"Employees_USA":

Now we want to list all the different employees in Norway and USA

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway 
UNION
SELECT E_Name FROM Employees_USA 

The result-set will look like this:

Note: This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them will be listed. The UNION command selects only distinct values.

SQL UNION ALL Example

Now we want to list all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway 
UNION ALL 
SELECT E_Name FROM Employees_USA

Result :

SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different table

The SELECT INTO statement is most often used to create backup copies of tables

Syntax: 
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename 

Or we can select only the columns we want into the new table:

SELECT column_name(s) 
INTO new_table_name [IN externaldatabase]
FROM old_tablename 

SQL SELECT INTO Example

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.

We use the following SQL statement:

SELECT * 
INTO Persons_Backup
FROM Persons 	

We can also use the IN clause to copy the table into another database:

SELECT * 
INTO Persons_Backup IN 'Backup.mdb' 
FROM Persons 

We can also copy only a few fields into the new table:

SELECT LastName,FirstName 
INTO Persons_Backup 
FROM Persons 

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city

"Hyderabad":

SELECT LastName,Firstname 
INTO Persons_Backup 
FROM Persons 
WHERE City='Hyderabad' 

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).

We will focus on the following constraints:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK DEFAULT
6. DEFAULT

SQL NOT NULL Constraint

By default, a table column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept

NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new

record, or update a record without adding a value to this field

Example
CREATE TABLE Persons 
( 
P_Id int NOT NULL, 
LastName varchar(255) NOT NULL, 
FirstName varchar(255),
Address varchar(255), 
City varchar(255) )

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of

columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

CREATE TABLE Persons 
(
P_Id int NOT NULL UNIQUE, 
LastName varchar(255) NOT NULL, 
FirstName varchar(255), 
Address varchar(255), 
City varchar(255) 
)

Constraint on multiple columns

CREATE TABLE Persons 
(
P_Id int NOT NULL, 
LastName varchar(255) NOT NULL, 
FirstName varchar(255), 
Address varchar(255), 
City varchar(255), 
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) 
)

SQL UNIQUE Constraint on ALTER TABLE

ALTER TABLE Persons 
ADD UNIQUE (P_Id) 
ALTER TABLE Persons 
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) 

To DROP a UNIQUE Constraint

ALTER TABLE Persons 
DROP CONSTRAINT uc_PersonID 

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values

Each table should have a primary key, and each table can have only ONE primary key

CREATE TABLE Persons 
(
P_Id int NOT NULL PRIMARY KEY, 
LastName varchar(255) NOT NULL, 
FirstName varchar(255), 
Address varchar(255), 
City varchar(255) 
)   

SQL PRIMARY KEY Constraint on ALTER TABLE 
ALTER TABLE Persons 
ADD PRIMARY KEY (P_Id) 
ALTER TABLE Persons 
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) 

To DROP a PRIMARY KEY Constraint
ALTER TABLE Persons 
DROP PRIMARY KEY 
ALTER TABLE Persons 
DROP CONSTRAINT pk_PersonID 

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Persons" table:

The "Orders" table

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column,

because it has to be one of the values contained in the table it points to.

Example

CREATE TABLE Orders 
(
O_Id int NOT NULL PRIMARY KEY, 
OrderNo int NOT NULL, 
P_Id int FOREIGN KEY REFERENCES Persons(P_Id) 
) 

CREATE TABLE Orders 
(
O_Id int NOT NULL, 
OrderNo int NOT NULL, 
P_Id int, 
PRIMARY KEY (O_Id), 
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES  Persons(P_Id) 
)	

SQL FOREIGN KEY Constraint on ALTER TABLE 
	ALTER TABLE Orders
ADD FOREIGN KEY (P_Id) 
REFERENCES Persons(P_Id)  

ALTER TABLE Orders 
ADD CONSTRAINT fk_PerOrders 
FOREIGN KEY (P_Id) 
REFERENCES Persons(P_Id) 

CREATE TABLE Persons 
( 
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL, 
FirstName varchar(255), 
Address varchar(255), 
City varchar(255) 
)

CREATE TABLE Persons 
( 
P_Id int NOT NULL, 
LastName varchar(255) NOT NULL, 
FirstName varchar(255), 
Address varchar(255), 
City varchar(255) DEFAULT 'Hyderabad' ) 

CREATE TABLE Orders 
( 
O_Id int NOT NULL, 
OrderNo int NOT NULL, 
P_Id int, 
OrderDate date DEFAULT GETDATE() 
)