1 / 31

SQL

SQL. “Structured Query Language; standard language for relational data manipulation ” DB2, SQL/DS, Oracle, INGRES, SYBASE, SQL Server, dBase/Win, Paradox, Access, and others all rely upon SQL. Introduction to SQL. IBM in the mid-1970s as SEQUEL

jdiaz
Download Presentation

SQL

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. SQL “Structured Query Language; standard language for relational data manipulation” DB2, SQL/DS, Oracle, INGRES, SYBASE, SQL Server, dBase/Win, Paradox, Access, and others all rely upon SQL

  2. Introduction to SQL • IBM in the mid-1970s as SEQUEL • SQ92 = 1992 ANSI standard [a newer standard exists for SQL to extend it to object-oriented languages] • data access language that is embedded in application programs • result of an SQL statement is a relation

  3. many vendors go beyond the ANSI standards for SQL because they want to better position their product in relation to their competitors consequently there are minor variations among vendors

  4. stand-alone • SQL can be used by itself to retrieve and report information • embedded • SQL is frequently embedded in application programs • SQL is not a programming language

  5. Sample Data ENROLLMENT Relation STUDENT Relation CLASS Relation

  6. Simple Select SELECT SID, Name, Major FROM STUDENT STUDENT Relation

  7. SELECT Major FROM STUDENT SELECTDISINCT Major FROM STUDENT as opposed to STUDENT Relation

  8. SELECTDISINCT Major FROM STUDENT

  9. Selection SELECT SID, Name, Major, GradeLevel, Age FROM STUDENT WHERE Major = ‘MATH’ STUDENT Relation

  10. SELECT SID, Name, Major, GradeLevel, AgeFROM STUDENTWHERE Major = ‘MATH’

  11. Selection SELECT SID, Name, Major, GradeLevel, Age FROM STUDENT WHERE Major = ‘MATH’ AND Age>21 STUDENT Relation

  12. SELECT SID, Name, Major, GradeLevel, AgeFROM STUDENTWHERE Major = ‘MATH’ AND Age>21

  13. Selection SELECT SID, Name, Major, GradeLevel, Age FROM STUDENT WHERE GradeLevel IN [‘FR’, ‘SO’] What about NOT IN ? STUDENT Relation

  14. SELECT SID, Name, Major, GradeLevel, AgeFROM STUDENTWHERE GradeLevel IN [‘FR’, ‘SO’]

  15. Selection % is a wildcard match, like * is a wildcard match _ (the underscore symbol) is for a character-by-character match SELECT Name FROM STUDENT WHERE Name LIKE ‘R%’ STUDENT Relation

  16. Sorting SELECT Name, Major, Age FROM STUDENT WHERE Major = ‘ACCOUNTING’ ORDER BY Name STUDENT Relation

  17. SELECT Name, Major, AgeFROM STUDENTWHERE Major = ‘ACCOUNTING’ORDER BY Name

  18. Sorting SELECT Name, Major, Age FROM STUDENT WHERE Major = ‘ACCOUNTING’ ORDER BY Name DESC ASC is for ascending STUDENT Relation

  19. SQL Built-In Functions • there are five • COUNT • SUM [only applies to numeric fields] • AVG [only applies to numeric fields] • MIN • MAX

  20. SQL Built-In Functions SELECT COUNT(Major) FROM STUDENT SELECT COUNT(DISTINCT Major) FROM STUDENT yields 8 as the answer yields 3 as the answer

  21. SELECT COUNT(DISTINCT Major)FROM STUDENT

  22. Grouping SELECT Major, COUNT(*) FROM STUDENT GROUP BY Major HAVING COUNT(*) > 1 FROM and WHERE go together and GROUP BY and HAVING go together

  23. Querying Multiple Tables • Retrieval Using Subquery • Joining with SQL

  24. what are the names of students in BD445? STUDENT Relation ENROLLMENT Relation CLASS Relation

  25. Subquery (the second SELECT) so this SELECT yields Jones and Baker SELECT Name FROM STUDENT WHERE SID IN (SELECT StudentNumber FROM ENROLLMENT WHERE ClassName = ‘BD445’) this SELECT yields 100 and 200

  26. Joining with SQL column names are unique within a table but it helps to ‘fully qualify’ a column name when more than one table is targeted by the FROM parameter SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName FROM STUDENT, ENROLLMENT WHERE STUDENT.SID = ENROLLMENT.StudentNumber in a JOIN always look to match the common column values students not in a class don’t get reported, why?

  27. SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassNameFROM STUDENT, ENROLLMENTWHERE STUDENT.SID = ENROLLMENT.StudentNumber

  28. What Is The Answer To This Query? SELECT SID, Name FROM STUDENT WHERE SID NOT IN (SELECTDISTINCT StudentNumber FROM ENROLLMENT) why is DISTINCT used? what is this query really asking?

  29. Inserting Data you must know both the field order and field type; text fields require surrounding apostrophes INSERTINTO ENROLLMENT VALUES (400, ‘BD445’, 44) to insert a STUDENT record where you don’t know the Major or GradeLevel INSERTINTO STUDENT notice the empty positions will place null values in the table VALUES (500, ‘Hamilton’, , , 45) key values must always be entered

  30. Modifying Data UPDATE ENROLLMENT SET PositionNumber = 44 WHERE SID = 400 be careful of wildcard matches

  31. Deleting Data DELETE STUDENT WHERE STUDENT.SID = 100 probably the most dangerous SQL statement

More Related