SQL Queries Set 2

 

SQL Examples

Normalization with Examples:

Database Normalization is organizing non structured data in to structured data.Database normalization is nothing but organizing the tables and columns of the tables in such way that it should reduce the data redundancy and complexity of data and improves the integrity of data.

 

There are following Four Normal Forms used in Database Normalization:

1.First Normal Form

2.Second Normal Form

3.Third Normal Form

4. Boyce-code Normal Form(BCNF)

 

1.First Normal Form/1st Normal Form:

Example:

Consider following table which is not normalized:

Employee Table:

Employee No	Employee Name	Department
1	Amit	OBIEE,ETL
2	Divya	COGNOS
3	Rama	Administrator

To bring it in to first normal form We need to split table into 2 tables.

First table:Employee Table

Employee No	Employee Name
1	Amit
2	Divya
3	Rama

Second Table: Department table

Employee No	Department
1	OBIEE
1	ETL
2	COGNOS
3	Administrator

We have divided the table into two different tables and the column of each table is holding the automic values and duplicates also removed.

2.Second Normal Form/2nd Normal Form:

Example:

Let us consider following table which is in first normal form:

Employee No	Department No	Employee Name	Department
1	101	Amit	OBIEE
2	102	Divya	COGNOS
3	101	Rama	OBIEE

In above example we can see that department .Here We will see that there is composit key as{ Employee No,Department No}.Employee No is dependent on Employee Name and Department is dependent on Department No.We can split the above table into 2 different tables:

Table 1:Employee_NO table

Employee No	Department No	Employee Name
1	101	Amit
2	102	Divya
3	101	Rama

 

Table 2:Department table

Department No	Department
101	OBIEE
102	COGNOS

 

Now we have simplified the table in to second normal form where each entity of table is functionally dependent on primary key.

 

Third Normal Form/3rd Normal Form:

Example:

Consider following table:

Employee No	Salary Slip No	Employee Name	Salary
1	0001	Amit	50000
2	0002	Divya	40000
3	0003	Rama	57000

 

In above table Employee No determines the Salary Slip No.And Salary Slip no Determines Employee name.Therefore Employee No determines Employee Name.We have transitive functional dependency so that this structure not satisfying Third Normal Form.

For That we will Split tables into following 2 tables:

 

 

 

 

 

Employee table:

Employee No	Salary Slip No	Employee Name
1	0001	Amit
2	0002	Divya
3	0003	Rama

 

Salary Table:

Salary Slip No	Salary
0001	50000
0002	40000
0003	57000

 

Following are 2 Advantages of 3rd normal form:

1.Amount of data duplication is removed because transitive dependency is removed in third normal form.

2.Achieved Data integrity

 

4.BCNF(Boyce-Codd Normal Form)

Example:

Address-> {City,Street,Zip}

Key 1-> {City,Zip}

Key 2->{City,Street}

No non key attribute hence this example is of 3 NF.

{City,Street}->{zip}

{Zip}->{City}

There is dependency between attributes belonging to key.Hence this is BCNF.

SQL CREATE INDEX

CREATE INDEX index_name table_name (column1, column2, ...);

CREATE INDEX idx_lastname ON Persons (LastName);

CREATE INDEX idx_pname ON Persons (LastName, FirstName);

 

DROP INDEX

DROP INDEX table_name.index_name;

 

Sub Queries

SELECT column_name [, column_name ]FROM   table1 [, table2 ]

WHERE  column_name OPERATOR

   (SELECT column_name [, column_name ]

    FROM table1 [, table2 ]

    [WHERE])

 

SELECT * 

   FROM CUSTOMERS

   WHERE ID IN (SELECT ID

         FROM CUSTOMERS

         WHERE SALARY > 4500) ;

 

 

 

 

 

Subqueries with the INSERT Statement

INSERT INTO table_name [ (column1 [, column2 ]) ]

   SELECT [ *|column1 [, column2 ]

   FROM table1 [, table2 ]

   [ WHERE VALUE OPERATOR ]

 

INSERT INTO CUSTOMERS_BKP    SELECT * FROM CUSTOMERS    WHERE ID IN (SELECT ID    FROM CUSTOMERS) ;

Subqueries with the UPDATE Statement

UPDATE table SET column_name = new_value

[ WHERE OPERATOR [ VALUE ]

   (SELECT COLUMN_NAME

   FROM TABLE_NAME)

   [ WHERE) ]

 

UPDATE CUSTOMERS

   SET SALARY = SALARY * 0.25

   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP

      WHERE AGE >= 27 );

 

 

