Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- 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