Database

Section A Application-Based Question(s) 50 Marks S Question 1 Oracle databases use structured query language (SQL) for writing and querying data. Consider the Table 1 below: Table 1: Employees eid sname Init job dept dob Office location 2001 Stones Admin HR 3-12-1980 JHB 2002 Johnson K Salesrep Sales 12-4-1991 Midrand 2003 Nkosi Manager Accounting 4-6-1978 Pretoria 2004 Dludla H Trainer Training 20-6-1985 JHB 2005 Dlamini J Manager HR 7-9-1976 Pretoria 2006 Pale Admin HR 16-3-1997 Midrand 2007 Peter Trainer Training 14-4-1969 JHB F M Use the above information and write SQL queries to answer the following questions 1.1 Print a list of all workers, ordered by job and then by age (from young to old). (3 Marks) 1.2 Except for Peter M., display all the workers' names and initials. (3 Marks) 1.3 Determine the number of workers born before 1990, as well as their job and dates of birth. (3 Marks) 1.4 Determine all the employees that does not work for the department of sales. (3 Marks) 1.5 Determine all the employees that have people working under them. (3 Marks) [Sub Total 15 Marks] Question 2 Use the tables (Courses and Lessons below and the employees table in question 1. Write SOL 10 answer the following questions 21 Provide the course code and description for any courses having a four-day duration (2 Marks 22 Which courses have been held in JHB andlor in Pretoria? (3 Marks) 23 Which employees took both the JAV and the DATA courses? 5 Marks) 24 Print a list of all workers who did not attend the JAV training 15 Marks) [Sub Total 15 Marks Courses Code Description Duration Campus DATA Database 4 days management systems JAV Javascript 7 days Midrand MATH Mathematics 4 days Pretoria ISY Information 3 days Pretona systems HTML Hyper Tek 4 days Markup Language PMT Project 7 days Midrand management Lessons Attendant Subject 2001 DATA JAV 2002 DATA 2003 SAV 2004 SAV DATA 2005 MATH 2006 PVT 2007 ISY 15 Marks) Question 3 3.1 Name and define five SOL aggregate operators 32 Write SQL queries to show how the five SOL aggregate operators are used 33 Consider the table named "Students below. 15 Marks) ITD1B2 Bt. - Intl Online Assessment Paper Book 2021 V10 Page 3 of 7 number name 2175 Jackson 2199 Brenda 2163 Martins Students email course rating 2175@eduvos.com Mathematics 7 2199@eduvos.com English 2163@eduvos.com History a b. Write a query to show only the number and the name in the table including rating 10, and show the results. (5 Marks) Write a query to display the entire contents of the table using the cause WHERE rating