SQL Case Study with Sample Data

CASE STUDY

1) Create the following tables:

a) All 3 Employee tables (EMP_IND, EMP_SGD, EMP_USD) with the following structure:

COLUMN_NAME	DATATYPE
EMP_NO	VARCHAR2(10)
FIRSTNAME	VARCHAR2(10)
LASTNAME	VARCHAR2(10)
DESIGNATION	VARCHAR2(10)
MANAGER_ID	VARCHAR2(10)
DATE OF JOINING	DATE
SALARY	DECIMAL(7,2)
ALLOWANCE	DECIMAL(7,2)
CURRENCY	VARCHAR2(3)
DEPT_NO	NUMBER(4) AS FOREIGN KEY REFERNCE TO DEPT TABLE

 b) Create DEPT table with the following structure:

COLUMN_NAME	DATATYPE
DEPT_NO	NUMBER(4) AS PRIMARY KEY
DEPT_NAME	VARCHAR2(10)
LOC	VARCHAR2(10)

c) Create CURRENCY_CONVERSION table with the following structure:

COLUMN_NAME	DATATYPE
FROM_CURRENCY	VARCHAR2(3)
TO_CURRENCY	VARCHAR2(3)
CONVERSION_DATE	DATE
MULTIPLIER_RATE	DECIMAL(6,3)
DIVISOR_RATE	DECIMAL(6,3)

d) Create SALGRADE table with the following structure:

COLUMN_NAME	DATATYPE
GRADE	NUMBER(2) AS PRIMARY KEY
LOWSAL	DECIMAL(7,2)
HIGHSAL	DECIMAL(7,2)

2) Insert sample data into the above tables which is provided.

3) Perform the below operations with queries.

Join Ex:          

o    What is the department name for each manager?

o    When currency is SGD, where is the department located?

o    Who joined first and who joined last?

o    Which manager joined after his reportee?

o    Which department has most number of managers?

o    Which employee earns more than his manager?

Union Ex:       

o    How many unique designations are there amongst all employees in total?

o    How many unique currencies are there in total?

o    Give the count of employees per department from all locations.

Aggregations:

o    What is the total salary paid in US?

o    What is the total salary paid per department per currency?

o    How many people have joined in the last one month, one year, three years, ten years, older?

Lookup:          

o    Convert all salaries into USD

o    Find total salary in base currency and USD

o    Find the salary grade of each employee

Case Statement:       

o    When an employee is from Singapore, he gets twice the allowance. Compute the total allowance paid

o    When an employee is from Singapore and his manager is from US, he gets thrice the allowance. Compute the total allowance paid in base currency and USD

Format Conversion:  

o    Display the employee name and joining date in "DD-MM-YYYY" format.

o    Prefix zeroes to each department as needed to make the length 4.

o    Round off all salaries to the nearest integer.

o    Pick the first two characters of first name, first two characters of last name, date from Date of joining and display as user id. Eg: If name is David Booth , joining date is 18-Sep-1998 - then user id = dabo1809"

Sample Data: 

 Sorry guys i am not able to attach xls files here, If you required sample data please send email to anand4dwh@gmail.com with subject line ---> SQL CASE STUDY SAMPLE DATA REQ.  Thanks