1 / 21

BACS 485

BACS 485. Structured Query Language 2. SQL Practice Problems. Assume that a database named COLLEGE exists. It contains the tables defined below. STUDENT( SSN , Lname, Fname, Class, BDate, Major, GPA) TEACHER( FacNum , Name, Dept, Title, Salary)

nau
Download Presentation

BACS 485

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BACS 485 Structured Query Language 2

  2. SQL Practice Problems • Assume that a database named COLLEGE exists. It contains the tables defined below. STUDENT(SSN, Lname, Fname, Class, BDate, Major, GPA) TEACHER(FacNum, Name, Dept, Title, Salary) CLASS(ClassNum, ClassName, Time, Credits, FacNum) ENROLL(ClassNum,SSN, Grade)

  3. E/R Diagram

  4. SQL Practice 1 • Write a valid SQL query to retrieve and display the last name, major, and GPA of all students.

  5. SQL Answer 1 • Write a valid SQL query to retrieve and display the last name, major, and GPA of all students. SELECT Lname, Major, GPA FROM STUDENT;

  6. SQL Practice 2 • Write a valid SQL query to retrieve and display the SSN, last name, and classification of all math majors.

  7. SQL Answer 2 • Write a valid SQL query to retrieve and display the SSN, last name, and classification of all math majors. SELECT SSN, Lname, Class FROM STUDENT WHERE Major = “Math”;

  8. SQL Practice 3 • Write an SQL query to display the SSN and last name of all seniors with a grade point average above 3.5.

  9. SQL Answer 3 • Write an SQL query to display the SSN and last name of all seniors with a grade point average above 3.5. SELECT SSN, Lname FROM STUDENT WHERE Class = “Senior” AND GPA > 3.5;

  10. SQL Practice 4 • Write an SQL query to display the name, department, title, and salary of all teachers who make between $30,000 and $40,000. Sort the results in ascending salary order.

  11. SQL Answer 4 • Write an SQL query to display the name, department, title, and salary of all teachers who make between $30,000 and $40,000. Sort the results in ascending salary order. SELECT Name, Dept, Title, Salary FROM TEACHER WHERE Salary between 30000 and 40000 ORDER BY Salary;

  12. SQL Practice 5 • Write an SQL query to count the number of classes taught.

  13. SQL Answer 5 • Write an SQL query to count the number of classes taught. SELECT Count(*) FROM CLASS; -OR- SELECT Count(ClassNum) FROM CLASS;

  14. SQL Practice 6 • Write an SQL query to display the average GPA for each major. Sort the results by descending major.

  15. SQL Answer 6 • Write an SQL query to display the average GPA for each major. Sort the results by descending major. SELECT Major, AVG(GPA) FROM STUDENT GROUP BY Major ORDER BY Major Desc;

  16. SQL Practice 7 • Write an SQL query to display the faculty number, faculty name, and class numbers for all classes being taught.

  17. SQL Answer 7 • Write an SQL query to display the faculty number, faculty name, and class numbers for all classes being taught. SELECT FacNum, Name, ClassNum FROM TEACHER, CLASS WHERE TEACHER.FacNum = CLASS.FacNum;

  18. SQL Practice 8 • Write an SQL query to display the class number, class name, teacher name, and credits for all classes with 1 credit hour. Sort by ascending class number.

  19. SQL Answer 8 • Write an SQL query to display the class number, class name, teacher name, and credits for all classes with 1 credit hour. Sort by ascending class number. SELECT ClassNum, ClassName, Name, Credits FROM TEACHER, CLASS WHERE TEACHER.FacNum = CLASS.FacNum AND Credits = 1 ORDER BY ClassNum;

  20. SQL Practice 9 • Write an SQL query to display the student number and last name, class name, and grade for all history majors.

  21. SQL Answer 9 • Write an SQL query to display the student number and last name, class name, and grade for all history majors. SELECT SSN, Lname, ClassName, Grade FROM STUDENT, ENROLL, CLASS WHERE STUDENT.SSN = ENROLL.SSN AND CLASS.ClassNum = ENROLL.ClassNum AND Major = ‘History’;

More Related