Structured Query Language

Section 0: Module Objectives or Competencies

Section 1: Overview of the Structured Query Language

Structured Query Language (SQL) pronounced as "S-Q-L" or sometimes as "Sequel" is the standard language for dealing with Relational Databases

• SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987, and there have been ongoing updates.
• The latest SQL version is ISO/IEC 9075:2016.
• Each RDBMS's implementation of SQL is slightly different, typically providing a superset of SQL commands, but the core language is the same.

Overview

• SQL is designed for data manipulation in a relational database
• It is a nonprocedural language – specify what, not how

Justification

Programmers, database administrators, and business analysts alike use SQL to access information.

• A working knowledge of the language is necessary to anyone who interacts with a database.
• SQL has roots that extend back to the 1970’s and the language has remained popular since this time.

Learning SQL increases your marketability. Some of the most common positions that require knowledge of SQL include:

• Back-end developer – responsible for managing the code that ensure that web applications interact with databases correctly.
• Database administrator – make certain that data is appropriately stored, managed, and secured.
• Data analyst – responsible for analyzing data for relevant trends so that other departments can operate more effectively.
• Data scientist – similar to an analyst position, workers in the role are tasked with handling data in great amounts.

Technological Uses

SQL has countless technological uses

• SQL is the default code language for retrieving data from or interfacing with many databases.
  • Numerous types of databases including Microsoft SQL Server, MySQL, and PostgreSQL (pronounced Post-Gres-Q-L) support innumerable companies of various sizes.
• SQL can also be found in other types of technology including iPhones and Android phones.

Here are some additional applications of SQL:

• data mining – Learning SQL will allow information to be unearthed from data with greater efficiency. Using simple queries makes it possible to identify specific data at time intervals, view update events, monitor table activity, and so much more. Extracting key trends and performance indicators from the mass of data that is collected allows a business to operate more effectively.
• data manipulation – SQL is well suited to data manipulation because it provides an ease of viewing and interacting with data, making it easier to test and manipulate the data. Further, data stored in SQL is dynamic, meaning it can be changed and manipulated at any time using basic queries.
• combining data from different sources – bringing together data from two or more sources can be a time-consuming and frustrating task, but SQL facilitates the process because it supports simple "unions" in which the specified fields or entire databases can be joined.
• managing large collections of data – although NoSQL (non-relational) databases have emerged to handle the large amounts of data associated with "Big Data", relational databases and SQL are resurging. All of the major cloud providers now offer popular managed relational database services: e.g., Amazon RDS, Google Cloud SQL, and Azure Database for PostgreSQL. According to the Amazon CTO, its PostgreSQL- and MySQL-compatible Aurora database product has been the "fastest growing service in the history of AWS". SQL interfaces on top of Hadoop, Spark, and Kafka continue to thrive. SQL can handle data pools greater than 100 million records, making it a popular choice for data analysis.
• Internet usage – SQL is used in the three-tiered Internet architecture comprising of client, application server, and a database, used to support powerful web applications such as banking or shopping websites and even simple WordPress blogs.

Database? Meh...

Knowledge of database concepts is critical. StackOverflow Insights explains that technologies cluster together into related ecosystems that tend to be used by the same developers.

