Thursday, 4 July 2013

SQL


1) Distinct
The SELECT DISTINCT statement is used to return only distinct (different) values ie. it eliminates duplicate values from the column.
 
SELECT DISTINCT City FROM Customers;


2) WHERE
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SELECT * FROM Customers WHERE Country='India';
 

3)The SQL AND & OR Operators
The AND operator displays a record if both the first condition AND the second condition are true.
The OR operator displays a record if either the first condition OR the second condition is true.


SELECT * FROM Customers WHERE Country='India' AND City='Mumbai';
SELECT * FROM Customers WHERE City='Mumbai' OR City='Chennai';


4) Combination of AND & OR

SELECT * FROM Customers WHERE Country='India' AND (City='Mumbai' OR City='Chennai');



5)INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Customers  (c_name, c_no)  VALUES (‘xyz’,’5’);
Note: Here c_name and c_no are column names in database and xyz
and 5 are the values inserted.

 5.1)  insert into table1(column_name) select column_name from table2
Note: Query 5.1 copy's column from another table to an existing table

5.2) select * into newTable_name from ExistingTable_name
Note: query 5.2 runs only in sql server. newTable_name does not exist. It is created after this query is fired

6)UPDATE Statement
UPDATE statement is used to update existing records.
UPDATE Customers SET c_name='abc', c_no='123' WHERE c_id='6';


7)DELETE Statement
The DELETE statement is used to delete records in a table.
DELETE  FROM Customers WHERE c_id='1’  ;

8)BETWEEN Operator
BETWEEN operator selects values within a range. The values can be numbers, text, or dates.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Products WHERE ProductName BETWEEN 'C' AND 'M';
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);



9) IN Operator
This query selects all customers with a City of  'Mumbai' or 'Chennai'
SELECT * FROM Customers WHERE City IN ('Mumbai', 'Chennai');

10) Having
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.


>select employee, sum(bonus) from emp_bonus group by employee;

Note:  group by arranges in ascending order by default. Also Aggregate function sum would normally add all the rows in  the column and display the result, but here we have used group by employee clause along with sum function so it would sum only similar employee and display the result as above .

Running the SQL above would return this:
 Now find the employees who received more than $1,000

BAD SQL:
select employee, sum(bonus) from emp_bonus 
group by employee where sum(bonus) > 1000;

Above query will not work

GOOD SQL:
select employee, sum(bonus) from emp_bonus 
group by employee having sum(bonus) > 1000; 


Difference between having clause and group by statement
Group by clause is used to group column(s) so that aggregates (like SUM, MAX, etc) can be used to find the necessary information.
The having clause is used with the group by clause when comparisons/conditions need to be made with those aggregate functions – like to see if the SUM is greater than 1,000.

Delete vs Truncate 
You can't rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently.
Reason:When you type DELETE all the data get copied into the Rollback Tablespace first. Then delete operation gets performed. That's why when you type ROLLBACK after deleting a table , you can get back the data(The system gets it for you from the Rollback Tablespace). All this process takes time.But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster. Once you Truncate you can't get back the data.

Rollback for delete command: (There should be, begin statement for rollback command)
begin transaction
delete  from Table_name;      // delete query

rollback transaction;   // Query to rollback

or


begin tran
delete  from
Table_name; 

rollback tran;
 

Note: Delete and rollback command must be fired separately  in order to see the difference. This works for Sql server only.

union vs union all
UNION removes duplicate records  UNION ALL does not. 
All the rows from both tables are displayed without duplicates in UNION
There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

eg. 
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers; 


Count(*) vs Count(1) vs Count(column)

Because 1 is an expression, there is no difference between Count(*) and Count(1).
COUNT(*) – Returns the total number of records in a table (Including NULL valued records).
COUNT(Column Name) Returns the total number of records in a table (Excluding NULL valued records).














SQL  Practice Examples:
                                                                                                           
  
    - To find top 3 salary
Ans: select e_id,salary from employee order by salary desc limit 3

- The following SQL statement selects the "ProductName" and "Price" records that have an above average price:

Ans: SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);

   - Display the highest, lowest, sum, and average salary of all employees. Label the columns as Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number
