SQL Structured Query Language A Brief Overview 1272020

SQL Structured Query Language A Brief Overview 12/7/2020 SQL-2 1

What is it? • An attempt to make access to data have a universal language. • Most vendors these days support at least a subset of the SQL syntax. • However, there will be variations even among “ANSI SQL” implementations. • Go with the flow. It is an improvement. 12/7/2020 SQL-2 2

Vocabulary • • • Query: Command given to Database Rows: Record Fields: Columns Clause: Part of a Query { , , } : Select ONLY One of These [ , , ] : Zero or More of These 12/7/2020 SQL-2 3

SQL: Two Kinds of Queries • Queries that return rows: Select • Queries that don’t: Action Queries Syntax very similar: VERB [Qualifiers] Where [Criteria] 12/7/2020 SQL-2 4

VERBS • SELECT – Returns rows • Action Verbs – Update : Changes Existing Rows – Insert : Insert New Rows – Delete : Remove Rows 12/7/2020 SQL-2 5

Other Parts of ANSI SQL • • Table Definition Language Users & Security Stored Procedures Views Triggers Configuration Utilities (Backup, compress, etc) 12/7/2020 SQL-2 6

SQL Punctuation Summary • ANSI SQL • ACCESS Wild Card: % True: -1 False: 0 Delete: Omit * Dates: Use ' or " Grouping: Only ( ) 12/7/2020 Wild Card: * True: TRUE False: FALSE Delete: Required * Dates: Use # Grouping: ( ) or [ ] SQL-2 7

SQL Overview Day 1: Select 12/7/2020 SQL-2 8

SELECT • For our purposes SELECT is the most useful. Can be used in the RECORDSOURCE property of a data control, or to provide the record source of a RECORDSET object. 12/7/2020 SQL-2 9

SELECT Syntax SELECT [List of Fields or *] FROM [Table(s)] {Optionally Join Syntax} WHERE [Criteria] GROUP BY [List of Fields] ORDER BY [List of Fields] 12/7/2020 SQL-2 10

Data Type Delimiters – Apostrophes (or Quotes) for Strings/Text/Memos: • ‘text’ ‘The Brown Cow is How…’ "Moo" • For coding in most languages the quote character is used for a string literal so most high level programmers use apostrophe to delimit SQL literals – Pound signs or Apostrophes for Dates/Times: • #5/2/62# #4: 12 am# '2/17/94 13: 00' • ANSI is the apostrophe, but Access etc needs pound – Nothing for Booleans: • TRUE FALSE • 0 is false, and non-zero is true, traditionally, -1 is used as testing for the sign bit is very fast on most machines. – Nothing for Numbers: • 4. 12 12/7/2020 1423 -43. 1 -15 SQL-2 11

NULL • There is a special value for no data called NULL (that name) • Any field type can contain NULL. • You can test for it in a where clause with the built in test Is NULL e. g. Where Name. Last is Null • Some SQL variants do not allow the previous syntax ut provide a function Is. Null() to test for Nulls. • Null is NOT zero or the empty string it is NULL is what you get when you don’t give a field an explicit value and there is not a default. 12/7/2020 SQL-2 12

SELECT: Examples • All columns, all rows from Authors Table SELECT * FROM Authors • All columns, rows where Pub. ID = 1213 SELECT * FROM Publishers WHERE Pub. ID = 1213 • Just Two Columns for just matching rows SELECT Last. Name, Placeof. Birth FROM Customers WHERE ((AGE > 30) and (SEX = ‘M’)) ORDER BY Last. Name, Place. Of. Birth 12/7/2020 SQL-2 13

Select: Renaming columns To make the output of SQL select quires more human eye readable, columns may be renamed in the output. Note that this does NOT rename them in the database. SELECT Last. Name as 'Last Name', Placeof. Birth as 'Place Born in' FROM Customers Notice the use of apostrophes (or quotes) when the output column name includes spaces or punctuation. This results in good looking output in reports. NOTE: In ANSI SQL the AS is optional. On some implementations it is not allowed at all. 12/7/2020 SQL-2 14

WHERE Clause • Can use Boolean AND, OR or NOT to join statements together. • Should use ( ) around clauses and around entire thing if multiple clauses. • Can use traditional < > = • Also can use Math Operators: + - * / • Also can use LIKE with wildcards (% or *) 12/7/2020 SQL-2 15

