Slide 3.4 - varchar data type 
CREATE TABLE x (vc VARCHAR(10))
INSERT INTO x VALUES('12')
INSERT INTO x VALUES('12   ')
SELECT LENGTH(vc) FROM x
SELECT vc FROM x WHERE vc = '12'

Slide 3.5 - Create Table
INSERT INTO instructor VALUES ('10211', 'Smith', 'Biology', 66000)
INSERT INTO instructor VALUES ('10211', null, 'Biology', 66000)

Slide 3.12 - DISTINCT & All
* SQL allows duplicates in relations as well as in query results
SELECT  		            dept_name FROM instructor
SELECT DISTINCT 	dept_name FROM instructor
SELECT ALL         	dept_name FROM instructor

Slide 3.13 - *, arithmetic expressions
* denotes "all attributes"
SELECT * FROM instructor

What is an expression?
	A combination of operators AND VALUES that result in a single value
What is an arithmetic expression?  
	Combines arithmetic operators AND numeric VALUES to return a single numeric value
What are the arithmetic operators that you can use?
	+, -, *, /,
	
WHERE do the VALUES come from?
	FROM numeric constants
	VALUES in attributes FROM THAT PARTICULAR ROW

SELECT id, name, salary*1.10 FROM instructor
What does this return ?
ONLY changes the way the data is displayed - does NOT change the data in the table

What is operator precedance?
SELECT id, name, 100+salary*1.10 FROM instructor
What is the order of evaluation in this expression?
What do we use to change the order of evaluation?

What about char and varchar attributes?
May use character operators to return a single char result.


Slide 3.14 - WHERE clause
Corresponds to the selection predicate in relational algebra
Predicate MUST return a boolean
Filters out rows that are returned in the result relation
  Expressions the return FALSE or NULL are not part of the result
SELECT name, dept_name FROM instructor WHERE dept_name = 'Comp. Sci.'
Can be combined with logical connectives.
  What are they?
SELECT name, dept_name FROM instructor WHERE dept_name = 'Comp. Sci.' or dept_name = 'Physics'

You may include string AND arithmetic expressions in the predicate.

Slide 3.15 - FROM clause
Lists ALL the relations involved in a query
Results in a CARTESIAN PRODUCT of all the tuples of all the tables UNLESS you tell it otherwise using a JOIN
SELECT * FROM instructor,
SELECT * FROM teaches
SELECT * FROM instructor, teaches

Slide 3.17 - Joins
For all instructors who have taught courses, find their names AND the course ID of the courses they taught.
  SELECT name, course_id  FROM instructor, teaches WHERE instructor.id = teaches.id
  SELECT name, course_id FROM instructor JOIN teaches ON instructor.id = teaches.id
  SELECT name, course_id FROM instructor JOIN teaches USING (id)
Show mon_qual

Slide 3.18
Find the names of courses in Computer science department which have 3 credits 
For the student with ID 12345 (or any other value), show all course_id AND title of all courses registered for by the student. 

Slide 3.19 - NATURAL Join
Matches tuples with the same VALUES for all common attributes, AND retains only one coy of each common column
SELECT name, course_id FROM instructor NATURAL JOIN teaches

Slide 3.20 - NATURAL Join
Danger: two unrelated columns of the same name with be joined !
List the names of the instructors along with the titles of courses that they teach
Look at schema
  What columns are we looking for?
    SELECT name, title 
  What tables do we need to hook together to get that information?
    instructor, teaches, course
Start with 
    SELECT name, title 
    FROM instructor NATURAL JOIN teaches
Are the results correct?
Let's add the final table with a natural join
    SELECT name, title 
    FROM instructor NATURAL JOIN teaches NATURAL JOIN course;
*GOOD JOIN
SELECT name, title 
FROM (instructor NATURAL JOIN teaches)  JOIN course using(course_id);

Slide 3.21 - Rename
Rename relations or attributes using the AS clause
	AS is optional (in Oracle it is NOT allowed at all)

Why would you do that?
	SELECT name, salary/12 FROM instructor
What is the name of that column in the output?	

Find all the instructors with a higher salary than Katz
How much does Katz make?
  SELECT name, salary
  FROM instructor
  WHERE name = 'Katz'
