# BACS 485 - PowerPoint PPT Presentation

1 / 21

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.

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

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

CLASS(ClassNum, ClassName, Time, Credits, FacNum)

### SQL Practice 1

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

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

### SQL Practice 2

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

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

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

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

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

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

### SQL Practice 5

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

### SQL Answer 5

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

SELECT Count(*)

FROM CLASS;

-OR-

SELECT Count(ClassNum)

FROM CLASS;

### SQL Practice 6

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

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

### SQL Practice 7

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

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

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

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

### SQL Practice 9

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

### 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’;