Example of sql query

Consider the relations given below:

EMPLOYEE (EmployeeID, EmployeeName, Street, City)

COMPANY (CompanyID, CompanyName, City)

WORKS (EmployeeID, CompanyID, Salary)

MANAGES (EmployeeID, ManagerID)

CREATE TABLE EMPLOYEEE

(

EmployeeID INT PRIMARY KEY,

EmployeeName VARCHAR(15),

Street VARCHAR(15),

City VARCHAR(15)

)

CREATE TABLE COMPANY

(

CompanyID INT PRIMARY KEY,

CompanyName VARCHAR(15),

City VARCHAR(15)

)

CREATE TABLE WORKS

(

EmployeeID INT REFERENCES EMPLOYEEE(EmployeeID),

CompanyID INT REFERENCES COMPANY(CompanyID),

Salary MONEY

)

CREATE TABLE MANAGERS

(

 ManagerID INT,

EmployeeID INT REFERENCES EMPLOYEEE(EmployeeID)

)

INSERT INTO COMPANY VALUES(6,'TechMahindra','Delhi')

INSERT INTO COMPANY Values(7,'TCS','pune')                  

INSERT EMPLOYEEE VALUES(10,'mithilesh','SrNagar','hyd'),

                       (11,'sony','Ameerpet','hyd'),

        (12,'rupesh','madhopur','bbsr'),

        (13,'Priyanka','Cuttack','chenai'),

                      (14,'Anita','Balesore','Banglore'),

        (15,'Ramu','Begampet','hyd'),

        (16,'Sonu','Madhabpur','hyd'),

                      (17,'mohan','AhmadaBad','Delhi'),

        (18,'Subashis','Baripada','Banglore'),

        (19,'Asish','Ameerpet','hyd')

insert into  works(EmployeeID,CompanyID,Salary) values(10,6,25000),

                   (11,7,13000)    

insert managers values(100,10),

                      (101,12),

       (102,13)

select * from company

select * from EMPLOYEEE

select * from works

select * from managers 

I. Create the above relations using your own data types and print the structure of each relation. 
II. Insert at least 10 records in the relation EMPLOYEE and 5 records in the relation 
COMPANY. Insert appropriate records in the relations WORKS and MANAGES. 
III. Print the contents of each relation. 
IV. Give an expression in SQL with output for each of the following queries:  

1. Find the names of all employees who work for First Bank Corporation. 
Ans:

select EmployeeName from EMPLOYEEE where EmployeeID IN(select EmployeeID from WORKS where CompanyID=1)

2. Find the names and cities of residence of all employees who work for the First Bank Corporation. 
Ans:

select EmployeeName, City from EMPLOYEE where EmployeeID IN(select EmployeeID from WORKS where CompanyID=1) 

3. Find the names, street, and cities of residence of all employees who work for First Bank Corporation and earn more than Rs. 10,000/-.
 Ans:

SELECT EmployeeName,City FROM EMPLOYEEE WHERE   EmployeeID IN 

(SELECT EmployeeID FROM WORKS WHERE CompanyID=1 AND Salary>10000)

4. Find the employees who live in the same cities as the companies for which they work. 
Ans:

SELECT * FROM EMPLOYEEE WHERE City IN

(SELECT City FROM COMPANY WHERE CompanyID IN

(SELECT CompanyID FROM WORKS WHERE CompanyID

IN (SELECT EmployeeID FROM EMPLOYEEE))) 

5. Find all employees who do not work for First Bank Corporation.
 Ans:

SELECT * FROM EMPLOYEEE WHERE EmployeeID IN

(SELECT EmployeeID FROM WORKS WHERE CompanyID != 1) 

6. Find all employees who earn more than every employee of Small Bank 
Corporation.
Ans:

SELECT EmployeeID,EmployeeName,Street,City FROM EMPLOYEEE WHERE EmployeeID IN 

(SELECT EmployeeID FROM WORKS WHERE Salary>(SELECT MAX(Salary)MAXSAL 

FROM WORKS WHERE CompanyID=1))

7. Find all companies located in every city in which Small Bank Corporation is located.  
Ans:

SELECT CompanyName FROM COMPANY WHERE City IN 

(SELECT City FROM COMPANY WHERE CompanyName='SmallBank') 

8. Find all employees who earn more than the average salary of all employees of their company.
Ans:

SELECT EmployeeName FROM EMPLOYEEE WHERE EmployeeID IN 

(SELECT EmployeeID FROM WORKS WHERE Salary>(SELECT AVG(Salary) FROM WORKS)) 

9. Find the company that has the most employees. 
Ans:

SELECT TOP 1 COMPANY.CompanyName,TOTALEMPLOYE=COUNT(*) FROM EMPLOYEE 

INNER JOIN WORKS ON EMPLOYEEE.EmployeeID=WORKS.EmployeeID 

INNER JOIN COMPANY ON WORKS.CompanyID=COMPANY.CompanyID 

GROUP BY COMPANY.CompanyName ORDER BY COUNT(*) DESC 

10. Find the company that has the smallest payroll. 
Ans:

select CompanyName from COMPANY where CompanyId= 

(select CompanyId from works where salary=(select min(Salary) from works)) 

11. Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.
Ans:

select Works.CompanyId, EMPLOYEEE.EmployeeId,EMPLOYEEE.EmployeeName, 

EMPLOYEEE.Street,EMPLOYEEE.City,Works.Salary from EMPLOYEEE  

inner join works on EMPLOYEEE.EmployeeId=Works.EmployeeId inner join 

Company on Works.CompanyId=Company.CompanyId where Company.CompanyId!=1 

and Works.Salary>(select  Avg(Works.Salary) from Employeee inner join works 

on EMPLOYEEE.EmployeeId=Works.EmployeeId 

inner join Company on Works.CompanyId=Company.CompanyId where company.CompanyId=1 

or Company.CompanyName='firstbank' )