• The above network graph demonstrates related ecosystems by showing which technologies are most highly correlated with each other.
• Note that the red dots indicate databases.
• We see a large cluster on the left for web development (with JavaScript, HTML/CSS, TypeScript, and React.js) connected via SQL to one for Microsoft technologies (with C#, Visual Studio, and .NET Core).
• Along the lower left we see a mobile constellation connecting Java, Kotlin, Android, and SQLite to iOS and Xcode.
• We observe a cluster of operations technologies with Docker, AWS, and PostgreSQL connected to the Python ecosystem network through Linux.
• Other smaller correlated clusters include Scala/Spark/Hadoop, C/C++/Assembly, and smaller technologies like language-specific IDEs or frameworks.

Clearly SQL knowledge is vital to multiple technology ecosystems.

Section 2: Categories of Commands

SQL commands fall into three categories:

1. Data definition – commands to create database and table structures

   CREATE DATABASE <database name>
   CREATE TABLE <table name>

2. Data management – commands to enter, correct, delete, and update data within database tables

   INSERT
   COMMIT
   UPDATE
   DELETE
   ROLLBACK

3. Data query – commands to explore database contents and convert data to information

   SELECT

Section 3: Data Types

Here is a description of the Numeric types supported by MySQL.

• Note that in MySQL (and MariaDB), the length component of NUMBER and DECIMAL does not count the decimal point.
• For example, a DECIMAL(18,9) column has nine digits on either side of the decimal point.

This link describes the String, or Character, types supported in MySQL.

MySQL supports Date and Time types as well.

MySQL does not have built-in Boolean type, but uses TINYINT(1) instead. MySQL provides BOOLEAN or BOOL as the synonym of TINYINT(1).

• In MySQL, zero is considered as false, and non-zero value is considered as true.
• To use Boolean literals, you use the constants TRUE and FALSE that evaluate to 1 and 0 respectively.

SELECTING DATA TYPES

While determining the appropriate data type is typically fairly obvious, what about numeric data like zip codes, phone number, credit card number, or social security number?

• No brainer right? They contain only digits and therefore are obviously numeric!
• Not so fast! Typically if you are not going to use a variable that contains numeric data in a calculation (i.e., the variable does not represent an amount or quantity), then it should be declared as a character type.
  • Here is a discussion of this topic.
  • There are exceptions:
    • If the variable is to be a key, either make it numeric or make sure you have a secondary key that is integer. If not, it is a performance hog.
    • It is generally more efficient to use a numeric index, because a string variable may lead to difficulties with access predicates.
  • Further, if there is ever any chance that characters like parentheses, commas, periods, and dashes will be included in the data, you cannot use numeric types.
    • This could come into play when you start to consider internationalization.
      • Some countries format phone numbers like 208.282.4783 or even +33 (0)6 12 34 56 78.
      • Until about the 1950s, US phone numbers were alphanumeric, eventually standardizing on a 2-letter, 5-number system. Do any countries currently do that?
      • Post codes are also an issue. Canadian zip codes include letters!

Section 4: Data Definition

Creating Database Structure

CREATE SCHEMA AUTHORIZATION <creator>;

...or...

CREATE DATABASE <database name>;

Creating Table Structure

CREATE TABLE <table name> (
attr1 char1 spec_req1,
attr2 char2 spec_req2,
  .
  .
attrn charn spec_reqn );

The following examples are based on this database model:

1:M Example: CUSTOMER table

CREATE TABLE CUSTOMER (
CUS_CODE INTEGER PRIMARY KEY,
CUS_LNAME VARCHAR(15) NOT NULL,
CUS_FNAME VARCHAR(15) NOT NULL,
CUS_INITIAL CHAR(1),
CUS_AREACODE CHAR(3),
CUS_PHONE CHAR(8) NOT NULL,
CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00,
CONSTRAINT CUS_UNIQUE_FIELDS UNIQUE(CUS_LNAME,CUS_FNAME, CUS_PHONE));

Note that the CREATE TABLE command allows constraints to be defined in two different places:

• In the column definition (known as a column constraint) as in the CUS_CODE above.
• Using the CONSTRAINT keyword (known as a table constraint) as in the final line of the example.
  • You can optionally specify constraint names by preceding the constraint definition with the CONSTRAINT keyword followed by the name.

The preceding SQL command results in the following table:

1:M Example: INVOICE table

CREATE TABLE INVOICE (
INV_NUMBER INTEGER PRIMARY KEY,
CUS_CODE INTEGER NOT NULL,
INV_DATE DATETIME NOT NULL,
CONSTRAINT FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER(CUS_CODE));

Since the INVOICE table's foreign key, CUS_CODE, comes from the CUSTOMER table's primary key, the INVOICE table must be created after the CUSTOMER table.

• Since a CUSTOMER can have many INVOICES, but an INVOICE is associated with only one CUSTOMER, CUSTOMER is in a 1:M relationship with INVOICE.
• Recall that the primary key of the one must appear as a foreign key in the table of the many.

The preceding SQL command results in the following table:

1:M Example: VENDOR table

CREATE TABLE VENDOR (
V_CODE INTEGER NOT NULL UNIQUE,
V_NAME VARCHAR(35) NOT NULL,
V_CONTACT VARCHAR(15) NOT NULL,
V_AREACODE CHAR(3) NOT NULL,
V_PHONE CHAR(8) NOT NULL,
V_STATE CHAR(2) NOT NULL,
V_ORDER CHAR(1) NOT NULL,
PRIMARY KEY (V_CODE));

Notice that a different syntax is used to create this primary key.

The preceding SQL command results in the following table:

1:M Example: PRODUCT table

CREATE TABLE PRODUCT (
P_CODE VARCHAR(10) PRIMARY KEY,
P_DESCRIPT VARCHAR(35) NOT NULL,
P_INDATE DATETIME NOT NULL,
P_QOH INTEGER NOT NULL,
P_MIN INTEGER NOT NULL,
P_PRICE NUMERIC(8,2) NOT NULL,
P_DISCOUNT NUMERIC(4,2) NOT NULL,
V_CODE INTEGER,
CONSTRAINT FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE));

