Simple SQL Queries (2) Review SQL the structured query language - - PowerPoint PPT Presentation

Simple SQL Queries (2)

CMPT 354: Database I -- Simple SQL (2) 2

Review

• SQL – the structured query language for

relational databases

– DDL: data definition language – DML: data manipulation language

• Create and maintain tables

CMPT 354: Database I -- Simple SQL (2) 3

Retrieving Data from a Table

• What do you want to retrieve from a table?

– Some tuples (e.g., some customer records, some movies, …) – Some attributes about the tuples (e.g., the age, the title, …)

• Query results are still (conceptual) tables

– Query results can be used as the sources of

• ther queries

– A simple and consistent model of data processing

CMPT 354: Database I -- Simple SQL (2) 4

Queries and Results

Name Gender Occupation Age Arbor M Student 23 Bob M Teacher 34 Cindy F Student 18 Daisy F Lawyer 47 Eddy M Doctor 41 Frank M Student 19 Greg M Sales 27 Helen F Police 28 Jenny F Banker 46

CMPT 354: Database I -- Simple SQL (2) 5

Queries and Results

Name Gender Occupation Age Arbor M Student 23 Bob M Teacher 34 Cindy F Student 18 Daisy F Lawyer 47 Eddy M Doctor 41 Frank M Student 19 Greg M Sales 27 Helen F Police 28 Jenny F Banker 46

CMPT 354: Database I -- Simple SQL (2) 6

Queries and Results

Name Gender Occupation Age Arbor M Student 23 Bob M Teacher 34 Cindy F Student 18 Daisy F Lawyer 47 Eddy M Doctor 41 Frank M Student 19 Greg M Sales 27 Helen F Police 28 Jenny F Banker 46 Gender Age M 23 F 18 M 19

CMPT 354: Database I -- Simple SQL (2) 7

Query Specification

• Data sources
• Attributes required
• Tuples interesting
• The SELECT-FROM-WHERE structure in

SQL

– The idea has been borrowed by some other query languages, such as XQuery

CMPT 354: Database I -- Simple SQL (2) 8

Basic Query Structure

• A typical SQL query has the form

select A1, A2, ..., An from r1, r2, ..., rm where P

– Ai represents an attribute – Ri represents a relation – P is a predicate, filtering out unwanted tuples

• The result of an SQL query is a table

CMPT 354: Database I -- Simple SQL (2) 9

The SELECT Clause

• List the attributes desired in the result of a

query

• Example: find the names of all branches in

the loan relation: select branch_name from loan

• SQL names are case insensitive

– You may use upper- or lower-case letters

CMPT 354: Database I -- Simple SQL (2) 10

Duplicates

• SQL allows duplicates in relations and in query

results

• To force the elimination of duplicates, use the

keyword distinct or unique after keyword select

– UNIQUE is not supported in SQL Server 2005 – Find the names of all branches in the loan relations, and remove duplicates select distinct branch_name from loan

• The keyword all specifies that duplicates are not

be removed

select all branch_name from loan

CMPT 354: Database I -- Simple SQL (2) 11

Selecting All Attributes and More

• An asterisk in the select clause

select * from loan

• Arithmetic expressions involving the
• peration, +, –, , and /, and operating on

constants or attributes of tuples

select loan_number, branch_name, amount * 100 from loan

CMPT 354: Database I -- Simple SQL (2) 12

The WHERE Clause

• Specify conditions that the result tuples

must satisfy

select loan_number from loan where branch_name = ‘ Perryridge’ and amount > 1200

• Comparison results can be combined using

the logical connectives and, or, and not

• Comparisons can be applied to results of

arithmetic expressions

CMPT 354: Database I -- Simple SQL (2) 13

Predicate Between

• Find the loan number of those loans with

loan amounts between $90,000 and $100,000

select loan_number from loan where amount between 90000 and 100000

CMPT 354: Database I -- Simple SQL (2) 14

The FROM Clause

• List the relations involved in the query
• Find the name, loan number and loan amount of

all customers having a loan at the Perryridge branch

select customer_name, borrower.loan_number, amount from borrower, loan where borrower.loan_number = loan.loan_number and branch_name = ‘Perryridge’ – Schema

• borrower (customer_name, loan_number)
• loan (loan_number, branch_name, amount)

CMPT 354: Database I -- Simple SQL (2) 15

The Rename Operation

• Renaming relations and attributes using the as

clause

• ld-name as new-name
• Find the name, loan number and loan amount of

all customers; rename the column name loan_number as loan_id

select customer_name, borrower.loan_number as loan_id, amount from borrower, loan where borrower.loan_number = loan.loan_number

CMPT 354: Database I -- Simple SQL (2) 16

Tuple Variables

• Defined in the from clause via the use of the

as clause

• Find the customer names and their loan

numbers for all customers having a loan at some branch

select customer_name,T.loan_number,S.amount from borrower as T, loan as S where T.loan_number = S.loan_number – from borrower as T, loan as S can be written as from borrower T, loan S in SQL Server

CMPT 354: Database I -- Simple SQL (2) 17

