SQL

Create Database and Use Database

Create database Q
use Q

Create Sales Table Structure

Create table Sales(Item varchar(15),Price int, Qty int)

============================================================================

Alter table structure --- Add new column in SQL

Alter table Sales add Zone varchar(15)

============================================================================

Alter table structure --- Alter the data type and its size in SQL

alter table Sales alter column Zone char(11)

============================================================================

Alter table structure --- DROP column in SQL

Alter table Sales drop column Zone

============================================================================

Rename Table -- in SQL

Sp_rename 'Sales','Sale'
Sp_rename 'Sale','Sales'

============================================================================

Rename Column Name -- in SQL

sp_rename 'Sales.Qty', 'Quantity', 'COLUMN'

============================================================================

Drop table -- in SQL

drop table Sales

============================================================================

Delete all the rows -- in SQL

Delete Sales

============================================================================

UPDATE COLUMN -- in SQL

update Sales set Price = 500
update Sales set Price = 1100 where Item in('Apple','Grapes')

============================================================================

DELETE table -- in SQL

Drop table Sales

============================================================================

Insert values into table for all the columns -- in SQL

insert into Sales values('Apple',15,120)
insert into Sales values('Orange',25,20)
insert into Sales values('Papaya',35,520)
insert into Sales values('Grapes',45,320)
insert into Sales values('Pineapple',40,250)

============================================================================

Insert values into table for Specified columns -- in SQL

insert into Sales(Item,Quantity) values('Pen',120)
insert into Sales(Price,Quantity) values(11,120)

============================================================================

Extract all the columns and specified columns -- in SQL

Select * from Sales
Select Item,Quantity from Sales

============================================================================

computed columns -- in SQL

Select Item, Price, Price * Quantity as TotalCost from Sales

============================================================================

FROM Clause Examples -- in SQL

Select * from Sales
Select Item,Price from Sales
Select Item, Price from (Select * from Sales) T where Price > 35

============================================================================

WHERE Clause Examples -- in SQL

Select * from Sales where Item = 'Grapes'
Select * from Sales where price > 35

============================================================================

GROUP BY Clause Examples -- in SQL

Columns mentioned in Select clause has to mentioned in Group by

Select Item, sum(Price) as Total from Sales group by Item
Select Item, count(Price) as CountData from Sales group by Item
Select Item, Avg(Price) as AvgData from Sales group by Item
Select Item, Max(Price) as MaxData from Sales group by Item
Select Item, Min(Price) as MinData from Sales group by Item
Select Item,Zone, sum(Price) as Total from Sales group by Item,Zone

============================================================================

HAVING Clause Examples -- in SQL

Select Item,sum(Price) as Total from Sales group by Item having sum(Price) > 50

Apply having clause on grouped data
Select Zone,count(Qty) as c from Sales group by Zone having count(Qty) > 3
Select Zone,Sum(Qty) as s from Sales group by Zone having Sum(Qty) > 3
Select Zone,Avg(Qty) as a from Sales group by Zone having Avg(Qty) > 3
Select Zone,Min(Qty) as m from Sales group by Zone having Min(Qty) > 3
Select Zone,Max(Qty) as m from Sales group by Zone having Max(Qty) > 3

============================================================================

ORDER BY Clause Examples -- in SQL

Select Item,Price from Sales order by Price Desc
Select Item,Price from Sales order by Item,Price Desc
Select Item,Price from Sales order by 1
-------Based on column Number -------
Select * from Sales order by 4

============================================================================

Left Outer Join

Create SALES Table
Create table Sales (ID int,Item varchar(15), Price int, Quantity int)
Insert values into Table
insert into Sales values(10,'Apple',15,120)
insert into Sales values(11,'Orange',25,20)
insert into Sales values(12,'Papaya',35,520)
insert into Sales values(13,'Grapes',45,320)
insert into Sales values(14,'Pineapple',40,250)
insert into Sales values(18,'Banana',22,470)
Create LOCATION Table and INSERT values
Create table Location (ID int,Zone varchar(15))
Insert into Location values(10,'East')
Insert into Location values(11,'West')
Insert into Location values(12,'North')
Insert into Location values(13,'South')
Insert into Location values(15,'North East')
Insert into Location values(16,'South West')
LEFT OUTER JOIN Select sales.ID,Sales.Item,Sales.Price, Location.Zone from Sales left outer join Location on Sales.ID = Location.ID

============================================================================

LIKE Operator -- in SQL

AND Operator -- in SQL

