DDL – Data definition Language(Which deals with database schemas and descriptions that how the data should reside in database) – create,alter,drop, truncate, comment
DML – Data manipulation language(Modify, Update, Delete data in database) – select, insert, update, delete
DCL – Data Control Language(Regarding the rights of the database) grant,revoke
select * from dbo.tbl_empdtls
select Department, Count(*) as 'Number of Employees'
group by Department
select Gender, count(*) as Gender
group by Gender
select Department,Gender, Count(*) as 'Number of Employees'
group by Department, Gender
order by 2 ASC --'1' it means the first column in the select statement Department
--How many Employees who is hired in each year
select YEAR(HireDate) as HireYear,count(*) as 'Number of Employee'
group by year(HireDate)
order by count(*) desc
--select min sal,max sal, avg sal in each department
select JobTitle, min(Salary) as 'MinSalary', max(Salary) as 'Maximum', avg(Salary) as 'AverageSalary'
group by JobTitle
order by MinSalary
SQL Aggregate functions, slides presentation: This presentation describes SQL Aggregate function covering SQL COUNT(), SUM(), MAX(), MIN(), AVG) functions with examples.
A query within another query is called subquery or nested query . Inner Query, Outer query is in subquery.
Sub Query are two types they are, co-related sub query and non co-related subquery. They works for both DDL and DML statements
Co-related means relationship/dependent on
Non co-related means independent
There are two queries in sub query. You will see group by, order by is single type query but in sub query it needs multiple query
Non co related: They are two types,
Inner query: It will first execute. It will return some result. Independently it will execute.
Outer Query: Based on the inner query result now outer query will execute. The whole process is known as non co-related
--who is getting the minimum salary in the table
select * from tbl_empdtls
where salary =
(select min(salary) from tbl_empdtls)
where ID IN(select ID
= for comapring single value
IN for comparing multiple value
create table customers_backup(ID int primary key,name varchar(20),age int,salary numeric(9,2), address varchar(20));
insert into Customers_backup
select * from customers
where ID in(select ID
select * from customers_backup
--update sub query
WHERE AGE IN(select age
delete from customers
where AGE IN(select AGE
--select the details of the product not yet sale at least once
--non co related
select id ,name,[Description]
from tbl_products where id not in(select distinct ProductId from tbl_ProductSales)
--subquery and join is same
select p.Id,p.Name,p.[Description] from tbl_products p
left join tbl_ProductSales ps on p.Id=ps.ProductId
where ps.ProductId is null
Co-related: It is dependent query. Inner query can’t execute independently. Inner query depends on outer query. Without returning row from the outer query inner can’t execute. In co-related sub query. All execute first.
(select sum(quantity) from tbl_ProductSales where ProductId=tbl_Products.Id) as QuantitySold
MS SQL Server store procedure is used to save time write the same code again and again for storing in database and also get the rquired output by passing parameters.
Following the basic syntax of stored procedure creation.
create procedure <procedure name>
Lots of parameters used in stored procedure:
IN paramater is a paramater whose value is past inside of the procedure
OUT paramater is a paramater whose value is pass outside the parameter
OUT parameter return some value
IN does not return any value
Here are the store procedure example basic in my blog