Question

B) DML and SQL Query
For this DB Schema, answer the below questions:
Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
Department (Dname, Dnumber, Mgr_ssn, Mgr_start_date)

5. Delete from Employee table, those who have First name: James.
6. Delete from Employee table, those who have Salary more than 4000.
7. What happens if you try to delete one department?
8. Retrieve the data of all employee.
9. Retrieve the data of all male employee.
10. Retrieve the Fname, Lname of all male employee.
11. Retrieve the Fname, Lname and Salary of all employee
12. Retrieve the Fname, Lname and Salary of all employee with Salary more than 4000
13. Retrieve the Fname, Lname and Salary of all female employee with Salary more than 4000
14. Retrieve the Fname, Lname and Address of all female employee with Salary more than 4000 and Birthdate after 1-1-1990.
15. Retrieve the Address and Salary of all employee with Fname = John or James.
16. Retrieve the Address and Salary of all employee with Fname = John or Birthdate after 1-1-1990
17. Retrieve the Name and Number of all Departments
18. Retrieve the Name and Number of all Departments with Manager SSN=123456
19. Retrieve the Employee Fname, Lnameand Department number for all employees.
20. Retrieve the Employee Fname, Lnameand Department number and Department Name for all employees.
21. Retrieve the Employee Fname, Lnameand who works in Department name = Research
22. Retrieve the Employee Fname, Lnameand who works in Department name = Research and IT
23. Retrieve the Employee Fname, Lnameand who works in Department name = Research OR IT
24. Retrieve the Department Name ,Number , Manager SSN
25. Retrieve the Department Name ,Number , Manager SSN, Manager FName
26. Retrieve the Department Name, Manager SSN, Manager FName for departments with Manager Start Date > 1-1-2010
please write it in computer word not pin and thank you

Answer 1

Hi, hope you are doing good.

5) DELETE FROM Employee WHERE Fname='James';

6) DELETE FROM Employee WHERE Salary>4000;

7) When we delete one tuple/row from Department table then entry in Employee table for that Dno changed to NULL or deleted.

8) SELECT * FROM Employee;

9) SELECT * FROM Employee WHERE Sex='Male';

10) SELECT Fname, Lname FROM Employee WHERE Sex='Male';

11) SELECT Fname, Lname, Salary From Employee;

12) SELECT Fname, Lname, Salary From Employee WHERE Salary>4000;

13) SELECT Fname, Lname, Salary From Employee WHERE Sex='Female' ^ Salary>4000;

14) SELECT Fname, Lname, Salary From Employee WHERE Salary>4000^Sex='Female'^Bdate>('1-1-1990');

15) SELECT Address, Salary FROM Employee WHERE Fname IN ('John','James');

16) SELECT Address, Salary FROM Employee WHERE Fname='John' Bdate >('1-1-1990');

17) SELECT Dname, Dnumber FROM Department;

18) SELECT Dname, Dnumber FROM Department WHERE Mgr_ssn='123456';

19) SELECT Fname, Lname, Dnumber From Employee, Department;

20) SELECT Fname, Lname, Dnumber, Dname From Employee, Department;

21) SELECT Fname, Lname, Dnumber FROM Department Where Dname ='Research';

22) SELECT Fname, Lname, Dnumber FROM Department Where Dname ='Research'^Dname='IT';

23) SELECT Fname, Lname, Dnumber FROM Department Where Dname IN('Research', 'IT');

24) SELECT Dname, Dnumber, Mgr_ssn FROM Department ;

25) SELECT Dname, Dnumber, Mgr_ssn, Fname From Employee, Department WHERE Department.Mgr_ssn= Employee.ssn;

26) SELECT Dname, Dnumber, Mgr_ssn, Fname From Employee, Department WHERE Department.Mgr_ssn= Employee.ssn ^ Mgr_start_date>'1-1-2010';

Note: If you have any query please let me know in the comment. Have a nice day!