SELECT SID, Name, Major, GradeLevel, Age FROM STUDENT WHERE Major='MATH'
SELECT * FROM STUDENT WHERE Major='MATH'
SELECT Name, Age FROM STUDENT WHERE Major='Math' AND Age > 21
SELECT Name FROM STUDENT WHERE Major IN ['MATH', 'ACCOUNTING']
Sorting
SELECT Name, Major, Age FROM STUDENT WHERE Major = 'ACCOUNTING' ORDER BY Name
SELECT Name, Major, Age FROM STUDENT WHERE GradeLevel IN ['FR', 'SO', 'SR'] ORDERED BY Major ASC, Age DESC
SQL Built-In Functions
SELECT COUNT(*) FROM STUDENT
SELECT COUNT (DISTINCT Major) FROM STUDENT
Built-in Functions and Grouping
SELECT Major, COUNT(*) FROM STUDENT GROUP BY Major
SELECT Major, AVG (Age) FROM STUDENT WHERE GradeLevel = 'SR' GROUP BY Major HAVING COUNT(*)>1
Querying a Multiple Table
Retrieval Using SubQuery
SELECT Name FROM STUDENT WHERE SID IN
(SELECT StudentNumber FROM ENROLLMENT WHERE ClassName='BD445')
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
SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName FROM STUDENT, ENROLLMENT WHERE STUDENT.SID = ENROLLMENT.StudentNumber
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'
Exists and Not Exists
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)
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))
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