Assignment 2 CSC343 Fall 2016
Due November 11th, 11:59pm sharp.
Learning Goals
By the end of this assignment you should have:
1. good understanding of SELECT statement of SQL
2. writing SQL queries
3. updating / inserting / deleting tuples from or into an existing instance
4. good understanding of SQL DDL commands including addition / deletion of constraints
5. writing PL/SQL blocks, stored procedures, functions and package
6. writing PL/SQL triggers
Instructions
Group work: You may work in groups of 2.
Submission: Your assignment must be typed – handwritten assignments will not be marked. You may use any editor you like. Submit your assignment on the portal (blackboard). Marking scheme: 24 + 10 + 10 + 10 + 10 = 64 marks
Question 1 (SQL): Run the scripts in create_insert_assn2_data.zip file for this question.
Given a relational schema as shown below, write SQL queries to answer the following. Expected results for the given instance are shown for your convenience.
PRODUCT (maker, model, type)
PC (model, speed, ram, hd, price)
LAPTOP (model, speed, ram, hd, screen, price) PRINTER (model, color, type, price)
a. What PC models have a speed of at least 3.00?
Result:
b. Which manufacturers make laptops with a hard disk of at least 100GB?
Result:
c. Find the model number and price of all products (of any type) made by manufacturer B. Result:
d. Find the model number of all color laser printers. Result:
model
1005
1006
1013
maker
E
A
B
F
G
model
price
1004
649
1005
630
1006
1049
2007
1429
model
3003
3007
e. Find those manufacturers that sell Laptops, but not PCs. Result:
f. Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once e.g. if (I,j) is listed, do not list (j,i).
Result:
g. Find the average price of PCs and laptops made by manufacturer ‘D’. Result:
AVG_PRICE ———–
730
h. Find the manufacturers that make at least 3 different models of PC. Result:
MAKER —–
A
B
D
E
i. List all manufacturers that make all models (3001 – 3007) of a printer.
j. For each laptop made by manufacturer B, add one inch to the screen size and subtract $100
from the price.
k. Delete all manufacturers who do not make a colored printer.
Question 2 (PL/SQL block using cursors) – Run setup_script_assn2_q2.sql for this question.
This script creates 3 tables (emp, dept, dept_stat) and a sequence (seq_assn2_2). The 2 tables emp and dept are populated with data (20 in emp and 6 in dept).
Maker
F
G
PC1.model
PC2.model
1004
1012
Currently, there are no records in table dept_stat.
Write a PL/SQL block using a cursor to populate some statistics into the “dept_stat” table using tables emp and dept.
CHATUR18 @ XE > desc dept_stat
Name Null? Type ————————————————————————————————————————-
DNAME TOTAL_EMP TOTAL_SAL A VG_SAL SEQ_NUM COMMENTS
NOT NULL
VARCHAR2(25) NUMBER(3) NUMBER(8,2)
NUMBER(8,2) NUMBER(3)
V ARCHAR2(50)
Description of columns:
DNAME is the department name.
TOTAL_EMP – total number of employees in each department.
TOTAL_SAL – total salary for each department
AVG_SAL – average salary for each department
SEQ_NUM – number assigned by the system (to populate the column seq_num , use the sequence seq_asssn2_2 created by the setup script).
COMMENTS – comment
If the total number of employees in a department is less than or equal to 3, Column comments gets its values as ‘Cannot insert – Too few employees’. In such cases, TOTAL_SAL, AVG_SAL and SEQ_NUM get null values.
In addition to populating the table dept_stat, your program must display messages on the screen as shown below (this output is for the given instance of dept and emp):
Data 1 – Cannot insert – too few employeees Data 2 – Cannot insert – too few employeees Data 3 – Successful insertion
Data 4 – Successful insertion
Data 5 – Successful insertion
Data 6 – Cannot insert – too few employees
PL/SQL procedure successfully completed.
After successful execution of your program, dept_stat must have the following rows:
Result:
Question 3 (stored procedure):
Create a table called POSSIBLE_IDS with 1 field called ID (VARCHAR2(60)).
Create a procedure called generate_id that takes the first name, last name and dob of an employee (stored as a varchar2), and generates a list of possible login ids for the person by using combinations of first name, lastname, age and the sun sign of the person and stores the list in the table POSSIBLE_IDS !
You can use a built-in function MONTHS_BETWEEN and ROUND to get the age. For example CHATUR18 @ XE > select round(MONTHS_BETWEEN(SYSDATE,’12-JAN-1976′)/12) age
from dual;
AGE ——– 41
For sun-signs, you may use
Aries – March 21 – April 20
Taurus – April 21 – May 21
Gemini – May 22 – June 21
Cancer – June 22 – July 22
Leo – July 23 -August 21
Virgo – August 22 – September 23
Libra – September 24 – October 23
Scorpio – October 24 – November 22
Sagittarius – November 23 – December 22
Capricorn – December 23 – January 20
Aquarius – January 21 – February 19
Pisces – February 20- March 20
Sample Input/Output :
execute generate_id(‘Ash’,’Bagley’,’12-JAN-1976′);
generates the following ids:
Ash_Bagley
Ash_Bagley_41
Ash_41
Bagley_41
Ash_Bagley_Capricorn
Bagley_Capricorn
Ash_Capricorn
Ash_Bagley_Capricorn_41
SELECT * FROM POSSIBLE_IDS; should now display 8 rows of such ids generated by your procedure.
Question 4 (Packages, procedures and functions) – run scripts create_sp.sql and insert_sp.sql for this question
Given below is the command to create a package specification called sp_specs. You have to write a package body for this specification. Then test both the package specification and body by using an anonymous PL/SQL block.
CREATE OR REPLACE PACKAGE SP_SPECS IS
— this procedure inserts a tuple into table SP using the values of S_NO. P_NO and Quantity PROCEDURE INSERT_SP( S_NO SP.SNO%TYPE ,
P_NO SP.PNO%TYPE, QUANTITY SP.QTY%TYPE);
— this procedure finds suppliers who supply exactly 2 parts and outputs the result as ‘TRUE’ if S_NO supplies exactly 2 parts and FALSE otherwise. It also prints a message. For example, if S_NO=’S2’, then it prints ‘JONES supplies exactly 2 parts’, whereas if S_NO =’S1’, it prints ‘This supplier does not supply 2 parts’.
PROCEDURE GET_SNAME_2PARTS( S_NO SP.SNO%TYPE , RESULT OUT VARCHAR2);
— this function returns the max quantity supplied by a supplier
FUNCTION FIND_MAX_QTY_SUPPLIED_BY_A_SUPPLIER( S_NO SP.SNO%TYPE) RETURN NUMBER ;
— this function finds an returns the sname of a supplier, given his or her sno
FUNCTION FIND_SNAME_GIVEN_SNO( S_NO SP.SNO%TYPE) RETURN VARCHAR2 ;
— this procedure takes no input nor gives back any output. It simply prints the suppliers and the quantity supplied by them (as shown below)
Sample output:
PROCEDURE REPORT_SUPPLIERS(); END SP_SPECS;
Question 5 (Triggers):
a. Write a trigger for table S called ensure_case that converts the sname and city to uppercase before they are inserted or updated => if the insert statement given by a user is
Insert into S values (‘S8’, ‘HARRY’, 20, ‘WINDSOR’) ;
This trigger must convert the name to HARRY and city to WINDSOR before actually inserting them.
b.
Write a trigger for table S so that anytime the name of a supplier is changed, there is a message stating the change. For example, an update statement such as the one given below must be followed by a message ‘Supplier name JONES has changed to HARRY’:
UPDATE S
SET SNAME = ‘HARRY’ WHERE SNAME = ‘JONES’ 1 row updated.