Loading...

Structure Query Language

Coming SOon

A database is a structured collection of data stored electronically. It can contain data in various formats, including text, images, and videos.

A Database Management System (DBMS) is a software system that efficiently manages, stores, and retrieves data in a structured way. It acts as an interface between the database and users or applications.

Core Functions of a DBMS
  • Data Management: A DBMS allows users to create, update, and query databases efficiently, providing a systematic way to handle data.
  • Data Integrity and Security: A DBMS ensures data consistency and security while reducing data redundancy and inconsistency.
  • Concurrency and Reliability: A DBMS supports concurrent access and transaction management, allowing multiple users to work simultaneously without conflict and providing backup facilities.

Database হলো electronicভাবে সংরক্ষিত একটি structured data collection, যেখানে text, image এবং video সহ বিভিন্ন ধরনের data থাকতে পারে।

Database Management System (DBMS) হলো একটি software system যা database-এ data store, manage এবং retrieve করতে সাহায্য করে। এটি database এবং user বা application-এর মধ্যে একটি interface হিসেবে কাজ করে।

DBMS এর Core Functions
  • Data Management: DBMS ব্যবহার করে user সহজে database create, update এবং query করতে পারে, যা data handle করার একটি systematic উপায় প্রদান করে।
  • Data Integrity and Security: DBMS data-এর consistency ও security নিশ্চিত করে এবং data redundancy ও inconsistency কমিয়ে আনে।
  • Concurrency and Reliability: DBMS একই সময়ে একাধিক user-কে data access করার সুবিধা দেয় এবং transaction management ও backup support প্রদান করে, যাতে data loss না হয়।

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:

i) SELECT Count (*) FROM Students S LEFT JOIN Marks M;
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

Find out the names of manager of each employee.
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;
Sort the employees of each department based on salary in descending order
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;
Suppose that we have a relational database with the following table. Underlined one represent primary key

Movies (mid, title, year)
People (pid, name)
Genres (gid, genre)
HasRole (pid, mid, role)
Has Genre (gid, mid)

Write a SQL query to return the number of movies that are romantic comedies.BB, (AP)-2023

SELECT COUNT(*)
FROM Movies, Genres, HasGenre
WHERE Movies.mid = HasGenre.mid
AND HasGenre.gid = Genres.gid
AND Genres.genre = “romantic comedy”;

Suppose we have a relational database with five tables. table key Attributes S(sid, A)Sid T(tid, B) Tid U(uid, C) Uid R(sid, tid, D) sid, tid Q(tid, uid, E) tid, uid Here R implements a many-to-many relationship between the entities implemented with tables S and T, and Q implements a many-to-many relationship between the entities implemented with tables T and U CB, AP, 23.
Write an SQL query that returns all records of the form sid, uid where sid is the key of an S- record and uid is the key of a U-record and these two records are related through the relations R and Q. Use SELECT and not SELECT DISTINCT in your query.

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 the sid from table R.
  • Q.uid: Selects the uid from table Q.
  • The JOIN connects R and Q through their tid columns, which establish the relationship between the two tables.
Write an SQL query that returns records of the form A, C where the A-value is from an S- record and the C-value is from a U-record and these two records are related through the relations R and Q. Use SELECT and not SELECT DISTINCT in your query.

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 the A-value from the S table (which corresponds to sid).
  • The first JOIN connects table S with table R through sid.
  • The second JOIN connects R and Q on tid.
  • Finally, Q is connected to U via uid, allowing us to select C from U.
Consider the following relation:
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

SQL Query
The following query displays the region, average sale amount, and total number of sales for each region where the average sale amount is greater than BDT 50,000 and the total number of sales is at least 3.

SELECT region,
       AVG(sale_amount) AS avg_sale_amount,
       COUNT(*) AS total_sales
FROM Sales
GROUP BY region
HAVING AVG(sale_amount) > 50000
   AND COUNT(*) >= 3;

SQL Query
নিচের query টি প্রতিটি region অনুযায়ী average sale amount এবং total sales সংখ্যা দেখায়, যেখানে average sale amount BDT 50,000 এর বেশি এবং ঐ region এ মোট sales কমপক্ষে 3টি।

SELECT region,
       AVG(sale_amount) AS avg_sale_amount,
       COUNT(*) AS total_sales
FROM Sales
GROUP BY region
HAVING AVG(sale_amount) > 50000
   AND COUNT(*) >= 3;
