mirror of
https://github.com/robindhole/fundamentals.git
synced 2025-03-15 21:50:16 +00:00
2.8 KiB
2.8 KiB
Queries
Joins
- Get all batch names along with their instructor names.
SELECT
b.name, i.first_name, i.last_name
FROM
batches b
JOIN
instructors i ON b.instructor_id = i.id;
- Get all students along with their batch names if present else
NULL
.
SELECT
s.first_name, s.last_name, b.name
FROM
students s
LEFT JOIN
batches b ON s.batch_id = b.id;
- Get all students along with their batch names. Also, fetch all the batches which have no students.
SELECT
s.first_name, s.last_name, b.name
FROM
students s
RIGHT JOIN
batches b ON s.batch_id = b.id
- Get all the combinations of batches and instructors.
SELECT
b.name, i.first_name, i.last_name
FROM
batches b, instructors i
- Get all students with their instructors. If a student has no instructor, then show
NULL
for the instructor's name.
SELECT
s.first_name, s.last_name, i.first_name, i.last_name
FROM
students s
LEFT JOIN
batches b ON s.batch_id = b.id
LEFT JOIN
instructors i ON b.instructor_id = i.id;
Aggregation
- Get the maximum IQ in all students (Try without aggregation first).
SELECT
first_name, iq
FROM
students
ORDER BY iq DESC
LIMIT 1;
- Get the maximum IQ in all students (With aggregation).
SELECT
MAX(IQ) AS 'IQ'
FROM
students;
- Get the oldest batch from the batches table.
SELECT
MIN(start_date) AS 'start date'
FROM
batches;
- Fetch the number of batches that start with the word
Jedi
.
SELECT
COUNT(id)
FROM
batches
WHERE
name LIKE 'Jedi%';
- Get the average IQ of all students (Without using
AVG
)
SELECT
SUM(iq) / COUNT(iq) as 'Average IQ'
FROM
students;
- Get the average IQ of students in all batches.
SELECT
AVG(IQ) AS 'IQ'
FROM
students
WHERE
batch_id IS NOT NULL;
- Find the average IQ of students in each batch.
SELECT
batch_id, AVG(iq)
FROM
students
GROUP BY batch_id;
- Find the total number of students in each batch.
SELECT
b.name, COUNT(s.id)
FROM
batches b
LEFT JOIN
students s ON b.id = s.batch_id
GROUP BY b.id;
- Get the total number of batches taught by each instructor.
SELECT
i.first_name, COUNT(b.id)
FROM
instructors i
LEFT JOIN
batches b ON i.id = b.instructor_id
GROUP BY i.id;
- Find the average IQ of students in batches with batch ID
1
and2
.
SELECT
batch_id, AVG(iq)
FROM
students
WHERE batch_id IN (1, 2)
GROUP BY batch_id;
- Find count of students that are part of batches that have average IQ greater than
120
.
SELECT
batch_id, AVG(iq) as avg_iq, COUNT(iq)
FROM
students
GROUP BY batch_id
HAVING avg_iq > 130;