USA: +1 (725) 712-1730 
Sign in
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, First_Name, 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.
Expert Answer
REM Create the REGIONS table to hold region information for locations
Prompt ****** Creating REGIONS table ….
CREATE TABLE regions
( region_id NUMBER
CONSTRAINT region_id_nn NOT NULL
, region_name VARCHAR2(25)
);
REM ********************************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations.
Prompt ****** Creating COUNTRIES table ….
CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
ORGANIZATION INDEX;
REM ********************************************************************
REM Create the LOCATIONS table to hold address information for company departments.
Prompt ****** Creating LOCATIONS table ….
CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
) ;
REM ********************************************************************
REM Create the JOBS table to hold the different names of job roles within the company.
Prompt ****** Creating JOBS table ….
CREATE TABLE jobs
( job_id VARCHAR2(10)
, job_title VARCHAR2(35)
CONSTRAINT job_title_nn NOT NULL
, min_salary NUMBER(6)
, max_salary NUMBER(6)
) ;
REM ********************************************************************
REM Create the DEPARTMENTS table to hold company department information.
Prompt ****** Creating DEPARTMENTS table ….
CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, manager_id NUMBER(6)
, location_id NUMBER(4)
) ;
REM ********************************************************************
REM Create the EMPLOYEES table to hold the employee personnel
REM information for the company..
Prompt ****** Creating EMPLOYEES table ….
CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;
REM ********************************************************************
REM Create the JOB_HISTORY table to hold the history of jobs that
REM employees have held in the past.
Prompt ****** Creating Employee_HISTORY table ….
CREATE TABLE job_history
( employee_id NUMBER(6)
CONSTRAINT jhist_employee_nn NOT NULL
, joining_date DATE
CONSTRAINT jhist_start_date_nn NOT NULL
, Last_date DATE
CONSTRAINT jhist_end_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT jhist_job_nn NOT NULL
, department_id NUMBER(4)
, CONSTRAINT jhist_date_interval
CHECK (end_date > start_date)
) ;
COMMIT;
