6 chapter 6 structured query language (sql). 6 2 sample database for the following examples...
TRANSCRIPT
6
Chapter 6
Structured Query Language (SQL)
2
6
Sample Database for the following examples
• Publishing company has three subsidiary publishers.
• Need to keep info about books, authors, editors, & publishers.
• Assume:– an author may write more than one book– a book may have more than one author– each book has one publisher– each book has one editor (may need to revise this
assumption)– each editor may edit more than one book.
3
6BookBiz Database
title_idtitlepricetypepubdateytd_salesadvancepub_ided_id
au_idau_lnameau_fnamephoneaddresscitystatezip
pub_idpub_nameaddresscitystatezip
authors publisherstitles
ed_ided_lnameed_fnamephoneaddresscitystatezip
editors
titleauthors
title_idau_idau_ordroyaltyshare
4
6SELECT Queries
SELECT [ALL| DISTINCT] Select_list
FROM Table_list
[WHERE Search_conditions]
[GROUP BY Group_by_list]
[HAVING Search_conditions]
[ORDER BY Order_by_list]
• Order of clauses is important• keywords are case insensitive. Everything else is (depends on
system)• Attribute and table names may have to be qualified:
owner.database.table.attribute
5
6
Eliminating Duplicate Row: DISTINCT
SELECT [ALL| DISTINCT] Desired Attributes
……• ALL returns all qualified rows (default)• DISTINCT returns only unique rows
Example:
Select title Select DISTINCT title
from titles from titles
order by title order by title
6
6
WHERE Clause: Search Conditions
• Relational operators• Logical: AND, OR, NOT
• Special– BETWEEN - defines limits– IN - checks for value in a set– LIKE - checks for similar string– IS NULL - checks for nulls– EXISTS - used with subqueries
SELECT *FROM PublishersWHERE zip = 77002 OR V_CODE = 77077;
7
6
BetweenSelect title_id, ytd_sales
From titles
Where ytd_slates BETWEEN 4000 AND 12000;
Select title_id, ytd_sales
From titles
Where ytd_slates NOT (BETWEEN 4000 AND 12000);
8
6
IN
• List all authors who reside in the states of CA, TX or NY
Select au_lname
From authors
where state in ('CA', 'TX', 'NY');
• List must be in parentheses• list elements separated by commas
9
6
IS [NOT] NULL
• Null is a place holder for unknown info.
Select title_id, advance
from titles
where advance is not null
10
6
LIKE
• Match for embedded pattern in string• Syntax:
WHERE attribute [NOT] LIKE 'pattern' • pattern must include a wildcard:
• % matches a string of zero or more chars
• - matches a single char
E.g. find city of some author whose last name starts with 'Mc' or 'Mac'
E.g. list all editors in the 713 area code
11
6
ORDER BY ClauseSELECT Select_List
FROMTable_list
[WHERE Search_Conditions]
[ORDER BY Order_by_list]
• Order by allows you to sort results by any expression or mixture of expressions from the select_list
12
6
ORDER BY Clause• Ordering is ascending, unless you specify the
DESC keyword.• Ties are broken by the second attribute on the
ORDERBY list, etc.• Generally, items in orderby_list must also appear
in select_list– attribute name
– arithmetic expr of attributes
– display label (alias)
– a position number in select_list
13
6
ORDER BY Clause
Select title_id, pub_id, price*ytd_sales as income
From titles
Where type in (‘business’ , ‘psychology’)
Order by pub_id, income desc
14
6
ORDER BY Clause
Select title_id, pub_id, price*ytd_sales as income
From titles
Where type in (‘business’ , ‘psychology’)
Order by pub_id, 3 desc
15
6
Aggregate Functions
• Aggregate functions are used to get summary values: sum, avg, min, max, count
• aggregates are applied to sets of rows– to all the rows in a table
– to those rows specified in WHERE clause
– to groups of rows set up by the GROUP BY clause
• Outcome is always a single value for each set of rows
16
6Aggregate Syntax
aggregate_function([DISTINCT] expression)
• sum( [Distinct] expression)
• avg ( [Distinct] expression)
• count ([Distinct] expression)
• count(*) //count number of selected rows
• Max (expression)
• Min (expression)
• All aggregate functions, except count(*), ignore rows where expression is null
17
6
Aggregate fiunctions
• Compute total ytd sales for all books
Select sum(ytd_sales)
From titles
Note:
Select price, sum(price)
From titles
NOT ALLOWED
18
6
Aggregate fiunctions
• Compute total ytd sales for all books
Select sum(ytd_sales) as Total
From titles
Total
87654
19
6
Aggregate fiunctions• Total number of books:
Select count(title_id)From titles
• May use any other attribute:Select count(price)From titles
• Same results as long as there are no NULLSelect count(price), count(*)From titles
Count distinct values?Select count(DISTINCT price)from titles
20
6
Aggregate Functions--Count
• Count(expression)– expression is generally a column name
– returns the number of non-null occurrences of the expression
• Count(distinct expression)– Each occurrence is counted only once
• Count(*)– counts numbers of rows (includes NULLs)
21
6GROUP BY Clause
SELECT [ALL| DISTINCT] Select_list
FROM Table_list
[WHERE Search_conditions]
[GROUP BY Group_by_list]
[HAVING Search_conditions]
[ORDER BY Order_by_list]
22
6
Grouping Data & Aggregates
• The Group by clause partitions a table into sets of rows
• Aggregate functions (if any) produce summary values for each set ==> vector aggregates
• Having clause puts conditions on the group by results much like the Where clause qualifies the individual rows
23
6
Grouping Data & Aggregates
• How many books each publisher has?
Select pub_id , count(title_id) as count
from titles
group by pub_id
pub_id count
1380 6
2733 17
1322 21
24
6Groups within groups
Select pub_id , type, count(title_id) as count
from titles
group by pub_id, typepub_id type count
1380 math 2
1380 cs 4
2733 math 5
2733 Business 12
1322 cs 5
1322 biology 3
1322 physics 6
• Divide the rows in the table by publishers
• Separate the rows in each publisher group by type
• Apply aggregate function(s) to each set
25
6
Avoid confusing queries•
Select pub_id, type , count(title_id) as count
from titles
group by pub_id
pub_id type count
1380 math 6
1380 cs 6
2733 math 17
2733 Business 17
1322 cs 14
1322 biology 14
1322 physics 14
26
6
Groups• All NULLs are include in same group
• Order_by clause always goes after Group_by• E.g. list avg price for each book type in ascend order.
Include only books with more than $500 advance
Select type, avg(price) as avg_price
from titles
where advance > 500
group by type
order by avg_price
27
6
Having Clause
• Puts conditions on the group_by results I.e. on groups formed by the group_by
• Having conditions must be either– from select_list
– aggregate function
Order of execution:
1. Where clause selects rows
2. Group_by partitions resulting rows from (1)
3. Aggregates on groups are calculated
4. Having clause checks results of group rows to see which qualify for output.
28
6Having Clause
• Get number of books and average price of each book type whose average price is above $50
Select type, count(*), avg(price)
from titles
group by type
having avg(price) > 50
29
6
Where vs Having
Select type, count(*), avg(price)
from titles
where price > 50
group by type
• Get number of books and average price of each book type. Include only books whose price is above $50
30
6
Where vs Having
• Can't use aggregates in WHERE clause• WHERE clause applies to individual rows• HAVING clause applies to groups of rows
31
6
Where vs Having
Select type, count(*)
from titles
group by type
having type like 'B%'
Equivalent to:
Select type, count(*)
from titles
where type like 'B%'
group by type
32
6BookBiz Database
title_idtitlepricetypepubdateytd_salesadvancepub_ided_id
au_idau_lnameau_fnamephoneaddresscitystatezip
pub_idpub_nameaddresscitystatezip
authors publisherstitles
ed_ided_lnameed_fnamephoneaddresscitystatezip
editors
titleauthors
title_idau_idau_ordroyaltyshare
SalesSonumstore_idsdate
Sonum
title_id
qty_ordered
qty_shipped
date_shipped
Salesline
33
6
Joining Tables
• The join operation lets you retrieve data from more than one table in a SELECT statement.
• Joins are needed because during the normalization process, data is split in multiple tables to minimize redundancy.
• There can be more than one join op in a SELECT statement
• Each join operation is done on two tables, using one column from each table as a connecting column
• Join column should have compatible data types
34
6
Joining Tables--Example
• List the names of the authors of the book titled “Database Systems”
SELECT au_lname, au_fname
FROM authors a, titles t, titleauthors tl
WHERE t.title_id = tl.title_id AND
tl.au_id = a.au_id AND
t.title = ‘Database Systems’ ;
35
6
Joining Tables – SYNTAX-1SelectSelect_listFrom table-1, table_2 [, table_3,…]Where [table_1.]Col join_op [table_2.]Col
• Tables are joined pairwise• join_op: a relational operator• Specify a join on two tables using a column from
each table as a connecting column.• connecting columns must be join compatible.
Best if they have same data type.• Connecting columns DO NOT have to have the
same name.
36
6
Joining Tables• Most often: connecting column is a primary key
of one table and foreign key of the second table.
• Example: Find the name of the editor of book title "Database Systems"Select ed_fname, ed_lname
From editors e, titles t
Where t.ed_id = e.ed_id
AND t.title =‘Database Systems’ ;
37
6
Joining Tables – SYNTAX-2SELECT Select_list
FROM table-1 [NATURAL|CROSS]JOIN table_2
[ON [table_1.]Col join_op [table_2].Col]
| USING (column) ]
• join_op: a relational operator• If keywords NATURAL or CROSS are used, the ON and
USING clauses are not allowed• NATURAL JOIN works only when the two tables have a
single identical column. The join is done on this column• CROSS JOIN produces the Cartesian product need to
use a WHERE clause with it.
38
6
Joining Tables• Example: Find the name of the editor of book title "Database
Systems“
SELECT ed_fname, ed_lnameFROM editors JOIN titles ON editors.ed_id = titles.ed_idWHERE title.title ='Database Systems’
SELECT ed_fname, ed_lnameFROM editors NATURAL JOIN titlesWHERE title.title = ‘Database Systems’
SELECT ed_fname, ed_lnameFROM editors CROSS JOIN titles WHERE editors.ed_id = titles.ed_id
AND title.title ='Database Systems’
39
6
Joining Tables
• e.g. find out if any editor is also an author
select ed_lname, ed_fname
from editors, authors
where editors.ed_id = authors.au_id
Note: • if connecting columns have NULLs, the NULLs
won't join: NULLs are not equal to each other
40
6
Joining Tables--Examples
• Find all authors who live in same city as any publisher
• Select a.au_lname, a.city, p.pub_name
From authors a, publishers p
where a.city = p.city
41
6
Joining more than two tables
• example: find titles of all business books and the names of their authors
Select au_lname, au_fname, title
from authors a, titles b, tiltesauthors ba
where a.au_id = ba.au_id
and b.title_id = ba.title_id
and b.type = 'business'
43
6
Self_joins
• Find authors who live in same city as "Steven King"
Selectau_lname, au_fname, city
from authors a1, authors a2
where a1.city = a2.city
and a2.au_lname = 'King'
and a2.au_fname = 'Steven'
44
6
Non-equijoins
• Equijoin: join on matching (equal) values
• Join can be done on other relational operations:>
>=
<=
<=
<>
45
6
Joins not based on equality
• Example: list all authors with same last name
Select a1.au_id, a1.au_fname, a1.au_lname
from authors a1, authors a2
where a1.au_lname = a2.au_lname
and a1.au_id <> a2.au_id
46
6
Joins not based on equality
• Find all sales orders that were on date later than the sales date (company tries to ship same day) and the store id and date they were sold at.
SELECT DISTINCT s.sonum, s.store_id, s.sdate
FROM sales s, salesline sl
WHERE s.sdate < sl.date_shipped
AND s.sonum = sl.sonum;
47
6Subqueries
A subquery is a SELECT statement that nests inside the WHERE or HAVING clause of another SELECT, INSERT, UPDATE or DELETE statement
SELECT [DISTINCT] Select_list
FROM Table_list
WHERE
{expression [not] IN | comparison_op [ANY|ALL] | EXISTS}
(SELECT [DISTINCT] Select_list
FROM Table_list
WHERE Conditions )
[GROUP BY Group_by_list]
[HAVING Search_conditions]
[ORDER BY Order_by_list]
48
6
Simple Subqueries• Find names of publishers who publish CS books
Select distinct pub_name
from publishers
where pub_id IN
(Select pub_id
from titles
where type = 'CS')• Inner and outer queries are evaluated in two steps. • First the inner subquery is evaluated. Then the
result (a set of pub_ids) are passed to outer subquery.
49
6
Subqueries & Joins
Select distinct pub_name
from publishers p, titles b
where p.pub_id = b.pub_id
and type = 'CS'
• Subqueries and joins can often be used interchangeably
• In some cases, one is preferred to other
50
6
Subqueries & Joins
• Find all books with prices equal to the minimum book price.
• Subquery:select title, pricefrom titleswhere price = ( select min (price) from titles)
• Can NOT be done with a join.
51
6
Subqueries & Joins
• Find name of the authors whose address is in the same city as the home office of a publisher (list pub_name, authors name & city)
• Join:
select pub_name, au_fname, au_lname, authors.city
From publishers, authors
where publishers.city = authors.city
52
6
Subqueries & Joins
• Subquery
select pub_name
From publishers
where city in
( Select city from authors)
• Can only list information from outer query, NOT from both
53
6
Subqueries & Joins: Which is better?
• Use subqueries when you need to compare aggregates to other values.
• Use joins when you need to display results from multiple tables.
• Otherwise, you may use either.
54
6
Subqueries & Joins
• Find the names of all second authors who live in California and receive less than 30% of the royalties on the books they coauthor
select au_lname, au_fname
from authors
where state = 'CA'
and au_id IN
( select au_id
from bookauthors
where au_ord = 2 and royaltyshare < 0.30)
55
6
Subqueries & Joins
select au_lname, au_fname
from authors a, bookauthors ba
where state = 'CA'
and a.au_id = ba.au_id
and ba.au_ord = 2 and ba.royaltyshare < 0.30)
56
6
Self_join
• Find authors who live in same city as "Steven King"
Select au_lname, au_fname, cityfrom authors
where city IN
(select city
from authors
where au_fname='Steven'
and au_lname='King')
57
6Correlated Subqueries
• Find names of publishers who publish CS books
Select distinct pub_name
from publishers
where pub_id IN
(Select pub_id
from titles
where type = 'CS')
58
6Correlated Subquery
• Find names of publishers who publish CS books
Select distinct pub_name
from publishers p
where exists
(Select *
from titles t
where t.pub_id = p.pub_id
and t.type = 'CS')• Inner query needs p.pub_id values from outer
query, and passes results to outer query.• The exists operator succeeds if the result of the
inner query is non-empty.
59
6Correlated Subquery Processing
1. Outer query finds the first name in the publishers table and its id: p.pub_id
2. The inner query looks up the titles table to find rows whose tiltes.pub_id = p.pub_id and titles.type = 'CS'
3. The result is returned to the outer query.
4. The exists operator of the outer query check for existence: if result is not empty set, outer query include the p.pub_id of step 1 in it final result
5. Steps 1-4 are repeated for each pub_id in publishers table.
60
6Subquery Rules
1. The SELECT list of an inner subquery introduced with a comparison operator or IN can include only one expression or column. The column you name in the WHERE clause of the outer query must be join-compatible with the column you name in the subquery SELECT list.
Select pub_name
from publishers
where pub_id IN
(Select pub_id
from titles
where type = 'CS')
61
6
Sub-query Rules Cont.2. Sub-queries can not include the ORDER BY
clause
3. The SELECT list if a subquery introduced with the EXISTS operator always consists of the (*)
Select pub_name
from publishers p
where exists
(Select *
from titles t
where t.pub_id = p.pub_id
and t.type = 'CS')
62
6
Subquery Rules Cont.Three types of subqueries:
1. Subqueries that return 0 or any number of items: Use IN operator, or comparison modified by ALL or ANY
2. Subqueries that return a single value: use unmodified comparison
3. Subqueries that are an existence test: [NOT]EXISTS operator
• The EXISTS operator is generally used with correlated queries.
63
6
Subqueries: IN & NOT IN
• Find names of publishers who have NOT published CS books
Select pub_name
from publishers
where pub_id NOT IN
(select pub_id
from titles
where type = 'CS')
64
6
Comparisons with ALL and ANY
• > ALL ==> larger than the max• > ANY ==> larger than the min• < ALL ==> less than the min• < ANY ==> less than the max• = ALL ==> equal to each (all at same time)• = ANY ==> equal to at least one (same as IN op)
65
6
Comparisons with ALL and ANY
• Which books commanded an advance greater than any book published by Pub1 Inc.
select title
from titles
where advance > all
(select advance
from publishers p, titles b
where p.pub_name = 'Pub1 Inc.'
and b.pub_id = p.pub_id)
66
6
Comparisons with ALL and ANY
• Which books commanded an advance greater than the minimum advance amount paid published by Pub1 Inc.
select title
from books
where advance > ANY
(select advance
from publishers p, titles b
where p.pub_name = 'Pub1 Inc.'
and b.pub_id = p.pub_id)
67
6Comparisons with ALL and ANY• Find authors who live in same city as some
publisher
1) Select au_lname, au_fname, city
from authors
where city IN (select city
from publishers)
order by city;
2) Select au_lname, au_fname, city
from authors
where city =ANY (select city
from publishers)
order by city;
68
6Unmodified Comparisons
• Unmodified comparisons return a single value
Select au_lname, au_fname
from authors
where city =
(select city
from publishers
where pub_name = 'Prentice Hall')• Finds names of authors who live in same city
where Prentice Hall is located.• Assumes each publisher is located in one city.• Note: Subquery must return a single value.
Otherwise, error.
69
6Unmodified Comparisons
• Usually used with aggregate function because they are guaranteed to return a single value.
Example: Find names of all books with prices higher than the average price
select title
from titles
where price >
(select avg(price)
from titles)
70
6
Unmodified Comparisons• Comparison subqueries can not include GROUP
BY and HAVING clause.
E.g.
select title
from titles
where price >
(select min(price)
from titles
group by type )• generates an error
71
6Correlated Subqueries with comparisons
• Find titles whose price is greater than the average price for books of its type
Select t1.type t1.title
from titles t1
where t1.price > (Select avg(t2.price)
from titles t2
where t2.type = t1.type )• For each row of t1, the system evaluates the
subquery and includes the row in the final result if the price value in that row is greater than the calculated average.
72
6
Multi-Level Subqueries• Find the names of authors who have participated
in writing at least one CS book.
Select au_lname, au_fname
from authors a, titles t, titleauthors ta
where a.au_id = ta.au_id AND
t.title_id = ta.title_id AND
t.type ='CS'
73
6
Multi-Level Subqueries• Find the names of authors who have participated in
writing at least one CS book.
Select au_lname, au_fname
From authors
Where au_id IN
(Select au_id
From titleauthors
Where title_id IN
(Select title_id
From titles
Where type= 'CS' ) )
74
6
Subqueries in the FROM Clause
• Get number of sales orders for each store
Select store_id, count(sonum)
From sales
group by store_id• Get store_id and the number of sales orders of
stores with more than 10 sales orders.
Select store_id, count(sonum)
From sales
group by store_id
having count(sonum) > 10;
75
6
Subqueries in the FROM Clause CNTD
• Get store_id and number of sales orders for the store with highest sales order
Select store_id, count(sonum)
From sales
group by store_id
having count(sonum) = max(count(sonum))• Can't nest aggregates !
76
6
Subqueries in the FROM Clause• Create a subquery in the outer FROM to define a
subset of data (called a view) with a single column consisting of sales order count for each store
• alias the view• use the view in a select statement
Select store_id, max(s.num)
from ( select store_id, count(sonum) as num
from sales
group by store_id) s
78
6
Creating and Using Views
• A view creates a virtual table from a SELECT statement.
• Think of it as a movable window through which users can see data.
• Definition of a view is stored in the system catalog.
• Views don’t exist as independent entities in the Database as “real” tables do.
• create views using CREATE VIEW• delete views using DROP VIEW
79
6
Creating and Using Views
CREATE VIEW view_name [ zero or more columns]
AS
SELECT_Statement
example: create a view that displays names of authors who live in Houston, Texas
80
6
Creating and Using Views
create view houstonians (FirstName, LastName, Book)
as
select au_fname, au_lname, title
from authors a, titles t, titleauthors ta
where a.au_id = ta.au_id
and t.title_id = ta.au_id
and a.city = 'Houston'
and a.state = 'Texas'
81
6
Displaying Data thru the view
Select *
from houstonians
• Creating a view does not create any tables. It
simply stores the view's name and definition in the database catalog
• when the view is used, its select statement is retrieved from the catalog and executed.
82
6
Displaying Data thru the view
• a view in a select statement works just like a table
• Select Book as Title, Lastname as Surname
from houstonians
order by Title
• Select LastName, count(Book)
from houstonians
group by LastName
having count(Book) > 5
83
6
Advantages of views
1. Once a view is created ==> less typing and less errors due to typos
example: a salesman need info on which author wrote which book and their order
create view books_SalesmanVersion
as
select titles.title_id, au_ord, au_lname, au_fname
from authors a, titles t, titleauthors ta
where a.au_id = ta.au_id
and t.title_id = ta.book_id
and b.type = 'business'
84
6
Advantages of views
• Once the view is created, no need to retype the whole select query.
• E.g. select *
from books_SalesmanVersion
etc.
85
6
Advantages of views. Views can be used to provide users with access to only a
subset of whole data.
E.g. an executive may only be interested on knowing how the different categories of books are doing at each subsidiary.
Create view currentinfo (Pub#, Type, Income, Avg_Price, Avg_sales)
as
Select pub_id, type, sum(price*ytd_saels), avg(price), avg(ytd_sales)
From titles
Group by pub_id, type
86
6
Advantages of views
Limiting user access with views may be needed:2. Customization: to help users focus on only the
data they need
3. Independence: separating the users view of the data from actual structure of the database. If the structure of the database is changed, users don't even notice it. They still have the same view
1. Security: different users with access to different subsets of the data
87
6
Naming view columns
1. Create view books1 (ID, Ord, Surname)
as
select title_id, au_ord, au_lname
from titles
//need a name for each item in select_list
2. Create view books2
as
select title_id as ID, au_ord, au_lname as Surname
from titles
88
6
Naming view columns
View columns MUST be named if• view's column is a complex expression
• the view winds up with more than one column with same name. This occurs if there is join operation in the Select statement, the joined tables have columns of same names.
89
6
Naming view columnsCreate a view that shows how the different
categories of books are doing at each subsidiary.
Create view currentinfo (Pub#, Type, Income, Avg_Price, Avg_sales)
as
Select pub_id, type, sum(price*ytd_saels), avg(price), avg(ytd_sales)
from books
group by pub_id, type
90
6
Naming view columns
Create view cities (Author, AuthorCity, Pub, PubCity)
as
Select au_lname, authors.city, pub_name, publishers.city
from authors, publishers
where authors.city = pubslishers.city
91
6
Deriving Views from Views
RECALL:
Create view currentinfo (Pub#, Type, Income, Avg_Price, Avg_sales)
as
Select pub_id, type, sum(price*ytd_saels), avg(price), avg(ytd_sales)
from books
group by pub_id, type
92
6
Deriving Views from Views
• Need to view same info., but only for books of type CS
create view CS_currentinfo
as
select *
from currentinfo
where type = 'CS'
93
6
Modifying Views• Syntax is the same as for modifying tables• E.g.
Update Houstonians
Set FirstName = ‘Sylvia’
Where FirstName = ‘Stan’;
• Changing data through views is a thorny issue.• Problem: command to change data thu view are
sometimes ambiguous.
94
6
Modifying Views– Rules according to ANSI
• View are read-only if the CREATE VIEW statement contains one of the following:– DISTINCT in the select statement
– Expressions in the select list (computed column, aggregates, etc)
– References to more than one table– in the FROM clause, a subquery, or a Union
– A Group By or HAVING clause
– References to a view that is itself not updatable.
95
6
UNION, INTERSECT & DIFFERENCE
• UNION:– Get a list of all authors and editors
(Select au_lname as lname, au_fname as fname
from authors)
UNION
(Select ed_lname as lname, ed_fname as fname
from editors)
96
6
UNION, INTERSECT & DIFFERENCE
• INTERSECTION– Get a list of all authors who are also editors
(Select au_lname as lname, au_fname as fname
from authors)
INTERSECT
(Select ed_lname as lname, ed_fname as fname
from editors)
98
6
DATA MODIFICATION
• INSERT: add new rows to a table• UPDATE: change existing rows• DELETE: delete existing rows
99
6
INSERTTwo ways:
– With VALUES keyword
– With SELECT statement
INSERT with VALUES:
INSERT INTO table_name
[(column1, column2, …)]
VALUES [(constant1, constant2, …)]
100
6
INSERT with VALUES:
Insert into publishers
Values (‘1622’, ‘UHD Press’, ‘1 Main Str.’, ‘Houston, ‘TX’);
Insert into publishers (pub_id, pub_name)
Values (‘1777’, ‘Prentice Hall’);
• All other fields are Null• Can’t insert same entity more than once
101
6
Insert with Select• Used if you need to insert values generated by a
query
Insert into table_name [(insert_column_list)]
Select column_list
From table_list
Where search_conditions
102
6
Insert with Select• Suppose a table called newauthors exists with
same format as authors
Insert into newauthorsSelect au_id, au_lname, au_fname, phone, address,
city, state, zipFrom authors
ORInsert into newauthorsSelect *
From authors
103
6
Insert with Select• Insert into books
Select title_id, title, type, pub_id, price*1.5, advance, pubdate, ytd_sales, ed_id
From titles;
104
6
Update
Update table_name
Set column_name = expression
[Where search_condition];
Update publishers
Set city = ‘Atlanta’, state = ‘CA’
Changes city and state of all pubs
105
6
UpdateUpdate publishers
Set city = ‘Atlanta’, state = ‘CA’
Where pub_id = ‘1777’;
Update tiltles
Set price = price/2;
106
6
Delete
Delete from table_name
Where search_conditions
Delete from publishers
Where pub_id = ‘1622’ or pub_id=‘1777’;
Update publishers
Set city = ‘Atlanta’, state = ‘CA’
107
6
DATA DEFINITIONCREATE TABLE table_name ( column_name datatype [NULL| NOT NULL] [ , column_name datatype [NULL| NOT NULL]]…)
CREATE table authors ( au_id char(11) not null,
au_lname varchar(40) not null,au_fname varchar(20) not null,phone char(12) null,address varchar(40) null,city varchar(20) null,state char(2) null,zip char(5) null)
108
6
Data Types• Character data types
– Fixed length characters: char(n)– Variable length characters: varchar(n)
• Bit strings– Bit(n)– Bit varying(n)
• Boolean– True , false or unknown
• Integers– Int or integer– Shortint
109
6
Data Types cntd• Floating point numbers:
– FLOAT or REAL
– DOUBLE for higher precision
– DECIMAL(n,d)• n: total number of digits• d: number of digits to the right of decimal point
• DATE and TIME:– DATETIME
• MONEY • BINARY
110
6
Deleting a Table
• DROP TABLE table_name
• DROP DATABASE database_name
111
6
Modifying Relation Schemas
ALTER TABLE table_nameADD/DROP/MODIFY column_name
ALTER TABLE authorsADD birth_date datetime null
ALTER TABLE authorsMODIFY birth_date char(10)
ALTER TABLE authorsDROP birth_date
112
6
Default Values
The default of defaults: NULL
Specifying default values:
CREATE TABLE Person(
name VARCHAR(30) not null, social-security-number CHAR(11) not null, age SHORTINT DEFAULT 100 null, city VARCHAR(30) DEFAULT “Houston” null, gender CHAR(1) DEFAULT “?”, Birthdate DATE null,
113
6
Indexes
REALLY important to speed up query processing time.
Suppose we have a relation
Person (name, social security number, age, city)
An index on “social security number” enables us to fetch a tuple for a given ssn very efficiently (not have to scan the whole table).
The problem of deciding which indexes to put on the relations isvery hard! (it’s called: physical database design).
114
6
Indexes
• Think of each table as being stored in several disk sectors, possibly on more than one disk.
• Searching for a tuple based on some attribute requires scanning the entire table (I.e. all sectors and possibly several disks where table is stored).
• An index is like the index of a book. It’s a lookup table gives the physical location of a tuples based on the values an indexed attribute(s).
115
6
Creating Indexes
CREATE INDEX ssnIndex ON Person(social-security-number)
Indexes can be created on more than one attribute:
CREATE INDEX doubleindex ON Person (name, social-security-number)
Why not create indexes on everything?
116
6
Indexes• Mechanism for user/application to get improved
performance from DBMS. It speeds up retrieval of data from large databases
• Somewhat similar to a book index: when looking for a particular subject to read, you don’t want to read every page to find the topic of interest.
• An index on a column allows the system to match values from that column without having to scan through all storage. An index is basically a table that provides logical pointers to the physical location of the data.
117
6
Indexes
• Index on attribute R.A:
- Creates additional persistent data structure stored with the database.
- Can dramatically speed up certain operations:
e.g. Find all R tuples where R.A = v
Find all R and S tuples where R.A = S.B
119
6
Creating an index
CREATE INDEX IndexName
ON Table_name(Column_name)
Create index NameIndex
On Students (Name)
Create index auIndex
On authors (au_id)
120
6
UNIQUE Index
Create UNIQUE index auIndex
On authors (au_id)
• No duplicate values in the indexed column are allowed
121
6
Composite IndexesSELECT *
FROM Student
WHERE name = "Mary" and GPA > 3.5
• Could use: – Index on Student.name – Index on Student.GPA – Index on (Student.name,Student.GPA)
Create index NameGPAIndex
On Students (Name, GPA)
122
6
Composite Index
Create index auNameInd
On authors (au_lname, au_fname)
• Needed when composite primary key
123
6
Indexes• Why not index every column in every table?
• Choosing which indexes to create is a difficult design issue
• the decision depends on size of tables and most importantly query/update load.
• Generally, you want to put indexes on columns you use frequently in retrieval, especially:– Primary key columns– Columns used frequently in joins (foreign keys)– Columns searched frequently for ranges of values– column searched frequently e.g. fname, lname
124
6
Indexes
Indexes should be avoided for• Columns that are rarely referenced in queries• Columns that are updated frequently• Columns that have only few distinct values
– e.g. gender: male, female
• Small tables with few rows