Introduction to Databases

Chapter 7:Data Access and Manipulation

SELECT Statement

• Primary statement for data retrieval• Clauses let you specify precise

information retrieved, sorted, organized, and grouped

• Source can be tables, views, and table-valued user-defined functions

• Most basic syntax is:

SELECT column_list FROM source WITH search_conditions

SELECT Statement Advanced Syntax

SELECT column_list [ALL | DISTINCT][TOP expression [PERCENT] [WITH TIES]][INTO table_name]FROM source [[INNER|OUTER|CROSS] JOIN]WHERE search_condition[ORDER BY order_expression [ASC] | [DESC]][GROUP BY [ALL] group_expression][HAVING search_condition]

Keywords: ALL DISTINCT TOP INTOJOIN ORDER BY GROUP BY HAVING

Filtering Results• Filter results using comparison operators:

=, >, >=, <, <=, <>, !=

• AND and OR: To combine conditionsSELECT CUSTNUM, CUSTNAME FROM CUSTOMERWHERE HQCITY=‘New York’ OR(CUSTNUM>1500 AND HQCITY=‘Atlanta’)

• BETWEEN and IN: For rangesSELECT CUSTNUM, CUSTNAME FROM CUSTOMERWHERE CUSTNUM BETWEEN 1000 AND 1700

• LIKE: Adds flexibilitySELECT CUSTNAME FROM CUSTOMERWHERE CUSTNAME LIKE ‘C_t%’

Managing Result Sets• Specifying columns

SELECT PRODNUM, (QOH * UNITPRICE) AS [VALUE]FROM PRODUCT

• Specifying rowsSELECT TOP 3 PRODNUM, (QOH * UNITPRICE) AS [VALUE]FROM PRODUCT

• Writing results to new tableSELECT PRODNUM, (QOH * UNITPRICE) AS [VALUE]INTO PRODVAL FROM PRODUCT

• Limiting results to unique valuesSELECT DISTINCT SPNUM FROM CUSTOMER

– With DISTINCT, entire result must be duplicated for row to be dropped

Sorting and Organizing

• ORDER BY: Defines order; can use multiple columns

SELECT * FROM SALES ORDER BY SPNUM, PRODNUM

• ASC and DESC: Returns rows in ascending or descending order

SELECT PRODNUM, PRODNAME, UNITPRICE FROM PRODUCT ORDER BY UNITPRICE DESC

Grouping

• GROUP BY: To group rows in result in order to perform functions such as SUM

SELECT SPNUM, SUM(QUANTITY) AS [SUM] FROM SALES WHERE SPNUM> =150 GROUP BY SPNUM

• GROUP BY column list and ORDER BY column list must be aggregate values or called out in GROUP BY clause

• GROUP BY can use HAVING clause to limit results from group calculations performed by built-in function

SELECT SPNUM, SUM(QUANTITY) AS [SUM] FROM SALES WHERE SPNUM>=150 GROUP BY SPNUM HAVING SUM(QUANTITY)>=5000

Operator PrecedenceSQL Server Transact SQL Precedence

Level 1: ~Level 2: *, /, %Level 3: +, -, &Level 4: =, >, <, >=, <=, <>, !>, !<, Level 5: ^Level 6: NOTLevel 7: ANDLevel 8: ALL, ANY, BETWEEN, IN, LIKE, SOMELevel 9: =

Operator Precedence

• Operators at same precedence level evaluated from left to right

• When same symbol used for two operations (e.g. “ =”), type of operation being performed can be determined by context in which symbol used

• Precedence varies by DBMS implementation

Combining Statement Results

• To combine, statements must have:– Same number of columns– Columns in same order– Columns with compatible data types

• UNION: Combines two resultsSELECT HQCITY FROM CUSTOMER WHERE SPNUM = 137

UNION

SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 186

• EXCEPT: Returns results in first query that do not appear in second

• INTERSECT: Returns values that appear in both queries

Using SELECT with Other Commands

• SELECT can be value source in other statements

• Replacing values clause:INSERT EmpCopy SELECT SPNUM, SPNAME, SPFIRSTNAME FROM SALESPERSON

• Replacing scalar input values:UPDATE SALESPERSON SET COMMPERCT = (SELECT AVG(COMMPERCT) FROM SALESPERSON)

Joins and Subqueries

• Join: – Combines columns from two sources– Can combine with other joins to join

more than two sources

• Subquery: – Uses multiple SELECT statements in

single query– Nested query; can include several layers

of nestings, with innermost executed first

Joins• Two specifications must be made in SELECT

statement:1. Tables to join must be listed in FROM clause 2. Join attributes in tables must be declared and matched

• If same column name used in both tables, qualify column names with table name in SELECT statement

SELECT SPNAME FROM SALESPERSON, CUSTOMER WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM AND CUSTNUM=1525

