Personnel, department, project and employee relationship schemes defined for a company database are given. Personnel (registryNo, name, surname, title, salary, address, gender, workingDate, managerRegistryNo, deptID) Department (ID, deptName, managerRegistryNo) Project (prjNo, projectName, projectLocation, deptID) Running (registryNo, prjNo, workingTime) Note: the managers of the employees are also employees and are associated with the managerRegistryNo. Write the queries below according to these schemes. It is important that the queries are running !!! 1-) List the employees in the 'Marketing' department with their name, surname and address. 2-) Create a view for each department that shows the department number, name, number of employees in the department, average salary and total salary. 3-) List the registration numbers, names and surnames of all employees working in projects numbered 2, 3 or 5.
Personnel, department, project and employee relationship schemes defined for a company
Personnel (registryNo, name, surname, title, salary, address, gender, workingDate, managerRegistryNo, deptID)
Department (ID, deptName, managerRegistryNo)
Project (prjNo, projectName, projectLocation, deptID)
Running (registryNo, prjNo, workingTime)
Note: the managers of the employees are also employees and are associated with the managerRegistryNo.
Write the queries below according to these schemes. It is important that the queries are running !!!
1-) List the employees in the 'Marketing' department with their name, surname and address.
2-) Create a view for each department that shows the department number, name, number of employees in the department, average salary and total salary.
3-) List the registration numbers, names and surnames of all employees working in projects numbered 2, 3 or 5.
4-) Create the list of all company employees, including the name, surname of the employee and the name and surname of their managers (if not, the name and surname of the manager will be blank).
5-) List the employees who have the same name as their manager, with their names and surnames.
6-) For each project where more than two people work, list the project number, project name and the number of employees on that project.
7-) Please list the projects that include people whose surname is "Kaya" as an employee or the manager of the department of the project with the surname "Sabancı".
8-) Show the FK's in each table and the tables they are related to.
9-) Write the SQL sentence that provides an 8% increase in the salaries of all employees working in the 'Internet Banking' project.
10-) List the names and total working hours of the projects that have been worked for more than 300 hours.
Step by step
Solved in 3 steps with 3 images
