SQL

SQL (Structured Query Language) is a programming language designed for managing data in a relational Database.

Learned in CS348, but honestly, W3Schools is much better.

Resources

• https://www.w3schools.com/sql/
• SQL Practice: https://www.sql-practice.com/
• SQL on LeetCode: https://leetcode.com/studyplan/top-sql-50/

On SQL

SQL keywords are NOT case sensitive: select is the same as SELECT.

Some of The Most Important SQL Commands

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

To select the database, use the command

USE DATABASE;

Basic SQL Queries

A basic SQL block is composed of SELECT, FROM and WHERE

SELECT column1, column2, ...
FROM table_name
WHERE condition

Under the hood, the order of operations are: 3. SELECT

1. FROM
2. WHERE

Be careful with FROM

The teacher in CS348 didn’t teach us this, but if you put in multiple tables under FROM, what SQL really is doing is taking the cartesian product of the tables (1.FROM), and then checking the conditions in (2.WHERE). This can result in slow or wrong answers.

Cartesian product operation (Cross-Join)

The Cartesian product operation produces rows combining each row from the first table with each row from the second table.

See the example below.

On OR instead of UNION

A common mistake is to use OR in the WHERE clause instead of the UNION operator.

For example, An incorrect SQL query to compute all publication titles for journals or books:

SELECT DISTINCT title
FROM publication, book, journal
WHERE publication.pubid = book.pubid
OR publication.pubid = journal.pubid

This often works, but consider where there are no books, no results will ever be found.

You can do something like (there might be the issue that book an journal have the same title)

with bookorjournal (pubid) as ( \
	(select distinct pubid from book) \
	union \
	(select distinct pubid from journal) ) \
select distinct title \
from publication, bookorjournal \
where publication.pubid = bookorjournal.pubid

You can also do it this way??

select distinct title \
from publication, ((select pubid from book ) union (select pubid from journal)) as bookorjournal \
where publication.pubid = bookorjournal.pubid

• yes, this also works

This DOESN’T WORK because you might get duplicate titles.

(select distinct title from book) \
union \
(select distinct title from journal)

Why is this the case?

This is because, in SQL, when you perform a cross-join (implied by listing the tables as publication, book, journal) and there’s an empty table involved, the whole result becomes empty. So if one of the tables has no rows, there are no combinations to be made, and the result is no rows.

• note that an empty table is different than a table with NULL values

Set Operations

The UNION operator was briefly covered above. Here are the set operations that you can use:

• Set union: $Q_1$ UNION $Q_2$
• Set difference: $Q_1$ EXCEPT $Q_2$
• Set intersection: $Q_1$ INTERSECT $Q_2$

Also see Advanced SQL, as we can do multiset operations with Aggregate Functions.

Where Clause Operators

The WHERE clause is used to filter records. This is the MOST important to understand. It is used to extract only those records that fulfill a specified condition.

Operator	Description	Example
=	Equal	Try it
>	Greater than	Try it
<	Less than	Try it
>=	Greater than or equal	Try it
<=	Less than or equal	Try it
<>	Not equal. Note: In some versions of SQL this operator may be written as !=	Try it
BETWEEN	Between a certain range	Try it
LIKE	Search for a pattern	Try it
IN	To specify multiple possible values for a column	Try it

SQL Like Operator

Learn more

• https://www.w3schools.com/sql/sql_like.asp
• https://www.w3schools.com/sql/sql_wildcards.asp

SQL Exists, All, In

I get these confused. This is how the processor introduces them.

Exists

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

For example, The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

SELECT SupplierName  
FROM Suppliers  
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

All

The ALL operator:

• returns a boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition
• is used with SELECT, WHERE and HAVING statements ALL means that the condition will be true only if the operation is true for all values in the range.

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);

The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10. This will of course return FALSE because the Quantity column has many different values (not only the value of 10):

SELECT ProductName  
FROM Products  
WHERE ProductID = ALL  
  (SELECT ProductID  
  FROM OrderDetails  
  WHERE Quantity = 10);

In

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

The following SQL statement selects all customers that are located in “Germany”, “France” or “UK”:

SELECT * FROM Customers  
WHERE Country IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are from the same countries as the suppliers:

SELECT * FROM Customers  
WHERE Country IN (SELECT Country FROM Suppliers);

SQL Insert

The general syntax for inserting a single tuple is

INSERT INTO T [(A1, . . ., Ak)] 
VALUES (c1, . . ., ck)

Example: Add Martha as new author with author identification 4.

INSERT INTO author (aid, name) \
VALUES (4, ’Martha’)

Example: Inserting a Tuple with a Query Add Tim as an author, with a new unique identification.

INSERT INTO author ( \
SELECT max(aid) + 1, ’Tim’ \
FROM author )

SQL Delete

Deletion using a condition

DELETE FROM T WHERE <condition>

• T is the table, NOT the column name

You can also delete using cursors, which is available in Embedded SQL.

Example: delete all authors who have not written anything.

DELETE FROM author \
WHERE NOT exists ( SELECT * FROM wrote \
WHERE author = aid )

SQL Update

Two components:

1. SET, an assignment of values to attributes; and
2. WHERE, a search condition.

The syntax is something like

UPDATE T
SET <assignments>
WHERE <condition>

• T is the table, NOT the column name

Example: Update anyone named Sue to be named Susan instead.

UPDATE author \
SET name = ’Susan’ \
WHERE aid IN ( \
	SELECT aid FROM author \
	WHERE name = ’Sue’ )

Why is it not just

UPDATE author \
SET name = ’Susan’ \
WHERE name = ’Sue’

• Because of DUPLICATE entries!! Make sure you understand the duplicates.

SQL Views

Syntax:

CREATE VIEW <view-name> [AS] (<query>)

A view has many of the same properties as a table.

• Its definition appears in the database schema;
• Access controls can be applied to it;
• Other views can be defined in terms of it; and
• It can be queried as if it were a table.

Unlike tables, only some views are updatable.