Structured Query Language  Querying a Single Table  Querying Multiple Tables  EXISTS and NOT EXISTS  Changing Data

Querying a Single Table  Projections Using SQL • SELECT Major FROM STUDENT and SELECT DISTINCT Major FROM STUDENT

Querying a Single Table (Cont.)  Selections Using SQL • SELECT Name, Age FROM STUDENT WHERE Major=‘MATH’ AND Age>21 and SELECT Name FROM STUDENT WHERE Major IN [‘MATH’,’HISTORY’]

Querying a Single Table (Cont.)  Sorting • SELECT Name, Major, Age FROM STUDENT WHERE GradeLevel IN [‘FR’,’SO’,’SN’] ORDER BY Major ASC, Age DESC

Querying a Single Table (Cont.)  SQL Built-In Functions • COUNT, SUM, AVG, MAX, MIN • e.g. SELECT COUNT(DISTINCT Major) FROM STUDENT and SELECT Major, COUNT(*) FROM STUDENT WHERE Major IN [‘MATH’,’HISTORY’] GROUP BY Major HAVING COUNT(*)>2

Querying Multiple Tables  Using Subqueries • What are the names of the students enrolled in the class BD445?

SELECT Name FROM STUDENT WHERE SID IN (SELECT StudentNumber FROM ENROLMENT WHERE ClassName=‘BD445’)

Querying Multiple Tables (Cont.) • What are the names of the students enrolled in classes on Monday, Wednesday, and Friday at 3 o’clock (MWF3)?

SELECT STUDENT.Name

FROM STUDENT WHERE STUDENT.SID IN (SELECT ENROLLMENT.StudentNumber

FROM ENROLLMENT WHERE ENROLLMENT.ClassName IN

Querying Multiple Tables (Cont.) (SELECT CLASS.Name

FROM CLASS WHERE CLASS.Time=‘MWF3’))

Querying Multiple Tables (Cont.)  Joining with SQL • List the student number and class name of all students named RYE who were first to enroll in a class.

SELECT STUDENT.SID, ENROLMENT.ClassName

FROM STUDENT, ENROLLMENT WHERE STUDENT.SID=ENROLLMENT.StudentNumber

AND ENROLLMENT.PositionNumber=1 AND STUDENT.Name= ‘RYE’

Querying Multiple Tables (Cont.)  EXISTS and NOT EXISTS • List the student numbers of students enrolled in more than one class.

SELECT DISTINCT StudentNumber FROM ENROLLMENTA WHERE EXISTS (SELECT * FROM ENROLLMENTB WHERE A.StudentNumber=B.StudentNumber

AND A.ClassName NOT = B.ClassName)

Querying Multiple Tables (Cont.)  CHANGING DATA • • • Inserting Data INSERT INTO ENROLLMENT VALUES(400,’BD445’,44) Deleting Data DELETE STUDENT WHERE STUDENT.Major=‘Accounting’ Modifying Data UPDATE ENROLLMENT SET PositionNumber=44 WHERE SID=400