Given the following relational database schema: (using SQL please make sure its
ID: 3853906 • Letter: G
Question
Given the following relational database schema: (using SQL please make sure its accurate).
1. List the name, SSN and the number of courses the student has taken (courses with the same CourseNumber taken in different quarters are counted as different courses).
2. Answer #1 assuming courses with the same CourseNumber taken in different quarters are considered as one course.
3. List the name, SSN and number of courses the student has taken and complete, i.e. received a letter grade (courses with the same CourseNumber taken in different quarters are counted as different courses)
4. List the name, SSN and number of courses the student has taken and complete with a letter grade C or better (courses with the same CourseNumber taken in different quarters are counted as different courses).
5 List the Course Title and CourseNumber which does not have any prerequisite.
6.List the name of every student and SSN who earned an A in all courses he or she has completed, i.e. every grade is either Null or an A)
7.List the name of every student, SSN and the CourseNumber who has taken the course at least three times.
8.List the name of every student, SSN, every quarter, and the number of courses he or she has taken in the listed quarter.
9.List the name of every student and SSN who has not taken any course more than
once.
10.List the name, SSN , the number of courses the student has taken, and the number of courses completed(courses with the same CourseNumber taken in different quarters are counted as different courses).
11.List every two CourseNumber and their titles which have the same prerequisites.
12.List the name of every student and SSN who has completed all the courses he/she has taken and earned an A in each course, i.e. every grade is an A and no NULL value
13.List the name of every student and SSN who earned no A in any course.
14.List the name and major of every student who has only taken courses that meet MW afternoon (12 or after).
15.List the name and major of every student who has taken all the courses that meet MW afternoon.
16.List the name and major of every student who has not taken any course that meets MW afternoon.
17.List every CoursaeNumber and Quarter which has the highest enrollment.
18. List every CourseNumber and CourseTitle which has the highest enrollment based on all quarters.
19.List the name and major of every student who has completed the highest number of units.
20. List every Course tile which is a prerequiste for the largest number of courses.
Given the following relational database schema: Student (SSN, Name, Major) Course (CourseNumber. PrerequisiteCourseNumber, Course Title, NumberUnits) Course (CouneNimbxs Penegiscember, Coune Section = (CourseNumber,Quarter, Room Number, DayTime), where DayTime is ofthe form MW 1:0-2:00PM Enrollment = (SSN CourseNumber Quarter, Grade)// Grade is either Null or a letter grade.Explanation / Answer
1.
Use GROUP BY function to group the columns of the tables Student and Enrollment, then use the SELECT function to print the desired results
SELECT s.Name, s.SSN, COUNT(*) as NumberOfCourses
FROM Student s, Enrollment e
WHERE s.SSN=e.SSN
GROUP BY s.Name, s.SSN;
2.
Quarter is not taken as part of the query. Thus, the courses are not distinguished with respect to quarters.
SELECT s.Name, s.SSN, COUNT (*)
FROM Student s, (SELECT SSN, CourseNumber
FROM Enrolment
GROUP BY SSN, CourseNumber) AS e
WHERE s.SSN=e.SSN
GROUP BY s.SSN, s.NAME
3.
The condition written in WHERE clause selects the NOT NULL grades.
SELECT s.Name, s.SSN, COUNT(*)
FROM STUDENT s, ENROLMENT e
WHERE s.SSN=e.SSN AND e.GRADE is NOT NULL
GROUP BY s.Name, s.SSN;
4.
WHERE clause selects the Grade from table Enrolment which distinguishes the courses with respect to Quarter.
SELECT s.Name, s.SSN, s.SSN, COUNT(*) AS NumberOfCourses
FROM Students, Enrollment e
WHERE s.SSN = e.SSN AND e.GRADE IN(SELECT Grade
From Enrollment
WHERE Grade = ‘A’ or Grade = ‘B’ OR
Grade ‘C’)
GROUP BY S.Name, S.SSN;
5.
The courses where prerequisite is NULL are selected from the table Course.
SELECT C.CourseTitle, C.CourseNumber
FROM Course C
WHERE C.PrerequisiteCourseNumber is NULL;
6.
NOT LIKE A selects the tuples without A from the schema.
NOT IN selects all the tuple that are not in the nested query result. Thus, it selects all the tuples that are having A.
SELECT s.Name, S.SSN
FROM Student s, Enrollment e
WHERE s.SSN=e.SSN AND
s.Name NOT IN (SELECT s1.Name FROM Enrollment e1, Student s1
WHERE e1.SSN=s1.SSN AND e1.Grade NOT LIKE ‘A’);
7.
HAVING COUNT is used to specify the count of particular entity.
SELECT s.Name, E.SSN, E.CourseNumber
FROM Student AS s, Enrollment AS e
WHERE s.SSN=e.SSN
GROUP BY s.Name, E.SSN, e.CourseNumber HAVING COUNT(*) >=3;
8.
The table is grouped by Name, quarter and SSN, and Name, SSN, Quarter and count of the courses are listed using SELECT clause.
SELECT s.Name, S.SSN, e.Quarter, COUNT(*)
FROM Student S, Enrollment e
WHERE s.SSN=e.SSN
GROUP BY s.Name, s.SSN, e.Quarter
9.
SELECT s.Name, s.SSN,
FROM Student s
WHERE s.Name NOT IN (SELECT s1.Name
FROM Student s1, Enrollment e1, Enrollment e2
WHERE s1.SSN=e1.SSn AND
e1.SSN=e2.SSN AND
e1.CourseNumner = e2.CourseNumber);
10.
SELECT s.SName, s.SSN, COUNT(*) AS NumberOfCourses, COUNT(IF(Grade IS NOT NULL, 1, 0)) AS NumberOfCoursesCompleted
FROM Student s, Enrollment e
WHERE s.SSN = e.SSN
GROUP BY s.Name, s.SSN;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.