The Class table contains information about students, classes and grades. In this
ID: 3914941 • Letter: T
Question
The Class table contains information about students, classes and grades. In this example, we are not concerned with normalizations. The goal is the use the data provided and write SQL statements to view the desired output. The score attribute is a number data type, the DOB attribute is a date data type and all other attributes are string data types.
You will be provided the results of a query from the class table. Your task is to write the query that would produce those results.
1. Display the number of students from each class who got an A or B in the course.
className grade count(grade)
Database A 3
Database B 2
Computer Based IS A 3
Computer Based IS B 3
2. Display the className, grade and how many students got an A, B or C in the class. Filter the data to only display the class and grades where there is a count less than 3.
className grade count(grade)
Database B 2
Database C 2
Computer Based IS C 1
3. Display the last name of all students who have more than 8 characters in their name. The query results should only display unique values (no duplicate results). The results should appear in descending alphabetical order.
lName Lenth
Stephenson 10
O’Connell 9
McRoberts 9
Explanation / Answer
Following is the answer:
1) In this query Count(Grade) returns double value then grade count so round(COUNT(Grade)/2) is used for correct value.
SELECT className, Grade, round(COUNT(Grade)/2) as Count from Class where Grade="A" or Grade= "B" GROUP BY className, Grade
2) This Query Displays the className, grade and how many students got an A, B or C in the class. Filter the data to only display the class and grades where there is a count less than 3.
SELECT className, Grade, round(COUNT(Grade)/2) as Count from Class GROUP BY className, Grade HAVING Count < 3
3) This Query Displays the last name of all students who have more than 8 characters in their name. The query results should only display unique values (no duplicate results). The results should appear in descending alphabetical order.
SELECT DISTINCT lName, Length(lName) as Length FROM `Class` HAVING Length > 8 ORDER by lName DESC