Agenda - PowerPoint PPT Presentation

Agenda l.jpg
Download
1 / 33

  • 283 Views
  • Updated On :
  • Presentation posted in: Pets / Animals

Agenda. Single-level Ordered Indexes Multilevel Indexes Dynamic Multilevel Indexes Loading data SQL Queries. Four Questions to Create a Query. What output do you want to see? What do you already know (or what constraints are given)? What tables are involved?

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

Agenda

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


Agenda l.jpg

Agenda

  • Single-level Ordered Indexes

  • Multilevel Indexes

  • Dynamic Multilevel Indexes

  • Loading data

  • SQL Queries


Four questions to create a query l.jpg

Four Questions to Create a Query

  • What output do you want to see?

  • What do you already know (or what constraints are given)?

  • What tables are involved?

  • How are the tables joined together?

Post


Sample questions l.jpg

List all animals with yellow in their color.

List all dogs with yellow in their color born after 6/1/98.

List all merchandise for cats with a list price greater than $10.

List all dogs who are male and registered or who were born before 6/1/98 and have white in their color.

What is the average sale price of all animals?

What is the total cost we paid for all animals?

List the top 10 customers and total amount they spent.

How many cats are in the animal list?

Count the number of animals in each category.

List the CustomerID of everyone who bought something between 4/1/98 and 5/31/98.

List the first name and phone of every customer who bought something between 4/1/98 and 5/31/98.

List the last name and phone of anyone who bought a registered white cat between 6/1/98 and 12/31/98.

Which employee has sold the most items?

Sample Questions

Post


Harder questions l.jpg

How many cats are “in-stock” on 10/1/98?

Which cats sold for more than the average price?

Which animals sold for more than the average price of animals in their category?

Which animals have not been sold?

Which customers (who bought something at least once) did not buy anything between 11/1/98 and 12/31/98?

Which customers who bought Dogs also bought products for Cats (at any time)?

Harder Questions

Post


Query by example qbe sql l.jpg

List all animals with yellow in their color.

Query By Example (QBE) & SQL

What tables?

What

conditions?

SELECTAnimalID, Category, Breed, Color

FROMAnimal

WHERE(Color LIKE “*yellow*”);

What to see?

Post


Distinct l.jpg

DISTINCT

SELECT Category

FROM Animal;

SELECT DISTINCT Category

FROM Animal;

Category

Fish

Dog

Fish

Cat

Cat

Dog

Fish

Dog

Dog

Dog

Fish

Cat

Dog

. . .

Category

Bird

Cat

Dog

Fish

Mammal

Reptile

Spider

Post


Order by l.jpg

ORDER BY

SELECTcolumns

FROMtables

JOINjoin columns

WHEREconditions

ORDER BYcolumns (ASC DESC)

NameCategoryBreed

CathyBirdAfrican Grey

BirdCanary

DebbieBirdCockatiel

BirdCockatiel

TerryBirdLovebird

BirdOther

CharlesBirdParakeet

CurtisBirdParakeet

RubyBirdParakeet

SandyBirdParrot

HoytBirdParrot

BirdParrot

SELECT Name, Category, Breed

FROM Animal

ORDER BY Category, Breed;

Post


Useful where conditions l.jpg

Useful Where Conditions

Post


Constraints and l.jpg

List all dogs with yellow in their color born after 6/1/98.

Constraints: AND

SELECTAnimalID, Category, DateBorn

FROMAnimal

