4.2 - Intermediate SQL
Show slide
Topics for this chapter

Slide 4.3 - Joined Relations
Show slide
What is a join?  
	A pairwise combining of the tuples in one relation with the tuples in another relation
	What are the attributes in the resulting relation?
		All the attributes for the first relation (r1) appended with all the attributes of the second relation (r2)
	How are the tuples combined?
		Cartesian or Cross product - Every tuple in r1 is combined with every other tuple in r2

	What is a NATURAL JOIN?
		Natural Join - Tuples in r1 are combined with tuples in r2 where the values match on 
			attributes that have the same name
	Three issues that arise with NATURAL JOIN
		How do you join on attributes that do NOT have the same name?
		How about attributes that HAVE the same name but should NOT be used in a join
		How about rows that are excluded because they contain a value of NULL in the attribute that is being joined
	How do you solve these issues?
		We will look at explicit join predicates
		JOIN ... USING - form of the NATURAL JOIN that requires values to match only on specified attributes

Slide 4.4 - Join operations – Example
NO Show

* Two relations that we will be using in this section
SELECT * FROM student
* How many rows?
* How many columns? 4

SELECT * FROM takes
* How many rows?
* Notice the last row.  What grade is recorded in the last row?
* How many columns? 6

What happens when we do a NATURAL JOIN on these two relations?
SELECT * FROM student NATURAL JOIN takes
* How many columns? 9

Equivalent queries:
SELECT * FROM student JOIN takes USING(id)
* How many columns? 9
SELECT * FROM student JOIN takes ON student.id = takes.id
* How many columns? 10
SELECT * 
  FROM student, takes 
  WHERE student.id = takes.id
* How many columns? 10

 * Even though they are equivalent, use the expression that most closely resembles what you are trying to do
 * Do NOT do the join in the WHERE clause - 
	JOIN .. ON behaves differently than WHERE 
	More readable
	Use WHERE only for more filtering out rows based on other conditions
 
 Slide 4.5 - Outer Join
 NO Show
 
 * Take a look at students again
SELECT * FROM student 
ORDER BY name
* How many credits does Snow have?
 
* Get a list of all students and how many credits they have taken
SELECT * 
FROM student NATURAL JOIN takes
ORDER BY name 
 * What happened to Snow in this query?
  
 * We won't get information back about students that have not taken any courses
 * We lost information in the query - may be lost from either one of the 2 joined relations
 
* OUTER JOIN preserves tuples that would be lost when the join-attribute value is null
	Fills in the values of attributes in the non-matching rows with nulls
	
 * There are 3 forms of the outer join
	LEFT OUTER JOIN
	RIGHT OUTER JOIN
	FULL OUTER JOIN
 
* Which one should we use here for our students?
SELECT * 
FROM student NATURAL LEFT OUTER JOIN takes
ORDER BY name

* Find all students who have not taken a course
SELECT * 
FROM student NATURAL LEFT OUTER JOIN takes
WHERE course_id IS NULL

* We could have just filtered on the number of credits.  Why is this a bad idea?
	Student might have takend a course and not gotten credit - failed a course
	Very bad design - calculated values should never be included

* What happens if we switch the order of the relations in the FROM clause
SELECT * 
FROM takes NATURAL LEFT OUTER  JOIN student
ORDER BY name
* What happened to Snow now?
* How do we fix it?
SELECT * 
FROM takes NATURAL RIGHT OUTER  JOIN student
ORDER BY name

* LEFTand RIGHT outer joins are symmetric

* What does a FULL OUTER JOIN do
Show slides 4.4, 4.6, 4.7, 4.8

* What is this query doing?
SELECT *
  FROM 	(SELECT *
         FROM student
	 WHERE dept_name = 'Comp. Sci.'	)
  NATURAL FULL OUTER JOIN
	(SELECT *
	 FROM takes
	 WHERE semester = 'Spring' AND year = 2009 )
* List of all students in Comp Sci along with the course sections, if any, that they have taken in Spring 2009 
	All course sections from Spring 2009 must be displayed, even if no student from Comp Sci has taken it	
	
Slide 4.9 - Joined Relations
Show slide
JOIN OPERATION  - anytime the JOIN keyword is used
JOIN CONDITION - determines what attributes you are joining on
JOIN TYPE - what to do when there are nulls in the join attributes

* What is an INNER join?
	Just a normal join

* You can include the keyword INNER to NATURAL JOINS
SELECT * FROM student NATURAL INNER JOIN takes
SELECT * FROM student INNER JOIN takes USING(id)
SELECT * FROM student INNER JOIN takes ON student.id = takes.id

Slide 4.10 - Joined Relations – Examples
* Review join types


Slide 4.11 - Joined Relations – Examples
Show slide
* Review join types
* Why did we lose a column?

Slide 4.12 - Views
Show slide
* So far, we have assumed that we have been selecting from tables that actually exist in the database
	We have been operating on the logical-model level
	
* Sometimes it is necessary to 
	Hide information - security
	Create an easier query by hiding its complexity
	
* Create a query on courses offered this semester
	Depends on today's date
	
SELECT * 
FROM course NATURAL JOIN section
* That's a lot of columns
	What columns do we want ?
	What rows do we want to filter in?

SELECT course_id, title, dept_name, credits, sec_id, building, room_number, time_slot_id, year, semester
FROM course NATURAL JOIN section
WHERE year = EXTRACT(YEAR FROM SYSDATE)
AND semester = 
    CASE 
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 2 AND 5 THEN 'Spring'
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 6 AND 8 THEN 'Summer'
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 9 AND 12 THEN 'Fall'
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 1 AND 1 THEN 'Winter'
    END
	
