Answer questions using the database classes.mdb. You can try out any of your answers in Access, but you must write all your answers in the bluebook.

 

  1. Write CREATE TABLE commands for the four tables in the database. Be sure to include primary and foreign key constraints.

 

  1. Write a command to insert a new course – CIS520, Database Development, 3 credits.

 

  1. Write a command to change the max enrollment  to 18 for all offerings of CIS 111.

 

  1. Write a command to delete the new course that you added in question 2.

 

  1. Write a query to print the first and last names of all computer science majors.

 

  1. Write a query to print the first and last names of all computer science majors who were born in 1987.

 

  1. Write a query to print the names of all faculty members who teach an offering on Friday. Note - the offering might meet on F or on MWF.

 

  1. Write a query that will print the course number of all classes that have the word “Computer” in their description.

 

  1. Write a query that will print the crn, course number, section, and credits for all offerings. Don’t use INNER JOIN.

 

  1. Write a query that will print the crn, course number, section, and credits for all offerings. You must use INNER JOIN.

 

  1. Write a query that will print the course number and section for all 4 credit offerings that meet only on Wednesday and all 3 credit offerings that meet only on Friday.

 

  1. Write a query that will print the average maximum enrollment in the Offerings table.

 

  1. Write a query that will print the course number and the number of offerings of each course.

 

  1. Write a query that will print the course number and the number of offerings of each course. Only include offerings that have at least 2 sections.

 

  1. Write a query that will print the crn, course number, section, and actual enrollment for each offering. The actual enrollment is the number of enrollments for that offering.

 

  1. Write a query that will print the first and last name of each student and the number of credits that they are enrolled in.

 

  1. Write a query that will print the first and last name of each student and the amount of money they owe for tuition. Assume that students pay $1000 per credit.