{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

07-sql-notes

# 07-sql-notes - SQL Part II CPS 116 Introduction to Database...

This preview shows pages 1–5. Sign up to view the full content.

1 SQL: Part II CPS 116 Introduction to Database Systems 2 Announcements (Thu. Sep. 22) Homework #2 assigned today Due on Tue. Oct. 4 (in less than 2 weeks) Midterm in class in 2 weeks Project milestone 1 due in 3 weeks 3 Incomplete information Example: Student ( SID , name , age , GPA ) Value unknown We do not know Nelson’s age Value not applicable Nelson has not taken any classes yet; what is his GPA?

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
2 4 Solution 1 A dedicated special value for each domain (type) GPA cannot be –1, so use –1 as a special value to indicate a missing or invalid GPA Leads to incorrect answers if not careful SELECT AVG(GPA) FROM Student; Complicates applications SELECT AVG(GPA) FROM Student WHERE GPA <> -1; Ever heard of the Y2K bug? • “00” was used as a missing or invalid year value 5 Solution 2 A valid-bit for every column Student ( SID , name , name_is_valid , age , age_is_valid , GPA , GPA_is_valid ) Complicates schema and queries SELECT AVG(GPA) FROM Student WHERE GPA_is_valid; 6 Solution 3? Decompose the table; missing row = missing value StudentName ( SID , name ) StudentAge ( SID , age ) StudentGPA ( SID , GPA ) StudentID ( SID ) Conceptually the cleanest solution Still complicates schema and queries • How to get all information about a student in a table?
3 7 SQL’s solution A special value NULL For every domain Special rules for dealing with NULL ’s Example: Student ( SID , name , age , GPA ) h 789, “Nelson”, NULL , NULL i 8 Computing with NULL ’s When we operate on a NULL and another value (including another NULL ) using +, –, etc., the result is NULL Aggregate functions ignore NULL , except COUNT(*) (since it counts rows) 9 Three-valued logic TRUE = 1, FALSE = 0, UNKNOWN = 0.5 x AND y = min( x , y ) x OR y = max( x , y ) NOT x = 1 – x When we compare a NULL with another value (including another NULL ) using =, >, etc., the result is UNKNOWN WHERE and HAVING clauses only select rows for output if the condition evaluates to TRUE UNKNOWN is not enough

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
4 10 Unfortunate consequences SELECT AVG(GPA) FROM Student; SELECT SUM(GPA)/COUNT(*) FROM Student; SELECT * FROM Student; SELECT * FROM Student WHERE GPA = GPA; ) Be careful: NULL breaks many equivalences 11 Another problem Example: Who has NULL GPA values?
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 11

07-sql-notes - SQL Part II CPS 116 Introduction to Database...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online