Read and Understand the Db Schema in Enrolment.sql
Student Enrollment SQL Challenge
How many questions tin you lot answer?
The Claiming
Given the database tables beneath, use your SQL skills to respond equally many of the questions that follow.
To go started, build the schema provided in SQL Fiddle or DB Fiddle using PostgreSQL v9.6.
Database Definition
Driblet TABLE IF EXISTS teachers;
CREATE TABLE teachers (
teacher_id BIGINT Primary Cardinal
, teacher_name VARCHAR(64)
); Driblet Tabular array IF EXISTS courses;
CREATE TABLE courses (
course_id VARCHAR(16) Master KEY
, course_name VARCHAR(128) NOT Nix
, teacher_id BIGINT
, FOREIGN Central (teacher_id) REFERENCES teachers(teacher_id) ); DROP Tabular array IF EXISTS students;
CREATE TABLE students (
student_id BIGINT Main KEY
, student_name VARCHAR(64)
); Drop TABLE IF EXISTS student_courses;
CREATE TABLE student_courses (
course_id VARCHAR(xvi)
, student_id BIGINT
, Strange Key (course_id) REFERENCES courses(course_id)
, FOREIGN Fundamental (student_id) REFERENCES students(student_id)
, PRIMARY Cardinal (course_id, student_id)
);
Questions
- Draw an entity relationship diagram (or land in words the entity relationships) that accurately describes this database.
- Implement a query to get a listing of all students and how many courses each student is enrolled in.
- Implement a query that shows the number of total-time and part-time students A full-time educatee is enrolled in at least iv courses. A part-time educatee is enrolled in at least 1 class, but no more than 3.
- Write a query that shows which teacher(s) are pedagogy the nearly number of courses.
- Write a query that shows which teacher(s) are teaching the least number of courses.
- Write a query that shows which instructor(due south) are teaching the most number of students.
- Write a query that shows which teacher(s) are teaching the least number of students.
- Write a query that shows what the average number of courses taught by a instructor.
- Write a query that tells us how many students are not enrolled. Who are these unenrolled students?
- Write a query that lists the courses in club of nearly popular to least pop.
Pause! If you lot haven't answered any of the questions above, try to answer as many as you can on your own earlier looking at the solutions below.
Solutions
There are multiple ways to write the SQL queries that answer this claiming's questions, and so they may not exactly match your ain. Here are my answers to the questions. Check out the solutions Gist, if y'all adopt to view them there.
1/ Draw an entity relationship diagram (or state in words the entity relationships) that accurately describes this database.
student is enrolled in 0 or more courses
teacher teaches 0 or more than courses
course has 0 or more than students enrolled there is a many-to-many relationship between students and courses
there is a one-to-many relationship between teachers and courses
ii/ Implement a query to get a list of all students and how many courses each student is enrolled in.
SELECT
s.student_id
, s.student_name
, COUNT(sc.course_id) AS course_count
FROM students south
LEFT JOIN student_courses sc
ON s.student_id = sc.student_id
Grouping By
s.student_id
, s.student_name
; iii/ Implement a query that shows the number of full-time and part-fourth dimension students. A full-fourth dimension educatee is enrolled in at least 4 courses. A office-time student is enrolled in at least 1 course, simply no more than 3.
WITH enrolled_student_course_counts Equally (
SELECT
due south.student_id
, south.student_name
, COUNT(sc.course_id) Every bit course_count
FROM students s
LEFT JOIN student_courses sc
ON s.student_id = sc.student_id
Grouping Past
s.student_id
, s.student_name
HAVING COUNT(sc.course_id) > 0
)
, student_enrollment_statuses Equally (
SELECT
student_id
, student_name
, Example WHEN course_count >= 4 THEN 'full-time'
WHEN course_count Betwixt one AND 3 THEN 'part-fourth dimension'
End AS student_enrollment_status
FROM enrolled_student_course_counts
)
SELECT
UPPER(student_enrollment_status) AS student_enrollment_status
, COUNT(student_enrollment_status) AS student_enrollment_status_count
FROM student_enrollment_statuses
Group BY student_enrollment_status
; 4/ Write a query that shows which instructor(s) are teaching the most number of courses.
WITH teacher_course_rankings AS (
SELECT
t.teacher_id
, t.teacher_name
, COUNT(c.course_id) AS teacher_course_count
, RANK() OVER(Lodge Past COUNT(c.course_id) DESC) Equally teacher_course_rank
FROM teachers t
LEFT Join courses c
ON t.teacher_id = c.teacher_id
GROUP By
t.teacher_id
, t.teacher_name
)
SELECT
teacher_id
, teacher_name
FROM teacher_course_rankings
WHERE teacher_course_rank = ane
; v/ Write a query that shows which instructor(s) are education the to the lowest degree number of courses.
WITH teacher_course_rankings Equally (
SELECT
t.teacher_id
, t.teacher_name
, COUNT(c.course_id) Every bit teacher_course_count
, RANK() OVER (ORDER Past COUNT(c.course_id)) AS teacher_course_rank
FROM teachers t
LEFT JOIN courses c
ON t.teacher_id = c.teacher_id
Grouping BY
t.teacher_id
, t.teacher_name
)
SELECT
teacher_id
, teacher_name
FROM teacher_course_rankings
WHERE teacher_course_rank = ane
; six/ Write a query that shows which instructor(s) are teaching the most number of students.
WITH teacher_student_rankings Every bit (
SELECT
t.teacher_id
, t.teacher_name
, COUNT(DISTINCT sc.student_id) Every bit teacher_student_count
, RANK() OVER (Society BY COUNT(DISTINCT sc.student_id) DESC) Equally teacher_student_rank
FROM teachers t
LEFT JOIN courses c
ON t.teacher_id = c.teacher_id
LEFT Join student_courses sc
ON c.course_id = sc.course_id
GROUP BY
t.teacher_id
, t.teacher_name
)
SELECT
teacher_id
, teacher_name
FROM teacher_student_rankings
WHERE teacher_student_rank = i
; 7/ Write a query that shows which teacher(s) are education the least number of students.
WITH teacher_student_rankings AS (
SELECT
t.teacher_id
, t.teacher_name
, COUNT(Distinct sc.student_id) Equally teacher_student_count
, RANK() OVER (Club BY COUNT(DISTINCT sc.student_id)) AS teacher_student_rank
FROM teachers t
LEFT Bring together courses c
ON t.teacher_id = c.teacher_id
LEFT Join student_courses sc
ON c.course_id = sc.course_id
Group BY
t.teacher_id
, t.teacher_name
)
SELECT
teacher_id
, teacher_name
FROM teacher_student_rankings
WHERE teacher_student_rank = one
; 8/ Write a query that shows what the average number of courses taught by a teacher.
WITH teacher_course_counts Equally (
SELECT
t.teacher_id
, t.teacher_name
, COUNT(c.course_id) As teacher_course_count
FROM teachers t
LEFT Join courses c
ON t.teacher_id = c.teacher_id
Grouping Past
t.teacher_id
, t.teacher_name
)
SELECT
AVG(teacher_course_count) avg_courses_taught
FROM teacher_course_counts
; 9/ Write a query that tells u.s.a. how many students are not enrolled. Who are these unenrolled students?
WITH student_course_counts Equally (
SELECT
s.student_id
, s.student_name
, COUNT(sc.course_id) As course_count
FROM students s
LEFT JOIN student_courses sc
ON due south.student_id = sc.student_id
Group By s.student_id
)
, student_enrollment_statuses Every bit (
SELECT
student_id
, student_name
, CASE WHEN course_count = 0 THEN 'unenrolled'
ELSE 'enrolled'
END As student_enrollment_status
FROM student_course_counts
)
SELECT
UPPER(student_enrollment_status) Every bit student_enrollment_status
, COUNT(student_enrollment_status) AS student_enrollment_status_count
FROM student_enrollment_statuses
WHERE student_enrollment_status = 'unenrolled'
Group BY student_enrollment_status
; 10/ Write a query that lists the courses in order of virtually popular to least popular.
SELECT
c.course_id
, c.course_name
, COUNT(sc.student_id) AS student_count
FROM courses c
LEFT Bring together student_courses sc
ON c.course_id = sc.course_id
GROUP Past
c.course_id
, c.course_name
ORDER BY 3 DESC
; In terms of habits and style, I tend to capitalize SQL keywords and place commas at the front when listing columns, and make heavy employ of common table expressions. I also endeavor to maintain consistent indentation and generously utilize reasonable cavalcade and table aliases.
Hungry for more than?
I highly recommend Way Analytics' SQL Tutorial for Data Assay. Information technology was "designed for people who want to answer questions with information," and explains the beginner, intermediate, and advanced SQL techniques for doing that quite well. Additionally, their tutorials have interactive elements, which allow yous to write queries confronting existing data sets.
Also, take a await at this 4-hour long SQL tutorial found on freeCodeCamp'south YouTube channel. The tutorial provides a solid introduction to SQL for beginners.
Cheers for reading!
If you learned something new or enjoyed reading this article, please clap it up 👏 and share information technology and then that others will run across it. Experience free to leave a annotate too.
Source: https://medium.com/the-core/student-enrollment-sql-challenge-25bf160cfa60
0 Response to "Read and Understand the Db Schema in Enrolment.sql"
Post a Comment