How do we find all the instructors that make more than him?
  SELECT name, salary
  FROM instructor
  WHERE salary > 75000
How do we put those 2 queries together?
  SELECT t.name, t.salary 
  FROM instructor s, instructor t
  WHERE s.name = 'Katz'
  AND t.salary > s.salary;

Slide 3.22 - String Operations
String Matching using the LIKE clause
	% char matches any SUBSTRING
	_	matches any CHARACTER

Find the names of all instructors that start with 'S'
  SELECT name 
  FROM instructor
  WHERE name LIKE 'S%' - help me write the LIKE expression

Concatenation operator is || or use CONCAT()
  SQL Server does not support this 
      Uses + sign
	  
Other string operations
  Done using functions  -  UPPER(), LOWER(), LENGTH()
  
 Slide 3.23 ORDER BY
 Get names of all instructors in each department
  SELECT dept_name, name, 
  FROM instructor
  
Get list sorted and ordered by department  
  ORDER BY dept_name  
  
Get list sorted and ordered by department  and name
  ORDER BY dept_name , name
  
Specify ascending or descending order
  ORDER BY dept_name  DESC, name ASC
  
Slide 3.24 - WHERE - BETWEEN clause
Find the names of all instructors with salary between $90,000 and $100,000 
  SELECT name, salary
  FROM instructor
  WHERE salary BETWEEN 90000 AND 100000  
Is this inclusive or exclusive of the endpoints?

What is this query doing?  (Taken from textbook slide)
Find names and courses taught for all instructors in the Biology dept that have taught some course
  SELECT name, course_id
  FROM instructor, teaches
  WHERE (instructor.ID, dept_name) = (teaches.ID, ’Biology’);
Theoretical - not supported by Oracle or SQL Server
  (a1,a2) <= (b1,b2) is true if a1 <= b1 AND a2 <= b2
  Likewise for the equality operator
How do we re-write it?
  SELECT name, course_id
  FROM instructor JOIN teaches
  ON  (instructor.ID = teaches.ID)  AND (instructor.dept_name = 'Biology')
  
Slides 3.24 - 2.25 - SKIP FOR NOW

Slide 3.27 - Set Operations - UNION, INTERSECT, EXCEPT
sql_union.gif
sql_intersect.gif
sql_except_12.gif
sql_symmetric_difference.gif

* column count must be the same; 
* data types of retrieved columns should match or at least should be implicitly convertible by database; 
* one can use many set operators for example Query1 UNION Query2 UNION Query3 MINUS Query4 . 
	Look into DB documentation to find out what the order of operators is
		Oracle executes operators starting from left to right, but DB2 executes Intersect first; 
* NULL values using set operators are considered to be equal to each other  
* Usually returned column names are taken from the first query; 
* Order by clauses may only be used on the final query
	Don't assume that rows from first query will always be returned first. 
* UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; 
* EXCEPT operator is NOT commutative
	sql_except_12.gif
	sql_except_21.gif
	When would you use except?
		Usually EXCEPT is used to compare date in different data sources (tables) to find differences, 
			differences in the same tables across test and production 
			difference in actual copy and backup - what data has changed since the last backup?
	Oracle uses MINUS operator instead of EXCEPT, but the functionality is the same. 

Slide 3.28 - Duplicates
* UNION, EXCEPT and INTERSECT return only unique values.
	What happens when you union a query that returns 10 row of which 5 rows are duplicates with a query that returns now row?  How many rows are in your result?
	If you need to distinguish which query produced the rows then you can add some flag column to each query. 
		Will change your results - duplicates rows from different queries are no longer duplicate
* To retain all duplicates, use the corresponding multiset versions - union all, intersect all and except all
*  Suppose a tuple occurs m times in relation r and n times in relation s, then, it occurs:
		How many duplicates will appear in the result set for :
			r union all s 		=> m + n  
			r intersect all s 	=> min(m,n)  
			r except all s 	=> max(0, m –n)  
  
Slide 3.29 - NULLS
* It is possible for tuples to have a null value, denoted by null, for some of their attributes
* null signifies an unknown value or that a value does not exist.
* The result of any expression involving nullis null
	Example: 5 + null  returns null
