This task concerns the following tables:PersonsidnameaddressageeyeColorgenderTakesClassesidperson_id → Personsclass_id → ClassesWrite a query that returns a table with columns name and nrOfClasses that contains the list of all names of people and the number of classes that this person takes.NoteYou can give names to columns in the SELECT clause by SELECT … as someName.
Question
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderTakesClassesidperson_id → Personsclass_id → ClassesWrite a query that returns a table with columns name and nrOfClasses that contains the list of all names of people and the number of classes that this person takes.NoteYou can give names to columns in the SELECT clause by SELECT … as someName.
Solution
To answer your question, you can use the SQL GROUP BY and COUNT functions to count the number of classes each person takes. Here is the SQL query:
SELECT p.name, COUNT(tc.class_id) as nrOfClasses
FROM Persons p
JOIN TakesClasses tc ON p.id = tc.person_id
GROUP BY p.name;
Here's a step-by-step explanation of the query:
-
SELECT p.name, COUNT(tc.class_id) as nrOfClasses: This line is selecting the name from the Persons table and counting the number of class_id's from the TakesClasses table for each person. Theas nrOfClassespart is renaming the count column to 'nrOfClasses'. -
FROM Persons p: This line is specifying the Persons table as the main table we are selecting from, and it's giving it an alias of 'p' for simplicity. -
JOIN TakesClasses tc ON p.id = tc.person_id: This line is joining the TakesClasses table (with an alias of 'tc') to the Persons table based on matching person_id's. -
GROUP BY p.name: This line is grouping the results by the name of the person. This means that for each unique name in the Persons table, it will count the number of classes they are taking.
This query will return a table with each person's name and the number of classes they are taking.
Similar Questions
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderClassesidkindTakesClassesidperson_id → Personsclass_id → ClassesWrite a query that returns the table with the name of persons and the kind of classes that they take. The columns of the resulting table should be:ResultsnamekindFor example, if George Orwell takes the classes "dancing" and "baking", then the result should contain rows (George Orwell, dancing) and (George Orwell, baking).
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderKnowsidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Knows table is that the person referenced by personA_id knows the person referenced by personB_id.TakesClassesidperson_id → Personsclass_id → ClassesThe semantics of the TakesClasses table is that person referenced by person_id takes the class referenced by class_id.Use NOT IN to write a query that returns the name of all persons that do not know anyone who takes classes.
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;
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
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.