Instructor: Craig Duckett

Lecture 11: Thursday, May 3th, 2018

Set Operations, Subqueries, Views

1

2

• MID-TERM EXAM GRADED!• Assignment 2 is due LECTURE 12, NEXT Tuesday, May 8th

in StudentTracker by MIDNIGHT

• Assignment 3 is due LECTURE 20, Tuesday, June 5th

• Database Presentation is due LECTURE 20, Tuesday, June 5th

• Final Exam is LECTURE 21, Thursday, June 7th

3

3 x 150 Points (450 points Total)

• Assignment 1 GRADED! • Assignment 2 (Stage 2): NEXT Tuesday, May 8th

• Assignment 3 (Stage 3): DUE LECTURE 20 Tuesday, June 5th

• Database Presentation: DUE LECTURE 20 Tuesday, June 5th

4

Tuesday (LECTURE 10)• Database Design for Mere Mortals: Chapter 7

Thursday (LECTURE 11)• The Language of SQL: Chapters 9, 10

5

• Mid-Term Post-Mortem• Set Operations [LoSQL, Chapter 15]

• Subqueries [LoSQL, Chapter 14]

• Views [LoSQL, Chapter 13]

PLEASE NOTE: I am introducing these topics now even though you haven’t read about them yet in TheLanguage of SQL book. This way I am “planting seeds” to let you know that this functionality isavailable, and to remove some of the “mystery” from them when you finally get to them in thereading.

6

http://www.sololearn.com/

RECOMMENDED SITE: SoloLearn

7

Mid-Term Post-Mortem

Mid-Term Post-Mortem• Answers

• Ideal Field, Ideal Table• Ideal Table: “It represents a single subject which can be an object or an event”• http://www.databaseanswers.org/data_models/• Purpose of database is threefold:

• A repository of data• The gathering of data (interface, multiple users)• The collection of information from the data

• Extra Credit• 100% + Extra Credit (my idea was that the extra credit was meant to supplement

points towards 100)• 100% + Extra Credit (less points available)• 100% + extra credit truncated to 100 points possible• 100% + “Bonus” question (with no possible points advertised) Bansenauer• No extra credit

9

Set Operations

Set OperationsSet theory is fundamental to the relational model. But whereas mathematical sets are unchanging, database sets are dynamic—they grow, shrink, and otherwise change over time. This section covers the following SQL set operators, which combine the results of two SELECT statements into oneresult:

• UNION returns all the rows returned by both queries, with duplicates removed.

• INTERSECT returns all rows common to both queries (that is, all distinct rows retrieved by both queries).

• EXCEPT returns all rows from the first query without the rows that appear in the second query, with duplicates removed.

These set operations are not joins, but you can mix and chain them to combine two or moretables.

Set Operations: UNION OperatorThe SQL UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements.

Each SELECT statement within the UNION must have the same number of column fields in the result sets and all with the same data types.

UNION can be useful in data warehouse applications where tables aren't perfectly normalized.

A simple example would be a database having tables sales2005 and sales2006 that have identicalstructures but are separated because of performance considerations. A UNION query couldcombine results from both tables.

Note that UNION does not guarantee the order of rows. Rows from the second operand mayappear before, after, or mixed with rows from the first operand. In situations where a specificorder is desired, ORDER BY must be used.

Note that UNION ALL may be much faster than plain UNION.

We will look at both of these in a moment. • http://www.w3schools.com/sql/sql_union.asp• http://www.w3resource.com/sql/sql-union.php• http://www.techonthenet.com/sql/union.php• http://www.tizag.com/sqlTutorial/sqlunion.php

Difference Between SQL JOIN and a UNION

Joins and Unions can be used to combine data from one or more tables. The difference lies in how the data is combined.

In simple terms, joins combine data into new columns. If two tables are joined together, then the data from the first table is shown in one set of column alongside the second table’s column in the same row.

Unions combine data into new rows. If two tables are “unioned” together, then the data from the first table is in one set of rows, and the data from the second table is in another set of rows.

To repeat: in order to union two tables there are a couple of requirements:

• The number of columns must be the same for both SELECT statements.• The columns, in order, must be of the same data type.

