Looked Over and Talked About ER Diagram Exercise

CS474

Notes 2/12/09

Covered the answers to HW6.

Looked over and talked about ER Diagram exercise.

Continuation of SQL Language Slides

BETWEEN includes endpoints

IN – For some people it works with double quotes while others are able to use single. Use whatever works for you.

WILDCARDS

Keyword: LIKE

Example: SELECT <attribute>

FROM <table>

WHERE <attribute> LIKE ‘xxx*’;

xxx = Partial or full sting you wish to search for.

* =Can be used before or after string to bring up anything with that letter combination in it.

If placed before it will look for that letter combination after any other letters.

If placed after it will look for that letter combination before any other letters.

CALCULATIONS

Keywords: SUM, AVG, MIN, MAX

Example: SELECT SUM (<attribute>) AS ‘<new attribute name>’

FROM <table>

Things to note:

AVG cannot be used on strings

MAX and MIN can be used on strings

Make sure that parentheses are placed around the attribute because it is a function call.

Keyword: COUNT

Example: SELECT COUNT (<attribute>) AS ‘<new attribute name>’

FROM <table>

Things to note:

Wildcard symbol (*) can be used to show all attributes.

Any attribute and the wildcard symbol with display the same amount of rows.

The keyword DISTINCT can be used to eliminate doubles.

Example: SELECT COUNT ( DISTINCT <attribute> )

Multi-table Queries

Using sub-queries: Queries on queries

Example: SELECT <attribute>

FROM <table>

WHERE <attribute> <rel-op> <value>

(SELECT <attribute>

FROM <table>

WHERE <attribute> <rel-op> <value> )

Look at Multi-table Queries (pg. 244) and JOINING (pg. 246)