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