SQL Basic to Advanced Command

         SQL Basic to AdvanceD Command

 

 

 

 

Duplicate Delete command

 

SELECT*FROM EMPLOYEE

SELECT*FROM Employee ORDER BY First_Name;

with CTE_Employee AS

(select*,Row_number()over(partition by First_Name order by First_Name)

AS RN from Employee)

select*from CTE_Employee

Delete from CTE_Employee WHERE RN>1

Delete

delete Employee

where First_Name='Sonu'

Update Table

UPDATE Employee

set Department='Finance'

WHERE First_Name='sangeeta'

Between

select*from Employee

where salary between 30000 and 50000;

MIN MAX COUNT AVG

select MIN(age) from Employee;

SELECT MAX(SALARY) FROM Employee;

SELECT COUNT(*) FROM Employee WHERE AGE='F'

SELECT AVG(SALARY) FROM Employee

Add Table

 

Alter table Employee

add Department varchar(50);

View Columns Ascending

select*from Employee

ORDER by salary ASC;

View Columns Dscending

select*from Employee

ORDER by salary DESC;

 

 

TOP

select TOP 2 * FROM Employee

----------------------------------------------------

select TOP 2 * FROM Employee

WHERE Salary>=20000

 

SELECT TOP 3 * FROM EMPLOYEE ORDER BY SALARY DESC;

 

Group

select avg(salary) department From Employee

group by department;

-----------------------------------------------------------------------

How would you find 2^nd highest salary from this table

select max(salary) from Employee

where salary not in (select max(salary) from employee)

--------------------------------------------------------------------------

And Operator

select*from Employee

where First_Name='dk' and 

Last_Name='yadav' and Gender='M'

OR Operator

Select*from Employee

where gender='f' or salary=50000