Scribd
Upload
2 views3 pages

Program 6

The document provides SQL queries demonstrating the use of sub-queries and nested queries. It includes the creation of 'Department' and 'Employee' tables, along with various queries such as finding the maximum salary, employees in the IT department, salaries greater than the average salary of IT, and departments with an average salary greater than 45000. The results of each query are also shown in tabular format.

Uploaded by

harshit.gupta.1379
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
2 views3 pages

Program 6

The document provides SQL queries demonstrating the use of sub-queries and nested queries. It includes the creation of 'Department' and 'Employee' tables, along with various queries such as finding the maximum salary, employees in the IT department, salaries greater than the average salary of IT, and departments with an average salary greater than 45000. The results of each query are also shown in tabular format.

Uploaded by

harshit.gupta.1379
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

Program 6 :—Write SQL queries for sub-queries

and
nested queries.

CREATE TABLE Department(


dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE Employee (


emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES
Department(dept_id)
);

INSERT INTO Department VALUES


(1,'HR'),
(2,'IT'),
(3,'Sales');

INSERT INTO Employee VALUES


(101,'AMIT',50000,2),
(102,'ANIKET',40000,1),
(103,'RAHUL',60000,2),
(104,'UJJAWAL',35000,3),
(105,'SANGAM',70000,2);

-- Max salary
SELECT emp_name,salary
FROM Employee
WHERE salary = (SELECT MAX(salary) FROM
Employee);

+----------+--------+
| emp_name | salary |
+----------+--------+
| SANGAM | 70000 |
+----------+--------+

-- Employees in IT department
SELECT emp_name
FROM Employee
WHERE dept_id = (
SELECT dept_id
FROM Department
WHERE dept_name='IT'
);
+----------+
| emp_name |
+----------+
| AMIT |
| RAHUL |
| SANGAM |
+----------+

-- Salary greater than average salary of IT


SELECT emp_name,salary
FROM Employee
WHERE salary > (
SELECT AVG(salary)
FROM Employee
WHERE dept_id = (
SELECT dept_id
FROM Department
WHERE dept_name='IT'
)
);

+----------+--------+
| emp_name | salary |
+----------+--------+
| SANGAM | 70000 |
+----------+--------+

-- Department with average salary > 45000


SELECT emp_name, dept_id
FROM Employee
WHERE dept_id IN (
SELECT dept_id
FROM Employee
GROUP BY dept_id
HAVING AVG(salary) > 45000
);

+----------+---------+
| emp_name | dept_id |
+----------+---------+
| AMIT | 2 |
| RAHUL | 2 |
| SANGAM | 2 |
+----------+---------+

You might also like