SQL DML/DDL There are five tables describing employees of a company, departments, buildings, which department(s) an employee works in (and a percentage of the time for each), and in which building an employee works (an employee may have more than one office). The primary key of each table is the attribute(s) in capitals and underlined. The foreign keys are in italics. Other attributes are not necessarily unique. Employee (EID, Ename, Salary, Start_Date, End_Date) Building (BID, Bname, Address) Department (DID, Dname, Annual_Budget) In_Department (EID, DID, Percentage_Time) In_Building (EID, BID) Write the SQL statements for the following. 1. Find the names of Departments where NO employees work. 2. For employees who are still working in the company, end date has no value (empty). For those who have left the company and have end date values, show their names and the duration of their employment in years. 3. Find the names of buildings where more than 50 employees work. 4. The tables have been created. The company then decides that the maximum annual budget for a department is 100000. Write the SQL statement that adds the constraint to limit the value that can be placed in the column. 5. Find, for each department, the number of employees and their total salaries as in the expected output. Expected Output: Dname NoOfEmployees TotalSalary Development 2 10000 Research 3 12000 Sales 2 8000 6. Find the name of the Department(s) where the highest paid employee works.
SQL DML/DDL
There are five tables describing employees of a company, departments, buildings, which department(s) an employee works in (and a percentage of the time for each), and in which building an employee works (an employee may have more than one office). The primary key of each table is the attribute(s) in capitals and underlined. The foreign keys are in italics. Other attributes are not necessarily unique.
Employee (EID, Ename, Salary, Start_Date, End_Date)
Building (BID, Bname, Address)
Department (DID, Dname, Annual_Budget)
In_Department (EID, DID, Percentage_Time)
In_Building (EID, BID)
Write the SQL statements for the following.
1. Find the names of Departments where NO employees work.
2. For employees who are still working in the company, end date has no value (empty). For those who have left the company and have end date values, show their names and the duration of their employment in years.
3. Find the names of buildings where more than 50 employees work.
4. The tables have been created. The company then decides that the maximum annual budget for a department is 100000. Write the SQL statement that adds the constraint to limit the value that can be placed in the column.
5. Find, for each department, the number of employees and their total salaries as in the expected output.
Expected Output:
Dname
NoOfEmployees
TotalSalary
Development
2
10000
Research
3
12000
Sales
2
8000
6. Find the name of the Department(s) where the highest paid employee works.
Trending now
This is a popular solution!
Step by step
Solved in 7 steps with 7 images
