[SOLVED]- CSC 352 / 452: DATABASE PROGRAMMING ASSIGNMENT #3 (60 POINTS)
5 (100%) 1 vote[s]

CSC 352 / 452: DATABASE PROGRAMMING
ASSIGNMENT #3 (60 POINTS)

Due on Tuesday, 2/2/2016 at 11:59PM

Unless prior arrangements are made, homework turned in late but within 24 hours of the due time will be graded at 75% credit, homework turned in between 24 and 48 hours will be graded at 50% credit, and homework turned in later than 48 hours will not be accepted.

Please note that only TEXT files will be accepted. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept.

• If you modified the DEPARTMENT and EMPLOYEE tables created in Assignment #1, you need to delete and re-populate them.
• Do not try to use complicated queries/cursors(e.g., joins) to get the results.You can use multiple PL/SQL statements to get the results easily.
• You cannot use hard-coded values (e.g., IF department_name = ‘MARKETING’ THEN ……) in your programs.
• You are not allowed to create temporary tables, views, functions, or procedures.
• The EXCEPTION section is NOT allowed in your programs.
• Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file.
1) (CSC 352 – 25Points | CSC 452 – 15 Points)

The table popular_names consists of some popular given names for male and female babies born during the years 1915-2014. Rank 1 is the most popular, rank 2 is the next most popular, and so forth.

Create and populate thetable popular_namesas described below.

CREATE TABLE popular_names
( RANK NUMBER(3),
SEX CHAR CONSTRAINT ck_sex CHECK (SEX in (‘M’,’F’)),
GIVEN_NAME VARCHAR2(20) NOT NULL,
NUMBER_OF_OCCURRENCES NUMBER NOT NULL,
CONSTRAINT pk_popular_names PRIMARY KEY (RANK, SEX));
/
INSERT INTO popular_names VALUES (1,’F’, ‘Mary’, 3569442);
INSERT INTO popular_names VALUES (1, ‘M’, ‘James’, 4855084);
INSERT INTO popular_names VALUES (2, ‘M’, ‘John’, 4712974);
INSERT INTO popular_names VALUES (2,’F’, ‘Patricia’, 1566449);
INSERT INTO popular_names VALUES (3,’F’, ‘Jennifer’, 1462389);
INSERT INTO popular_names VALUES (15, ‘F’, ‘Lisa’, 963349);
INSERT INTO popular_names VALUES (3, ‘M’, ‘Robert’, 4648775);
INSERT INTO popular_names VALUES (6, ‘M’, ‘David’, 3540971);
INSERT INTO popular_names VALUES (6,’F’, ‘Barbara’, 1418293);
INSERT INTO popular_names VALUES (10, ‘M’, ‘Thomas’, 2188254);
INSERT INTO popular_names VALUES (15, ‘M’, ‘Anthony’, 1382783);
INSERT INTO popular_names VALUES (9,’F’, ‘Jessica’, 1039233);
INSERT INTO popular_names VALUES (7,’F’, ‘Susan’, 1107596);
INSERT INTO popular_names VALUES (30, ‘M’, ‘Ryan’, 899850);
INSERT INTO popular_names VALUES (33, ‘F’, ‘Anna’, 682104);
INSERT INTO popular_names VALUES (29, ‘F’, ‘Rebecca’, 728055);
INSERT INTO popular_names VALUES (34, ‘M’, ‘Jacob’, 863998);
INSERT INTO popular_names VALUES (33, ‘M’, ‘Eric’, 864935);
INSERT INTO popular_names VALUES (18, ‘M’, ‘Steven’, 1271700);
INSERT INTO popular_names VALUES (61, ‘F’, ‘Kelly’, 468590);
COMMIT;

Write aPL/SQL anonymous blockthat accepts a positive integer nfrom the user input and displays all rows(Sex, Given Name, Sex, Rank, and Number of Occurrences)having rank ≤ nin the popular_names table.Sort your outputin ascending orderby sex (Female, Male), and then given name.

The sex column (“M” or “F”) must be displayed as “Male” or “Female” in your output. You will lose 5 points if you fail to do so. (Hint: you can use IF…THEN…ELSE/CASE/DECODEstatement/function to convert one string to another.)

To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9, plus (+) or minus (-) sign, and Enter. But, you need to check whether the user input is a positive number or not.

Please test your program. The output of your program must match the following (ONE rank per line):

Case 1a)

Output:

Case 1b)

Output:

Case 2)

Output:

Case 3)

Output:

2) (CSC 352 – 35Points | CSC 452 – 20 Points)

Based on the tables created in Assignment #1, write aPL/SQL anonymous block to perform the following tasks:

• Display the number of employees in the company.
• Displaythe maximum/minimum/averagesalary for the company.
• Display 50% of the maximum salary for the company.
• For each department whose average salary is greater than 50%of the maximum salary for the company, displaythe name of the department, the number of employees in the department, and themaximum/minimum/average salary for the department.

