Complicated queries

--*******Create Employee_Test Table ******
--Drop Table Employee
CREATE TABLE Employee
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee VALUES ('Anees',1000);
INSERT INTO Employee VALUES ('Rick',1200);
INSERT INTO Employee VALUES ('John',1100);
INSERT INTO Employee VALUES ('Stephen',1300);
INSERT INTO Employee VALUES ('Maria',1400);

--******Select All
Select * from employee

--*****Find Heighest Salary
Select Max(Emp_Sal) From Employee

--*****Find 3rd Heighest Salary
Select Min(Emp_Sal)
From Employee
Where Emp_Sal IN (Select Distinct top 3 Emp_Sal From Employee Order By Emp_Sal Desc)

--*******Find Nth Heighest Salary
Select Min(Emp_Sal) From Employee Where Emp_Sal IN (Select Distinct Top N Emp_Sal From Employee Order By Emp_Sal Desc)

--****Create another table
create table photo_test
(
pgm_main_Category_id int,
pgm_sub_category_id int,
file_path varchar(MAX)
)

insert into photo_test values
(17,15,'photo/bb1.jpg');    
                                               
insert into photo_test values(17,16,'photo/cricket1.jpg');                                                  
insert into photo_test values(17,17,'photo/base1.jpg');                                                      
insert into photo_test values(18,18,'photo/forest1.jpg');                                                      
insert into photo_test values(18,19,'photo/tree1.jpg');                                                          
insert into photo_test values(18,20,'photo/flower1.jpg');                                                    
insert into photo_test values(19,21,'photo/laptop1.jpg');                                                      
insert into photo_test values(19,22,'photo/camer1.jpg');                                                

insert into photo_test values(19,23,'photo/cybermbl1.jpg');                                                  
insert into photo_test values
(17,24,'photo/F1.jpg');

--*****select top 2 records from each group
select pgm_main_category_id,pgm_sub_category_id,file_path from
(
select pgm_main_category_id,pgm_sub_category_id,file_path,
rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid
from photo_test
) photo_test
where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.
order by pgm_main_category_id,pgm_sub_category_id

--*********
CREATE TABLE Employee_Test1
(
Emp_ID INT,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

--**********Deleting duplicate rows from a table
;with T as
(
select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
from employee_test1
)

delete
from T
where rank > 1


--***Date Functions
--Get the first day of the month

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

---*****SCHEMA LEVEL QUERIES****---------
select * from INFORMATION_SCHEMA.TABLES
SELECT * FROM ORDERS
SELECT * FROM EMPLOYEE
SELECT * FROM photo_test
SELECT * FROM Employee_Test1
SELECT * FROM sysdiagrams
SELECT * FROM ImageUpload
SELECT * FROM Customer
---*****SCHEMA LEVEL QUERIES****---------