Share
Explore BrainMass

SQL DDL and DML Queries

Hi Rao,
I need help to write query statement for this.

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

4.Give ordid and description of all products that were ever ordered in quantities of 100 or more.
Q14-Q17 HAVE TO BE COMPLETED in sequence. 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

Drop worker and skill tables that you have just created. You may get an error message saying something like "ORA-02449: unique/primary keys in table referenced by foreign keys" when you try to drop either table. Work around this and really drop the tables. Submit all relevant SQL statements used to accomplish this. If you can't figure out how to work around the error message and really drop the table, just ignore it and submit your best guess SQL statements for partial credits. (Hint: Drop constraints first.)

Solution Summary

This solution deals with a set of SQL queries based on EMP table in Oracle.

$2.19