REVIEW: JOINHere is a visual depiction of a join. Table A and Table B’s columns are combinedinto a single result.

Each row in the result contains columns from BOTH Table A and Table B. Rows are created when columns from one table match columns from another table. This match is called the join condition.

This makes joins really great for looking up values and including them in results. This is usually the result of denormalizing (reversing normalization) and involves using the foreign key in one table to look up column values by using the primary key in another.

With a JOIN you can pick and choose what columns will be returned

UNIONNow compare the previous depiction with that of a union. In a union each row within the result is from one table OR the other. In a union, columns are not combined to create results, rows are combined.

Unions are typically used where you have two results whose rows you want to include in the same result. A use case may be that you have two tables: Teachers and Students. You would like to create a master list of names and birthdays sorted by date. To do this you can use a union to first combine the rows into a single result and then sort them.

Let’s now take slightly deeper look into both.

Combining Data with a JoinIn this section well look at the inner join. It is one of the most common forms of join and is used when you need to match rows from two tables. Rows that match remain in the result, those that don’t are rejected.

SELECT Employee.NationalIDNumber, Person.FirstName, Person.LastName, Employee.JobTitleFROM EmployeeJOIN PersonON Employee.BusinessEntityID = Person.BusinessEntityIDORDER BY Person.LastName;

Notice the join condition, see how we are matching BusinessEntityID from both the Employee and Person tables.

Check out that the results contains columns from both the Employee and Person tables.

Set Operations: UNION OperatorNow let’s take a look at UNION and how it works…

Note that there are two rows for Joe because those rows are distinct across their columns.

There is only one row for Alex because those rows are not distinct for both columns.

Set Operations: UNION OperatorUNION ALL gives different results, because it will not eliminate duplicates.

Executing this statement…

…would give these results, again allowing variance for the lack of an ORDER BY statement:

Set Operations: UNION Operator

Set Operations: INTERSECT OperatorMySQL does not support the INTERSECT operator

The SQL INTERSECT operator is used to return the results of two or more SELECT statements. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Each SQL statement within the SQL INTERSECT must have the same number of fields in the result sets with similar data types. For purposes of duplicate removal the INTERSECT operator does not distinguish between NULLs.

The INTERSECT operator removes duplicate rows from the final result set.

The INTERSECT ALL operator does not remove duplicate rows from the final result set.

• http://www.techonthenet.com/sql/intersect.php• http://www.tutorialspoint.com/sql/sql-intersect-clause.htm• http://www.essentialsql.com/learn-to-use-union-intersect-and-except-clauses/

Set Operations: INTERSECT OperatorMySQL does not support the INTERSECT operator

Example : The following example INTERSECT query returns all rows from the Orders tablewhere Quantity is between 50 and 100.

Set Operations: EXCEPT OperatorMySQL does not support the EXCEPT operator

Example

The following example EXCEPT query returns all rows from the Orders table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100.

Worded another way: the query returns all rows where the Quantity is between 1 and 100, except from rows where the quantity is between 50 and 75.

Set Operations: EXCEPT Operator

The following example is equivalent to the above example but withoutusing the EXCEPT operator. Instead it utilizes a JOIN and subqueries, which we will be looking at next

23

Subqueries

NOTE: We’ll look at this a few times with different examples. We will also look at it again in greater detail once we have Microsoft SQL Server up and running.

SQL Subqueries

SELECT * FROM ToysWHERE numberLegs =

(SELECT MAX(numberLegs) FROM Toys)

Subqueries are query statements tucked inside of query statements. Like the order of operations from Algebra class, order of operations also come into play when you start to embed SQL commands inside of other SQL commands (subqueries).

Let's take a look at an example involving the toys table and figure out how to select only toys with the greatest number of legs.

To accomplish this, we are first going to introduce a built-in SQL function, MAX(). This function wraps around a table column and quickly returns the current maximum (max) value for the specified column. We are going to use this function to return the names of all those toys that possess the current "max" (maximum) number of legs.

http://www.w3schools.com/sql/sql_func_max.asp

SELECT MAX(numberLegs) FROM Toys -- Let’s say this returns 6

