SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME = 'John' AND MINIT = 'B' AND LNAME = 'Smith'; SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME = 'Research' AND DEPARTMENT.DNUMBER = EMPLOYEE.DNO; SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME = 'Smith' UNION SELECT PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER = PNO AND ESSN = SSN AND LNAME = 'Smith'; SELECT DISTINCT PNAME FROM PROJECT WHERE PNUMBER IN (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME = 'Smith') OR PNUMBER IN (SELECT PNO FROM WORKS_ON, EMPLOYEE WHERE ESSN = SSN AND LNAME = 'Smith'); CREATE VIEW WORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN = ESSN AND PNO = PNUMBER; CREATE VIEW DEPT_INFO (DNAME, EMP_NO, AVG_SAL) AS SELECT DNAME, COUNT(*), AVG(SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME; CREATE VIEW WORKS_SUM AS SELECT FNAME, LNAME, SUM(HOURS) FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN = ESSN AND PNO = PNUMBER GROUP BY FNAME, LNAME;