Assignment:
Q1. Give a list of hire dates of all employees in the following format. The output contains 3 columns so you'll need to select three things.
ENAME Hired_On Hire Date
---------- ------------- ------------------------------
KING was hired on Tuesday , November 17, 1981.
BLAKE was hired on Friday , May 01, 1981.
CLARK was hired on Tuesday , June 09, 1981.
JONES was hired on Thursday , April 02, 1981.
MARTIN was hired on Monday , September 28, 1981.
ALLEN was hired on Friday , February 20, 1981.
TURNER was hired on Tuesday , September 08, 1981.
JAMES was hired on Thursday , December 03, 1981.
WARD was hired on Sunday , February 22, 1981.
FORD was hired on Thursday , December 03, 1981.
SMITH was hired on Wednesday, December 17, 1980.
SCOTT was hired on Thursday , December 09, 1982.
ADAMS was hired on Wednesday, January 12, 1983.
MILLER was hired on Saturday , January 23, 1982.
Q2. Give ordid and description of all products that were ever ordered in quantities of 100 or more.
You also should do all of them on Oracle even though theoretically you can still complete the problems without Oracle. But you may not get all problems right because you may not be minding integrity rules. You may need to use multiple SQL statements in sequence in each problem.
Create these two tables with data types as befitting the data you see in the sample data. Do not define the primary key for each table at this time. Do not insert data at this time either.
Worker
WorkerId LastName FirstName skillID
1001 PARKER AMY K1
1002 SMITH JANE K2
Skill
SkillId descrip HourlyMax ApprovedBy
K1 COMPUTING 50 1002
K2 MANAGEMENT 80 1002
Insert data as shown. You'll need to use multiple DML statements.
Define the following primary keys and foreign keys for these two tables. Use multiple DDL statements for this. Think carefully about the sequence of SQL statements used here. Submit the SQL statements in the right sequence.
Worker: pk=workerid. Foreign key=skillid, referencing Skill
Skill: pk=skillid. Foreign key=ApprovedBy, referencing worker