By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- Craete students table (id, name)
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name varchar(64) NOT NULL
);
ALTER TABLE students ADD CONSTRAINT students_name_uk UNIQUE (name);
CREATE TABLE
ALTER TABLE
-- Craete scores table (student_id, score)
CREATE TABLE scores (
id SERIAL PRIMARY KEY,
student_id integer NOT NULL,
score integer NOT NULL
);
ALTER TABLE scores ADD CONSTRAINT scores_student_id_fk FOREIGN KEY (id) REFERENCES students;
CREATE TABLE
ALTER TABLE
-- populate students table
INSERT INTO students(name) VALUES('Clinton');
INSERT INTO students(name) VALUES('Bush');
INSERT INTO students(name) VALUES('Obama');
INSERT INTO students(name) VALUES('Trump');
INSERT INTO students(name) VALUES('Biden');
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
-- check students table data
SELECT * FROM students;
| id | name |
|---|---|
| 1 | Clinton |
| 2 | Bush |
| 3 | Obama |
| 4 | Trump |
| 5 | Biden |
SELECT 5
-- populate scores table
INSERT INTO scores(student_id, score) VALUES(1, 90);
INSERT INTO scores(student_id, score) VALUES(2, 90);
INSERT INTO scores(student_id, score) VALUES(3, 95);
INSERT INTO scores(student_id, score) VALUES(4, 95);
INSERT INTO scores(student_id, score) VALUES(5, 80);
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
-- check scored table data
SELECT * FROM scores;
| id | student_id | score |
|---|---|---|
| 1 | 1 | 90 |
| 2 | 2 | 90 |
| 3 | 3 | 95 |
| 4 | 4 | 95 |
| 5 | 5 | 80 |
SELECT 5
-- Get scores of all students from two tables
SELECT st.id, st.name, sc.score
FROM students st
INNER JOIN scores sc ON st.id = sc.student_id;
| id | name | score |
|---|---|---|
| 1 | Clinton | 90 |
| 2 | Bush | 90 |
| 3 | Obama | 95 |
| 4 | Trump | 95 |
| 5 | Biden | 80 |
SELECT 5
-- Get scores of all students ordered by score desc
SELECT st.id, st.name, sc.score
FROM students st
INNER JOIN scores sc ON st.id = sc.student_id
ORDER BY sc.score desc;
| id | name | score |
|---|---|---|
| 3 | Obama | 95 |
| 4 | Trump | 95 |
| 1 | Clinton | 90 |
| 2 | Bush | 90 |
| 5 | Biden | 80 |
SELECT 5
-- Get scores of all students ordered by score desc, limit 3
SELECT st.id, st.name, sc.score
FROM students st
INNER JOIN scores sc ON st.id = sc.student_id
ORDER BY sc.score desc
LIMIT 3;
| id | name | score |
|---|---|---|
| 3 | Obama | 95 |
| 4 | Trump | 95 |
| 1 | Clinton | 90 |
SELECT 3
-- Get max, min scores from scores
SELECT MAX(score), MIN(score) FROM scores;
| max | min |
|---|---|
| 95 | 80 |
SELECT 1
-- Get different scores and occurances from scores, DESC
SELECT score, count(*)
FROM scores
GROUP by score
ORDER BY score DESC;
| score | count |
|---|---|
| 95 | 2 |
| 90 | 2 |
| 80 | 1 |
SELECT 3
-- Get all students who scored the highest score
SELECT s.id, s.name, sc.score, ':)' as smilie
FROM students s
JOIN scores sc ON s.id=sc.student_id
AND sc.score = (SELECT MAX(score) FROM scores);
| id | name | score | smilie |
|---|---|---|---|
| 3 | Obama | 95 | :) |
| 4 | Trump | 95 | :) |
SELECT 2
-- Get all students who scored specific given score
SELECT s.id, s.name, sc.score, ':)' as smilie
FROM students s
JOIN scores sc ON s.id=sc.student_id
AND sc.score = 90;
| id | name | score | smilie |
|---|---|---|---|
| 1 | Clinton | 90 | :) |
| 2 | Bush | 90 | :) |
SELECT 2