SQL

(10 pts.) Database creation and querying exercise.

The schema, DDL, and instance for the University database from the textbook are provided in Appendix A. The electronic form of the DDL and insert statements can be downloaded from the textbook website. Follow Lab material from the textbook home page to Sample tables and download DDL with drop tables and SQL code for creating small relations. Analyze these files. Next, create the university database in SQLite (if you are comfortable with another DBMS, you may use it as long as it follows standard SQL). You do not have to retype any SQL statements, just use the files. Submit sample screenshots of the database schema and the database instance. Attached is one such example. sqlite_interaction.jpgDownload sqlite_interaction.jpg

Note: All screenshots should be included in one document. Zip folder submissions will not be graded.

(10 pts.) Run the following queries on the university database you just created. Submit actual queries and their results (copy and paste text would be best). Hint - All these queries are single relation queries.

List all students.

List only course_ids of courses that are offered in Spring 2009.

List only student names and how many more credits they have to take to complete their degree. Assume that the degree completion requirement is 124 credits for all students.

Find the total number of instructors and their average salary.

List only course_ids of all courses that are either offered in Spring or Summer.

List all rooms that have a capacity of at least 50 and utmost 100.

List all instructors who have a name that begins with K.

List only student_ids of students who have received a grade of A, A-, or B+ in any course.

(8 pts.) Exercise 3.11 - you may want to ignore Spring in part b. Run these queries on the university database you have created. Submit actual queries and their results (copy and paste text would be best).

(4 pts.) Exercise 3.15 parts b and c only.

(4 pts.) Exercise 3.21 parts a and c only.

(2 pts.) Exercise 4.14

(2 pts.) Exercise 5.12