[2022] CSCI-UA 9479 Data Management and Analysis - Mid Semester Quiz - Q1 SQL Query
1. A multinational company has offices (sites) in several cities. Each site has one or more departments (accounting, research, production, etc.). Every employee works at some site in a particular department. Every department at each site has a manager. Each employee undergoes an evaluation and the result of his or her evaluation is composed of `grade' which is an integer between 0 and 10 for various criterion: efficiency, productivity, innovation, inter-personal relation etc.
The schema is given as follows: (Primary keys are underlined)
• Department (Dep id, Dep name)
• Site (Site id, Site name, City, Country)
• Site Department (Site id, Dep id)
• Manager (Site id, Dep id, Emp id)
• Employee (Emp id, start date, Site id, Dep id, salary)
• Evaluation (Emp id, C id, grade)
• Criterion (C id, C name)
Write queries for the following (Use only mysql.connector package) (You can write all of these queries in a single python file and store the results in variables result1, result2,result3, result4, result5)
[2 points 5 = 10 points]
(a) Retrieve all the department id and names of the departments that are located in France.
(b) Retrieve the list of countries in which is the average salary of employees the highest.
(c) Of the employees in the `Accounting' department, list the ones with the lowest ratings. (Here rating is the average score on all criteria is taken).
(d) Give the list of countries that have no 'Accounting' department, in the alphabetical order of the country names.
(e) Retrieve the Site(s) that has the smallest number of employees.
Get the Solution to This Question
WeChat
WhatsApp
