CIST 1220: SQL Assign #5

(40 Points)

[Order By/Functions Chap 4 & Chap6]

Using the database provided, Write and Execute SELECT statements to get the following information:

Order By:

List of Students, first name and last name only, in alphabetical order by last name.

List all students, only id, first name, last name. The student with the highest gpa should be listed first.

Same as #2 above except the student with the lowest gpa should be listed first.

List all sections, all columns, sorted by instructor id.

List all sections, all columns, sorted by roomNo.

List all courses, all columns, sorted by creditHours

List all students, all columns, sorted first by state, then within each state, sorted by gpa.

Which student (give id, first name and last name) has the lowest gpa? Use order by to see a list of students in order by gpa, and read the name of the student with the lowest gpa.

Aggregate Functions :

What is the average gpa for all students?

How many students are there from Illinois?

If a student were to take three courses(CIST 1001, CIST 1122 and CIST 1130) how many credit hours would this student be taking?

How many sections are being taught by the instructor with id 3?

How many sections are being taught in room F1150?

How many students are in section with CRN=30115? (Hint: use StudentSchedule table.)

How many students are there from Marietta, Ga?

Which student from Georgia has the highest GPA?

Table Tools Access Sign in File Home Create External Dato Database Tools Fields abe Tell me what you want to do Custom Custom Group Unassigned Objects Courses Instructors sections students CourselD CourseName Description CreditHours , Click to Add r Concepts Intro to Computers Hardware Maintainence Explore how an Operating System works Learning about SQL Intro to Programming Networking Fundamentals Develop Web-pages using HTML Intro to C# Programming Advanced Cf Programming Intro to Java Intermediate Java Topics Object Oriented Analysis and Design Capstone Project Class Using Java to build Web Applications IST 1001 CIST 1122 CIST 1130 CIST 1220 CIST 1305 CIST Hardware Maintainence Course Operating Systems SQL Programming Program Design and Development Networking Fundamentals Web Development I Cf Programming I C# ProgrammingI Java Programming I Java Programming lI Analysis and Design - 1401 10 CIST 15 CIST 2341 CIST 2342 CI ST 2371 CIST 2372 CIST 2921 CIST 2931Systems Project ST 3273 Java Programming II Record: l-M 1 of 14 1 K: No Filter | Search Datasheet View O Type here to search 1:36 PM 2/1/2018