Employee type fixed length character column with length
CIS 2002
SEMESTER 1, 2010
SECTION A (IMPLEMENTATION OF A DATA MODEL) (40 marks)
Using the relations and the rules set out in the notes under each relation, write statements to create the two sequence generators specified in the notes.
Question 3 (2 marks)
2. Name, address and email should use variable length character columns with a maximum length of 50 characters. Name and address should not permit null values.
3. Employee type is a fixed length character column with a length of 4. The foreign key constraint must be enforced.
ROSTER(roster id#, day, start time, engine id#)
FIRE(fire id#, address, intensity, person name, estimated damage, time taken)
4. Person name should use a variable length character column with a maximum length of 40 characters.
5. Estimated damage should be a number with a precision of 11 and a scale of 2.
2. Engine id should use a fixed length character column with a length of 7.
3. Despatch time and return time will use non-null date datatypes. A check constraint should be used to make sure that the return time is later than the despatch time.
SECTION B (25 marks)
ORDER_LINE(orderno#, order line number#, item_number#, no_of_items, item_ cost, shipping_date)
Notes:
Display all the client numbers in the ORDER table. Remove duplicates.
Display the order number and client number from the ORDER table. Output the result in the format. Client <clientno> ordered <orderno>
Display the client name and order date for all orders using the JOIN . . . USING method.
Display the client number, order date and shipping date for all orders where the shipping date is between three and six months after the order date.
Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.
Display the order number, order line number and the shipping date. If the shipping date is null, display the string <not shipped yet>.
Using a set operator, display the client number of all clients who have ever placed an order and whose whose name does not contain the string Sm.
Display the order number, number of lines in the order, total number of items and total value for all orders that have a total value greater than $100
SECTION C (35 marks)
Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server.
You must supply the output for each query. A screen dump will suffice but make sure the output is readable. The output will count for two of the seven marks.
Display the post code and the purchase order number for each purchase order. Sort the output set by postcode.
Display the post code and the total number of purchase orders placed with creditors in that post code.
Adapt your query in (2) above so that only post codes with more than twenty orders are displayed.
Using a set operator, display the creditor number of all creditors who have ever been paid.
Display full details for the creditor/s who has received the single largest payment. Do not use a table join or set operator anywhere in your query.
