Scribd
Upload
10 views1 page

SQL Lab

The document contains a series of SQL queries related to employee and department data management. It includes operations such as counting employees by town, retrieving distinct manager names for salesmen, identifying employees without managers, counting employees in different towns, and filtering employees based on hire dates. Additionally, it includes update and delete operations for employees hired before a specific date.

Uploaded by

gokumsan0
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
10 views1 page

SQL Lab

The document contains a series of SQL queries related to employee and department data management. It includes operations such as counting employees by town, retrieving distinct manager names for salesmen, identifying employees without managers, counting employees in different towns, and filtering employees based on hire dates. Additionally, it includes update and delete operations for employees hired before a specific date.

Uploaded by

gokumsan0
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

1) SELECT [Link] AS Town, COUNT([Link]) AS Employee_Count, AVG(e.

esalary) AS
Average_Salary
FROM EMPLOYEE e
JOIN DEPARTMENT d ON [Link] = [Link]
GROUP BY [Link];

2) SELECT DISTINCT [Link] AS Manager_Name


FROM EMPLOYEE e
JOIN EMPLOYEE m ON [Link] = [Link]
WHERE [Link] = 'Salesman';

3) SELECT [Link] AS Employee_Name


FROM EMPLOYEE e
WHERE [Link] NOT IN (SELECT DISTINCT emgr FROM EMPLOYEE WHERE emgr IS NOT NULL);

4) SELECT COUNT([Link]) AS Employees_In_Different_Towns


FROM EMPLOYEE e
JOIN DEPARTMENT ed ON [Link] = [Link]
JOIN EMPLOYEE m ON [Link] = [Link]
JOIN DEPARTMENT md ON [Link] = [Link]
WHERE [Link] != [Link];

5) SELECT eno, ename, ejob, ehiredate


FROM EMPLOYEE
WHERE ehiredate < '2003-01-01';

UPDATE EMPLOYEE e1
JOIN (SELECT eno FROM EMPLOYEE WHERE ehiredate < '2003-01-01') e2
ON [Link] = [Link]
SET [Link] = NULL;

DELETE FROM EMPLOYEE


WHERE ehiredate < '2003-01-01';

SELECT * FROM EMPLOYEE;

You might also like