CSCI 153

1. CSCI 153 Structured Query Language (SQL)

2. Relational Databases • Data is stored in related tables • Database may be one or more tables • Examples: • Access • SQL Server • MySQL • RDBMS • Relational Database Management System

3. SQL and RDBMS • SQL allows for easy manipulation of data • SELECT (selecting a record(s)) • INSERT (adding a record) • UPDATE (changing a record(s)) • DELETE (deleting a record(s)) • Tables can be created or deleted • CREATE • DROP

4. SELECT Statement • SELECT Player_Stats.T_Code, Player_Stats.Player_FName, Player_Stats.Player_LName, Player_Stats.Hits, Player_Stats.At_Bats, Player_Stats.Home_Runs, Player_Stats.Triples, Player_Stats.DoublesFROM Player_Stats; • Result – See Demo Query 1

5. SELECT Statement • SELECT T_Code, Player_FName, Player_LName, Hits, At_Bats, Home_Runs, Triples, Doubles FROM Player_Stats; • Result – See Demo Query 1

6. SELECT Statement • SELECT * FROM Player_Stats; • Result – See Demo Query 1

7. SELECT Statement • SELECT * FROM Player_StatsWHERE Home_Runs > 25; • Result – See Demo Query 2

8. SELECT Statement • SELECT * FROM Player_StatsWHERE Home_Runs > 25 AND Doubles > 30; • Result – See Demo Query 3

9. SELECT Statement usingLike Wildcard • Select * FROM Team_CodesWHERE T_Code Like 'NLE*' • Result – See Demo Query 7

10. SELECT Statement usingOrder By statement • SELECT * FROM Team_CodesWHERE T_Code Like 'NLE*' ORDER BY Team_Name; • Result – See Demo Query 8

11. SELECT From 2 tables using a union • SELECT * FROM Player_Stats, Team_Stats • Result – See Demo Query 9 • This will result in n*m rows where Table_1 has n rows, and Table_2 has m rows(144 rows in this case)

12. SELECT From 2 tables using a join • SELECT Player_FName, Player_LName, Team_City, Team_Name • FROM Team_Codes, Player_Stats • WHERE Team_Codes.T_Code = Player_Stats.T_Code • Result – See Demo Query 10

13. SELECT using formulas • SELECT Player_LName, (Hits/At_Bats) • FROM Player_Stats; • Result – See Demo Query 11

14. SELECT – Renaming fields • SELECT Player_LName, (Hits/At_Bats) AS AvgFROM Player_Stats • Result – See Demo Query 12 • Data Abstraction

15. INSERT Statement • INSERT INTO Team_Codes (T_Code, Team_City, Team_Name) VALUES ('ALE1', 'Baltimore', 'Orioles') • Inserts a row into table (does not display anything) • Inserts represented by a + icon in Access

16. INSERT Statement • INSERT INTO Team_Codes Values ('ALE2', 'Boston', 'Red Sox') • Inserts a row into table – sets the values according to the default field order (does not display anything)

17. UPDATE Statement • UPDATE Player_StatsSET Home_Runs = 42 WHERE Player_LName = 'Piazza' • Changes the row in the table associated with Piazza – sets his home runs to 42 (nothing is displayed) • Updates represented by a pencil icon in Access

18. UPDATE Statement • UPDATE Player_StatsSET Home_Runs = 0 • Changes all of the rows in the table – sets home runs to 0 (nothing is displayed) • Unconditional – BE VERY CAREFUL

19. DELETE Statement • DELETE FROM Team_CodesWHERE T_Code='ALE1'; • Deletes row from table (nothing is displayed) • Deletes represented by an X icon in Access

20. DELETE Statement • DELETE FROM Team_Codes • Deletes all rows in the table (nothing is displayed) • Unconditional – BE VERY CAREFUL

21. HAVING and GROUP BY SELECT T_Code, SUM(Home_Runs) FROM Player_Stats WHERE T_Code LIKE 'NLE*' GROUP BY T_Code HAVING SUM(Home_Runs) > 85 • ORDER BY and WHERE can not be used with aggregate functions • replaced by GROUP BY and HAVING respectively

22. CREATE Statement • CREATE TABLE Sports • (Sport_ID AUTOINCREMENT, • Sport_Name TEXT NOT NULL, • Number_Teams INT) • Creates a table named Sports with 3 columns

23. DROP Statement • DROP TABLE Sports • Unconditionally deletes the table