ctci_student_enrollment_db.sql

DROP TABLE IF EXISTS teachers;
CREATE TABLE teachers (
  teacher_id BIGINT PRIMARY KEY
  , teacher_name VARCHAR(64)
);
INSERT INTO teachers VALUES (1001, 'Zelenski, J.');
INSERT INTO teachers VALUES (1002, 'Borestein, J.');
INSERT INTO teachers VALUES (1003, 'Peich, C.');
INSERT INTO teachers VALUES (1004, 'Engler, D.');
INSERT INTO teachers VALUES (1005, 'Lee, C.');
INSERT INTO teachers VALUES (1006, 'Schwarz, K.');

DROP TABLE IF EXISTS courses;
CREATE TABLE courses (
  course_id VARCHAR(16) PRIMARY KEY
  , course_name VARCHAR(128) NOT NULL
  , teacher_id BIGINT
  , FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
INSERT INTO courses VALUES ('CS 1U', 'Practical Unix', 1001);
INSERT INTO courses VALUES ('CS 11SI', 'How to Make VR: Introduction to Virtual Reality Design and Development', 1002);
INSERT INTO courses VALUES ('CS 21SI', 'AI for Social Good', 1003);
INSERT INTO courses VALUES ('CS 49N', 'Using Bits to Control Atoms', 1004);
INSERT INTO courses VALUES ('CS 82SI', 'Wellness in Tech: Designing an Intentional Lifestyle in a Tech-Driven World', 1003);
INSERT INTO courses VALUES ('CS 100A', 'Algorithms I', 1003);
INSERT INTO courses VALUES ('CS 100B', 'Algorithms in the Real World', 1001);
INSERT INTO courses VALUES ('CS 106B', 'Programming Abstractions', 1005);
INSERT INTO courses VALUES ('CS 106L', 'C++ Programming for Linux', 1005);

DROP TABLE IF EXISTS students;
CREATE TABLE students (
  student_id BIGINT PRIMARY KEY
  , student_name VARCHAR(64)
);
INSERT INTO students VALUES (3001, 'Delroy Green');
INSERT INTO students VALUES (3002, 'Anna Williams');
INSERT INTO students VALUES (3003, 'Romario Grant');
INSERT INTO students VALUES (3004, 'Kristen Brown');
INSERT INTO students VALUES (3005, 'Bobby Wilson');
INSERT INTO students VALUES (3006, 'Reina Clarke');
INSERT INTO students VALUES (3007, 'Anthony Dennis');
INSERT INTO students VALUES (3008, 'Crystal White');
INSERT INTO students VALUES (3009, 'Monica Erickson');
INSERT INTO students VALUES (3010, 'George Valdez');
INSERT INTO students VALUES (3011, 'Angela Concepción');
INSERT INTO students VALUES (3012, 'Bradley Emerson');
INSERT INTO students VALUES (3013, 'Derrick Anderson');
INSERT INTO students VALUES (3014, 'David Williamson');
INSERT INTO students VALUES (3015, 'Susan Redd');
INSERT INTO students VALUES (3016, 'Maya Harris');
INSERT INTO students VALUES (3017, 'Amir Khan');
INSERT INTO students VALUES (3018, 'Hannah Horowitz');
INSERT INTO students VALUES (3019, 'Malek Hadad');
INSERT INTO students VALUES (3020, 'Esther Kattan');

DROP TABLE IF EXISTS student_courses;
CREATE TABLE student_courses (
  course_id VARCHAR(16)
  , student_id BIGINT
  , FOREIGN KEY (course_id) REFERENCES courses(course_id)
  , FOREIGN KEY (student_id) REFERENCES students(student_id)
  , PRIMARY KEY (course_id, student_id)
);
-- Delroy Green: 7 courses
INSERT INTO student_courses VALUES ('CS 1U', 3001);
INSERT INTO student_courses VALUES ('CS 11SI', 3001);
INSERT INTO student_courses VALUES ('CS 21SI', 3001);
INSERT INTO student_courses VALUES ('CS 49N', 3001);
INSERT INTO student_courses VALUES ('CS 82SI', 3001);
INSERT INTO student_courses VALUES ('CS 100A', 3001);
INSERT INTO student_courses VALUES ('CS 106B', 3001);

-- Anna Williams: 7 courses
INSERT INTO student_courses VALUES ('CS 21SI', 3002);
INSERT INTO student_courses VALUES ('CS 49N', 3002);
INSERT INTO student_courses VALUES ('CS 82SI', 3002);
INSERT INTO student_courses VALUES ('CS 100A', 3002);
INSERT INTO student_courses VALUES ('CS 1U', 3002);
INSERT INTO student_courses VALUES ('CS 106B', 3002);
INSERT INTO student_courses VALUES ('CS 106L', 3002);

-- Romario Grant: 1 course
INSERT INTO student_courses VALUES ('CS 1U', 3003);

-- Kristen Brown: 3 courses
INSERT INTO student_courses VALUES ('CS 49N', 3004);
INSERT INTO student_courses VALUES ('CS 82SI', 3004);
INSERT INTO student_courses VALUES ('CS 21SI', 3004);

-- Bobby Wilson: 2 courses
INSERT INTO student_courses VALUES ('CS 106B', 3005);
INSERT INTO student_courses VALUES ('CS 106L', 3005);

-- Reina Clarke: 4 courses
INSERT INTO student_courses VALUES ('CS 1U', 3006);
INSERT INTO student_courses VALUES ('CS 82SI', 3006);
INSERT INTO student_courses VALUES ('CS 106B', 3006);
INSERT INTO student_courses VALUES ('CS 106L', 3006);

-- Anthony Dennis: 5 courses
INSERT INTO student_courses VALUES ('CS 21SI', 3007);
INSERT INTO student_courses VALUES ('CS 49N', 3007);
INSERT INTO student_courses VALUES ('CS 82SI', 3007);
INSERT INTO student_courses VALUES ('CS 1U', 3007);
INSERT INTO student_courses VALUES ('CS 100A', 3007);

-- Crystal White: 4 courses
INSERT INTO student_courses VALUES ('CS 1U', 3008);
INSERT INTO student_courses VALUES ('CS 21SI', 3008);
INSERT INTO student_courses VALUES ('CS 106B', 3008);
INSERT INTO student_courses VALUES ('CS 106L', 3008);

-- Monica Erickson: 4 courses
INSERT INTO student_courses VALUES ('CS 82SI', 3009);
INSERT INTO student_courses VALUES ('CS 11SI', 3009);
INSERT INTO student_courses VALUES ('CS 106B', 3009);
INSERT INTO student_courses VALUES ('CS 106L', 3009);

-- George Valdez: 6 courses
INSERT INTO student_courses VALUES ('CS 100A', 3010);
INSERT INTO student_courses VALUES ('CS 100B', 3010);
INSERT INTO student_courses VALUES ('CS 49N', 3010);
INSERT INTO student_courses VALUES ('CS 21SI', 3010);
INSERT INTO student_courses VALUES ('CS 106B', 3010);
INSERT INTO student_courses VALUES ('CS 11SI', 3010);

-- Angela Concepción: 0 courses

-- Bradley Emerson: 2 courses
INSERT INTO student_courses VALUES ('CS 106B', 3012);
INSERT INTO student_courses VALUES ('CS 106L', 3012);

-- Derrick Anderson: 0 courses

-- David Williamson: 3 courses
INSERT INTO student_courses VALUES ('CS 1U', 3014);
INSERT INTO student_courses VALUES ('CS 49N', 3014);
INSERT INTO student_courses VALUES ('CS 21SI', 3014);

-- Susan Redd: 4 courses
INSERT INTO student_courses VALUES ('CS 100A', 3015);
INSERT INTO student_courses VALUES ('CS 100B', 3015);
INSERT INTO student_courses VALUES ('CS 49N', 3015);
INSERT INTO student_courses VALUES ('CS 21SI', 3015);

-- Maya Harris: 4 courses
INSERT INTO student_courses VALUES ('CS 1U', 3016);
INSERT INTO student_courses VALUES ('CS 21SI', 3016);
INSERT INTO student_courses VALUES ('CS 100A', 3016);
INSERT INTO student_courses VALUES ('CS 82SI', 3016);

-- Amir Khan: 4 courses
INSERT INTO student_courses VALUES ('CS 49N', 3017);
INSERT INTO student_courses VALUES ('CS 11SI', 3017);
INSERT INTO student_courses VALUES ('CS 100A', 3017);
INSERT INTO student_courses VALUES ('CS 106B', 3017);

-- Hannah Horowitz: 2 courses
INSERT INTO student_courses VALUES ('CS 1U', 3018);
INSERT INTO student_courses VALUES ('CS 11SI', 3018);

-- Malek Hadad: 7 courses
INSERT INTO student_courses VALUES ('CS 1U', 3019);
INSERT INTO student_courses VALUES ('CS 11SI', 3019);
INSERT INTO student_courses VALUES ('CS 21SI', 3019);
INSERT INTO student_courses VALUES ('CS 49N', 3019);
INSERT INTO student_courses VALUES ('CS 82SI', 3019);
INSERT INTO student_courses VALUES ('CS 100A', 3019);
INSERT INTO student_courses VALUES ('CS 100B', 3019);

-- Esther Kattan: 3 courses
INSERT INTO student_courses VALUES ('CS 100A', 3020);
INSERT INTO student_courses VALUES ('CS 100B', 3020);
INSERT INTO student_courses VALUES ('CS 106B', 3020);

questions.md

Instructions

0. Copy and paste the code into sqlfiddle and build the schema using PostgreSQL 9.6.

1. Answer the following questions.

Questions

0. Draw an entity relationship diagram (or state in words the entity relationships) that accurately describes this database.

1. Implement a query to get a list of all students and how many courses each student is enrolled in.

2. Implement a query that shows the number of full-time and part-time students A full-time student is enrolled in at least 4 courses. A part-time student is enrolled in at least 1 course, but no more than 3.

3. Write a query that shows which teacher(s) are teaching the most number of courses.

4. Write a query that shows which teacher(s) are teaching the least number of courses.

5. Write a query that shows which teacher(s) are teaching the most number of students.

6. Write a query that shows which teacher(s) are teaching the least number of students.

7. Write a query that shows what the average number of courses taught by a teacher.

8. Write a query that tells us how many students are not enrolled. Who are these unenrolled students?

9. Write a query that lists the courses in order of most popular to least popular.

A MySQL implementation (any and all feedback welcome):

-- Draw an entity relationship digram (or state in words the entity relationships) that accurately describes this database.
courses (pk: course_id, fk reference to teachers.teacher_id, referred to from the student_courses table
student_courses (compound pk on both course_id and student_id)
students (pk student_id, fk reference from the student_courses table)
teachers (pk teacher id, fk reference from the courses table)

An interesting callout is the student_courses table that serves as a bridge table between the courses and students table. Many students can take many course, and this adequately establishes this many to many relationship.

-- Implement a query to get a list of all students and how many courses each student is enrolled in.

select sc.student_id, student_name, count(course_id) from student_courses sc
left join students s on
s.student_id = sc.student_id
group by 1, 2;

-- Implement a query that shows the number of full-time and part-time students. A full-time student is enrolled in at least 4 courses. A part-time student is enrolled in at least 1 course, but no more than 3.

select sum(fulltime), sum(parttime), sum(invalid) from
(select
distinct student_id,
case when count(course_id) >= 4 then 1 else 0 end as fulltime,
case when count(course_id) > 0 and count(course_id) < 4 then 1 else 0 end as parttime,
case when count(course_id) < 0 then 1 else 0 end as invalid
from student_courses
group by 1) statuses;

-- Write a query that shows which teacher(s) are teaching the most number of courses.

select teacher_id, count(course_id) from courses
group by 1
order by 2 desc;

-- Write a query that shows which teacher(s) are teaching the least number of courses.
select teacher_id, count(course_id) from courses
group by 1
order by 2;

-- Write a query that shows which teacher(s) are teaching the most number of students.

select teacher_id, count(student_id) from courses c
join student_courses sc
on c.course_id=sc.course_id
group by 1
order by 2 desc;

-- Write a query that shows which teacher(s) are teaching the least number of students.

select teacher_id, count(student_id) from courses c
join student_courses sc
on c.course_id=sc.course_id
group by 1
order by 2;

-- Write a query that shows what the average number of courses taught by a teacher.
select avg(count_classes.my_course_load)
from
(select
teacher_id, count(course_id) as my_course_load
from courses
group by 1) as count_classes;

-- Write a query that tells us how many students are not enrolled. Who are these unenrolled students?
select count(*) from students s
left outer join student_courses sc
on s.student_id = sc.student_id
where course_id is null;

select * from students s
left outer join student_courses sc
on s.student_id = sc.student_id
where course_id is null;
-- Derrick Anderson, 3013
-- Angela Concepción, 3011

-- Write a query that lists the courses in order of most popular to least popular.
select course_id, count(student_id)
from student_courses
group by 1
order by 2 desc;

/*********************************************************************************************************
POSTGRESQL IMPLEMENTATION

Solving the challenge queries on PostgreSQL using JOINS and CTEs (Common Table Expressions)
varun.dhawan@gmail.com
https://data-nerd.blog/

SHOW server_version;

server_version|
--------------+
13.4 |
*********************************************************************************************************/

Q1. DRAW AN ENTITY RELATIONSHIP DIAGRAM (OR STATE IN WORDS THE ENTITY RELATIONSHIPS) THAT ACCURATELY DESCRIBES THIS DATABASE

a. A STUDENT can be enrolled in 0 or more COURSES
b. A TEACHER can teach 0 or more COURSES
c. A COURSE can have 0 or more students ENROLLED
d. There is a MANY-TO-MANY relationship between STUDENTS and COURSES
e. There is a ONE-TO-MANY relationship between TEACHERS and COURSES 
f. There is a MANY-TO-MANY relationship between TEACHERS and STUDENTS

Q2. 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 "count_course"
	FROM students s 
	 LEFT JOIN student_courses sc 
	 	ON s.student_id = sc.student_id 
	 		GROUP BY s.student_id , s.student_name 
	 		ORDER BY count_course desc;

Q3. IMPLEMENT A QUERY THAT SHOWS THE NUMBER OF FULL-TIME AND PART-TIME STUDENTS A FULL-TIME STUDENT IS ENROLLED IN AT LEAST 4 COURSES. A PART-TIME STUDENT IS ENROLLED IN AT LEAST 1 COURSE, BUT NO MORE THAN 3.

WITH student_enrollment_by_course_cte  AS
(
	SELECT 
		s.student_id
		, s.student_name
		, COUNT(sc.course_id) AS total_course
	FROM students s 
		LEFT JOIN student_courses sc 
	 	ON s.student_id = sc.student_id 
			GROUP BY s.student_id
	 			, s.student_name 
			HAVING COUNT(sc.course_id) > 0
)
, enrollment_status_cte AS (
  SELECT 
  	student_id
	, student_name
	, CASE 
		WHEN total_course >= 4 
			THEN 'full-time-course' 
		WHEN total_course >= 1 AND total_course <= 3 
			THEN 'part-time-course'
	  END course_status
	FROM student_enrollment_by_course_cte
)
SELECT
  course_status
 , COUNT(course_status) AS no_of_enrollment
FROM enrollment_status_cte
	GROUP BY course_status
	ORDER BY COUNT(course_status) DESC;

Q4. WRITE A QUERY THAT SHOWS WHICH TEACHER(S) ARE TEACHING THE MOST NUMBER OF COURSES.

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 
			GROUP BY t.teacher_id , t.teacher_name 
			ORDER BY teacher_course_count DESC
			LIMIT 1;			-- as here we only need that one teacher teaching most no. of courses

Q5. WRITE A QUERY THAT SHOWS WHICH TEACHER(S) ARE TEACHING THE LEAST NUMBER OF COURSES.

	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 
			GROUP BY t.teacher_id , t.teacher_name 
			ORDER BY teacher_course_count
			LIMIT 1;			-- as here we only need that one teacher teaching least no. of courses

Q6. WRITE A QUERY THAT SHOWS WHICH TEACHER(S) ARE TEACHING THE MOST NUMBER OF STUDENTS.

	SELECT 
		t.teacher_name  
		, COUNT(sc.student_id) AS teacher_student_count
	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_name 
				ORDER BY COUNT(sc.student_id) DESC
				LIMIT 1;		-- as here we only need that one teacher teaching most no. students

Q7. WRITE A QUERY THAT SHOWS WHICH TEACHER(S) ARE TEACHING THE LEAST NUMBER OF STUDENTS

	SELECT 
		t.teacher_name  
		, COUNT(sc.student_id) AS teacher_student_count
	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_name 
				ORDER BY COUNT(sc.student_id)
				LIMIT 1;		-- as here we only need that one teacher teaching least no. students

Q8. WRITE A QUERY THAT SHOWS WHAT THE AVERAGE NUMBER OF COURSES TAUGHT BY A TEACHER

	WITH teacher_course_cte AS 
	(				
		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 
				GROUP BY t.teacher_id , t.teacher_name 
	)
	SELECT AVG(teacher_course_count) AS avg_courses_taught_by_teacher
	 FROM teacher_course_cte;

Q9. WRITE A QUERY THAT TELLS US HOW MANY STUDENTS ARE NOT ENROLLED. WHO ARE THESE UNENROLLED STUDENTS?

	WITH teacher_course_cte AS 
	(				
	 	SELECT
			s.student_id
			, s.student_name
			, CASE 
				WHEN COUNT(sc.course_id) = 0 
					THEN 'unenrolled'
				ELSE 'enrolled'
			END enrollment_status
		FROM students s
			LEFT JOIN student_courses sc
			ON s.student_id = sc.student_id
				GROUP BY s.student_id

	)
	SELECT 
		enrollment_status
		, COUNT (enrollment_status)
		FROM teacher_course_cte
			GROUP BY enrollment_status
			HAVING enrollment_status = 'unenrolled';

Q10. WRITE A QUERY THAT LISTS THE COURSES IN ORDER OF MOST POPULAR TO LEAST POPULAR

	SELECT c.course_id
		, c.course_name 
		, COUNT(sc.student_id) 
	FROM courses c 
		LEFT JOIN student_courses sc 
			ON c.course_id = sc.course_id
			GROUP BY c.course_id, c.course_name 
			ORDER BY COUNT(sc.student_id) DESC;