WHERE((Category="Dog") AND (Color Like "*Yellow*")

AND (DateBorn>#6/1/98#));

Post


Conditions and or l.jpg

List all dogs who are male and registered or who were born before 6/1/98 and have white in their color.

Conditions: AND, OR

SELECT AnimalID, Category, Gender, Registered, DateBorn, Color

FROM Animal

WHERE (( Category="Dog") AND

( ( (Gender="Male") AND (Registered Is Not Null) ) OR

( (DateBorn<#6/1/98#) AND (Color Like "*White*") ) ) );

Post


Boolean algebra l.jpg

Boolean Algebra

a = 3

b = -1

c = 2

And:Both must be true.

Or:Either one is true.

Not:Reverse the value.

(a > 4) And (b < 0)

F

T

F

(a > 4) Or (b < 0)

F

T

T

NOT (b < 0)

T

F

Post


Boolean algebra12 l.jpg

Boolean Algebra

The result is affected by the order

of the operations.

Parentheses indicate that an

operation should be performed first.

With no parentheses, operations are

performed left-to-right.

a = 3

b = -1

c = 2

( (a > 4) AND (b < 0) ) OR (c > 1)

F

T

T

F

T

T

Always use parentheses,

so other people can read

and understand your query.

(a > 4) AND ( (b < 0) OR (c > 1) )

F

T

T

F

T

F

Post


Demorgan s law l.jpg

Negation of clauses

NOT (A AND B) becomes NOT A OR NOT B

NOT (A OR B) becomes NOT A AND NOT B

DeMorgan’s Law

Registered=ASCF

Color=Black

NOT ((Registered is NOT NULL) OR (Color LIKE “*red*”))

T

F

or

T

not

F

(Registered is NULL) AND NOT (Color LIKE “*red*”)

F

F

not

and

T

F

Post


Demorgan s law example l.jpg

DeMorgan’s Law Example

Customer:"I want to look at a cat, but I don’t want any cats that are

registered or that have red in their color."

SQL:SELECT . . . WHERE (Category=“cat”) AND

NOT ((Registered is NOT NULL) or (Color LIKE “*red*”)).

QBE:

Post


Simple computations l.jpg

Simple Computations

OrderItem(OrderID, ItemID, Price, Quantity)

Select OrderID, ItemID, Price, Quantity, Price*Quantity As Extended

From OrderItem;

OrderIDItemIDPriceQuantityExtended

151976419.50239.00

1517653 8.35325.05

1518673 6.89213.78

Basic computations (+ - * /) can be performed on numeric data.

The new display column should be given a meaningful name.

Post


Computations example avg l.jpg

What is the average sale price of all animals?

Sum

Avg

Min

Max

Count

StDev

Var

Computations: Example--Avg

SELECT Avg(SalePrice) AS AvgOfSalePrice

FROM SaleAnimal;

Post


Computations math operators l.jpg

What is the total value of the order for PONumber 22?

Use any common math operators on numeric data.

Operate on data in one row at a time.

OrderTotal

1798.28

Computations (Math Operators)

SELECT Sum([Quantity]*[Cost]) AS OrderTotal

FROM OrderItem

WHERE (PONumber=22);

Post


Subtotals where l.jpg

How many cats are in the Animal list?

Subtotals (Where)

SELECTCount(AnimalID) AS CountOfAnimalID

FROMAnimal

WHERE(Category = “Cat”);

Post


Groups and subtotals l.jpg

Count the number of animals in each category.

You could type in each WHERE clause, but that is slow.

And you would have to know all of the Category values.

CategoryCountOfAnimalID

Dog100

Cat47

Bird15

Fish14

Reptile6

Mammal6

Spider3

Groups and Subtotals

SELECTCategory, Count(AnimalID) AS CountOfAnimalID

FROMAnimal

GROUP BYCategory

ORDER BYCount(AnimalID) DESC;

Post


Conditions on totals having l.jpg

Count number of Animals in each Category, but only list them if more than 10.

CategoryCountOfAnimal

Dog100

Cat47

Bird15

Fish14

Conditions on Totals (Having)

SELECTCategory, Count(AnimalID) AS CountOfAnimalID

FROMAnimal

GROUP BYCategory

HAVINGCount(AnimalID) > 10

ORDER BYCount(AnimalID) DESC;

Post


Where detail vs having group l.jpg

Count Animals born after 6/1/98 in each Category, but only list Category if more than 10.

CategoryCountOfAnimalID

Dog30

Cat18

Where (Detail) vs. Having (Group)

SELECTCategory, Count(AnimalID) AS CountOfAnimalID

FROMAnimal

WHEREDateBorn > #6/1/98#

GROUP BYCategory

HAVINGCount(AnimalID) > 10

ORDER BYCount(AnimalID) DESC;

Post


Multiple tables intro distinct l.jpg

List the CustomerID of everyone who bought something between 4/1/98 and 5/31/98.

CustomerID

6

8

14

19

22

24

28

36

37

38

39

42

50

57

58

63

74

80

90

Multiple Tables (Intro & Distinct)

Avoid Duplicates

with DISTINCT

SELECT DISTINCT CustomerID

FROM Sale

WHERE (SaleDate Between #4/1/98# And #5/31/98#)

ORDER BY CustomerID;

Post


Joining tables l.jpg

List LastNames of Customers who bought between 4/1/98 and 5/31/98.

CustomerIDLastName

22Adkins

57Carter

38Franklin

42Froedge

63Grimes

74Hinton

36Holland

6Hopkins

50Lee

58McCain

37McPherson

Joining Tables

SELECT DISTINCT Sale.CustomerID, Customer.LastName

FROM Customer

INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID

WHERE (Sale.SaleDate Between #4/1/98# And #5/31/98#)

ORDER BY Customer.LastName;

Post


Sql join l.jpg

SQL JOIN

FROM table1

INNER JOIN table2

ON table1.column = table2.column

SQL 92 syntax

FROM table1, table2

WHERE table1.column = table2.column

SQL 89 syntax

FROM table1, table2

JOIN table1.column = table2.column

Informal syntax

Post


Multiple tables many l.jpg

List the Last Name and Phone of anyone who bought a registered White cat between 6/1/98 and 12/31/ 98.

Multiple Tables (Many)

SELECT DISTINCTROW Customer.LastName, Customer.Phone

FROM Customer INNER JOIN (Sale INNER JOIN (Animal INNER JOIN SaleAnimal

ON Animal.AnimalID = SaleAnimal.An

imalID) ON Sale.SaleID = SaleAnimal.SaleID)

ON Customer.CustomerID = Sale.CustomerIDWHERE ((Animal.Category="Cat") AND (Animal.Registered Is Not Null)

AND (Color Like "*White*") AND (SaleDate Between #6/1/98# And #12/31/98#));

Post


Building a query l.jpg

Identify the tables involved.

Look at the columns you want to see.

LastName, Phone: Customer

Look at the columns used in the constraints.

Registered, Color, Category: Animal

Sale Date: Sale

Find connector tables.

To connect Animal to Sale: SaleAnimal

Select the desired columns and test the query.

Enter the constraints.

Set Order By columns.

Add Group By columns.

Add summary computations to the SELECT statement.

Building a Query

Post


Joining tables hints l.jpg

Build Relationships First

Drag and drop

From one side to many side

Avoid multiple ties between tables

SQL

FROM Table1

INNER JOIN Table2

ON Table1.ColA = Table2.ColB

Join columns are often keys, but they can be any columns--as long as the domains (types of data) match.

Multiple Tables

FROM (Table1

INNER JOIN Table2

ON T1.ColA = T2.ColB )

INNER JOIN Table3

ON T3.ColC = T3.ColD

Shorter Notation

FROM T1, T2, T3

JOIN T1.ColA = T2.ColB

T1.ColC = T3.ColD

Shorter Notation is not correct syntax, but it is easier to write.

Joining Tables (Hints)

Post


Tables with multiple joins l.jpg

Potential problem with three or more tables.

Access uses predefined relationships to automatically determine JOINs.

JOINS might loop.

Most queries will not work with loops.

Tables with Multiple Joins

A query with these four tables with four JOINS would only return rows where the Employee had the same ZipCode as the Supplier. If you only need the Supplier city, just delete the JOIN between Employee and ZipCode. If you want both cities, add the ZipCode table again as a fifth table.

Post


Table alias l.jpg

Table Alias

SELECT Supplier.SID, Supplier.CityID, City.City, Employee.EID, Employee.LastName, Employee.CityID, City2.City

FROM (City INNER JOIN Supplier ON City.CityID = Supplier.CityID) INNER JOIN ((City AS City2 INNER JOIN Employee ON City2.CityID = Employee.CityID) INNER JOIN AnimalOrder ON Employee.EmployeeID = AnimalOrder.EmployeeID) ON Supplier.SupplierID = AnimalOrder.SupplierID;

SIDSupplier.CityIDCity.CityEIDLastNameEmployee.CityIDCity2.City

47972Middlesboro5James7083Orlando

210896Springfield1Reeves9201Lincoln

47972Middlesboro3Reasoner8313Springfield

910740Columbia8Carpenter10592Philadelphia

510893Smyrna3Reasoner8313Springfield

Post


Saved query create view l.jpg

Save a query

Faster: only enter once

Faster: only analyze once

Any SELECT statement

Can use the View within other SQL queries.

Saved Query: Create View

CREATE VIEW Kittens AS

SELECT *

FROM Animal

WHERE (Category = ‘cat’) AND (Today - DateBorn < 180);

SELECT Avg(ListPrice)

FROM Kittens

WHERE (Color LIKE “*Black*”);

Post


Updateable views l.jpg

To be updateable, a view must focus on one primary table. (OrderItem)

Goal is to change data in only one table. (OrderItem)

Data can be displayed from other tables. (Item)

Never include or attempt to change primary keys from more than one table. (Item.ItemID)

Updateable Views

OrderItem(OrderID, ItemID, Quantity)

Item(ItemID, Description)

OrderLine(OrderID, ItemID, Description, Quantity)

Post


Non updateable view l.jpg

Non Updateable View

OrderItem(OrderID, ItemID, Quantity)

Item(ItemID, Description)

121573

121822

122571

57Cat food

58Dog food

59Bird food

OrderLine(OrderID, Item.ItemID, Description, Quantity)

12157Cat food3

12182Bird feeder2

12257Cat food1

32

If you attempt to change the Item.ItemID in the OrderLineView:

You will simply change the primary key value in the Item table.

It will not add a new row to the OrderItem table.

Post


Next lecture l.jpg

Next Lecture

  • SQL continued

  • Relational Algebra


  • Login