SELECT * FROM ToysWHERE numberLegs = 6; -- manually put in the 6

Query 1

Query 2

-or-

Query with asubquery

SQL SubqueriesSELECT * FROM ToysWHERE numberLegs =

(SELECT MAX(numberLegs) FROM Toys)

A subquery is a SQL query nested inside another query.

A subquery may occur in a:

• SELECT clause• FROM clause• WHERE clause

The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

A subquery is usually added within the WHERE Clause of another SQL SELECT statement.

You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.

A subquery is treated as an inner query, which is a query placed as part of an outer query.

The inner query executes first before its parent query so that the results of inner query can be passed to the outer query.

SQL Subqueries Basic SQL Query

SQL Subqueries

SQL Subqueries Students table

SQL Subqueries Tests table

SQL Subqueries

SQL Subqueries

SQL Subqueries

SQL Subqueries

SQL Subqueries

This is what is returned by the subquery

SQL Subqueries

SQL Subqueries

SQL Subqueries

SQL Subqueries

http://www.w3schools.com/sql/sql_in.asp

inwith a list

=with single row

SQL Subqueries

SELECT CompanyNameFROM CustomersWHERE CustomerID = ?

SELECT CustomerIDFROM OrdersWHERE OrderID = 10290; [RESULT: COMMI]

SELECT CompanyNameFROM CustomersWHERE CustomerID = ‘COMMI’ [RESULT: Comércio Mineiro]

SELECT CompanyNameFROM CustomersWHERE CustomerID = (SELECT CustomerID

FROM OrdersWHERE OrderID = 10290);

[RESULT: Comércio Mineiro]

Northwind Database ExampleWithout Subquery With Subquery

SQL SubqueriesW3Resource

http://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php

http://www.w3resource.com/sql/subqueries/single-row-subqueries.php

http://www.w3resource.com/sql/subqueries/multiplee-row-column-subqueries.php

http://www.w3resource.com/sql/subqueries/nested-subquerie.php

42

Views

We’ll look at VIEWs in much greater detail once we are up and running in Microsoft SQL Server…

SQL ViewsSQL VIEWS are data objects, and like SQL Tables, they can be queried, updated, and dropped.

A SQL VIEW is a virtual table containing columns and rows except that the data contained insidea view is generated dynamically from SQL tables and does not physically exist inside the viewitself.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if thedata were coming from one single table.

• http://www.tutorialspoint.com/sql/sql-using-views.htm• http://www.w3schools.com/sql/sql_view.asp• http://www.techonthenet.com/sql/views.php• http://www.mysqltutorial.org/mysql-views-tutorial.aspx• http://www.sqlinfo.net/sqlserver/sql_server_VIEWS_the_basics.php

SQL ViewsCreating a View with CREATE VIEW

Think of a view as being a tailored presentation that provides a tabular window into one or morebase tables. The window can display an entire base table, part of a base table, or a combinationof base tables (or parts thereof). A view also can reflect the data in base tables through otherviews—windows into windows. Generally, SQL programmers use views to present data toend-users in database applications. Views offer these advantages:

Simplified data access. Views hide data complexity and simplify statements, so users canperform operations on a view more easily than on the base tables directly. If you create acomplex view—one that involves, say, multiple base tables, joins, and subqueries—users canquery this view without having to understand complex relational concepts or even knowing thatmultiple tables are involved.

Automatic updating. When a base table is updated, all views that reference the table reflectthe change automatically. If you insert a row representing a new author into the table authors,for example, all views defined over authors will reflect the new author automatically. This schemesaves disk space and prevents redundancy because, without views, the DBMS would have tostore derived data to keep it synchronized.

SQL ViewsCreating a View with CREATE VIEW (CONTINUED)

Increased security. One of the most common uses of views is to hide data from users by filteringthe underlying tables. Suppose that the table employees contains the columns salary andcommission. If you create a view on employees that omits these two columns but contains otherinnocuous columns (such as email_address), the database administrator can grant userspermission to see the view but not see the underlying table, thereby hiding compensation datafrom the curious.

