Chapter 6
This presentation is the property of its rightful owner.
Sponsored Links
1 / 120

Chapter 6 PowerPoint PPT Presentation


  • 57 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 6. Structured Query Language (SQL). 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

Download Presentation

Chapter 6

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 6

Chapter 6

Structured Query Language (SQL)


Sample database for the following examples

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.


Bookbiz database

BookBiz Database

titles

authors

publishers

editors

title_id

title

price

type

pubdate

ytd_sales

advance

pub_id

ed_id

pub_id

pub_name

address

city

state

zip

au_id

au_lname

au_fname

phone

address

city

state

zip

ed_id

ed_lname

ed_fname

phone

address

city

state

zip

titleauthors

title_id

au_id

au_ord

royaltyshare


Select queries

SELECT Queries

SELECT [ALL| DISTINCT] Select_list

FROM Table_list

[WHERE Search_conditions]

[GROUP BY Group_by_list]

[HAVINGSearch_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


Eliminating duplicate row distinct

Eliminating Duplicate Row: DISTINCT

SELECT [ALL| DISTINCT] Desired Attributes

……

  • ALL returns all qualified rows (default)

  • DISTINCT returns only unique rows

    Example:

    Select titleSelect DISTINCT title

    from titlesfrom titles

    order by titleorder by title


Where clause search conditions

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;


Between

Between

Select title_id, ytd_sales

Fromtitles

Where ytd_slates BETWEEN 4000 AND 12000;

Select title_id, ytd_sales

Fromtitles

Where ytd_slates NOT (BETWEEN 4000 AND 12000);


Chapter 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


Is not null

IS [NOT] NULL

  • Null is a place holder for unknown info.

    Select title_id, advance

    from titles

    where advance is not null


Chapter 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


Order by clause

ORDER BY Clause

SELECTSelect_List

FROMTable_list

[WHERESearch_Conditions]

[ORDER BYOrder_by_list]

  • Order by allows you to sort results by any expression or mixture of expressions from the select_list


Order by clause1

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


Order by clause2

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


Order by clause3

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


Aggregate functions

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


Aggregate syntax

Aggregate 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


Aggregate fiunctions

Aggregate fiunctions

  • Compute total ytd sales for all books

    Select sum(ytd_sales)

    From titles

    Note:

    Select price, sum(price)

    From titles

    NOT ALLOWED


Aggregate fiunctions1

Aggregate fiunctions

  • Compute total ytd sales for all books

    Select sum(ytd_sales) as Total

    From titles

  • Total

    87654


Aggregate fiunctions2

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 NULL

    Select count(price), count(*)

    From titles

    Count distinct values?

    Select count(DISTINCT price)

    from titles


Aggregate functions count

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)


Group by clause

GROUP BY Clause

SELECT [ALL| DISTINCT] Select_list

FROM Table_list

[WHERE Search_conditions]

[GROUP BY Group_by_list]

[HAVINGSearch_conditions]

[ORDER BY Order_by_list]


Grouping data aggregates

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


Grouping data aggregates1

Grouping Data & Aggregates

  • How many books each publisher has?

    Select pub_id , count(title_id) as count

    from titles

    group by pub_id

    pub_idcount

    13806

    273317

    132221


Groups within groups

Groups within groups

Select pub_id , type, count(title_id) as count

from titles

group by pub_id, type

pub_idtypecount

1380math2

1380cs4

2733math5

2733Business12

1322cs5

1322biology3

1322physics6

  • Divide the rows in the table by publishers

  • Separate the rows in each publisher group by type

  • Apply aggregate function(s) to each set


Avoid confusing queries

Avoid confusing queries

Select pub_id, type , count(title_id) as count

from titles

group by pub_id

pub_idtypecount

1380math6

1380cs6

2733math17

2733Business17

1322cs14

1322biology14

1322physics14


Groups

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


Having clause

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.


Having clause1

Having 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


Where vs having

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


Where vs having1

Where vs Having

  • Can't use aggregates in WHERE clause

  • WHERE clause applies to individual rows

  • HAVING clause applies to groups of rows


Where vs having2

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


Bookbiz database1

BookBiz Database

titles

authors

publishers

editors

title_id

title

price

type

pubdate

ytd_sales

advance

pub_id

ed_id

pub_id

pub_name

address

city

state

zip

au_id

au_lname

au_fname

phone

address

city

state

zip

ed_id

ed_lname

ed_fname

phone

address

city

state

zip

Salesline

Sonum

title_id

qty_ordered

qty_shipped

date_shipped

titleauthors

Sales

title_id

au_id

au_ord

royaltyshare

Sonum

store_id

sdate


Joining tables

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


Joining tables example

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’ ;


Joining tables syntax 1

Joining Tables – SYNTAX-1

SelectSelect_list

From 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.


Joining tables1

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’ ;


Joining tables syntax 2

Joining Tables – SYNTAX-2

SELECTSelect_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.


Joining tables2

Joining Tables

  • Example: Find the name of the editor of book title "Database Systems“

    SELECT ed_fname, ed_lname

    FROM editors JOIN titles

    ON editors.ed_id = titles.ed_id

    WHERE title.title ='Database Systems’

    SELECT ed_fname, ed_lname

    FROM editors NATURAL JOIN titles

    WHERE title.title = ‘Database Systems’

    SELECT ed_fname, ed_lname

    FROM editors CROSS JOIN titles

    WHERE editors.ed_id = titles.ed_id

    AND title.title ='Database Systems’


