Answered Essay: Consider a DB schema consisting of the following relation schemes:

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;

Buy Essay
Calculate your paper price
Pages (550 words)
Approximate price: -

Help Me Write My Essay - Reasons:

Best Online Essay Writing Service

We strive to give our customers the best online essay writing experience. We Make sure essays are submitted on time and all the instructions are followed.

Our Writers are Experienced and Professional

Our essay writing service is founded on professional writers who are on stand by to help you any time.

Free Revision Fo all Essays

Sometimes you may require our writers to add on a point to make your essay as customised as possible, we will give you unlimited times to do this. And we will do it for free.

Timely Essay(s)

We understand the frustrations that comes with late essays and our writers are extra careful to not violate this term. Our support team is always engauging our writers to help you have your essay ahead of time.

Customised Essays &100% Confidential

Our Online writing Service has zero torelance for plagiarised papers. We have plagiarism checking tool that generate plagiarism reports just to make sure you are satisfied.

24/7 Customer Support

Our agents are ready to help you around the clock. Please feel free to reach out and enquire about anything.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

HOW OUR ONLINE ESSAY WRITING SERVICE WORKS

Let us write that nagging essay.

STEP 1

Submit Your Essay/Homework Instructions

By clicking on the "PLACE ORDER" button, tell us your requires. Be precise for an accurate customised essay. You may also upload any reading materials where applicable.

STEP 2

Pick A & Writer

Our ordering form will provide you with a list of writers and their feedbacks. At step 2, its time select a writer. Our online agents are on stand by to help you just in case.

STEP 3

Editing (OUR PART)

At this stage, our editor will go through your essay and make sure your writer did meet all the instructions.

STEP 4

Receive your Paper

After Editing, your paper will be sent to you via email.

× How can I help you?