Scribd
Upload
17 views4 pages

NVQ 5 ICT Database System II Guide

The document outlines a sample database structure for an NVQ 5 in ICT-Database System II course, detailing tables for employees, projects, jobs, and order forms. It includes a series of questions that require SQL commands and procedures related to database management, such as updating charges, creating foreign keys, and retrieving employee information. The document concludes with a prompt for discussing the use of triggers in database programs.

Uploaded by

softwatchinfosys8
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views4 pages

NVQ 5 ICT Database System II Guide

The document outlines a sample database structure for an NVQ 5 in ICT-Database System II course, detailing tables for employees, projects, jobs, and order forms. It includes a series of questions that require SQL commands and procedures related to database management, such as updating charges, creating foreign keys, and retrieving employee information. The document concludes with a prompt for discussing the use of triggers in database programs.

Uploaded by

softwatchinfosys8
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

NVQ 5 in ICT-Database System II – Part Time

Relational Model – Db_company

EMPLOYEE EMP_SSN EMP_LNAME EMP_FNAME JOB_CODE


E1 PERERA SUNIL 101
E2 SILVA SAMAN 104
E3 MUDALIGE VIKUM 101
E5 PRIYANTHA MALITH 103
PROJECT PRJ_NUM PRJ_NAME EMP_SSN
2001 ITX1 E1
2003 ITX2 E3
2004 ITY1 E3
2006 ITY3 E2

JOB JOB_CODE JOB_NAME JOB_HOUR_CHARGE


101 DEVELOPMENT 1000.45
102 TESTING 800.50
103 MANAGING 11000.30
104 BUDGETING 900.60

ORDERFORM ORD_N ORD_DATE ORD_ ORD_CHARG ORD_CHA PRJ_NUM EMP_SSN


UM HOU E_HOURS RGE
RS
51 2012-02-03 100 1000.00 0 2003 E3
52 2012-05-01 80 5000.40 0 2003 E2
53 2012-04-03 50 1200.12 0 2001 E5
54 2012-04-16 30 2000.30 0 2004 E5

Using the given sample database, Answer on following questions. No need to implement the database
or any data tables.

1
1. How do you update ORD_CHARGE column, if it is calculated using the formula below.
ORD_CHARGE = ORD_CHARGE_HOURS * ORD_HOURS (10 Marks)

2. Write SQL command for creating foreign key for the Project Table (10 Marks)

3. Write SQL command to Select Employees who is having the least charge payment per hour in
each project. (10 Marks)

4. Write a subquery to return job name of having second highest job_hour_charge?


(10 Marks)

2
5. Get a list of employees who work for more than one project, using JOIN statement.
(10 Marks)

6. ADD a view (view_order_form) for processing the entries including

order date
order hours
order charge hours
project name
(10 Marks)

7. Propose an index (using SQL)to search order details more efficiently .

(10 Marks)

3
8. Create a simple procedure p_OrderProgreass ( empid ) to give information on manipulating
number of ordered has been processed by each employees.(you may input employee number as
an input parameter (IN parameter) )

The procedure should display following details


Employee id -
Number of orders taken -
(10 Marks)

9. How do you re-Write the above program to return the number of order taken when input the
employee id use Function name as f_OrderProgreass ( empid ) .

(10 Marks)

10. List out two important situation where triggers are use useful for a database program.
(10 Marks)

--- END ---

You might also like