Joining tables3

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


Joining tables examples

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


Joining more than two tables

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'


Self joins

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'


Non equijoins

Non-equijoins

  • Equijoin: join on matching (equal) values

  • Join can be done on other relational operations:

    >

    >=

    <=

    <=

    <>


Joins not based on equality

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


Joins not based on equality1

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;


Subqueries

Subqueries

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]

[HAVINGSearch_conditions]

[ORDER BY Order_by_list]


Simple subqueries

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.


Subqueries joins

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


Subqueries joins1

Subqueries & Joins

  • Find all books with prices equal to the minimum book price.

  • Subquery:

    select title, price

    from titles

    where price =

    ( select min (price)

    from titles)

  • Can NOT be done with a join.


Subqueries joins2

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


Subqueries joins3

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


Subqueries joins which is better

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.


Subqueries joins4

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)


Subqueries joins5

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)


Self join

Self_join

  • Find authors who live in same city as "Steven King"

    Select au_lname, au_fname, city

    from authors

    where city IN

    (select city

    from authors

    where au_fname='Steven'

    and au_lname='King')


Correlated subqueries

Correlated 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')


Correlated subquery

Correlated 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.


Correlated subquery processing

Correlated 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.


Subquery rules

Subquery 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')


Sub query rules cont

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')


Subquery rules cont

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.


Subqueries in not in

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')


Comparisons with all and any

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)


Comparisons with all and any1

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)


Comparisons with all and any2

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)


Comparisons with all and any3

Comparisons 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;


Unmodified comparisons

Unmodified 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.


Unmodified comparisons1

Unmodified 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)


Unmodified comparisons2

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


Correlated subqueries with comparisons

Correlated 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.


Multi level subqueries

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'


Multi level subqueries1

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' ) )


Subqueries in the from clause

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;


Subqueries in the from clause cntd

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 !


Subqueries in the from clause1

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


Creating and using views

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


Creating and using views1

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


Creating and using views2

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'


Displaying data thru the view

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.


Displaying data thru the view1

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


Advantages of views

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'


Advantages of views1

Advantages of views

  • Once the view is created, no need to retype the whole select query.

  • E.g. select *

    from books_SalesmanVersion

    etc.


Advantages of views2

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 viewcurrentinfo(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


Advantages of views3

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


Naming view columns

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


Naming view columns1

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.


Naming view columns2

Naming view columns

Create 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


Naming view columns3

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


Deriving views from views

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


Deriving views from views1

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'


Modifying views

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.


Modifying views rules according to ansi

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.


Union intersect difference

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)


Union intersect difference1

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)


Data modification

DATA MODIFICATION

  • INSERT: add new rows to a table

  • UPDATE: change existing rows

  • DELETE: delete existing rows


Insert

INSERT

Two ways:

  • With VALUES keyword

  • With SELECT statement

    INSERT with VALUES:

    INSERT INTO table_name

    [(column1, column2, …)]

    VALUES [(constant1, constant2, …)]


Insert with values

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


Insert with select

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


Insert with select1

Insert with Select

  • Suppose a table called newauthors exists with same format as authors

    Insert into newauthors

    Select au_id, au_lname, au_fname, phone, address, city, state, zip

    From authors

    OR

    Insert into newauthors

    Select *

    From authors


Insert with select2

Insert with Select

  • Insert into books

    Select title_id, title, type, pub_id, price*1.5, advance, pubdate, ytd_sales, ed_id

    From titles;


Update

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


Update1

Update

Update publishers

Set city = ‘Atlanta’, state = ‘CA’

Where pub_id = ‘1777’;

Update tiltles

Set price = price/2;


Delete

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’


Data definition

DATA DEFINITION

CREATE 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,

phonechar(12) null,

addressvarchar(40) null,

cityvarchar(20) null,

statechar(2) null,

zipchar(5) null)


Data types

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


Data types cntd

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


Deleting a table

Deleting a Table

  • DROP TABLE table_name

  • DROP DATABASE database_name


Modifying relation schemas

Modifying Relation Schemas

ALTER TABLE table_name

ADD/DROP/MODIFY column_name

ALTER TABLE authors

ADD birth_date datetime null

ALTER TABLE authors

MODIFY birth_date char(10)

ALTER TABLE authors

DROP birth_date


Default values

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 DEFAULT100 null,

city VARCHAR(30) DEFAULT“Houston” null,

gender CHAR(1) DEFAULT“?”,

Birthdate DATE null,


Indexes

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 is

very hard! (it’s called: physical database design).


Indexes1

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).


Creating indexes

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?


Indexes2

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.


Indexes3

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


Creating an index

Creating an index

CREATE INDEX IndexName

ON Table_name(Column_name)

Create index NameIndex

On Students (Name)

Create index auIndex

On authors (au_id)


Unique index

UNIQUE Index

Create UNIQUE index auIndex

On authors (au_id)

  • No duplicate values in the indexed column are allowed


Composite indexes

Composite Indexes

SELECT *

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)


Composite index

Composite Index

Create index auNameInd

On authors (au_lname, au_fname)

  • Needed when composite primary key


Indexes4

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


Indexes5

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


  • Login