Scribd
Upload
10 views4 pages

SQL PL SQL Answers

The document provides SQL queries and PL/SQL tasks for creating tables, inserting records, and performing various operations such as counting employees by department, identifying the department with the highest employee count, and creating views and stored procedures. It includes specific SQL commands for creating tables for departments, managers, and employees, along with sample data inserts. Additionally, it outlines PL/SQL procedures and functions for retrieving employee information and calculating the nth highest salary.

Uploaded by

ved. teck
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views4 pages

SQL PL SQL Answers

The document provides SQL queries and PL/SQL tasks for creating tables, inserting records, and performing various operations such as counting employees by department, identifying the department with the highest employee count, and creating views and stored procedures. It includes specific SQL commands for creating tables for departments, managers, and employees, along with sample data inserts. Additionally, it outlines PL/SQL procedures and functions for retrieving employee information and calculating the nth highest salary.

Uploaded by

ved. teck
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

*** SQL Queries & PL/SQL Tasks - Answers ***

1. Create Tables and Insert Records

CREATE TABLE department (id INT PRIMARY KEY, dept_name VARCHAR(100));

CREATE TABLE manager (id INT PRIMARY KEY, name VARCHAR(100), dept_id INT, FOREIGN KEY (dept_id)

REFERENCES department(id));

CREATE TABLE employee (

id INT PRIMARY KEY,

name VARCHAR(100),

salary DECIMAL(10, 2),

age INT,

dept_id INT,

manager_id INT,

joining_date DATE,

city VARCHAR(100),

FOREIGN KEY (dept_id) REFERENCES department(id),

FOREIGN KEY (manager_id) REFERENCES manager(id)

);

-- Insert into department

INSERT INTO department VALUES (1, 'IT'), (2, 'HR'), (3, 'Finance'), (4, 'Marketing'), (5, 'Sales');

-- Insert into manager

INSERT INTO manager VALUES (1, 'Ramesh', 1), (2, 'Jayesh', 2), (3, 'Shreya', 3);

-- Insert into employee


INSERT INTO employee VALUES

(1, 'Rani', 60000, 28, 1, 1, '2022-03-15', 'Mumbai'),

(2, 'Aishwarya', 75000, 30, 1, 1, '2021-07-22', 'Delhi'),

(3, 'Shruti', 50000, 35, 2, 2, '2023-01-10', 'Mumbai'),

(4, 'Tushar', 55000, 32, 2, 2, '2022-06-05', 'Delhi'),

(5, 'Aryan', 72000, 40, 3, 3, '2019-11-25', 'Mumbai'),

(6, 'Akshay', 68000, 33, 3, 3, '2021-08-13', 'Delhi'),

(7, 'Varun', 49000, 29, 4, NULL, '2024-02-01', 'Mumbai'),

(8, 'Rani', 52000, 27, 5, NULL, '2023-10-10', 'Delhi');

2. Department-wise Employee Count

SELECT d.dept_name, COUNT([Link]) AS employee_count

FROM department d

LEFT JOIN employee e ON [Link] = e.dept_id

GROUP BY d.dept_name;

3. Department with Highest Employee Count

SELECT d.dept_name, COUNT([Link]) AS total_employees

FROM department d

JOIN employee e ON [Link] = e.dept_id

GROUP BY d.dept_name

ORDER BY total_employees DESC

LIMIT 1;

4. View: Employee Count per Manager

CREATE VIEW manager_employee_count AS

SELECT [Link] AS manager_name, COUNT([Link]) AS total_employees


FROM manager m

LEFT JOIN employee e ON [Link] = e.manager_id

GROUP BY [Link];

5. Employees Living in the Same City

SELECT city, GROUP_CONCAT(name), COUNT(*)

FROM employee

GROUP BY city

HAVING COUNT(*) > 1;

6. Employees Earning More Than Dept Average

SELECT [Link], [Link], [Link], d.dept_name

FROM employee e

JOIN department d ON e.dept_id = [Link]

WHERE [Link] > (

SELECT AVG(salary) FROM employee e2 WHERE e2.dept_id = e.dept_id

);

*** PL/SQL Tasks ***

1. Stored Procedure: Display employee name with department

CREATE OR REPLACE PROCEDURE GetEmpDept (emp_id IN INT) IS

emp_name VARCHAR(100);

dept_name VARCHAR(100);

BEGIN

SELECT [Link], d.dept_name INTO emp_name, dept_name

FROM employee e
JOIN department d ON e.dept_id = [Link]

WHERE [Link] = emp_id;

DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_name || ' | Department: ' || dept_name);

END;

2. Function: Nth Highest Salary

CREATE OR REPLACE FUNCTION GetNthHighestSalary(n INT) RETURN NUMBER IS

nth_salary NUMBER;

BEGIN

SELECT DISTINCT salary INTO nth_salary

FROM (

SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk

FROM employee

) WHERE rnk = n;

RETURN nth_salary;

END;

You might also like