Assignment 01 Semester: Spring 2019 Database Programming Using Oracle 11g – CS405
-
Assignment 01 Semester: Spring 2019 Database Programming Using Oracle 11g – CS405
Weeks Covered : 01 - 03Total 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 [email protected]
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.
-