--Q1 write a query to get all students details ?
select * from Students
--Q2 Write a query to get Sname and Marks for sid is 3 ?
select sname,marks from Students where sid =3
--Q3 write a query to get all details
--in wchick students marks are above 90?
select * from Students where marks>90
--Q4 write a query to get all details
--which students marks are below or equal 80 ?
select * from Students where marks<=85
--Q5 Write a Query to get details of students
--which student Sid is 4 and name is Anils ?
select * from students where sid =4 and sname='Anil'
--Note: And is must between two condition
--Q6 Write a Query to get details of students
--which sname is Manoj or Anil or Suma ?
select * from students
where sname='Manoj' or sname='Anil' or sname= 'Bro'
--Note: All the values or any one of these values are matching
select * from Students
select * from Students where sid=1 or sid=2 or sid=3 --- Normal Query
select * from students where sid in(1,2,3)--- Special Operator in
--Except Sid 1,2,3
select * from students where sid not in(1,2,3) --special operator Not In
select * from Students where sid<>1 And sid<>2 And sid<>3 ----> Normal Query
--SName 'Anil' or 'Manoj'
select * from Students where Sname in('Anil','Manoj')
select * from students where Sname ='Anil' or Sname='Manoj'
--SQL DATE FORM YYYY-MM--DD
Select * from Students where sid between 1 and 3
--NOte: Including 1 and 5 [ All 1 to 5 Records]
select * from Students where marks between 80 and 100
--[Link] a query to get students details which name starts with 'A' Char
Select * from Students where sname like 'A%'
--Q2 write a query to get students details whcih students name ends with 'A"
select * from Students where sname like '%A'
--Q3> Write a Query to get studetns details
--which students name starts with either A or F or S
select * from students where sname like '[AFS]%'
--Purpose:
--it is used to get the table records for specified column pattren matching Characters
--Not Like : Exactly reverse to like
--Q1. Write a Query to get students details
--which name should not start with 'A' char
select * from Students where sname Not like 'A%'
select * from Students where marks like '7%'
select * from Students where marks not like '7%'
select * from Students
insert into Students(sid,marks) values (6,89)
select * from students where sname is null
select * from Students where Sname is not null
---select len(Column Name) from table name
--Without Table
select len('Surya') -- 5
select * from emp
--With table
select ename,len(Ename) from emp
--Note: Ename -- column name
--without table
--select lower('String")
--With table
--select lower(column name) from table name
select lower ('SIVA')
Select lower (Ename),ename from emp
select * from emp
--Syntax:
--select upper('String')
select Upper('siva')
--Select Upper(Ename) from Emp
select upper (Ename),ename from emp
select proper('SIVA') -- NOT AVAILBALE
--First Letter
--left()
select left('siva',1)
select UPPER(left('siva',1))
--Rest of the letter:
--Right()
select right('siva',3)
select right ('siva',len('siva')-1)
select UPPER(left('siva',1))+ right ('siva',len('siva')-1)
--With Table
select len(ename), from emp
--first Character
--left
select upper(left(ename,1)), ename from emp
--last Characters
--Right function
select right(ename,len(ename)-1), ename from emp
select upper(left(ename,1))+ lower(right(ename,len(ename)-1)) from emp
--Reverse() : Left --- Right--- Left
--syntax:
--select Reverse('String')
--select Reverse(column) from Table name
Select Reverse('SIVA')
SELECT ename,reverse(ENAME) from emp
--Sub String()
--Syntax: Select substring('String, pos,length)
--Mid(Text,sn,noofcha)
--Pos: position--- Sn start number
select Substring('Hyderabad',1,5)
select substring (Ename,1,4),ename from emp
select upper( substring ('Srinivas',4,5))
--Replace()
--Syntax: select Replace ('String','oldchars','Newchars)
select REPLACE('SRINIVAS','NIVAS','RAM')
SELECT ename,replace(ename,'A','HA') from emp
--Purpose: it is used to get replaced string instead of old chars with newchars
-- this function returs 'string ' tryp
--Charindex()
--Excel--- Find(Ft,wt,sn) Seach()
--Syntax: select charindex('Char','String')
select CHARINDEX('a','Surya')
SELECT * FROM EMP
SELECT CHARINDEX('a','Surya') -- 5th position
select charindex('a','Saibaba') ---2
select charindex('a','Saibaba',3)---5th ---Statict
--nested Function:
select charindex('a','Saibaba',CHARINDEX('a','Saibaba')+1) ---5 Dynamic
--Note: if the letter is not availblae then Zero will be displayed
select charindex('x','Saibaba') ---0
--=FIND("a",E3,FIND("a",E3)+1)
--Note: Index: Occerance position
-- Note Find , Search in Excel
select Ename,CHARINDEX('s',ename) from emp
Replicate()
--Syntax() Select Replicate ('String', No of times)
select replicate ('Sri Rama',5)
select ename,replicate(Ename,3) from emp -- Without space
select ename,replicate(Ename+' ',3) from emp --- With space
--Left()
--Syntax: select left('string',No of chars)
select left('Srinivas',3)
--Right()
--Syntax: select Right('string',No of chars)
select right('Srinivas',3)
--concat()
--Syntax: select concat(Expr,Expr,..)
Select 'Sai'+ 'Kumar'
select 'Sai'+space(5)+'Kumar'
select concat('Sai','Kumar')
select concat('Sai',' ','Kumar')
select concat('Sai',space(5),'Kumar')
--ABS()
--Syntax: select abs(Number)
select abs(-20) ---20
--with table:
select SAL,abs(sal) from emp
--Sign()
--select sign(number)
select sign(-20) ---- -1
select sign(20) ---1
select Sal,sign(sal) from emp
Ceiling(number)
--syntax: select ceiling(number)
select ceiling (20.4) --- 21
select ceiling (20.1) --- 21
select ceiling (-20.4) --- -20
-- Note: -20 > -20.4
--With table
Select ceiling(sal) from emp
--Floor()
--Syntax: Select Floor(num)
select FLOOR(20.9) --- 20
select Sal,floor(sal) from emp
--Power()
--Syntax: select power(No1,No2)
select power(2,3)
--With table
select Sal,power(sal,2) from emp
--sqrt()
--syntax: select SQRT(Number)
select sqrt(169) ---8
--with table
select sal,sqrt(sal) from emp
--square()
--select SQUARE(Number)
select SQUARE(9)
--Round()
-- 2 arguments are required
-- select Round( Number, Precision)
select Round(20.567,2)
select ROUND(sal,2) from emp
--syntax: select getdate() --- No Arguments
select getdate() ---Complete Date
--Purpose: it is used to get current system date & time in sql server format
[YYYY-MM-ddd HH:MI:SS:Ms]
--Day()
--Syntax:
--select Day(getdate() or userdate())
select day(getdate())
select day('2017-05-19') --- user Date
--With column Name
select * from emp
select (hiredate) from emp
select hiredate,Day(hiredate) from emp
-- Month()
--Syntax: select Month(Getdate() or User Date)
select Month(getdate())
select month(getdate())
select HIredate,month(hiredate) from emp
--Year()
--Syntax: select year (getdate() or 'userdate')
select year(getdate())
select Hiredate,year(hiredate) from emp
Purpose: to get year value form given date
--Datepart()
--Syntax: Select datepart(intervals,Getdate() or 'userdate')
select DATEPART(Dw,Getdate())
select datepart(dw,'2018-01-01')
--with Table
select Hiredate,DATEPART(dw,hiredate) from emp
--Purpose: it is used to get date partician value for given date.
-- this function retursn integer
--DateName()
--Syntax: Select datename(interval,Getdaty() or 'userdate')
select datename(dw,getdate())
select hiredate,datename(dw,hiredate) from emp
select hiredate,DATEPART(dw,hiredate),datename(dw,hiredate) from emp
--Datediff()
--Syntax: select datediff(interval,startdate,enddate)
--Same for vba
--difference in Days
select datediff(DD,'2018-01-01',Getdate())
--Difference in years
Select hiredate,datediff(yy,hiredate,getdate()) from emp
--Excel
--select datedif(startdate, enddate, Interval)
--DateAdd()
--Syntax: select dateadd(Interval,Increment+ / Decrement -, datettime)
select dateadd(dd,30,getdate())
select dateadd(dd,-30,getdate())
select dateadd(mm,3,getdate())
select hiredate,dateadd(yy,10,hiredate)from emp
--IntQ
-- from today after 2 month same day is what week day
-- nested Function
select datename(dw,dateadd(mm,2,getdate()))
Date & Time Function
--1 Getdate()
-- Day()
-- Month()
-- Year()
-- datepart()
--Datename()
--DateDiff()
-- DateAdd()
--Ntervals:
select getdate()
select Cast(getdate() as varchar)
select cast(0 as datetime)
--Select Cast(Source value as Target type)
select cast(12.3 as int) --- Float to int
select cast('123' as int) --- Varchar to in
select getdate()
select cast (getdate() as varchar) ----> date to varchar
select cast(365 as datetime) --- int to date
select cast(getdate() as int) --- Date to int
select cast(45169 as datetime) --- int to date
select cast('2021-05-21' as int)
--Difference btw Cast & Convert
--Cast: First Source , next Target
--Convert First Target , next Source
select convert (Int,Getdate())
--Advantage: Date style :100-130 styles
select convert(varchar,Getdate(),100) --- Date & Time
select convert(varchar,getdate(),101)--- only Date --09/02/2023
select convert(varchar,getdate(),110)---09-02-2023
select convert(varchar,getdate(),108)--- only time
--Syntax: selet Convert(Target type , source expression,Style)
select convert(int,12.3)
select convert(varchar,getdate())
select convert(Varchar,123)
select convert(datetime,0)
select convert(int,getdate())
select convert(varchar,getdate(),101)-- only Date
select convert(varchar,getdate(),108)-- only time
-- How to check indentity values:
--Special function are there to check these
--1.Ident_Seed()
--Syntax: Select ident_seed('Table Name')
select ident_seed('S1') --- 100
--2.Ident_Incr()
--Syntax: Select ident_incr('Table Name')
select ident_incr('S1') --- 1
--3 Ident_Current()
--Syntax: Select ident_Current('Table Name')
select IDENT_CURRENT('S1') ---102
select * from s1
delete from s1 where Eno=101
insert into s1 values('KK'),('II'),('MM')
select ename from S1 where eno=101
select ename, sal, rank()over(order by sal Desc)as Rk from emp
select ename,sal,Rank() over(order by sal desc) from ESAL
select * from Students
select sname,marks,rank() over(order by marks desc) as rk from Students
select sname,marks,rank() over(order by marks asc) as rk from Students
select ename,sal, DENSE_RANK()over( order by sal desc) as rk from esal
select ename,sal, DENSE_RANK()over( order by sal asc) as rk from esal
select sname,marks,DENSE_RANK() over(order by marks desc) as RK from Students
select ename, sal, row_Number() over(order by sal desc) as Rk from esal
select sname,marks,row_Number() over(order by marks desc) as Rk from students
select * from emp
select max(sal) as Max_Sal from emp --- 5000
select min(Sal) as Min_Sal from emp ---800
select Sum(Sal) as Total_Sal from emp ---29025
select avg(Sal) as Avg_Sal from emp --2073
select count(Sal) as CNT from emp
select
max(Sal) as Max_Sal , Min(sal) as Min_Sal,
sum(sal) as Total_Sal,AVG(sal) as AVg_sal , count(sal) as CNT
FROM EMP
select *,count(Sal) as CNT from emp
select ename,max(sal) from emp
--Q1 Department wise Employee Count ?
select count(deptno) from emp where deptno =10
select count(deptno) from emp where deptno =20
select count(deptno) from emp where deptno =30
select deptno,count(deptno) as Emp_Count from emp group by DEPTNO
--Q2 Department wise emplyee count
--where in department employees are minimum >3
select deptno, count(deptno) as emp_count from emp group by deptno
having count(deptno)>3
--Q3
select
deptno, sum(sal) as Total_Sal from emp group by deptno
having sum(sal)>10000
select deptno, count(deptno) from emp
group by deptno having count(deptno)>=5
---Difference between Where & Having
--Having : we can apply the condition for groupd rows
--It is extended clasue of Group by
--Where: we can apply the condition for un groupd rows
--Where Clause
--it is a conditional clause
--it is applicable before group by
-- it is executed row by row --Row level
--group Functions are optional
-- In this Query * is allowed
-- First level of condition
--Having Clause
-- It is also a conditional clause
-- it is applicable after group by
-- it is executed on Grouped rows[Column] --Column Level
---Group Functions are mandatory
-- In this Query * is not allowed
-- Second level of condition
Select colname, Fn(col) from tableName Group by colName with rollup
select deptno, count(deptno) as Emp_Count from emp group by DEPTNO with rollup
select deptno,sum(sal) as Total_Sal from emp group by DEPTNO with Rollup
select
deptno,
sum(sal) as Total_Sal from emp
group by DEPTNO having sum(sal)>10000
with Rollup
Select * from emp
select empno,ename,sal from emp order by sal Asc ---Small to Big
select empno,ename,sal from emp order by sal Desc --- Big to Small
select empno,ename,sal from emp order by sal asc,ename desc
select empno,ename,sal from emp order by sal asc,ename asc ---Adv Sorting
select * from emp order by empno asc
select * from emp order by empno desc
select * from emp order by empno Asc,order by sal desc ---Error
---Note: no 2 order by are not allowed
select * from emp order by sal Asc
select * from emp order by sal Desc
--Group By | Having | Distinct ----- * is not allowed
--Order by allows *
select * from emp order by ename asc
select * from emp order by ename desc
--Select distinct column name from table name
select sal from emp
select distinct sal from emp
select distinct sal, deptno from emp
select sal from emp --- will get duplicates
select distinct sal from emp --- will not get duplicates [Unique]
Select * distinct sal from emp -- Error * is not allowed
select distinct sal, DEPTNO from emp
select distinct sal, distinct deptno from emp
select * from Branches
select * from Customers
delete from Branches where Bcode =10
delete from Customers where Bcode =10