SQL III

1. SQL III CIS2450 Advanced Programming Concepts

2. The Join Operation • It is one of the most important features of a relational system that it allows you to SELECT data from two or more tables. • The tables are conceptuallyjoined on their common column. • has to contain the same data (ideally PK or FK) • column names may (not) be identical

3. Let’s create a table… CREATE TABLE AUTHORS ( AUTHORID DECIMAL(3), NAME CHAR(40), BIRTHYR CHAR(4), DEATHYR CHAR(4), PRIMARY KEY (AUTHORID) )

4. …one more… CREATE TABLE BOOKS ( BOOKID DECIMAL(3), TITLE CHAR(30), AUTHORID DECIMAL(3), DATE CHAR(4), PAGES INTEGER, PRIMARY KEY (BOOKID) )

5. Let's add some rows to the AUTHORS table… AUTHORID NAME BIRTHYR DEATHYR 100 Austen, Jane 1845 1880 200 Bronte, Emily 1830 1866 300 Dickens, Charles 1840 1899

6. Let's add some rows to the BOOKS table… BOOKID TITLE AUTHORID DATE PAGES 123 Wuthering Heights 200 1865 550 124 Oliver Twist 300 1878 600 220 Great Expectations 300 1880 900 340 Mansfield Park 100 1875 1000 490 Sense and Sensibility 100 1873 980

7. Types of Joins • Cross Join (Cartesian product) SELECT * FROM AUTHORS, BOOKS • A cross join simply multiplies the two tables together into a new virtual table. • 3 rows in the AUTHORS table and 5 rows in the BOOKS table → 15 rows in the result • 4 cols. in AUTHORS and 5 cols. in BOOKS → 9 cols. in the result

8. Result of Cross Join AUTHORID NAME BIRTHYR DEATHYR 100 Austen, Jane 1845 1880 100 Austen, Jane 1845 1880 100 Austen, Jane 1845 1880 100 Austen, Jane 1845 1880 100 Austen, Jane 1845 1880 200 Bronte, Emily 1830 1866 200 Bronte, Emily 1830 1866 200 Bronte, Emily 1830 1866 200 Bronte, Emily 1830 1866 200 Bronte, Emily 1830 1866 300 Dickens, Charles 1840 1899 300 Dickens, Charles 1840 1899 300 Dickens, Charles 1840 1899 300 Dickens, Charles 1840 1899 300 Dickens, Charles 1840 1899 BOOKID TITLE AUTHORID DATE PAGES 123 Wuthering Heights 200 1865 550 124 Oliver Twist 300 1878 600 220 Great Expectations 300 1880 900 340 Mansfield Park 100 1875 1000 490 Sense and Sensibility 100 1873 980 123 Wuthering Heights 200 1865 550 124 Oliver Twist 300 1878 600 220 Great Expectations 300 1880 900 340 Mansfield Park 100 1875 1000 490 Sense and Sensibility 100 1873 980 123 Wuthering Heights 200 1865 550 124 Oliver Twist 300 1878 600 220 Great Expectations 300 1880 900 340 Mansfield Park 100 1875 1000 490 Sense and Sensibility 100 1873 980

9. More Selective Joins • Inner Join • Tables are combined based on a common column. • Outer Join • This join involves combining all rows of one table with only matching rows from the other table. • Self Join • A table is joined to itself.

10. The Inner Join “List all the books with their matching author data.”Requires data from 2 tables! SELECT * FROM AUTHORS, BOOKS WHERE ( BOOKS.AUTHORID=AUTHORS.AUTHORID ) join condition: mentions 2 tables

11. Would really appear twice The Inner Join BOOKID AUTHORID TITLE DATE PAGES NAME BIRTHYR DEATHYR 123 200 Wuthering Heights 1865 550 Bronte, Emily 1830 1866 124 300 Oliver Twist 1878 600 Dickens, Charles 1840 1899 220 300 Great Expectations 1880 900 Dickens, Charles 1840 1899 340 100 Mansfield Park 1875 1000 Austen, Jane 1845 1880 490 100 Sense and Sensibility 1873 980 Austen, Jane 1845 1880 “List all the books with their matching author data.”

