Bacs 485
This presentation is the property of its rightful owner.
Sponsored Links
1 / 21

BACS 485 PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on
  • Presentation posted in: General

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)

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

BACS 485

Structured Query Language 2


Sql practice problems

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)


E r diagram

E/R Diagram


Sql practice 1

SQL Practice 1

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


Sql answer 1

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

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

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

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

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

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

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

SQL Practice 5

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


Sql answer 5

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

SQL Practice 6

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


Sql answer 6

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

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

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

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

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

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

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


  • Login