1. Create a database schema named cs4707and then create tables from exercise 5.1. Extend database by adding table for following entities.
Grade(snum, cname, score)
Prerequisite(cname, prereqcname)
• No two classes can have same name.
• Score is an integer value which indicates students’ score out of 100.
• A student can take more than 1 class.
Use above information as needed while creating tables.
2. Use the csv files from P1.zip and insert given data into the database.
3. Write and execute all these queries on your database.
1. Complete all queries from Exercise 5.1.
2. Find names of Students who have enrolled in some classes, but have not yet received any grades for any of those classes.
3. For each class, print the name of the class and name of the student who topped the class. If 2 students have the same score, select the one who is younger.
4. Find the 2nd topper for class ‘Operating System Design’. If there are two students with the same score, select the one whose name is lexicographically smallest.
5. Query to check if 2 students have the same name.
6. Rank each student for the class ‘Operating System Design’. For example, one who has scored highest will have rank 1, second highest will have rank 2 and so on. If 2 students have scored the same, they will have the same rank.
7. Find class names with no prerequisites.
8. Find all prerequisite for class ‘Operating System Design’ and ‘Multivariate Analysis’.
9. Find all class names with at least 2 prerequisite.
10. Find the youngest student by each department and major if their average grade score is more than 80 and order them by their name.
11. Add 5 to the score of every student who has grades for database systems, but ensure no one's score goes beyond 100.