You must display the maximum/minimum/averagesalary with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56).Sort your output in ascending orderbydepartment name.

Hint: IFv_dept_avg_sal> 0.50 * v_comp_max_sal THEN ……

Please test your program. The output of your program must match the following:

3) (CSC 452only – 25 points)

The table AUTO_SERVICEconsists of auto repair and maintenance information. Create and populate the table AUTO_SERVICE by using the following SQL statements.

CREATE TABLE auto_service
( VIN VARCHAR2(18),
SERVICE_ID VARCHAR2(10),
SERVICE_DATE DATE,
DESCRIPTION VARCHAR2(50),
SERVICE_PRICE NUMBER(7, 2),
CONSTRAINT pk_auto_service PRIMARY KEY (VIN, SERVICE_ID));
/
INSERT INTO auto_serviceVALUES(‘ZZZZ21’, ‘NAP0010189′, ’13-OCT-03’, ‘—‘, 33.98);
INSERT INTO auto_serviceVALUES(‘ZZZZ21’, ‘CHI0010123′, ’31-DEC-04’, ‘—‘, 289.92);
INSERT INTO auto_serviceVALUES(‘XYZ111’, ‘JOL0200080′, ’20-OCT-06’, ‘—‘, 1230.23);
INSERT INTO auto_serviceVALUES(‘ABC123’, ‘NAP1000890′, ’12-JAN-08’, ‘—‘, 23.09);
INSERT INTO auto_serviceVALUES(‘MMM789’, ‘NAP1000891′, ’12-JAN-08’, ‘—‘, 44.01);
INSERT INTO auto_serviceVALUES(‘ZZZZ21’, ‘AUR0002456′, ’12-FEB-08’, ‘—‘, 1020.97);
INSERT INTO auto_serviceVALUES(‘QWERT1’, ‘NAP1000990′, ’12-FEB-08’, ‘—‘, 99.09);
INSERT INTO auto_serviceVALUES(‘QWERT1’, ‘NAP1001890′, ’18-FEB-09’, ‘—‘, 39.09);
INSERT INTO auto_serviceVALUES(‘ABC123’, ‘CHI0018089′, ’12-OCT-09’, ‘—‘, 46.00);
INSERT INTO auto_serviceVALUES(‘ZXCV88’, ‘CHI0018189′, ’13-OCT-09’, ‘—‘, 46.98);
INSERT INTO auto_serviceVALUES(‘ZXCV88’, ‘JOL0300080′, ’20-MAR-11’, ‘—‘, 123.25);
INSERT INTO auto_serviceVALUES(‘ABC123’, ‘NAP1011123′, ’31-DEC-11’, ‘—‘, 289.91);
INSERT INTO auto_serviceVALUES(‘QWERT1’, ‘JOL0400090′, ’18-MAR-12’, ‘—‘, 123.23);
INSERT INTO auto_serviceVALUES(‘ZXCV88’, ‘NAP1014123′, ’31-DEC-12’, ‘—‘, 289.90);
INSERT INTO auto_serviceVALUES(‘ABC123’, ‘CHI0031199′, ’30-MAR-13’, ‘—‘, 66.68);
INSERT INTO auto_serviceVALUES(‘TAX999’, ‘CHI0031208′, ’30-MAR-13’, ‘—‘, 20.91);
INSERT INTO auto_serviceVALUES(‘WWW000’, ‘CHI0031298′, ’04-APR-13’, ‘—‘, 1000.01);
INSERT INTO auto_serviceVALUES(‘ZXCV88’, ‘AUR0700979′, ’04-APR-13’, ‘—‘, 66.67);
INSERT INTO auto_serviceVALUES(‘XYZ111’, ‘JOL0400080′, ’20-SEP-14’, ‘—‘, 2200.10);
INSERT INTO auto_serviceVALUES(‘WWW000’, ‘CHI0041299′, ’01-SEP-14’, ‘—‘, 40.01);
COMMIT;

(A vehicle identification number, commonly abbreviated to VIN, is a unique code used by the automotive industry to identify individual motor vehicles.)

To avoid complicating issues, we assume that:

• For each VIN, there can be at most one service on any day.
• For each VIN, the service price is unique.

Write a PL/SQL anonymous blockthat performs the following task:

• Foreach VINin the AUTO_SERVICE table, if the first service date is in the period 02/01/2008 (01-FEB-08) to 03/31/2013 (31-MAR-13), the following items along with the VINare displayed:
o The first service date
o The last service date and the service price for the last service
o The minimum service price and the service date for the minimum service price
o The maximum service price and the service date for the maximum service price
o The total number of services
o The total service prices for all services

• You must display the service price with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56).Sort your output in ascending orderby VIN.