Let a database has two tables, Customers and Orders. The following figure shows the partial data of these two tables. Based on this partial data, explain Inner, Left, Right and Full join. Show the result set of each join operation
CB, SO(IT), 2024

Inner 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:

IDFirst NameOrder IDAmount
3Belal2500
5Helal4800

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:

IDFirst NameOrder IDAmount
1RahimNULLNULL
2KarimNULLNULL
3Belal2500
4RonyNULLNULL
5Helal4800

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:

IDFirst NameOrder IDAmount
NULLNULL1200
3Belal2500
NULLNULL3300
5Helal4800
NULLNULL5150

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:

IDFirst NameOrder IDAmount
1RahimNULLNULL
2KarimNULLNULL
3Belal2500
4RonyNULLNULL
5Helal4800
NULLNULL1200
NULLNULL3300
NULLNULL5150

Below tables are given, Employee (employee_id, name, salary, department) Leave (employee_id, date, reason, no_leaves) Holiday (Date, description) Rupali Bank, ANE, 2022

(i) Write mapping cardinality between 'Employee' and 'Holiday' table.

(ii) Write query to show all employee's leave count. 
SELECT employee_id, COUNT(employee_id)
FROM Leave
GROUP BY employee_id;
(iii) Write query to show employees who are in 'HR' department and have taken at least 5 leaves.
SELECT *
FROM employee
WHERE employee_id IN (
    SELECT employee_id
    FROM leave
    GROUP BY employee_id
    HAVING COUNT(employee_id) > 5
);
Analyze the output of the following SQL :
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

Explanation of SQL Query Clauses

SELECT Clause: The SELECT clause specifies which columns or calculated values will appear in the result.

  • department_name selects the department name from the departments table.
  • AVG(salary) AS average_salary calculates the average salary of selected employees and renames it as average_salary.

FROM Clause: The FROM clause specifies the tables used in the query.

  • employees e selects the employees table with alias e.
  • JOIN departments d ON e.department_id = d.department_id joins employees with departments using department_id.

WHERE Clause: The WHERE clause filters rows before grouping.

  • salary > (SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) selects employees whose salary is greater than the average salary of their department.

GROUP BY Clause: The GROUP BY clause groups rows to perform aggregate calculations.

  • GROUP BY department_name groups employees by department name.

HAVING Clause: The HAVING clause filters groups after aggregation.

  • HAVING COUNT(*) > 2 selects only departments with more than two qualifying employees.

ORDER BY Clause: The ORDER BY clause sorts the final result.

  • ORDER BY average_salary DESC sorts departments by average salary in descending order.

SQL Query Clause-এর ব্যাখ্যা

SELECT Clause: SELECT clause নির্ধারণ করে কোন column বা calculated value result-এ দেখানো হবে।

  • department_name departments table থেকে department-এর নাম নির্বাচন করে।
  • AVG(salary) AS average_salary employee-দের average salary হিসাব করে এবং নাম দেয় average_salary।

FROM Clause: FROM clause নির্ধারণ করে কোন table থেকে data নেওয়া হবে।

  • employees e employees table ব্যবহার করে, যেখানে e হলো alias।
  • JOIN departments d ON e.department_id = d.department_id department_id ব্যবহার করে দুইটি table যুক্ত করে।

WHERE Clause: WHERE clause grouping-এর আগে row filter করে।

  • salary > (SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) শুধুমাত্র সেই employee নির্বাচন করে যাদের salary তাদের department-এর average salary-এর চেয়ে বেশি।

GROUP BY Clause: GROUP BY clause aggregate calculation করার জন্য data group করে।

  • GROUP BY department_name department অনুযায়ী employee group করে।

HAVING Clause: HAVING clause aggregation-এর পরে group filter করে।

  • HAVING COUNT(*) > 2 শুধুমাত্র সেই department দেখায় যেখানে ২ জনের বেশি qualifying employee আছে।

ORDER BY Clause: ORDER BY clause final result sort করে।

  • ORDER BY average_salary DESC average salary অনুযায়ী descending order-এ result দেখায়।

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,2021,AP

(a) Find name, street, city who work for First Corporation Bank and earn more than 30000
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;
(b) Find name of all employees, who live in the same city and company for which they work.
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;
(c) Give all employees of First Century Bank 10 percent salary raise.
UPDATE Works
SET salary = salary + salary * 0.1
WHERE cname = 'First Century Bank';
(d) Find the company with payroll less than 100000.
SELECT cname
FROM Works
GROUP BY cname
HAVING SUM(salary) < 100000;
Coming Soon
WhatsApp Telegram Messenger
←