Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Consider a DB schema consisting of the following relation schemes: Regions (Region ID, Region_Name) Countries (Country_id, Country_Name, Region_Id) Locations (Location_Id, Street_address, Postal_code, City, State_Province, Country_Id)
Consider a DB schema consisting of the following relation schemes: Regions (Region ID, Region_Name) Countries (Country_id, Country_Name, Region_Id) Locations (Location_Id, Street_address, Postal_code, City, State_Province, Country_Id) Jobs (Job_Id, Job_title, Min_Salary, Max_salary) Departments (Dep_Id, Dep_Name, Manager_Id, Location_Id) Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id) Employee_History (Emp_ID, Joining_date, last_date, Job_ID, Dep_ID) Keys are underlined Now, express the following queries in SQL: 1. Find the count of departments, region name(s) and cities for the department(s) that have more than 500 employees. 2. For a department in which the max salary is greater than 100000 for employees who worked in the past, set the manager name as 'Picard'. 3. Find month and year which witnessed lowest count of employees joining a department located in "Vancouver. 4. With the help of schema find the year which witnessed maximum number of employee intake. 5. For the year in query-4, find how many joined in each month in that specific year
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started