Assignment 2 SQL CSC343 Fall 2016

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:

5. Find those manufacturers that sell Laptops, but not PCs. Result:
6. 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:
7. Find the average price of PCs and laptops made by manufacturer ‘D’. Result:

   AVG_PRICE ———–

   730

8. Find the manufacturers that make at least 3 different models of PC. Result:

   MAKER —–
   A
   B

   D

   E

9. List all manufacturers that make all models (3001 – 3007) of a printer.
10. For each laptop made by manufacturer B, add one inch to the screen size and subtract $100

    from the price.

11. 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).

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.