Introduction

Most commonly used relational DBMS query and modify the database through a language called SQL ("Structured Query Language").
We have already seen some DDL (data definition language - create, insert, update, delete) aspects of SQL.
The portion of SQL that supports queries, the DML (data manipulation language), has capabilities very close to the relational algebra we have been studying.
It first became an ANSI (American National Standards Institute) standard in 1986.
The most recent version of the standard is SQL2016.
Today, we are going to learn about the DML portion of SQL in detail.
So far this semester we have been doing homeworks using Sqlite as our DBMS.
We chose Sqlite because it is easy to install and is found everywhere -- it has the most deployed instances of any DBMS.
We start today by looking at a second DBMS system, MySQL, which is also very common.

MySQL and Xampp

SQlite can be thought of as a file format for storing databases in a single file, together with a C API for reading and writing such files, where many of the C API calls take SQL commands as string arguments.
MySQL is a traditional DBMS which runs in its own process with robust transaction, query caching, logging, recovery support.
MySQL was originally developed in Sweden by Michael "Monty" Widenius and David Axmark in 1994-1995 based on the msql DBMS from Hughes Technologies that had been created in 1993.
The original MySQL AB company was sold to Sun in 2008, which in turn became part of Oracle in 2010.
In 2009, around the time the Sun-Oracle deal was announced, Widenius forked the MySQL code to create MariaDB.
As of Fall 2019, MySQL 8.0.x is the latest version of MySQL.
The community edition of Mysql is free to download from MySQL Website.
One could also install it using apt on Linux, brew on Macos, or choco on Windows.
Finally, if you think you might take a Web Development class in the future, then consider downloading XAMPP which comes with Mysql, Apache, all pre-configured.

MySQL Databases

To manage MySQL databases you need to know how to:
- Create and manage users
- Create new databases and tables
- Do lots of other stuff I won't talk about
- Many of these operations can be done using phpMyAdmin, GUI web tool that comes with XAMPP, which when XAMPP is running, can be found at http://localhost/phpmyadmin
- Alternatively, you can use the command line.

MySQL Command Line

I will assume you have set your path so that you can directly run mysqladmin and mysql.
To set the root password you can go to the command prompt and type:
```
mysqladmin -uroot password what_password_is
```
You can get a mysql shell by then typing:
```
mysql -uroot -pwhat_password_is
```

MySQL Command Line

To see what databases are available you can use the command:
```
show databases;
```
To use a particular database type:
```
use dbname;
```
To see what tables the currently use database has type:
```
show tables;
```

To add a new user you can type at the prompt:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES 
  on dbname.* TO username@127.0.0.1 IDENTIFIED BY 'password';

To quit mysql type \q or quit. For help type \h or help.
To login using this new user type at the command prompt:
```
mysql -uusername -ppassword
```
In addition to MySQL specific commands, from the command shell we can also execute standard SQL commands. So let's resume our discussion of SQL's DML.
In what follows, we will assume that we are issuing the SQL commands from a MySQL shell.
In reality, most SQL is executed by programs written in a host language like C, Java, or PHP that make a connection to a DBMS server and then send the commands over this connection.

Quiz

Which of the following is true?

`{1,1,2,3}` and `{1,2,2,3}` are the same bag.
`gamma_{mbox{starName, COUNT(title)->ctTitle}}(StarsIn)` would output a two column relation: starName, ctTitle, representing for each starName the number of movies they were in.
Given that we said in class that we don't allow datalog rules to have the same predicate in both their head and their tail, it is possible that all datalog rules in a datalog program are safe, yet a predicate in it represents an infinite set.

Simple Queries in SQL

Recall in SQL terminology a relation is called a table, and an attribute is called a column.
Almost all SQL queries make use of the three keywords SELECT, FROM, and WHERE:
```
SELECT list_of_columns
FROM list_of_tables
WHERE some_condition;
```

For example, if we have the relation:

