Assignment No. 02
SEMESTER Fall 2019
CS405- Database Programming Using Oracle 11g Total Marks: 20
Due Date: 29-11-19
The assignment has been designed to enhance your knowledge about how to use / do:
• Subqueries in SQL
• SELECT INTO IN PL/SQL
• Implementing SELECT INTO
• DML in PL/SQL
• Implementing DML & SQL into PL/SQL
Please read the note section and following instructions carefully before solving & submitting assignment:
- The assignment will not be accepted after due date.
- Assignment will not be accepted via email.
- Zero marks will be awarded to the assignment that does not open or the file is corrupt.
- The assignment file must be in MS word (.doc) file format; Assignment will not be accepted in any other format.
- Zero marks will be awarded to the assignment if copied (from other student or copied from handouts or internet).
For any query about the assignment, contact at [email protected]
Consider the following tables containing columns to answer the questions:
SALESRESP (empl_num, name, age, designation, city, quota, joining_date, sal, commision)
OFFICES (office_id, city, region, mgr, target, sales)
Question 1: [Screenshot = 5 Marks, Code = 5 Marks]
Write a SQL query to list the sales representatives, whose “quota” are equal to or higher than the “target” of the “Lahore” sales office.
Question 2: [Screenshot = 5 Marks, Code = 5 Marks]
Write a PL/SQL block to decrease commission by 1% of all those sales representatives who have been working in the organization for less than two years and their salary is greater than 50,000.
Lectures Covered: This assignment covers WEEK# 4-5
Note: You are required to paste code and screenshots of your programs in a Word File, and submit that file on or before November 29, 2019