Use of Like • SQL’s response to case varies by database so in looking for all of the people who start with ‘SM’ we could (Access uses *, ANSI SQL %): . . . Where ( (Last. Name Like ‘SM*’) or (Name Like ‘sm*’) or (Name Like ‘Sm*’) ) • If you know your database is case insensitive, you can simplify your queries considerably: …Where (Last. Name Like 'SM%') • Notice to be safe we use ( ) around clauses. Wild cards can be in front, back or both. • Like clauses where the wild card is at the end are faster then mixed or at the beginning of the field to be searched. 12/7/2020 SQL-2 16

Dots, Parenthesis, Braces, and Brackets • ANSI SQL uses parenthesis ( ) and not braces { } or brackets [ ]. Some implementations like MS Access allow use of []. • If you use the excellent query tool in MS Access, you should convert all of the [ ] to ( ) for maximum portability. • If a query needs to indicate the source of a column the DOT (. ) notation is used. Database. Table. Field 12/7/2020 SQL-2 17

Where : IN • In is handy in Where clauses to identify a subset of the records to be examined. • Can be a list or the results of a sub-query. Select Name, Year. Born From Authors Where Year. Born In (1962, 1963, 1964) 12/7/2020 SQL-2 18

Sub Queries - I • In previous example if years had been in the Hole. In. One table and we wanted a list of all authors born in years that had a hold in one we could: Select Name, Year. Born From Authors Where Year. Born In (Select Year From Hole. In. One) • Notice we only return the ONE field in the sub query corresponding to the field in the Where clause. 12/7/2020 SQL-2 19

Optimizing SELECT Queries • Only return the columns (fields) you need, extras require data you don’t need to be Transmitted over the wire. • So avoid * (all fields) if possible. • If there is a computation, do it in the select query. Better yet do your transformations with UPDATE. • Avoid iterating sets of records if possible. 12/7/2020 SQL-2 20

SQL Overview Day 2: Joins 12/7/2020 SQL-2 21

Back to SELECT: The Join • Sometimes we want fields from more then one table. To do this we use a JOIN. BTW: JET = Join Engine Technology. • Two Types: – INNER: Rows have only fields in one that match the other. – OUTER: May include some of each with NULLS for missing values. 12/7/2020 SQL-2 22

Joins • Must use DOT (. ) notation i. e. TABLE. FIELD • Best constructed with a tool (actually all queries are best constructed with a tool). • Most powerful feature in SQL. We normalize tables for efficiency, joins allow us to view data de-normalized like in a spreadsheet. 12/7/2020 SQL-2 23

Join Statement Syntax SELECT [Fields] FROM Table-A {INNER | LEFT | RIGHT} JOIN Table-B ON (Table-A. Field-1 = Table-B. Field-2) WHERE [criteria] ORDER BY [fields] Some database implementations also allow the key word FULL in the join syntax. 12/7/2020 SQL-2 24

Inner Joins • In an inner join the rows returned only include those in which there is a matching row or rows from BOTH tables. • For example if I have a table of Customers and Invoices and if there are customers with out invoices or invoices without customers those rows will NOT be included in the rows returned from an inner join. 12/7/2020 SQL-2 25

Join Example From Shop. mdb Query: v. All. Orders: All Orders SELECT Customer. ID, Customer. Name. Last, Customer. Name. First, Sale. ID, Sale. Date, Sale. Detail. Quantity, Sale. Detail. Unit. Price, Product. Name FROM Product INNER JOIN ((Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Detail ON Sale. ID = Sale. Detail. Sale. ID) ON Product. ID = Sale. Detail. Product. ID WHERE (((Customer. Voided)=0) AND ((Sale. Voided)=0)) ORDER BY Customer. Name. Last, Customer. Name. First; 12/7/2020 SQL-2 26

Join Example Picture 12/7/2020 SQL-2 27

Join Example Results 12/7/2020 SQL-2 28

Outer Joins • In an outer join it is possible to get back rows from one or both table for which there is no matching row in the other table. • For example if I have a table of Customers and Invoices and if there are customers with out invoices or invoices with out customers I can still do a join that returns all of the rows. 12/7/2020 SQL-2 29

Outer Joins: Left or Right • Good for – detecting mismatches – detecting missing records – Reporting on records that might not have subrecords or detail information • LEFT implies all record from left table that may or not have records in right table. • RIGHT: is the opposite 12/7/2020 SQL-2 30

Outer Join Types Customers LEFT Sale All Customers With or Without Sales Customers RIGHT Sale All Sales With or Without Customers A Full join would be both 12/7/2020 SQL-2 31

Venn Diagrams of Joins A B Inner: On the records that match in both Left: All the records In A and the matching Ones in B 12/7/2020 SQL-2 A B Right: All the records in B and the matching ones in A 32

Outer Join: Example Find customers without Orders. Note use of NULL. SELECT Customer. ID, Customer. Name. First, Customer. Name. Last FROM Customer Left JOIN Sale ON Customer. ID = Sale. Customer. ID WHERE (((Sale. ID) Is Null)); Customer. ID 12/7/2020 Name. First Name. Last -1 System Administrator -2 Product Manager SQL-2 33

HAVING • HAVING filters the results after the grouping or other query, • WHERE filters what is included in the query at all. • WHERE is generally better, HAVING may be inevitable. • Syntax is identical. 12/7/2020 SQL-2 34

Group By • For lumping data together into groups. • All fields listed in SELECT part must participate in the GROUP BY portion somehow. • AS can be used to give computed field a name. These functions result in aggregate fields and are called aggregate functions. 12/7/2020 SQL-2 35

Grouping Operators • Statistical – COUNT, SUM, AVE, MIN, MAX, STDEV, VAR, FIRST, LAST • Selection – EXPRESSION, WHERE • Grouping – 12/7/2020 GROUP BY SQL-2 36

Group By: Example SELECT Catalog. ID, Catalog. Name, Count(Product. ID) AS Countof. Products FROM [Catalog] LEFT JOIN Product ON Catalog. ID = Product. Catalog. ID GROUP BY Catalog. ID, Catalog. Name; Catalog. ID 12/7/2020 Name Countof. Products 1 Neverland Supply 31 2 It Suits You 0 3 Gameland 0 SQL-2 37

Sub-Queries - II • Useful in asking for complex information. • Notice that named queries (views in SQL*SERVER, queries in Access) can act as sub queries. • Good for finding duplicates. • Also good for intense reports. 12/7/2020 SQL-2 38

Sub Query Example: Duplicates Sub-Query is in BOLD SELECT DISTINCTROW product. Productname, Product. ID FROM product WHERE (( (product. Productname) In (SELECT Productname FROM product As Tmp GROUP BY Productname HAVING Count(*)>1 ) )) ORDER BY product. Productname Note: Alternate use of IN qualifier for a sub-query! 12/7/2020 SQL-2 39

SQL Overview Day 3: Action and Special Queries 12/7/2020 SQL-2 40

Action Queries Update Insert Delete 12/7/2020 SQL-2 41

Action Queries: DELETE • DELETE * FROM Table WHERE criteria • Examples Delete * From Authors Where Dead = TRUE Delete * From Publishers Where Pub. ID > 30 Delete * From Moo. Cows Delete * (This query is EVIL) (Again ANSI SQL omits the * entirely) • Delete powerful, usually no way to UNDO. 12/7/2020 SQL-2 42

Action Queries: UPDATE • UPDATE table SET field = value [, field = value. . . ] WHERE criteria • Examples Update Authors Set Commissions = (Sales * 0. 1) Update Authors Set Address = ‘ 123 Maple’ Where (Au. ID = 3121) Update Authors Set Dead = False, Stupid = True Where ( (Sales > 100000) and (Commissions = 0) ) [Note use of ( ) in where clause] 12/7/2020 SQL-2 43

Action Queries: Update • Notice that we can do computation that takes place on server side (better). • Most all of the familiar math operators work as expected ( + - * / ) • Different SQL variations add mathematical functions. ( Sin, Cos, Abs, Sgn, Powers ) 12/7/2020 SQL-2 44

Action Queries: INSERT • INSERT INTO table (field, field) VALUES (value, value) • Examples INSERT INTO authors (Name, Address, Sales) VALUES (‘Smith, Frank’, ‘ 123 Main St’, 35232. 06) INSERT INTO publishers (Name, ABACODE, Paperbacks) VALUES (‘Smith Books’, 1311, TRUE ) ANSI SQL: True is <> 0 (usually – 1) False is 0 12/7/2020 SQL-2 45

Special Queries These queries not supported on all machines. 12/7/2020 SQL-2 46

Transform and Pivot • Useful for making cross-tabs and doing scary manipulations of data. • Hard to get the syntax right, so best made with a tool. TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value 1[, value 2[, . . . ]])] 12/7/2020 SQL-2 47

Cross tab Example TRANSFORM Count(Titles. ISBN) AS [The Value] SELECT Titles. [Year Published], Count(Titles. ISBN) AS [Total Of ISBN] FROM Titles GROUP BY Titles. [Year Published] PIVOT Titles. Pub. ID 12/7/2020 SQL-2 48

Crosstab Result Year Published 1980 1986 1987 1989 1990 1991 1992 1993 1994 1995 1996 12/7/2020 Total Of ISBN 1 3 4 3 11 23 39 19 44 52 28 SQL-2 3 3 10 19 34 14 31 46 27 4 1 1 4 5 5 13 6 1 49