HERE IS MY CODE .SQL: /**Baisalbek Urustanbekov*/ CREATE TABLE EMPLOYEE ( Fname
ID: 3910829 • Letter: H
Question
HERE IS MY CODE .SQL:
/**Baisalbek Urustanbekov*/
CREATE TABLE EMPLOYEE (
Fname varchar(128),
Minit varchar(5),
Lname varchar(128),
Ssn int,
Bdate date,
Address varchar(128),
Sex varchar(128),
Salary int,
Super_ssn BIGINT,
Dno int,
PRIMARY KEY (Ssn) );
CREATE TABLE DEPARTMENT
(Dname varchar(128),
Dnumber int,
Mgr_ssn int,
Mgr_start_date date,
PRIMARY KEY (Dnumber),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn));
CREATE TABLE DEPT_LOCATIONS
(Dnumber int,
Dlocation varchar(128),
PRIMARY KEY (Dnumber,Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber));
CREATE TABLE PROJECT
(Pname varchar(128),
Pnumber int,
primary key (Pnumber),
Plocation varchar(128), Dnum int,
foreign key (Dnum) references DEPARTMENT(Dnumber));
CREATE TABLE WORKS_ON
(Essn int ,
Pno int ,
Hours int,
primary key(Essn,Pno),
foreign key (Essn) references EMPLOYEE(Ssn),
foreign key (Pno) references PROJECT(Pnumber));
CREATE TABLE DEPENDENT
(Essn int,
Dependent_name varchar(128),
Sex varchar(2),
Bdate date,
Relationship varchar(128),
primary key(Essn,Dependent_name),
foreign key (Essn) references EMPLOYEE(Ssn));
/*-------------------------------------------*/
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5 );
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5 );
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Alicia', 'J', 'Zelaya', 999887777, '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4 );
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4 );
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Ramesh', 'K', 'Narayan', 666884444, '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5 );
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Joyce', 'A', 'English', 453453453, '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5 );
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Ahmad', 'V', 'Jabbar', 987987987, '1969-03-29', '980 Dalas, Houston, TX', 'M', 25000, 987654321, 4);
INSERT INTO EMPLOYEE ( Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('James', 'E', 'Brog', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1 );
/*-------------------------------------------*/
INSERT INTO DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
VALUES ('Research', 5, 333445555, '1988-05-22');
INSERT INTO DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
VALUES ('Administration', 4, 987654321, '1995-01-01');
INSERT INTO DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
VALUES ('Headquarters', 1, 888665555, '1981-06-19');
/*-------------------------------------------*/
INSERT INTO DEPT_LOCATIONS(Dnumber, Dlocation)
VALUES (1, 'Houston');
INSERT INTO DEPT_LOCATIONS(Dnumber, Dlocation)
VALUES (4, 'Stafford');
INSERT INTO DEPT_LOCATIONS(Dnumber, Dlocation)
VALUES (5, 'Bellaire');
INSERT INTO DEPT_LOCATIONS(Dnumber, Dlocation)
VALUES (5, 'Sugarland');
INSERT INTO DEPT_LOCATIONS(Dnumber, Dlocation)
VALUES (5, 'Houston');
/*-------------------------------------------*/
INSERT INTO PROJECT (Pname, Pnumber, Plocation, Dnum)
VALUES ('ProductX', 1, 'Bellaire', 5);
INSERT INTO PROJECT (Pname, Pnumber, Plocation, Dnum)
VALUES ('ProductY', 2, 'Sugarland', 5);
INSERT INTO PROJECT (Pname, Pnumber, Plocation, Dnum)
VALUES ('ProductZ', 3, 'Houston', 5);
INSERT INTO PROJECT (Pname, Pnumber, Plocation, Dnum)
VALUES ('Computerization', 10, 'Stafford', 4);
INSERT INTO PROJECT (Pname, Pnumber, Plocation, Dnum)
VALUES ('Reorganization', 20, 'Houston', 1);
INSERT INTO PROJECT (Pname, Pnumber, Plocation, Dnum)
VALUES ('Newbenefits', 30, 'Stafford', 4);
/*-------------------------------------------*/
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (123456789, 1, 32.5);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (123456789, 2, 7.5);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (666884444, 3, 40.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (453453453, 1, 20.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (453453453, 2, 20.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (333445555, 2, 10.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (333445555, 3, 10.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (333445555, 10, 10.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (333445555, 20, 10.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (999887777, 30, 30.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (999887777, 10, 10.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (987987987, 10, 35.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (987987987, 30, 5.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (987654321, 30, 20.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (987654321, 20, 15.0);
INSERT INTO WORKS_ON (Essn, Pno, Hours)
VALUES (888665555, 20, NULL);
/*-------------------------------------------*/
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
VALUES (333445555, 'Alice', 'F', '1986-04-05', 'Daughter');
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
VALUES (333445555, 'Theodore', 'M', '1983-10-25', 'Son');
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
VALUES (333445555, 'Joy', 'F', '1958-05-03', 'Spouse');
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
VALUES (987654321, 'Abner', 'M', '1942-02-28', 'Spouse');
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
VALUES (123456789, 'Michael', 'M', '1988-01-04', 'Son');
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
VALUES (123456789, 'Alice', 'F', '1988-12-30', 'Daughter');
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
VALUES (123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse');
2. Write the following queries in SQL. Test your queries. KEEP IN MIND that your queries must work for values other than what's provided below. For example, on a) I can replace 10 with 5 or 'ProductX" with 'ProductY', etc (a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'Productx' project. (b) List the names of employees who have a dependent with the same first name as themselves. (c) Find the names of employees that are directly supervised by 'Franklin Wong' (d) For each project, list the project name and the total hours per week (by all employees) spent on that project. (e) Retrieve the names of employees who work on every project. (f) Retrieve the names of employees who do not work on any project. (g) For each department, retrieve the department name, and the average salary of employees working in that department. (h) Retrieve the average salary of all female employees. (i) Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston. () List the last names of department managers who have no dependents.
Explanation / Answer
(a)
SELECT Fname , Lname
FROM EMPLOYEE, WORKS_ON , PROJECT
WHERE Ssn = Essn AND Dno = 5 AND Pnumber = Pno AND Hours >10 AND Pname ='ProductX' ;
Above query takes cross product of EMPLOYEE , WORKS_ON and PROJECT tables and fetches result satisfying where clause.
b)
SELECT Fname , Lname
FROM EMPLOYEE , DEPENDENT
WHERE Ssn = Essn AND Fname = Dependent_name ;
Above query takes cross product of EMPLOYEE and DEPENDENT and fetches employees that have same Fname as that of dependent.
c)
SELECT Fname , Lname
FROM EMPLOYEE E , EMPLOYEE F
WHERE F.Ssn = E.Super_ssn AND F.Fname = 'Franklin' AND F.Lname = 'Wong';
Above query fetches employees who are supervised by Franklin Wong
d)
SELECT Pname, sum(Hours)
FROM PROJECT , WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pname ;
Above query fetches project name and time spent in hours by employees on that project.
Group by is used to group the result by Pname.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.