1 / 11

Structured Query Language

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

oliver
Télécharger la présentation

Structured Query Language

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Structured Query Language • Querying a Single Table • Querying Multiple Tables • EXISTS and NOT EXISTS • Changing Data

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

  3. 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’]

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

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

  6. 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’)

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

  8. Querying Multiple Tables (Cont.) (SELECT CLASS.Name FROM CLASS WHERE CLASS.Time=‘MWF3’))

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

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

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

More Related