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.
[GROUP BY group_by_list]
[ORDER BY order_list[ASC|DESC]]
Master Table: Employees
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)
-> 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
select eid fname+lname as 'fullname', salary from employees
i) write a query to change the column name eid to empid and shift the column fullname.
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%.
select eid,fname+''lname as 'fullname',salary*1.1 as 'hikedsalary' from employees.
By using distinct keyword we can eliminate duplicate rows in intermediate result set.
Select distinct * from employees
Intermediate result set:
Final result set:
Select distinct age,dept from employees
Select top 3 * from employees
It will select top 3 rows from the employees table.
'order by' clause:
-> This must be the last clause in the query.
select * from employees order by salary desc,fname asc
Final result set:
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
Write a query for displaying employee fullname in desecending order of the department name.
select * from employees fname+lname as 'fullname' orderby dept desc
Select * from employees where age>23
-> This will give employees list whose age is above 23.
To display the employees whose salary is greater than or equal to 18000
Query: select * from employees where salary >= 18000
To display the employees who are not from android department
Query: select fnam+lname as 'fullname' from employees where dept!='andriod'
To display the employees whose salary is between 10000 and 20000
Query: select * from employees whose salary between 10000 and 20000
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
-> 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)
Write a query to display employee whose salary is undefined or null
select * from employees where salary is null
Write a query to display db department employee details in the descending order of their salary.
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.
->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
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;
insert into employees select *from employeedetails;
drop table employeedetails;
->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.
create table students
sid int identity(1,1),
insert into students values (3, '5th class')---> will not work
insert into students values('vinny','5th class')--->will work
-> In a given table we can apply any number of identity columns.
->if we delete one column it will not affect on next column.