CS340 · Chapter 6 · Annotated SlidesBasic SQL
Original
Overview

Basic SQL

SQL as a declarative language, SELECT–FROM–WHERE with projection and selection, duplicates and DISTINCT, renaming, LIKE patterns, arithmetic, BETWEEN, ORDER BY, the set operators UNION / INTERSECT / EXCEPT, and INSERT, DELETE and UPDATE.

How to read this page

Every original slide is shown unchanged. Yellow marks on a slide point to a numbered note: hover or tap a mark to jump to its note, and hover a note to see its marks. Questions tagged Past exam appeared on a real paper; Prediction questions are likely angles, not confirmed. Pick an answer to each question: a wrong pick tells you why it is wrong, so you can try again.

All 43 slides
  1. 1 Basic SQL
  2. 2 LEARNING GOALS
  3. 3 BEFORE WE START..
  4. 4 DATABASES SO FAR..
  5. 5 THE SQL QUERY LANGUAGE
  6. 6 RECALL DDL
  7. 7 THE SQL QUERY LANGUAGE
  8. 8 Student Course Takes
  9. 9 Slide 9
  10. 10 Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96
  11. 11 Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
  12. 12 Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
  13. 13 Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
  14. 14 Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
  15. 15 CREATINGTABLESINSQL(DDL) - REVISITED
  16. 16 RUNNING EXAMPLE IN YOUR DATABASE
  17. 17 PRACTICE QUESTION:SQLPROJECTION
  18. 18 SELECTION IN SQL
  19. 19 SELECTION EXAMPLE
  20. 20 ACTIVITY: SELECTION
  21. 21 ACTIVITY: SELECTION
  22. 22 R fro
  23. 23 DUPLICATES, SETS, AND MULTISETS (BAGS)
  24. 24 d ubeds
  25. 25 RENAMING ATTRIBUTES
  26. 26 PATTERN MATCHING
  27. 27 ARITHMETIC OPERATIONS
  28. 28 BETWEEN OPERATOR
  29. 29 And
  30. 30 ORDER BY CLAUSE (CONTD)
  31. 31 y
  32. 32 UNION ALL
  33. 33 MORE SET OPERATORS (EXCEPT/MINUS- INTERSECT)
  34. 34 SET OPERATORS
  35. 35 REVIEW OF BASIC QUERIES
  36. 36 INSERT, DELETE, AND UPDATE STATEMENTS IN SQL
  37. 37 INSERT
  38. 38 INSERT (CONTD)
  39. 39 INSERT (CONTD)
  40. 40 INSERT (CONTD)
  41. 41 DELETE
  42. 42 UPDATE
  43. 43 SUMMARY
Slide 1 / 43

Basic SQL

Slide 1: Basic SQL
Slide 2 / 43

LEARNING GOALS

Slide 2: LEARNING GOALS
Slide 3 / 43

BEFORE WE START..

Slide 3: BEFORE WE START..
Slide 4 / 43

DATABASES SO FAR..

Slide 4: DATABASES SO FAR..
Slide 5 / 43

THE SQL QUERY LANGUAGE

Slide 5: THE SQL QUERY LANGUAGE
Slide 6 / 43

RECALL DDL

Slide 6: RECALL DDL
Slide 7 / 43

THE SQL QUERY LANGUAGE

Slide 7: THE SQL QUERY LANGUAGE

Notes

  1. SQL is declarative: say WHAT you want, not how to get it.

Slide 8 / 43

Student Course Takes

Slide 8: Student Course Takes
Slide 9 / 43

Slide 9

Slide 9: Slide 9
Slide 10 / 43

Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96

Slide 10: Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96
Slide 11 / 43

Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96

Slide 11: Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
Slide 12 / 43

Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96

Slide 12: Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
Slide 13 / 43

Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96

Slide 13: Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
Slide 14 / 43

Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96

Slide 14: Sailors Reserves sid sname Rating age sid bid day 22 dustin 7 45.0 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96
Slide 15 / 43

CREATINGTABLESINSQL(DDL) - REVISITED

Slide 15: CREATINGTABLESINSQL(DDL) - REVISITED
Slide 16 / 43

RUNNING EXAMPLE IN YOUR DATABASE

Slide 16: RUNNING EXAMPLE IN YOUR DATABASE
Slide 17 / 43

PRACTICE QUESTION:SQLPROJECTION

