CS405 Assignment 2 Solution and Discussion
-
Assignment No. 02
SEMESTER Fall 2019
CS405- Database Programming Using Oracle 11g Total Marks: 20
Due Date: 29-11-19Objective:
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
Instructions:
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]
Marks: 20
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
GOOD LUCK -
100% Solved:
Q.1 SolutionSELECT NAME FROM SALESRESP WHERE QUOTA >= (SELECT TARGET FROM OFFICES WHERE CITY = 'LAHORE')
Q.2 Solution
DECLARE eno salesresp.empl_num%type; commission salesresp.commision%type; update_commision number(10):=0; BEGIN Select empl_num, commision from SALESRESP where months_between(sysdate, joining_date) < 24 AND sal > 50000; update_commision:=commission-commision*0.01; update salesresp set commission = update_commision; dbms_output.put_line('Updated Commision ' || commision); END;