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
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
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]
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.
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
Please share idea