Final Review

put the following items in their correct order

where order by select having group by from select from where group by having order by

Table_A has 3 records and Table_B has 4 records How many records will the following query return?

select * from Table_A, Table_B

What does DDL stand for?

• Stands for Data Definition Language – create statements – drop statements – alter statements

List reasons for using views

Tell me about indexes

Using SQL, how do you add records to a table

Using SQL, how do you remove data from a table?

Using SQL, how do you change data in a table?

Using SQL, how do you change your default database?

) ( CREATE TABLE a_marina MARINA_NUM char(4) NOT NULL, NAME char(20), ADDRESS char(15), CITY char(15), STATE char(2), ZIP What will this SQL statement do?

ERROR

We did not specify a datatype for the column ZIP

Write the SQL that will create an

P_ORDER_LINE

table with the following structure:

Field

ORDER_NUM PART_NUM NUM_ORDERED QUOTED_PRICE

Type

char(5) char(4) decimal(3,0) decimal(6,2)

Null

NO NO YES YES

Key

PRI PRI

Default Extra ) ( CREATE TABLE p_order_line ORDER_NUM char(5) NOT NULL, PART_NUM char(4) NOT NULL, NUM_ORDERED decimal(3,0) NULL, QUOTED_PRICE decimal(6,2) NULL, PRIMARY KEY (ORDER_NUM,PART_NUM)

Write the SQL that will create an

H_INVENTORY

table with the following structure:

Field

BOOK_CODE BRANCH_NUM ON_HAND

Type

char(4) decimal(2,0) decimal(2,0)

Null

NO NO YES

Key

PRI PRI

Default

0

Extra ) ( CREATE TABLE h_inventory BOOK_CODE char(4) NOT NULL, BRANCH_NUM decimal(2,0) NOT NULL default 0, ON_HAND decimal(2,0) NULL, PRIMARY KEY (BOOK_CODE,BRANCH_NUM)

What is a database?

structure containing categories of information and relationships between these categories

What is a Database Management System (DBMS)?

software that lets you create a database and maintain the structures and data within

What is open source software?

software whose source code is freely and publicly available

Describe the relationship

17

A sales rep has one or many customers

18

A sales rep has one or many customers A customer has one and only one sales rep

19

What does SQL stand for?

Structured Query Language

• • What is another name for a table?

– Entity What is a relationship – An association between entities

What are some reasons for database normalization?

• • • minimize duplication of data help safeguard against data anomalies improve performance for certain activities

What is a primary key?

• A primary key uniquely identifies a row in a table. A primary key can be a single column, or a group of columns. When a primary key is made up of multiple columns it may also be referred to as a composite key or a concatenated key, both of which mean “consisting of more than one column.”

Tell me about null values

Shops

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop Revenues

shop_id year quarter revenues

1 1 2008 2008 1 2 10000 7500 1 2 2 2 2008 2008 2008 2008 3 1 2 3 6300 4800 7300

null

Workers

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop What will the following query return?

select * from Shops

Results

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop Results

shop_id

2 3

shop_name

College Coffee Shop Northland Coffee Shop Write a query that will return all records that have the letter “L” in their shop name

select * from where Shops shop_name like '%L%'

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop Write a query that will return a count of all records in the shops table

select count(*) from Shops

Results

Count(*)

3

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15 What will the following query return?

select shop_name from join Shops s workers w on w.shop_id = s.shop_id and w.name = 'Kris'

Results

shop_name

Wisconsin Coffee Shop

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15 Write a query that will return average pay rate by shop name

select s.shop_name, avg(w.pay_rt) as 'Avg Pay Rt' from Shops s join workers w on w.shop_id = s.shop_id

group by s.shop_name

Results

shop_name

College Coffee Shop Northland Coffee Shop Wisconsin Coffee Shop

Avg Pay Rt

8.75

9 8.5

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15 Write a query that will return average pay rate by shop name. Only display shops with an average pay rate over 8.50

select s.shop_name, avg(w.pay_rt) as 'Avg Pay Rt' from Shops s join workers w on w.shop_id = s.shop_id

group by s.shop_name

having avg(w.pay_rt) > 8.5

Results

shop_name

College Coffee Shop Northland Coffee Shop

Avg Pay Rt

8.75

9

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15 Results

shop_name

College Coffee Shop Northland Coffee Shop Wisconsin Coffee Shop

Avg Pay Rt

8.75

9 8.75

Write a query that will return average pay rate by shop name. Don’t include workers making less then 8.50 and only display shops with an average pay rate over 8.50

select s.shop_name, avg(w.pay_rt) as 'Avg Pay Rt' from Shops s join workers w on w.shop_id = s.shop_id and group by w.pay_rt >= 8.5

s.shop_name

having avg(w.pay_rt) > 8.5

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15 What will the following query return?

select distinct name from workers

Results

name

Kris Jill Erika Kim Bill

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15 What will the following query return?

select distinct name from workers sort by 1

Results

ERROR

Sort by isn’t a valid clause

worker_id shop_id name pay_rt weekly_hrs

1 2 1 2 Kris Jill 8 8.5

25 40 3 4 5 6 2 1 1 3 Erika Kim Bill Bill 9 8.5

9 9 40 30 25 15 What will the following query return?

select distinct name from order by workers 1

Results

name

Bill Erika Jill Kim Kris

put the following items in their correct order

where order by select having group by from select from where group by having order by

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

shop_id year quarter revenues

1 1 2008 2008 1 2 10000 7500 1 2 2 2 2008 2008 2008 2008 3 1 2 3 6300 4800 7300

null

What will the following query return?

select s.shop_name, sum(r.revenues) from shops s left outer join revenues r on r.shop_id = s.shop_id

Results

ERROR

Query is missing a group by

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

shop_id year quarter revenues

1 1 2008 2008 1 2 10000 7500 1 2 2 2 2008 2008 2008 2008 3 1 2 3 6300 4800 7300

null

What will the following query return?

select s.shop_name, sum(r.revenues) from shops s left outer join revenues r on r.shop_id = s.shop_id

group by s.shop_name

Results

shop_name

College Coffee Shop Northland Coffee Shop Wisconsin Coffee Shop

sum(r.revenues)

12100 23800

Explain left outer joins

Left outer join: Returns data from the table on the left regardless if it has a match in the table on the right. If there is not a match on the right, null values are returned.

Explain right outer joins

right outer join: Returns data from the table on the right regardless if it has a match in the table on the left. If there is not a match on the left, null values are returned.

All operator

condition is true only if it satisfies all values

Any operator

condition is true only if it satisfies any value

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

shop_id year quarter revenues

1 1 2008 2008 1 2 10000 7500 1 2 2 2 2008 2008 2008 2008 3 1 2 3 6300 4800 7300

null

Results

shop_name

Northland Coffee Shop Write a query that will list shops that do not have any recorded revenues (use

IN

in your query)

select shop_name from shops where ) shop_id not in ( select shop_id from revenues

shop_id

1 2 3

shop_name

Wisconsin Coffee Shop College Coffee Shop Northland Coffee Shop

shop_id year quarter revenues

1 1 2008 2008 1 2 10000 7500 1 2 2 2 2008 2008 2008 2008 3 1 2 3 6300 4800 7300

null

Results

shop_name

Northland Coffee Shop Write a query that will list shops that do not have any recorded revenues (use

EXISTS

in your query)

select shop_name from shops s where ) not exists ( select shop_id from revenues r where r.shop_id = s.shop_id

What is a correlated subquery?

A subquery that involves a table listed in outer query

Car_Dealer_ID

1 2 3 4 5

Name

Kris Windorski Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

Sale_ID

1 2 3 4 5 6 7

Car

Porsche Cavalier Mustang Vibe BMW Ferrari Lexus

Cost

40,000 60,000 30,000 19,000 42,000 65,000 61,000

Car_Dealer_ID

1 1 3 4 4 2 2 What will the following query return?

select name, car from car_dealer c left outer join sales s on s.car_dealer_id = c.car_dealer_id and s.cost > 50000

Results

name

Kris Windorski Al Pacino Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

car

Cavalier Ferrari Lexus

null null null

46

Car_Dealer_ID

1 2 3 4 5

Name

Kris Windorski Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

Sale_ID

1 2 3 4 5 6 7

Car

Porsche Cavalier Mustang Vibe BMW Ferrari Lexus

Cost

40,000 60,000 30,000 19,000 42,000 65,000 61,000

Car_Dealer_ID

1 1 3 4 4 2 2 What will the following query return?

select name, car from car_dealer c left outer join sales s on s.car_dealer_id = c.car_dealer_id

where s.cost > 50000

Results

name

Kris Windorski Al Pacino Al Pacino

car

Cavalier Ferrari Lexus 47

Why do these queries produce different results?

select name, car from car_dealer c left outer join sales s on s.car_dealer_id = c.car_dealer_id and s.cost > 50000 select name, car from car_dealer c left outer join sales s on s.car_dealer_id = c.car_dealer_id

where s.cost > 50000

When a query only has inner joins a filter placed in the “where” clause will yield the same result as placing the filter in the “from” clause.

However, the same cannot be said when dealing with outer joins Let’s take a closer look

48

Car_Dealer_ID

1 2 3 4 5

Name

Kris Windorski Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

Sale_ID

1 2 3 4 5 6 7

Car

Porsche Cavalier Mustang Vibe BMW Ferrari Lexus

Cost

40,000 60,000 30,000 19,000 42,000 65,000 61,000

Car_Dealer_ID

1 1 3 4 4 2 2 A left outer join returns data from the table on the left regardless if it has a match in the table on the right.

select name, car from car_dealer c left outer join sales s on

In this query in order for there to be a match between the two tables the join criteria must be met, which includes the following : - Matching car dealer ids - A cost greater then 50,000

s.car_dealer_id = c.car_dealer_id and s.cost > 50000

49

Car_Dealer_ID

1 2 3 4 5

Name

Kris Windorski Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

Sale_ID

1 2 3 4 5 6 7

Car

Porsche Cavalier Mustang Vibe BMW Ferrari Lexus

Cost

40,000 60,000 30,000 19,000 42,000 65,000 61,000

Car_Dealer_ID

1 1 3 4 4 2 2

select name, car from car_dealer c left outer join sales s on

The car_dealer table is the table on the left, so we know every car dealer’s name will be returned regadless if it satisfies the join criteria

s.car_dealer_id = c.car_dealer_id and s.cost > 50000

Results

name

Kris Windorski Al Pacino Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

car

Cavalier Ferrari Lexus

null null null

50

Car_Dealer_ID

1 2 3 4 5

Name

Kris Windorski Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

Sale_ID

1 2 3 4 5 6 7

Car

Porsche Cavalier Mustang Vibe BMW Ferrari Lexus

Cost

40,000 60,000 30,000 19,000 42,000 65,000 61,000

Car_Dealer_ID

1 1 3 4 4 2 2

select name, car from car_dealer c left outer join sales s on

If the join condition is satisfied the information from the right (in this case the sales table) will also be returned. If the condition is not met

null

values will be returned from the right.

s.car_dealer_id = c.car_dealer_id and s.cost > 50000

Results

name

Kris Windorski Al Pacino Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

car

Cavalier Ferrari Lexus

null null null

51

Car_Dealer_ID

1 2 3 4 5

Name

Kris Windorski Al Pacino Paris Hilton Jessica Simpson A.J. Hawk

Sale_ID

1 2 3 4 5 6 7

Car

Porsche Cavalier Mustang Vibe BMW Ferrari Lexus

Cost

40,000 60,000 30,000 19,000 42,000 65,000 61,000

Car_Dealer_ID

1 1 3 4 4 2 2 A left outer join returns data from the table on the left regardless if it has a match in the table on the right.

select name, car from car_dealer c left outer join sales s on s.car_dealer_id = c.car_dealer_id

where s.cost > 50000

In this query in order for there to be a match between the two tables the join criteria must be met, which includes the following : - Matching car dealer ids

Note: the “where” clause also contains join/filter criteria, but logically it is applied after the join/filter criteria in the “from” clause have been applied

52

Union vs. Union all

Instructor_ID

1

Name

Doug Waterman 2 3 4 Terri Keane Glen Orsburn Kris Windorski

Class_NBR Instructor_ID

1111 4 4444 2222 1 3 5555 99999 7777 2 4 4

Name

SQL VB.net

SQL C++ Accounting SQL Results

Instructor Name Class Name

Doug Waterman Terri Keane Glen Orsburn SQL SQL SQL Kris Windorski Doug Waterman Terri Keane Glen Orsburn Kris Windorski SQL VB.net

VB.net

VB.net

VB.net

Doug Waterman Terri Keane Glen Orsburn Kris Windorski Doug Waterman Terri Keane SQL SQL SQL SQL C++ C++ Glen Orsburn Kris Windorski C++ C++ Doug Waterman Accounting Terri Keane Accounting Glen Orsburn Accounting Kris Windorski Doug Waterman Terri Keane Glen Orsburn Kris Windorski Accounting SQL SQL SQL SQL What will the following query return?

select i.name as 'Instructor Name', c.name as 'Class Name' from instructor i, class c

A join is not specified for the tables SQL returns every possible combination Referred to as a Cartesian Product 54

put the following items in their correct order

where order by select having group by from select from where group by having order by