Transcript document
Industry standard for interacting with
relational databases
Statements to Enter, Retrieve, Modify, Remove
& Display data stored in database tables
◦ Pose Ad-Hoc Queries
◦ Create Various Objects
◦ Perform Database Maintenance
Works with SQL-Server and almost all other
data management sytems
Query Statements
Data Definition Language Statements
Data Manipulation Language Statements
Transaction Control Statements
Data Control Language Statements
Retrieve rows from one or more tables
◦ Select Statement
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Define Structures that make up the database
◦ Tables
◦ Views
CREATE
ALTER
DROP
RENAME
TRUNCATE
Modify the contents of tables
◦ INSERT
◦ UPDATE
◦ DELETE
Tools to make changes to rows permanent, or
to revoke the changes
◦ COMMIT
◦ ROLLBACK
◦ SAVEPOINT
Who has access to what tables
Who can log into the SQL Server System
Access and Privileges for each user for
various tables
◦ GRANT
◦ REVOKE
Write SQL statements to accomplish a database task
SQL-Server SQL Server Management Studio
Interface
Can be written on multiple lines (cannot split words)
Cannot abbreviate words
Separate words by at least one white space
Not Case Sensitive
Commas separate lists
Period delimits two names (tableName.fieldName)
Single Quotation marks enclose literal character
strings (including dates)
Semicolon indicates the end of a SQL Statement
SQL SELECT Statement
Column References
ORDER BY Clause
WHERE Clause
SQL Statement String Construction
10
4/11/2016
Syntax:
SELECT [DISTINCT | ALL] { * | <column list>}
FROM <table list>
[WHERE <search condition>]
[GROUP BY <group field list>]
[HAVING <group criteria>]
[ORDER BY <order condition>]
11
4/11/2016
Syntax:
<derived column> [[AS] <column name>]
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
12
4/11/2016
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
First, Last, City
Player.First, Team.Location
Price * Qty AS InvoiceAmount
First, [Last Name] AS Last
Max(TeamId)
Avg(Price)
IsNull(Name)
DISTINCT State
*
Player.*, Team.Nickname
13
4/11/2016
Specifies which records from the tables listed
in the FROM clause are affected by the
SELECT statement
Only those records satisfying <search
condition> are included in the result
<search condition> is a logical expression
which may include:
◦ One or more of four types of logical predicates
◦ the logical operators AND, OR, NOT
14
4/11/2016
WHERE clause has four logical predicates:
◦ comparisons w/relational operators (<, <=, =,
>=, >, <>)
expression1 comparison-operator expression2
◦ LIKE
expression LIKE pattern
◦ IN
expression [NOT] IN (value1, value2, ...)
◦ BETWEEN
expression [NOT] BETWEEN value1 AND value2
15
4/11/2016
WHERE City = ‘New Orleans’
WHERE Rate > 5.50
‘* relational ops
WHERE State LIKE ‘?L’
WHERE [Last Name] LIKE ‘S*’
‘* LIKE predicate
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 City=‘New Orleans’ AND Name LIKE ‘L*’
WHERE NOT State=‘AL’ AND Major=‘CIS’
WHERE Class=‘Union’ OR Rate>10.00
16
4/11/2016
Syntax:
ORDER BY <order condition>
Examples:
ORDER BY LastName, FirstName
ORDER BY HR DESC
ORDER BY TeamId ASC, HR DESC
17
4/11/2016
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 City=‘Jacksonville‘;”
or
Sql = “Select ... WHERE City=‘“ & txtCity.Text & “‘;”
When variable contains numeric data do not use quotes
Sql = “Select ... WHERE Age > “ & intAge & “ ORDER BY Age”
18
4/11/2016
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
19
4/11/2016
Syntax:
INSERT INTO <table name>
[(<column name>[{,<column name>}…])]
VALUES (<value>[{,<value>}…])
◦ 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
20
4/11/2016
INSERT INTO Team (League, Location,
Nickname, Stadium)
VALUES (‘AL’, ‘Mobile’, ‘Bay Bears’,
‘Hank Aaron Stadium’)
21
4/11/2016
Syntax:
UPDATE <table name>
SET <set clause expression> [{, <set clause
expression>}…]
[WHERE <search condition>]
◦ <set clause expression> syntax:
<column name> = <value expression>
◦ Omitting WHERE updates all rows
22
4/11/2016
UPDATE Team
SET Stadium=‘Citizens Bank Park’
WHERE Stadium=‘Veterans Stadium’
UPDATE Player
SET HR=HR + 1
WHERE PlayerId=22
23
4/11/2016
Syntax:
DELETE FROM <table name>
[WHERE <search condition>]
◦ Omission of WHERE removes all rows
24
4/11/2016
DELETE FROM Team
WHERE Nickname=‘Bay Bears’
DELETE FROM Team
WHERE League NOT IN ('AL', 'NL')
25
4/11/2016
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
26
4/11/2016
Aggregate function
Description
AVG(expr)
Average of the values in a column. The column can
contain only numeric data.
COUNT(expr), COUNT(*)
A count of the values in a column (if you specify a
column name as expr) or of all rows in a table or
group (if you specify *). COUNT(expr) ignores null
values, but COUNT(*) includes them in the count.
MAX(expr)
Highest value in a column (last value
alphabetically for text data types). Ignores null
values.
MIN(expr)
Lowest value in a column (first value
alphabetically for text data types). Ignores null
values.
SUM(expr)
Total of values in a column. The column can
contain only numeric data.
4/11/2016
27
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
28
4/11/2016
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
29
4/11/2016
Combines records with identical values in the
specified field list into a single record.
Syntax:
SELECT <fieldlist>
FROM <tableList>
[WHERE <criteria>]
[GROUP BY <groupfieldlist>]
[HAVING <condition>]
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
30
4/11/2016
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, team’s with more than 100 homers:
SELECT TeamId, Sum(HR) As TotalTeamHR
FROM Player
GROUP BY TeamId
HAVING Sum(HR)>100
Or, a count of each team’s players with less than
10 homers:
SELECT TeamId, Count(*) As LowHRcount
FROM Player
WHERE HR<10
GROUP BY TeamId
31
4/11/2016
Direct Execution
◦ Communicate directly through a client application
SQL Server Management Studio
Module Binding
Embedded SQL
CLI (Call-Level Interface)
◦ Create blocks of SQL statements and combined with a
complete application through a linker program
◦ Statements are placed directly into the host
programming language (C++ or Java)
◦ Requires a SQL Server preprocessor
◦ Invoke SQL statements by passing the statements
directly to the subroutines that process them
◦ Executed directly by the DBMS
Rich, Easy to Use environment for creating
and executing SQL instructions
Windows dialog box
GUI Interface – closely matches Visual
Studio.Net 2005 interface