SQL

Today SQL (Structured Query Language) is the standard for getting information from a database, or for inserting/updating data in a database. All serious databases provide support for SQL. This includes Oracle, Sybase, DB2, and many others.

https://en.wikipedia.org/wiki/SQL

SQL was born in 1970, and became standard in 1986. Original SQL was designed to work with tables (rows and columns). New versions of SQL may work also on JSON unstructured data (MongoDB, Snowflake, etc.).

There are many SQL tutorials online. For example, you can use this one:
http://www.sqlcourse.com

There are also books on amazon, videos on youtube, there are apps for all platforms (including iPhones and Android phones). There are PowerPoint tutorials – may be the best for short introduction. Just google for
sql tutorial ppt
and you can get many Power Point tutorials.

You can also consider downloading some PDFs. Google for:
sql tutorial pdf
and you will get files like these:
http://www.tutorialspoint.com/sql/sql_tutorial.pdf
http://magnum.anu.edu.au/~TDFgg/Public/Release/Database/sql_intro.pdf
http://www.e-reading.club/bookreader.php/141481/Oppel_-_SQL_-_a_beginner’s_guide.pdf
etc.

Below is a simple 1-page SQL tutorial – good for printing-out to read on a plane.

 

Simple SQL tutorial

 

Basic SQL Statements

SELECT Retrieves columns and rows from a table or tables
INSERT Adds rows to a table
UPDATE Updates columns in existing rows in a table
DELETE Deletes rows from a table
CREATE TABLE Creates a new table based on the specified table schema
SELECT INTO Creates a new table based on rows and columns output by a SELECT statement

 

SELECT Statement


 

The SELECT statement is used to retrieve rows of data from your database into a recordset object. The SELECT statement specifies the columns of data you want to retrieve, where the columns are stored, what criteria the returned data must meet, and the sort order of the data. A SELECT statement can further group rows of data together and assign retrieval criteria at the group level.

The components of the SELECT statement

SELECT Specifies the columns of data to be retrieved
FROM Specifies the tables from which to retrieve rows
WHERE Specifies criteria that returned data must meet
GROUP BY For aggregate queries, specifies the returned columns by which the data is to be grouped
HAVING For aggregate queries, specifies criteria that the aggregate value returned must meet
ORDER BY Specifies the sort order of the returned rows

 

So let us begin with a very simple SELECT statement. We will build on this statement as we move from one example to the next, adding complexity through the use of WHERE and ORDER BY clauses. A Simple SELECT Statement In the following example, you simply query several columns in the authors table:

SELECT au_id, au_lname, au_fname, state, zip, contract
FROM authors;

The results of this simple query reveal that the data in the selected columns was returned for each row that exists in the authors table.

Adding the WHERE Clause Using the same basic SELECT statement, you can narrow down your results by adding a WHERE clause. Suppose that you only want to know the names of authors located in the state of California. In the following example, only those records whose state column has a value of `CA’ will be returned.

SELECT au_id, au_lname, au_fname, state, zip, contract
FROM authors
WHERE state = 'CA';

Your WHERE clause can use several columns as criteria for row retrieval. If you additionally wanted only those rows whose contract value was zero, you would use a logical AND, as in the following:

SELECT au_id, au_lname, au_fname, state, zip, contract
FROM authors
WHERE state = `CA' AND contract = 0;

You can also have the query return rows for authors in any state except California. The way to indicate inequality in SQL is to combine a greater-than and a less-than sign (<>).

SELECT au_id, au_lname, au_fname, state, zip, contract
FROM authors
WHERE state <> `CA';

The WHERE clause can use a number of different comparison operators for checking field values.

Comparison operators in the WHERE clause

= Is equal to
> Is greater than
< Is less than
>= Is greater than or equal to
<= Is less than or equal to
< > Is not equal to
 IN Is in a specified list of values or in the results of a specified subquery
BETWEEN..AND  Is between two values
LIKE  Contains the same pattern as a specified string. The pattern being compared is a string that contains one or more wildcard characters

 

In addition to a variety of comparison operators, the columns included in the WHERE clause can be checked using the AND and OR logical operators. If the AND operator is used, the conditions on both sides of the AND must be met for a row to be returned. The OR operator requires that at least one of the conditions must be met. Adding the ORDER BY Clause Now let’s take your simple query one step further and sort the results by author last name (au_lname). To do this, you add an ORDER BY clause.

SELECT au_id, au_lname, au_fname, state, zip, contract
FROM authors
WHERE state = `CA'
ORDER BY au_lname;

