To Previous Chapter To Table of Contents To Bottom of Page To Next Chapter

Chapter 10 - Structured Query Language

  1. Querying a Single Table
    1. JUNIOR(Snum, Name, Major)
    2. HONOR-STUDENT(Number, Name, Interest)
    3. STUDENT(SID, Name, Major,GradeLevel, Age)
    4. CLASS(Name, Time, Room)
    5. ENROLLMENT(StudentNumber, ClassName, PositionNumber)
    6. FACULTY (FID, Name, Department)
    • Projections Using SQL
      1. SELECT SID, Name, Major FROM STUDENT
      2. SELECT DISTINCT Major FROM STUDENT
    • Selections Using SQL
      1. SELECT SID, Name, Major, GradeLevel, Age FROM STUDENT WHERE Major='MATH'
      2. SELECT * FROM STUDENT WHERE Major='MATH'
      3. SELECT Name, Age FROM STUDENT WHERE Major='Math' AND Age > 21
      4. SELECT Name FROM STUDENT WHERE Major IN ['MATH', 'ACCOUNTING']
    • Sorting
      1. SELECT Name, Major, Age FROM STUDENT WHERE Major = 'ACCOUNTING' ORDER BY Name
      2. SELECT Name, Major, Age FROM STUDENT WHERE GradeLevel IN ['FR', 'SO', 'SR'] ORDERED BY Major ASC, Age DESC
    • SQL Built-In Functions
      1. SELECT COUNT(*) FROM STUDENT
      2. SELECT COUNT (DISTINCT Major) FROM STUDENT
    • Built-in Functions and Grouping
      1. SELECT Major, COUNT(*) FROM STUDENT GROUP BY Major
      2. SELECT Major, AVG (Age) FROM STUDENT WHERE GradeLevel = 'SR' GROUP BY Major HAVING COUNT(*)>1

  2. Querying a Multiple Table
    • Retrieval Using SubQuery
      1. SELECT Name FROM STUDENT WHERE SID IN
        (SELECT StudentNumber FROM ENROLLMENT WHERE ClassName='BD445')
      2. SELECT STUDENT.Name FROM STUDENT WHERE STUDENT.SID IN
        (SELECT ENROLLMENT.StudentNumber FROM ENROLLMENT WHERE ENROLLMENT.ClassName IN
        (SELECT CLASS.Name FROM CLASS WHERE CLASS.Time='MWF3'))
    • Joining with SQL
      1. SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName FROM STUDENT, ENROLLMENT WHERE STUDENT.SID = ENROLLMENT.StudentNumber
      2. SELECT STUDENT.SID, CLASS.Name, CLASS.Time, ENROLLMENT.PositionNumber FROM STUDENT, ENROLLMENT, CLASS WHERE STUDENT.SID=ENROLLMENT.StudentNumber AND ENROLLMENT.ClassName = CLASS.Name AND STUDENT.Name = 'BAKER'
    • Comparison of SQL SubQuery and Join (joins can be used as an alternative way of expressing many subqueries)
      SELECT STUDENT.NAME FROM STUDENT, ENROLLMENT, CLASS WHERE STUDENT.SID=ENROLLMENT.StudentNumber AND ENROLLMENT.ClassName=CLASS.Name AND CLASS.Time ='MWF3'

  3. Exists and Not Exists
    1. Find all students enrolled in more than one class:
      SELECT DISTINCT StudentNumber FROM ENROLLMENT A WHERE EXISTS
      (SELECT * FROM ENROLLMENT B WHERE A.StudentNumber=B.StudentNumber AND A.ClassName NOT = B.ClassName)
    2. Find names of all students taking all classes
      SELECT STUDENT.Name FROM STUDENT WHERE NOT EXISTS
      (SELECT * FROM ENROLLMENT WHERE NOT EXISTS
      (SELECT * FROM CLASS WHERE CLASS.Name = ENROLLMENT.ClassName AND ENROLLMENT.StudentNumber=STUDENT.SID))

  4. Changing Data
    • Inserting Data
      INSERT INTO ENROLLMENT VALUES (400, 'BD445', 44)
    • Deleting Data
      DELETE STUDENT WHERE STUDENT.SID=100
    • Modifying Data
      UPDATE ENROLLMENT SET PositionNumber=44 WHERE SID=400

To Previous Chapter To Table of Contents To top of page To Next Chapter