CS405 Assignment 2 Solution and Discussion

[zaasmi]

Re: CS405 Assignment 2 Solution and Discussion

Assignment No. 02

SEMESTER Spring 2020
CS405- Database Programming Using Oracle 11g Total Marks: 20
Due Date: 19-06-2020

Objective:
The assignment has been designed to enhance your knowledge about how to use / do:
• Implementing Data Manipulation Language (DML) in PL/SQL
• Implementing Structured Query Language (SQL) in PL/SQL
• Implementation of CASE Statement
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  

Question 1:
Create / Consider the following tables: [Screenshot = 2.5 Marks, Code = 7.5 Marks]
SALESRESP (empl_num, name, age, designation, city, quota, joining_date, sal, commision)
OFFICES (office_id, city, region, mgr, target, sales)
You are required to:

Write a PL/SQL block to increase salary by 10% as healthcare bonus of all those sales persons who are working in the organization for more than 8 years and their salary is less than 50,000.

Question 2:
Consider the following table: [Screenshot = 2.5 Marks, Code = 7.5 Marks]
Student ID FirstName Age City
1 Asad 14 Karachi
2 Rehmat 21 Lahore
3 Usman 13 Multan
4 Ali 25 Faisalabad
5 Atif 20 Islamabad
6 Babar 22 Karachi
7 Noman 24 Multan
8 Aakif 17 Lahore
9 Faiq 19 Lahore
10 Majid 15 Sargodha

Implement CASE statement to find the following resulting table: (You have to write SQL using CASE statement)

Student ID City Comments
1 Karachi Age is less than 15
2 Lahore Age is greater than 20
3 Multan Age is less than 15
4 Faisalabad Age is greater than 20
5 Islamabad Age is equal to 20
6 Karachi Age is greater than 20
7 Multan Age is greater than 20
8 Lahore Age is greater than 15
9 Lahore Age is greater than 20
10 Sargodha Age is equal 20

Lectures Covered: This assignment covers WEEK# 4-6
Note: You are required to paste code and screenshots of your programs in a Word File, and submit that file on or before June 19, 2020
GOOD LUCK

[zaasmi]

Please share idea