1 / 21

BACS 485 - PowerPoint PPT Presentation

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)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

PowerPoint Slideshow about ' BACS 485' - nau

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

BACS 485

Structured Query Language 2

• 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)

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

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

SELECT Lname, Major, GPA

FROM STUDENT;

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

• 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”;

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

• 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;

• 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.

• 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;

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

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

SELECT Count(*)

FROM CLASS;

-OR-

SELECT Count(ClassNum)

FROM CLASS;

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

• 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;

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

• 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;

• 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.

• 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;

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

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