12. Another Inner Join “List the books that author 100 (Jane Austen) wrote, with her name & years.” Requires data from 2 tables! SELECT * FROM AUTHORS, BOOKS WHERE ( BOOKS.AUTHORID=AUTHORS.AUTHORID AND AUTHORS.AUTHORID=100 ) Better: AND NAME=‘Austen, Jane’

13. Another Inner Join BOOKID AUTHORID TITLE DATE PAGES NAME BIRTHYR DEATHYR 340 100 Mansfield Park 1875 1000 Austen, Jane 1845 1880 490 100 Sense and Sensibility 1873 980 Austen, Jane 1845 1880

14. Inner Join Operator • In addition to using the equal sign (=) operator to join tables, SQL includes a JOIN operator that can be used to make more explicit kinds of joins between two or more tables. SELECT * FROM ( AUTHORS INNER JOIN BOOKS ON BOOKS.AUTHORID=AUTHORS.AUTHORID ) WHERE AUTHORS.AUTHORID=100

15. Aliasing • It can become time-consuming to write out these SQL statements in full so it is common to create queries that have shorter table names by aliasing the table name to a one- or two-letter abbreviation. SELECT * FROM AUTHORS AS A, BOOKS AS B WHERE B.AUTHORID=A.AUTHORID

16. The Inner Join • The Inner Join statement provides a useful filter by picking out only rows where the key fields have the same value (specified in join condition) • The result of the Inner Join operation is one row for each key value that exists in both joined tables. • If there are rows in one table that do not have a corresponding match in the other table than the inner join ignores these rows.

17. The Outer Join • An outer join is used to include the rows that are missing in an inner join. • The purpose of an outer join is to include the missing or unmatched rows. • There are three types of Outer Joins: • LEFT OUTER JOIN (*=) • RIGHT OUTER JOIN (=*) • FULL OUTER JOIN *= / =* symbols in join condition in place of =

18. The Outer Join • The LEFT, RIGHT, and FULL syntax all describe which of the table's unmatchedrows to include. • extra cols. of unmatched rows get NULLs SELECT * FROM AUTHORS, BOOKS WHERE AUTHORS.AUTHORID *= BOOKS.AUTHORID

19. The Outer Join • The LEFT OUTER JOIN operator ensures that all rows on the left side of the join, in this case the AUTHORS table, will be included. • A FULL OUTER JOIN includes all unmatched rows from both tables in the result.

20. The Self Join • A Self Join is simply a normal SQL join that joins one table to itself. • It is accomplished by using table name aliases to give each instance of the table a separate name. • A Self Join is useful when a table of data contains a link to data values that are stored in the same table.

21. The Self Join • Imagine a table named Students with the following three columns: • StudentID StudentName TAID • Now, what if I want to know the names of the TA’s? • TA’s are students too so I need to do a Self Join.

22. The Self Join SELECT Students.StudentName AS TA FROM Students AS TAs, Students WHERE TAs.TAID = Students.StudentID

23. Views • A view (or viewed table) is a virtual table. • It does not really exist, but appears to the user as if it did. • They are defined in terms of other tables (basetables and other views). • Base Tables are real tables - they are physical entities and require storage space (such as files).

24. Creating a View • Creating the view LS - London Suppliers. CREATE VIEW LS ( SNO, SNAME, STATUS ) AS SELECT S.SNO, S.SNAME, S.STATUS FROM S WHERE S.CITY = 'London‘

25. Creating a View • The view LS is a virtual table derived from S which contains only the rows with CITY = 'London'. VIEW LS SNO SNAME STATUS S1 Smith 20 S4 Adams 30 • To the user, it appears a table LS exists.

26. Creating a View • Views can be updated only if they are a simple row/column subset of a single underlying base table (cannot be a join). • Views can be used for security by hiding sensitive data from unauthorized users. • The GRANT operation is used to provide and restrict access to tables (for SELECT, INSERT, DELETE, UPDATE) and columns (INSERT and UPDATE only).