* The predicate IS NULL can be used to check for null values.
	Example: Find all instructors whose salary is null.
  
  INSERT INTO instructor VALUES('99999', 'Grasso', 'Comp. Sci.', null)

  SELECT name, salary
  FROM instructor 
  WHERE salary IS NULL
  How is null being displayed in the results?
  
  What happens if I do this instead?
  SELECT name, salary
  FROM instructor 
  WHERE salary = NULL
  
Slide 3.30 - Nulls and Three Valued Logic
* Any comparison with null returns unknown
	Example: 5 < null or null <> null or null = null
* Three-valued logic using the truth value unknown:
	OR: 		(unknown or true) 			= true,
				(unknown or false) 			= unknown
				(unknown or unknown) 	= unknown
				
	AND:	(true and unknown) 		= unknown,
				(false and unknown) 		= false,
				(unknown and unknown) = unknown
				
	NOT: 	(not unknown) = unknown
* Result of where clause predicate is treated as false if it evaluates to unknown  
  
  c.3 SQL Functions
  Used for 
  * Perform calculations on data - COS, SIN, UPPER, LOWER
  * Format data for display
  * Convert data types
  SOMETIMES take arguments, but ALWAYS return a SINGLE value
  We will mostly be looking at functions specific to Oracle
  
  c.4  Two types of functions:
	 Single row or SCALAR functions
		Operate on single rows only and return ONE result PER row
	Multiple-row or AGGREGATE or GROUP functions
		Manipulate groups of row to give on result per group of rows
		Computes a single value across all rows returned in the WHERE or HAVING clause
  
  c.5 SCALAR
	Accept one or more arguments and return one value for each row
		Arguments must be a compatible data type for that function
	Argument can be
		Literal
		Attribute / Column name 
		Expression
	Can be used in SELECT, WHERE and ORDER BY
  
  c.5 SCALAR Character Functions
	LOWER(expr), UPPER(expr), INITCAP(expr)
	CONCAT(expr, expr)
	SUBSTR(expr, start_col, len)
	LENGTH(expr)
	INSTR(expr, 'string')
	LPAD(expr, width), RPAD((expr, width)
	TRIM( LEADING | TRAILING | BOTH, trim_char FROM trim_source)
	REPLACE(text, search_string, replacement_string)
	
SELECT name	
FROM    student
WHERE  UPPER(name) = 'SNOW'

c.13 SCALAR Numeric Functions
	ROUND(expr, decimal_places)
	TRUNC(expr, decimal_places)
	MOD(m,n) - remainder of m divided by n
	
c.17 Dates	
	Oracle stores dates in an internal numeric format
	Date values also include TIME of day
	Default date format in Oracle is DD-MON-RR
		What is the RR in this format?
			0-49	 => current century
			50-99 => next century

* Valid dates are Jan 1, 4712 BC to Dec 31, 9999 AD
			
SELECT SYSDATE FROM DUAL
* Dual contains one column DUMMY and one row with value X
		
c.20 Date Expressions
	Operation				Result			Description
	date + number		Date				Adds a number of days
	date - number		Date				Subtracts a number of days
	date - date			Number of days
	date + number /24	Date				Adds a number of hours to the date

What is a Julian Date	? 
SELECT SYSDATE - TO_DATE('01-JAN-10') 
FROM DUAL
Add +1 for the real answer

# of weeks between two dates
SELECT SYSDATE - TO_DATE('01-JAN-10') / 7
FROM DUAL	
  
* Problems with dates
CREATE TABLE Dates (day DATE)
INSERT INTO Dates VALUES( SYSDATE )
SELECT * FROM Dates
SELECT * FROM Dates WHERE day = '18-SEP-10'
SELECT * FROM Dates WHERE day = SYSDATE
SELECT TO_CHAR(day, 'DD-MON-RR HH:MM:SS') FROM Dates

How to get rid of time portion
SELECT TRUNC(SYSDATE)
FROM DUAL  
  
c.27 Datatype conversion
SELECT salary FROM instructor WHERE salary =  90000 - show this first
SELECT salary FROM instructor WHERE salary = '90000' - will this work ?

	Implicit 
		VARCHAR / CHAR		NUMBER or DATE
		NUMBER or DATE			VARCHAR 
		Only if char strigs represents a valid number or date
	Explicit
		TO_CHAR(number | date, format_string)
		TO_NUMBER(char, format_string)
		TO_DATE(char, format_string)

* TO_CHAR with dates
SELECT 'Todays date is ' || TO_CHAR( SYSDATE, 'MM/DD/YYYY') today
FROM DUAL
What does the 'today' at the end of the query do?

SELECT 'Time is ' || TO_CHAR( SYSDATE, 'HH:MM') time
FROM DUAL

* TO_CHAR with numbers
Add dollar sign to result
SELECT TO_CHAR(salary, '$99999')

Put comma in thousands place
SELECT TO_CHAR(salary, '$99G999')
FROM instructor

* Most formatting should be handled by the front end

* TO_DATE with non-default date format
SELECT  TO_DATE( '09/01/2010', 'MM/DD/YYYY') 
FROM DUAL
* Notice the format the date comes out as

c.45 Nesting Functions
* Single row functions can be nested to any level
* Evaluated from the inside (deepest level) out

Find next review date - 1st friday 6 months from today
SELECT 'Review date is ' || 
    TO_CHAR( 
        NEXT_DAY(
	        ADD_MONTHS(SYSDATE, 6) ,
               'FRIDAY'),
        'fmDay, Month DDth, YYYY')
    AS review
FROM DUAL

c.55 CASE Expressions
* If - Then - Else logic
CASE expr WHEN comparison_expr THEN return_expr1
               [ WHEN comparison_expr THEN return_expr2
			      ...
				 WHEN comparison_expr THEN return_exprN
				 ELSE  return_expr
			   ]
			   

c.47  Functions for NULLS
* If first value is null, return the second value instead - Oracle ONLY
SELECT NVL( null, 'Clare') FROM DUAL
* If expressions are equal, return NULL.  Else return value of first expression
SELECT NULLIF( 2 , 2 ) FROM DUAL
* Return first expression in the list that is not null
SELECT COALESCE(null,  TO_CHAR(2+null), 'Clare' || null,  'All nulls') FROM DUAL
* Oracle - A null value can only result from the concatenation of two null strings. 
* SQL Server - null string concatenated with any other string is null

c.55 CASE Expressions
Form 1
CASE expr WHEN comparison_expr THEN return_expr1
               [ WHEN comparison_expr THEN return_expr2
			 ...
  		       WHEN comparison_expr THEN return_exprN
		       ELSE  return_expr
		    ]

SELECT course_id,
  CASE time_slot_id 
       WHEN 'A' THEN 'MWF 8:00-8:50'
       WHEN 'B' THEN 'MWF 9:00-9:50'
       WHEN 'C' THEN 'MWF 10:00-10:50'
       WHEN 'D' THEN 'MWF 11:00-11:50'
       WHEN 'E' THEN 'TR 11:00-11:50'
  END AS meets
FROM section

* Form 2
CASE 
       WHEN boolean_expr  THEN return_expr1
     [ WHEN boolean_expr  THEN return_expr2
	   ...
  	   WHEN boolean_expr   THEN return_exprN
	   ELSE  return_expr
	 ]

SELECT name,
  CASE WHEN tot_cred = 0                THEN 'Not a student'
       WHEN tot_cred BETWEEN  1 AND  29 THEN 'Freshmen'
       WHEN tot_cred BETWEEN 30 AND  59 THEN 'Sophomore'
       WHEN tot_cred BETWEEN 60 AND  89 THEN 'Junior'
       WHEN tot_cred BETWEEN 90 AND 119 THEN 'Senior'
                                        ELSE 'Am I done yet?'
  END
FROM student

3.31 - Aggregate Functions
Show slide 
* Group functions operate on a set of rows to return  ONE value for that set of rows

* Find the average salary of instructors in the Computer Science department 
select salary from instructor where dept_name= 'Comp. Sci.';
How many rows did we get back?

select AVG(salary) from instructor where dept_name= 'Comp. Sci.';
How many rows did we get back?
	
3.32 Aggregate Examples
* Find the number of tuples in the course relation	ie, number of rows in the table
Use the COUNT function to find out the number of rows 
select count (*)  from course

* Find the total number of instructors who teach a course in the Spring 2010 semester
select COUNT(id) from teaches where semester = 'Spring' and year = 2010
How many instructors are there?

SELECT * FROM teaches WHERE semester = 'Spring' AND year = 2010 
Are there any instructors that get counted more than once?

* For each group function you must know how it will handle duplicates
DISTINCT vs ALL
	DISTINCT considers only unique values returned
	ALL considers all values including duplicates.  This is the default.

select COUNT(DISTINCT id) from teaches where semester = 'Spring' and year = 2010

* All of the group functions ignore null values.
	To include them, use COALESCE to turn a null value in a row into a non-null value

3.33 Group By
* What if we want to get the average salary of instructors in EACH department?
select dept_name, salary
from instructor 
order by dept_name;

select dept_name, avg(salary) 
from instructor 
group by dept_name
order by dept_name;

3.34 Bad Group By
select id, dept_name, salary
from instructor 
order by dept_name;
  
select id, dept_name, avg(salary) 
from instructor 
group by dept_name, id
 
* * ALL column names in the SELECT list MUST appear in EITHER in the Group by Clause or in a Group Function
  
 3.35 HAVING
 * What if we want to filter out the groups that are returned based on the results of the grouping & aggregate functions?
 
* Find the names and average salaries of all departments whose average salary is greater than 42000
select dept_name, avg(salary)
from instructor
group by dept_name
having avg(salary) > 42000;
 
 * Order of processing SELECT query
1.	FROM /  JOIN 
2.	WHERE
3. GROUP BY
4.	HAVING 
5.	SELECT 
6. ORDER BY 

3.36   Null Values and Aggregates
Show slide 3.36

3.37 Nested Subqueries
Show slide 3.37

3.38 Examples
IN Operator 
* Find instructors in the sciences
select * from instructor 
WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.', 'Biology')
order by dept_name
* Attribute is compared against a LIST of values - based on EQUALITY

* Find courses offered in both Fall 2009 and in Spring 2010
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year= 2009 
 AND course_id  IN 
  (SELECT course_id
   FROM section
   WHERE semester = 'Spring' AND year= 2010
  );

* Find courses offered in Fall 2009 but not in Spring 2010
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year= 2009 
 AND course_id NOT IN 
  (SELECT course_id
   FROM section
   WHERE semester = 'Spring' AND year= 2010
  );
  
3.39 Example Query
Show slide 3.39
* Multi Column Comparison

3.40 Set Comparison - Return Boolean 
* Compare something with a list (or set) of values
* Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
select *
from instructor T, instructor S
where S.dept_name = 'Biology'
  and T.salary > S.salary

 * Same query using the SOME operator - allows for 
select name
from instructor
where salary > SOME
  (select salary
   from instructor
   where dept_name = 'Biology');  
  
3.41 Definition of the SOME Clause
* SOME => 'at least one member'

* If value is less than at least one member of the set
* What will the result be ?
SELECT 
  CASE WHEN 5 < SOME ( 0, 5, 6 ) THEN 'true'  ELSE 'false'  END
FROM DUAL

* How about 5 < SOME ( 0, 5 )  => take the 6 out

* How about 5 = SOME ( 0, 5 )

* How about 5 <> SOME ( 0, 5 )

* Used to be called the ANY keyword, but that resulted in abiguities with English usage

3.43	ALL Clause
* ALL => 'every member'

* If value is less than every member of the set
* What will the result be ?
SELECT 
  CASE WHEN 5 < ALL ( 0, 5, 6 ) THEN 'true'  ELSE 'false'  END
FROM DUAL

* How about 5 < ALL ( 6, 10 )  => take the 6 out

* How about 5 = ALL ( 4, 5 )

* How about 5 <> ALL ( 4, 6 )

3.44	EXISTS - Boolean operator
* Returns true if the argument subquery is nonempty.
SELECT 
  CASE WHEN EXISTS (SELECT * FROM instructor  ) 
       THEN 'true'  
       ELSE 'false'  
  END
FROM DUAL 

* Add WHERE dept_name = 'x'  to query

3.45	Correlation Variables
* Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester
SELECT course_id
FROM   section s
WHERE  semester = 'Fall' AND year= 2009 AND
  EXISTS (SELECT *
          FROM  section t
          WHERE t.semester = 'Spring' AND t.year= 2010
          AND   t.course_id= s.course_id
         );
		 
* Called a Correlated Subquery	
* course_id is the correlated name or correlation variable

3.45 NOT EXISTS
Show slide 3.46

select distinct S.ID, S.name
from   student S
where not exists ( 
  (select course_id
   from course
   where dept_name= 'Biology')
  minus
  (select T.course_id
   from takes T
   where S.ID = T.ID)
);

* For each student, create a list of course offered in the Biology dept and remove from that the biology courses that that student has taken

3.47	UNIQUE construct
Show slide 3.46

* Example doesn't work on Oracle
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id= R.course_id
and R.year= 2009);

