SQL by Example 1 SQL What is it

SQL by Example 1

SQL: What is it? • SQL stands for Structured Query Language. • It was originally developed in the early 1970 s 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. 2

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

SQL is Intuitive • To show 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. 4

Players Table • SELECT Last. Name FROM Players WHERE Player. ID = 8 • SELECT Position FROM Players WHERE Last. Name = ‘Schrute’ • SELECT Age FROM Players WHERE First. Name = ‘Dwight’ 5

Answers • SELECT Last. Name FROM Players WHERE Player. ID = 8 – Jennings • SELECT Position FROM Players WHERE Last. Name = ‘Schrute’ – Left Field • SELECT Age FROM Players WHERE First. Name = ‘Dwight’ – 27 6

Trick Question • SELECT Player. ID FROM Players WHERE Last. Name = ‘Johnson’ 7

• SELECT Player. ID FROM Players WHERE Last. Name = ‘Johnson’ Query 1 Player. ID 15 17 8

• SELECT Player. ID FROM Players WHERE Position = 'AH' 9

• SELECT Player. ID • FROM Players WHERE Position = 'AH' Query 1 Player. ID 11 17 22 31 10

• SELECT First. Name, Last. Name, Age FROM Players WHERE Position = 'Pitcher' 11

• SELECT First. Name, Last. Name, Age FROM Players WHERE Position = 'Pitcher' Query 1 First. Name Walter Derrick Tim Last. Name Age Williams Markel Ford 40 28 29 12

• SELECT * FROM Players WHERE Player. ID = 1 13

• SELECT * FROM Players WHERE Player. ID = 1 Query 1 Player. ID Last. Name First. Name Age 1 Cage Nick Position 25 1 st Base Phone. Number Team. ID (734)431 -9880 1 14

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

• SELECT * FROM Players WHERE Position = 'Catcher' Query 1 Player. ID Last. Name First. Name Age Position Phone. Number Team. ID 10 Gomez Felipe 36 Catcher (734)697 -4356 1 16 Sherman Mike 28 Catcher (734)607 -0436 2 34 Malone Kevin 26 Catcher (734)695 -9019 3 16

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Age < 22 17

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Age < 22 Query 1 Player. ID First. Name 8 Greg 30 Hector Last. Name Jennings Gonzales Age 21 21 18

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND Age>35 19

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND Age>35 Query 1 Player. ID First. Name Last. Name Age 7 Walter Williams 10 Felipe Gomez 40 36 11 Chris 36 Swanson 20

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND Age>37 OR Position='1 st Base' 21

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND Age>37 OR Position='1 st Base' Query 1 Player. ID 1 7 21 25 First. Name Nick Walter Joey Justin Last. Name Age Cage Williams Szarek Fleeson 25 40 37 33 22

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

SELECT symbol FROM elements WHERE grp=13 AND period=4 Query 1 symbol Ga grp period 13 4 24

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

• SELECT symbol, grp, period FROM elements WHERE grp=13 OR period=4 Query 1 symbol grp period Al 13 3 As 15 4 B 13 2 Br 17 4 Ca 2 4 Co 9 4 Cr 6 4 Cu 11 4 Fe 8 4 Ga 13 4 Ge 14 4 In 13 5 K 1 4 Kr 18 4 Mn 7 4 Ni 10 4 Sc 3 4 Se 16 4 Ti 4 4 Tl 13 6 Uut 13 7 V 5 4 Zn 12 4 26

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND (Age>37 OR Position='1 st Base') ORDER BY Age 27

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND (Age>37 OR Position='1 st Base') ORDER BY Age Query 1 Player. ID First. Name Last. Name 1 Nick Cage 7 Walter Williams Age 25 40 28

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

Trick Question! • SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND (Age>37 AND Position='1 st Base') ORDER BY Age 30

• SELECT Player. ID, First. Name, Last. Name, Age FROM Players WHERE Team. ID=1 AND (Age>37 AND Position='1 st Base') ORDER BY Age 31

• SELECT * FROM Beings WHERE Birth. Year>17 32

• SELECT * FROM Beings WHERE Birth. Year>17 Query 1 Being. ID Being. Name 6 Obiwan 12 Ohuru 13 Scott Home. Planet. ID Birth. Year 3 1 5 19 19 19 33

• SELECT * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year 34

