Oracle 11g Sql Hands On Assignments Answers To The Impossible Quiz

Orginally created by Darrell Aucoin in conjuection with the Stats Club, find the original here.

Installation of SQLite Browser

SQLite Browser webpage

Windows

Download and install the file below

Windows download

Mac

Download and install the package below

Mac download

Ubuntu and Linux Mint

In terminal execute the following command

Get the material

  1. Go to https://github.com/NormallySane/IntroSQL
  2. Download zip file
  3. Unzip downloaded file in your preferred directory

Using SQLite Browser

  1. Open SQLite browser
  2. Open database file in the downloaded directory
  3. Click on ‘Execute SQL’ tab
    1. Open SQL file file in the downloaded directory, for second presentation.
    2. Follow along with file, executing statement as topics dictate
  4. Content of the talk is on https://github.com/NormallySane/IntroSQL/wiki (open in your favorite browser)
  • SQLite browser is a great tool for learning SQLite and SQL in general

Features of SQLite Browser

  • Lets you see the data structure of tables in the database

  • Explore the data entered in tables

  • Execute SQL statements and see results

  • Easily construction tables from files

  • If the table is already created it will just import the data into the table, otherwise it will create a new table

Motivation

Q: Why learn a database lanugage?

  • Data is rarely tidy as they are in many stats courses
  • Most of the time the data we want (outside of assignments) is in a database

Q: Why learn SQL?

Q: What is SQL?

  • A programming language allowing multiple concurrent users storing, manipulating, and querying data stored in a relational database.
  • Data does not necessary have to fit into memory.

Q: Why use SQLite?

  • Very easy to install
  • Easy to share (database is a single file)
  • Good SQL language to practice with (can easily use it to preprocess data for kaggle competitions)

Different Implementations of SQL

MySQL: Highly popular open source SQL implementation.

PostgreSQL: Open source SQL designed around letting users create User Defined Functions (UDF).

SQLite: Open sources light weight SQL usually used as an embedded database for applications (web browsers, mobile applications, etc.), or light to medium traffic websites. Database is saved as a single file and only allows one writer at a time. Various OS’s have SQLite preinstalled (type in terminal for mac)

Oracle: SQL implementation produced and marketed by Oracle Corporation.

Microsoft SQL Server: SQL implementation developed by Microsoft.

DB2: SQL developed by IBM. The database used by University of Waterloo.

Relational Databases

Relational Database: A relational database is a system organized in tables containing records (rows) related to other records in other tables. Each entity having attributes (columns also called fields) which give additional information on an entity.

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/RelationalDatabase.png)

Field, Attribute (Column): An individual piece of data stored in a table.

Record (Row): A tuple of columns describing an entity or action of an entity.

Table: A collection of rows. Usually in reference a persistent table saved permanently to memory

Result set: A non-persistent table, usually the result of a query.

(Virtual) View: A named query saved into memory and performed whenever it is named. Some SQL servers have materialized views that permanently save the data for faster access.

Subquery: A query that returns a table to another query.

Primary key: A tuple of columns that uniquely define each row in a table.

Foreign key: A tuple of columns identifying a relationship to another table.

The E-R Model

The E-R (entity-relationship) Model is an analysis tool for relational databases. E refers to entity: An object R refers to relationship: As in how objects relate to each other.

Each entity has properties associated with every instance of an object.

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/ER_Model.png)

Objects are related to each other through relationships:

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/ER_Model2.png)

E-R Relationships

  1. One to one relationships: For every instance of object A, there are exactly one instance of object B.
    • Every student has exactly one quest account
  2. One to many relationships: For every instance of object A, there are an indeterminate number of instances of object B.
    • A single course section has many students
  3. Many to one relationships: For many instances of object A, there are a single instance of object B.
    • Many students are enrolled in a course section
  4. Many to many relationships: For many instances of object A, there are a many possible instances of object B.
    • Many students take many courses

Normalization

Database Normalization is a process in relational databases to minimize redundacy. Data can be constructed so that additions, deletions, and modifications can be made in just one table.

Data goes through three forms until it is fully utilizied by a relational database:

  1. First Normalized Form: Each attribute contains only atomic values and only a single value from that domain. i.e. The data is in the form of a table with no merged columns or rows.
  2. Second Normalized Form: Each attribute in the table is dependent on the entity it is describing (dependent on the primary key).
  3. Third Normalized Form: All non-primary key attributes are not determined by any other non-primary key attributes.