* Use group by / having count(*) > 1
SELECT course_id
  FROM section 
  WHERE year= 2009
  GROUP BY course_id
  HAVING COUNT(*) > 1

3.48	Derived Relations
Show slide 3.48

SELECT dept_name, avg_salary
FROM (SELECT dept_name, avg(salary) 
      FROM instructor
      GROUP BY dept_name)  
WHERE avg_salary> 42000;
* Notice the column rename of avg_salary
* What happens if we take the rename away?

AS dept_avg(dept_name, avg_salary) - doesn't work in Oracle

3.49	LATERAL clause
Show slide 3.49
* A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause

ANSI Standard but not part of Oracle

3.50	WITH
Show slide 3.50

with max_budget(value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department.budget= max_budget.value;
* Oracle -> Change to 
with max_budget as
(select max(budget) AS value

3.51 Complex queries using WITH
Show slide 3.51
* The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. 

WITH 
  dept_total AS
   (SELECT dept_name, SUM(salary)AS value
    FROM instructor
    GROUP BY dept_name),
  dept_total_avg AS
   (SELECT AVG(value) AS value
    FROM dept_total)
SELECT dept_name
FROM dept_total, dept_total_avg
WHERE dept_total.value >= dept_total_avg.value;

* Notice there are 2 selects defined in the WITH clause - dept_total and dept_total_avg
* Second Select uses the table/view created in the first select

3.52	SCALAR subquery
Show slide 3.52

SELECT dept_name,(SELECT COUNT(*)
                  FROM instructor
                  WHERE department.dept_name= instructor.dept_name)
                  AS num_instructors
FROM department;

* Works only if the subquery returns only one row

3.53	DELETE
Show slide 3.53

* What is going to happen when we run
SELECT * FROM instructors

* What is going to happen when we run
DELETE FROM instructors
* That is why we turn Autocommit off
* Test your deletes using a select first - make sure you know what you are deleting

* Why is there no * after the DELETE keyword?

* Fire all the instructors that work for a department located in the Watson building
* If this were a select we would you a natural join
SELECT * 
FROM instructor NATURAL JOIN department
WHERE building = 'Watson'
* Why can't we do this with a DELETE
* You have to use the IN construct

3.54 Example Query
Show slide 3.54

* You have to do it in 2 steps

3.55	INSERTS
Show slide 3.55

* Second form is much better

3.56	INSERTS using SELECT
Show slide 3.55

* Add all instructors to the student relation with tot_creds set to 0
SELECT * FROM student

INSERT INTO student (id, name, dept_name, tot_cred)
  SELECT id, name, dept_name, 0
  FROM instructor

3.57	UPDATES
Show slide 3.57

3.58 	UPDATES using CASE
Show slide 3.57

* Formatted differently but the same statement
UPDATE instructor
  SET salary = CASE WHEN salary <= 100000 THEN salary * 1.05
                                          ELSE salary * 1.03
               END
			   
3.59	Updates with Scalar Subqueries
Show slide 3.58

UPDATE student 
  SET tot_cred= ( SELECT SUM(credits)
                  FROM takes NATURAL JOIN course
                  WHERE student.id = takes.id 
                    AND takes.grade <> 'F'
                    AND takes.grade IS NOT NULL);

SELECT * FROM student
* What value do we get for Snow?
* Why
* How do we fix it?

* With the CASE statement
UPDATE student 
  SET tot_cred = (SELECT CASE WHEN sum(credits) IS NOT NULL THEN SUM(credits)
                                                            ELSE 0
                         END
                  FROM takes NATURAL JOIN course
                  WHERE student.id = takes.id 
                    AND takes.grade <> 'F'
                    AND takes.grade IS NOT NULL);