GULF COLLEGE – MUSCAT – SULTANATE OF OMAN
UNDERGRADUATE COMPUTING PROGRAMME
ACADEMIC YEAR: 2019-2020
FRONT COVER
ASSIGNMENT
Module Title: Enterprise Application Development |
|
Module Code: COIS51045 |
|
Method of Assessment : PRACTICAL SQL TIMED TASKS |
|
Weighting: 20% |
Module Credits: 30 |
Level: 5 |
Semester/Teaching block: 2 |
Morning-Session/Evening-Session: both |
Examiner: Ms Indu G |
Additional Information (if any): Answer all the four (4) tasks. Total Marks is 20. Maximum Time Allowed: 2 hours |
Instructions:
After doing each task, you have to copy the screenshot, SQL code and output to a Word file.
Create a folder in desktop with your name and ID (ex: Ahmed11234).
Name the Word file by your name and ID and save it in the desktop folder .
Print the Word file and submit to your teacher.
Task 1. Create Tables with Constraints (5 Marks)
You have to create the following two tables, employee and salary, as per the details given.
You have to add primary keys and foreign key.
Employee |
Eid Number(10) (PK) Ename Varchar2(40) Ephone Number(10) Elocation Varchar2(40) Email Varchar2(40) |
Salary |
Sid Number(10) (PK) Smonth Number (2) Syear Number(4) Basic Number(9,3) HRA Number(9,3) Netsalary Number(9,3) Eid Number(10) (FK) |
Add sample data of at least 3 rows to Employee and Salary tables.
Task 2. JOIN Query (5 Marks)
Develop an SQL query to join the two tables Employee and Salalry, to list the details such as Ename, Eid, Elocation, Basic and HRA. Sort the output in the order of Ename.
Task 3. MIS Sub-Query (5 Marks)
Join the two tables Employee and Salary.
Develop a sub-query to list the details such as eid, ename, ephone, elocation and basic for the employees who get more than average basic salary.
OR
Join the two tables Employee and Salary.
Develop a sub-query to list eid, ename, ephone, elocation and basic for the employees who get less than maximum basic salary.
Task 4. Develop and Test Trigger (5 Marks)
- Create a trigger for table Employee.
- The trigger is for the event – before insert.
- To automatically generate the email to the employee, when a new employee data is inserted.
Ex: if the eid is 12345, then the email can be 12345@gulfcollege.edu.om.
- Save and compile for any errors.
- Test the trigger by adding a new employee with details such as Eid, ename, ephone and elocation. Test the email is automatically added as the output from the trigger.
OR
- Create a trigger for table Salary.
- The trigger is for the event – before insert or update.
- To calculate the Netsalary.
[Hint: Netsalary = Basic + HRA ]
- Save and compile for any errors.
- Test the trigger by adding a new salary record with details such as Sid, Smonth, Syear, basic, HRA and Eid. Test the Netsalary is shown as the output from the trigger.
Marking Scheme
Student ID:
Student Name:
Task 1 Create Tables with constraints (5 Marks) |
Marks Available |
Awarded Mark |
Create two tables using the specification given. |
2 |
|
Set appropriate PK and FK |
1 |
|
Set appropriate data types and sizes |
1 |
|
Test with sample data(at least 3 rows) |
1 |
|
Task 2 JOIN Query (5Marks) |
Marks Available |
Awarded Mark |
Join the two tables |
2 |
|
List the required columns |
1 |
|
Sort the output |
1 |
|
Test the query with output |
1 |
|
Task 3 MIS Sub-Query (5 Marks) |
Mark Available |
Awarded Mark |
Use of Sub-query with aggregate function |
3 |
|
Provide the output |
2 |
|
Task 4 Trigger (5 Marks) |
Mark Available |
Awarded Mark |
Create the appropriate trigger |
3 |
|
Test with sample data |
2 |
|
Total Marks |
20 |