CS SE 6360 002 Assignment 2 Due Date February 20 2017 11 59 PM Part 1 1 Create all the tables given in following schema Define required constraints on given tables Define triggered actions that will be attached to each foreign key constraint Part 2 1 Write following queries in SQL a For each department whose average employee salary is more than 30 000 retrieve the department name and the number of employees working for that department b Same as a except output the number of male employees instead of the number of employees c Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees d Retrieve the names of employees who make at least 10 000 more than the employee who is paid the least in the company e Retrieve the names of employees who is making least in their departments and have more than one dependent solve using correlated nested queries 2 Specify following views in SQL Solve questions using correlated nested queries except a a A view that has the department name manager name and manager salary for every department b A view that has the department name its manager s name number of employees working in that department and the number of projects controlled by that department for each department c A view that has the project name controlling department name number of employees working on the project and the total hours per week they work on the project for each project d A view that has the project name controlling department name number of employees and total hours worked per week on the project for each project with more than one employee working on it e A view that has the employee name employee salary department that the employee works in department manager name manager salary and average salary for the department How to connect to Oracle server Connect to the Oracle Database server using SQL Developer program with following parameters User Name yournetid Password yournetid Host Name onecore myds me Port 1521 SID xe You can download SQL Developer from the following link http www oracle com technetwork developer tools sql developer downloads index html Deliverables 1 sql or pdf file that includes all SQL statements for Part 1 and Part 2 2 SQL statements for Part 1 should be executed on Oracle database Thus you will have COMPANY database tables created on your account
View Full Document
Unlocking...