Using One Table Twice in a Query

• Find the names of all branches that have

greater assets than some branch located in Brooklyn

select distinct T.branch_name from branch as T, branch as S where T.assets > S.assets and S.branch_city = ‘ Brooklyn’

CMPT 354: Database I -- Simple SQL (2) 18

String Operations

• The operator “like” uses patterns that are

described using two special characters

– The % character matches any substring – The _ character matches any character

• Find the names of all customers whose street

includes the substring “Main” select customer_name from customer where customer_street like ‘%Main%’

• How to match the name “Main%”?

like ‘Main\%’ escape ‘\’

CMPT 354: Database I -- Simple SQL (2) 19

More on String Operations

• SQL supports a variety of string
• perations such as

–Concatenation (using “||”) –Converting from upper to lower case (and vice versa) –Finding string length, extracting substrings, etc.

• Check them out by yourself

CMPT 354: Database I -- Simple SQL (2) 20

Ordering the Display of Tuples

• List in alphabetic order the names of all customers

having a loan in Perryridge branch

select distinct customer_name from borrower, loan where borrower loan_number = loan.loan_number and branch_name = ‘Perryridge’

• rder by customer_name
• desc for descending order or asc for ascending
• rder, for each attribute

– Ascending order is the default. – Example: order by customer_name desc

CMPT 354: Database I -- Simple SQL (2) 21

Set Operations

• The set operations union, intersect, and except
• perate on relations
• Each of the above operations automatically

eliminates duplicates

• To retain all duplicates use union all, intersect all

and except all

– Suppose a tuple occurs m times in r and n times in s – m + n times in r union all s – min(m,n) times in r intersect all s – max(0, m – n) times in r except all s

CMPT 354: Database I -- Simple SQL (2) 22

Set Operations – Examples

• Find all customers who have a loan, an account, or both

(select customer_name from depositor) union (select customer_name from borrower)

• Find all customers who have both a loan and an account

(select customer_name from depositor) intersect (select customer_name from borrower)

• Find all customers who have an account but no loan

(select customer_name from depositor) except (select customer_name from borrower)

CMPT 354: Database I -- Simple SQL (2) 23

Basic Aggregate Functions

• avg: average value
• min: minimum value
• max: maximum value
• sum: sum of values
• count: number of values

CMPT 354: Database I -- Simple SQL (2) 24

Aggregate Functions – Examples

• Find the average account balance at the

Perryridge branch

select avg (balance) from account where branch_name = ‘Perryridge’

• Find the number of tuples in the customer relation

select count (*) from customer

• Find the number of depositors in the bank

select count (distinct customer_name) from depositor

CMPT 354: Database I -- Simple SQL (2) 25

Group By

• Apply an aggregate function to groups of tuples

– Each group returns an aggregate value

• Find the number of depositors for each branch

select branch_name, count (distinct customer_name) from depositor, account where depositor.account_number = account.account_number group by branch_name

• Attributes in select clause outside of aggregate

functions must appear in the group by list

CMPT 354: Database I -- Simple SQL (2) 26

Having Clause – Constraints on Groups

• Selecting groups
• Find the names of all branches where the average

account balance is more than $1,200

select branch_name, avg (balance) from account group by branch_name having avg (balance) > 1200

• Predicates in the having clause are applied after

the formation of groups

– Predicates in the where clause are applied before forming groups

CMPT 354: Database I -- Simple SQL (2) 27

Null Values

• Predicate “is null” can be used to check for

null values

– Find all loan number which appear in the loan relation with null values for amount

select loan_number from loan where amount is null

• The result of any arithmetic expression

involving null is null

– Example: 5 + null returns null

CMPT 354: Database I -- Simple SQL (2) 28

Null Values and 3 Valued Logic

• Any comparison with null returns unknown

– Example: 5 < null, null <> null, null = null

• Three-valued logic using the truth value unknown

– OR: (unknown or true) = true, (unknown or false) = unknown, (unknown or unknown) = unknown – AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown – NOT: (not unknown) = unknown – “P is unknown” evaluates to true if predicate P evaluates to unknown

• Result of where clause predicate is treated as false if it

evaluates to unknown

CMPT 354: Database I -- Simple SQL (2) 29

Null Values and Aggregates

• Total all loan amounts

select sum (amount ) from loan

– Ignore null amounts – Result is null if there is no non-null amount

• All aggregate operations except count(*) ignore

tuples with null values on the aggregated attributes

– Count(*) counts the number of tuples, including those of null values – Count(amount) counts the number of tuples of non-null values on amount

CMPT 354: Database I -- Simple SQL (2) 30

Summary

• Basic SELECT-FROM-WHERE structure
• Selecting from multiple tables
• String operations
• Set operations
• Aggregate functions and group-by queries
• Null values

CMPT 354: Database I -- Simple SQL (2) 31

To-Do List

• Check out the string operations available in

SQL Server 2005

• Use the pubs database, write the following

queries, and run them on SQL Server

– List all titles and the number of authors of each title – For each author, find the most expensive title of the author – Find all cities and states where there is an author or a publisher