Solved: Enterprise Application Development In Oracle

By |

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)

  1. Create a trigger for table Employee.
  2. 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.

  1. Save and compile for any errors.
  2. 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

  1. Create a trigger for table Salary.
  2. The trigger is for the event – before insert or update.
  • To calculate the Netsalary.

[Hint: Netsalary = Basic + HRA ]

  1. Save and compile for any errors.
  2. 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

 

Answer Preview

Employee Table:

CREATE TABLE EMPLOYEE
(
  EID        NUMBER(10)                         NOT NULL,
  ENAME      VARCHAR2(40 CHAR)                  NOT NULL,
  EPHONE     NUMBER(10)                         NOT NULL,
  ELOCATION  VARCHAR2(40 CHAR)                  NOT NULL,
  EMAIL      VARCHAR2(40 CHAR)                  NOT NULL
);

Salary Table:

CREATE TABLE SALARY
(
  SID        NUMBER(10)                         NOT NULL,
  SMONTH     NUMBER(2)                          NOT NULL,
  SYEAR      NUMBER(4)                          NOT NULL,
  BASIC      NUMBER(9,3)                        NOT NULL,
  HRA        NUMBER(9,3)                        NOT NULL,
  NETSALARY  NUMBER(9,3)                        NOT NULL,
  EID        NUMBER(10)                         NOT NULL
);

MIS Sub-Query:

SELECT e.eid, ename, ephone, elocation, basic 

    FROM employee e LEFT JOIN salary s ON e.eid = s.eid

where basic > (select avg(basic) from salary);

SELECT e.eid, ename, ephone, elocation, basic 

    FROM employee e LEFT JOIN salary s ON e.eid = s.eid

where basic < (select max(basic) from salary);

Get Answer Now


Buy (USD $49.20)

Get Answer Now

Answered
Buy @ USD $49.20