CS405 Assignment 2 Solution and Discussion


  • Cyberian's Gold

    Assignment No. 02
    

    SEMESTER Fall 2019
    CS405- Database Programming Using Oracle 11g Total Marks: 20
    Due Date: 29-11-19

    Objective:
    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:

    1. The assignment will not be accepted after due date.
    2. Assignment will not be accepted via email.
    3. Zero marks will be awarded to the assignment that does not open or the file is corrupt.
    4. The assignment file must be in MS word (.doc) file format; Assignment will not be accepted in any other format.
    5. 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


  • Cyberian's Gold

    100% Solved:
    Q.1 Solution

    SELECT 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;
    

  • Cyberian's Gold

    100% Solved:
    Q.1 Solution

    SELECT 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;
    


Quiz 100% Result Quiz 100% Result
| |