Cs 2550 Sql Assignment

1. Create a query that returns the average cost for all courses. (Round to two places).

SELECT ROUND(AVG(cost),2)

FROM course;

2. Create a query that returns the total number of Students that registered during February 2007. Alias

the column as "February_Registrations".

SELECT COUNT(registration_date) AS February_Registrations

FROM student

WHERE TO_CHAR(registration_date, 'MON YYYY') = 'FEB 2007';

3. Create a query that returns the average, highest and lowest final exam scores for Section 147.

SELECT ROUND(AVG(numeric_grade),2) AS Average, MAX(numeric_grade) As Highest, MIN(numeric_grade) As Lowest

FROM grade

WHERE grade_type_code = 'FI' and section_id = 147

4. List the city, state and number of zipcodes for all cities with more than two zipcodes. Arrange by

state and city.

SELECT city, state, COUNT(zip) AS Zipcodes

FROM zipcode

GROUP BY city, state

HAVING COUNT(zip) > 2

ORDER BY state, city;

5.Provide a list of Sections and the number of students enrolled in each section for students who

enrolled on 2/21/2007. Sort from highest to lowest on the number of students enrolled.

SELECT section_id, COUNT(enroll_date) As Enrolled

FROM enrollment

GROUP BY section_id, enroll_date

HAVING TO_CHAR(enroll_date, 'MM/DD/YYYY') = '02/21/2007'

ORDER BY Enrolled DESC, section_id DESC;

6. Create a query listing the student ID, Section ID and Average Grade for all students in Section 86.

Sort your list on the student ID and display all of the average grades with 4 decimal places.

SELECT student_id, section_id, ROUND(AVG(numeric_grade),4) As Average_Grade

FROM grade

GROUP BY student_id, section_id

HAVING section_id = 86

ORDER BY student_id;

7. Create a query to determine the number of sections that student ID 250 is enrolled in.

Your output should contain the student ID and the number of sections enrolled.

SELECT student_id, COUNT(section_id) As Sections

FROM enrollment

GROUP BY student_id

HAVING student_id = 250

8.List...