CIST 1220: SQL Assign #6

(40 Points)

[Functions/Group By/Having Chap 6]

Group By : Using the database provided, Write and Execute SQL SELECT statements, using GROUP BY, to get the following information. Also use functions(like max(salary) with your GROUP BYs.

Examples:

SELECT au_fname, POSITION('e' IN au_fname) AS "Pos e", au_lname, POSITION('ma' IN au_lname) AS "Pos ma" FROM authors;

SELECT title_name, POSITION('u' IN title_name) AS "Pos" FROM titles WHERE POSITION('u' IN title_name) BETWEEN 1 AND 10 ORDER BY POSITION('u' IN title_name) DESC;

Group By:

How many classes are scheduled in each room?

How many sections of each course are there being taught?

Which courses have only 1 section being taught?

What is the avg gpa for all students from each different state?

Which states have an avg gpa higher than 3.0?

How many courses is each instructor teaching?

Do we have more 3 credit hour courses or 5 credit hour courses.(use Group by to answer this question)

How many instructors do we have from each state?

Same as #8 above, but only list the states that have 3 or more instructors.

How many students are there from each state?

Same as #10 above, but only list the states that have 2 or more students.