Again, the VENDOR table has to be created before the PRODUCT table, because the primary key of the VENDOR table appears as a foreign key in the PRODUCT table.

• VENDOR has a 1:M relationship with PRODUCT, and in a 1:M relationship the primary key of the one must appear as a foreign key in the table of the many.

The preceding SQL command results in the following table:

M:N Example: LINE table

CREATE TABLE LINE (
INV_NUMBER INTEGER NOT NULL,
LINE_NUMBER NUMERIC(2,0) NOT NULL,
P_CODE VARCHAR(10) NOT NULL,
LINE_UNITS NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
LINE_PRICE NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (INV_NUMBER,LINE_NUMBER),
FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE (INV_NUMBER) ON DELETE CASCADE,
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT UNIQUE (INV_NUMBER, P_CODE));

The LINE table is a composite entity or associative entity because the LINE table is required to model the M:N relationship between INVOICE and PRODUCT.

• Remember that a composite entity is an entity whose primary key is composed of a combination of the primary keys of the entities being linked.
• Hence, the LINE entity has as its primary keys the combination made up of the primary key from PRODUCT, which is P_CODE, and the primary key from INVOICE, or INV_NUMBER.
• In addition, P_CODE serves as a foreign key link to PRODUCT, and INV_NUMBER provides a foreign key link to INVOICE.
• That said, be sure to notice in the above SQL statement that the PRIMARY KEY is specified in the seventh line, and the foreign keys are specified in lines 8 and 9.

The preceding SQL command results in the following table:

1:1 Example: EMPLOYEE and PILOT tables

In the discussion of supertypes and subtypes and elsewhere, we encountered 1:1 relationships.

• We explained that the foreign key is placed in the most frequently accessed entity, as in the example below.
• EMP_NUM is the primary key of the EMPLOYEE table, but in the PILOT table the EMP_NUM serves as both the primary key as well as the foreign key.

CREATE TABLE IF NOT EXISTS EMPLOYEE (
EMP_NUM INT NOT NULL,
EMP_LNAME VARCHAR(15),
EMP_FNAME VARCHAR(15),
EMP_INITIAL VARCHAR(1),
EMP_HIRE_DATE DATETIME,
EMP_TYPE VARCHAR(10),
CONSTRAINT PRIMARY KEY (EMP_NUM)
);

CREATE TABLE PILOT (
EMP_NUM INT NOT NULL,
PIL_LICENSE VARCHAR(25) NOT NULL,
PIL_RATINGS VARCHAR(25),
PIL_MED_TYPE VARCHAR(1),
CONSTRAINT PRIMARY KEY (EMP_NUM),
CONSTRAINT FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE
);

Here is a script for table creation:

Notes:

• You can include IF NOT EXISTS after the table name to prevent an error from occurring if the table exists.
• NOT NULL specification ensures that the primary key cannot be null, thereby enforcing entity integrity.
• Specification of the primary key as UNIQUE results in automatic enforcement of entity integrity.
• You can specify AUTO_INCREMENT on your primary keys in MySQL to generate a unique identity for new rows. See this link to set a starting value.
• Specification of the foreign key allows the enforcement of referential integrity.
• Attribute descriptions are enclosed in parentheses.
• Attribute descriptions are separated by commas.
• The command sequence is terminated by a semicolon, like Oracle, but some RDBMSs do not require it.

Copying a Table's Structure: Shallow Cloning

Shallow cloning creates a copy of an existing table data structure and column attributes without the data being copied. This will create an empty table based on the structure of the original table.

CREATE TABLE new_table LIKE original_table;

To get a description of a table's structure, use the DESCRIBE command.

