CS405 - Database Programming using Oracle 11g

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: 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 cs405@vu.edu.pk 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

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: 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 cs405@vu.edu.pk 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

Assignment No. 03 SEMESTER Fall 2019 CS405- Database Programming Using Oracle 11g Total Marks: 20 Due Date: 21-01-20 Objective: The assignment has been designed to enhance your knowledge about how to use: • Loops and implementing Nested Loops • Cursors and it’s types • Cursor Implementation 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 cs405@vu.edu.pk Marks: 20 Consider the following table containing columns to answer the questions: Employee(emp_ID, emp_name, emp_rank, emp_scale, emp_experience, emp_salary) Question 1: [Screenshot =2.5 Marks, Code = 2.5 Marks] Write a PL/SQL block to display the reverse of maximum experience from Employee table. Question 2: [Screenshot = 7.5 Marks, Code = 7.5 Marks] Write a PL/SQL block using cursors to update the salary of employees for the following cases. a) If employee scale (16 or less than 16) [Screenshot = 2.5 Marks, Code = 2.5 Marks] 20 % increase in the salary. b) If employee Scale (17 or above) [Screenshot = 2.5 Marks, Code = 2.5 Marks] 10% increase in salary. Also display the emp_ID, emp_scale and emp_updated salary. [Screenshot = 2.5 Marks, Code = 2.5 Marks] Lectures Covered: This assignment covers WEEK# 9-10 Note: You are required to paste code and screenshots of your programs in a Word File, and submit that file on or before January 21, 2020 GOOD LUCK

Database Programming Using Oracle 11g – CS405 Semester: Fall 2019 Lectures Covered : 01-07 Total Marks: 20 Due Date: 14/11/2019 Objectives: Students will be able to learn: • Entity Relationship Diagram • Structured Query Language • How to write SQL Statement Instructions: Please read the following instructions carefully before submitting assignment: It should be clear that your assignment will not get any credit if:  The assignment is submitted after due date.  The assignment is submitted via email.  The assignment is copied from Internet or from any other student.  The submitted assignment does not open or file is corrupt.  It is in some format other than .doc/docx. Note: All types of plagiarism are strictly prohibited. For any query about the assignment, contact at Question no 1 You are required to draw Entity Relationship Diagram of the following scenario to show how you would track this information. Show entity names, primary keys, attributes for each entity, relationships between the entities and cardinality. Scenario “A shipping company named ABC shippers keeps up-to-date information upon the processing and current location of each shipped item. For record keeping, following product information is stored: product name, product ID and quantity. Products are made up of many components and each component can be supplied by one or more suppliers. The following component information is stored in databases for record keeping: component ID, name, description, suppliers, and products in which they are used.” Keep the following assumptions in mind while drawing ERD I. A supplier can exist without providing components. II. A component does not have to be associated with a supplier. III. A component does not have to be associated with a product. Not all components are used in products. IV. A product cannot exist without components. Question no 2 Consider the following table and its attributes: Employee (emp_id, emp_name, designation, phone_no, address, joining_date, salary, department) You are required to use this table to write SQL statements for the following cases: a) Display the name and annual salary of all employees. b) Display the name of all employees whose name is at least 4 characters long. c) Display the name and department of the employees who earns the maximum salary. d) Display data for all employees who earn between 10000 and 20000. e) Count no. of employees working in each department.

Assignment 01
 Semester: Spring 2019 
Database Programming Using Oracle 11g – CS405 Weeks Covered : 01 - 03 Total Marks: 20 Due Date: 17-05-19 Objectives: Students will be able to learn: • Relationships among tables • Structured Query Language Basics • SQL Joins Instructions: Please read the following instructions carefully before submitting assignment: It should be clear that your assignment will not get any credit if: ♣ The assignment is submitted after due date. ♣ The assignment is submitted via email. ♣ The assignment is copied from Internet or from any other student. ♣ The submitted assignment does not open or file is corrupt. ♣ It is in some format other than .doc/docx. Note: All types of plagiarism are strictly prohibited. For any query about the assignment, contact at CS405@vu.edu.pk Scenario Suppose a Software Development Company is maintaining Employee’s Basic Information, Attendance Record and Leave Record. The following three tables have been created and their attributes are also given. Employee (emp_id, emp_name, designation, phone_no, address, joining_date, basic_salary, net_salary, department) Attendance (emp_id, date, duty_time_in, duty_time_out, emp_time_in, emp_time_out, working_hours) Leave (emp_id, leave_id, leave_date, leave_type, leave_status, total_leaves_allowed, leaves_availed) Question: You are required to use these relations to write SQL statements for the following cases: Display the list of names of all employees who are having either ‘S’ in the name or the name should end with ‘I’ and designation is ‘Lecturer’. Display the net salary paid to the Lecturers of all departments where net salary is more than 85000 and records are order by salary. Display information of all those employees who are working in CS dept and their minimum salary is greater than 70000. Display the employee name, employee ID and total leaves availed by employee in last 6 months. Display the empID, name and designation of employees having same joining date. Spring 2019_CS405_1.docx