Table Structure with Appropriate SQL Data Types

Primary Key

RollNumber should be defined as the Primary Key because it uniquely identifies each student record, does not allow duplicate values, and cannot contain NULL values.

Sample SQL Statement

CREATE TABLE STUDENT (
    RollNumber INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Email VARCHAR(100),
    AdmissionDate DATE
);

CREATE TABLE: A table t is created with one column val of type INTEGER.

INSERT INTO: The values 1, 2, 2, 3, NULL, NULL, 4, 5 are inserted into the column val.

SELECT COUNT(val): This query counts all non-NULL values in the val column.

Total non-NULL values: 1, 2, 2, 3, 4, 5

Result: 6

SELECT COUNT(DISTINCT val): This query counts only the unique non-NULL values in the val column.

Unique values: 1, 2, 3, 4, 5

Result: 5

We are given two relations: Student(sid, name, dept) and Course(sid, cid, cname, marks). The common attribute sid is used to join both tables.

1. Students with marks greater than 80:

This query retrieves high-performing students by applying a selection condition on marks.

SELECT S.name, S.dept, C.cname, C.marks
FROM Student S
JOIN Course C ON S.sid = C.sid
WHERE C.marks > 80;

2. Department-wise and subject-wise students (marks ≥ 30):

This query filters moderately passed students and organizes output by department and course.

SELECT S.dept, C.cname, S.name, C.marks
FROM Student S
JOIN Course C ON S.sid = C.sid
WHERE C.marks >= 30;

Conclusion: JOIN operation is essential to combine student identity with course performance data.

SELECT
    Department,
    COUNT(EmpID) AS TotalEmployees,
    AVG(Salary) AS AverageSalary
FROM Employee
GROUP BY Department;

Explanation

Department → Displays the department name.
COUNT(EmpID) → Counts the total number of employees in each department.
AVG(Salary) → Calculates the average salary of employees in each department.
GROUP BY Department → Groups all employees by department and returns one record for each department.

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;

SELECT cname
FROM Works
GROUP BY cname
HAVING SUM(salary) < 100000;

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;

A Transaction is a sequence of one or more SQL statements executed as a single logical unit of work. A transaction must be terminated using SQL transaction control statements.

SQL Statements to End a Transaction

COMMIT saves the transaction permanently, ROLLBACK cancels the transaction, and SAVEPOINT allows partial rollback within a transaction.

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 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
);

Apprx..

SELECT p_name
FROM publication
WHERE loc = 'dhaka'
  AND p_date > 1980;

Apprx..

UPDATE BOOK
SET Book_name = 'Computer Science'
WHERE Book_name = 'Science';

SELECT HospitalName

FROM Hospital

WHERE Specialization = 'Accident Treatment'

ORDER BY PatientCount DESC

LIMIT 5;

Explanation:

• WHERE filters hospitals specializing in accident treatment.
• ORDER BY PatientCount DESC sorts hospitals from highest to lowest patient count.
• LIMIT 5 returns the top five hospitals.

Problem Identification: The query is incorrect because AVG(Salary) is an aggregate function and cannot be used directly in the WHERE clause. Aggregate functions must be computed using a subquery.

Incorrect Query:

SELECT EmployeeName, Salary

FROM Employees

WHERE Salary > AVG(Salary);

Why it fails:

• WHERE clause is evaluated before aggregation.
• AVG(Salary) requires grouped or subquery context.

Correct Query:

SELECT EmployeeName, Salary

FROM Employees

WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Explanation:

• The subquery calculates the average salary first.
• Main query compares each employee salary with the computed average.

Assume the Employee table contains the following columns:

• employee_id — Employee ID
• employee_name — Employee Name
• join_date — Joining Date
• salary — Employee Salary

(a) Find the employees who joined on the same date.

First, identify the joining dates that occur more than once, then retrieve the employees having those dates:

SELECT *
FROM Employee
WHERE join_date IN (
    SELECT join_date
    FROM Employee
    GROUP BY join_date
    HAVING COUNT(*) > 1
);

Explanation:

• GROUP BY join_date → Groups employees according to their joining date.
• HAVING COUNT(*) > 1 → Selects dates on which more than one employee joined.
• The outer query displays all employees whose joining date matches those dates.

(b) Find employees whose salary is greater than 8,000 and less than 25,000.

SELECT *
FROM Employee
WHERE salary > 8000
  AND salary < 25000;

