An Employee has an employee id, first name, last name, gender, date of birth, So

Question

An Employee has an employee id, first name, last name, gender, date of birth, Social Security and can manage multiple projects which have a project id, description, status, start date and projected end date. a. Write the SQL Script to create tables named EMPLOYEE and PROJECT given the description above. The names, gender, SSN, project status and project description are strings, the kids are numbers and the rest are dates. Use the appropriate SQL Data types. The PROJECT table has an additional column named Manager which will contain the ID of the employee which manages the project. b. Given that the two tables have been created, write SQL statements to insert 2 records into the Employee table and 4 records into the Project table. Two of the projects must be managed by the same employee. c. Write a SQL statement to retrieve the first and last name of the employees which manage 2 or more projects. d. Delete all the employees who have been born before Aug 1^st, 1970 and have an SSN starting with 522. To verify the results, first insert some sample data to delete

Explanation / Answer

This is not that difficult, the creation of the table is totally upto you and values inserted is random. Here you go with the solution:

CREATE TABLE EMPLOYEE
( employee_id int NOT NULL,
first_name varchar(50) NOT NULL,
last_name varchar(50),
gender char(1) NOT NULL,
dateOfBirth date NOT NULL,
ssn varchar(40),
PRIMARY KEY (employee_id)
);

CREATE TABLE PROJECT
( project_id int NOT NULL,
description varchar(50) NOT NULL,
status varchar(50),
startdate date NOT NULL,
projectedenddate date NOT NULL,
Manager int NOT NULL,
PRIMARY KEY (project_id),
FOREIGN KEY (Manager) REFERENCES EMPLOYEE(employee_id)
);

Insert into EMPLOYEE values
(1234,'Someone','Something','M','1982-10-15','409222002'),
(1235,'Another','One','F','1981-12-15','408222002'); //random value insertion

Insert into PROJECT values
(11,'XYZ project','working on','2017-02-10','2017-04-10',1234),
(12,'ABC project','almost done','2016-12-11','2017-02-11',1235),
(13,'PQR project','developing','2016-10-10','2017-01-10',1234),
(14,'MNO project','creating','2017-05-12','2017-09-12',1235);

select first_name, last_name from EMPLOYEE where (select count(*) from PROJECT where employee_id=Manager) >=2 ; // nested query to check the occurence in another table

Insert into EMPLOYEE values
(1236,'ABC','qwer','M','1970-07-29','409222002'),
(1237,'ASD','YUI','F','1991-12-15','522222002');
select * from EMPLOYEE; //to check the data inserted
delete from EMPLOYEE where dateOfBirth < '1970-08-01' OR ssn like '522%'; // date less than and ssn starting with 522 and followed by n number of characters
select * from EMPLOYEE; //to check the deletion

If you have any problem let me know in the comments !

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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