Hints: First service date – MIN(SERVICE_DATE)
Last service date – MAX(SERVICE_DATE)
ORDER BY VIN

The output of your program must match the following (one VIN per line):

Please submit a text file containing all the source codes to D2L before or on due date.

Optional Question

Just for fun (no credit, no extra credit, no need to submit, just for if you are a curious person and like database programming).

In a bug tracking database, there is a table called BUGS. The table has several columns: BUG_ID, REPORTED_DATE, DESCRIPTION, PRIORITY, ASSIGNED_TO, CLOSED_DATE, and NOTE.

Create and populate the BUGStable.

CREATE TABLE bugs
(
BUG_ID NUMBER PRIMARY KEY,
REPORTED_DATE DATE NOT NULL,
DESCRIPTION VARCHAR2(20),
PRIORITY NUMBER(2),
ASSIGNED_TO VARCHAR2(10),
CLOSED_DATE DATE,
NOTE VARCHAR2(20)
);

INSERT INTO BUGS VALUES (1230, ’25-APR-14’, NULL, 3, ‘Team 3′, ’28-APR-14′, NULL);
INSERT INTO BUGS VALUES (1231, ’29-APR-14’, NULL, 1, ‘Team 1′, ’29-APR-14′, NULL);
INSERT INTO BUGS VALUES (1232, ’03-MAY-14’, NULL, 1, ‘Team 1′, ’03-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1233, ’03-MAY-14’, NULL, 1, ‘Team 3′, ’08-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1234, ’04-MAY-14’, NULL, 2, ‘Team 5′, ’15-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1235, ’04-MAY-14’, NULL, 2, ‘Team 1′, NULL, NULL);
INSERT INTO BUGS VALUES (1236, ’05-MAY-14’, NULL, 1, ‘Team 2′, ’06-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1237, ’05-MAY-14’, NULL, 3, ‘Team 3′, ’10-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1238, ’09-MAY-14’, NULL, 4, ‘Team 5′, ’16-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1239, ’09-MAY-14’, NULL, 5, ‘Team 6′, NULL, NULL);
INSERT INTO BUGS VALUES (1240, ’12-MAY-14’, NULL, 5, ‘Team 2′, ’30-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1241, ’12-MAY-14’, NULL, 1, ‘Team 1′, ’20-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1242, ’13-MAY-14’, NULL, 4, ‘Team 4′, ’25-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1243, ’14-MAY-14’, NULL, 4, ‘Team 3′, ’01-JUN-14′, NULL);
INSERT INTO BUGS VALUES (1244, ’14-MAY-14’, NULL, 2, ‘Team 4′, ’25-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1245, ’20-MAY-14’, NULL, 2, ‘Team 4′, NULL, NULL);
INSERT INTO BUGS VALUES (1246, ’22-MAY-14’, NULL, 2, ‘Team 4′, ’25-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1247, ’25-MAY-14’, NULL, 2, ‘Team 1′, ’29-MAY-14′, NULL);
INSERT INTO BUGS VALUES (1248, ’30-MAY-14’, NULL, 1, ‘Team 1′, ’01-JUN-14′, NULL);
INSERT INTO BUGS VALUES (1249, ’05-JUN-14’, NULL, 1, ‘Team 2′, ’07-JUN-14’, NULL);
COMMIT;

“Open Bugs” – A bug is considered open on a given day if (1) its “REPORTED_DATE” is on or before that day, and (2) its “CLOSED_DATE” is on or after that day (or is unknown (NULL)). For example, we have 5 open bugs on 5/5/2014.

Write a PL/SQL anonymous block that generates a report to show the number of open bugsfrom 5/1/2014 through 5/31/2014. Sort your output by the number of open bugs in descending order and then by the date in ascending order. At the end of the report, the maximum number of open bugs on a single dayis displayed. Assume that there were no open bugs on 4/30/2014-14.

The output of your program should match the following:

Number of Open Bugs Date
9 14-MAY-14
9 15-MAY-14
9 25-MAY-14
8 16-MAY-14
8 20-MAY-14
8 22-MAY-14
8 23-MAY-14
8 24-MAY-14
7 13-MAY-14
7 17-MAY-14
7 18-MAY-14
7 19-MAY-14
7 21-MAY-14
6 12-MAY-14
6 26-MAY-14
6 27-MAY-14
6 28-MAY-14
6 29-MAY-14
6 30-MAY-14
5 05-MAY-14
5 06-MAY-14
5 09-MAY-14
5 10-MAY-14
5 31-MAY-14
4 07-MAY-14
4 08-MAY-14
4 11-MAY-14
3 04-MAY-14
2 03-MAY-14
0 01-MAY-14
0 02-MAY-14
—————————————
The maximum number of open bugs on a single day is 9.
There were 9 open bugs on 14-MAY-14.
There were 9 open bugs on 15-MAY-14.
There were 9 open bugs on 25-MAY-14.