Example: For each event for Stats Club we have: 1. a list of attendies along with some basic information (email, ect.), 2. the event’s name, type (social or education), the roles for Stats Club execs for the event, time and location of the event, and a budget, 3. as well as a list of expenses for the event

A report of the event would look something like:

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Event_report.png)

We first need to tabulate the data:

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Event_report_table.png)

However this is not enough, the data is not in a form that can be easily recognized by computers. - How do you add new events? - What about members that attend multiple events? - that attend no events?

1st Normalization

First Normalized Form: Each attribute contains only atomic values and only a single value from that domain

Example: To bring the data into 1st normalized form we need to break the table into two tables: Event, and Expenses:

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Event_report_table_1st.png)
![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Event_report_table_1st.png)
  • This contains all of the information before but is more organized for the computers to deal with
  • Still not enough, a mispelling of an event or type could make the database think there is a new event

2nd Normalization

Second Normalized Form: Each attribute in the table is dependent on the entity it is describing (dependent on the primary key).

Example: To bring the data into 2nd normalized form, we need to break the Event table again. Let’s break the table so we get important description of the events (name, type, presenter, organizer, etc.) and a list of members that attended each event.

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Event_report_table_2nd.png)
![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Event_report_table_2nd.png)
  • Attendance is 2nd normalized form if we consider the primary key as the tuple of event and quest ID.
  • Attendance still has redundant information, several members can attend multiple events or none at all

3rd Normalization

Third Normalized Form: All non-primary key attributes are not determined by any other non-primary key attributes.

Example: The information on each member (name, email, etc.) is not determined by the event. We need to break the attendance table to bring into 3rd normalized form: attendance and members.

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Event_report_table_3rd.png)

Primary Keys and Foriegn Keys

We can reconstruct the orignal table by joining tables, foreign keys with what they reference (primary keys). - We can only construct an instance of a foreign keys if their is already an instance of their reference - i.e. the set of foreign keys for one table is a subset of the primary keys they reference

Primary key: A tuple of columns that uniquely define each row in a table. (Red items below) Foreign key: A tuple of columns identifying a relationship to another table. (Blue items below)

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/primary_keys.png)

Quiz: Normalization

Normalize the following table:

Student NameQuest IDCourseDescriptionSection

Solutions are here

Relational Algebra Operations

Projection: Returns a subset of columns.

Selection: Returns only entities where some condition is true.

Rename: Rename an attribute.

Natural Join: Tuples from one table is joined to tuples from another table based on common attributes (at least one column with the same name and possible values is common between them)

Θ__-Join and Equijoin:__ Join tuples from two different tables where some binary condition (Θ = {≥, ≤, >, <, =}) between two tables attributes is true. When Θ is =, the join is called an equijoin.

Set Operations: Set theory’s unions, set difference, and cartesian product of tuples performed on tuples of different tables.

Quiz: Relational Algebra

Q1: What kind of operation is performed on the following tables?

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/quiz2a.png)

Q2: What kinds of joins can peform on tables?

Solutions are here

Constraints

Constraints limit what can be entered into fields in a table and help ensure encapsulation:

PRIMARY KEY constraints Uniquely identifies each record in a table (quickly referencing it)

FOREIGN KEY constraints Points to a PRIMARY KEY of another table, enabling them to easily join them

CHECK constraints Limits the range of values that a field can take.

UNIQUE constraints Enforces uniqueness on an field (column).

NOT NULL constraints Enforces a field to always contain a value.

  • We can also create indexes for fields making them easily searchable

SQL Language

  • Case does not matter, for presentation purposes UPPER CASE is used for SQL key words
  • SQL statements are processed as a whole (ignoring white space and new lines) and ends with a ‘;’

  • We will be used a database based on Stats Club, supplied with fake data:
![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/StatClubTables.png)

Data Types

In SQLite3, there are 5 basic data types:

  1. INTEGER The value is a signed integer.

  2. REAL The value is a floating point value, stored as an 8-byte IEEE floating point number.

  3. TEXT The value is a text string.

  4. BLOB The value is a blob of data, stored exactly as it was input.

  5. NUMERIC May contain integer or real values. The data is stored as text but converted as necessary.

SQL General Data Types

