practice questions.txt

Retrieve all students.

Find all courses taught by 'Mr. Johnson'.
Get the names of all students who are 16 years old.
List all students enrolled in 'Mathematics'.
Count the number of students in each class.
Get the list of courses along with the instructor names, ordered by course name.
Find the total number of enrollments.
Get the list of students along with the courses they are enrolled in.
Find the students who are enrolled in more than one course.
Get the list of instructors and the number of courses they teach.
List the students and their ages who are in the 10th class.
Find the courses that 'Emily Davis' is enrolled in.
Count the number of students enrolled in each course.
Get the list of students who are not enrolled in any course.
Find the courses that have more than 2 students enrolled.
Retrieve the details of students who are enrolled in 'Chemistry'.
List all courses that 'Jane Smith' is not enrolled in.
Find the average age of students in each class.
Get the list of students and the number of courses they are enrolled in.
List the names of students along with the names of the instructors for their enrolled courses.



=========Answers============
-- Retrieve all students.
-- select * from students;
-- Find all courses taught by 'Mr. Johnson'.
-- select course_name 
--  from courses 
-- where instructor='mr. johnson';
-- Get the names of all students 
-- who are 16 years old.
-- select student_name
-- from students
-- where age=16

-- List all students enrolled in 'Mathematics'.
-- select s.student_name
-- from students as s
-- inner join enrolements as e 
-- ON e.student_id= s.student_id
-- inner join courses as c 
-- ON c.course_id= e.course_id
-- where c.course_name= "Mathematics"

-- Count the number of students in each class.
-- select class, count(class) as count
-- from students
-- group by class;
-- Get the list of courses along with the
-- instructor names
-- , ordered by course name.
-- select course_name, instructor       
-- from courses  
-- order by course_name;

-- Find the total number of enrollments.
-- select count(*) as total from enrolements;
-- Get the list of students along 
-- with the courses
-- they are enrolled in.
-- select s.student_name,c.course_name
-- from students as s
-- inner join enrolements as e ON e.student_id= s.student_id
-- inner join courses as c ON c.course_id=e.course_id;
-- Find the students who are
--  enrolled in more than one course.
-- select s.student_name 
-- from students as s
-- inner join enrolements as e 
-- on s.student_id =e.student_id 
-- group by s.student_id
-- having count(e.student_id)>1;


-- Get the list of instructors and 
-- the number of courses they teach.
use practice1;
-- select instructor,count(*) as n_course
-- from courses
-- group by instructor;
-- List the students and their ages 
-- who are in the 10th class.
-- select student_name,age
-- from students
-- where class=10
-- Find the courses that 
-- 'Emily Davis' is enrolled in.
-- select c.course_name
-- from courses as c
-- inner join enrolements as e 
-- ON c.course_id= e.course_id
-- inner join students as s
-- ON e.student_id=s.student_id 
-- where s.student_name='Emily Davis';
-- Count the number of students 
-- enrolled in each course.
-- select c.course_name,count(e.student_id) as no_of_students
-- from courses as c
-- left join enrolements as e 
-- ON e.course_id=c.course_id
-- group by c.course_name
-- Get the list of students
--  who are not enrolled in any course.
-- select s.student_name
-- from students as s
-- left join enrolements as e ON e.student_id=s.student_id
-- where e.course_id is null
-- Find the courses that have more than 2 students enrolled.
-- use above query with value 2
-- Retrieve the details of students who are enrolled in 'Chemistry'.
-- use above query instead of instructor name use
-- course-name
-- List all courses that 
-- 'Jane Smith' is not enrolled in.
select c.course_name,c.course_id from courses as c
where c.course_id NOT IN (select e.course_id
from enrolements as e
inner join students as s 
ON s.student_id= e.student_id
where s.student_name= "Jane Smith");


-- select s.student_name,c.instructor
-- from students as s
-- inner join enrolements as e ON e.student_id= s.student_id
-- inner join courses as c ON c.course_id=e.course_id;