employee fname CHARACTER VARYING15) minit CHARACTER VARYINGC1) Iname CHARACTER V
ID: 3738634 • Letter: E
Question
employee fname CHARACTER VARYING15) minit CHARACTER VARYINGC1) Iname CHARACTER VARYING 15) essn CHARACTERO) bdate DATE address CHARACTER VARYING50) dependent works on CHARACTERP) dependent name CHARACTER VARYINO(15) / essn CHARACTER() pno NUMERIC hours NUMERIC CHARACTER(1) DATE CHARACTER VARYING(B) bdate CHARACTERC1) dept locations relationship salary NUMERIC super an CHARACTER) dno dno NUMERIC dlocation CHARACTER VARYING(15) NUMERIC department CHARACTER VARYING(25) NUMERI CHARACTER) project dno mgissn mgistartd ate DATE pname CHARACTER VARYINGC25) pio ploeation CHARACTER VARYING(15) dne NUMERIc NUMERIC Write SQL statements for the following queries. 1) (10 points) Retrieve essns of employees who worked more than 45 hours on projects 2) (10 points) Retrieve fnames of employees who have no working experience 3) (10 points) Retrieve fnames of employees who have the highest salary points) Retrieve the minimum, maximum and average salaries of employees whose department is Research 5) (10 points) Retrieve fnames of employees who have more than two dependentsExplanation / Answer
Hi,
Please find the below details.
1) Retrieve ESSN of employee's who worked morethan 45 hrs.
SELECT E.ESSN FROM EMPLOYEE E, WORKS_ON W, PROJECT P WHERE E.ESSN = W.ESSN AND W.PNO = P.PNO
AND W.HOURS > '45';
2) Retrieve FNAME's of employees who don't have experience.
SELECT E.FNAME FROM EMPLOYEE E WHERE E.ESSN NOT IN (SELECT DISTINCT W.ESSN FROM WORKS_ON W);
3) Retrieve FNAME's of employees who have highest salary.
SELECT E.FNAME FROM EMPLOYEE E WHERE E.SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE);
4) Find Min, Max and Avg of employees who belong to Research department.
SELECT MIN(E.SALARY), MAX(E.SALARY), AVG(E.SALARY) FROM EMPLOYEE E, DEPARTMENT D WHERE E.DNO = D.DNO AND D.DNAME = 'Research';
5) Retrieve FNAME of employees who have more than 2 dependents.
SELECT E.FNAME FROM EMPLOYEE E , (
SELECT COUNT(*), D.ESSN ESSN FROM DEPENDENT D GROUP BY D.ESSN HAVING COUNT(*) > 2) INN
WHERE E.ESSN = INN.ESSN;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.