Need Help With THese two Sql Querries I am using sqlite studio here are table an

Question

Need Help With THese two Sql Querries I am using sqlite studio here are table and question

Q1- Display the manager of the employee with the oldest project start date.
Q2- List the average cost of projects for employees who weakness is "no super power" and the average cost of projects for all other employees, create a new column that labels each total (use UNION ALL).

create table DEPARTMENT ( department char(24) NOT NULL, manager char(24) NOT NULL, Location char(32) NOT NULL, dno Integer NOT NULL, PRIMARY KEY (dno) );

create table EMPLOYEE (emp_id Integer NOT NULL, title char(18) NOT NULL, bdate char(24) NOT NULL, gender char(8) NOT NULL, fname char(12) NOT NULL, lname char(18) NOT NULL, dno Integer NOT NULL, address char(32) NOT NULL, PRIMARY KEY (emp_id) );

create table POWERS ( emp_id Integer NOT NULL, power char(32) NOT NULL, PRIMARY KEY (emp_id,power) );

create table WEAKNESS ( emp_id Integer NOT NULL, weakness char(32) NOT NULL, PRIMARY KEY (emp_id,weakness) );

create table PROJECT ( prj_id Integer NOT NULL, emp_id Integer NOT NULL, start_date date NOT NULL, days_duration Integer NOT NULL, villain char(28) NOT NULL, project_cost Numeric(18,2) NOT NULL, PRIMARY KEY (prj_id) );

Explanation / Answer

Hi.

Below is the answer-

ANS 1-
SELECT MANAGER FROM DEPARTMENT D
JOIN EMPLOYEE E
ON D.DNO=E.DNO
JOIN PROJECT P
ON E.EMP_ID=P.EMP_ID
AND P.START_DATE IN(SELECT MIN(START_DATE) FROM PROJECT);

ANS 2-
SELECT AVG(PROJECT_COST) AS AVERAGE_COST FROM PROJECT P
JOIN EMPLOYEE E
ON P.EMP_ID=E.EMP_ID
JOIN WEAKNESS W
ON E.EMP_ID=W.EMP_ID
AND W.WEAKNESS='no super power'
UNION ALL
SELECT AVG(PROJECT_COST) AS AVERAGE_COST FROM PROJECT P
JOIN EMPLOYEE E
ON P.EMP_ID=E.EMP_ID
JOIN WEAKNESS W
ON E.EMP_ID=W.EMP_ID
AND W.WEAKNESS!='no super power'

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.