Consider the following database schema (primary keys are bolded and underlined):
ID: 3813690 • Letter: C
Question
Consider the following database schema (primary keys are bolded and underlined): Employes (eID: integer, eName: string, age: integer, salary: real) Works(eID:integer, dName string, started Date: date, ended date) Departments (dName: string budget real, managerEID: integer) The meaning of these relations is straightforward, for example, the table Works has one record per employee-department pair, such that an employee works for a department for a specific period of time. There are four foreign keys for this database. 1. the attribute eID of the relation Works that references the relation Employees. 2. the attribute dName of the relation Works that references the relation Departments. 3 Write the following queries in SQL. No duplicates should be printed in any of the answers, You should use the SQL keyword DISTINCT only when necessary. In addition, the creation of temporary tables is not allowed, i.e., for each question, you must write exactly one SQL statement. 1 Retrieve the name, age, and salary of all employees. 2. Retrieve the name, age, and salary of every employee who is older than 40. 3. For all departments with a budget greater than $500,000.00, print their manager name and budget. 4. Print the name and average salary of each department. 5. Find the name and salary of each employee who makes more than the highest salary of employees in the Information Technology department.Explanation / Answer
created below script to test your use-case:
create table employees(
eID INT(2) primary key,
eName varchar(300),
age int,
salary double
);
create table works(
eID INT(2) primary key,
dName varchar(200),
startedDate DATE,
ended DATE
);
create table Departemnts(
dName varchar(200) primary key,
budget double,
managerEID int
);
Insert into employees values(1, 'E1',40, 100);
Insert into employees values(2, 'E2',40, 200);
Insert into employees values(3, 'E3',22, 300);
Insert into employees values(4, 'E4',21, 400);
Insert into employees values(5, 'E5',27, 500);
Insert into employees values(6, 'E6',27, 500);
Insert into Departemnts values('D1', 4000, 4);
Insert into Departemnts values('D2', 57000, 5);
Insert into Departemnts values('Information Technology', 7000, 5);
Insert into works values(1, 'D1', null, null);
Insert into works values(2, 'D1', null, null);
Insert into works values(4, 'D2', null, null);
Insert into works values(3, 'D2', null, null);
Insert into works values(5, 'D1', null, null);
Insert into works values(6, 'Information Technology', null, null);
SQL queries for the Questions in you image:
1. select eName, age, salary from employees
2. select eName, age, salary from employees where age > 40.
3. select e.eName as Managername, d.budget
from employees e, departments d
where d.budget > 500000
and d.ManagerEID = e.eID
4. select d.dName, avg(e.salary)
from employees e, departments d, works w
where d.dName = w.dName
and w.eID = e.eID
group by d.dName;
5. select eName, salary
from employees
where salary > (select max(e.salary) from employees e, works w
where w.dName = 'Information Technology'
and w.eID = e.eID);