---Records having ID greater than 11 and Less than 14
Select ID from Sales where ID > 11 and ID < 14
---Records having Price is greater than 25 and less than 40
Select ID,Price from Sales where Price > 25 and Price < 40
---Quantity is greater than 40 and Less than 250
Select Quantity from Sales where Quantity > 40 and Quantity < 250
---ID is greater than 11 and Zone belongs to South
Select ID,Zone from Sales where ID > 11 and Zone = 'South'
--- Query by using all the columns
Select * from Sales where
ID > 11 and
Item = 'Papaya' and
Price > 20 and
Quantity > 300 and
Zone = 'East'
---Records having ID greater than 12 or Zone belongs to South
Select ID,Zone from Sales where ID > 12 or zone = 'south'
--- Query by using all the columns ----------------
Select * from Sales where
ID > 15 or
Item = 'Apple' or
price > 22 or
Quantity > 500 or
Zone = 'East'
---------------------In Operator------
Select ID,Item from Sales where item in('Apple','Papaya','Grapes')
---In operator on Multiple columns
Select ID,Item,Zone from Sales where item in('Apple','Papaya','Grapes') or zone in('North','West')
---Not in Operator
Select ID,Item from Sales where item not in('Apple','Papaya','Grapes')
-----------Between and Not between Operators
----Between Operator Lower bound to Upper
Select ID,Item,Price from Sales where ID between 12 and 17
----Between Operator Upper bound to Lower
Select ID,Item,Price from Sales where ID between 17 and 12
----Not Between Operator
Select ID,Item,Price from Sales where ID not between 12 and 17
--Item starts with A
Select Item from Sales where Item like 'A%'
--Item ends with E
Select Item from Sales where Item like '%E'
--Second character is A
Select Item from Sales where Item like '_A%'
--Item starts From A to G
Select Item from Sales where Item like '[A-G]%'
--Item doesn't starts from A to G
Select Item from Sales where Item like '[^A-G]%'
--Item starts with either A or O Select Item from Sales where Item like '[AO]%'

============================================================================

SET Operators -- in SQL

Create Tables Create table S2020 (ID int,Item varchar(15), Price int)
Create table S2021 (ID int,Item varchar(15), Price int,Zone varchar(15))
---insert values insert into S2020 values(10,'Apple',15)
insert into S2020 values(11,'Orange',25)
insert into S2020 values(12,'Papaya',35)
insert into S2020 values(10,'Apple',15)
insert into S2021 values(13,'Grapes',45,'North')
insert into S2021 values(14,'Orange',40,'South')
insert into S2021 values(15,'Banana',22,'East' )

----use star when tables are having different columns\size Select * from S2020
union
Select * from S2021

----interchange the columns and apply different data types Select Item,Price from S2020
union
Select Price,Item from S2021

Select Price,Item from S2020
union
Select Price,Item from S2021
----Union --- Duplicates on multiple columns
Select Price,Item from S2020
union
Select Price,Item from S2021
----Union ----Duplicates on single column
Select Item from S2020
union
Select Item from S2021

----Union All
Select Item from S2020
union all
Select Item from S2021

----Intersect
Select Price,Item from S2020
intersect
Select Price,Item from S2021

----Except
Select Price,Item from S2020
Except
Select Price,Item from S2021

Select Price,Item from S2021
Except
Select Price,Item from S2020

============================================================================

TOP Clause Examples -- in SQL

Select Top 2 Price,Id,Zone from Sales
Select top 50 percent * from emp

============================================================================

Copy the data from one table to another -- in SQL

Copy by creating new table only schema
Select * into Newtable from sales where 1 = 0
copy by creating new table along with data
Select * into newt from Sales where 1 = 1

============================================================================

COMMON EXPRESSION TABLE using WITH clause -- in SQL

with CTE as ( Select Item,Price from Sales ) Select * from CTE where Item in('Grapes','Apple')

============================================================================

Find 2nd heighest using WITH clause -- in SQL by creating COMMON EXPRESSION TABLE

with CTE as( Select Item,Zone,Price,DENSE_RANK() over(order by price) as Rnk from Sales) Select * from CTE where rnk = 2

============================================================================

Rank and Dense rank functions

consists of OVER and ORDER BY clause

Select Item,Dense_Rank() over(Order by Price asc) as Dnsrnk, Rank() over(Order by Price asc) as rnk from Sales

============================================================================

PARTITION BY clause

Partition clause consists of Aggregate function, Over clause

Select Id,Item,Qty,Zone,max(Qty) over(partition by Zone) as ma from Sales

============================================================================

STORED PROCEDURES

--Create Procedure Procedure name --alter procedure Procedure name

Create procedure SalesData
as
Select * from Sales where Zone = 'East'

Exec SalesData

============================================================================

Create Procedure with varibales

Create Procedure Sales1
@@ZoneName Varchar(15)
as
Select * from Sales where Zone = @@ZoneName
Exec Sales1 'East'
Drop procedure Sales1
drop procedure SalesData

============================================================================

VIEW - virtual Table

Create View
Create View [abcde] as Select * from Sales
Use View
Select * from abcde where item in('Apple','Grapes')
Drop View
drop view abcde

============================================================================

time and date functions in SQL

