Ravindra BagaleCourses & study guides

10. MySQL Part 1: Install, Create, Insert and SELECT

10.10 Subqueries

By Last updated:

Subquery mhanje query chya aat query – aadhi aatli chalte, tiche uttar baherchi vaprte.

-- students who paid more than the average
SELECT name, fees_paid FROM students
WHERE fees_paid > (SELECT AVG(fees_paid) FROM students);

-- students enrolled in 'Ethical Hacking'
SELECT name FROM students
WHERE student_id IN (
  SELECT student_id FROM enrollments
  WHERE course_id = (SELECT course_id FROM courses WHERE course_name = 'Ethical Hacking')
);

-- EXISTS: students with at least one score above 85
SELECT s.name FROM students s
WHERE EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.score > 85);

-- subquery in FROM (derived table)
SELECT city, total FROM (
  SELECT city, SUM(fees_paid) AS total FROM students GROUP BY city
) AS t WHERE total > 20000;

Why this matters for security

Blind SQL injection relies on subqueries: AND (SELECT SUBSTRING(password,1,1) FROM users LIMIT 1) = 'a' asks the database a yes/no question one character at a time. Tools like sqlmap automate thousands of such subqueries – that is why prepared statements, not input "cleaning", are the real fix.

Ravindra Bagale's Tip

WHERE id = (SELECT ...) lihila aani subquery ne ek peksha jast rows dile tar "Subquery returns more than 1 row" error yeto. Khup students ithe atakta. Anek values astil tar = chya jaagi IN vapra.

Practice task

Find the student(s) with the highest score using a subquery, and the courses that have no enrollment using NOT IN.

Thodkyaat sangaycha tar

  • Install: mariadb105-server (Amazon Linux 2023) or mysql-server (Ubuntu); run mysql_secure_installation; keep 3306 private.
  • CREATE DATABASE, USE, CREATE TABLE, DESCRIBE, INSERT INTO t (cols) VALUES (...).
  • SELECT cols FROM t WHERE ... ORDER BY ... LIMIT n; DISTINCT, aliases with AS.
  • LIKE (%, _), IN, BETWEEN (inclusive), IS NULL.
  • Aggregates COUNT SUM AVG MIN MAX + GROUP BY; WHERE filters rows, HAVING filters groups.
  • JOINs: INNER, LEFT, RIGHT, SELF; subqueries with IN, EXISTS, derived tables.
  • OR 1=1, ORDER BY n, UNION SELECT and subqueries are also the building blocks of SQL injection.

Samjla ka? Nasel tar pratyek query punha type kara aani output bagha. Aata pudhe jaauya – data badalne, keys aani users.