Ans: (Not rounded)
SELECT  MAX(salary) as MAXIMUM ,
 MIN(salary) ,
 SUM(salary),
 AVG(salary)
FROM employee;

(Only AVG rounded)
Syntax: SELECT ROUND (column_name,decimals) FROM table_name;
  Here decimals specifies the number of decimals to be returned

SELECT  MAX(salary) as MAXIMUM ,
MIN(salary) ,
SUM (salary),
ROUND (AVG (salary),0) 
FROM employee;

Note: For  ROUND (AVG (salary), 2 )  two decimal will be displayed eg. 23.12
For  ROUND (AVG (salary), 1 )  one decimal will be displayed eg. 23.1

- Select all customers with a City starting with the letter "s"
Ans:SELECT * FROM Customers WHERE City LIKE 's%'; 

Select all customers with a City ending with the letter "s"
Ans: SELECT * FROM Customers WHERE City LIKE '%s';



- 3rd max salary
Ans. Syntax:
Select salary from employee order by salary desc limit n-1,1
To find 3rd highest salary substitute n=3 , ie.  3-1,1 = 2,1 hence query would be
Select salary from employee order by salary desc limit 2,1
Note: Above will display only 3rd highest salary. To display 3rd and 4th highest salary use following query.
Select salary from employee order by salary desc limit 2,2
This will display two(3rd and 4th salary) rows. The no. marked with blue indicates how many rows to display.

- To find employee who have same salary
Ans select * from employee where (sal IN (select sal from employee group by sal having count(sal)>1 ))
Note: For salary which are repeated 3 times replace 1 by 3

- We can copy all records from one table to another ie.existing table:
Ans. INSERT INTO table2
     SELECT * FROM table1;
Note: Same no. of columns with matching data types should exist in both tables

- Copy only a few columns
Ans. INSERT INTO table2 (NAME, id)
SELECT NAME,emp_id FROM table1;

- Copy only the particular column into table2
INSERT INTO b (NAME,id)
SELECT NAME,emp_id FROM a where emp_id='2';
Note: a and b are tablename


- Inserting multiple rows in a single SQL query 
INSERT INTO MyTable ( Column1, Column2 ) VALUES( Value1, Value2 ), ( Value1, Value2 )


- Only table structure is copied, data is not copied
 Select * into newtable From dept Where 1=0 

-To find max length of a value in column
select MAX(len(dept_name)) as name from dept 

 

 JOIN:

 The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Note: inner join and join are same


Consider the following two tables, (a) CUSTOMERS table is as follows:
+----+----------+-----+-----------+----------+
| 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 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
(b) Another table is ORDERS as follows:
+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+




  • INNER JOIN:  (both Join and Inner join are same)returns rows when there is a match in both tables.
Syntax:
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
Eg.
SELECT  emp.id,  emp.name,  dept.dept_id,  dept.dept_name
FROM emp
INNER JOIN dept
ON emp.id = dept.dept_id;
 

  • LEFT JOIN: (right side null)returns all rows from the left table (table 1)with the matching rows in the right table (table2).The result is NULL in the right side when there is no match ie. If there are 100 rows in left table then all 100 rows from left table will be displayed and for that 100 rows if value does not exist in right table then null will be displayed in for right side.

    SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

  • RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

  •  FULL JOIN: (Left join + Right join)The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     FULL JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;


  • SELF JOIN :  A self join is a join in which a table is joined with itself.  
Consider table EMPLOYEE.  

 







The above data shows :
 Unnath Nayar's supervisor is Vijes Setthi
 Anant Kumar and Vinod Rathor can also report to Vijes Setthi.
 Rakesh Patel and Mukesh Singh are under supervison of Unnith Nayar. 


To get the list of employees and their supervisor the following sql statement has to be used :

SELECT a.emp_id AS "Emp_ID",a.emp_name AS "Employee Name", 
b.emp_id AS "Supervisor ID",b.emp_name AS "Supervisor Name
FROM employee a, employee b 
WHERE a.emp_supv = b.emp_id;


Output:









Stored Procedure:

Compilation is done only once when the stored procedure is created. Hence it is fast

1] Strored procedure with Input parameter

