The following questions are base on the schema. Database Schema: Setup the datab
ID: 3681634 • Letter: T
Question
The following questions are base on the schema. Database Schema: Setup the database and input the data. You must implement the referential constraints if necessary. Complete the following queries: 1. Retrieve the birthdate and address of the employee whose name is 'John B. Smith'. 2. Retrieve the name and address of all employees who work for the 'Research' department. 3. For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate. 4. For each employee, retrieve the employee's first and last name and the first and last name of the his or her immediate supervisor. 5. Select all of combinations of EMPLOYEE SSN and DEPARTMENT DNAME in the database. 6. Retrieve the salary of every employee. 7. Make a list of all project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project. 8. Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee. 9. Retrieve the name of employees who have no dependents. 10. List the name of managers who have at least one dependent. 11. Retrieve the names of all employees who do not have supervisors. 12. Find the sum of the salaries of all employees of the 'Research' department, as well as the maximum salary, the minimum salary, and the average salary in this department. 13. Count the number of distinct salary values in the database. 14. Retrieve the names of all employees who have two or more dependents. 15. For each department, retrieve the department number, the number of employees in the department, and their average salary. 16. For each project, retrieve the project number, the project name, the number of employees who work on that project. 17. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on that project. 18. For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project. 19. Retrieve all employees whose address is in Houston, Texas. 20. Find all employees who were born during the 1950s. I need the answers for this practice assignment. professor wants to see it if I did it correctly.
Explanation / Answer
1)Select bdate,address
from employee
where fname='John' and minit="B"
and lname="Smith";
2)select fname,lname,address
from employee,department
where dnumber=dno and
dname='Research';
3)select pnumber,dnum,lname,address,bdate
from project,department,employee
where dnum=dnumber and mgrssn=ssn and plocation='Stafford';
4)select e.fname,e.lname,s.fname,s.lname
from employee as e, employee as s
where e.superssn = s.ssn;
5)select ssn,dname
from employee,department;
65)select distinct salary from employee;
7)(select distinct pno
from project,department,employee
where dnum=dnumber and mgrssn=ssn and lname='Smith')
union
(select pno
from project,works_on,employee
where pnumber=pno and essn=ssn and lname='Smith');
8)select e.fname,e.lname
from employee as e
where e.ssn in (select essn
from dependent
where e.fname=dependent_name and e.sex=sex);
9)select fname,lname
from employee
where not exists (select * from dependent where ssn=essn);
10)select fname,lname
from employee
where exists (select * from department where ssn=mgrssn) and
exists (select * from dependent where ssn=essn);
11)select fname, lname from employee where superssn is null;
12)select sum(salary) as [sum(salary)], max(salary) as [max(salary)],
min(salary) as [min(salary)], avg(salary) as [avg(salary)]
from employee, department
where dno=dnumber and dname='Research';
13)select count(distinct salary) as [no of distinch salary]
from employee;
14)select lname,fname
from employee
where (select count(*) from dependent where ssn=essn) >=2;
15)select dno,count(*)as [no of employees],avg(salary)as [average salary]
from employee, department
where dno = dnumber
group by dname;
16)select pnumber,pname,count(*)as [no of employees]
from project,works_on
where pnumber=pno
group by pnumber,pname;
17)select pnumber,pname,count(*)as [no of employees]
from project,works_on
where pnumber=pno
group by pnumber,pname
having count(*)>2;
18)select pnumber,pname,count(*) as [no of employees]
from project,works_on,employee
where pnumber=pno and ssn=essn and dno=5
group by pnumber,pname;
19)select fname,lname
from employee
where address like '%Houston, TX%';
20)select fname,lname
from employee
where bdate between '1950-01-01' and '1959-12-31';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.