Movies(title, year, length, genre, studioName, producerC#)

then to return all movies produced by Disney Studios in 1990, we could write:

SELECT *
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990;

In the above, * says return all columns that appear in tables/relations of the FROM clause, in this case, MOVIES.
The WHERE clause has the effect of a relational algebra select. I.e., in the relational algebra we could have written the above SQL as `sigma_{mbox(studioName = 'Disney' AND year = 1990)}(MOVIES)`.
SQL is case insensitive, so fRoM could have been used rather than the keyword FROM. Similarly, tables names etc are case insensitive.
Typically, SQL keywords are written in ALL-CAPS.
Content of quoted strings (usually single quotes), however, is case sensitive. So 'MOVIES' is different than 'Movies'.

Projections and More on Selection

SQL supports both standard and extended relational algebra projections.
For example, rather than use the * of the last slide, if we wanted just the title and length of movies produced by Disney Studios in 1990, we could have written:
```
SELECT title, length
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990;
```
To do extended projection operation, we can use the AS keyword to rename the output columns or have an output column which is computed as some expression. For example,
```
SELECT title AS name, length*0.016667 AS lengthInHours
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990;
```
Besides using "="'s to compare items in the WHERE clauses, we can compare things using `<>` (meaning not equal), `<`, `>`, <=, >=.
We can also make use of expressions such as:
```
(year - 1930) * (year - 1930) < 100
```
WHERE clauses can also be built up using the AND, OR, NOT boolean connectives, as well as the boolean values TRUE and FALSE. For example,
```
SELECT title
FROM Movies
WHERE (year > 1970 OR length < 90) AND studioName = 'MGM';
```

Strings and Pattern Matching in SQL

SQL strings may arise from attributes in tables defined using either the CHAR or VARCHAR.
Or they may arise from string literals: For example, 'hi there'.
Or from expressions built out of these using the concatenation operator ||. For example, 'Mr.'||fname||lname . Note this works in Sqlite, for Mysql there is a CONCAT function.
When comparing strings s1 and s2, SQL views them as a sequence of characters ignoring any padding characters that might arise from using a CHAR of some fixed length.
So s1 = s2 if the strings s1 and s2 are the same sequence of characters up to padding.
Comparisons such as `<`, `>`, <= and >= are done according to lex order (dictionary order). So 'fodder' < 'foo' and 'bar' < 'bargain'.
SQL also supports pattern matching using the LIKE and NOT LIKE operators and the two special characters % and _.
Here % matches any strings of 0 or more characters, and _ matches a single character.

For example,

SELECT title
FROM Movies
WHERE title LIKE 'Star ____';

whould also title to match 'Star Wars'.

If we wanted to match titles that contained an apostrophe s, we could do (notice we escape ' by repeating twice):
```
SELECT title
FROM Movies
WHERE title LIKE '%''s%';
```

Dates, Times

Recall we could define a date or time type in a table column using the keywords DATE and TIME.
Constants of these types can be given using the using quoted strings preceded by either the DATE or TIME keyword.
For example, DATE '1948-05-14', TIME '15:00:02', TIME '15:00:02.5', etc.
SQL also supports a TIMESTAMP type which combines DATE's and times. An example, timestamp literal might be: TIMESTAMP '1948-05-14 15:00:02'.

NULL, UNKNOWN in SQL

SQL allows attributes to have a special value NULL.
This value can be interpreted in different ways depending on the circumstance:
1. The value exists but is currently unknown.
2. The value is not applicable. For example, an unmarried person, might have a NULL value for spouseName.
3. The value is witheld. You don't have access to the actual value. For example, Boss' phone number.
If x happens to have the value NULL, then x+3 or other expressions built from x will also be NULL. Note the expression NULL + 3 and the comparison NULL = 3 are not legal SQL.
You can use the boolean expression, x IS NULL, to check if x is currently NULL.
When NULL's are compared rather than getting either TRUE or FALSE, in SQL, one can end up with the value UNKNOWN. Boolean combinations of AND, OR, NOT of UNKNOWN are given a standard semantics. For example, x AND y where y is UNKNOWN is UNKNOWN. Similarly, x OR y where x is TRUE and y might be UNKNOWN is TRUE.

Ordering Output in SQL

We can order the output of our queries by adding an ORDER BY clause:

SELECT *
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990
ORDER BY length, title;

The output above would be sorted by movie length where ties are broken by movie title in increasing alphabetical order.
The list of sort attributes in the ORDER BY clause can consist of either table attributes or expressions built out of table attributes.
We can also add the keyword ASC and DESC to say whether the sort direction is either ascending (default) or descending.

SQL as a Query Language

Outline

Introduction

MySQL and Xampp

MySQL Databases

MySQL Command Line

MySQL Command Line

Quiz

Simple Queries in SQL

Projections and More on Selection

Strings and Pattern Matching in SQL

Dates, Times

NULL, UNKNOWN in SQL

Ordering Output in SQL