SUB Queries

 SUB Queries in SQL

Table 1: Students

student_id name age department
1 Asha 19 Computer
2 Ravi 20 Mechanical
3 Priya 21 Computer
4 Suresh 20 Civil
5 Meena 19 Computer

Table 2:

Marks

mark_id student_id subject marks
1 1 DBMS 85
2 1 Networks 78
3 2 DBMS 69
4 2 Networks 75
5 3 DBMS 92
6 3 Networks 88
7 4 DBMS 55
8 4 Networks 60
9 5 DBMS 70
10 5 Networks 72



CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    department VARCHAR(50)
);


CREATE TABLE Marks (
    mark_id INT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(50),
    marks INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);


INSERT INTO Students (student_id, name, age, department) VALUES
(1, 'Asha', 19, 'Computer'),
(2, 'Ravi', 20, 'Mechanical'),
(3, 'Priya', 21, 'Computer'),
(4, 'Suresh', 20, 'Civil'),
(5, 'Meena', 19, 'Computer');


INSERT INTO Marks (mark_id, student_id, subject, marks) VALUES
(1, 1, 'DBMS', 85),
(2, 1, 'Networks', 78),
(3, 2, 'DBMS', 69),
(4, 2, 'Networks', 75),
(5, 3, 'DBMS', 92),
(6, 3, 'Networks', 88),
(7, 4, 'DBMS', 55),
(8, 4, 'Networks', 60),
(9, 5, 'DBMS', 70),
(10, 5, 'Networks', 72);


Solve Question from Page number 96 in PPT


Assignment 4 Query 


31. Find students who scored above average marks

SELECT 
    (SELECT sub_name 
     FROM subject 
     WHERE subject.sub_id = marks.sub_id) AS sub_name,
    (SELECT s_name 
     FROM student 
     WHERE student.s_id = marks.s_id) AS s_name,
    marks_obtain AS marks
FROM marks
WHERE marks_obtain > (
    SELECT AVG(m2.marks_obtain)
    FROM marks AS m2
    WHERE m2.sub_id = marks.sub_id
)
ORDER BY sub_name, marks DESC;


32. Get subjects with maximum enrollment using subquery.

A) If you want all subject(s) that have the maximum enrollment (returns multiple rows when there is a tie):

SELECT 
  sub_id,
  (SELECT sub_name FROM subject WHERE subject.sub_id = marks.sub_id) AS sub_name,
  COUNT(s_id) AS total_enrolled
FROM marks
GROUP BY sub_id
HAVING COUNT(s_id) = (
  SELECT MAX(sub_count)
  FROM (
    SELECT COUNT(*) AS sub_count
    FROM marks
    GROUP BY sub_id
  ) AS t
);

B) If you want only one subject (the single top subject) — returns a single row:

SELECT 
  sub_id,
  (SELECT sub_name FROM subject WHERE subject.sub_id = marks.sub_id) AS sub_name,
  COUNT(s_id) AS total_enrolled
FROM marks
GROUP BY sub_id
ORDER BY total_enrolled DESC
LIMIT 1;
33. List students with the highest marks in each subject.

SELECT 
    (SELECT sub_name 
     FROM subject 
     WHERE subject.sub_id = marks.sub_id) AS sub_name,
    (SELECT s_name 
     FROM student 
     WHERE student.s_id = marks.s_id) AS s_name,
    marks_obtain AS marks
FROM marks
WHERE marks_obtain = (
    SELECT MAX(m2.marks_obtain)
    FROM marks AS m2
    WHERE m2.sub_id = marks.sub_id
)
ORDER BY sub_name;

34. Show departments with fewer than 5 students.

SELECT 
    d_id,
    (SELECT d_name 
     FROM department 
     WHERE department.d_id = student.d_id) AS department_name,
    COUNT(s_id) AS total_students
FROM student
WHERE d_id IS NOT NULL
GROUP BY d_id
HAVING COUNT(s_id) < 5;

35. Find students who failed in at least one subject.

SELECT 
    (SELECT s_name 
     FROM student 
     WHERE student.s_id = marks.s_id) AS s_name,
    (SELECT sub_name 
     FROM subject 
     WHERE subject.sub_id = marks.sub_id) AS sub_name,
    marks_obtain AS marks
FROM marks
WHERE marks_obtain < 40
ORDER BY s_name;

36. Use a subquery to list students not enrolled in any subject.

SELECT s_id, s_name
FROM student
WHERE s_id NOT IN (SELECT s_id FROM marks);


37. Display teacher names who teach more than 2 subjects

SELECT f_name
FROM faculty
WHERE f_id IN (
  SELECT f_id
  FROM subject
  GROUP BY f_id
  HAVING COUNT(*) > 2
);


38. Show names of students who have 100% attendance in all subjects

SELECT a.s_id,
       (SELECT s_name FROM student WHERE student.s_id = a.s_id) AS s_name
FROM attendance a
GROUP BY a.s_id
HAVING SUM(CASE WHEN a.present_count < a.total_classes THEN 1 ELSE 0 END) = 0;


39. Find subjects where average marks are below 40.

SELECT sub_id,
       (SELECT sub_name FROM subject WHERE subject.sub_id = marks.sub_id) AS sub_name,
       ROUND(AVG(marks_obtain),2) AS avg_marks
FROM marks
GROUP BY sub_id
HAVING AVG(marks_obtain) < 40;

40. List students who are in the same department as a specific student (e.g., ‘Ravi’).

SELECT s_id, s_name
FROM student
WHERE d_id IN (SELECT d_id FROM student WHERE s_name = 'Ravi')
  AND s_name <> 'Ravi';





Comments

Post a Comment

Popular posts from this blog

Data Control Language

 Data Control Language (DCL) 🔹 Step 1: Login as Admin (root) Open MySQL command line and login as root (or another admin account): mysql -u root -p Enter your root password. 🔹 Step 2: Create Database and Table CREATE DATABASE UniversityDB; USE UniversityDB; CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR ( 50 ), Marks INT ); INSERT INTO Students VALUES ( 1 , 'Amit' , 85 ), ( 2 , 'Raj' , 70 ), ( 3 , 'Neha' , 92 ), ( 4 , 'Sneha' , 60 ); 🔹   Step 3: Create Users CREATE USER 'user1' @ 'localhost' IDENTIFIED BY 'pass1' ; CREATE USER 'user2' @ 'localhost' IDENTIFIED BY 'pass2' ; CREATE USER 'user3' @ 'localhost' IDENTIFIED BY 'pass3' ; 'localhost' means these users can log in only from the same computer where MySQL is running. If you want t...
Read more

Checkpoint

Image
  What is a Checkpoint in DBMS? A checkpoint in a Database Management System (DBMS) is a mechanism used to minimize the amount of work needed for recovery after a system crash. It essentially acts like a “save point” — the system writes all modified data (dirty pages) from the buffer (RAM) to the disk and records a checkpoint in the log file . Purpose of Checkpoint Reduce recovery time — only redo or undo transactions after the last checkpoint. Ensure consistency between database and log files. Flush dirty pages (modified data not yet written to disk). Mark a safe state from which the system can recover. How It Works  When a checkpoint occurs: All committed transactions before the checkpoint are written to disk . The checkpoint record is written to the log file . Transactions after the checkpoint may still be in progress (these are handled by recovery if crash occurs). During recovery: Start from the last checkpoint , not from the b...
Read more