Data typeDescription
CHAR(n)Character string. Fixed length n
VARCHAR(n)Character string. Variable length <= n
BINARY(n)Binary string. Fixed length n
BOOLEANTRUE or FALSE values
VARBINARY(n)Binary string. Variable length <= n
INTEGER(p)Integer numerical (no decimal). Precision p
SMALLINTInteger numerical (no decimal). Precision 5
INTEGERInteger numerical (no decimal). Precision 10
BIGINTInteger numerical (no decimal). Precision 19
DECIMAL(p,s)Exact numerical. precision p, scale s
NUMERIC(p,s)Exact numerical. precision p, scale s
FLOAT(p)Floating point number. mantissa precision p
REALApproximate numerical. Mantissa percision 7
FLOATApproximate numerical. Mantissa percision 16
DATEStores year, month, and day values
TIMEStores hour, minute, and second values
TIMESTAMPStores year, month, day, hour, minute, and second values

NULL Values

NULL A NULL can be thought of as an unknown value. - Any datatype can have NULL values - if either x or y is NULL:

x+y => NULL x>y => NULL

SQL uses a three-value logic system: TRUE, FALSE, NULL:

ANDTRUEFALSENULL
TRUETFNULL
FALSEFFF
NULLNULLFNULL
ORTRUEFALSENULL
TRUETTT
FALSETFNULL
NULLTNULLNULL
NOTTRUEFALSENULL
FTNULL

Projection (SELECT Clause)

SELECT Statement: The SELECT statement returns a table of values (sometimes empty) as specified in the statement.

SELECT Clause: The SELECT clause of a SELECT statement specifies the columns for the result set.

  • We can also specify all columns from a table
  • If we leave out ‘’ we are effectively taking from an empty table

In SELECT clauses, we can specify more than just columns from a table: Literials: Strings, numbers that are repeated for every row Expressions: Expressions of columns/literals Functions: Built in functions in SQL (ROUND(), etc.) User Defined Functions: Functions that a user can create within SQL to run

Example: Let’s try something simple: some literials and a calculation of

12'this is a string'1/2
0 1 2 this is a string 0

Q: What happened here?

A: Since 1 and 2 are both integers, the expression 1/2 returns an integer. To get 0.5, we need to use a real number (a FLOAT).

12'this is a string'1/24/25/21/2.1./21/CAST(2 AS FLOAT)
0 1 2 this is a string 0 2 2 0.5 0.5 0.5

SQL statements ignore white spaces and new lines, the statement is only processed when after it sees ‘;’

Example: The following two statements produce the same table.

12'this is a string'1/2
0 1 2 this is a string 0
12'this is a string'1/2
0 1 2 this is a string 0

SQL Functions

There are many functions in SQLite and each implementation of SQL have different functions. Here are some random functions in SQLite:

FunctionDescription
ABS(col)Absolute value of numeric column
LENGTH(col)Return length of string column
LOWER(col)Return the string column in lower case
UPPER(col)Return the string column in upper case
RANDOM()A pseudo-random integer between -9223372036854775808 and +9223372036854775807
ABS(-8)LENGTH('This is a String')LOWER('ThIS Is A StRiNg')RANDOM()
0 8 16 this is a string 1085249690759383083

There are many more core functions within SQLite.

Quiz: SELECT Clause

Calculate the average of 2 random numbers.

Q: What is the upper and lower case for the string ‘UPPER or lower’?

Solutions are here

FROM Clause

FROM Clause: Specifies the table: either a persistant table, or a result set: a join of two or more tables or a subquery or some combination of the two.

Example: What are the names, type, times and locations for Stats Club?

nametypestart_timeend_timelocation
0 BOT social 2015-01-28 19:00:00 2015-01-28 22:00:00 C & D
1 EOT social None None None
2 Intro to Hadoop educational None None None
3 Intro to SQL educational 2015-02-05 18:00:00 2015-02-05 19:30:00 MC-3003
4 Prof Talk educational None None None
5 Intro to SQL 2 educational None None None
6 Prof Talk 2 educational None None None
7 Prof Talk 3 educational None None None

Quiz: FROM Clause

Q: Who are the execs for Stats Club, and what are their positions, and emails? - Projection of name, position, and email from the table exec

Solutions are here

Aggregate Functions

Aggregate Functions: Takes in the columns of a table and aggregates over the entries.

FunctionReturn value
AVG(column)Average of non-null values
COUNT(column)Count of non-null values
MAX(column)Maximum of values
MIN(column)Minimum of values
SUM(column)Sum of values
GROUP_CONCAT(column)Concatenation of column strings
  • There are more aggregate functions for other implementations of SQL

More detailed descriptions of the aggregate functions within SQLite can be found here.

