2 Sept practice

• Clauses (WHERE, GROUP BY, ORDER BY, HAVING)

• Joins (INNER, LEFT, RIGHT, FULL OUTER via UNION)

• Operators (Relational, Arithmetic, Logical, Set)

---

🛠 Database Schema Used (for all examples)

-- Students table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    branch VARCHAR(50),
    year INT,
    marks INT
);

-- Subjects table
CREATE TABLE subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(100),
    branch VARCHAR(50)
);

-- Results table
CREATE TABLE results (
    result_id INT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    marks_obtained INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

---

💡 Bonus Tip: Create Sample Data

INSERT INTO students VALUES
(1, 'Rahul', 'Computer Science', 3, 82),
(2, 'Sneha', 'Electronics', 2, 76),
(3, 'Amit', 'Mechanical', 4, 65),
(4, 'Priya', 'Computer Science', 3, 91),
(5, 'Neha', 'Electronics', 1, 88);

INSERT INTO subjects VALUES
(101, 'DBMS', 'Computer Science'),
(102, 'Networks', 'Electronics'),
(103, 'Thermodynamics', 'Mechanical');

INSERT INTO results VALUES
(1, 1, 101, 84),
(2, 2, 102, 78),
(3, 4, 101, 92);

✅ Query 1: WHERE Clause (Relational + Logical Operators)

SELECT * 
FROM students
WHERE branch = 'Computer Science' AND year = 3;

---

✅ Query 2: GROUP BY with COUNT()

SELECT branch, COUNT(*) AS total_students
FROM students
GROUP BY branch;

---

✅ Query 3: ORDER BY (Descending marks)

SELECT name, marks
FROM students
ORDER BY marks DESC;

---

✅ Query 4: HAVING Clause (Aggregate filter)

SELECT branch, AVG(marks) AS avg_marks
FROM students
GROUP BY branch
HAVING avg_marks > 75;

---

✅ Query 5: INNER JOIN (Student + Results + Subject)

SELECT s.name, sub.subject_name, r.marks_obtained
FROM students s
INNER JOIN results r ON s.student_id = r.student_id
INNER JOIN subjects sub ON r.subject_id = sub.subject_id;

---

✅ Query 6: LEFT JOIN (Even if student has no result)

SELECT s.name, r.marks_obtained
FROM students s
LEFT JOIN results r ON s.student_id = r.student_id;

---

✅ Query 7: RIGHT JOIN (Show all results even if student is missing)

SELECT r.result_id, s.name, r.marks_obtained
FROM students s
RIGHT JOIN results r ON s.student_id = r.student_id;

---

✅ Query 8: Nested Query (Subquery inside IN)

SELECT name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM results
    WHERE marks_obtained > 85
);

---

✅ Query 9: SET OPERATORS – UNION (CSE + ECE students)

SELECT name, branch
FROM students
WHERE branch = 'Computer Science'
UNION
SELECT name, branch
FROM students
WHERE branch = 'Electronics';

---

✅ Query 10: ARITHMETIC Operators

SELECT name, marks, marks + 5 AS bonus_marks
FROM students;

---

✅ Query 11: WHERE with multiple conditions (Logical + Relational Operators)

Find students in either 2nd or 4th year with marks above 70.

SELECT name, year, marks
FROM students
WHERE (year = 2 OR year = 4) AND marks > 70;

---

✅ Query 12: GROUP BY with MAX() function

Get the highest marks in each branch.

SELECT branch, MAX(marks) AS highest_marks
FROM students
GROUP BY branch;

---

✅ Query 13: ORDER BY multiple columns

List students ordered by branch and then by marks descending.

SELECT name, branch, marks
FROM students
ORDER BY branch ASC, marks DESC;

---

✅ Query 14: HAVING with COUNT()

Find branches with more than 1 student.

SELECT branch, COUNT(*) AS student_count
FROM students
GROUP BY branch
HAVING student_count > 1;

---

✅ Query 15: INNER JOIN with filter in WHERE

Get students who scored more than 80 in any subject.

SELECT s.name, sub.subject_name, r.marks_obtained
FROM students s
JOIN results r ON s.student_id = r.student_id
JOIN subjects sub ON r.subject_id = sub.subject_id
WHERE r.marks_obtained > 80;

---

✅ Query 16: LEFT JOIN to show all students and their marks (if available)

SELECT s.name, sub.subject_name, r.marks_obtained
FROM students s
LEFT JOIN results r ON s.student_id = r.student_id
LEFT JOIN subjects sub ON r.subject_id = sub.subject_id;

---

✅ Query 17: RIGHT JOIN to show all results even if student data is missing

(Good for when results table may have orphaned records)

SELECT r.result_id, s.name, r.marks_obtained
FROM results r
RIGHT JOIN students s ON r.student_id = s.student_id;

---

✅ Query 18: Simulated FULL OUTER JOIN using UNION

Show all students and their results, even if one side is missing.

SELECT s.student_id, s.name, r.marks_obtained
FROM students s
LEFT JOIN results r ON s.student_id = r.student_id

UNION

SELECT s.student_id, s.name, r.marks_obtained
FROM students s
RIGHT JOIN results r ON s.student_id = r.student_id;

---

✅ Query 19: SET OPERATOR – INTERSECT simulation using IN

List students who have results for the subject "DBMS".

SELECT name
FROM students
WHERE student_id IN (
    SELECT r.student_id
    FROM results r
    JOIN subjects sub ON r.subject_id = sub.subject_id
    WHERE sub.subject_name = 'DBMS'
);

---

✅ Query 20: Use of Arithmetic Operators (percentage calculation)

Calculate percentage assuming total marks per subject = 100

SELECT s.name, sub.subject_name, r.marks_obtained,
       (r.marks_obtained / 100) * 100 AS percentage
FROM students s
JOIN results r ON s.student_id = r.student_id
JOIN subjects sub ON r.subject_id = sub.subject_id;

---