-list the name of all employees with two or more projects -list the name of empl
ID: 3675240 • Letter: #
Question
-list the name of all employees with two or more projects
-list the name of employees who have at least one dependent
The tables CREATE TABLE EMPLOYEE (Fname VARCHAR(15) NOT NULL Minit NOT NULL, NOT NULL, VARCHAR(15) CHAR(9) DATE, VARCHAR(30), Ssn Bdate Sex Super ssn DECIMAL(10,2), CHAR(9), INT Dno NOT NULL, PRIMARY KEY (Ssn) FOREIGN KEY (Super-ssn) REFERENCES EMPLOYEE( Ssn), FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) ); CREATE TABLE DEPARTMENT VARCHAR(15) INT CHAR(9) DATE, NOT NULL NOT NULL, NOT NULL Dname Mgr ssn Mgr start date PRIMARY KEY (Dnumber), UNIQUE (Dname), FOREIGN KEY (Mgr ssn) REFERENCES EMPLOYEE(Ssn) ); CREATE TABLE DEPT LOCATIONS INT VARCHAR(15) NOT NULL, NOT NULL, (Dnumber PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber) ); CREATE TABLE PROJECT NOT NULL NOT NULL VARCHAR(15) INT VARCHAR(15), INT (Pname Dnum NOT NULL, PRIMARY KEY (Pnumber), UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES DEPARTMENT Dnumber) ) CREATE TABLE WORKS ON Essn Pno Hours CHAR(9) INT DECIMAL(3,1) NOT NULL NOT NULL NOT NULL PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)) CREATE TABLE DEPENDENT NOT NULL, NOT NULL Essn CHAR(9) VARCHAR(15) CHAR, DATE, VARCHAR(8), Bdate Relationship PRIMARY KEY (Essn, Dependent name), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) );
Explanation / Answer
Please find the quries below :
1) select emp.lname, t.cnt from (Select e.ssn as ssn, count(e.ssn) as cnt from employee e, department d, project p where e.dno=d.dnumber and d.dnumber=p.dnum ) group by e.ssn) t, employee emp where emp.ssn=t.ssn and t.cnt >= 2
2) select emp.lname, t.cnt from (Select e.ssn as ssn, count(e.ssn) as cnt from employee e, dependent d where e.ssn = d.essn group by e.ssn) t, employee emp where emp.ssn=t.ssn and t.cnt > 1
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.