Basic Structure - Database Management Systems - Lecture Slides, Slides of Introduction to Database Management Systems

Download Basic Structure - Database Management Systems - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems

1

Objectives

• Why do you need a query language?

• What are the main tasks of a query language?

• What is the basic structure of a query?

• What tables and columns are used in the Pet Store?

• How do you write queries for a specific DBMS?

• How do you create a basic query?

• What types of computations can be performed in SQL?

• How do you compute subtotals?

• How do you use multiple tables in a query?

2

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?

4

Joining Tables

5

CustomerID LastName FirstName Phone

Customer SaleID SaleDate CustomerID

Sales

CustomerID LastName FirstName Phone 1 Jones Mary 111- 2 Smith Marta 222- 3 Jackson Miguel 444- 4 Smith Mark 555-

SaleID SaleDate CustomerID 1 5/1 1 2 5/1 2 3 5/2 4 4 5/2 1

SaleID SaleDate CustomerID CustomerID LastName FirstName Phone

1 5/1 1 1 Jones Mary 111-

2 5/1 2 2 Smith Marta 222-

3 5/2 4 4 Smith Mark 555-

4 5/2 1 1 Jones Mary 111-

Tables

7

SupplierID Name ContactName Phone Address ZipCode CityID

Supplier

PONumber OrderDate ReceiveDate SupplierID EmployeeID ShippingCost

Merchandise Order

OrderID OrderDate ReceiveDate SupplierID ShippingCost EmployeeID

AnimalOrder OrderID AnimalID Cost

Animal OrderItem

CityID ZipCode City State AreaCode Population Population Country Latitude Longitude

City

EmployeeID LastName FirstName Phone Address ZipCode CityID TaxPayerID DateHired DateReleased

Employee

PONumber ItemID Quantity Cost

OrderItem

Category Registration

Category

Category Breed

Breed

AnimalID Name Category Breed DateBorn Gender Registered Color ListPrice Photo

Animal

SaleID SaleDate EmployeeID CustomerID SalesTax

Sale

SaleID ItemID Quantity SalePrice

SaleItem

ItemID Description QuantityOnHand ListPrice Category

Merchandise

SaleID AnimalID SalePrice

SaleAnimal

CustomerID Phone FirstName LastName Address ZipCode CityID

Customer

Organization

• Single table

• Constraints

• Computations

• Groups/Subtotals

• Multiple Tables

8

Query By Example & SQL

Field AnimalID Name Category DateBorn

Table Animal Animal Animal Animal

Sort

Criteria >’01-Aug-2007’

Or

10

SELECT AnimalID, Name, Category, Breed

FROM Animal

WHERE DateBorn > ’01-Aug-2007’;

Query04_Fig

AnimalID

Name

Category

Breed

DateBorn

Gender

Animal

What animals were born after August 1?

Basic SQL SELECT

11

SELECT columns What do you want to see?

FROM tables What tables are involved?

JOIN conditions How are the tables joined?

WHERE criteria What are the constraints?

DISTINCT

13

SELECT Category

FROM Animal;

Category Fish Dog Fish Cat Cat Dog Fish Dog Dog Dog Fish Cat Dog

...

SELECT DISTINCT Category FROM Animal;

Category Bird Cat Dog Fish Mammal Reptile Spider

Constraints: And

Which dogs have a list price greater than $250?.

14

SELECT AnimalID, Category, DateBorn

FROM Animal

WHERE Category-’Dog’ AND ListPrice>250;

Query04_Fig

AnimalID

Name

Category

Breed

DateBorn

Gender

Animal

Field AnimalID Name Category ListPrice

Table Animal Animal Animal Animal

Sort

Criteria Dog >

Or

Boolean Algebra

16

And: Both must be true.

Or: Either one is true.

Not: Reverse the value.

a b a AND b a OR b T T T T

T F F T F T F T

F F F F

a = 3

b = -

c = 2

(a > 4) Or (b < 0)

F T F

(a > 4) And (b < 0)

F T T

NOT (b < 0)

T F

Boolean Algebra

17

F T F T

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

T

F T

F F

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

T

a = 3

b = -

c = 2

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.

Always use parentheses,

so other people can read

and understand your query.

DeMorgan’s Law

• Negation of clauses
  • Not (A And B) becomes Not A Or Not B
  • Not (A Or B) becomes Not A And Not B

19

T F

T F

NOT ((Registered is NOT NULL) OR (Color LIKE ‘%Red%’))

Registered=ASCF Color=Black

(Registered is NULL) AND NOT (Color LIKE ‘%Red%’)

F T

F

or

not

and

not^ F

Conditions: And, Or

20

List all dogs who are male and registered or who were

born before 6/1/2007 and have white in their color.

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

FROM Animal

WHERE (( Category=‘Dog’) AND

( ( (Gender=‘Male’) AND (Registered Is Not Null) ) OR

( (DateBorn<’01-Jun-2007’) AND (Color Like ‘%White%’) ) ) );

Query04_Fig

AnimalID

Name

Category

Breed

DateBorn

Gender

Animal

Field AnimalID Category Gender Registered DateBorn Color

Table Animal Animal Animal Animal Animal Animal

Sort

Criteria ‘Dog’ ‘Male’ Is Not Null

Or ‘Dog’ < ’01-Jun-2004’ Like ‘%White%’