HIT234_Midterm_Exam_2022_converted

.doc

School

Macquarie University *

*We aren’t endorsed by this school

Course

203

Subject

Business

Date

Apr 3, 2024

Type

doc

Pages

9

Uploaded by chocochally

Report
Business Intelligence and Data Mining HIT234 Midterm Examination 28 th April 2022 9:00am – 11:15am IMPORTANT: Answer All questions in the spaces provided after each question Answer ALL three questions Question 1: Vehicle Database SQL statements (16 points, each question is 2 points) Using the vehicle database in Appendix A, write SQL statements for each of the following queries. a) Write an SQL statement to show the name of Employee . SELECT FirstName, LastName FROM Employee Table; b) Find the rego numbers of cars that were taken on or before 2/09/2011. SELECT Reg No FROM Employee Vehicle Table WHERE Date_Taken < =‘2011-09-02’ c) Find the cars that were used by Employees in the administration department on 3-9- 2011. SELECT Make, Model FROM Vehicle,WHERE Employee_Vehicle ON Vehicle.Reg_No = Employee Vehicle Table.Reg_No INNER JOIN Employee ON Employee_Vehicle Table.Employee_No = Employee Table.Employee_No WHERE Department = 'Business' AND Date_Taken = '3-9-2011' OR Date_Taken = '2-9- 2011' OR Date_Taken = '1-9-2011' THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOTPERMITTEDTO BE REMOVED FROM ANY EXAMINATION VENUE INANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTEDDOUBLE-SIDED. This study source was downloaded by 100000844288999 from CourseHero.com on 04-24-2023 23:49:58 GMT -05:00 https://www.coursehero.com/file/145506910/HIT234-Midterm-Exam-2022-convertedpdf/
Semester 1, 2022 Mid-Term EXAMINATION HIT234 – Database Concepts Page 1 of 6 THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOTPERMITTEDTO BE REMOVED FROM ANY EXAMINATION VENUE INANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTEDDOUBLE-SIDED. This study source was downloaded by 100000844288999 from CourseHero.com on 04-24-2023 23:49:58 GMT -05:00 https://www.coursehero.com/file/145506910/HIT234-Midterm-Exam-2022-convertedpdf/
d) Find the number of cars used by employees whose number is above 120, excluding those employees who are part of the Information tech department. SELECT Employee Vehicle Table.Employee_No, count(Employee Vehicle Table.Reg_no) FROM Employee Vehicle Table, Employee Table WHERE Employee Table. Employee_No= Employee Vehicle Table. Employee_No AND Employee Vehicle Table. Employee_No > 120 AND Employee Table.Employee_Department <> 'Information Tech' GROUP BY Employee_Vehicle Table.Employee_No; e) Find the name and department of the employees, who have taken a Mitsubishi car between 11/08/2011 and 11/09/2011. SELECT NAME(Employee Table.FirstName,’ ‘, Employee Table.LastName) AS EmployeeName, Employee Table.Department FROM Vehicle, Employee Vehicle Table, Employee Table WHERE Vehicle.Make=’Mitsubishi’ AND Vehicle.RegNo= Employee Vehicle Table RegNo AND Vehicle.Employee_No= Employee Table.Employee_No AND Employee Vehicle Table.Date_Taken BETWEEN ‘11-08-2011’ AND ‘11-09-2011’; f) Find the employee names who used the least cars. SELECT Employee Table.Employee_No, NAME(FirstName,’ ‘, LastName) AS EMPLOYEENAME FROM Employee Table, Employee Vehicle Table, Vehicle WHERE Vehicle.Employee_No= Employee Table.Employee_No GROUP BY Vehicle.Employee_No HAVING COUNT(Vehicle.Reg No)=MIN(COUNT(Vehicle.Reg No)); g) Find the employee/s who returned a vehicle on 15/11/2011. THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOTPERMITTEDTO BE REMOVED FROM ANY EXAMINATION VENUE INANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTEDDOUBLE-SIDED. This study source was downloaded by 100000844288999 from CourseHero.com on 04-24-2023 23:49:58 GMT -05:00 https://www.coursehero.com/file/145506910/HIT234-Midterm-Exam-2022-convertedpdf/
SELECT Employee Table.Employee_No, NAME(FirstName,’ ‘, LastName) AS EMPLOYEENAME FROM Employee Table, Employee Vehicle Table WHERE Employee Table. Employee_No= Employee Vehicle Table. Employee_No AND Date Returned=’ 15/11/2011’; Semester 1, 2022 Mid-Term EXAMINATION HIT234 – Database Concepts Page 2 of 6 THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOTPERMITTEDTO BE REMOVED FROM ANY EXAMINATION VENUE INANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTEDDOUBLE-SIDED. This study source was downloaded by 100000844288999 from CourseHero.com on 04-24-2023 23:49:58 GMT -05:00 https://www.coursehero.com/file/145506910/HIT234-Midterm-Exam-2022-convertedpdf/
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Recommended textbooks for you
Text book image
BUSN 11 Introduction to Business Student Edition
Business
ISBN:9781337407137
Author:Kelly
Publisher:Cengage Learning
Text book image
Essentials of Business Communication (MindTap Cou...
Business
ISBN:9781337386494
Author:Mary Ellen Guffey, Dana Loewy
Publisher:Cengage Learning
Text book image
Accounting Information Systems (14th Edition)
Business
ISBN:9780134474021
Author:Marshall B. Romney, Paul J. Steinbart
Publisher:PEARSON
Text book image
Introduction to Business
Business
ISBN:9781947172548
Author:OpenStax
Publisher:OpenStax College
Text book image
International Business: Competing in the Global M...
Business
ISBN:9781259929441
Author:Charles W. L. Hill Dr, G. Tomas M. Hult
Publisher:McGraw-Hill Education
Text book image
Bcom
Business
ISBN:9780357026595
Author:LEHMAN, Carol M.
Publisher:Cengage Learning,
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
BUSN 11 Introduction to Business Student Edition
Business
ISBN:9781337407137
Author:Kelly
Publisher:Cengage Learning
Text book image
Essentials of Business Communication (MindTap Cou...
Business
ISBN:9781337386494
Author:Mary Ellen Guffey, Dana Loewy
Publisher:Cengage Learning
Text book image
Accounting Information Systems (14th Edition)
Business
ISBN:9780134474021
Author:Marshall B. Romney, Paul J. Steinbart
Publisher:PEARSON
Text book image
Introduction to Business
Business
ISBN:9781947172548
Author:OpenStax
Publisher:OpenStax College
Text book image
International Business: Competing in the Global M...
Business
ISBN:9781259929441
Author:Charles W. L. Hill Dr, G. Tomas M. Hult
Publisher:McGraw-Hill Education
Text book image
Bcom
Business
ISBN:9780357026595
Author:LEHMAN, Carol M.
Publisher:Cengage Learning,
SEE MORE TEXTBOOKS