Homework Sourse1 Create a database called ConstructCo and read the data fro
1. Create a database called ConstructCo and read the data from the script file: Here\'s the script file
/* Database Systems, Coronel/Morris */
/* Type of SQL : MySQL */
/* -- */
CREATE TABLE JOB (
JOB_CODE VARCHAR(3),
JOB_DESCRIPTION VARCHAR(25),
JOB_CHG_HOUR FLOAT(8),
JOB_LAST_UPDATE DATETIME,
PRIMARY KEY(JOB_CODE)
);
INSERT INTO JOB VALUES(\'500\',\'Programmer\', \'35.75\',\'2015-11-20\');
INSERT INTO JOB VALUES(\'501\',\'Systems Analyst\', \'96.75\',\'2015-11-20\');
INSERT INTO JOB VALUES(\'502\',\'Database Designer\', \'125\', \'2016-3-24\');
INSERT INTO JOB VALUES(\'503\',\'Electrical Engineer\', \'84.5\', \'2015-11-20\');
INSERT INTO JOB VALUES(\'504\',\'Mechanical Engineer\', \'67.9\', \'2015-11-20\');
INSERT INTO JOB VALUES(\'505\',\'Civil Engineer\', \'55.78\',\'2015-11-20\');
INSERT INTO JOB VALUES(\'506\',\'Clerical Support\', \'26.87\',\'2015-11-20\');
INSERT INTO JOB VALUES(\'507\',\'DSS Analyst\', \'45.95\',\'2015-11-20\');
INSERT INTO JOB VALUES(\'508\',\'Applications Designer\',\'48.1\', \'2016-3-24\');
INSERT INTO JOB VALUES(\'509\',\'Bio Technician\', \'34.55\',\'2015-11-20\');
INSERT INTO JOB VALUES(\'510\',\'General Support\', \'18.36\',\'2015-11-20\');
/* -- */
CREATE TABLE EMPLOYEE (
EMP_NUM VARCHAR(3),
EMP_LNAME VARCHAR(15),
EMP_FNAME VARCHAR(15),
EMP_INITIAL VARCHAR(1),
EMP_HIREDATE DATETIME,
JOB_CODE VARCHAR(3),
EMP_YEARS INT(3),
PRIMARY KEY(EMP_NUM),
FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)
);
INSERT INTO EMPLOYEE VALUES(\'101\',\'News\',\'John\',\'G\',\'2000-11-8\',\'502\',\'4\');
INSERT INTO EMPLOYEE VALUES(\'102\',\'Senior\',\'David\',\'H\',\'1989-7-12\',\'501\',\'15\');
INSERT INTO EMPLOYEE VALUES(\'103\',\'Arbough\',\'June\',\'E\',\'1996-12-1\',\'503\',\'8\');
INSERT INTO EMPLOYEE VALUES(\'104\',\'Ramoras\',\'Anne\',\'K\',\'1987-11-15\',\'501\',\'17\');
INSERT INTO EMPLOYEE VALUES(\'105\',\'Johnson\',\'Alice\',\'K\',\'1993-2-1\',\'502\',\'12\');
INSERT INTO EMPLOYEE VALUES(\'106\',\'Smithfield\',\'William\',NULL,\'2004-6-22\',\'500\',\'0\');
INSERT INTO EMPLOYEE VALUES(\'107\',\'Alonzo\',\'Maria\',\'D\',\'1993-10-10\',\'500\',\'11\');
INSERT INTO EMPLOYEE VALUES(\'108\',\'Washington\',\'Ralph\',\'B\',\'1991-8-22\',\'501\',\'13\');
INSERT INTO EMPLOYEE VALUES(\'109\',\'Smith\',\'Larry\',\'W\',\'1997-7-18\',\'501\',\'7\');
INSERT INTO EMPLOYEE VALUES(\'110\',\'Olenko\',\'Gerald\',\'A\',\'1995-12-11\',\'505\',\'9\');
INSERT INTO EMPLOYEE VALUES(\'111\',\'Wabash\',\'Geoff\',\'B\',\'1991-4-4\',\'506\',\'14\');
INSERT INTO EMPLOYEE VALUES(\'112\',\'Smithson\',\'Darlene\',\'M\',\'1994-10-23\',\'507\',\'10\');
INSERT INTO EMPLOYEE VALUES(\'113\',\'Joenbrood\',\'Delbert\',\'K\',\'1996-11-15\',\'508\',\'8\');
INSERT INTO EMPLOYEE VALUES(\'114\',\'Jones\',\'Annelise\',NULL,\'1993-8-20\',\'508\',\'11\');
INSERT INTO EMPLOYEE VALUES(\'115\',\'Bawangi\',\'Travis\',\'B\',\'1992-1-25\',\'501\',\'13\');
INSERT INTO EMPLOYEE VALUES(\'116\',\'Pratt\',\'Gerald\',\'L\',\'1997-3-5\',\'510\',\'8\');
INSERT INTO EMPLOYEE VALUES(\'117\',\'Williamson\',\'Angie\',\'H\',\'1996-6-19\',\'509\',\'8\');
INSERT INTO EMPLOYEE VALUES(\'118\',\'Frommer\',\'James\',\'J\',\'2005-1-4\',\'510\',\'0\');
/* -- */
CREATE TABLE PROJECT (
PROJ_NUM VARCHAR(3),
PROJ_NAME VARCHAR(25),
PROJ_VALUE FLOAT(8),
PROJ_BALANCE FLOAT(8),
EMP_NUM VARCHAR(3),
PRIMARY KEY(PROJ_NUM),
FOREIGN KEY(EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO PROJECT VALUES(\'15\',\'Evergreen\',\'1453500\',\'1002350\',\'103\');
INSERT INTO PROJECT VALUES(\'18\',\'Amber Wave\',\'3500500\',\'2110346\',\'108\');
INSERT INTO PROJECT VALUES(\'22\',\'Rolling Tide\',\'805000\',\'500345.2\',\'102\');
INSERT INTO PROJECT VALUES(\'25\',\'Starflight\',\'2650500\',\'2309880\',\'107\');
/* -- */
CREATE TABLE ASSIGNMENT (
ASSIGN_NUM INT(5),
ASSIGN_DATE DATETIME,
PROJ_NUM VARCHAR(3),
EMP_NUM VARCHAR(3),
ASSIGN_JOB VARCHAR(3),
ASSIGN_CHG_HR FLOAT(8),
ASSIGN_HOURS FLOAT(8),
ASSIGN_CHARGE FLOAT(8),
PRIMARY KEY (ASSIGN_NUM),
FOREIGN KEY (PROJ_NUM) REFERENCES PROJECT(PROJ_NUM),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
);
INSERT INTO ASSIGNMENT VALUES(\'1001\',\'2016-3-22\',\'18\',\'103\',\'503\',\'84.5\',\'3.5\',\'295.75\');
INSERT INTO ASSIGNMENT VALUES(\'1002\',\'2016-3-22\',\'22\',\'117\',\'509\',\'34.55\',\'4.2\',\'145.11\');
INSERT INTO ASSIGNMENT VALUES(\'1003\',\'2016-3-22\',\'18\',\'117\',\'509\',\'34.55\',\'2\',\'69.10\');
INSERT INTO ASSIGNMENT VALUES(\'1004\',\'2016-3-22\',\'18\',\'103\',\'503\',\'84.5\',\'5.9\',\'498.55\');
INSERT INTO ASSIGNMENT VALUES(\'1005\',\'2016-3-22\',\'25\',\'108\',\'501\',\'96.75\',\'2.2\',\'212.85\');
INSERT INTO ASSIGNMENT VALUES(\'1006\',\'2016-3-22\',\'22\',\'104\',\'501\',\'96.75\',\'4.2\',\'406.35\');
INSERT INTO ASSIGNMENT VALUES(\'1007\',\'2016-3-22\',\'25\',\'113\',\'508\',\'50.75\',\'3.8\',\'192.85\');
INSERT INTO ASSIGNMENT VALUES(\'1008\',\'2016-3-22\',\'18\',\'103\',\'503\',\'84.5\',\'0.9\',\'76.05\');
INSERT INTO ASSIGNMENT VALUES(\'1009\',\'2016-3-23\',\'15\',\'115\',\'501\',\'96.75\',\'5.6\',\'541.80\');
INSERT INTO ASSIGNMENT VALUES(\'1010\',\'2016-3-23\',\'15\',\'117\',\'509\',\'34.55\',\'2.4\',\'82.92\');
INSERT INTO ASSIGNMENT VALUES(\'1011\',\'2016-3-23\',\'25\',\'105\',\'502\',\'105\',\'4.3\',\'451.5\');
INSERT INTO ASSIGNMENT VALUES(\'1012\',\'2016-3-23\',\'18\',\'108\',\'501\',\'96.75\',\'3.4\',\'328.95\');
INSERT INTO ASSIGNMENT VALUES(\'1013\',\'2016-3-23\',\'25\',\'115\',\'501\',\'96.75\',\'2\',\'193.5\');
INSERT INTO ASSIGNMENT VALUES(\'1014\',\'2016-3-23\',\'22\',\'104\',\'501\',\'96.75\',\'2.8\',\'270.9\');
INSERT INTO ASSIGNMENT VALUES(\'1015\',\'2016-3-23\',\'15\',\'103\',\'503\',\'84.5\',\'6.1\',\'515.45\');
INSERT INTO ASSIGNMENT VALUES(\'1016\',\'2016-3-23\',\'22\',\'105\',\'502\',\'105\',\'4.7\',\'493.5\');
INSERT INTO ASSIGNMENT VALUES(\'1017\',\'2016-3-23\',\'18\',\'117\',\'509\',\'34.55\',\'3.8\',\'131.29\');
INSERT INTO ASSIGNMENT VALUES(\'1018\',\'2016-3-23\',\'25\',\'117\',\'509\',\'34.55\',\'2.2\',\'76.01\');
INSERT INTO ASSIGNMENT VALUES(\'1019\',\'2016-3-24\',\'25\',\'104\',\'501\',\'110.5\',\'4.9\',\'541.45\');
INSERT INTO ASSIGNMENT VALUES(\'1020\',\'2016-3-24\',\'15\',\'101\',\'502\',\'125\',\'3.1\',\'387.5\');
INSERT INTO ASSIGNMENT VALUES(\'1021\',\'2016-3-24\',\'22\',\'108\',\'501\',\'110.5\',\'2.7\',\'298.35\');
INSERT INTO ASSIGNMENT VALUES(\'1022\',\'2016-3-24\',\'22\',\'115\',\'501\',\'110.5\',\'4.9\',\'541.45\');
INSERT INTO ASSIGNMENT VALUES(\'1023\',\'2016-3-24\',\'22\',\'105\',\'502\',\'125\',\'3.5\',\'437.5\');
INSERT INTO ASSIGNMENT VALUES(\'1024\',\'2016-3-24\',\'15\',\'103\',\'503\',\'84.5\',\'3.3\',\'278.85\');
INSERT INTO ASSIGNMENT VALUES(\'1025\',\'2016-3-24\',\'18\',\'117\',\'509\',\'34.55\',\'4.2\',\'145.11\');
/* -- */
CREATE TABLE EMP_1 (
EMP_NUM VARCHAR(3),
EMP_LNAME VARCHAR(15),
EMP_FNAME VARCHAR(15),
EMP_INITIAL VARCHAR(1),
EMP_HIREDATE DATETIME,
JOB_CODE VARCHAR(3)
);
INSERT INTO EMP_1 VALUES(\'101\',\'News\',\'John\',\'G\',\'2000-11-8\',\'502\');
INSERT INTO EMP_1 VALUES(\'102\',\'Senior\',\'David\',\'H\',\'1989-7-12\',\'501\');
INSERT INTO EMP_1 VALUES(\'103\',\'Arbough\',\'June\',\'E\',\'1996-12-1\',\'500\');
INSERT INTO EMP_1 VALUES(\'104\',\'Ramoras\',\'Anne\',\'K\',\'1987-11-15\',\'501\');
INSERT INTO EMP_1 VALUES(\'105\',\'Johnson\',\'Alice\',\'K\',\'1993-2-1\',\'502\');
INSERT INTO EMP_1 VALUES(\'106\',\'Smithfield\',\'William\',NULL,\'2004-6-22\',\'500\');
INSERT INTO EMP_1 VALUES(\'107\',\'Alonzo\',\'Maria\',\'D\',\'1993-10-10\',\'500\');
INSERT INTO EMP_1 VALUES(\'108\',\'Washington\',\'Ralph\',\'B\',\'1991-8-22\',\'501\');
INSERT INTO EMP_1 VALUES(\'109\',\'Smith\',\'Larry\',\'W\',\'1997-1-18\',\'501\');
/* -- */
CREATE TABLE EMP_2 (
EMP_NUM VARCHAR(3),
EMP_LNAME VARCHAR(15),
EMP_FNAME VARCHAR(15),
EMP_INITIAL VARCHAR(1),
EMP_HIREDATE DATETIME,
JOB_CODE VARCHAR(3),
EMP_PCT FLOAT(8),
PROJ_NUM VARCHAR(2)
);
INSERT INTO EMP_2 VALUES(\'101\',\'News\',\'John\',\'G\',\'2000-11-8\',\'502\',\'5\',NULL);
INSERT INTO EMP_2 VALUES(\'102\',\'Senior\',\'David\',\'H\',\'1989-7-12\',\'501\',\'8\',NULL);
INSERT INTO EMP_2 VALUES(\'103\',\'Arbough\',\'June\',\'E\',\'1996-12-1\',\'500\',\'3.85\',NULL);
INSERT INTO EMP_2 VALUES(\'104\',\'Ramoras\',\'Anne\',\'K\',\'1987-11-15\',\'501\',\'10\',NULL);
INSERT INTO EMP_2 VALUES(\'105\',\'Johnson\',\'Alice\',\'K\',\'1993-2-1\',\'502\',\'5\',NULL);
INSERT INTO EMP_2 VALUES(\'106\',\'Smithfield\',\'William\',NULL,\'2004-6-22\',\'500\',\'6.2\',NULL);
INSERT INTO EMP_2 VALUES(\'107\',\'Alonzo\',\'Maria\',\'D\',\'1993-10-10\',\'500\',\'5.15\',NULL);
INSERT INTO EMP_2 VALUES(\'108\',\'Washington\',\'Ralph\',\'B\',\'1991-8-22\',\'501\',\'10\',NULL);
INSERT INTO EMP_2 VALUES(\'109\',\'Smith\',\'Larry\',\'W\',\'1997-7-18\',\'501\',\'2\',NULL);
/* -- */
CREATE TABLE EMP_2_MOD (
EMP_NUM VARCHAR(3),
EMP_LNAME VARCHAR(15),
EMP_FNAME VARCHAR(15),
EMP_INITIAL VARCHAR(1),
EMP_HIREDATE DATETIME,
JOB_CODE VARCHAR(3),
EMP_PCT FLOAT(8),
PROJ_NUM VARCHAR(2)
);
INSERT INTO EMP_2_MOD VALUES(\'101\',\'News\',\'John\',\'G\',\'2000-11-8\',\'502\',\'5\',\'25\');
INSERT INTO EMP_2_MOD VALUES(\'102\',\'Senior\',\'David\',\'H\',\'1989-7-12\',\'501\',\'8\',NULL);
INSERT INTO EMP_2_MOD VALUES(\'103\',\'Arbough\',\'June\',\'E\',\'1996-12-1\',\'500\',\'3.85\',\'18\');
INSERT INTO EMP_2_MOD VALUES(\'104\',\'Ramoras\',\'Anne\',\'K\',\'1987-11-15\',\'501\',\'10\',NULL);
INSERT INTO EMP_2_MOD VALUES(\'105\',\'Johnson\',\'Alice\',\'K\',\'1993-2-1\',\'502\',\'5\',\'25\');
INSERT INTO EMP_2_MOD VALUES(\'106\',\'Smithfield\',\'William\',NULL,\'2004-6-22\',\'500\',\'6.2\',\'18\');
INSERT INTO EMP_2_MOD VALUES(\'107\',\'Alonzo\',\'Maria\',\'D\',\'1993-10-10\',\'500\',\'5.15\',\'18\');
INSERT INTO EMP_2_MOD VALUES(\'108\',\'Washington\',\'Ralph\',\'B\',\'1991-8-22\',\'501\',\'10\',NULL);
INSERT INTO EMP_2_MOD VALUES(\'109\',\'Smith\',\'Larry\',\'W\',\'1997-7-18\',\'501\',\'2\',NULL);
2. Set EMP_NUM as primary key in table EMP_1.
3. Set JOB_CODE as foreign key in table EMP_1
4. Constrain the EMP_LNAME NOT NULL.
5. Constrain the EMP_FNAME NOT NULL.
6. Write the SQL code that will list all attributes for a job code of 502.
+----------+-------------------+--------------+---------------------+
| JOB_CODE | JOB_DESCRIPTION | JOB_CHG_HOUR | JOB_LAST_UPDATE |
+----------+-------------------+--------------+---------------------+
| 502 | Database Designer | 125 | 2016-03-24 00:00:00 |
+----------+-------------------+--------------+---------------------+
7. List projects’ name and ve where the project balance is less than 1000000.
+--------------+------------+
| proj_name | proj_value |
+--------------+------------+
| Rolling Tide | 805000 |
+--------------+------------+
8. Write the SQL code to change the EMP_PCT value to 3.90 for the person whose employee number (EMP_NUM) is 103.
9. Write the SQL code required to list all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing.
+---------+------------+-----------+-------------+---------------------+----------+-----------+
| EMP_NUM | EMP_LNAME | EMP_FNAME | EMP_INITIAL | EMP_HIREDATE | JOB_CODE | EMP_YEARS |
+---------+------------+-----------+-------------+---------------------+----------+-----------+
| 106 | Smithfield | William | NULL | 2004-06-22 00:00:00 | 500 | 0 |
| 109 | Smith | Larry | W | 1997-07-18 00:00:00 | 501 | 7 |
| 112 | Smithson | Darlene | M | 1994-10-23 00:00:00 | 507 | 10 |
+---------+------------+-----------+-------------+---------------------+----------+-----------+
10. List assignment number and assignment date for employee whose employee number is 117 and the assignment charge is less than 100.
11. List all the projects that is done by employee whose number is 102 or employee whose number is 107.
12. List all the jobs that has hour charge in the range of 50 and 100.
13. List employee’s number, last name, first name whose initial is null.
14. Write the SQL code that will produce a listing for the data in the EMP_2 table in ascending order by the bonus percentage.
15. Write the SQL code that will list only the distinct employee numbers found in the assignment table.
Solution
2)
3)
ALTER TABLE EMP_1
ADD CONSTRAINT emp_fk
FOREIGN KEY (JOB_CODE)
REFERENCES JOB(JOB_CODE);
4)alter table emp_2_mod modify emp_lname varchar2(15) not null;
5)alter table emp_2_mod modify emp_fname varchar2(15) not null;
6)select * from job where job_code=\'502\';
7)select proj_name ,proj_value from project where proj_balance<1000000;
9)select * from EMP_2_MOD where emp_fname like \'smith%\';
11)select proj_name,proj_num where emp_num=102 or emp_num=117;
12)select job_description from job where job_chg_hour between \'50\' and \'100\';
14)select * from emp_2 order by emp_pct;
15)select distinct emp_num from assignment;
13)select emp_num,l_name,f_name from employee where emp_initial IS NULL;