Explanation:

• salary > 8000 → The salary must be greater than 8,000.
• salary < 25000 → The salary must be less than 25,000.
• AND → Both conditions must be true.

Alternative for (b):

SELECT *
FROM Employee
WHERE salary BETWEEN 8001 AND 24999;

Important: BETWEEN is inclusive. Therefore, for the exact condition greater than 8,000 and less than 25,000, using > 8000 and < 25000 is clearer and safer.

• 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.

 SELECT E.Name
FROM EMPLOYEES E
JOIN DEPARTMENTS D
  ON E.DeptID = D.DeptID
WHERE D.DeptName = 'IT'

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:

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:

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:

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:

WHERE vs HAVING Clause in SQL

In SQL, both WHERE and HAVING are used to filter data, but they operate at different stages of query execution and serve different purposes. Understanding their behavior is essential when working with aggregate functions like SUM, COUNT, AVG, MIN, and MAX.

1. WHERE Clause

The WHERE clause filters individual rows before any grouping or aggregation takes place. It works directly on raw table data.

• Stage: Executes before GROUP BY and aggregate functions
• Scope: Filters individual rows based on column values
• Aggregate functions: Cannot be used directly in WHERE
• Performance: Reduces row count early, improving efficiency of later operations

Example:

Select only employees from the Sales department before calculating average salary.

SELECT dept, AVG(salary)
FROM employees
WHERE dept = 'Sales'
GROUP BY dept;

Here, WHERE first filters rows where dept = 'Sales', and then AVG is calculated on the filtered dataset.

2. HAVING Clause

The HAVING clause filters groups of rows after grouping and aggregation are completed. It works on aggregated results.

• Stage: Executes after GROUP BY and aggregation
• Scope: Filters grouped results
• Aggregate functions: Can be used in HAVING
• Performance: Works after aggregation, so it cannot reduce initial row processing

Example:

Show only departments where the average salary is greater than 50,000.

SELECT dept, AVG(salary)
FROM employees
GROUP BY dept
HAVING AVG(salary) > 50000;

Here, GROUP BY creates department groups, AVG calculates the average, and HAVING filters groups based on the aggregated value.

3. Execution Order in SQL

4. Combined Example

Find departments in the Sales region where total sales exceed 100,000.

SELECT region, SUM(sales) AS total
FROM orders
WHERE region = 'Sales'
GROUP BY region
HAVING SUM(sales) > 100000;

• WHERE region = 'Sales': Filters individual rows belonging to Sales region
• GROUP BY region: Groups remaining rows by region
• SUM(sales): Computes total sales per group
• HAVING SUM(sales) > 100000: Filters groups based on aggregated result

SELECT d.department_name, AVG(e.salary) AS average_salary

This clause selects the department name and calculates the average salary of employees in each department.

FROM employees e

This clause specifies the employees table as the main data source and uses e as an alias.

JOIN department d ON e.department_id = d.department_id

This join links employees with their departments using the common department_id.

WHERE e.salary > (SELECT AVG(salary) FROM employees)

This condition filters employees whose salary is higher than the overall average salary of all employees.

GROUP BY d.department_name

This clause groups records by department name to calculate aggregate values.

HAVING COUNT(*) > 2

This condition ensures that only departments with more than two employees are included.

ORDER BY average_salary DESC

This clause sorts the result in descending order based on average salary.

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.

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.

(i) SQL Query to Display StudentID, Name, and Marks for Students Scoring More Than 80 Marks

SELECT StudentID, Name, Marks
FROM STUDENTS
WHERE Marks > 80;

Explanation:

• SELECT chooses the required columns.
• FROM STUDENTS specifies the table name.
• WHERE Marks > 80 filters students who scored more than 80 marks.

(ii) SQL Query to Count Students Scoring More Than 80 Marks in Each Department

SELECT Department, COUNT(*) AS Total_Students
FROM STUDENTS
WHERE Marks > 80
GROUP BY Department;

Explanation:

• COUNT(*) counts the number of students.
• GROUP BY Department groups records department-wise.
• WHERE Marks > 80 considers only students scoring above 80.

SELECT A.Type AS AnimalType,
COUNT(*) AS TotalAdoptions
FROM Animals A
JOIN Adoption AD ON A.ID = AD.AnimalID
WHERE AD.AdoptDate = '2024-06-30'
GROUP BY A.Type;

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.