COUNT( 12 )COUNT('ssdf')COUNT(NULL)SUM(23)SUM(0)SUM(NULL)AVG(0)AVG(NULL)
0 1 1 0 23 0 None 0 None

Lets work with some aggregate functions with the table below:

numberfloatingstring
0 1 23.23 this
1 3232-21.23 is
2 11 -2.00 a
3 -23 54.00 string
4 2 NaN concatenated
COUNT(*)COUNT(string)COUNT(floating)AVG(number)SUM(number)GROUP_CONCAT(string, ' ')
0 5 5 4 644.6 3223 this is a string concatenated

DISTINCT Prefix

In the SELECT clause we can specify to return only distinct tuples of columns

  • We can also use DISTINCT within aggregate functions making them only aggregate over distinct entries

Example: What events have members attended? - What are the distinct events where at least one member attended?

event
0 Intro to SQL
1 BOT
2 EOT
3 Intro to Hadoop
4 Intro to SQL 2
5 Prof Talk
6 Prof Talk 2

Quiz: DISTINCT

Q: What are the distinct majors of Stats Club members?

Q: How many distinct majors of Stats Club members are there?

  • DISTINCT can be within aggregate functions

Solutions are here

Alias

Column Alias

To increase the readability of SQL as well as the result set, we can give columns new names:

  • Column aliases make the final table more readiable and workiable for subqueries

Table Alias

We can also give tables new names as well:

  • Very useful when tables have common column names

Note: We can reference what table a column is coming from by a ‘.’

Example: If we give column aliases for the previous table, we make the result more interpretiable:

num_rowsnum_stringsnum_floatavg_integersum_intcat_string
0 5 5 4 644.6 3223 this is a string concatenated

Quiz: Aliases

Perform a query using a table alias, and use this table alias when referencing the column i.e. - This will be really important for bigger, more complicated queries

Solutions are here

Selection (WHERE Clause)

WHERE clauses filters the result set, removing rows where the condition returns either FALSE or NULL.

Example: What Stats Club events are social?

nametypestart_timeend_timelocation
0 BOT social 2015-01-28 19:00:00 2015-01-28 22:00:00 C & D
1 EOT social None None None

Prediate Operators

OperatorDescriptionExample
=Equal toWHERE gender = ‘M’
<>, !=Not equal toWHERE gender <> ‘M’
>Greater thanWHERE num > 5
<Less thanWHERE num < 5
>=Greater than or equal toWHERE num >= 5
<=Less than or equal toWHERE num <= 5
IS NULLValue is NULLWHERE num IS NULL
IS NOT NULLValue is not NULLWHERE num IS NOT NULL
BETWEENBetween a rangeWHERE num BETWEEN 3 AND 5
INIn a list of valuesWHERE num IN (3, 5, 8)
LIKEPattern SearchWHERE str LIKE ‘F%’
EXISTSSubquery have any rows?WHERE EXISTS (subquery)

LIKE Predicate

LIKE predicates

‘_’ means a character of any type ‘%’ means between 0 or more characters of any type

Example: What Stats Club members has a name begining with F?

quest_idnameemailfacultymajorpaidcard
0 fred Fred E. Finch fred@uwaterloo.ca Math Pure Math Y Y
1 frances Frances A. Miller frances@uwaterloo.ca Math Stats Y Y

Operator Modifiers

ALL, ANY or SOME Operator Modifiers

The operators =, <>, !=, >, <, >=, <= can be used with a list of values and the operators or .

  • SQLite does NOT have or implemented

ANY, SOME Operator returns true, if operator is true for any value in the set.

ALL Operator returns true, if operator is true for all values in the set.

Example: What are the expenses for non-social events? (, , and are not implmented in SQLite)

eventexpenseprice
0 Intro to SQL pizza 87.43
1 Intro to SQL pop 15.34
2 Intro to Hadoop coffee 23.12
3 Intro to Hadoop water 10.23
4 Intro to Hadoop donuts 53.23
5 Intro to Hadoop cookies 10.23
6 Intro to SQL: Basic Queries cookies 10.23
7 Intro to SQL: Basic Queries donuts 20.34
8 Intro to SQL: Basic Queries pop 21.54
9 Intro to SQL: Basic Queries water 10.52
10 Prof Talk pop 20.31
11 Prof Talk pizza 62.56
12 Prof Talk 2 pizza 61.56
13 Prof Talk 2 pop 15.65
14 Prof Talk 3 pizza 62.45
15 Prof Talk 3 pop 13.23
16 Intro to SQL: Advanced Queries cookies 10.23
17 Intro to SQL: Advanced Queries donuts 20.34
18 Intro to SQL: Advanced Queries pop 21.54
19 Intro to SQL: Advanced Queries water 10.52

