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