Select GETDATE()
Select Year(getdate())
Select Month(getdate())
Select day(getdate())
-Select datepart(interval,date)
Select datepart(YY,getdate())
Select datename(mm,getdate())
Select datename(DW,getdate())
Select datename(DD,getdate())
--day of years in digits
Select datename(DY,getdate())
-- week of the year in digits
Select datename(WW,getdate())
-- Quarter of the year in digits
Select datename(QQ,getdate())
Select DATEADD(dd,11,getdate())
Select DATEDIFF(DD,GETDATE(),'20-April-2023')

============================================================================

STRING functions in SQL

Select Subject, Upper(Subject) as UpperCase from Marks

Select Subject, Lower(Subject) as LowerCase from Marks

Select Subject, Len(Subject) as LenOfmarks from Marks

Select Subject, left(Subject, 2) as LeftfunctionData from Marks

Select Subject, Right(Subject, 2) as RightFunctionData from Marks

Select Subject, reverse(Subject) as ReverseFunctionData from Marks

Select Subject, substring(subject,2,2) as PartOfString from Marks

Select Subject, charindex('En', subject) as PostionOfString from Marks

Select Subject, REPLACE(Subject, 's', 'sss') as ReplaceData from Marks

Select Subject, space(Left(Subject,3) + space(5) + right(Subject,2)) as AddSpace from Marks

Select Subject, Ltrim(subject) as RemoveSpaceFromLeft from Marks

Select Subject, Rtrim(subject) as RemoveSpaceFromRight from Marks

Select Subject, Replicate(Subject,3) as ReplicateSub from Marks

============================================================================

Numeric and Math functions in SQL

Select Subject,abs(Marks) as absoluteMarks from Marks

Select Subject,power(5,2) as PowerValue from Marks

Select Subject,marks,SQRT(Marks) as SQRTValue from Marks

Select Subject,marks,Square(Marks) as SquareValue from Marks

Select Subject,marks,round(SQRT(Marks),0) as RoundValue from Marks

Select Marks,CEILING(SQRT(Marks)) as NearesIncrementalValue from Marks

Select Marks,Floor(SQRT(Marks)) as NearesDecrementalValue from Marks

Select count(marks) from Marks

Select count(*) from Marks

Select COUNT_BIG(*) from Marks

============================================================================

Remove duplicates using GROUP BY CLAUSE in SQL

Select Item,Price,Qty from Sales group by Item,Price,Qty
Select Item from Sales group by Item
Select Item,Qty from Sales group by Item,Qty

============================================================================

Find the duplicates\Unique records count using GROUP BY and Having CLAUSE in SQL

Create Employee table Create table Employee (id int, Name varchar(11),salary int, DepartmentID int)
Insert values into table insert into Employee values(1,'abc',25000,20)
insert into Employee values(2,'mno',28000,20)
insert into Employee values(1,'abc',25000,20)
insert into Employee values(3,'pqr',32000,35)
insert into Employee values(4,'xyz',27000,25)
insert into Employee values(1,'abc',25000,20)
insert into Employee values(5,'zzz',40000,35)
Retrieve the count of DUPLICATES
SELECT id, name, Salary, COUNT(*) as duplicate_count FROM employee GROUP BY id, name, Salary HAVING COUNT(*) > 1
retrieve the count of UNIQUE records
SELECT id, name, Salary, COUNT(*) as duplicate_count FROM employee GROUP BY id, name, Salary HAVING COUNT(*) = 1

============================================================================

Row_Number() Function

Add row number:

Provide Row number
Select ROW_NUMBER() over (order by zone) as rownumber from Sales
Provide row number for each partition
SELECT Price,Qty, ROW_NUMBER() OVER(Partition by Item ORDER BY Item) AS Row_Number from Sales

============================================================================

Row_Number() Function - extract required number of rows

WITH cte AS (
SELECT
*,ROW_NUMBER() OVER(ORDER BY Item) row_num
FROM Sales)
SELECT Item,Price FROM cte
WHERE row_num >= 1 AND row_num <= 5;

============================================================================

Common Table Expression

Partition By Clause

Partition by clause

Select Item, Price,Avg(Price) over(partition by Item) as AveragePrice from Sales

============================================================================

Left Outer Join

Select s.Id,s.Price,s.Zone,l.Locid,l.category from Sales s Left Outer Join Location L on s.Locid = L.Locid

============================================================================

Classification of Joins

Right Outer Join

Select s.Id,s.Price,s.Zone,l.Locid,l.category from Sales s Right Outer Join Location L on s.Locid = L.Locid

============================================================================

Offset and Fetch

Select Item,Price from sales Order by Price Desc Offset 0 rows fetch next 5 rows only

============================================================================

Case

Select CONCAT(ID, ' ' , Item), Price,
Case
when Price > 0 and Price < 500 then 'bad'
when Price > 501 and Price < 800 then 'good'
when Price > 801 then 'best'
end as Resultdata
from Sales Order by 1

============================================================================

IsNull and IsNotNull Operators

-----Is null Operator
Select * from sales where zone is null
-----Is not null Operator
Select * from sales where zone is not null