• SELECT * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year Query 1 Being. ID Being. Name 15 Armstrong 20 Ilya 16 Fred 1 Luke 2 Leia 12 Ohuru Home. Planet. ID Birth. Year 1 1 1 2 4 5 12 17 19 35

• SELECT * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year ASC 36

• SELECT * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year ASC Query 1 Being. ID Being. Name 15 Armstrong 20 Ilya 16 Fred 1 Luke 2 Leia 12 Ohuru Home. Planet. ID Birth. Year 1 1 1 2 4 5 12 17 19 37

SELECT * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year DESC 38

SELECT * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year DESC Query 1 Being. ID Being. Name Home. Planet. ID Birth. Year 12 Ohuru 1 19 2 Leia 1 17 1 Luke 1 12 16 Fred 1 5 20 Ilya 1 4 15 Armstrong 1 2 39

SELECT * FROM Beings WHERE Birth. Year BETWEEN 11 AND 15 40

SELECT * FROM Beings WHERE Birth. Year BETWEEN 11 AND 15 Query 1 Being. ID 1 7 8 23 Being. Name Luke Chewbaca Anakin Bob Home. Planet. ID Birth. Year 1 12 5 12 4 11 2 12 41

SELECT Symbol, Element, Atomic. Number, Atomic. Mass / Atomic. Number AS Weight. Ratio FROM Elements WHERE Grp = 4 42

SELECT Symbol, Element, Atomic. Number, Atomic. Mass / Atomic. Number AS Weight. Ratio FROM Elements WHERE Grp = 4 Query 1 Symbol Element Hf Hafnium Rf Rutherfordium Ti Zr Atomic. Number Atomic. Mass Weight. Ratio 72 178. 49 2. 47902774810791 104 261 2. 50961542129517 Titanium 22 47. 867 2. 17577266693115 Zirconium 40 91. 224 2. 28060007095337 43

SELECT TOP 1 * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year 44

SELECT TOP 1 * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year Query 1 Being. ID Being. Name Home. Planet. ID Birth. Year 15 Armstrong 1 2 45

• SELECT TOP 1 * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year DESC 46

• SELECT TOP 1 * FROM Beings WHERE Home. Planet. ID = 1 ORDER BY Birth. Year DESC Query 1 Being. ID Being. Name Home. Planet. ID Birth. Year 12 Ohuru 1 19 47

SELECT TOP 3 * FROM Beings ORDER BY Being. Name 48

SELECT TOP 3 * FROM Beings ORDER BY Being. Name Query 1 Being. ID 8 15 23 Being. Name Anakin Armstrong Bob Home. Planet. ID Birth. Year 4 1 2 12 49

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

SELECT * FROM Elements WHERE Atomic. Number BETWEEN 80 AND 90 ORDER BY Atomic. Number DESC Query 1 Symbol Element Atomic. Number Atomic. Mass Grp Period Series Th Thorium 90 232. 0381 0 7 Actinide Ac Actinium 89 227 0 7 Actinide Ra Radium 88 226 2 7 Alkaline earth metal Fr Francium 87 223 1 7 Alkali(metal) Rn Radon 86 220 18 6 Noble gas At Astatine 85 210 17 6 Halogen Po Polonium 84 210 16 6 Metalloid Bi Bismuth 83 208. 9804 15 6 Poor metal Pb Lead 82 207. 2 14 6 Poor metal Tl Thallium 81 204. 3833 13 6 Poor metal Hg Mercury 80 200. 59 12 6 Transition metal 51

SELECT COUNT(*) AS Number. Of. Elements FROM Elements 52

SELECT COUNT(*) AS Number. Of. Elements FROM Elements Query 1 Number. Of. Elements 117 53

SELECT Series, COUNT(*) AS Number. Of. Elements FROM Elements GROUP BY Series ORDER BY Series 54

SELECT Series, COUNT(*) AS Number. Of. Elements FROM Elements GROUP BY Series ORDER BY Series Query 1 Series Actinide Number. Of. Elements 15 Alkali(metal) 6 Alkaline earth metal 6 Halogen 5 Lanthanide 15 Metalloid 7 Noble gas 7 Nonmetal 7 Poor metal 11 Transition metal 38 55

SELECT Period, MAX(Atomic. Number) AS Max. At. Num FROM Elements GROUP BY Period ORDER BY Period 56

SELECT Period, MAX(Atomic. Number) AS Max. At. Num FROM Elements GROUP BY Period ORDER BY Period Query 1 Period Max. At. Num 1 2 3 4 5 6 7 2 10 18 36 54 86 118 57