SELECT * FROM Employee SELECT * FROM Project SELECT * FROM Works_for SELECT * FROM Departments SELECT * FROM Dependent --1. Display the Department id, name and id and the name of its manager. SELECT d.Dnum, d.Dname, e.SSN AS ManagerID, CONCAT(e.Fname,' ',e.Lname) AS ManagerName FROM Departments d JOIN Employee e ON d.MGRSSN = e.SSN --2. Display the name of the departments and the name of the projects under its control.

SELECT D.Dname , P.Pname FROM Departments D JOIN Project P ON D.Dnum = P.Dnum

--Display the full data about all dependents with the employee name SELECT d.* , CONCAT(e.Fname , ' ' , e.Lname) AS name FROM Dependent d JOIN Employee e ON d.ESSN = e.SSN

--Display the Id, name and location of the projects in Cairo or Alex SELECT Pnumber , Pname , Plocation FROM Project WHERE City = 'Cairo' or City= 'Alex'

--5. Display the Projects full data of the projects with a name starts with "a" letter. SELECT * FROM Project where pname like ('a%')

--6. display all the employees in department 30 whose salary from 1000 to 2000 LE monthly select * from Employee where Dno = 30 and Salary between 1000 and 2000

---7 SELECT CONCAT(FNAME,' ',LNAME) AS name ,hours from Employee e join Works_for w on e.ssn=w.essn join Project p on p.Pnumber=w.Pno where Dno = 10 and hours >= 10 and pname = 'AL Rabwah' group by fname, lname, Hours

--8. Find the names of the employees who directly supervised with Kamel Mohamed select CONCAT(FNAME,' ',LNAME) AS employees_name from Employee where Superssn =( select ssn from employee where fname= 'kamel' and lname='mohamed')

--9. Employees and the projects they work on (sorted by project name) SELECT CONCAT(e.Fname,' ',e.Lname) AS Name , p.Pname FROM Employee e JOIN Works_for w ON e.SSN = w.ESSN JOIN Project p ON w.Pno = p.Pnumber ORDER BY p.Pname

--10. For each project in Cairo: project number, department name, manager data

select p.pname , p.pnumber , d.dname ,e.lname,e.address , e.bdate from Project p join Departments d on d.dnum=p.Dnum join Employee e on e.SSN=d.MGRSSN where city ='cairo' group by pname , pnumber , dname ,lname, address , bdate

--11. Display All Data of the managers SELECT e.* FROM Employee e JOIN Departments d ON e.SSN = d.MGRSSN

--12. Display All Employees data and the data of their dependents even if they have no dependents

SELECT e., d. FROM Employee e LEFT JOIN Dependent d ON e.SSN = d.ESSN

--13 SELECT d.Dependent_name , d.Sex FROM Dependent d JOIN Employee e ON d.ESSN = e.SSN WHERE d.Sex='F' AND e.Sex='F' group by Dependent_name, d.Sex

UNION

SELECT d.Dependent_name , d.Sex FROM Dependent d JOIN Employee e ON d.ESSN = e.SSN WHERE d.Sex='M' AND e.Sex='M' group by Dependent_name, d.Sex

--14. Total hours per week spent on each project

SELECT p.Pname , SUM(w.Hours) AS TotalHours FROM Project p JOIN Works_for w ON p.Pnumber = w.Pno GROUP BY p.Pname

--15. Display the data of the department which has the smallest employee ID over all employees' ID.

SELECT d.* FROM Departments d JOIN Employee e ON d.Dnum = e.Dno WHERE e.SSN = ( SELECT MIN(SSN) FROM Employee WHERE Dno IS NOT NULL )

--16. Department salary statistics

SELECT d.Dname, MAX(e.Salary) AS MaxSalary, MIN(e.Salary) AS MinSalary, AVG(e.Salary) AS AvgSalary FROM Departments d JOIN Employee e ON d.Dnum = e.Dno GROUP BY d.Dname

--17. List the full name of all managers who have no dependents. SELECT CONCAT(e.Fname,' ',e.Lname) AS ManagerName FROM Employee e JOIN Departments d ON e.SSN = d.MGRSSN LEFT JOIN Dependent dep ON e.SSN = dep.ESSN WHERE dep.ESSN IS NULL

--18. Departments whose avg salary < company avg salary

SELECT d.Dnum, d.Dname, COUNT(e.SSN) AS NumEmployees FROM Departments d JOIN Employee e ON d.Dnum = e.Dno GROUP BY d.Dnum , d.Dname HAVING AVG(e.Salary) < ( SELECT AVG(Salary) FROM Employee )

--19. Employees and projects ordered by department then name

SELECT CONCAT(e.Fname,' ',e.Lname) AS EmployeeName, p.Pname, e.Dno FROM Employee e JOIN Works_for w ON e.SSN = w.ESSN JOIN Project p ON w.Pno = p.Pnumber ORDER BY e.Dno , e.Lname , e.Fname

--20. Try to get the max 2 salaries using subquery

SELECT DISTINCT Salary FROM Employee e1 WHERE 2 > ( SELECT COUNT(DISTINCT Salary) FROM Employee e2 WHERE e2.Salary > e1.Salary )

--21. Get the full name of employees that is similar to any dependent name

SELECT CONCAT(Fname,' ',Lname) AS EmployeeName FROM Employee WHERE Fname IN( SELECT Dependent_name FROM Dependent )

SELECT e.Fname , d.Dependent_name FROM Employee e JOIN Dependent d ON e.Fname = d.Dependent_name

--22. Employees who have dependents

SELECT SSN , CONCAT(Fname,' ',Lname) AS EmployeeName FROM Employee e WHERE EXISTS( SELECT * FROM Dependent d WHERE d.ESSN = e.SSN )