DESCRIBE PRODUCT;

To get a list of all tables in a database, use the SHOW TABLES command.

SHOW TABLES IN SPRINGCATALOG;

If you are using MySQL, attribute names with a hash mark (#), like emp#, will cause problems if you do not enclose them in backquotes (the key to the left of the 1/! on your keyboard), like `emp#`.

Section 5: Data Management

DATA ENTRY

SQL requires the use of the INSERT command to enter data into the table.

INSERT lets you insert data into the table, one row at a time. It is used to make the first data entry into a new table structure or to add data to a table that already contains data.

(NOTE that SQL data entry is not noted for ease of use, so the input utilities available with the RDBMS are preferred.)

INSERT INTO PRODUCT
VALUES ('11-QER/31', 'Power Painter, 15 psi., 3 nozzle', '2015-11-03', 8, 5, 109.99, 0.00, 25595);

INSERT INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE)
VALUES ('13-Q2/P2', '7.25-in. pwr. saw blade', '2015-12-13', 32, 15, 14.99, 0.05, NULL);

Notes:

• Attribute names are optional, and should be enclosed in parentheses and separated by commas.
• The row contents are entered between parentheses. The first character after VALUES is an open parenthesis and the last character in the command sequence is a close parenthesis.
• Character (string) data values must be entered between apostrophes (').
• Numeric entries are NOT enclosed in apostrophes.
• A NULL value is not enclosed in apostrophes.
• Oracle and MySQL require the apostrophes around the date.
• Access requires pound signs (#) around the date.
• Attribute entries are separated by commas.

---

Here is a script for data entry:

---

MAKING CORRECTIONS

UPDATE enables the user to make changes in the data.

Task: find the record with the primary key P_CODE = '13-Q2/P2' and changes P_INDATE.

UPDATE PRODUCT
SET P_INDATE = '2016-01-18'
WHERE P_CODE = '13-Q2/P2';

Notes:

• If multiple attributes are to be changed, they can be separated by commas.

  UPDATE PRODUCT
  SET P_INDATE = '2016-01-18', P_PRICE = 15.99, P_MIN = 10
  WHERE P_CODE = '13-Q2/P2';

---

WHERE Clause

Note the use of the WHERE clause, which is used to specify conditions that must be met by a tuple's data in order for that tuple to be affected by the query.

---

DELETING TABLE ROWS

DELETE makes it possible to delete all table rows or those that meet the conditions specified in the WHERE clause.

Note:

• Use of a primary key (and the = operator) allows deletion of single record.

DELETE FROM PRODUCT
WHERE P_CODE = '2238/QPD';




Note:

• Use of non-key attributes allows deletion of multiple records.

DELETE FROM PRODUCT
WHERE P_MIN = 5;




DELETE can omit the WHERE clause if all table rows are to be deleted.

DELETE FROM PRODUCT;


Note:

• When there is no WHERE clause all rows will be removed from the table.
• This deletes all rows in a table without deleting the table, meaning that the table structure, attributes, and indexes remain intact.

---

SAVING TABLE CONTENTS

COMMIT <table name> saves the table contents to disk.

Table contents are not saved unless the COMMIT command is issued, the database is closed, or SQL is terminated.

COMMIT PRODUCT;

The ultimate purpose of the COMMIT command is the assurance of database update integrity in transaction management.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:

SET autocommit=0;

---

RESTORING TABLE CONTENTS

ROLLBACK is used to restore a database to its previous state.

• If a COMMIT command has not yet been used to permanently save changes in the table, the database can be restored to its previous condition with the ROLLBACK command.
• SQL assumes the current table is the one to be restored.

Section 6: Data Queries

CHECKING TABLE CONTENTS

The SELECT command lists the table contents. SELECT can be followed by a set of all attributes to be listed or by the wildcard symbol * if all attributes are to be listed.

SELECT * FROM PRODUCT;

...or...

SELECT P_DESCRIPT, P_INDATE, P_PRICE, ..., V_CODE
FROM PRODUCT;

Notes:

• Although SQL commands may be issued on a single line, command sequences are best structured when the SQL command's components are shown on separate lines.

The following Learn Basic SQL in 10 Minutes video provides a very clear explanation of SELECT statements. And be sure to check out the fancy logo!

PARTIAL LISTING OF TABLE CONTENTS

The WHERE clause can be used in conjunction with the SELECT command to limit the table contents included in the output:

SELECT <column(s)>
FROM <table name>
WHERE <conditions>;

Example:

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;

Section 7: Data Queries – Relational Operators

Relational (comparison) operators can also be used to test for the truth of some condition. Comparison operators return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

Example:

SELECT P_DESCRIPT, P_QOH, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344;

Example:

SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_PRICE <= 10;

Example:

SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE < '1558-QW1';

Notes:

• Character attributes are compared on the basis of their ASCII codes.
  • For example, the character 'A' has a code 65, while an 'a' has code 97.
  • A '1' has code 49.
  • All other character symbols have similar codes, and character strings are compared from left to right, character by character.
  • Therefore 'zebra' is greater than 'giraffe' but less than 'zeus'. Such comparisons can be used to generate sorted lists.
  • While '5' is greater than '4', it is also greater than '44'.

Example:

Task: list all records in which the stock dates occur on or after January 20, 2016.

SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE
FROM PRODUCT
WHERE P_INDATE >= '2016-01-20';

Section 8: Data Queries – Logical Operators

SQL allows the use of the logical operators NOT, AND, and OR to test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

• NOT – negates the condition.
• AND – requires that both parts of the condition be true.
• OR – requires that either part of the condition be true.

Example:

Task: list records with a V_CODE field of either 21344 or 24288.

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 24288;

Example:

Task: list all rows for which the PRICE is less than $50.00 and for which P_INDATE occurs on or after January 20, 2016.

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE < 50 AND P_INDATE >= '2016-01-20';

Example:

Task: list all rows for which the vendor code is not 21344.

SELECT P_DESCRIPT, P_QOH, P_PRICE, V_CODE
FROM PRODUCT
WHERE NOT (V_CODE = 21344);

Logical operators can be combined to place complex restrictions on the output.

Example: list all rows that meet the following conditions:

• P_INDATE on or after January 20, 2016 and P_PRICE less than $50.00.
• Or, a V_CODE of 24288.

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE < 50 AND P_INDATE >= '2016-01-20')
OR V_CODE = 24288;

Section 9: Data Queries – Arithmetic Operators

SQL allows the use of arithmetic operators with table attributes in a column list or in a conditional expression.

Example:

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE
FROM PRODUCT

• To make the output more readable, the SQL standard permits the use of aliases for any column in a SELECT statement.
• An alias is an alternative name given to a column or table in any SQL statement.
  • If your column alias is one word only, delimiters are not required.
  • However, if it consists of multiple words it must be enclosed in identifier quote characters.
    • The identifier quote character for MySQL and MariaDB is the backtick (`).
    • The backtick is on the upper left of most keyboards.
    • More on backticks can be found in Single quotes, Double quotes and Backticks in MySQL.

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE AS `TOTAL VALUE`
FROM PRODUCT

Note that any extraneous digits to the right of the decimal can be eliminated using the ROUND function, as in the following query:

SELECT P_DESCRIPT, P_QOH, P_PRICE,
ROUND(P_QOH * P_PRICE, 2) AS TOTVALUE
FROM PRODUCT

Example:

Task: list all products for which the quantity on hand is within five of the minimum quantity.

SELECT P_DESCRIPT, P_CODE, P_QOH, P_MIN
FROM PRODUCT
WHERE P_QOH < P_MIN + 5;

Notes:

• Demonstrates an arithmetic operator used in conjunction with a relational operator.

Section 10: Data Queries – Advanced Operators

There are advanced comparison keywords (sometimes referred to as "special operators" or "advanced logical operators") that are used to enhance the search capabilities of a SQL query.

BETWEEN

BETWEEN checks to see if a value is between value1 and value2 inclusive.

• Conditions involving characters allow the use of wildcards in MySQL:
  • % matches any and all following characters
  • _ matches any one character

Example:

SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 and 100.00;

Lists all products with a price greater than or equal to 50 and less than or equal to 100.

Example:

SELECT P_DESCRIPT
FROM PRODUCT
WHERE P_DESCRIPT BETWEEN "B%" AND "D%";

Lists the product description for all products whose descriptions fall between 'B' and 'D', inclusive.

IS NULL

IS NULL is used to check for null attribute values.

Example:

SELECT P_CODE, P_DESCRIPT
FROM PRODUCT
WHERE V_CODE IS NULL;

LIKE

LIKE is used to check for similar character strings.

• Character comparisons are case sensitive. SMITH <> Smith
• The UPPER function will convert both table character entries and query character entries to uppercase. 'SMITH' = UPPER('Smith')

Example:

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM VENDOR
WHERE V_CONTACT LIKE 'Smith%';

Notes:

• Returns Smith, Smithson.

Example:

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM VENDOR
WHERE V_CONTACT NOT LIKE 'Smith%';

Notes:

• Returns all other vendors.

Example:

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM VENDOR
WHERE V_CONTACT LIKE 'Sm_th%';

Notes:

• Returns Smith, Smithson, Smythe.
• FYI: Pronunciation of Smythe

IN

IN is used to check whether or not an attribute value matches any of the values contained within a set of listed values.

Example:

SELECT *
FROM PRODUCT
WHERE V_CODE IN (21344, 24288);

Returns all tuples with a V_CODE that equals 21344 or 24288.

The IN operator is especially useful when it is used in conjunction with subqueries (or nested queries).

SELECT V_CODE, V_NAME
FROM VENDOR
WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT);

Notes:

• Returns only those vendors who provide products.

NOT IN is also valid.

EXISTS

EXISTS is used to check whether an attribute has a value.

• IS NOT NULL is the same as EXISTS.

DELETE FROM PRODUCT
WHERE P_CODE EXISTS;

The EXISTS operator can be used whenever there is a requirement to execute a command based on the result of another query.

• In other words, if a subquery returns any results, run the main query; otherwise do not.

Example:

SELECT *
FROM VENDOR
WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH <= P_MIN);

Notes:

• The query above will list all vendors, but only if there is a need to order products due to inventory levels below the minimum threshold.

Section 11: Data Queries – Joining Tables

The normalization process was required to reduce anomalies and to ensure that each relation contained data about a single entity only.

• However, the data still needs to be "linkable" even after the normalization process has been completed.
• That is there the JOIN comes into play.
• Recall this discussion about linking tables, as well as the relational algebra JOIN discussion.
• Again, the ability to link, or JOIN, tables is the power behind relational databases.

The ability to combine (JOIN) tables on common attributes is one of the most important distinctions between relational databases and others.

• The concept of joining tables via common attributes refers to linking the primary key in the "one" table to the foreign key in the "many" table, i.e., controlled redundancy.

A JOIN is performed when data are retrieved from more than one table at a time.

Visualization of Inner Join:

Another Visualization of Inner Join:

This type of JOIN is referred to as a Natural Join or an Inner Join, or maybe even an EQUI JOIN.

Syntax using WHERE:

SELECT <COLUMN₁>, <COLUMN₂>, ... <COLUMN_n>
FROM <TABLE₁>, <TABLE₂>
WHERE <TABLE₁>.<FOREIGN_KEY> = <TABLE₂>.<PRIMARY_KEY>;

The syntax using INNER JOIN is shown in the next section.

In order to perform a successful JOIN, the source tables of the attributes listed in the SELECT are specified.

• Most current RDBMS packages do not require table names to be used as prefixes, unless the same attribute name occurs in multiple tables.
• If they are included, the syntax puts the data source (table) before the attribute, separating the table name from the attribute name with a period.

To list information from the PRODUCT and VENDOR databases, the V_CODE is used to establish the link:

SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT,VENDOR.V_AREACODE, VENDOR.V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

- or -

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

• The preceding command joins a row in the PRODUCT table with a matching row in the VENDOR table's V_CODE column.
• Each V_CODE in VENDOR can be matched with many V_CODE rows in PRODUCT.
• If the WHERE clause is omitted, the result is a PRODUCT rather than a JOIN!
• All of the SQL commands can be used on joined tables.

An alias (alternate name) may be used to identify the source(s) from which the data are taken.

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT P, VENDOR V
WHERE P.V_CODE = V.V_CODE;

A more predictable list can be generated by using the ORDER BY condition:

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE
ORDER BY P_PRICE;

Task: list PRODUCT and VENDOR information for all products stocked after January 15, 2016.

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE
AND P_INDATE > '2016-01-15';

Note that while PVC pipe, 3.5-in., 8-ft was stocked on February 20, 2016, it is not included because it has a NULL V_CODE field.

Click for my explanation of how to write a JOIN.

Video Tutorials

For supplemental explanations, view the videos below. The order in which they are listed reflects my opinion of the videos.

SQL Joins Explained (Goofy intro, but good explanation)

Follow-up Video - Discussion of Other Types of Joins (Still goofy, but good)

Three-table Join

Inner Join

Inner Join with Using

Links to Further Explanations

• A step-by-step walkthrough of SQL Inner Join
• SQL multiple joins for beginners with examples
• Learn SQL: Join multiple tables
• SQL Join types overview and tutorial

Section 12: Data Queries – Explicit Inner Join

The preceding type of Join is known as an Inner Join, so the SQL-92 standard introduced the SQL statement INNER JOIN.

• Instead of using the WHERE clause to join tables, the INNER JOIN is used.
• While the result is the same, the syntax is considerably different.

Syntax using WHERE:

SELECT <COLUMN₁>, <COLUMN₂>, ... <COLUMN_n>
FROM <TABLE₁>, <TABLE₂>
WHERE <TABLE₁>.<FOREIGN_KEY> = <TABLE₂>.<PRIMARY_KEY>;

Syntax using INNER JOIN:

SELECT SELECT
FROM <TABLE₁> INNER JOIN <TABLE₂>
ON <TABLE₁>.<FOREIGN_KEY> = <TABLE₂>.<PRIMARY_KEY>;

In order to convert a JOIN that uses the WHERE clause to an INNER JOIN

• replace the comma between the table names with INNER JOIN and
• replace the WHERE with ON

Remember that every INNER JOIN must be paired with an ON.

Joins between more than two tables can become fairly complicated, as seen in the examples that follow.

The following examples use the table schema below:

Example: Get the name of all suppliers that have shipped to London (script).

Using WHERE ("old style" join, implicit join):

SELECT DISTINCT Suppliers.SupplierName
FROM Suppliers, Shipments, Projects
WHERE (Suppliers.SupplierNum = Shipments.SupplierNum) AND
(Shipments.ProjectNum = Projects.ProjectNum) AND
(Projects.city = "London");

Using INNER JOIN and ON (when you are joining table1 and table2 on a column with the same name or different names in the two tables):

SELECT DISTINCT SupplierName
FROM Suppliers
INNER JOIN Shipments ON Suppliers.SupplierNum = Shipments.SupplierNum
INNER JOIN Projects ON Shipments.ProjectNum = Projects.ProjectNum
WHERE Projects.city = "London";

Using INNER JOIN and USING (USING is simply a shortcut when you are joining table1 and table2 on a column with the same name in both tables):

SELECT DISTINCT SupplierName
FROM Suppliers
INNER JOIN Shipments USING(SupplierNum)
INNER JOIN Projects USING(ProjectNum)
WHERE Projects.city = "London";

Example: List all those parts supplied by a supplier to a project in the same city:

Using WHERE:

SELECT DISTINCT Shipments.partNum, Suppliers.supplierNum, Projects.projectNum, Projects.city, Suppliers.city, Parts.city
FROM Parts, Shipments, Projects, Suppliers
WHERE (Parts.partNum = Shipments.partNum) AND
(Shipments.projectNum = Projects.projectNum) AND
(Shipments.SupplierNum = Suppliers.SupplierNum) AND
(Suppliers.city = Projects.city) and (Suppliers.city = Parts.city);

Using INNER JOIN:

SELECT DISTINCT Shipments.PartNum, Suppliers.SupplierNum, Projects.ProjectNum, Projects.City, Suppliers.City, Parts.City
FROM Parts
INNER JOIN Shipments ON (Parts.partNum = Shipments.partNum)
INNER JOIN Projects ON (Shipments.projectNum = Projects.projectNum)
INNER JOIN Suppliers ON (Shipments.SupplierNum = Suppliers.SupplierNum)
WHERE (Suppliers.city = Projects.city) and (Suppliers.city = Parts.city);

Using USING:

SELECT DISTINCT Shipments.PartNum, Suppliers.SupplierNum, Projects.ProjectNum, Projects.City, Suppliers.City, Parts.City
FROM Parts
INNER JOIN Shipments USING (partNum)
INNER JOIN Projects USING (projectNum)
INNER JOIN Suppliers USING (SupplierNum)
WHERE (Suppliers.city = Projects.city) and (Suppliers.city = Parts.city);

The Inner Join returns records that have matching values in both tables.