( please copy the solution in the word file and make it as attachment), Thank yo

Question

( please copy the solution in the word file and make it as attachment), Thank you.

1. Submit one .sql file that creates and interacts with a simple database. You must do each of the following:

(1) Create 6 tables as shown in Figure 5.5. You need to create/include constraints—at a minimum, primary and foreign keys! (

2) Insert values into the tables as shown in Figure 5.6.

(3) Use the command SELECT to show the entire contents of a table.

(4) Implement SQL for the following:

(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.

(j) List the last names of department managers who have no dependents.

(5) Drop all tables.

Notes:

• TEST your code in a DBMS such as XAMPP. Use the template code baseline for the comment syntax and update the fields for NAME, date, and MySQL if you tested on another relational database management system.

• Make sure you DROP all your tables successfully. Once all SQL commands have been executed there should be no tables or data remaining

. • You are submitting ONLY ONE .sql file to Canvas which I will run on my local DBMS XAMPP installation.

The following is the information that we need it:

-- NAME / date / CS2300
-- This code should be tested in MySQL (technically MariaDB) on XAMPP
-- (1) Create the tables

-- (2) Populate the tables
-- order will be important due to referential integrity!

INSERT INTO Employee VALUES ('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5);
INSERT INTO Employee VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5);
INSERT INTO Employee VALUES ('Alicia', 'J', 'Zelaya', 999887777, '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4);
INSERT INTO Employee VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4);
INSERT INTO Employee VALUES ('Ramesh', 'K', 'Narayan', 666884444, '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5);
INSERT INTO Employee VALUES ('Joyce', 'A', 'English', 453453453, '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5);
INSERT INTO Employee VALUES ('Ahmad', 'V', 'Jabbar', 987987987, '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4);
INSERT INTO Employee VALUES ('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, null, 1);

INSERT INTO Department VALUES ('Research', 5, 333445555, '1988-05-22');
INSERT INTO Department VALUES ('Administration', 4, 987654321, '1995-01-01');
INSERT INTO Department VALUES ('Headquarters', 1, 888665555, '1981-06-19');

INSERT INTO Dept_Locations VALUES (1, 'Houston');
INSERT INTO Dept_Locations VALUES (4, 'Stafford');
INSERT INTO Dept_Locations VALUES (5, 'Bellaire');
INSERT INTO Dept_Locations VALUES (5, 'Sugarland');
INSERT INTO Dept_Locations VALUES (5, 'Houston');

INSERT INTO Works_On VALUES (123456789, 1, 32.5);
INSERT INTO Works_On VALUES (123456789, 2, 7.5);
INSERT INTO Works_On VALUES (666884444, 3, 40.0);
INSERT INTO Works_On VALUES (453453453, 1, 20.0);
INSERT INTO Works_On VALUES (453453453, 2, 20.0);
INSERT INTO Works_On VALUES (333445555, 2, 10.0);
INSERT INTO Works_On VALUES (333445555, 3, 10.0);
INSERT INTO Works_On VALUES (333445555, 10, 10.0);
INSERT INTO Works_On VALUES (333445555, 20, 10.0);
INSERT INTO Works_On VALUES (999887777, 30, 30.0);
INSERT INTO Works_On VALUES (999887777, 10, 10.0);
INSERT INTO Works_On VALUES (987987987, 10, 35.0);
INSERT INTO Works_On VALUES (987987987, 30, 5.0);
INSERT INTO Works_On VALUES (987654321, 30, 20.0);
INSERT INTO Works_On VALUES (987654321, 20, 15.0);
INSERT INTO Works_On VALUES (888665555, 20, null);

INSERT INTO Project VALUES ('ProductX', 1, 'Bellaire', 5);
INSERT INTO Project VALUES ('ProductY', 2, 'Sugarland', 5);
INSERT INTO Project VALUES ('ProductZ', 3, 'Houston', 5);
INSERT INTO Project VALUES ('Computerization', 10, 'Stafford', 4);
INSERT INTO Project VALUES ('Reorganization', 20, 'Houston', 1);
INSERT INTO Project VALUES ('Newbenefits', 30, 'Stafford', 4);

INSERT INTO Dependent VALUES (333445555, 'Alice', 'F', '1986-04-05', 'Daughter');
INSERT INTO Dependent VALUES (333445555, 'Theodore', 'M', '1983-10-25', 'Son');
INSERT INTO Dependent VALUES (333445555, 'Joy', 'F', '1958-05-03', 'Spouse');
INSERT INTO Dependent VALUES (987654321, 'Abner', 'M', '1942-02-28', 'Spouse');
INSERT INTO Dependent VALUES (123456789, 'Michael', 'M', '1988-01-04', 'Son');
INSERT INTO Dependent VALUES (123456789, 'Alice', 'F', '1988-12-30', 'Daugther');
INSERT INTO Dependent VALUES (123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse');

-- (3) Display the contents of a table

5.5 Schema diagram for the COMPANY relational database schema. EMPLOYEE Fname Min Lname Ssn Bdate Address Sex Salary Super ssn Dno DEPARTMENT Dname Dnumber Mgr ssn Mgr start date DEPT LOCATIONS Dnumbe Dlocation PROJECT Pname Pnumber Plocation Dnunm WORKS ON Essn Pno Hours DEPENDENT Essn Dependent name Sex Bdate Relationship

Explanation / Answer

Hi ,

I tried to upload the sql file but it is not working. so am pasting queries here.kindly check it

CREATE TABLE `sample`.`EMPLOYEE` (
`Fname` VARCHAR( 255 ) NOT NULL ,
`Minit` VARCHAR( 255 ) NOT NULL ,
`Lname` VARCHAR( 255 ) NOT NULL ,
`Ssn` INT( 11 ) NOT NULL ,
`Ddate` DATE NOT NULL ,
`Address` TEXT NOT NULL ,
`Sex` ENUM( 'M', 'F' ) NOT NULL ,
`Salary` INT( 11 ) NOT NULL ,
`Super_ssn` INT( 11 ) NOT NULL ,
`Dno` INT( 11 ) NOT NULL ,
PRIMARY KEY ( `Ssn` )
) ENGINE = InnoDB;

CREATE TABLE `sample`.`Department` (
`Dname` VARCHAR( 255 ) NOT NULL ,
`Dno` INT( 11 ) NOT NULL ,
`Mgr_ssn` INT( 11 ) NOT NULL ,
`Mgr_start_date` DATE NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `sample`.`Dept_Locations` (
`Dnumber` INT( 11 ) NOT NULL ,
`Dlocation` VARCHAR( 255 ) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `sample`.`Works_On` (
`Essn` INT( 11 ) NOT NULL ,
`Pno` INT( 11 ) NOT NULL ,
`Hours` FLOAT NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `sample`.`Project` (
`Pname` VARCHAR( 255 ) NOT NULL ,
`Pnumber` INT( 11 ) NOT NULL ,
`Plocation` VARCHAR( 255 ) NOT NULL ,
`Dnum` INT( 11 ) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `sample`.`Dependent` (
`Essn` INT( 11 ) NOT NULL ,
`Dependent_name` VARCHAR( 255 ) NOT NULL ,
`Sex` ENUM( 'M', 'F' ) NOT NULL ,
`Bdate` DATE NOT NULL ,
`Relationship` VARCHAR( 255 ) NOT NULL
) ENGINE = InnoDB;

SELECT * FROM `employee`;
SELECT * FROM `department`;
SELECT * FROM `dependent`;
SELECT * FROM `dept_locations`;
SELECT * FROM `project`;
SELECT * FROM `works_on`;

(a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
SELECT FNAME, LNAME
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Pno = Pnumber
AND DNO =5
AND PNAME = 'PRODUCTX'
AND HOURS >10
AND ESSN = SSN
LIMIT 0 , 30

Answer:
FNAME LNAME
JOHN SMITH
JOYCE ENGLISH

(b)List the names of employees who have a dependent with the same first name as themselves.

b.SELECT LNAME, FNAME
FROM EMPLOYEE, DEPENDENT
WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME

Answer:
Result (empty):
FNAME LNAME

C.SELECT A.FNAME, A.LNAME
FROM EMPLOYEE A, EMPLOYEE S
WHERE S.FNAME = 'Franklin'
AND S.LNAME = 'Wong'
AND A.SUPER_SSN = S.SSN
LIMIT 0 , 30

Answer :
FNAME LNAME
John Smith
Ramesh Narayan
Joyce English

(d) For each project, list the project name and the total hours per week (by all employees) spent on that project.
SELECT PNAME, SUM(HOURS)
FROM PROJECT, WORKS_ON

Answer : PNAME            SUM(HOURS)
         ProductX    1650

(e) Retrieve the names of employees who work on every project.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT PNUMBER
FROM PROJECT
WHERE NOT EXISTS ( SELECT *
FROM WORKS_ON
WHERE PNUMBER=PNO AND ESSN=SSN ) )

Answer : Result (empty):
FNAME LNAME

(f) Retrieve the names of employees who do not work on any project.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM WORKS_ON
WHERE ESSN=SSN )

Answer : Result (empty):
FNAME LNAME

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.
SELECT AVG( SALARY )
FROM EMPLOYEE
WHERE SEX = 'F'

Answer : AVG (SALARY)
31000.0000

(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.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PLOCATION='Houston' )
AND
NOT EXISTS ( SELECT *
FROM DEPT_LOCATIONS
WHERE DNO=DNUMBER AND DLOCATION='Houston' )

Answer : Jennifer   Wallace    291 Berry, Bellaire, TX

(j)List the last names of department managers who have no dependents.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPARTMENT
WHERE SSN=MGR_SSN )
AND
NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE SSN=ESSN )

Answer :
FNAME LNAME
James Borg

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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