Logical data independence. Base tables provide a real view of a database. But when you use SQLto build a database application, you want to present end users not the real view, but a virtualview specific to the application. The virtual view hides the parts of the database (entire tables orspecific rows or columns) that aren’t relevant to the application. Thus, users interact with thevirtual view, which is derived from—though independent of—the real view presented by the basetables.

SQL Views-- TO CREATE A VIEWCREATE VIEW view [(view_columns)]AS select_statement;

• view is the name of the view to create. The view name must be unique within the database.• view_columns is an optional, parenthesized list of one or more comma-separated names to be used

for the columns in view. The number of columns in view_columns must match the number ofcolumns in the SELECT clause of select_statement. (If you name one column this way, you mustname them all this way.) If view_columns is omitted, view inherits column names fromselect_statement. Column names also can be assigned in select_statement via AS clauses. Eachcolumn name must be unique within the view.

• select_statement is a SELECT statement that identifies the columns and rows of the table(s) that theview is based on. select_statement can be arbitrarily complex and use more than one table or otherviews. An ORDER BY clause usually is prohibited.

- - TO CREATE A VIEWCREATE VIEW CurrentBiddersIDASSELECT Bidders.name FROM BiddersWHERE Bidders.BidderIDIN (SELECT Bidders.bidderID FROM CurrentBids);

- - TO QUERY A VIEWSELECT * FROM CurrentBiddersID

SQL Views(We will go over these once we start using Microsoft SQL Server)

When you’re creating a view, some important considerations are:

• View names follow the same rules that table names do.• View names must be unique within a schema (or database). They can not have the same

name as any other table or view.• The columns in a view inherit the default column names from the underlying tables. You can

give view columns different names by using AS• You must specify a new name for a column in a view that would have the same name as

another column in the view (usually because the view definition includes a join and thecolumns from two or more different underlying tables have the same name).

• A column defined in a view can be a simple column reference, a literal, or an expression thatinvolves calculations or aggregate functions.

• In some DBMSs, you must specify explicitly the name of a column in a view if the column isderived from an arithmetic expression, a built-in function, or a literal.

• A view column inherits the data type of the column or expression from which it is derived.• You have no practical limit on the number of views that you can create. Generally, you want to

create views on subsets of data that are of interest to many users.• Almost any valid SELECT statement can define a view, though an ORDER BY clause usually is

prohibited.

SQL Views(CONTINUED)

• You can nest views—that is, a view’s SELECT statement can retrieve data from another view.Nested views eventually must resolve to base tables (otherwise, you’d be viewing nothing)The maximum number of nesting levels varies by DBMS.

• You can use views as a convenience to save complex queries. By saving a query that performsextensive calculations as a view, you can recalculate each time the view is queried.

• A view can express a query that you’d otherwise be unable to run. You can define a view thatjoins a GROUP BY view with a base table, for example, or define a view that joins a UNIONview with a base table.

• A view definition can’t reference itself, because it doesn’t exist yet.• Views can display data formatted differently from those in the underlying tables.• Unlike a base table, a view does not support constraints.• When you define a view by using SELECT *, SQL converts the * to a list of all columns

internally. This conversion occurs only once, at view creation (not at execution), so thedefinition of your view will not change if someone adds a column to an underlying table (byusing ALTER TABLE).

• Because views store no data, the DBMS must execute them every time they’re referenced.

SQL Views: ExamplesCreate a view that hides the authors’ personal information (telephone numbers and addresses).

Create a view that lists the authors who live in a city in which a publisher is located. Note that Iuse the column names au_city and pub_city in the view. Renaming these columns resolves theambiguity that would arise if both columns inherited the same column name city from theunderlying tables.

SQL Views: ExamplesCreate a view that lists total revenue (= price × sales) grouped by book type within publisher. Thisview will be easy to query later because I name the result of an arithmetic expression explicitlyrather than let the DBMS assign a default name.

SQL Views: Examples

Create a view that lists the last names of authors A02 and A05, and the books that each onewrote (or cowrote). Note that this statement uses a nested view: It references the viewau_names created earlier.

52

NO ICE TODAY

ASSIGNMENT 2 Work Day