AND, OR Operators

A group of filter conditions can be linked together with AND or OR operators.

Example: What Stats Club members has a name with it’s second letter as A or ends in B?

name
0 Darrell Aucoin
1 James M. Eddings
2 James A. Foxt
3 Daniel J. Moore
4 Nancy P. Jackson
5 Ralph L. Waldrop
6 Tameika M. McMaster
7 Janelle T. Smith
8 Ruben Lamb
9 Patrick Robertson

Quiz: Filtering (WHERE Clause)

Q: What events for Stats Club are introductory talks? - Introductory talk names start with ‘Intro’

Q: What Stats Club members have their first name starting with a letter BETWEEN A and G?

Solutions are here

GROUP BY Clause

  • GROUP BY clause groups the table by a column (or tuple of columns) and applies a function to each group
    • In the SELECT portion of the statement, you can only list the grouped column(s) and aggregate functions on them.
![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/Group By.png)

Aggregate Functions

Recall: Aggregate Functions: Takes in the columns of a table and aggregates over the entries. - If we use a GROUP BY clause, the aggregation will be over those groups

FunctionReturn value
AVG(column)Average of non-null values
COUNT(column)Count of non-null values
MAX(column)Maximum of values
MIN(column)Minimum of values
SUM(column)Sum of values
GROUP_CONCAT(column)Concatenation of column strings
  • There are more aggregate functions for other implementations of SQL

Example: What are the number of each type of event for Stats Club?

typenum_events
0 educational 6
1 social 2

HAVING Clause

HAVING clauses are very similar to WHERE clauses but can have aggregate function in their conditions.

You can have a WHERE and HAVING clause in the same statement.

Example: How many Stats Club members are in each major where the major has at least 2 members?

facultymajorCOUNT(*)
0 Math Act Sci 10
1 Math Applied Math 2
2 Math C & O 2
3 Math CS 3
4 Math Pure Math 2
5 Math Stats 14

GROUP BY with ROLLUP / CUBE

The ROLLUP operator produces a result set where the aggregate function is applied to each level of the GROUP BY hierachy. - The ROLLUP operator is NOT implemented in SQLite - Useful for making reports with totals and subtotals

Example: What are the number of Stats Club members in each faculty and major, including subtotals?

facultymajornum_members
0 Art Econ 1
1 Art None 1
2 Math Act Sci 10
3 Math Applied Math 2
4 Math C & O 2
5 Math CS 3
6 Math Pure Math 2
7 Math Stats 14
8 Math None 33
9 None None 34

Note that for Art and Math in faculty there is a row which has a NULL value. This is a total for those groups.

There is also one row with NULL values for faculty and major, this is the grand total of all members.

Quiz: Aggregation (GROUP BY Clause)

Q: What is the attendance for each Stats Club event?

Solutions are here

Joins

At times, we need information from multiple tables, to do this we need to join tables together. We can do this several ways:

  1. CROSS JOIN: The cartesian product of rows from each table.
  2. INNER JOIN: Join two tables on a join-predicate, losing rows when evaluated false/null.
  3. OUTER JOIN: Retains each record for the table(s) even when it has no matching rows from the other table. The returning table has null values for missing records.

    1. LEFT OUTER JOIN: Keep each record for first table but not the table it’s joining with.

    2. RIGHT OUTER JOIN: Keep each record for second table but not the table it’s joining with.

    3. FULL OUTER JOIN: Keep all record for all tables.

  4. NATURAL JOIN: Tables with the exact same column name and datatype are joined along that column.

CROSS JOIN

CROSS JOIN is the cartesian product of two tables

  • if table1 has n rows and table2 has m rows, then the result set has n * m rows

We can also get a CROSS JOIN by listing the tables seperated by a ‘,’

Example: Suppose we are creating a games tourtament between Stats Club members where every member play every other member once. How can we create such a table?

