1) Consider the following tables:
EMP ( DEPARTMENT ( JOB (
Th
EmployeeID INT PRIMARY DepartmentID INT
is
PD
KEY, PRIMARY KEY, JobTitle VARCHAR(100)
F
be
lo
Name VARCHAR(100), DepartmentName PRIMARY KEY );
ng
s
VARCHAR(100),
to
9J
Salary DECIMAL(15, 2),
a
z
id
Location VARCHAR(100)
A
(b
DepartmentNumber INT, );
ul
ad
ril
ax
m
ManagerID INT,
in
ar
av
s
im
JobTitle VARCHAR(100),
ha
5@
gm
FOREIGN KEY
ai
R
l.c
(DepartmentNumber)
om
REFERENCES
,9
06
DEPARTMENT(DepartmentID),
35
u
34
91
FOREIGN KEY (ManagerID)
9)
ab
REFERENCES
EMP(EmployeeID)
);
ab
Write a SQL query to
i) Retrieve the names and salaries of all employees from the EMP table.
dr
ii) Retrieve the employee IDs and names of all employees whose salary is greater than
50,000.
iii) Find the average salary of employees in the EMP table.
in
iv) Retrieve the department numbers and the count of employees in each department from the
EMP table.
v) Retrieve the names of employees along with the names of their respective managers.
av
Assume the EMP table has a ManagerID column that references the EmployeeID.
vi) Retrieve the employee IDs of employees whose salary is greater than the average salary
of the company.
R
vii) Increase the salary of all employees in department 10 by 10%.
viii) Delete all employees from the EMP table who are in department 5 and have a salary less
than 40,000.
ix) Create a table DEPARTMENT with columns DepartmentID, DepartmentName, and
Location.
x) nsert a new employee with the following details into the EMP table: EmployeeID = 101,
Name = 'John Doe', Salary = 60000, DepartmentNumber = 1, ManagerID = 100.
xi) Retrieve the employee IDs and a new column SalaryCategory which classifies salaries
into 'High', 'Medium', and 'Low'. Use thresholds of 70,000 for High, 50,000 for Medium, and
below 50,000 for Low.
xii) Retrieve the employee IDs and names of employees who have the highest salary in their
respective departments.
Th
is
PD
xiii) Retrieve distinct department numbers from the EMP table.
F
be
lo
ng
xiv) Retrieve all distinct job titles from the EMP table and another table JOB which has a
s
to
JobTitle column.
9J
a
z
id
A
(b
xv) Retrieve the employee IDs of employees who work in a department with at least one other
ul
ad
ril
employee.
ax
m
in
ar
av
xv) SELECT [Link] FROM EMP E1 WHERE EXISTS ( SELECT 1 FROM EMP E2
s
im
WHERE [Link] = [Link] AND [Link] !=
ha
5@
[Link] );
gm
ai
R
l.c
om
,9
2) Write a query to retrieve the names and department names of all employees.
06
35
u
34
EMP Table:
91
9)
ab
EmployeeID Name DepartmentNumber
101 John 1
ab
102 Jane 2
103 Alice 1
dr
DEPARTMENT Table:
in
DepartmentID DepartmentName
av
1 IT
2 Finance
R
(This Table is common for 2-5 Questions)
3) Given the following data, write a query to find the output.
4) Write a query to retrieve the Cartesian product of the EMP and DEPARTMENT tables.
EMP Table:
EmployeeID Name DepartmentNumber
Th
is
101 John 1
PD
F
be
102 Jane 2
lo
ng
s
to
103 Alice 1
9J
a
z
id
A
(b
DEPARTMENT Table:
ul
ad
ril
ax
m
in
DepartmentID DepartmentName
ar
av
s
im
ha
1 IT
5@
gm
2 Finance
ai
R
l.c
om
,9
06
35
u
34
91
9)
ab
5) Write a query to retrieve the names of employees who belong to department 1 or
ab
department 2.
dr
6) Write a query to retrieve the names of employees whose salary is greater than any
employee in department 1.
in
av
EMP Table:
EmployeeID Name Salary DepartmentNumber
R
101 John 60000 1
102 Jane 55000 2
103 Alice 50000 1
Table common for Question 7
7) Write a query to retrieve the names of employees whose name starts with 'J'.
Th
is
PD
F
be
lo
ng
s
to
9J
a
z
id
A
(b
ul
ad
ril
ax
m
in
ar
av
s
im
ha
5@
gm
ai
R
l.c
8) Consider the following tables:
om
,9
06
35
u
34
91
9)
ab
ab
dr
in
av
R
Th
is
PD
F
be
lo
ng
s
to
9J
a
z
id
A
(b
ul
ad
ril
ax
m
in
ar
av
s
im
ha
5@
gm
ai
R
l.c
om
,9
06
35
u
34
91
9)
ab
ab
dr
in
av
i) SELECT [Link], SUM([Link] * [Link]) AS total_sales
R
FROM Authors a
JOIN Books b ON a.author_id = b.author_id
JOIN Sales s ON b.book_id = s.book_id
GROUP BY [Link]
ORDER BY total_sales DESC;
Question: What is the output of the above query?
A) J.K. Rowling - $167.92, George Orwell - $128.90, J.R.R. Tolkien - $87.97
B) J.K. Rowling - $167.92, George Orwell - $103.92, J.R.R. Tolkien - $103.96
C) J.K. Rowling - $125.94, George Orwell - $103.92, J.R.R. Tolkien - $103.96
D) J.K. Rowling - $125.94, George Orwell - $128.90, J.R.R. Tolkien - $87.97
ii) SELECT [Link], SUM([Link]) AS total_quantity
Th
is
FROM Books b
PD
JOIN Sales s ON b.book_id = s.book_id
F
be
GROUP BY [Link]
lo
ng
ORDER BY total_quantity DESC
s
to
LIMIT 1;
9J
a
Question: What is the output of the above query?
z
id
A
(b
ul
ad
A) Harry Potter - 8
ril
ax
B) 1984 - 4
m
in
C) Animal Farm - 6
ar
av
s
D) The Hobbit - 2
im
ha
5@
gm
ai
R
l.c
om
,9
06
35
u
34
iii) SELECT [Link]
91
9)
FROM Authors a
ab
LEFT JOIN Books b ON a.author_id = b.author_id
LEFT JOIN Sales s ON b.book_id = s.book_id
WHERE s.sale_id IS NULL;
ab
Question: What is the output of the above query?
A) J.K. Rowling
B) George Orwell
dr
C) J.R.R. Tolkien
D) No authors
in
iv) SELECT [Link]
av
FROM Books b
LEFT JOIN Sales s ON b.book_id = s.book_id
WHERE s.sale_id IS NULL;
R
Question: What is the output of the above query?
1. A) Harry Potter, 1984, The Hobbit, Animal Farm, The Lord of the Rings
2. B) Harry Potter
3. C) No books
4. D) The Lord of the Rings
v) SELECT [Link]
FROM Authors a
JOIN Books b ON a.author_id = b.author_id
Th
GROUP BY [Link]
is
PD
HAVING COUNT(b.book_id) > 1;
F
be
lo
ng
s
Question: What is the output of the above query?
to
9J
a
z
id
1. A) J.K. Rowling
A
(b
2. B) George Orwell
ul
ad
ril
3. C) J.R.R. Tolkien
ax
m
4. D) George Orwell and J.R.R. Tolkien
in
ar
av
s
im
ha
5@
vi) SELECT [Link]
gm
FROM Books b
ai
R
l.c
JOIN Sales s ON b.book_id = s.book_id
om
,9
WHERE b.published_year > 1950
06
35
GROUP BY [Link];
u
34
91
9)
ab
Question: What is the output of the above query?
1. A) Harry Potter, The Lord of the Rings
2. B) Harry Potter
ab
3. C) 1984
4. D) The Hobbit
dr
vii) SELECT [Link], SUM([Link] * [Link]) AS total_sales
FROM Books b
in
JOIN Sales s ON b.book_id = s.book_id
WHERE b.published_year < 1950
GROUP BY [Link];
av
Question: What is the output of the above query?
R
1. A) 1984 - $63.96, Animal Farm - $77.94, The Hobbit - $51.98
2. B) 1984 - $63.96, Animal Farm - $77.94
3. C) 1984 - $63.96, The Hobbit - $51.98
4. D) Animal Farm - $77.94, The Hobbit - $51.98
●
viii) SELECT [Link] AS name
FROM Books b
WHERE b.published_year > 1950
Th
UNION
is
PD
SELECT [Link]
F
FROM Authors a
be
lo
ng
WHERE a.birth_year > 1950;
s
to
9J
a
z
id
A
Question: What is the output of the above query?
(b
ul
ad
ril
ax
1. A) Harry Potter, J.K. Rowling, The Lord of the Rings
m
in
2. B) Harry Potter, The Lord of the Rings
ar
av
s
3. C) J.K. Rowling, George Orwell
im
ha
4. D) Harry Potter, The Lord of the Rings, J.K. Rowling
5@
gm
ai
R
l.c
ix) SELECT [Link] AS name
om
,9
FROM Books b
06
35
WHERE b.published_year < 1950
u
34
91
INTERSECT
9)
ab
SELECT [Link]
FROM Authors a
WHERE a.birth_year < 1950;
ab
Question: What is the output of the above query?
1. A) 1984
dr
2. B) George Orwell
3. C) No results
4. D) Animal Farm
in
av
R
a
ul 06
35
34
91
9)
av ha
5@
gm
ai
l.c
om
,9
R ax
m
in
ar
s im
(b
ad
ril u
lo
ng
s
to
9J
z id
A
ab
Th
is
PD
F
be
ab
dr
in
av
R
