Knowee
Questions
Features
Study Tools

Give the database schema the following tables:Student (studentId, firstName, lastName, gender, birthday, phone, address, classId)Class (classId, className)Which code snippet will list the id and names of class with more than 15 students?SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.classNameHAVING COUNT(*) > 15;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student sWHERE c.classId = s.classId AND amount > 15GROUP BY c.classId, c.className;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.classNameHAVING amount > 15;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdWHERE COUNT(*) > 15;

Question

Give the database schema the following tables:Student (studentId, firstName, lastName, gender, birthday, phone, address, classId)Class (classId, className)Which code snippet will list the id and names of class with more than 15 students?SELECT c.classId, c.className, COUNT() AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.classNameHAVING COUNT() > 15;SELECT c.classId, c.className, COUNT() AS amountFROM dbo.Class c INNER JOIN dbo.Student sWHERE c.classId = s.classId AND amount > 15GROUP BY c.classId, c.className;SELECT c.classId, c.className, COUNT() AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.classNameHAVING amount > 15;SELECT c.classId, c.className, COUNT() AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdWHERE COUNT() > 15;

...expand
🧐 Not the exact question you are looking for?Go ask a question

Solution

The correct code snippet is:

SELECT c.classId, c.className, COUNT(*) AS amount
FROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classId
GROUP BY c.classId, c.className
HAVING COUNT(*) > 15;

This SQL query works as follows:

  1. The FROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classId part joins the Class and Student tables on the classId field. This means it combines rows from both tables where the classId value is the same.

  2. The SELECT c.classId, c.className, COUNT(*) AS amount part selects the classId and className fields from the Class table and counts the number of students in each class (because COUNT(*) counts the number of rows).

  3. The GROUP BY c.classId, c.className part groups the results by classId and className. This means it will give a separate count for each class.

  4. The HAVING COUNT(*) > 15 part filters out the classes with 15 or fewer students. This means it will only show classes with more than 15 students.

This problem has been solved

Similar Questions

Give the database schema the following tables: Student (studentId, firstName, lastName, gender, birthday, phone, address, classId) Class (classId, className) Which code snippet will display records with the following 3 columns: classId, className and amount (amount of students)?SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classId;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s WHERE c.classId = s.classIdGROUP BY c.classId, c.className;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId;SELECT c.classId, c.className, COUNT(*) AS amountFROM dbo.Class c INNER JOIN dbo.Student s ON c.classId = s.classIdGROUP BY c.classId, c.className;

Classes More Than 5 StudentsThere is a table courses with columns: student and classPlease list out all classes which have more than or equal to 5 students.For example, the table:studentABCDEFGHIclassMathEnglishMathBiologyMathComputerMathMathMathShould output:classmathNote:The students should not be counted duplicate in each course.Optionsselect classfrom coursesgroup classhaving count(distinct student) >= 5;select classfrom coursesgroup by studenthaving count(distinct student) >= 5;select classfrom coursesclasshaving count(distinct student) >= 5;select classfrom coursesgroup by classhaving count(distinct student) >= 5;Finish

Consider following schema and write SQL for given statements.Student (RollNo, Name, DeptCode, City)Department (DeptCode, DeptName)Result (RollNo, Semester, SPI)1. List out the RollNo, Name along with SPI of Student.2. Display student name who got highest SPI in semester 1.3. Display the list of students whose DeptCode is 5, 6,7,10

Consider these following tables and only solve the query. Tables: 1) student: s_no (primary key), student_name, course_no (foreign key), age 2) courser-course-no (primary key), course-name. Query: Display all the student name, course name and also age of the students, whose age is between 23 to 27 and course having 'BCA'. Find out the students whose age is maximum.

Given 3 tables, students(id, name) , courses(id, name) , grades(id, course_id, student_id, grade), find the name of the most popular course (the one with the most enrolled students and students should be passing, i.e. achieving a grade of at least 50) and if there is a tie, get the course name that's lexicographically smaller. Your query should output a table with the following columns (name)

1/3

Upgrade your grade with Knowee

Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.