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.

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

Terminal - sqlite 3952 uqlite> .tables advisor course instructor section taken time_slot classroom department prereq student teaches Bq1ites.schena elassroon CREATE TABLE olassroon (building varchar(15), roon_number varchar (7), capacity numerie (4,0), prinary key (building, roon_number) if belites relect - trom classroon; building room_number capacity PackardPainterTaylorWatsonNatson101514312810012050010703050 uglite> select - from department; dept_name building budget Blology Watson 90000 Comp. Sei. Taylor 100000 Elec. Eng. Taylor 85000 Finance: Painter 120000 History Painter 50000 Musie packard 80000 Physics Watson 20000 nglite>