Player_1Player_2
0 Darrell Aucoin Darrell Aucoin
1 Darrell Aucoin Fred E. Finch
2 Darrell Aucoin Ryan T. Luby
3 Darrell Aucoin Billy L. Hunter
4 Darrell Aucoin John J. Oquendo
5 Darrell Aucoin Stephanie R. Matthews
6 Darrell Aucoin Robert B. Williams
7 Darrell Aucoin Austin K. Gilliard
8 Darrell Aucoin James M. Eddings
9 Darrell Aucoin Elaine S. Ott
10 Darrell Aucoin James A. Foxt
11 Darrell Aucoin Daniel J. Moore
12 Darrell Aucoin Kelly S. Ferguson
13 Darrell Aucoin Joseph L. Wood
14 Darrell Aucoin Vivian R. Donley
15 Darrell Aucoin Frances A. Miller
16 Darrell Aucoin Mina W. Lawrence
17 Darrell Aucoin Phillip C. Mascarenas
18 Darrell Aucoin Jeff M. Wright
19 Darrell Aucoin Deborah D. Helfrich
20 Darrell Aucoin Nancy P. Jackson
21 Darrell Aucoin Bobbie D. Mathews
22 Darrell Aucoin Arnold J. Fuller
23 Darrell Aucoin Melvin O. Martin
24 Darrell Aucoin Ralph L. Waldrop
25 Darrell Aucoin Mildred F. Hottinger
26 Darrell Aucoin Tameika M. McMaster
27 Darrell Aucoin Melissa R. Anderson
28 Darrell Aucoin Janelle T. Smith
29 Darrell Aucoin Ann W. McLaughlin
.........
1126 Patrick Robertson John J. Oquendo
1127 Patrick Robertson Stephanie R. Matthews
1128 Patrick Robertson Robert B. Williams
1129 Patrick Robertson Austin K. Gilliard
1130 Patrick Robertson James M. Eddings
1131 Patrick Robertson Elaine S. Ott
1132 Patrick Robertson James A. Foxt
1133 Patrick Robertson Daniel J. Moore
1134 Patrick Robertson Kelly S. Ferguson
1135 Patrick Robertson Joseph L. Wood
1136 Patrick Robertson Vivian R. Donley
1137 Patrick Robertson Frances A. Miller
1138 Patrick Robertson Mina W. Lawrence
1139 Patrick Robertson Phillip C. Mascarenas
1140 Patrick Robertson Jeff M. Wright
1141 Patrick Robertson Deborah D. Helfrich
1142 Patrick Robertson Nancy P. Jackson
1143 Patrick Robertson Bobbie D. Mathews
1144 Patrick Robertson Arnold J. Fuller
1145 Patrick Robertson Melvin O. Martin
1146 Patrick Robertson Ralph L. Waldrop
1147 Patrick Robertson Mildred F. Hottinger
1148 Patrick Robertson Tameika M. McMaster
1149 Patrick Robertson Melissa R. Anderson
1150 Patrick Robertson Janelle T. Smith
1151 Patrick Robertson Ann W. McLaughlin
1152 Patrick Robertson Judith B. Gibbons
1153 Patrick Robertson Ruben Lamb
1154 Patrick Robertson Dominick Byrd
1155 Patrick Robertson Patrick Robertson

1156 rows × 2 columns

However we have have players playing themselves, and rounds with the same players in opposite roles. We just need to filter these out.