Create Procedure InsertStudentrecord
(
 @StudentFirstName Varchar(200), --Input parameter
 @StudentLastName  Varchar(200), --Input parameter
 @StudentEmail     Varchar(50)   --Input parameter
)
As
 Begin
   Insert into tbl_Students (Firstname, lastname, Email)
   Values(@StudentFirstName, @StudentLastName,@StudentEmail)
 End



2) Strored procedure with Transaction and try/catch block:

Create Procedure proc_customer
(
 @city Varchar(200) --Input parameter
)
As

 Begin transaction

 BEGIN TRY
   Insert into customer_copy (city)
   Values(@city)
       
   UPDATE customer
   SET city='chennai'
   WHERE customer_id='2';  
 
COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION  
END CATCH  

GO

Note: 1)To run above procedure execute following command
EXECUTE proc_customer 'Chennai'
2) If there are 2 input parameter then
EXECUTE  proc_name  'param_1', 'param_2'
3)Drop: drop procedure proc_name


3) Alter procedure:

alter Procedure proc_customer
(
 @city Varchar(200) --Input parameter
 @newColumn varchar(200)              --new parameter added/altered
)
As

 Begin transaction

 BEGIN TRY
   Insert into customer_copy (city,newColumn)        
   Values(@city,@newColumn)
       
   UPDATE customer
   SET city='chennai'
   WHERE customer_id='2';  
 
COMMIT TRANSACTION  
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH  

GO

Note: to run above procedure execute following command
EXECUTE proc_customer 'Chennai', 'anyValue'


4] Input and Output parameter

CREATE PROCEDURE spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output   --Output parameter
AS
BEGIN
SELECT @EmployeeCount = COUNT(Id)  --Output parameter is written in left side of =
FROM tblEmployee
WHERE Gender = @Gender
END

Execution:
To execute this stored procedure with OUTPUT parameter, follow the below steps.

1) First initialise a variable of same datatype as that of the output parameter. Here we have

declared @EmployeeTotal integer variable.
2) Then pass the @EmployeeTotal variable to the stored procedure. You have to specify the

OUTPUT keyword. If you don’t specify the OUTPUT keyword, the variable will be NULL.
Then execute the stored procedure.

DECLARE @Total int
EXECUTE spGetEmployeeCountByGender ‘Female’, @Total output

PRINT @Total

Note: An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage

TRIGGERS:
Triggers are created by USER but not called by user. It is called by SERVER when any EVENT or DML operations occur.

Note: As shown in diag below output is displayed in console when we insert update or delete
select * from inserted means display result for inserted queries.


Click image to zoom






Click on image to zoom


Strored procedures vs Trigger:
The trigger is called automatically by your database if a special event occurs (insert, update, delete).
The stored procedure is executed whenever it is called.


View:

A view is virtual, the data from a view is not stored physically. It is a set of queries that, when applied to one or more tables, is stored in the database as an object. A view encapsulates the name of the table. Thus provides security from hackers.

Syntax: 
    create view view_name 
           as
normal select query
  
Execution: select * from view_name      
   
Drop: drop view view_name

Rename: SP_Rename 'Old Name', 'New name'

Ex:
create view view_id_dept    ---- SIMPLE VIEW
        as
        select id,dept
        from employee
        where salary>10000


Types of Views:
1. Simple Views.
2. Complex Views.


Simple View:
1. It is based on ONLY ONE  TABLE.
2.We can perform DML(insert, update, delete) operations on Simple view, hence risky
3.If we delete a row from view than it will also delete the row from the actual TABLE.


COMPLEX VIEW:
1. It based on more than One table.
2. We cannot perform DML operations on Complex View. 
3. Complex View can contain functions, GROUP BY clause, DISTINCT keyword etc. 
4. Hence simple view can be made complex by using above conditions thus preventing
DML(insert,update,delete)operations


What is SCHEMABINDING a VIEW:
Schema binding binds/ties your views to the dependent physical columns i.e. if CustomerInfo_V is schema bind, no one will be able to alter the dbo.Customer table.

