Theory Coming Soon
Previous Job Question with ANSWER on SQL
Given Tables:
EMPLOYEES (ID, Name, DeptID)
DEPARTMENTS (DeptID, DeptName)
Objective:
To get the list of employee names who work in the IT department.
Logical Steps:
- Identify the DeptID of the department where DeptName is IT.
- Match this DeptID with the DeptID in the EMPLOYEES table.
- Select the Name of employees who belong to the IT department.
Pseudo-SQL Logic:
SELECT E.Name FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DeptID = D.DeptID WHERE D.DeptName = 'IT';
(i) Write an SQL query to display only StudentID, Name, and Marks for students scoring more than 80 marks.
(ii) Write an SQL query to count how many students scored more than 80 marks in each Department.CB, O(it-23), 2026
(a) Explain what ON DELETE CASCADE means in simple English.
(b) If you delete one Manager from the database, what will automatically happen to all Employees under that manager?
(c) Explain why this is very dangerous in a real banking systers. What is the best practice and how does it fix this danger?CB, SO(it-23), 2026
SELECT d.dept_name, AVG(e.salary) AS average_salary FROM department d JOIN employee e ON d.dept_id = e.dept_id GROUP BY d.dept_name;
The features of SQL (RDBMS), NoSQL, and NewSQL,
Given the following two tables (Students and Marks) in a database, write down the output of the given SQL queries and write down the SQL queries for the outputs:
ii) SELECT StudentName From Students S JOIN Marks M ON S.Studentld = M.StudentId GROUP BY S.Studentld, S.StudentName HAVING SUM (Mark)>=200;
iii)List all the students name and number of subjects they have completed.
iv)List all the students who have not completed any subject.
v) List all the subject names. [Ministry of Food, Network/Website Manager(ICT),2024]
-- i) Query:
SELECT COUNT(*)
FROM Students S
LEFT JOIN Marks M
ON S.StudentId = M.StudentId;
-- Output:
-- 10
--
-- Explanation:
-- Student 1 -> 3 rows
-- Student 2 -> 3 rows
-- Student 3 -> 3 rows
-- Student 4 -> 1 row (no marks, but LEFT JOIN keeps it)
-- Total = 3 + 3 + 3 + 1 = 10
-- ii) Query:
SELECT StudentName
FROM Students S
JOIN Marks M
ON S.StudentId = M.StudentId
GROUP BY S.StudentId, S.StudentName
HAVING SUM(Mark) >= 200;
-- Output:
-- Mr. A
-- Mr. B
--
-- Explanation:
-- Mr. A = 70 + 50 + 80 = 200
-- Mr. B = 90 + 60 + 70 = 220
-- Mr. C = 30 + 70 + 60 = 160
-- iii) List all the students name and number of subjects they have completed
SELECT S.StudentName, COUNT(M.Subject) AS NumberOfSubjects
FROM Students S
LEFT JOIN Marks M
ON S.StudentId = M.StudentId
GROUP BY S.StudentId, S.StudentName;
-- Output:
-- Mr. A 3
-- Mr. B 3
-- Mr. C 3
-- Mr. D 0
-- iv) List all the students who have not completed any subject
SELECT S.StudentName
FROM Students S
LEFT JOIN Marks M
ON S.StudentId = M.StudentId
WHERE M.StudentId IS NULL;
-- Output:
-- Mr. D
-- v) List all the subject names
SELECT DISTINCT Subject
FROM Marks;
-- Output:
-- Math
-- Bengali
-- Physics
See the following Table:
Employees(EMP_ID, EMP_Name, Manager_ID, Dept_ID);
Departments(Dept_ID, Salary, Dept_Name, Emp_ID); Submarine Cables ,AM (Engineering), 2024
SELECT e.EMP_Name AS Employee,
m.EMP_Name AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.Manager_ID = m.EMP_ID;
SELECT e.EMP_Name, d.Dept_Name, d.Salary
FROM Employees e
JOIN Departments d
ON e.EMP_ID = d.Emp_ID
ORDER BY d.Dept_ID, d.Salary DESC;
Movies (mid, title, year)
People (pid, name)
Genres (gid, genre)
HasRole (pid, mid, role)
Has Genre (gid, mid)
SELECT COUNT(*)
FROM Movies, Genres, HasGenre
WHERE Movies.mid = HasGenre.mid
AND HasGenre.gid = Genres.gid
AND Genres.genre = “romantic comedy”;
We need to retrieve sid from table R and uid from table Q, where the two tables are related through the common column tid.
SELECT R.sid, Q.uid FROM R JOIN Q ON R.tid = Q.tid;
Explanation:
R.sid: Selects thesidfrom tableR.Q.uid: Selects theuidfrom tableQ.- The
JOINconnectsRandQthrough theirtidcolumns, which establish the relationship between the two tables.
Here, we need to select A from table S and C from table U, connecting them through the relations R and Q.
SELECT S.A, U.C FROM S JOIN R ON S.sid = R.sid JOIN Q ON R.tid = Q.tid JOIN U ON Q.uid = U.uid;
Explanation:
S.A: Selects theA-valuefrom theStable (which corresponds tosid).- The first
JOINconnects tableSwith tableRthroughsid. - The second
JOINconnectsRandQontid. - Finally,
Qis connected toUviauid, allowing us to selectCfromU.
Sales(sales_id, salesman, region, sale_amount, sale_date) ,
Write an SQL query to display the region, average sale amount, and total number of sales for each region where: The average sale amount exceeds BDT 50,000 and the total number of sales in that region is at least 5.CB, SO(it), 2025
CB, SO(IT), 2024Inner Join:
An Inner Join returns only the rows that have matching values in both tables.
SELECT Customers.ID, Customers.FirstName, Orders.OrderID, Orders.Amount FROM Customers INNER JOIN Orders ON Customers.ID = Orders.CustomerID;
Result Set:
| ID | First Name | Order ID | Amount |
|---|---|---|---|
| 3 | Belal | 2 | 500 |
| 5 | Helal | 4 | 800 |
Left Join (or Left Outer Join):
A Left Join returns all rows from the left table (Customers), and the matched rows from the right table (Orders). If there is no match, the result is NULL on the right side.
SELECT Customers.ID, Customers.FirstName, Orders.OrderID, Orders.Amount FROM Customers LEFT JOIN Orders ON Customers.ID = Orders.CustomerID;
Result Set:
| ID | First Name | Order ID | Amount |
|---|---|---|---|
| 1 | Rahim | NULL | NULL |
| 2 | Karim | NULL | NULL |
| 3 | Belal | 2 | 500 |
| 4 | Rony | NULL | NULL |
| 5 | Helal | 4 | 800 |
Right Join (or Right Outer Join):
A Right Join returns all rows from the right table (Orders), and the matched rows from the left table (Customers). If there is no match, the result is NULL on the left side.
SELECT Customers.ID, Customers.FirstName, Orders.OrderID, Orders.Amount FROM Customers RIGHT JOIN Orders ON Customers.ID = Orders.CustomerID;
Result Set:
| ID | First Name | Order ID | Amount |
|---|---|---|---|
| NULL | NULL | 1 | 200 |
| 3 | Belal | 2 | 500 |
| NULL | NULL | 3 | 300 |
| 5 | Helal | 4 | 800 |
| NULL | NULL | 5 | 150 |
Full Join (or Full Outer Join):
A Full Join returns all rows when there is a match in either the left (Customers) or right (Orders) table. Rows without a match in one of the tables will have NULLs in the corresponding columns.
SELECT Customers.ID, Customers.FirstName, Orders.OrderID, Orders.Amount FROM Customers FULL OUTER JOIN Orders ON Customers.ID = Orders.CustomerID;
Result Set:
| ID | First Name | Order ID | Amount |
|---|---|---|---|
| 1 | Rahim | NULL | NULL |
| 2 | Karim | NULL | NULL |
| 3 | Belal | 2 | 500 |
| 4 | Rony | NULL | NULL |
| 5 | Helal | 4 | 800 |
| NULL | NULL | 1 | 200 |
| NULL | NULL | 3 | 300 |
| NULL | NULL | 5 | 150 |
Below tables are given, Employee (employee_id, name, salary, department) Leave (employee_id, date, reason, no_leaves) Holiday (Date, description) Rupali Bank, ANE, 2022
SELECT employee_id, COUNT(employee_id)
FROM Leave
GROUP BY employee_id;
SELECT *
FROM employee
WHERE employee_id IN (
SELECT employee_id
FROM leave
GROUP BY employee_id
HAVING COUNT(employee_id) > 5
);
SELECT d.department_name, AVG(e.salary) AS average_salary
FROM employees e
JOIN department d ON e.department_id = d.department_id
WHERE e.salary > (SELECT AVG(salary) FROM employees)
GROUP BY d.department_name
HAVING COUNT(*) > 2
ORDER BY average_salary DESC;
SELECT department_name, AVG(salary) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = d.department_id
)
GROUP BY department_name
HAVING COUNT(*) > 2
ORDER BY average_salary DESC; Rupali , ANE, 2023
Write SQL command from the following tables.
Employee (ename, street, city)
Works (ename, cname, salary, joindate)
Company (cname, city)
Manages (ename, mname) 6 Bank & FI, AP, 2021
SELECT e.ename, e.street, e.city
FROM Employee e, Works w
WHERE e.ename = w.ename
AND w.cname = 'First Corporation Bank'
AND w.salary > 30000;
SELECT e.ename
FROM Employee e, Works w, Company c
WHERE e.city = c.city
AND w.cname = c.cname
AND e.ename = w.ename
AND e.city = w.city;
UPDATE Works
SET salary = salary + salary * 0.1
WHERE cname = 'First Century Bank';
SELECT cname
FROM Works
GROUP BY cname
HAVING SUM(salary) < 100000;