T-SQL Statements:

DML:

Data manipulation Language is used to modify, delete, insert and update data in database. Ex: update, insert and delete statements.

DQL:

Data Query Language, by using which we can retrieve the data from the database. Ex: select statement

DDL:

Data Definition Language, used to create and modify the structure of database objects in database. Ex: create, alter and drop statements.

DCL:

Data Control Language, used to create role, permissions and referential integrity as well as it is used to control access to database by securing it. Ex: grant, revoke statements.

TCL:

Transactional Control Language, is used to manage different transactions occurring within a database. Ex: commit, rollback statements.

Syntax:

SELECT select_list

[INTO new_table_name]

FROM table_list

[WHERE search_conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list[ASC|DESC]]
Master Table: Employees
eidfnamelnameagesalarydeptdoj
1rajeevshukla2312000.net2011-10-23
2sowmyakumari2319000db2010-11-13
3kishorekumar2736000andriod2011-10-16
4abhimanyubiswal22NULLandriod2010-02-22
5sonikumar2421800.net2009-06-21
6anu_singh2212000db2010-10-23
7_dineshmoh%anty2315000.net2009-08-26
8nishalakumari2218000db2008-07-19
1rajeevshukla2312000.net2011-10-23
Here after we will be using this table as reference table for all the examples.

Forms of select statements :

1) seect * from employees

2) select eid,fname,lname,age,salary,dept,doj from employees
    -> this query is better than the first one

3) select sqltraining.dbo.employees.fname, sqltraining.dbo.employees.lname,    sqltraining.dbo.employees.age, sqltraining.dbo.employees.dept,
-> (sqltraining-database name) -> (dbo-schema) -> (employees-tablename) -> Third query is much better than 1) and 2) -> This is called as full naming convention on query.

'as' keyword :

as keyword is usefull to insert column name into the table Ex:
select eid fname+lname as 'fullname', salary from employees
result set:
eidfullnamesalary
Assignments: i) write a query to change the column name eid to empid and shift the column fullname. Query: select eid as 'empid',fname+''lname as 'fullname',salary from employees ii)write a query to change salary to hiked salary and salary must be hiked by 10%. Query: select eid,fname+''lname as 'fullname',salary*1.1 as 'hikedsalary' from employees.

'distinct' keyword:

By using distinct keyword we can eliminate duplicate rows in intermediate result set. EX1:
Select distinct * from employees
Intermediate result set:
eidfnamelnameagesalarydeptdoj
1rajeevshukla2312000.net2011-10-23
2sowmyakumari2319000db2010-11-13
3kishorekumar2736000andriod2011-10-16
1rajeevshukla2312000.net2011-10-23
Final result set:
eidfnamelnameagesalarydeptdoj
1rajeevshukla2312000.net2011-10-23
2sowmyakumari2319000db2010-11-13
3kishorekumar2736000andriod2011-10-16
Ex2: Select distinct age,dept from employees
agedept
23.net
23db
27android
22android
24.net
22db

'top' keyword:

Syntax:

Select top 3 * from employees
Final result: It will select top 3 rows from the employees table.

'order by' clause:

-> This must be the last clause in the query. Ex:
select * from employees order by salary desc,fname asc
Final result set:
eidfnamesalary
3kishore36000
5soni21800
2sowmya19000
8nishala18000
7_dinesh15000
6anu12000
1rajeev12000
1rajeev12000
4abinayuNULL
Note: If we use orderby,it will use table scanning technique to arrange in asc or desc i.e it will compare each rows. in this case it is 9*9=81 times, so it is better to avoid orderby keyword Assignment: Write a query for displaying employee fullname in desecending order of the department name. Query: select * from employees fname+lname as 'fullname' orderby dept desc

'where' clause:

Select * from employees where age>23
-> This will give employees list whose age is above 23. Ex1: To display the employees whose salary is greater than or equal to 18000
Query: select * from employees where salary >= 18000
Ex2: To display the employees who are not from android department
Query: select fnam+lname as 'fullname' from employees where dept!='andriod'
Ex3: To display the employees whose salary is between 10000 and 20000
Query: select * from employees whose salary between 10000 and 20000
EX4: To display the employee details who is getting highest salary in android department.
Query: select top 1 * from employees where dept='android' order by salary desc
Note: -> Include actual execution plan button to see the execution flow. Query To display employees whose salary = 15000,21800 or 19000
-> select * from employees where salary in (15000,21800,19000)
in case of unrealted values we can go for this query
select * from employees where salary=15000 or salary=21800 or salary=19000
Query to display employees whose salary is not equal to 15000,21000 and 19000
select * from employees where salary not in (15000,21800,19000)
Assignments: Write a query to display employee whose salary is undefined or null Query: select * from employees where salary is null Write a query to display db department employee details in the descending order of their salary. Query: select * from employees where dept='db' orederby salary desc

Copying data from one table to another table:

Here we have 2 cases i) When destination table is not existing. ii) When destination table is existing. Let us see the first case (i) ->in same database, if we have a table name students, if we want to copy to studentsdetails new table
select * into studentsdetails from students
-> other database
select * into db25.dbo.studentsdetails from db24.dbo.students.
Note: ->we have to use full naming convention when wee need to copy table to other database. Now let us see second case (ii)
insert into studentsdetails select * from students
-> If the destination table is having more columns when compared to source table we have to specify column names also
insert into studentsdetails(sid,name,age) select * from students
Note: columns name may be different but columns datatypes should match. Query to query for deleting duplicate records present in a row
select distinct into employeedetails from employees;
delete employees;
insert into employees select *from employeedetails;
drop table employeedetails;

Identity columns:

->If we declare any column as identity column then we cannot give value to that column explicitly. ->We can apply identity column only on int datatype. Ex:
create table students 
(
sid int identity(1,1),
name varchhar(40),
class char(20)
)
insert into students values (3, '5th class')---> will not work
insert into students values('vinny','5th class')--->will work
Note: -> In a given table we can apply any number of identity columns. ->if we delete one column it will not affect on next column.