Why would we need that?
Just think that someone drops/alters the table dbo.Customer without paying any heed to our view. Now that would leave our view useless. Hence schema bind it, this will prevent any such accidents from happening.
View with schema binding is known as Indexed View.
The only downside is, it will make changing the table schema a bit difficult, even if it is just a small change e.g changing a NOT NULL column to allow nulls.
Also to be able to create an index on the view you need it.

syntax:
create view view_name 
with schemabinding
     as
select column_name from dbo.table_name
  where salary>10000

Note:
1)Any query that explicitly uses this(SCHEMABINDING) view will be able to take advantage of the index on the view.
2)We CANNOT make Indexes on Normal Views, we can ONLY make Indexes on View on which there is a SCHEMA BINDING.
  
Conditions:
1.Never use "*", use Column names which were required.
2.Use Full Table Name.
3. View with Schema Binding ONLY Restricts the Base Table from Dropping, but it Allows the DML operations(insert,update,delete) on the Table ,
4.To save the Table from DML operations on the View its better to make the COMPLEX VIEW rather than SIMPLE VIEWS.


Index:

Indexes allow the database application to find data fast; without reading the whole table.

Types of Indexes:
1. Clustered Index
2. Non-Clustered Index

syntax:
create index index_name
on table_name(column_name)

For Unique Index:
create unique index index_name
on table_name(column_name)

Note:
1. You cannot create Unique Index on columns which contains Duplicate Values.
2. You cannot insert Duplicate values on Unique Indexes.

For clustered index:
create clustered index index_name
on table_name(column_name)

Note: Clustered index is created automatically when you create a Primary Key in a Table

For nonclustered index:
create nonclustered index index_name
on table_name(column_name)

Drop a Index:
drop index index_name on table_name

Clustered vs Non-Clustered Index:
1)One table can only have one clustered Index(primary key is clustered index), but it can have many  non clustered index.
2)Clustered index contains data in their leaf node, while nonclustered index contains pointer to data (address) in there leaf node, which means one more extra step to get the data.
3)Clustered Index physically sort all rows while Non-clustered Index doesn't.
4)Clustered Index is also very good on finding unique values in a table.


How to increase database performance ?
1)Use the same data type for both primary and foreign key because DBMS will have to convert one data type to another.
2) Use indexing
3)Creating indexes on foreign key column(s) can improve performance because joins are often done between primary and foreign key pairs.
4)CHAR vs VARCHAR
char:
-CHAR Data Type is a Fixed Length Data Type.
-If you declare a column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character
-Since CHAR fields require less string manipulation because of fixed field widths it is faster than varchar.
Varchar :
-VARCHAR is a variable length Data Type
-If you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes
5)Table size:
If your query hits one or more tables with millions of rows or more, it could affect performance. Tables should be divide into smaller ones instead of one large table and there should be relations(primary and foreign key) between them. So less data which means faster performance.
6) select "column names" should be used instead of select *
7) Connection pooling in application will enhance DB performance


primary key
The attribute(column) that uniquely identifies a row or record. Null values are not allowed. There can be only one primary key in a table.

Foreign Key
An attribute(column) in a table whose value match a primary key in another table. There can be more than 1 foreign key in a table

Composite key(compound key)
Composite Key is a key which is combination of more than one  column of a given
table. It can be a Candidate key, Primary key. So, a table with combination of its primary
key and candidate key can be called as composite key.


In above example, ID is the primary key and Name is the candidate key
So, both the columns i.e. ID & Name will be considered as composite key of the address.

Candidate key
It is a column in a table which has the ability to become a primary key. If the table has
more than one candidate key, one of them will become the primary key, and the rest are
called alternate keys.

Alternate Key
Any of the candidate keys that is not part of the primary key is called an alternate key. 
Can a table have multiple unique, foreign, and/or primary keys?
A table can have multiple unique and foreign keys. However, a table can have only one primary key.
Can a unique key have NULL values? Can a primary key have NULL values?
There can be only one NULL value in unique key. The values in a primary key column, however, can never be NULL.
Can a foreign key reference a non-primary key?
Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key. Both unique and primary keys can be referenced by foreign keys.
Can a foreign key contain null values?
Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.
How many columns can you have (maximum) in a MySQL table?
There is a hard limit of 4096 columns per table.
For Oracle 1000 columns is maximum