Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

db1-vt14-SQL [part 2]

Database Design I - 1DL300; SQL
by

Sobhan Badiozamany

on 7 February 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of db1-vt14-SQL [part 2]

Queries
Data Manipulation Language
Nested queries
Aggregate functions
Set operations
"Simple" queries
Data Definition Language
DATABASE DESIGN I - 1DL300 spring 2014
Sobhan badiozamany
Department of Information Technology, Uppsala University

SQL
Elmasri/Navathe ch 4
Padron-McCarthy/Risch ch 7/8/9

The subset of SQL that is used in creating, changing and deleting the description (schema) of tables.
The subset of SQL used in adding, modifying and removing rows in the tables.
S
L
Q
tructured
uery
anguage
Note:
This is an introduction in form of a tutorial. For the course&labs more SQL knowledge is needed. Official slides from the book will be available at Studentportalen.

SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>]
Optional
Joining tables
Select * from city;
Shows the whole table, that is
all column
all rows
What if we are interested in a subset of attributes? i.e. some columns?
The simplest query
Select
Returns only name and state columns
Returns only the state column.
Some state names are repeated.
name, state
from
;
city
Select
state
from
;
city
state
from
;
city
Select
distinct
Removes the duplicates
What if we are interested in some rows?
Select
?
from
;
?
The "where" clause is used to specify the filtering conditions.
can be a combination of conditions using and/or constructs.
Retrieve name and population of all cities that are in state 'Mass' with a population above 2 million.
Where
?
and
?
SELECT <attribute list>
FROM <table>
[WHERE <condition>]
Queries that retrieve information from one table.
Sub-string comparison
The comparison operator is used to compare partial strings.
is part of the where clause
LIKE
Two reserved characters are used:
Retreive the name and population of the cities that rhyme with 'El Ceritto', i.e. end with the letter 'o'.
'%'
'_'
replaces a single arbitrary character.
replaces an arbitrary number of characters.
Example: retrieving first name and last name of all employees living in Houston. TX
Select
f_name,l_name
from
;
employee
Where
address
like
'%Houston,TX%'
Let's try Sub-string matching
Select
?,?
from
city
Where
?
Select
f_name , l_name
from
;
employee
Where
address
like
'%Houston,TX%'
The previous example was:
What if the query involves more than one table?
Retrieve store numbers together with their corresponding state.
here we need to "join" tables store and city.
Table A
Table B
Cartesian product
The Cartesian product of A and B
Join condition
A and B joined
Select *
from A , B
the comma produces the Cartesian product
Provides all possible combination of rows in the tables A and B.
Is a filter specified in the where clause that is applied to the Cartesian product of A and B.
There are explicit join operators in SQL..
Specifies what rows are "related".
Alias
[Foreign key]
Lets join store and city tables
First form the Cartesian product of store and city by:
Select * from store , city ;
What is the join condition?
In
most cases
, an equality on the foreign key to the key it is referencing.
Select
Number, state

from
store , city
where
city=name
;
Select *
from store , city
where city=name;
is equal to:
Select *
from store join city on city=name;
There is more into Join
Read from the book:
Left outer join
Right outer join
Full outer join (AKA cross join)
Aliases are needed for:
Readability and clear semantics
Resolving attribute ambiguity.
Having several instances of the same table/attribute.
Recall the country table we added to the database . . .
The above query is ambiguous since both tables city and country have an attribute called "name".
Try the following query to retrieve name of cities and their official language:
Select
name , language
From
city , country
Where
country=name;
Select
city.name
as
city_name , language
From
city,country c
Where
country=c.name;
Aliases are needed to join a table to itself
Retrieve manager name for all employees that have a manager.
Select
E.name , M.name
from
employee
E
, employee
M
where
C is an Alias for table country, city_name is an alias for city.name
When you need to combine information from more than one table.
Aggregate functions
some queries need statistics
What is the average salary of an employee?
How many managers are there in the company?
select avg(salary)
from employee;
select count(manager)
from employee;
select
count( distinct manager)
from employee;
returns 23! why?
Operate on the set of values of a column of a relation, and return a value:
avg : average value
min : minimum value
max : maximum value
sum : sum of values
count : number of values
In the previous example, they operated on the whole table, but ...
. . . but sometimes we want to break down statistics into groups:
group by
How many employees are managed by each manager?
Select
manager
, count(*)
from
employee
group by
manager
;
attributes list in the select list (except aggregate functions) should be always present in the group by.
More than one attribute can be specified in a group by clause, BUT:
Aggregate/group-by/join
Group by can be applied to any relation/table. It is very common that the relation itself is produced by joining some other relations.
How many cities exists per each language?
If we had the language column next to each city, we could group by language, and then apply the count() .
1. Write a join query that "brings in" the language into the city table;
2. Add the aggregate/group by clauses to answer the query.
for calculating statistics:
count,min,avg, ...
Usually applied to groups of rows
Many times together with join
There is a union operation (UNION), and in some versions of SQL there are set difference (MINUS) and intersection (INTERSECT) operations.
The set operations apply only to union compatible relations; the two relations must have the same attributes and the attributes must appear in the same order
The resulting relations of these set operations are sets of tuples; duplicate tuples are eliminated from the result
Set operations - union
list of the parts used in 'side table' or 'coffee table':
What is the difference between this and the following query:
Select
part_number
from
part_product
where

product_name='coffee table' or
product_name='side table'
;
Union removes duplicates
A join query can do this too. [home work: what would it be?].
Many queries can be expressed in several ways
Retrieve the name of English speaking cities.
Retrieve store numbers and their corresponding state.
Select
name
from
city
Where
country
in

;
(select
name
from
country
where
language='English'
)
A new note:
a nice interactive SQL tutorial:

http://adbc.kennesaw.edu/index.php?mainmenu=sql&submenu=interactive_sql
http://prezi.com/7o65gelhphrj/?utm_campaign=share&utm_medium=copy&rc=ex0share
Animated prezi available at
[Foreign key]
Employee E
Employee M
Foreign Key
Hint:
City table
Country table
1.Retrieve the set of countries where English is Spoken.
2. Retrieve the cities in those countries.
How many rows do we get?
Retrieve manager name for all employees that have a manager.
Let's assume we have a 'copy' of the employee table.
We just need to join these two 'copies' of the employee table.
E.manager = M.number;
What's the join condition?
(A query used as a table, inside another query)
part_product
(select
part_number
from
part_product
where
product_name='side table'
)
union
(select
part_number
from
part_product
where
product_name='coffee table'
);
Full transcript