Slide 17: PRACTICE QUESTION:SQLPROJECTION

Notes

  1. Projection = choose columns in SELECT.

Slide 18 / 43

SELECTION IN SQL

Slide 18: SELECTION IN SQL

Notes

  1. Selection = choose rows in WHERE (=, <>, <, >, AND, OR, NOT).

Exam angle

PredictionQ1

In SQL, rows are filtered in the:

Slide 19 / 43

SELECTION EXAMPLE

Slide 19: SELECTION EXAMPLE
Slide 20 / 43

ACTIVITY: SELECTION

Slide 20: ACTIVITY: SELECTION
Slide 21 / 43

ACTIVITY: SELECTION

Slide 21: ACTIVITY: SELECTION
Slide 22 / 43

R fro

Slide 22: R fro
Slide 23 / 43

DUPLICATES, SETS, AND MULTISETS (BAGS)

Slide 23: DUPLICATES, SETS, AND MULTISETS (BAGS)

Notes

  1. SQL tables are multisets (bags): duplicates are kept unless you write SELECT DISTINCT.

Exam angle

PredictionQ1

SELECT removes duplicate rows by default.

Slide 24 / 43

d ubeds

Slide 24: d ubeds
Slide 25 / 43

RENAMING ATTRIBUTES

Slide 25: RENAMING ATTRIBUTES
Slide 26 / 43

PATTERN MATCHING

Slide 26: PATTERN MATCHING

Notes

  1. LIKE: % = any number of characters, _ = exactly one. '_A%' = second letter is A.

Exam angle

PredictionQ1

WHERE name LIKE '_a%' matches names whose:

Slide 27 / 43

ARITHMETIC OPERATIONS

Slide 27: ARITHMETIC OPERATIONS
Slide 28 / 43

BETWEEN OPERATOR

Slide 28: BETWEEN OPERATOR

Notes

  1. BETWEEN a AND b is INCLUSIVE of both ends.

Exam angle

PredictionQ1

rating BETWEEN 5 AND 7 includes ratings 5 and 7.

Slide 29 / 43

And

Slide 29: And
Slide 30 / 43

ORDER BY CLAUSE (CONTD)

Slide 30: ORDER BY CLAUSE (CONTD)
Slide 31 / 43

y

Slide 31: y
Slide 32 / 43

UNION ALL

Slide 32: UNION ALL

Notes

  1. UNION removes duplicates; UNION ALL keeps them. Both queries must be union-compatible (same number and types of columns).

Slide 33 / 43

MORE SET OPERATORS (EXCEPT/MINUS- INTERSECT)

Slide 33: MORE SET OPERATORS (EXCEPT/MINUS- INTERSECT)

Notes

  1. INTERSECT: rows in both. EXCEPT (MINUS): rows in the first but not the second.

Exam angle

PredictionQ1

Sailors who reserved a red boat but NOT a green boat:

Slide 34 / 43

SET OPERATORS

Slide 34: SET OPERATORS
Slide 35 / 43

REVIEW OF BASIC QUERIES

Slide 35: REVIEW OF BASIC QUERIES
Slide 36 / 43

INSERT, DELETE, AND UPDATE STATEMENTS IN SQL

Slide 36: INSERT, DELETE, AND UPDATE STATEMENTS IN SQL
Slide 37 / 43

INSERT

Slide 37: INSERT

Notes

  1. INSERT INTO T VALUES (…) or INSERT INTO T (cols) VALUES (…); values must respect the constraints.

Exam angle

Past exam · Midterm exam

Q16: insert a customer, update an address

Slide 38 / 43

INSERT (CONTD)

Slide 38: INSERT (CONTD)
Slide 39 / 43

INSERT (CONTD)

Slide 39: INSERT (CONTD)
Slide 40 / 43

INSERT (CONTD)

Slide 40: INSERT (CONTD)
Slide 41 / 43

DELETE

Slide 41: DELETE

Notes

  1. DELETE FROM T WHERE …; without WHERE it deletes every row (the table stays).

  2. Trap DELETE FROM T removes the rows; DROP TABLE T removes the table itself.

Slide 42 / 43

UPDATE

Slide 42: UPDATE

Exam angle

PredictionQ1

Give every employee in department 5 a 10% raise:

Slide 43 / 43

SUMMARY

Slide 43: SUMMARY