• Declaring and using table alias:SELECT SPNAME FROM SALESPERSON s, CUSTOMER c WHERE s.SPNUM=c.SPNUM AND CUSTNUM=1525

Joins

• Joining three tablesSELECT PRODNAME FROM SALESPERSON, PRODUCT, SALES WHERE SALESPERSON.SPNUM=SALES.SPNUM AND SALES.PRODNUM=PRODUCT.PRODNUM AND SPNAME=’Adams’ AND QUANTITY>2000

• Types of Joins:– Inner join: Only qualifying results are returned – Outer join: Returns qualifying rows from one

table and all rows from other (outer) table– Cross join: Returns all possible rows, whether or

not they meet qualifying join logic, in every possible combination

Different Join Syntaxes

• Inner joinSELECT SPNAME, CUSTNAME FROM SALESPERSON JOIN CUSTOMER ON (SALESPERSON.SPNUM=CUSTOMER.SPNUM)

• Outer join– May be LEFT or RIGHT, returning unqualified results from

either table to left or right of join syntax

SELECT SPNAME, CUSTNAME FROM SALESPERSON LEFT OUTER JOIN CUSTOMER ON (SALESPERSON.SPNUM=CUSTOMER.SPNUM)

• Cross joinSELECT SPNAME, CUSTNAME FROM SALESPERSON CROSS JOIN CUSTOMER

Basic Subqueries• Subqueries:

– Nested queries using several levels of SELECT statements contained in pairs of parentheses

• Noncorrelated subquery:– Simplest type of subquery– Inner query does not depend on outer

query– Nested (inner) query is used to retrieve a

value that is passed up to outer query and used as input to SELECT statement

Basic Subqueries• Noncorrelated subquery (cont.):

– Used in qualifying logic in WHERE clause– Innermost SELECT statement executed

first; results are provided as input to SELECT statement at next level up

– Can be used as alternative to join

SELECT SPNAME FROM SALESPERSON WHERE SPNUM=(SELECT SPNUM FROM CUSTOMER WHERE CUSTNUM=1525)

Batches and Scripts• Batch: Set of SQL commands that run as

groupSELECT * INTO ECOPY FROM EMPLOYEESSELECT * FROM ECOPY

• Script: – Automates periodic procedures– May contain batches

USE GeneralHardwareGOCREATE TABLE EmpTest(SPNUM CHAR(3), SPNAME VARCHAR(20))GOINSERT EmpTest SELECT SPNUM, SPNAMEFROM SALESPERSON

Programming Concepts: Variables

• Variables: – Used to temporarily store value in memory– Include

• Identifier (unique name)– Local variable names begin with @ symbol

• Data type

• To create variable: DECLARE• To assign value: SET, SELECT

DECLARE @num intSET @num = 5

Programming Concepts: Variables

• Value may be static (constant) or expression

• Value must be compatible with data type

DECLARE @avgpct REAL SELECT @avgpct = (SELECT AVG(COMMPERCT) FROM SALESPERSON) UPDATE SALESPERSON SET COMMPERCT = @avgpct

Programming Concepts: Control Statements

• Allow batch to:– Make decisions based on values– Control order in which statements

are executed– Repeatedly loop through statements

• Common controls:– BEGIN and END– IF…ELSE

Programming Concepts: Control Statements

IF boolean_expression[BEGIN]statement_block[END][ELSE[BEGIN]statement_block[END]]

Summary• SELECT statement basic syntax: SELECT

column_list FROM source WITH search_conditions.

• Advanced SELECT syntax keywords: ALL , DISTINCT, TOP, INTO, JOIN, ORDER BY, GROUP BY, HAVING.

• SELECT can be used as value source for other statements.

• Comparison operators used to filter and modify results: AND, OR, BETWEEN, IN, and LIKE.

• Result sets can be limited to specified columns or rows, written to new tables, limited to unique values, ordered, organized, and grouped.

Summary• Operator precedence: Defines execution

order of operators.• Joins: Combine columns from two or more

tables.• Subqueries: Nested queries using several

levels of SELECT statements contained in pairs of parentheses.

• Batches: Used to execute two or more statements as group.

• Scripts: Used to automate procedures; may be constructed using variables and control statements.

Key Terms• AND• Bitwise operations• Boolean expression• Cartesian product• Control statement• Control-of-flow

statement• Correlated subquery• Cross join• Identifier• Inner join

• Join• Local variable• Noncorrelated subquery• OR• Outer join• Subquery• Table alias• Table order• User-defined function• Variable• XML document

fragment

Copyright NoticeCopyright 2008 John Wiley & Sons, Inc. All rights reserved. Reproduction or translation of this work beyond that permitted in section 117 of the 1976 United States Copyright Act without express permission of the copyright owner is unlawful. Requests for further information should be addressed to the Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up copies for his/her own use only and not for distribution or resale. The Publisher assumes no responsibility for errors, omissions, or damages caused by the use of these programs or from the use of the information herein.