COMPANY DATABASE NAVATHE BOOK

  COMPANY DATABASE  NAVATHE BOOK


Questions to be performed on above schema

A. Create tables with relevant foreign key constraints -- CLICK HERE

B. Populate the tables with data - CLICK HERE

C. Perform the following queries on the database :

1. Display all the details of all employees working in the company.  CLICK HERE
2. Display ssn, lname, fname, address of employees who work in department no 7. - CLICK HERE
3. Retrieve the birthdate and address of the employee whose name is 'Franklin T. Wong'.CLICK HERE
4. Retrieve the name and salary of every employee. CLICK HERE
5. Retrieve all distinct salary values. CLICK HERE
6. Retrieve all employee names whose address is in „Bellaire‟.  CLICK HERE
7. Retrieve all employees who were born during the 1950s. CLICK HERE
8. Retrieve all employees in department 5 whose salary is between 50,000 and 60,000(inclusive) CLICK HERE 
9. Retrieve the names of all employees who do not have supervisors. CLICK HERE
10. Retrieve SSN and department name for all employees.  CLICK HERE
11. Retrieve the name and address of all employees who work for the 'Research' department. CLICK HERE
12. For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate. CLICK HERE
13. For each employee, retrieve the employee's name, and the name of his or her immediate supervisor. CLICK HERE
14. Retrieve all combinations of Employee Name and Department Name. CLICK HERE
15. Make a list of all project numbers for projects that involve an employee whose last name is 'Narayan‟ either as a worker or as a manager of the department that controls the project. CLICKHERE
16. Increase the salary of all employees working on the 'ProductX' project by 15%. Retrieve employee name and increased salary of these employees. CLICK HERE
17. Retrieve a list of employees and the project name each works in, ordered by the employee's department, and within each department ordered alphabetically by employee first name.CLICK HERE 
18. Select the names of employees whose salary does not match with salary of any employee in department 10. - CLICK HERE
19. Retrieve the name of each employee who has a dependent with the same first
name and same sex as the employee. CLICK HERE
20. Retrieve the employee numbers of all employees who work on project located in Bellaire, Houston, or Stafford. CLICK HERE
21. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary. Display with proper headings. - CLICK HERE
22. Find the sum of the salaries and number of employees of all employees of the „Marketing‟ department, as well as the maximum salary, the minimum salary, and the average salary in this department. CLICK HERE
23. Select the names of employees whose salary is greater than the average salary of all employees in department 10. CLICK HERE
24. For each department, retrieve the department number, the number of employees in the department, and their average salary. CLICK HERE
25. For each project, retrieve the project number, the project name, and the number of employees who work on that project. CLIK HERE
26. Change the location and controlling department number for all projects having more than 5 employees to „Bellaire‟ and 6 respectively. CLICK HERE
27. For each department having more than 10 employees, retrieve the department no, no of employees drawing more than 40,000 as salary. CLICK HERE
28. Insert a record in Project table which violates referential integrity constraint with respect to Department number. Now remove the violation by making necessary
insertion in the Department table. CLICK HERE(homework)
29. Delete all dependents of employee whose ssn is „123456789‟.CLICK HERE
30. Delete an employee from Employee table with ssn = „12345‟( make sure that this employee has some dependents, is working on some project, is a manager of some department and is supervising some employees). Check and display the cascading effect on Dependent and Works on table. In Department table MGRSSN should be set to default value and in Employee table SUPERSSN should be set to NULL-CLICK HERE(homework)
31. Perform a query using alter command to drop/add field and a constraint in Employee table. CLICK HERE