INFO 340 - L04

Assignment 1:
    Domains for search hardcoded in a file - limit up to you

    
How many distinct red boats have been reserved by sailors who are 35.0 years old or who have a rating greater than 10?

SELECT COUNT(DISTINCT r.bid)
FROM Boat b, Sailor s, Reserves r
WHERE (b.color = 'red') AND (r.sid = s.id) AND (r.bid = b.id) AND (s.age = 35.0 OR s.rating > 10);

Next week chapters 3 - 4
See sample questions on website for practice

Recap
SELECT [DISTINCT] Projection
FROM cross product
WHERE selection;

Grouping By and Having
Aggregate operators apply to all qualifying rows of a relation

GROUP BY clause indicates the list that you want to create - grouping list
HAVING group qualifications

Find the age of the youngest sailor for each rating level:
SELECT s.rating, MIN(s.age)
FROM Sailor s
GROUP BY s.rating;

would look something like this:
s.rating    MIN(s.age)
1           20
2           30.5
3           20.5
4           10
..          ...


Find the age of the youngest sailor who is eligible (> 18) for each rating level with at least two such sailors:
SELECT s.rating, MIN(s.age)
FROM Sailor s
WHERE s.age > 18
GROUP BY s.rating       <--- grouping list
HAVING COUNT(*) > 1;    <--- group qualification WHERE applies to full records, HAVING group only

Just show us the cases in which there is more than one record for the sailor ID

Look in CH.5 for more select statements

Other commands
INSERT
UPDATE
DELETE

CREATE TABLE
ALTER TABLE
DROP TABLE

You will be expected to write SQL queries on the midterm and final.  Syntax correctness isn't completely needed, but understanding of the underlying structure is needed.

Types of File Organization
- Heap (unordered)
- Sequential (ordered)
- Hash files

Standard Operations:
- Scan
- Search with equality selection
- Search with range
- Insert a record
- Delete a record

When Inserting and Deleting
1. identify page
2. fetch page from disk
3. modify page to include new record
4. write page back to the disk
(depending on the file organization this may have to be repeated many times)

Heap Files
Operations
    - Search is very slow (linear)
    - Insert is very fast (put it on the end)
    - Delete = Search (still has to search until it finds it, linear; marked deleted does not affect surrounding data)
    
Ordered Files
Operations
    - Equality search is fast (binary search)
    - Range searching is fast (binary search)
    - Insert / Delete is very slow

Hash Files
Operations
    - Equality search is very fast
    - Range search is very slow
    - Insert is very fast
    - Delete is very fast
    
SQL Indexes
A data structure that allows a DBMS to locate  particular records in a file more quickly and  thereby speed response to user queries.

Not part of SQL ANSI standard but is always provided

WHERE s.age BETWEEN 30 and 50
WHERE s.age = 55.5
How does the index on <age> help?
if there is a secondary index on age a binary search can be used
find the needed age quickly which has a pointer to the sailor record