Structured Query language

What is SQL?

• A data manipulation language
• It is useful in communicating with the data base
• Mostly it will be in the table format
• Not a case sensitive
• ; is a not mandatory

Its is classified into three types, DDL, DML AND DQL.

1. DDL(Data Definition Language)

Create, Drop and Alter

CREATE

Syntax: create table table_name(

integer_variable int  // employee_id int;

String Varchar(n) // Employee_name varchar(30);

);

How to see the table after creating?

select * form table_name

DROP

Syntax: Drop table table_name

ALTER

alter table table_name add Employee_city varchar(30);

alter table table_name drop column Emp_city

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2. DML(Data Manipulation Language)

Insert, Delete and Update

INSERT

Syntax: insert into table_name values(5,’Aneesh’,22000); // String should be always should be within the single quotes

(or)

insert into table_name (ename,esal) values (Panda,9000)

(or)

insert into table_name values(null,’hell boy’,25000)

UPDATE

Syntax: update table_name set eid=96 where ename=Aneesh

DELETE

Syntax: delete from table_name where eid=25

delete from table_name // it will delete all the contents in the table

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3. DQL(Data Query Language)

Relational Operators (<,>,<=,>=,=,<>) \\ <> not equal to

example: select * form table_name where Esal>25000

Logical Operators

A    B              AND     OR 

0    0               0           0     

0    1               0           1

1    0               0           1

1    1               1           1

A   NOT

0     1

1     0

Syntax:

select * form table_name where Eid=5 AND Ename=Panda

Note: If both are correct value it will display else nothing.

select * form table_name where Eid=5 OR Ename=Panda

Note: both the different rows are executed.

BETWEEN AND

Syntax: select * from table_name where Esal between 20000 and 60000

IN

Syntax: select * from table_name where Ejob in (‘System Admin’,’Test Lead’)

select * from table_name where Esal in (10000,20000,30000)

NOT

Syntax: select * form table_name where Esal not in (5000)

select * from table_name where Ename not like (‘%a’) // it will not show the name ends with a

Like – %a – string ends with a and a% – string starts with a

IS NULL

select * form table_name where Eid is null // if we left blank or when we specify null those can be identified

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CONSTRAINTS

UNIQUE KEY – Where we don’t need the repeat in the values example Employee id should not repeat where we can use this unique key

int EID unique

NOT NULL – Where do not need the value to be null. We can use in the name field

Employee_name varchar(30) not null

DEFAULT – When we assign default value, when we left blank or null it will auto assign

Ecity varchar(30) default ‘chennai’ // note if we specifly assign NULL it will not take the default value.

CHECK - Ensures that the value in a column meets a specific condition

eid int unique, check (eid>100)

PRIMARY KEY – Combination of unique and not null key but can be used only once for a column in a table

Eid int primary key

FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table

eid int foreign key refrerences emp1(eid)

Note: Eid values of both the tables should be same

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

JOINS

SQL join is a technique to join two or more tables on a specific condition(where clause) and retrieve desired result.

The different types are:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables
• SELF JOIN: A table can be joined to same table. Mostly used when a table has parent-child relationship in the same table

INNER JOIN
Select column_name(s) from table_name1 INNER JOIN table_name2
on table_name1.column_name = table_name2.column_name
LEFT JOIN
Select column_name(s) from table_name1 LEFT JOIN table_name2
on table_name1.column_name = table_name2.column_name
RIGHT JOIN
Select column_name(s) from table_name1 RIGHT JOIN table_name2
on table_name1.column_name = table_name2.column_name
FULL JOIN
Select column_name(s) from table_name1 FULL JOIN table_name2
on table_name1.column_name = table_name2.column_name
ORDER BY
If needed we can also sort the columns,
Select column_name(s) from table_name1 FULL JOIN table_name2
on table_name1.column_name = table_name2.column_name
ORDER BY Column_name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IN BUILT FUNCTIONS
1. AVG() – to find the average of a column
select avg(Column_name) from Table_name
2. Count() – to count the number of column count
3. Sum() – to sum of all the values in the column
4. Max() and Min() – to find the maximum and minimum values in the column
5.Group by - is used in collaboration with the SELECT statement to arrange identical data into groups.
6. Having - enables you to specify conditions that filter which group results appear in the final results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.
Select Employee_job, sum(Emp_salary) from Table_name GROUP BY Employee_job having sum(Employee_salary)>200000
7. UPPER() – to display in Upper case
8. LOWER() – to display in lower case
9. Get Date() – Returns the current system date and time
10. Len() – Returns the length of the text field
11. Round() – rounds the value
select round (column_name, decimal) from table_name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRANSACTION CONTROL LANGUAGE (TCL)
COMMIT, ROLL BACK & SAVE
Commit - What ever we type on the sql server it automatically commits that is saves the statements or lines but not permanently
ROLL BACK
We need to begin the transaction as
BEGIN TRANSACTION TRANSACTION_NAME
insert some values or do any operations
when we say
ROLLBACK TRANSACTION TRANSACTION_NAME
what ever the operation after the begin transaction is deleted.
SAVE
Begin the transaction
insert some values or do any operations
now, save the transaction
SAVE TRANSACTION TRANSACTION_NAME
Once Saved the transaction cannot be rolledback.