* Extra row I inserted to make query work
	INSERT INTO section VALUES('CS-101', 1, 'Fall', 2010, 'Packard', 101, 'H')
	INSERT INTO section VALUES('BIO-101', 1, 'Fall', 2010, 'Painter', 514, 'E')
	INSERT INTO section VALUES('EE-181', 1, 'Fall', 2010, 'Taylor', 3128, 'H')

* We could create new table with that info
CREATE TABLE this_semester AS ...

SELECT * FROM this_semester

* Why would we not want to do that?
	What happens whey we start a new semester?
	Data from original tables may change
DROP TABLE this_semester

* Slide: Any relation that is not of the LOGICAL model but is made visible to a user as a “virtual relation” is called a VIEW
	What does virtual mean?
		Virtual relation - acts like it exists, but it doesn't really
	
Slide 4.13 - View Definition
Show slide	

* Define a new relation based on existing tables and data - but don't actually create the relation
	Only the definition of how to create that relation is stored
		
CREATE VIEW view_this_semester AS
* Create VIEW instead of table
	Give it a name and a definition
		Let everybody know it is only a view
			There are limits with what you can do with a view

* Stores the query expression
	Works like a MACRO in C
		Simple substituion - sort of
			Pre-compiled
			The view definition - that query expression - is substituted into the outer select where ever it appears

* We can do anything in a query based on a view that we can do on a table
SELECT * FROM  view_this_semester
WHERE department = 'Biology'

* Might want to change the names of underlying attributes
CREATE OR REPLACE VIEW view_this_semester 
AS 
SELECT course_id, title, dept_name AS department, credits, sec_id AS section, building, room_number AS room, time_slot_id AS time, year, semester
FROM course NATURAL JOIN section
WHERE year = EXTRACT(YEAR FROM SYSDATE)
AND semester = 
    CASE 
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 2 AND 5 THEN 'Spring'
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 6 AND 8 THEN 'Summer'
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 9 AND 12 THEN 'Fall'
      WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 1 AND 1 THEN 'Winter'
    END

* Notice CREATE OR REPLACE- Oracle only

SELECT * FROM view_this_semester 
* Whenever a view realation appears in a query, it is replaced by the stored query expression.
* Whenever we evaluate the query, the view realtion is recomputed.



Slide 4.14 -  Example Views

CREATE VIEW departments_total_salary AS
SELECT dept_name, sum (salary) AS total_salary
FROM instructor
GROUP BY dept_name;

Slide 4.15 - Views Defined Using Other Views
Show slide
* You can include a view in the definition of another view
* Show RSAS views

Slide 4.16 - View Expansion
Show slide
* Shows how the view is really defined using view in previous slide

Slide 4.17 - Views Defined Using Other Views
Show slide

Slide 4.18 - View Expansion
Show slide

Slide 4.16 - Update of a View
Show slide
CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor

* What happens if we want to insert into faculty?
INSERT INTO faculty VALUES ('30765', 'Green', 'Music');

Slide 4.20 - Some Updates cannot be Translated Uniquely
Show slide
CREATE VIEW instructor_info AS
  SELECT id, name, building
  FROM instructor JOIN department USING (dept_name)

* What happens with this insert?
INSERT INTO instructor_info VALUES ('69987', 'White', 'Taylor');

You can DELETE if there are NO:
	Aggregate functions
	GROUP BY 
	DISTINCT

You can UPDATE if there are NO:
	Aggregate functions
	GROUP BY 
	DISTINCT
	Columns defined by expressions (calculated columns)
	
You can DELETE if there are NO:
	Aggregate functions
	GROUP BY 
	DISTINCT
	Columns defined by expressions (calculated columns)
	NOT NULL columns in the base tables that are not selected by the view

Text pg 123 - Materialized Views
View relation itself is stored
	Updated by db engine
		View maintenance 
		May be done
			Immediately when any underlying table changes
			Lazily - when the view is actually used
			Periodically - when the db engine gets around to it
				You might get back out of date data
Pros:
	With long running queries to create the view
Cons:
	Extra storage needed
	
Slide 4.21 - And Some Not at All
Show slide - sort of
CREATE VIEW history_instructors AS
SELECT *
FROM instructor
WHERE dept_name= 'History';

INSERT INTO history_instructors VALUES ('25566', 'Brown', 'Biology', 100000) 
* Huh ?
DELETE FROM history_instructors WHERE id = '25566'
SELECT * FROM history_instructors
SELECT * FROM instructor
DELETE FROM instructor WHERE id = '25566'

* Use the WITH CHECK OPTION
CREATE OR REPLACE 
VIEW history_instructors AS
SELECT *
FROM instructor
WHERE dept_name= 'History'
WITH CHECK OPTION
 
Slide 4.22 - Transactions
Show slide
* A transaction consists of a sequence of query  and / or update statements
* Begins when a SQL statement is executed
* Ends with either COMMIT WORK or ROLLBACK WORK is executed 
	WORK keyword is optional
	COMMIT makes the changes permanent
	ROLLBACK undoes all the changes that were made since the transaction started
* Implicit behavior
		If there is a system failure, a ROLLBACK is generated automatically when the db starts up again
		ANY DDL statement, first causes a COMMIT to take place
* Auto commit makes every statement permanent (unless there is an error)
* Atomic => indivisible
	All of the changes are made, or none of the changes are made

* Transfer funds between 2 accounts - savings to checking