1 / 57

SQL by Example

SQL by Example. SQL: What is it?. SQL stands for Structured Query Language . It was originally developed in the early 1970s by IBM as a way to manipulate and retrieve data stored in IBM’s relational DBMS, System R. It can be pronounced “Sequel” or “S-Q-L”.

braden
Download Presentation

SQL by Example

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 by Example

  2. SQL: What is it? • SQL stands for Structured Query Language. • It was originally developed in the early 1970s by IBM as a way to manipulate and retrieve data stored in IBM’s relational DBMS, System R. • It can be pronounced “Sequel” or “S-Q-L”. • With some variations, SQL is the standard query language for relational databases.

  3. When do we use SQL? • SQL is most commonly used to retrieve data from a database. • It is also commonly used to modify data. • SQL also contains commands for creating tables and other database objects, and for maintaining data security through granting and denying privileges to users.

  4. SQL is Intuitive • To show how easy it is to learn SQL, we’ll start out simply using examples. • We’ll review what you see in the examples in upcoming lectures.

  5. Players Table • SELECT LastName FROM Players WHERE PlayerID = 8 • SELECT Position FROM Players WHERE LastName = ‘Schrute’ • SELECT Age FROM Players WHERE FirstName = ‘Dwight’

  6. Answers • SELECT LastName FROM Players WHERE PlayerID = 8 • Jennings • SELECT Position FROM Players WHERE LastName = ‘Schrute’ • Left Field • SELECT Age FROM Players WHERE FirstName = ‘Dwight’ • 27

  7. Trick Question • SELECT PlayerID FROM Players WHERE LastName = ‘Johnson’

  8. SELECT PlayerID FROM Players WHERE LastName = ‘Johnson’

  9. SELECT PlayerID FROM Players WHERE Position = 'AH'

  10. SELECT PlayerID • FROM Players WHERE Position = 'AH'

  11. SELECT FirstName, LastName, Age FROM Players WHERE Position = 'Pitcher'

  12. SELECT FirstName, LastName, Age FROM Players WHERE Position = 'Pitcher'

  13. SELECT * FROM Players WHERE PlayerID = 1

  14. SELECT * FROM Players WHERE PlayerID = 1

  15. SELECT * FROM Players WHERE Position = 'Catcher'

  16. SELECT * FROM Players WHERE Position = 'Catcher'

  17. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE Age < 22

  18. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE Age < 22

  19. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND Age>35

  20. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND Age>35

  21. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND Age>37 OR Position='1st Base'

  22. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND Age>37 OR Position='1st Base'

  23. Elements Table SELECT symbol FROM elements WHERE grp=13 AND period=4

  24. SELECT symbol FROM elements WHERE grp=13 AND period=4

  25. SELECT symbol FROM elements WHERE grp=13 OR period=4

  26. SELECT symbol,grp,period FROM elements WHERE grp=13 OR period=4

  27. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND (Age>37 OR Position='1st Base') ORDER BY Age

  28. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND (Age>37 OR Position='1st Base') ORDER BY Age

  29. You are the DBMS • In these exercises, you are acting like the DBMS. • All major DBMS’s have SQL interpreters. • You can submit queries like these to a database and the DBMS will find the matching results for you.

  30. Trick Question! • SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND (Age>37 AND Position='1st Base') ORDER BY Age

  31. SELECT PlayerID, FirstName, LastName, Age FROM Players WHERE TeamID=1 AND (Age>37 AND Position='1st Base') ORDER BY Age

  32. SELECT * FROM Beings WHERE BirthYear>17

  33. SELECT * FROM Beings WHERE BirthYear>17

  34. SELECT * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear

  35. SELECT * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear

  36. SELECT * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear ASC

  37. SELECT * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear ASC

  38. SELECT * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear DESC

  39. SELECT * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear DESC

  40. SELECT * FROM Beings WHERE BirthYear BETWEEN 11 AND 15

  41. SELECT * FROM Beings WHERE BirthYear BETWEEN 11 AND 15

  42. SELECT Symbol, Element, AtomicNumber, AtomicMass, AtomicMass / AtomicNumber AS WeightRatio FROM Elements WHERE Grp = 4

  43. SELECT Symbol, Element, AtomicNumber, AtomicMass, AtomicMass / AtomicNumber AS WeightRatio FROM Elements WHERE Grp = 4

  44. SELECT TOP 1 * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear

  45. SELECT TOP 1 * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear

  46. SELECT TOP 1 * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear DESC

  47. SELECT TOP 1 * FROM Beings WHERE HomePlanetID = 1 ORDER BY BirthYear DESC

  48. SELECT TOP 3 * FROM Beings ORDER BY BeingName

  49. SELECT TOP 3 * FROM Beings ORDER BY BeingName

  50. SELECT * FROM Elements WHERE AtomicNumber BETWEEN 80 AND 90 ORDER BY AtomicNumber DESC

More Related