Player_1Player_2
0 Darrell Aucoin Billy L. Hunter
1 Darrell Aucoin Austin K. Gilliard
2 Darrell Aucoin Daniel J. Moore
3 Darrell Aucoin Bobbie D. Mathews
4 Darrell Aucoin Arnold J. Fuller
5 Darrell Aucoin Ann W. McLaughlin
6 Fred E. Finch Darrell Aucoin
7 Fred E. Finch Billy L. Hunter
8 Fred E. Finch Austin K. Gilliard
9 Fred E. Finch Elaine S. Ott
10 Fred E. Finch Daniel J. Moore
11 Fred E. Finch Frances A. Miller
12 Fred E. Finch Deborah D. Helfrich
13 Fred E. Finch Bobbie D. Mathews
14 Fred E. Finch Arnold J. Fuller
15 Fred E. Finch Ann W. McLaughlin
16 Fred E. Finch Dominick Byrd
17 Ryan T. Luby Darrell Aucoin
18 Ryan T. Luby Fred E. Finch
19 Ryan T. Luby Billy L. Hunter
20 Ryan T. Luby John J. Oquendo
21 Ryan T. Luby Robert B. Williams
22 Ryan T. Luby Austin K. Gilliard
23 Ryan T. Luby James M. Eddings
24 Ryan T. Luby Elaine S. Ott
25 Ryan T. Luby James A. Foxt
26 Ryan T. Luby Daniel J. Moore
27 Ryan T. Luby Kelly S. Ferguson
28 Ryan T. Luby Joseph L. Wood
29 Ryan T. Luby Frances A. Miller
.........
531 Dominick Byrd Daniel J. Moore
532 Dominick Byrd Deborah D. Helfrich
533 Dominick Byrd Bobbie D. Mathews
534 Dominick Byrd Arnold J. Fuller
535 Dominick Byrd Ann W. McLaughlin
536 Patrick Robertson Darrell Aucoin
537 Patrick Robertson Fred E. Finch
538 Patrick Robertson Billy L. Hunter
539 Patrick Robertson John J. Oquendo
540 Patrick Robertson Austin K. Gilliard
541 Patrick Robertson James M. Eddings
542 Patrick Robertson Elaine S. Ott
543 Patrick Robertson James A. Foxt
544 Patrick Robertson Daniel J. Moore
545 Patrick Robertson Kelly S. Ferguson
546 Patrick Robertson Joseph L. Wood
547 Patrick Robertson Frances A. Miller
548 Patrick Robertson Mina W. Lawrence
549 Patrick Robertson Jeff M. Wright
550 Patrick Robertson Deborah D. Helfrich
551 Patrick Robertson Nancy P. Jackson
552 Patrick Robertson Bobbie D. Mathews
553 Patrick Robertson Arnold J. Fuller
554 Patrick Robertson Melvin O. Martin
555 Patrick Robertson Mildred F. Hottinger
556 Patrick Robertson Melissa R. Anderson
557 Patrick Robertson Janelle T. Smith
558 Patrick Robertson Ann W. McLaughlin
559 Patrick Robertson Judith B. Gibbons
560 Patrick Robertson Dominick Byrd

561 rows × 2 columns

INNER JOIN

INNER JOIN Joins two tables where the join condition returns true. Discarded when returning false or NULL.

ON Clause

