Homework 2 (Basic SQL)

CSCI 330 Database Systems
Homework 2 (Basic SQL)
Total Points: 16 (4% of course grade)
Goals
The goals of this homework are as follows:
 To become familiar with basic SQL commands in MySQL.
o To create a database
o To insert data into tables
o To write basic SQL queries
 The SQL commands from chapter 3 will be sufficient for this homework.
 We will use a software called “MySQL Workbench” to write the SQL query in our
class. We recommend installing MySQL workbench on your computer. There are
numerous online tutorials and YouTube videos available to learn how to install and
use MySQL workbench. Some examples:
o https://www.youtube.com/watch?v=OM4aZJW_Ojs&ab_channel=WebDevSi
mplified (For Windows)
o https://www.youtube.com/watch?v=-BDbOOY9jsc&ab_channel=AmitThinks
(For Mac)
o https://www.youtube.com/watch?v=iLRY-NfXXsA&ab_channel=AmitThinks
(for Ubuntu)
 If you prefer not to install MySQL workbench on your computer, you may use a freely
available online editor. Some examples:
o https://paiza.io/en/languages/mysql
o https://extendsclass.com/mysql-online.html
What to do
1. Create a University Database: Use the SQL script (create_university.sql), which is
available on canvas, to create the following university database (see figure 1).
Figure 1: University database
2. Populate the university database with Data: Use the SQL script
(insert_university.sql), which is available on canvas, to populate the seven tables (see
below) of the university database
department
instructor
course
student
section
teaches
Takes
3. Write SQL queries for the following (2*8 = 16 points)
a. Find courses that taught either in Fall 2009 or in Spring 2010.
b. Find all instructors earning the highest salary (there may be more than one with the
same salary).
c. Find names and average salaries of all departments whose average salary > 42000
d. For each department, find the maximum salary of instructors in that department.
You may assume that every department has at least one instructor.
e. Find the names of all students who have taken any Comp. Sci. course ever (there
should be no duplicate names)
f. Find the enrollment of each section that was offered in Spring 2009.
g. Find the maximum enrollment, across all sections, in Spring 2009.
h. Delete all courses that have never been offered (that is, do not occur in the section
relation).
Submission Instructions
 Please put all of your SQL queries and their corresponding results (in table format)
in one PDF file.
 The file name should be YourLastName-CSCI330-HW2.pdf.
 Upload the pdf file on canvas.