Database Management Systems
Aggregate Functions
Assume three tables are given as follows:
Student: <StID, Name, Department>
Course: < CID, CName, Credit>
Grading: <StID, CID, Year, Grade >
1. Write SQL commands to create the tables. Consider necessary requirements such as primary
key, foreign key, unique, not null, etc.
2. Insert some records (Read the following parts, and add the records so that the queries have
enough records to process)
3. Find the number of students taking course CENG356 in 2020
4. Find the average grade for CENG356 in 2020
5. Find the average grade for CENG356 during years 2000 and 2010 inclusive
6. How many courses did a student named ‘John’ take in 2018?
7. What is the average grade of students in each course in 2018?
Bonus question:
Interpret the following query:
If you write a query such as
SELECT count(*), Name
FROM Student
You will get an error message. Why do you think this query is wrong?