The ON clause specifies the join condition:

  • The ON clause can use a multiple set of conditions connected by AND, OR

  • USING(

  • Some SQL implementations constructs the ON clause from the WHERE clause (DB2)

    • filtering by the WHERE clause gives the same result but in some implementations will product an intermediate cross product of tables (making the query slower)

Example: How many events does each member attend?

  • Note that this query does not include members who attended no events
    • Query is ordered by events_attended to show a comparison with a latter query
nameevents_attended
0 John J. Oquendo 1
1 James M. Eddings 2
2 Melissa R. Anderson 2
3 Melvin O. Martin 2
4 Mina W. Lawrence 2
5 Ann W. McLaughlin 3
6 Bobbie D. Mathews 3
7 Janelle T. Smith 3
8 Mildred F. Hottinger 3
9 Phillip C. Mascarenas 3
10 Ryan T. Luby 3
11 Vivian R. Donley 3
12 Arnold J. Fuller 4
13 Austin K. Gilliard 4
14 Jeff M. Wright 4
15 Kelly S. Ferguson 4
16 Nancy P. Jackson 4
17 Ralph L. Waldrop 4
18 Ruben Lamb 4
19 Billy L. Hunter 5
20 Deborah D. Helfrich 5
21 Dominick Byrd 5
22 Frances A. Miller 5
23 Fred E. Finch 5
24 Joseph L. Wood 5
25 Judith B. Gibbons 5
26 Robert B. Williams 5
27 Stephanie R. Matthews 5
28 Tameika M. McMaster 5
29 Elaine S. Ott 6
30 Daniel J. Moore 7
31 Patrick Robertson 7

OUTER JOIN

OUTER JOIN A join that returns all rows for 1 or 2 tables, even when there is no corresponding value. In these cases, NULL values are entered for these corresponding rows.

There are 3 types of OUTER JOINs:

  1. LEFT OUTER JOIN: An OUTER JOIN returning all rows of the table first mentioned.

  2. RIGHT OUTER JOIN: An OUTER JOIN returning all rows of the table second mentioned.

  3. FULL OUTER JOIN: An OUTER JOIN returning all rows of both tables.

  • Only LEFT OUTER JOIN is implemented in SQLite

Example: What are the names of Stat Club members and how many events they attended?

nameevents_attended
0 Darrell Aucoin 0
1 James A. Foxt 0
2 John J. Oquendo 1
3 James M. Eddings 2
4 Melissa R. Anderson 2
5 Melvin O. Martin 2
6 Mina W. Lawrence 2
7 Ann W. McLaughlin 3
8 Bobbie D. Mathews 3
9 Janelle T. Smith 3
10 Mildred F. Hottinger 3
11 Phillip C. Mascarenas 3
12 Ryan T. Luby 3
13 Vivian R. Donley 3
14 Arnold J. Fuller 4
15 Austin K. Gilliard 4
16 Jeff M. Wright 4
17 Kelly S. Ferguson 4
18 Nancy P. Jackson 4
19 Ralph L. Waldrop 4
20 Ruben Lamb 4
21 Billy L. Hunter 5
22 Deborah D. Helfrich 5
23 Dominick Byrd 5
24 Frances A. Miller 5
25 Fred E. Finch 5
26 Joseph L. Wood 5
27 Judith B. Gibbons 5
28 Robert B. Williams 5
29 Stephanie R. Matthews 5
30 Tameika M. McMaster 5
31 Elaine S. Ott 6
32 Daniel J. Moore 7
33 Patrick Robertson 7

Natural Join

NATURAL JOIN A join condition that lets the server decide on the join conditions based on the same column names and types across columns for the tables.

Example: What are the position and duties of each Stats Club exec?

namepositionduties
0 Darrell Aucoin President To be aware of MathSoc's Policies and Bylaws i...
1 Darrell Aucoin President To call and preside over general meetings.
2 Darrell Aucoin President To manage the executive team and the strategic...
3 Darrell Aucoin President To post announcements of all club meetings, an...
4 Judith B. Gibbons Events To assist the president and other vice-preside...
5 Judith B. Gibbons Events To chair the organization and promotion of lea...
6 Lamar Roy Finance To ensure membership fees are collected and ma...
7 Lamar Roy Finance To keep an up-to-date record of financial tran...
8 Lamar Roy Finance To prepare a summary of the financial records ...
9 Lamar Roy Finance To prepare the budget at the beginning of term.
10 Lamar Roy Finance To volunteer as president in the absence of th...
11 Gilberto Cross Events To assist the president and other vice-preside...
12 Gilberto Cross Events To chair the organization and promotion of lea...
13 Melba Lane President To be aware of MathSoc's Policies and Bylaws i...
14 Melba Lane President To call and preside over general meetings.
15 Melba Lane President To manage the executive team and the strategic...
16 Melba Lane President To post announcements of all club meetings, an...
17 Ruben Lamb Technology Maintain and update the club website.
18 Ruben Lamb Technology Maintain any hardware, software, or technology...
19 Ruben Lamb Technology Perform the duties of a Vice President - Event...
20 Patrick Robertson Events To assist the president and other vice-preside...
21 Patrick Robertson Events To chair the organization and promotion of lea...
22 Dominick Byrd Events To assist the president and other vice-preside...
23 Dominick Byrd Events To chair the organization and promotion of lea...

Quiz: Joining Tables

Q: What are the email addresses and phone numbers of stats club execs who are in change or organizing at least one event?

![alt text](https://raw.githubusercontent.com/NormallySane/IntroSQL/master/Images/StatClubTables2.png)

Solutions are here

Subqueries

Subqueries are queries contained in queries. These subqueries are contained in ‘(‘, ‘)’

There are two types of subqueries:

  1. Non-Correlated Subqueries: Can be run independently of the larger query.

  2. Correlated Subqueries: Must be run concurrently with the outer query. They are dependent on the outer query.

Non-Correlated Subqueries

Non-Correlated Subquery: Any valid query within query that if executed by itself will produce a result (including empty set). These are enclosed in ‘(‘, ‘)’ in FROM, WHERE, or HAVING clauses.

Example: What Stats Club execs are in charge of making posters?

CHAPTER 5 Hands-On Assignments 1. Add a new row in the ORDERS table with the following data: Order# = 1021, Customer# = 1009, and Order date = July 20, 2009. Answer: insert into Orders (Order#, Customer#, OrderDate) Values ('1021', '1009', '20-JUL-09'); 2. Modify the zip code on order 1017 to 33222. Answer: Update Orders set shipzip = '33222' where order# = '1017'; 3. Save the changes permanently to the database. Answer: Commit; 4. Add a new row in the ORDERS table with the following data: Order# = 1022, Customer# = 2000, and Order date = August 6, 2009. Describe the error raised and what caused the error. Answer: Error report: SQL Error: ORA-02291: integrity constraint (HR.ORDERS_CUSTOMER#_FK) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *