ITE 370 SQL

1
ITE 370 - SQL

• Chapters 7 - 12

2
Database Access SQL Queries

• SQL SELECT Statement
• Column References
• ORDER BY Clause
• WHERE Clause
• SQL Statement String Construction

3
SQL Select Statement

• Syntax
• SELECT DISTINCT ALL ltcolumn listgt
• FROM lttable listgt
• WHERE ltsearch conditiongt
• GROUP BY ltgroup field listgt
• HAVING ltgroup criteriagt
• ORDER BY ltorder conditiongt

4
Column References in the SELECT clause

• Syntax
• ltderived columngt AS ltcolumn namegt
• Derived column may
• Be a column
• Contain an expression referencing one or more
  columns
• Contain function arguments
• The AS is for an alias, or alternate name, for
  the derived column result

5
SELECT ltcolumn listgt Examples

• SELECT First, Last, City
• SELECT Player.First, Team.Location
• SELECT Price Qty AS InvoiceAmount
• SELECT First, Last Name AS Last
• SELECT Max(TeamId)
• SELECT Avg(Price)
• SELECT IsNull(Name)
• SELECT DISTINCT State
• SELECT
• SELECT Player., Team.Nickname

6
WHERE ltsearch conditiongt

• Specifies which records from the tables listed in
  the FROM clause are affected by the SELECT
  statement
• Only those records satisfying ltsearch conditiongt
  are included in the result
• ltsearch conditiongt is a logical expression which
  may include
• One or more of four types of logical predicates
• the logical operators AND, OR, NOT

7
Logical Predicates of WHERE Clause

• WHERE clause has four logical predicates
• comparisons w/relational operators (lt, lt, , gt,
  gt, ltgt)
• expression1 comparison-operator expression2
• LIKE
• expression LIKE pattern
• IN
• expression NOT IN (value1, value2, ...)
• BETWEEN
• expression NOT BETWEEN value1 AND value2

8
Examples of WHERE Clauses

• WHERE City New Orleans relational ops
• WHERE Rate gt 5.50
• WHERE State LIKE ?L LIKE predicate
• WHERE Last Name LIKE S
• WHERE State IN (AL, MS, FL) IN predicate
• BETWEEN predicate
• WHERE Birthday BETWEEN 7/1/78 AND 7/30/78
• WHERE Pay Rate BETWEEN 5.50 AND 10.00
• multiple comparisons with AND, OR, NOT
• WHERE CityNew Orleans AND Name LIKE L
• WHERE NOT StateAL AND MajorCIS
• WHERE ClassUnion OR Rategt10.00

9
ORDER BY Clause

• Syntax
• ORDER BY ltorder conditiongt
• Examples
• ORDER BY LastName, FirstName
• ORDER BY HR DESC
• ORDER BY TeamId ASC, HR DESC

10
SQL Statement String Construction

• Strings can be constructed in code with
  concatenation and variables can be concatenated
  into the string
• Dim Sql As String
• Sql Select LName, FName From Emp
• Sql Sql Where Title cboTitle.Text
  
• When variable contains a string surround it with
  quotes
• Sql Select ... WHERE CityJacksonville
• or
• Sql Select ... WHERE City txtCity.Text
  
• When variable contains numeric data do not use
  quotesSql Select ... WHERE Age gt intAge
  ORDER BY Age

11
Database Access SQL Queries

• SQL INSERT statement
• Adds one or more rows to a table
• SQL UPDATE statement
• Modifies one or more columns of one or more rows
  of a table
• SQL DELETE statement
• Removes one or more rows from a table

12
SQL INSERT Statement

• Syntax
• INSERT INTO lttable namegt
• (ltcolumn namegt,ltcolumn namegt)
• VALUES (ltvaluegt,ltvaluegt)
• Second line is optional
• If you omit, then values list must be complete
  and in order of field creation
• If you include column list, values list must
  match column list in number and order (but not in
  field creation order

13
INSERT Example

• INSERT INTO Team (League, Location,
• Nickname, Stadium)
• VALUES (AL, Mobile, Bay Bears,
• Hank Aaron Stadium)

14
SQL UPDATE statement

• Syntax
• UPDATE lttable namegt
• SET ltset clause expressiongt , ltset clause
  expressiongt
• WHERE ltsearch conditiongt
• ltset clause expressiongt syntax
• ltcolumn namegt ltvalue expressiongt
• Omitting WHERE updates all rows

15
UPDATE Examples

• UPDATE Team
• SET StadiumCitizens Bank Park
• WHERE StadiumVeterans Stadium
• UPDATE Player
• SET HRHR 1
• WHERE PlayerId22

16
SQL Delete Statement

• Syntax
• DELETE FROM lttable namegt
• WHERE ltsearch conditiongt
• Omission of WHERE removes all rows

17
DELETE Examples

• DELETE FROM Team
• WHERE NicknameBay Bears
• DELETE FROM Team
• WHERE League NOT IN ('AL', 'NL')

18
SQL Queries GROUP BY and HAVING

• Aggregate functions and queries
• More clauses in the SELECT statement
• GROUP By Clause
• HAVING Clause

19
Aggregate Function

• A function that generates a single value from a
  group of values
• often used with Group By and Having clauses
• a.k.a. set function
• Examples
• Avg, Count, Max, Min, and Sum

20
Examples of Aggregate Functions
21
Aggregate Query

• A query (SQL statement) that summarizes
  information from multiple rows by including an
  aggregate function such as Sum or Avg
• For example, you can create a query that averages
  the contents of a price column
• SELECT Avg(Price) AS AvgPrice
• FROM Book

22
Aggregate Queries (cont.)

• Aggregate queries can also display subtotal
  information by creating groups of rows that have
  data in common
• An example would be a query that displays the
  average price of a book for each publisher
• Use the GROUP BY clause
• SELECT PublisherID, Avg(Price) As AvgPrice
• FROM Book
• GROUP BY PublisherID

23
GROUP BY Clause

• Combines records with identical values in the
  specified field list into a single record.
• Syntax
• A summary value is created for each record if you
  include an SQL aggregate function, such as Sum or
  Count, in the SELECT statement.
• For example total the home runs for each team
• SELECT TeamId, Sum(HR) As TeamTotalHR
• FROM Player
• Group By TeamId

SELECT ltfieldlistgt FROM lttableListgt WHERE
ltcriteriagt GROUP BY ltgroupfieldlistgt HAVING
ltconditiongt
24
GROUP BY Clause (cont.)

• Use the WHERE clause to exclude rows you don't
  want grouped, and use the HAVING clause to filter
  records after they've been grouped.
• For example, teams with more than 100 homers
• SELECT TeamId, Sum(HR) As TotalTeamHR
• FROM Player
• GROUP BY TeamId
• HAVING Sum(HR)gt100
• Or, a count of each teams players with less than
  10 homers
• SELECT TeamId, Count() As LowHRcount
• FROM Player
• WHERE HRlt10
• GROUP BY TeamId

25
Executing SQL Statements

• Direct Invocation
• Communicate directly from a front-end application
  to the database
• SQL Server Query Analyzer
• Call Level Interface
• Invoke SQL statements through an interface by
  passing SQL statements are argument values to
  subroutines these statements are executed
  directly by the DBMS (example ODBC)
• Embedded SQL
• SQL statements are embedded directly into the
  host programming language. These statements are
  analyzed prior to compilation of the program
• Module Binding
• Create self-contained blocks of SQL code that are
  separate from the host programming language.
  These blocks (modules) are then linked to the
  application.