Assignment #4: SQL Introduction

Assignment #4 – SQL Introduction CS605
Assignment #4: SQL Introduction
Due: Wednesday, July 8 (end of day)
The purpose of this assignment is to give experience in creating and processing SQL statements.
PART ONE:
The following two tables are a partial description of a firm’s employee information.
a. Create an Oracle database containing exactly the data in these tables (e.g., including the spaces in “Employee #”, and with “Employee Name” being a single attribute. Start Date should be Oracle Date format – not CHAR or text). Note: the names of the columns don’t have to match exactly (for example, “Employee #” could be called EmpNumber, EmpNum, or EmpNo).
b. Name the tables CS605EMP and CS605REG, respectively.
c. The Primary Key for the CS605EMP table is Employee #, and the Primary Key for the CS605REG table is Region Code.
d. The Region column in the CS605EMP table should be a Foreign Key, referring to the Region Code column in the CS605REG table. The Manager column in the CS605REG table should be a Foreign Key to the CS605EMP table.
e. Only allow Salaries of less than $500,000. Start Dates prior to 1980 should not be allowed.
CS605EMP Table
Employee # Employee Name Region Start Date Salary
958 94 573 Jeff Smythe NW 16-MAY-2019 23000
900 58 472 Mary Faris NE 22-JAN-2011 33000
280 24 328 Scott Williams S 09-MAR-2015 46800
429 27 943 Keith Weber SW 15-FEB-2011 52500
944 58 432 Jeff Leffer NW 23-MAY-2018 71200
750 38 528 Gerry Cooke NE 04-OCT-2019 69300
132 45 678 Albert Alfredo S 19-APR-2013 19300
623 38 548 Keith Baker SW 04-OCT-2013 69300
333 33 333 Fran Weber HQ 11-DEC-2013 200800
684 39 542 Sally Weber SW 10-NOV-2012 38900
785 02 675 Bob Smothers SW 30-MAR-2015 23600
423 28 267 Jay Manson HQ 17-DEC-2016 31000
CS605REG Table
Region Code Region Name HQ Manager
NW Northwest Spokane, WA 944 58 432
S Southern Dallas, TX
NE New England Albany, NY 900 58 472
HQ Headquarters Staff Waltham, MA
SW Southwest Tucson, AZ 785 02 675
Assignment continued on Page 2!
Assignment #4 – SQL Introduction CS605
Part Two
Formulate each of the queries below in one Oracle SQL statement and run it on the database created above:
1. List all the employees that have a salary less than $33,000 and who joined the firm before 2015.
2. List all employees earning between $35,000 and $70,000.
3. List all employees that have either the same first name or the same last name as ‘Keith Weber.’ You may assume that ‘Weber’ and ‘Keith’ are not valid first and last names, respectively.
4. Prepare a listing showing employee names, region, and salary (only) in increasing order of seniority.
5. Prepare a listing showing the maximum, minimum, and average salary in each region. Only include regions having more than two employees.
To Turn In:
Submit a file with the SQL for each of the 5 queries above (only – not the output of the queries).
Each query should run without errors to receive any credit for the query. If you used SQL CREATE TABLE and INSERT commands to create the tables in Part One, your submission should include those SQL statements as well.


Buy plagiarism free, original and professional custom paper online now at a cheaper price. Submit your order proudly with us



Essay Hope