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
CS405 Assignment 2 Solution and Discussion
CS405 - Database Programming using Oracle 11g
2
Posts
1
Posters
563
Views
1
Watching
-
Re: CS405 Assignment 2 Solution and Discussion
Assignment No. 02SEMESTER Spring 2020
CS405- Database Programming Using Oracle 11g Total Marks: 20
Due Date: 19-06-2020Objective:
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: 20Question 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 SargodhaImplement 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 20Lectures 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 -
Please share idea
PAK VS BAN Live
File Sharing
