Welcome to CLP - ORACLE SQL - Self Paced - Assessment only - E2
LAB 1. SQL - Employee Query
Solution 1: SQL - Employee Query
/*
Instruction:
Consider the tables in your schema
EMPLOYEE: DEPT_ID, EMP_ACTIVEFROM, EMP_DOB, EMP_FNAME, EMP_ID, EMP_LNAME, EMP_SAL, EMP_STATUS,
EMP_TERMINATIONDATE, LOC_ID, MGR_ID
LOCATION: CITY, COUNTRY, LOC_ID, STATE
DEPARTMENT: DEPT_HEAD, DEPT_ID, DEPT_NAME
Write an SQL query to display the details of Active employees .The output should be sorted in descending order based
on employee-id column. Use required filter to fetch only first 15 records
Output format is as follows :-
employee id,emp Name, city,state, country,salary,activeDate, status, Department name, manager name
EmpName:- Concatenate firstname and lastName with space character and use required string functions to fetch
only first 10 letters from EmpName
manager name :- Display first name and Last Name (separate firstname and lastName with space char)
*/
/*
Enter your query below.
Please append a semicolon ";" at the end of the query
*/
SELECT
e.EMP_ID AS "employee id",
SUBSTR(CONCAT(e.EMP_FNAME, ' ', e.EMP_LNAME), 1, 10) AS "emp Name",
l.CITY AS "city",
l.STATE AS "state",
l.COUNTRY AS "country",
e.EMP_SAL AS "salary",
e.EMP_ACTIVEFROM AS "activeDate",
e.EMP_STATUS AS "status",
d.DEPT_NAME AS "Department name",
CONCAT(m.EMP_FNAME, ' ', m.EMP_LNAME) AS "manager name"
FROM
EMPLOYEE e
JOIN
LOCATION l ON e.LOC_ID = l.LOC_ID
JOIN
DEPARTMENT d ON e.DEPT_ID = d.DEPT_ID
LEFT JOIN
EMPLOYEE m ON e.MGR_ID = m.EMP_ID
WHERE
e.EMP_STATUS = 'Active'
ORDER BY
e.EMP_ID DESC
LIMIT 15;
Lab 2: SQL- Highest salary in each department
Solution: Highest salary in each department
/*
Consider the tables in your schema
EMPLOYEE : DEPT_ID, EMP_ACTIVEFROM, EMP_DOB, EMP_FNAME, EMP_ID, EMP_LNAME, EMP_SAL,EMP_STATUS, EMP_TERMINATIONDATE, LOC_ID, MGR_ID
LOCATION: CITY, COUNTRY, LOC_ID, STATE
DEPARTMENT: DEPT_HEAD, DEPT_ID, DEPT_NAME
Write an SQL query to find the Active employees who is getting highest salary in each department . Display the record only if highest salary is more than 50000.
Display the fields - employee id, first name, Last name, city,state,salary,dept name
*/
/*Solution Oracle:*/
SET NULL "NULL";
SET FEEDBACK OFF;
SET ECHO OFF;
SET HEADING OFF;
SET WRAP OFF;
SET LINESIZE 10000;
SET TAB OFF;
SET PAGES 0;
SET DEFINE OFF;
/*
Enter your query below.
Please append a semicolon ";" at the end of the query
*/
WITH MaxSalaries AS (
SELECT
e.DEPT_ID,
MAX(e.EMP_SAL) AS MAX_SALARY
FROM
EMPLOYEE e
WHERE
e.EMP_STATUS = 'Active'
GROUP BY
e.DEPT_ID
HAVING
MAX(e.EMP_SAL) > 50000
)
SELECT
e.EMP_ID AS "employee id",
e.EMP_FNAME AS "first name",
e.EMP_LNAME AS "last name",
l.CITY AS "city",
l.STATE AS "state",
e.EMP_SAL AS "salary",
d.DEPT_NAME AS "dept name"
FROM
EMPLOYEE e
JOIN
MaxSalaries ms ON e.DEPT_ID = ms.DEPT_ID AND e.EMP_SAL = ms.MAX_SALARY
JOIN
LOCATION l ON e.LOC_ID = l.LOC_ID
JOIN
DEPARTMENT d ON e.DEPT_ID = d.DEPT_ID
WHERE
e.EMP_STATUS = 'Active';
exit;
Lab 3: SOL- Amount larger than allotted
Solution: SOL- Amount larger than allotted
/*
Consider the tables in your schema:
EMP : emp_id, emp_name, hire_date, job_name, salary, status
Allotment : dpt_allotment, dpt_code, dpt_name, manager_id
Write an SQL query to list departments which alloted amount is larger than the average alloted
amount of all the departments and exclude the departments
1. If the manager is in INACTIVE state
2. The dept_code is in between 20-30
3. Manager Name starts with 'T'
Output format is as given below
Dpt_code, Dpt_name,Dpt_allotment,Manager Name
*/
/*
Enter your query below.
Please append a semicolon ";" at the end of the query
*/
-- Step 1: Calculate the average allotment
WITH AverageAllotment AS (
SELECT
AVG(dpt_allotment) AS avg_allotment
FROM
allotment
),
-- Step 2: List departments with allotment greater than average and apply filters
FilteredDepartments AS (
SELECT
a.dpt_code,
a.dpt_name,
a.dpt_allotment,
e.emp_name AS manager_name
FROM
allotment a
JOIN
emp e ON a.manager_id = e.emp_id
JOIN
AverageAllotment aa ON a.dpt_allotment > aa.avg_allotment
WHERE
e.status != 'INACTIVE'
AND a.dpt_code NOT BETWEEN 20 AND 30
AND e.emp_name NOT LIKE 'T%'
)
-- Step 3: Select the final filtered results
SELECT
dpt_code,
dpt_name,
dpt_allotment,
manager_name
FROM
FilteredDepartments;
Lab 4: SQL - Commission and Netpay
Solution: SQL - Commission and Netpay
/*
Schema consists of below table :-
employees : commission, dep_id, emp_id, emp_name, hire_date, job_name, manager_id, salary
manager : manager_id,manager_name,experience
Write an SQL Query to display employee records whose netpay is more than any other employee.
Columns to b e displayed :- name, salary, commission, netpay , Manager name. Exclude the records if
manager_id is NULL.
NetPay needs to be calculated using commission and Salary (ie, salary+commission=netpay).
Solution Oracle:
*/
/*
Enter your query below.
Please append a semicolon ";" at the end of the query
*/
WITH EmployeeNetPay AS (
SELECT emp_id,emp_name,salary,commission,
(salary + NVL(commission, 0)) AS netpay,manager_id
FROM
employees
WHERE
manager_id IS NOT NULL
),
MaxNetPay AS (
SELECT Min(netpay) AS max_netpay
FROM
EmployeeNetPay
)
SELECT
e.emp_name AS "name",
e.salary,
e.commission,
e.netpay,
m.manager_name
FROM
EmployeeNetPay e
JOIN
MaxNetPay mnp ON e.netpay > mnp.max_netpay
JOIN
manager m ON e.manager_id = m.manager_id;
exit;