Consider the following database. Employee has a unique eid, name, age and salary
ID: 3736957 • Letter: C
Question
Consider the following database. Employee has a unique eid, name, age and salary. Department has a unique did, name and an employee as a manager. A department may or may not have a manager assigned. An employee can work for multiple departments. The pct_time in Works table is the percentage of time an employee works for a department. The salary of the employees is fixed and is not based on pct_time or the deparments they work in. Emp (eid: INT(11), ename: VARCHAR(50), age: INT(11), salary: FLOAT) Works (eid: INT(11), did: INT(11), pct_time: INT(11)) Dept (did: INT(11), dname: VARCHAR(50, managerid: INT(11)) Constraints • eid is primary key for Emp table and cannot be NULL • did is primary key for Dept table and cannot be NULL • eid and did are foreign keys referencing to Emp and Dept tables respectively in Works table. combination of eid and did is the primary key or works table. • managerid is foreign key referencing to Emp table and can have NULL values if the department doesn’t have a manager assigned. Sample values. Emp
EMP
eid
ename
age
salary
1001
Georgi Facello
34
45000.00
1002
Parto Bamford
28
34000.00
1003
Christian Koblick
42
55000.00
1004
Saniya Peac
24
28000.00
Dept
did
dname
managerid
d001
Marketing
1003
d002
Finance
1002
d003
Information Security
NULL
works
eid
did
pct_time
1001
d001
60
1002
d002
100
1003
d001
100
1001
d003
40
1004
d003
50
1004
d002
50
1) Write the insert statements to enter the above sample values to the database. Add 4 more employees, 2 more departments and assign the 4 new employees to departments with percentage time. Submit all insert statements.
2) Alter the table Emp to change the column age to “dob” with data type DATE and update the values based on their current age (should be approximate). Submit the alter and update table statement.
eid
ename
age
salary
1001
Georgi Facello
34
45000.00
1002
Parto Bamford
28
34000.00
1003
Christian Koblick
42
55000.00
1004
Saniya Peac
24
28000.00
Explanation / Answer
Solution:
1)
Inserting 4 more emplyees to the Emp database"
INSERT INTO Emp(eid,ename,age,salary) VALUES(1005,'John Smith',35,34000.00);
INSERT INTO Emp(eid,ename,age,salary) VALUES(1006,'JC Sam',27,36000.00);
INSERT INTO Emp(eid,ename,age,salary) VALUES(1007,'Robert',37,40000.00);
INSERT INTO Emp(eid,ename,age,salary) VALUES(1008,'Kevin',28,39000.00);
inserting 2 more departments to Dept database:
INSERT INTO Dept(did,dname,managerid) VALUES(d004,'Welfare',1006);
INSERT INTO Dept(did,dname,managerid) VALUES(d005,'Publicity',NULL);
inserting 4 new employees to department with percentage time:
INSERT INTO Works(eid,did,pct_time) VALUES(1007,d005,60);
INSERT INTO Works(eid,did,pct_time) VALUES(1009,d005,70);
INSERT INTO Works(eid,did,pct_time) VALUES(1005,d002,40);
INSERT INTO Works(eid,did,pct_time) VALUES(1003,d004,100);
Query:
ALTER TABLE Emp
RENAME COLUMN "age" TO "dob";
UPDATE Emp SET dob = ‘1745-09-25’ WHERE eid=1001;
UPDATE Emp SET dob = ‘1995-11-09’ WHERE eid=1002;
UPDATE Emp SET dob = ‘2001-05-27’ WHERE eid=1003;
UPDATE Emp SET dob = ‘1982-01-24’ WHERE eid=1004;
Explanation:
The alter command is used to update the table here some random value at dob is used to insert into the employee table.
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.