Subqueries with the DELETE Statement

DELETE FROM TABLE_NAME

[ WHERE OPERATOR [ VALUE ]

   (SELECT COLUMN_NAME

   FROM TABLE_NAME)

   [ WHERE) ]

 

DELETE FROM CUSTOMERS

   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP

      WHERE AGE >= 27 );

 

 

 

Triggers

The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name  

{BEFORE | AFTER | INSTEAD OF }  

{INSERT [OR] | UPDATE [OR] | DELETE}  

[OF col_name]  

ON table_name  

[REFERENCING OLD AS o NEW AS n]  

[FOR EACH ROW]  

WHEN (condition)   

DECLARE

   Declaration-statements

BEGIN  

   Executable-statements

EXCEPTION

   Exception-handling-statements

END; 

 

Select * from customers;  

 

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

+----+----------+-----+-----------+----------+

 

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

WHEN (NEW.ID > 0) 

DECLARE

   sal_diff number; 

BEGIN 

   sal_diff := :NEW.salary  - :OLD.salary; 

   dbms_output.put_line('Old salary: ' || :OLD.salary); 

   dbms_output.put_line('New salary: ' || :NEW.salary); 

   dbms_output.put_line('Salary difference: ' || sal_diff); 

END; 

Index

Student

Id	Name	Gender	City
4	Kaira	Female	NY
3	Alex	Male	NY
2	Alanah	Female	NY
1	Trina	Female	Londan

 

 

CREATE UNIQUE INDEX index_name on table_name (column_name);

 

 

 CREATE CLUSTERED INDEX Student ON student(Id DESC)

 

-- Create Non Clustered Indexes in SQL Server

 

CREATE NONCLUSTERED INDEX IX_CustomerRecord_YearlyIncome

ON CustomerRecord ([Yearly Income] ASC)

 

CREATE NONCLUSTERED INDEX Student_1 ON Student (Name ASC

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL Joins

 

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

 

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

 

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID=Customers.CustomerID;

 

 

OrderID	CustomerName	OrderDate
10248	Wilman Kala	1996-07-04
10249	Tradição Hipermercados	1996-07-05
10250	Hanari Carnes	1996-07-08

 

 

· (INNER) JOIN: Returns records that have matching values in both tables

· LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

· RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

· FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

 

SQL INNER JOIN

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

 

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

 

 

 

 

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

 

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

 

SQL LEFT JOIN

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

 

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

 

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

 

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerName;

 

CustomerName	OrderID
Alfreds Futterkiste	null
Ana Trujillo Emparedados y helados	10308
Antonio Moreno Taquería	10365
Around the Horn	10355

 

 

 

 

 

 

SQL RIGHT JOIN

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

 

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

 

EmployeeID	LastName	FirstName	BirthDate	Photo
1	Davolio	Nancy	12/8/1968	EmpID1.pic
2	Fuller	Andrew	2/19/1952	EmpID2.pic
3	Leverling	Janet	8/30/1963	EmpID3.pic

 

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName

FROM Orders

RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

ORDER BY Orders.OrderID;

 

 

OrderID	LastName	FirstName
12342	West	Adam
10248	Buchanan	Steven
10249	Suyama	Michael

 

SQL FULL OUTER JOIN

 

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

 

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name

WHERE condition;

 

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

 

 

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

 

Self JOIN

 

SELECT column_name(s)

FROM table1 T1, table1 T2

WHERE condition;

 

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

 

 

 

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2,A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID

AND A.City = B.City 

ORDER BY A.City;

CustomerName1	CustomerName2	City
Cactus Comidas para llevar	Océano Atlántico Ltda.	Buenos Aires
Cactus Comidas para llevar	Rancho grande	Buenos Aires
Océano Atlántico Ltda.	Cactus Comidas para llevar	Buenos Aires
Océano Atlántico Ltda.	Rancho grande	Buenos Aires