The resulting rows are returned in ascending alphabetical order by the author’s last name.

You can also sort the records in a field in descending order using the keyword DESC. Suppose you want to first sort the records in descending order by contract and then sort in ascending order by author last name. An example is shown in the following SQL statement:

SELECT au_id, au_lname, au_fname, state, zip, contract
FROM authors
WHERE state = `CA'
ORDER BY contract DESC, au_lname;

NOTE: The default sort order is ascending (A-Z, 0-9).

 

Using the WHERE Clause to Join Tables


 

You have seen several ways to use SQL SELECT statements to look at the data in the authors table by specifying columns to return, assigning retrieval criteria, and sorting the results. But in the real world, will you want to look at data from only a single table at a time? The answer is most likely “No.” The data in the authors table has relationships with data in other tables in the pubs database. For instance, what if you want to know what titles were written by these authors? The author table alone won’t tell you this. You must search the cross-reference table titleauthor, which links author to title by the columns au_id and title_id. Unfortunately, this isn’t enough. Most of us don’t recognize authors or titles by IDs or codes. We know them by names. Because the names of the authors are located in the table author, the titles are located in the table title, and the relationships between the two are located in titleauthor, you need a way to tie these three tables together in a single SELECT; statement. You can do this using the WHERE clause.

SELECT authors.au_lname, authors.au_fname, titles.title
FROM  authors, titleauthor, titles
WHERE
authors.au_id = titleauthor.au_id AND
titleauthor.title_id = titles.title_id
ORDER BY authors.au_lname, authors.au_fname, titles.title;

Note the way you referenced the column names in this statement. When retrieving data from multiple tables in a single SQL statement, you must preface the column names that appear in more than one of the tables in the FROM clause with their associated table names in order to avoid an ambiguity error.

 

Using the JOIN operator to Join Tables


 

Another way to join tables is by using the JOIN operator. This is the ANSI standard method for joining tables and uses the following syntax:

SELECT column1, column2, column3
FROM table1 JOIN table2
ON join criteria;

The JOIN operator indicates how rows should be returned from the joined tables. The ON clause acts like a WHERE clause, indicating which fields in the joined table should be compared for equality.

JOIN Operators

CROSS JOIN Returns each row from the first table joined with each row from the second table, resulting in a returned number of rows equal to the product of the two tables’ rowcounts
INNER JOIN Returns all rows from each table that meet the WHERE clause search criteria and where there is a match on the joined fields in the ON clause.
LEFT [OUTER] JOIN Returns all rows from the table on the left side of the join that meet the WHERE clause search criteria and only those from the right side of the join where there is a match on the joined fields in the ON clause
RIGHT [OUTER] JOIN Returns all rows from the table on the right side of the join that meet the WHERE clause search criteria and only those from the left side of the join where there is a match on the joined fields in the ON clause
FULL [OUTER] JOIN Returns all rows from each table that meet the WHERE clause search criteria and where there is no match on the joined fields in the ON clause

 

Using the JOIN operator, the SQL statement in the previous example would be written as follows:

SELECT authors.au_lname, authors.au_fname, titles.title
FROM (authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id) INNER JOIN titles
ON titleauthor.title_id = titles.title_id
ORDER BY authors.au_lname, authors.au_fname, titles.title;

To indicate a LEFT or RIGHT outer join in the WHERE clause, you can use the *= and =* operators, respectively, in place of the = operator. The following two left outer JOIN statements produce the same result and illustrate the two methods for joining tables:

SELECT column1, column2, column3
FROM table1 LEFT JOIN table2
ON table1.column1 = table2.column1;

SELECT column1, column2, column3
FROM table1, table2
WHERE table1.column1 *= table2.column1;

One use for an outer join would be to list all authors and, if they wrote a book, list the title_id of that book (through an outer join with titleauthor). If the author did not write a book, you would still be able to see the author’s name listed, but the title_id would be returned as NULL.

 

Aggregate Functions in SQL Statements


 

Aggregate functions return summary values for specified columns or expressions in the form of sum totals, number of records, averages, and so on. The aggregate function might return a single value for all rows represented by the query. If a GROUP BY clause has been added to the SQL statement, such summary values will be calculated at each level of grouping. Table below lists the aggregate functions you can use.

Aggregate Functions in SQL Statements

AVG Returns the average of all values in the columns by taking their sum and dividing by the count
COUNT Returns the number of non-null values in the specified column or expression. If the expression is an asterisk (such as count(*)), the result will be the number of rows in the query.
MAX Returns the maximum value in the specified column or expression
MIN Returns the minimum value in the specified column or expression
STDEV, STDEV_POP Return estimates of the standard deviation for a population or population sample
SUM Returns the sum of values in the specified column or expression
VARIANCE, VAR_POP Return estimates of the variance for a population or a population sample

 

The following statement illustrates the use of the count function on the entire titles table with no grouping:

SELECT count(title) 'titles'
FROM titles;

The result is the total number of title records in the titles table.

NOTE: You can use an alias to return a different name for a column or assign a name to a column that returns an expression (and, therefore, has no name). There are three ways to assign an alias to a column. 

SELECT count(title) AS titles FROM titles;
SELECT count(title) 'titles' FROM titles;
SELECT 'titles' = count(title) FROM titles;

 

Use of the GROUP BY Clause


 

The GROUP BYstatement accepts a list of columns specifying how the results of a SELECT statement should be grouped. The SELECT statement returns one row for each set of distinct values in columns specified in the GROUP BY list. For example, consider a returned list of authors and the books that they wrote. Because some of the authors wrote more than one book, their names appear in more than one of the returned rows. By adding a GROUP BY clause we can group the returned rows by author last name and first name so that only one row will be returned for each distinct author name.

In the following example, we will apply the GROUP BY clause to the SELECT statement. Instead of returning the authors’ titles, we will use the aggregate count function to determine the number of titles associated with each author. Because we have grouped the returned set of records by author last name and first name, each author will have its own title count, rather than the count being performed on an entire table, as in the previous example of the count function.

SELECT authors.au_lname, authors.au_fname, count(titles.title) 'titles'
FROM authors, titleauthor, titles
WHERE
authors.au_id = titleauthor.au_id AND
titleauthor.title_id = titles.title_id
GROUP BY authors.au_lname, authors.au_fname;

This way you can determine how many books were written by each author.

NOTE: At this point, you are familiar with the use of table names prefacing column names in order to avoid ambiguity in multi-table queries. You also might have begun to notice that this can result in lengthy SQL statements. Using a table alias, you can shorten your SQL statement and make it easier to both read and type. Like the column alias shown earlier, the table alias is simply the use of a new name to represent the actual table name. They are often used to shorten the SQL statement and to enhance the readability of the WHERE clause, so the table names are usually replaced by an alias of a single character.

 

Let’s look at the preceding SQL example, replacing table names with aliases

SELECT a.au_lname, a.au_fname, count(c.title) 'titles'
FROM authors a, titleauthor b, titles c
WHERE
a.au_id = b.au_id AND
b.title_id = c.title_id
GROUP BY a.au_lname, a.au_fname;

 

Use of the HAVING Clause


 

Like the WHERE clause, the HAVING clause is used for specifying criteria for data to be returned in a query. The difference lies in the level at which the criteria is checked. The WHERE clause uses criteria to restrict rows of data returned by a query. The GROUP BY clause then forms the returned rows into groups and calculates any aggregate values. The criteria in the HAVING clause is then used to restrict groups of rows according to the group level data.

The following example shows how the HAVING clause is used to return only the names of authors who have written more than one book:

SELECT a.au_lname, a.au_fname, count(c.title) 'titles'
FROM authors a, titleauthor b, titles c
WHERE
a.au_id = b.au_id AND
b.title_id = c.title_id
GROUP BY a.au_lname, a.au_fname
HAVING count(c.title) > 1;

INSERT Statement


 

The INSERT statement is used to add rows of data to a table. The INSERT statement specifies the table to which rows are to be added, the columns in which the data is to be stored, the source of the data being added, and the data itself.

INSERT INTO Specifies the table to which rows are to be added.
(column list) specifies columns in which to add the data. It is necessary to provide a column list only when not all of the table’s columns are to have data added. If left blank, the column list can be understood to be every column in the table, in the order in which they appear in the table structure.
VALUES (value list) Specifies the values to be filled in the respective columns in the column list. (For example, the first value in the value list will be assigned to the first column in the column list, and so on.)
SELECT The SELECT statement that will return rows to be added to the table

 

NOTE: Either a value list or a SELECT statement (not both) is used to provide the data to be added.


Use of the INSERT Statement with a Value List

This example shows the use of the value list to add a row to the title table, providing values for each column in the table. Note that with a value list, you can insert only a single row into the table.

INSERT INTO titles
VALUES
( `SM1234', `The Small Business Tax Guide', `business', `1389', 15.99, 3000,
10, 0, `Tax guide for owners of small businesses', `1/1/2016');

You can also specify which columns to fill in the insert clause:

INSERT INTO titles (title_id, title)
VALUES (`SM5678', `The Small Business Marketing Guide');

A reason for not specifying all values would be that you do not know those values and want any possible default values to be added. Note, however, that if there are no default values for the omitted columns and the structure of the table to which you are adding the row requires that an omitted column be filled, you will receive an error.

In the first example shown, you might have noticed that the list of columns in the insert clause was omitted. This is the equivalent of listing all columns in the table in the order in which they appear in the table structure. This saves you some typing time. However, it is usually better to include the column list so that, if fields are reordered or added to the table at a later date, the SQL statement does not need to be altered to reflect the structure change. Use of the INSERT Statement with a SELECT Statement This example shows the use of the SELECT statement to add one or more rows of data to the title table. Let’s assume that the newtitles table is a temporary working table that holds new title information. From this table, you want to add all titles whose processing date is NULL.

INSERT INTO titles
( title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate );

SELECT
title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate
FROM newtitles
WHERE procdate is NULL;

NOTE: The INSERT INTO..SELECT statement can be interpreted as a two-part query. That is, the SELECT portion of the statement actually is performed by itself as “step one” of the statement. The resulting set of rows is then given to the INSERT INTO portion of the statement, which is “step two” of the statement. For this reason, there is no ambiguity between the identical column lists in these two distinct portions of the SQL statement. Therefore, it is not necessary to preface the column names with the table names in order to distinguish those in the destination table from those in the source table.

 

UPDATE Statement


 

The UPDATE statement is used to update column values in existing rows in a table. The UPDATE statement specifies the table to be updated, the columns to update, the new values to assign those columns, and criteria for the rows to be updated.

UPDATE Specifies the table to be updated
SET Specifies columns to update and the new values to assign to those columns
FROM Specifies the tables to include in the UDPATE statement
WHERE Specifies the criteria that determine which rows’ columns are to be updated

 

Setting Columns to a Fixed Value with the UPDATE Statement In some cases, you want to update columns in a table with a fixed value for every row in the table. For instance, assume that you want to update the processing date to January 1, 2016 for each row in the newtitles table (used in the revious example) that currently has a processing date of NULL:

UPDATE newtitles
SET procdate = `1/1/2016'
WHERE procdate = NULL;

Setting a Column Value Based on Existing Column Values Suppose you want to increase the price of each book in the title table by 10 percent of the current price. It would be difficult to update the table with fixed values (as in the previous example), because all books need to be updated to different prices. That could take all day. A better way to perform this update is to use the existing price as a base for the updated price. Just as you can assign a fixed value to a column, you can also assign the results of an expression as shown in the following statement:

UPDATE titles
SET price = price * 1.10;

Without knowing the value of any of the prices in the title table, you can successfully increase their values by 10 percent in one easy UPDATE statement. Setting a Column Based on Values in a Joined Table Now let’s imagine that you want to update the publisher associated with all titles written by a specific author. The author information is nowhere to be found in the titles table. You can get that information only by joining the titles table with the titleauthor table. To do this, you add a FROM clause to the UPDATE statement. This FROM clause works the same way as the FROM clause in a SELECT statement. While the UPDATE clause indicates the table to be updated, the FROM clause indicates the source of the data with which to update that table. The following example updates the pub_id column to `1389′ for all titles associated with au_id `998-72-3567′:

UPDATE titles
SET pub_id = '1389'
FROM titles a, titleauthor b
WHERE
a.title_id = b.title_id AND
b.au_id = `998-72-3567';

 

DELETE


 

The DELETE statement allows you to remove rows from tables. This statement specifies the table from which rows are to be deleted and criteria for the rows to be deleted.

 

DELETE FROM Specifies the table from which to delete rows
WHERE Specifies the criteria that determine which rows are to be deleted

 

Using DELETE to Delete All Rows From a Table:
To delete all rows from a table, you need only specify the name of the table from which to delete those rows. The following example shows how to delete all rows from the title table:

DELETE FROM titles;

There is another method, called the TRUNCATE TABLE statement, for removing all rows from a table. It acts like a DELETE statement that has no WHERE clause, with a couple of important exceptions that should be noted. The DELETE statement deletes rows from a table one row at a time, logging each deletion as a transaction, and it can therefore be rolled back. The TRUNCATE TABLE statement, on the other hand, removes entries from a table pages at a time, and does not log individual row deletions. Rows removed from a table with a TRUNCATE TABLE statement cannot be recovered.

The other important note is that delete triggers associated with a table will not be fired when that table is truncated.
So why use a TRUNCATE TABLE statement?
Speed. The TRUNCATE TABLE statement performs the task of removing all rows from a table much faster than a DELETE statement does.

 

IMPORTANT: TRUNCATE TABLE always removes all the rows from a table and these rows are not recoverable.

 

But sometimes you don’t want to wipe out all the rows in a table. Instead, assume that you want to delete only a specific title from the titles table. Using DELETE to Delete Specific Rows from a Table To delete specific rows from a table, you simply add the familiar WHERE clause. As you might expect, only the rows that meet the criteria of the WHERE clause will be deleted. Suppose you want to remove only the title `Silicon Valley Gastronomic Treats’, whose title_id is ‘MC2222’. Here is how to do it:

DELETE FROM titles
WHERE title_id = 'MC2222';

This is a little bit more complex than the previous example, but it’s still quite easy. Let’s try one more. Using DELETE to Delete Rows Based on a Joined Table Imagine that the criteria for the delete are based on a value in another table. For example, what if you want to delete all titles written by the author whose au_id is `123-45-6789′? You must use the joined titleauthor table to determine which titles were written by each author.

DELETE titles.*
FROM titles, titleauthor
WHERE titles.title_id = titleauthor.title_id and titleauthor.au_id = '123-45-6789';

DELETE FROM titles
WHERE title_id IN
( SELECT title_id from titleauthor
WHERE au_id = '123-45-6789' );

CREATE TABLE


 

The CREATE TABLE statement allows you to create a new table in the database. Sometimes you need to access the data in such a way that a SELECT statement would not effectively produce the desired results. For example, you might find that the set of data you need must be accessed and processed one row at a time through a cursor. Another example is a result set that would require table joins so complex that the query takes a very long time to run. In these cases, it is useful to have an empty table structured as you want the result set structured, so that you can fill in phases through either the use of a cursor or a combination of INSERT and UPDATE statements.

Using CREATE TABLE to Create the authors Table To create a new table using the CREATE TABLE statement, you simply specify the table to create and the columns that will configure the table. The following statement creates the authors table:

CREATE TABLE authors
( au_id id NOT NULL ,
au_lname varchar (40) NOT NULL ,
au_fname varchar (20) NOT NULL ,
phone char (12) NOT NULL ,
address varchar (40) NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
zip char (5) NULL ,
contract bit NOT NULL );

 

SELECT .. INTO


 

The SELECT..INTO statement is another way you can create a new table. This method differs from the CREATE TABLE method in that the structure of the table to create is not explicitly stated; rather, it is determined by the results of a SELECT statement.

 

SELECT (column list) Specifies the selected columns with which to build the new table
INTO Specifies the name of the new table being created
FROM Specifies the table or tables from which the column is being selected
WHERE Specifies criteria that returned data must meet
GROUP BY For aggregate queries, specifies the returned columns by which the data is to be grouped
HAVING For aggregate queries, specifies criteria that the aggregate value returned must meet
ORDER BY Specifies the sort order of the returned rows

 

Using SELECT..INTO to Create a New authortitles Table

Suppose that you want to store the results of a SELECT statement in a table for use later in your application or in a report. The SELECT..INTO statement allows you to do just that. You simply need to add the INTO clause to a standard SELECT statement. In this example, you use the SELECT statement from an earlier example, which displayed author names and the titles of books that the authors wrote, to create a new table called authortitles:

SELECT a.au_lname, a.au_fname, c.title
INTO authortitles
FROM authors a, titleauthor b, titles c
WHERE
a.au_id = b.au_id and
b.title_id = c.title_id
ORDER BY a.au_lname, a.au_fname, c.title;

Now, if you select all rows from the authortitles table, you get the same results that you got in the SELECT statement.

 

Summary


 

You’ve learned the most common SQL commands. You have learned how to retrieve rows in many different formats using the SELECT statement, as well as how to add, update, and delete rows in tables using the INSERT, UPDATE, and DELETE statements, respectively. You also learned how to create a new table using the CREATE TABLE or SELECT..INTO statements.