Essential SQL_ A Gentle Introdu - Bala Paranj

Prévia do material em texto

Table of Contents
 
1. About the Author 
2. Introduction 
3. Create, Insert and Select Statements 
4. Sorting 
5. Search Conditions 
6. Advanced Search Conditions 
7. Wildcard Search 
8. Calculated Fields 
9. Aggregate Functions 
10. Grouping Data 
11. Subqueries 
12. Joins 
13. Inner Join 
14. Advanced Joins 
15. Update and Delete 
16. Constraints 
17. Indexes 
About the Author
Bala Paranj has a Master's degree in Electrical Engineering from Wichita
State University. He began working in the IT industry in 1996. He started his
career as a Technical Support Engineer and then became a Web Developer
using Perl, Java and Ruby.
He is available for freelance work. Please contact him at support@zepho.com
or via Ruby Plus. He is also working on screencasts based on this book. If
you want notification about the release, please contact him.
https://www.rubyplus.com/contact
Introduction
What is a Database?
A database is a container that helps us to organize data. Databases are good at
storing large amounts of data in one place and make it easier to query the
data, add, update and delete data.
There are many types of databases, relational, object-oriented and document-
based. We will be focusing only on relational database. Most of the NoSQL
databases are documented based systems. They have their own query
language that have some similarity to SQL. So it is useful to learn SQL.
What is Relational?
According to the dictionary relational means things that relate to other things.
The relational model is a way to describe the data and the relationship
between those data. In a relational database, data is stored in a Table. A Table
is something that has a name. The table names describe the type of data that
is stored in that table. Every table also has a collection of columns that define
data that are stored in the Table.
Data is stored in columns. Every column has a name. Every column also has
a restriction that can restrict the size and the type of data that can be stored in
that column. So columns can store numbers, strings, date etc based on their
type. Columns can have restrictions on the value of data that could be stored
in them. Every column can be required or optional. The required columns
must have values whereas the optional columns do not force us to provide
any value. It could be a blank string or NULL value.
A column can be a Key. A Primary Key can uniquely identify that row.
Because the value in that column for that row is unique. No other row in that
table can have that same value. Now, if another table has a column that
contains that same key, we can then merge those two tables together. We can
query them together. A Foreign Key means that column's value is the same as
the Primary Key in another table. This allows rows the related tables to be
linked together. Keys establish the relationship between tables.
What is SQL?
SQL stands for Structured Query Language. SQL is what is a special-purpose
programming language. SQL is specifically built to manipulate relational
databases. It's a declarative language. So, we basically declare statements,
and the database system executes those statements. SQL is based on English,
so it is human readable. When we put SQL into a statement, we create an
actionable sentence. So a valid SQL statement is made up of a set of valid
actionable words. SQL is not case sensitive. Most SQL statements start with
a command. Generally that command is some sort of a verb to tell the
database to do something.
It has two parts. One is a part that is for defining the data in a relational
database, and the other part is a language for querying and manipulating that
data. By the end of this series of articles, you will be able to create a
database, query a database, add, update and delete data.
Summary
In this chapter you learned the meaning of the terminology used in relational
database. This gives you the background required to learn the SQL basics in
subsequent chapters.
Create, Insert and Select Statements
In this chapter, you will learn how to create a database, create tables, insert
data and retreive data from database.
Install Sqlite3
Install the sqlite3 gem.
$ gem install sqlite3
Output of running the command.
Fetching: sqlite3-1.3.9.gem (100%)
Building native extensions. This could take a while...
Successfully installed sqlite3-1.3.9
1 gem installed
Create a Database
When you type sqlite3 with a file name that ends in .sqlite3, sqlite3 gem will
automatically create an empty database.
$ sqlite3 play.sqlite3
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
To view the tables:
sqlite> .tables
This will return no output, since we don't have any tables yet.
Create a Table
Use CREATE TABLE <table name> as follows:
sqlite> CREATE TABLE users(
 ...> id int primary key not null,
 ...> first_name char(50),
 ...> last_name char(50),
 ...> email char(50) not null);
sqlite> .tables
users
We see only one table called users.
Counting Records
To count the number of records in a table, use count(*) in the select statement
as follows:
sqlite> select count(*) from users;
0
Here we see no records, since we don't have any data in the users table yet.
Inserting Rows
Let's insert some rows in the user table.
sqlite> INSERT INTO users(first_name, last_name, email)
 ...> VALUES('bugs', 'bunny', 'bbunny@disney.com');
Error: users.id may not be NULL
We get an error because we have a 'not null' constraint on the primary key id
of the users table. So, we need to provide it:
sqlite> INSERT INTO users(id, first_name, last_name, email)
 ...> VALUES(1,'bugs', 'bunny', 'bbunny@disney.com');
Now if we count again, we get:
sqlite> select count(*) from users;
1
Retrieving All Columns
To retrieve data for all columns, use * after the select keyword as follows:
sqlite> select * from users;
1|bugs|bunny|bbunny@disney.com
Let's turn on the column names to be displayed.
sqlite> .headers on
sqlite> select * from users;
id|first_name|last_name|email
1|bugs|bunny|bbunny@disney.com
Let's insert a second row into the users table.
sqlite> INSERT INTO users(id, first_name, last_name, email)
 ...> VALUES(2,'daffy', 'duck', 'daffy@disney.com');
sqlite> select count(*) from users;
count(*)
2
Now we have two rows in the users table.
Retrieving Single Column
sqlite> select first_name from users where id=2;
first_name
daffy
Let's make the output pretty with the column name and it's data aligned.
sqlite> .mode column
sqlite> select first_name from users where id=2;
first_name
----------
daffy
If you retrieve all the columns, you will get a nice output:
sqlite> select * from users;
id first_name last_name email 
---------- ---------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com
Retrieving Multiple Columns
To retrieve data for multiple columns from a table, specify the column names
after the select keyword separated by a comma, like this:
sqlite> select first_name, last_name from users;
first_name last_name 
---------- ----------
bugs bunny 
daffy duck
Exercise
Insert three records into users table. One of the record must have the same
first_name any one of the existing record.
Answer
sqlite> INSERT INTO users(id, first_name, last_name, email)
 ...> VALUES(3,'porky', 'pig', 'ppig@disney.com');
sqlite> INSERT INTO users(id, first_name, last_name, email)
 ...> VALUES(4,'pluto', 'dog', 'pdog@disney.com');
sqlite> INSERT INTO users(id, first_name, last_name, email)
 ...> VALUES(5,'pluto', 'pet', 'pdog@disney.com');
sqlite> select count(*) from users;
count(*) 
----------
5
Limiting Results
To limit the number of rows to retrieve from the database, use the 'limit'
keyword:
sqlite> select * from users limit 2;
id first_name last_name email---------- ---------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com
To get the next two rows, specify where to start and the number of rows to
retrieve:
sqlite> select * from users limit 2 offset 2;
id first_name last_name email 
---------- ---------- ---------- ---------------
3 porky pig ppig@disney.com
4 pluto dog pdog@disney.com
In this example it starts from row 3 (offset = 2) and retrieves two records
(limit = 2).
Retrieving Distinct Rows
We have 5 records in the users table.
sqlite> select * from users;
id first_name last_name email 
---------- ---------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com 
3 porky pig ppig@disney.com 
4 pluto dog pdog@disney.com 
5 pluto pet pdog@disney.com
If we want to retrieve only unique user names :
sqlite> select DISTINCT(first_name) from users;
first_name
----------
bugs 
daffy 
pluto 
porky
Summary
In this chapter, you learned how to create a new database, create a table,
populate the table with data and the basics of SQL SELECT statement. In the
next article, you will learn how to sort the retrieved data.
Sorting
In this chapter, you will learn how to use ORDER By clause to sort retrieved
data.
Sorting Single Column
When you select a column, the data seems to be not ordered:
sqlite> select first_name from users;
first_name
----------
bugs 
daffy 
porky 
pluto 
pluto 
tasmanian 
tweety 
elmer 
speedy 
yosemite
However, if you select the primary key and the first name:
sqlite> select id, first_name from users;
id first_name
---------- ----------
1 bugs 
2 daffy 
3 porky 
4 pluto 
5 pluto 
6 tasmanian 
7 tweety 
8 elmer 
9 speedy 
10 yosemite
You see that it is displayed in the order the record was inserted into the
database. You should not rely on this default behavior since the ordering can
be changed when you delete and update records in the users table. It is a good
practice to explicitly specify the sort order.
sqlite> select first_name from users order by first_name;
first_name
----------
bugs 
daffy 
elmer 
pluto 
pluto 
porky 
speedy 
tasmanian 
tweety 
yosemite
You can now see that these are ordered by the first_name and not by the
primary key:
sqlite> select id, first_name from users order by first_name;
id first_name
---------- ----------
1 bugs 
2 daffy 
8 elmer 
4 pluto 
5 pluto 
3 porky 
9 speedy 
6 tasmanian 
7 tweety 
10 yosemite
Sorting by Multiple Columns
Here the data is not sorted in any order:
sqlite> select last_name, first_name from users;
last_name first_name
---------- ----------
bunny bugs 
duck daffy 
pig porky 
dog pluto 
pet pluto 
devil tasmanian 
bird tweety 
fudd elmer 
gonzales speedy 
sam yosemite
Let's sort the users first by last_name and then first_name:
sqlite> select last_name, first_name from users order by last_name, first_name;
last_name first_name
---------- ----------
bird tweety 
bunny bugs 
devil tasmanian 
dog pluto 
duck daffy 
fudd elmer 
gonzales speedy 
pet pluto 
pig porky 
sam yosemite
Specifying Sort Direction
In the previous examples, the sorting by default was in ascending order, if
you want to order by descending, you can use DESC keyword:
sqlite> select first_name from users order by first_name desc;
first_name
----------
yosemite 
tweety 
tasmanian 
speedy 
porky 
pluto 
pluto 
elmer 
daffy 
bugs
Here is another example, this time with multiple columns:
select last_name, first_name from users order by last_name desc, first_name;
last_name first_name
---------- ----------
sam yosemite 
pig porky 
pet pluto 
gonzales speedy 
fudd elmer 
duck daffy 
dog pluto 
devil tasmanian 
bunny bugs 
bird tweety
Summary
In this chapter, you learned how to sort data using the ORDER By clause in
the SELECT statement. This clause must be the last in the SELECT
statement. It can be used to sort data in ascending or descending order on one
or more columns.
Search Conditions
In this chapter, you will learn how to use the WHERE clause in SELECT
statement to filter data.
Where Clause
To avoid retrieving large number of records we specify search criteria to
extract a subset of the table's data.
sqlite> select * from users where id > 5;
id first_name last_name email 
---------- ---------- ---------- -----------------
6 tasmanian devil tdevil@disney.com
7 tweety bird tweety@disney.com
8 elmer fudd efudd@disney.com 
9 speedy gonzales gonzales@disney.c
10 yosemite sam sam@disney.com
Here the where clause is used as the filter condition to retrieve records with
primary key greater than 5.
Where Clause Operator
The example above used greater than as the operator for the search criteria.
We can also use any of the following operators:
 
1. Equality
2. Non-Equality
3. Less than
4. Less than or equal to
5. Not less than
6. Greater than
7. Greater than or equal to
8. Not greater than
9. Between two values
10. Is a NULL value
Here is the equality operator as the filter condition:
sqlite> select * from users where id = 5;
id first_name last_name email 
---------- ---------- ---------- ---------------
5 pluto pet pdog@disney.com
Here is the non-equality operator as the filter condition:
sqlite> select * from users where first_name <> 'pluto';
id first_name last_name email 
---------- ---------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com 
3 porky pig ppig@disney.com 
6 tasmanian devil tdevil@disney.com
7 tweety bird tweety@disney.com
8 elmer fudd efudd@disney.com 
9 speedy gonzales gonzales@disney.c
10 yosemite sam sam@disney.com
The result filters out two of the records which contains pluto as the
first_name.
Exercise
Change the above non-equality query to use != instead of <>. What do you
see as the result?
Between a Range
We need to specify the beginning and end of the range as follows:
sqlite> select * from users where id between 5 and 10;
id first_name last_name email 
---------- ---------- ---------- ---------------
5 pluto pet pdog@disney.com
6 tasmanian devil tdevil@disney.c
7 tweety bird tweety@disney.c
8 elmer fudd efudd@disney.co
9 speedy gonzales gonzales@disney
10 yosemite sam sam@disney.com
Here we retrieve all records between 5 and 10, where 5 is the beginning and
10 is the end of the range. The range values is separated by the AND
keyword.
NULL Value
When a column does not have any value, it contains a NULL value. Since our
databasedoes not contain any record with NULL value, let's first insert a
record with NULL value for last_name by not providing a value in the insert
statement.
sqlite> select * from users where last_name is NULL;
sqlite> INSERT INTO users(id, first_name, email)
 ...> VALUES(12,'bosko', 'bosko@disney.com');
sqlite> select * from users where last_name is NULL;
id first_name last_name email 
---------- ---------- ---------- --------------
12 bosko bosko@disney.com
In this case, bosko does not have any last_name, it is NULL in database
terminology.
Exercise
Experiment with the remaining where clause operators from the list shown in
the Where Clause Operator section by querying the users table.
Summary
In this chapter, you learned how to specify search criteria to filter data using
the WHERE clause of the SELECT statement. You also learned how to use
various WHERE clause operators.
Advanced Search Conditions
In this chapter, you will learn how to combine WHERE clauses to create
advanced data filters and how to use NOT and IN operators.
Multiple Conditions in WHERE
Clauses
You can use more than one search criteria by combining where clauses with
AND or OR clauses. The AND and OR is known as logical operators.
Create the table and populate some data as follows:
sqlite> CREATE TABLE products(
 ...> id int primary key not null,
 ...> price int,
 ...> name char(50),
 ...> user_id int not null)
 ...> ;
sqlite> .tables
products users 
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(1, 10, 'Rock', 1);
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(2, 20, 'Sand', 1);
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(3, 30, 'Carrot', 1);
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(4, 40, 'Hammer', 1);
The AND Operator
You can use AND operator to filter by more than one column as follows:
sqlite> select * from products where user_id = 1 AND price < 30;
id price name user_id 
---------- ---------- ---------- ----------
1 10 Rock 1 
2 20 Sand 1
We have two conditions that is joined by the AND logical operator. In this
case the user_id must be 1 and the price must be less than 30. We have 4
records as demonstrated below:
sqlite> select * from products;
id price name user_id 
---------- ---------- ---------- ----------
1 10 Rock 1 
2 20 Sand 1 
3 30 Carrot 1 
4 40 Hammer 1
We found only two records when we applied the multiple conditions to the
query. You can have as many conditions as you want separated by AND
keyword.
The OR Operator
The OR operator is used to retrieve rows that match any of the condition in
the WHERE clause. Insert two records for product that belongs to different
users.
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(5, 40, 'Tree', 2);
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(6, 80, 'Cage', 3);
Let's retrieve all products that belong to user with id 2 or 3:
sqlite> select id, name, price from products where user_id=2 OR user_id = 3;
id name price 
---------- ---------- ----------
5 Tree 40 
6 Cage 80
Combining AND and OR Operators
Use parenthesis to group multiple conditions to avoid problems due to the
order of evaluation of the logical operators. Here is an example to combine
logical operators:
select id, user_id, name, price from products where (user_id = 1 OR user_id = 2) AND price > 
id user_id name price 
---------- ---------- ---------- ----------
4 1 Hammer 40 
5 2 Tree 40
You can see that there are 5 products that does not have the condition to filter
products with price greater than 30.
sqlite> select id, user_id, name, price from products where user_id = 1 OR user_id = 
id user_id name price 
---------- ---------- ---------- ----------
1 1 Rock 10 
2 1 Sand 20 
3 1 Carrot 30 
4 1 Hammer 40 
5 2 Tree 40
The IN Operator
The IN operator is used to specify a range of conditions. The results contain
matches that satisfy any of the conditions.
sqlite> select user_id, name, price from products where user_id IN (1,2,5);
user_id name price 
---------- ---------- ----------
1 Rock 10 
1 Sand 20 
1 Carrot 30 
1 Hammer 40 
2 Tree 40
In this example there are four rows that match the first value in the comma
separated list, just one row for the second value and no rows for user_id = 5,
because there is products that belongs to user_id = 5.
select user_id from products;
user_id 
----------
1 
1 
1 
1 
2 
3
There are products only for user_id = 1, 2 and 3.
The NOT Operator
The NOT is a negation operator that is always used in conjunction with
another operator.
sqlite> select * from products where NOT user_id = 1;
id price name user_id 
---------- ---------- ---------- ----------
5 40 Tree 2 
6 80 Cage 3
Here we retrieve all rows that does not belong to user_id = 1.
We could have accomplished the same thing with the <> operator as follows:
sqlite> select * from products where user_id <> 1;
id price name user_id 
---------- ---------- ---------- ----------
5 40 Tree 2 
6 80 Cage 3
Summary
In this chapter you learned how to use multiple conditions with WHERE
clauses by combining AND and OR operators. You also learned how to use
the IN and NOT operators.
Wildcard Search
In this chapter you will learn how to use wildcard for searches using the
LIKE operator.
The LIKE Operator
The Percent Wildcard
Let's insert few more rows to the products table.
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(7, 80, 'Cage Wire', 3);
sqlite> INSERT INTO products(id, price, name, user_id)
 ...> VALUES(8, 80, 'Camel Rope', 3);
The wildcard search can only be used with string fields. Let's search for
product names that has 'Ca' in it.
sqlite> select id, name from products where name LIKE 'Ca%';
id name 
---------- ----------
3 Carrot 
6 Cage 
7 Cage Wire 
8 Camel Rope
If we do :
sqlite> select id, name from products where name LIKE 'age%';
We don't get any results, to find the product with Cage and Cage Wire, we
can modify the query as follows:
sqlite> select id, name from products where name LIKE '%age%';
id name 
---------- ----------
6 Cage 
7 Cage Wire
So, the percent sign in the beginning matches any number of occurrences of
any character before the letter 'a'. The percent sign at the end of 'age' matches
any number of occurrences of any character after the string 'age'.
We can use the wildcard character anywhere in the string:
sqlite> select id, name from products where name LIKE 'C%e';
id name 
---------- ----------
6 Cage 
7 Cage Wire 
8 Camel Rope
Here we search for products that begins with the letter 'C' and has any
number of characters after that and ends in the letter 'e'.
The Underscore Wildcard
The underscore wildcard is used to match just a single character. Here is an
example:sqlite> select id, name from products where name LIKE '_ock';
id name 
---------- ----------
1 Rock
Here we match product with the name that has any letter before the 'ock'. We
can also combine both the wildcards:
sqlite> select id, name from products where name LIKE '_am%';
id name 
---------- ----------
4 Hammer 
8 Camel Rope
Summary
In this chapter you learned how to use wildcards to search text fields in the
WHERE clause. You learned about the percent and the underscore wildcards.
Calculated Fields
In this chapter, you will learn about calculated fields. Calculated fields are
useful to calculate total, averages or other calculations based on table data.
The calculated field does not exist in the tables, it is created on demand
within a SELECT statement. Why do we need to let the database do the
calculation instead of doing it in your client code? Because it is faster to
perform the calculations on the database server.
Concatenating Fields
Let's display the name of all the users by concatenating the first_name and
last_name.
sqlite> select first_name || ' ' || last_name from users;
first_name || ' ' || last_name
------------------------------
bugs bunny 
daffy duck 
porky pig 
pluto dog 
pluto pet 
tasmanian devil 
tweety bird 
elmer fudd 
speedy gonzales 
yosemite sam 
sylvester
Here we are using the two pipes || operator to concatenate first_name and
last_name. You can change the name of the resulting header to name by
using the AS keyword. The new header 'name' is an alias for the concatenated
field.
sqlite> select first_name || ' ' || last_name as name from users;
name 
----------
bugs bunny
daffy duck
porky pig 
pluto dog 
pluto pet 
tasmanian 
tweety bir
elmer fudd
speedy gon
yosemite s
sylvester
The last name is getting cut-off. Let's fix that by specifying the width for the
columns.
sqlite> .width 20 20
sqlite> select first_name || ' ' || last_name as name from users;
name 
--------------------
bugs bunny 
daffy duck 
porky pig 
pluto dog 
pluto pet 
tasmanian devil 
tweety bird 
elmer fudd 
speedy gonzales 
yosemite sam 
sylvester
Performing Calculations
Let's create a line_items table.
sqlite> CREATE TABLE line_items(
 ...> id int primary key not null,
 ...> product_id int not null,
 ...> quantity int,
 ...> price real,
 ...> order_number int);
sqlite> .tables
line_items products users
Populate line_times with some sample data.
sqlite> INSERT INTO line_items(id, product_id, quantity, price, order_number)
 ...> VALUES(1, 1, 4, 10, 1);
sqlite> INSERT INTO line_items(id, product_id, quantity, price, order_number)
 ...> VALUES(2, 3, 10, 10, 1);
sqlite> INSERT INTO line_items(id, product_id, quantity, price, order_number)
 ...> VALUES(3, 2, 1, 40, 1);
sqlite> INSERT INTO line_items(id, product_id, quantity, price, order_number)
 ...> VALUES(4, 4, 15, 30, 1);
We now have four rows in line_items table.
sqlite> select * from line_items;
id product_id quantity price order_number
-------------------- -------------------- ---------- ---------- ------------
1 1 4 10.0 1 
3 2 1 40.0 1 
2 3 10 10.0 1 
4 4 15 30.0 1
Let's calculate line_item_price for each of the line_items :
sqlite> select product_id, quantity, price, quantity * price AS line_item_price from line_items;
product_id quantity price line_item_price
-------------------- -------------------- ---------- ---------------
1 4 10.0 40.0 
2 1 40.0 40.0 
3 10 10.0 100.0 
4 15 30.0 450.0
The last column shows the line_item_price for each row in the line_items
table. You can add, subtract, multiply and divide to perform any
mathematical calculations.
Summary
In this chapter you learned how to use calculated fields and why it is used.
You also learned about the aliases for the calculated fields.
Aggregate Functions
In this chapter, you will learn how to use aggregate functions in SQL to
summarize table data. SQL provides functions to summarize data without
retrieving data. This saves bandwidth and avoids unnecessary retrieval of
data. This is useful for analysis and reporting purposes. These functions can
compute number of rows in a table, calculating sum of a set of rows in a
table, finding highest, lowest and average values in a table column.
The AVG() Function
Here is the average function :
sqlite> select AVG(price) AS average_price from products;
average_price 
--------------------
47.5
This query calculates the average price of all the products. You can also
calculate average price for a subset of rows.
sqlite> select AVG(price) AS average_price from products where user_id = 1;
average_price 
--------------------
25.0
In this example we calculate the average price of all products for a user with
user_id = 1.
The COUNT function
Let's count the number of products in the products table.
sqlite> select count(*) from products;
count(*) 
--------------------
8
Currently, we have 8 products. We have 12 rows in the users table.
sqlite> select * from users;
id first_name last_name email 
-------------------- -------------------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com 
3 porky pig ppig@disney.com 
4 pluto dog pdog@disney.com 
5 pluto pet pdog@disney.com 
6 tasmanian devil tdevil@disney.com
7 tweety bird tweety@disney.com
8 elmer fudd efudd@disney.com 
9 speedy gonzales gonzales@disney.c
10 yosemite sam sam@disney.com 
11 sylvester sam@disney.com 
12 bosko sam@disney.com
We get 12 when we count the users table.
sqlite> select count(*) from users;
count(*) 
--------------------
12
If we want to ignore NULL values and count only the number of rows that
have values in a specific column, we can use count(column_name).
sqlite> select count(last_name) from users;
count(last_name) 
--------------------
11
Although we see two blank values in the last_name column, we see 11
because only one of the column is NULL. We can find out which row it is by
firing this query:
sqlite> select * from users where last_name is NULL;
id first_name last_name email 
-------------------- -------------------- ---------- --------------
12 bosko sam@disney.com
The other record contains a blank string, we can find the row with blank
string using this query:
sqlite> select * from users where last_name ='';
id first_name last_name email 
---------------------------------------- ---------- --------------
11 sylvester sam@disney.com
We can provide a different name for the column name using AS keyword:
sqlite> select count(last_name) as last_name_count from users;
last_name_count 
--------------------
11
The MAX function
Let's find the price of the most expensive product.
sqlite> select MAX(price) from products;
MAX(price) 
--------------------
80
Exercise
Change the above query so that the name of the column is called 'Most
Expensive'.
The MIN Function
Let's find the price of the cheapest product.
sqlite> select MIN(price) from products;
MIN(price) 
--------------------
10
The SUM Function
sqlite> select SUM(quantity) as ordered_items from line_items where order_number = 
ordered_items 
--------------------
30
The SUM function returns the sum of all the quantity in the line_items table,
the WHERE clause restricts it to the order_number = 1.
sqlite> select * from line_items where order_number = 1;
id product_id quantity price order_number
-------------------- -------------------- ---------- ---------- ------------
1 1 4 10.0 1 
3 2 1 40.0 1 
2 3 10 10.0 1 
4 4 15 30.0 1
There is data for line_items for only one order. If you add all the columns
under the quantity, you get 30. We can also use SUM to total calculated
values. Here is an example to calculate the total amount for order with
order_number = 1.
sqlite> select SUM(price * quantity) AS Total from line_items where order_number = 
Total 
--------------------
630.0
This is the total amount that a customer would pay when they checkout the
cart on the online store.
sqlite> select * from line_items;
id product_id quantity price order_number
-------------------- -------------------- ---------- ---------- ------------
1 1 4 10.0 1 
3 2 1 40.0 1 
2 3 10 10.0 1 
4 4 15 30.0 1
You can double check the calculated answer by multiplying the quantity and
price and adding them up.
Combining Aggregate Functions
Here is an example that combines aggregate functions.
sqlite> select count(*) as count, 
 avg(price) as average_price, 
 max(price) as maximum_price, 
 min(price) as minimum_price from products;
count average_price maximum_price minimum_price
-------------------- -------------------- ------------- -------------
8 47.5 80 10
Summary
In this chapter, you learned about aggregate functions. They are faster than if
you were to calculate these values in your code. They can be combined
together to perform powerful calculations quickly.
Grouping Data
In this chapter you will learn how to group data using GROUP BY and
HAVING clause in the SELECT statement.
Why Group Data?
In the previous article you learned aggregate functions that performed
calculations on all the data in a table or on data that satisfied a specific
condition of the WHERE clause. We were able to count the number of
products. If we want to count the number of products for each user we need
to use grouping. Grouping allows us to divide data into logical sets and
perform aggregate calculations on each group.
Group By Clause
Let's count the number of products that belong to each user in the products
table.
sqlite> select user_id, count(*) as product_count from products group by user_id;
user_id product_count 
-------------------- --------------------
1 4 
2 1 
3 3
The result shows that the user with user_id = 1 has 4 products, user_id = 2
has 1 product and user_id = 3 has 3 products. You can verify this by
manually calculating the number of products from the following query.
sqlite> select * from products;
id price name user_id 
-------------------- -------------------- ---------- ----------
1 10 Rock 1 
2 20 Sand 1 
3 30 Carrot 1 
4 40 Hammer 1 
5 40 Tree 2 
6 80 Cage 3 
7 80 Cage Wire 3 
8 80 Camel Rope 3
You can see that the user with user_id has only one product whereas users
with user_id 1 and 3 have 4 and 3 products respectively.
Having Clause
You can filter groups to include and exclude by using Having clause.
sqlite> select user_id, count(*) as count from products group by user_id having count(*) > 
user_id count 
-------------------- --------------------
1 4 
3 3
The having clause filters on the group and finds only the rows that has more
than 2 products for the user. You can verify this by looking at the records.
sqlite> select * from products;
id price name user_id 
-------------------- -------------------- ---------- ----------
1 10 Rock 1 
2 20 Sand 1 
3 30 Carrot 1 
4 40 Hammer 1 
5 40 Tree 2 
6 80 Cage 3 
7 80 Cage Wire 3 
8 80 Camel Rope 3
You see there is only one user with user_id = 2 with the number of products
less than or equal to 2.
Summary
In this article you learned how to use the GROUP BY and HAVING clauses
to perform aggregate functions on groups of data.
Subqueries
In this chapter, you will learn how to use subqueries. Subqueries are queries
that are embedded into other queries.
Let's create the orders table.
sqlite> CREATE TABLE orders(
 ...> id int primary key not null,
 ...> confirmation char(50),
 ...> customer_id int not null,
 ...> created_at datetime default current_timestamp
 ...> );
Let's insert some records into the order table.
sqlite> INSERT INTO orders(id, confirmation, customer_id)
 ...> VALUES(1, 1, 1);
sqlite> INSERT INTO orders(id, confirmation, customer_id)
 ...> VALUES(2, 2, 1);
sqlite> INSERT INTO orders(id, confirmation, customer_id) 
...> VALUES(3, 3, 2);
Let's create customers table.
sqlite> CREATE TABLE customers(
 ...> id int primary key not null,
 ...> name char(50),
 ...> email char(50)
 ...> );
Let's insert some records into the customers table.
sqlite> INSERT INTO customers(id, name, email)
 ...> VALUES(1, 'Big Spender', 'bspender@example.com');
sqlite> INSERT INTO customers(id, name, email)
 ...> VALUES(2, 'Big Saver', 'bsaver@example.com');
Problem
List all customers who ordered item with product_id = 1:
Step 1
sqlite> select order_number from line_items where product_id = 1;
order_number
------------
1
Step 2
sqlite> select customer_idfrom orders where id = 1;
customer_id
-----------
1
We use the order_number retrieved from step 1 as the value for id because, id
is the primary key of orders and order_number in line_items is basically the
order_id. It should have been named order_id to be clear.
Step 3
sqlite> select customer_id from orders where id IN (select order_number from line_items 
customer_id
-----------
1
We can have subquery that finds the primary key of orders as above. Now we
can list the customer details as follows.
sqlite> select name, email from customers where id IN (select customer_id from orders 
name email 
----------- --------------------
Big Spender bspender@example.com
Subqueries as Calculated Fields
Let's find out the total number of orders placed by every customer in the
customers table. Orders table has the orders with the corresponding
customer_id. We have two steps:
 
1. Get the list of customers from customers table.
2. For each customer, count the number of orders placed from the orders
table.
sqlite> select count(*) as number_of_orders from orders where customer_id = 1;
number_of_orders
----------------
2 
sqlite> select count(*) as number_of_orders from orders where customer_id = 2;
number_of_orders
----------------
1
Here is the subquery that uses calculated fields.
sqlite> select name, (select count(*) from orders where orders.customer_id = customers.id) as number_of_orders from customers;
name number_of_orders
----------- ----------------
Big Spender 2 
Big Saver 1
To avoid ambiguity we have the order.customer_id = customers.id, this
relates the orders table and the customers table.
Summary
In this chapter, you learned how to use subqueries in WHERE clause IN
operators and for populating calculated columns.
Joins
In this chapter, you will learn the why and how to use joins.
Why Joins?
We currently have products and users table. Here is the list of records they
contain.
sqlite> select * from products;
id price name user_id 
---------- ---------- ---------- ----------
1 10 Rock 1 
2 20 Sand 1 
3 30 Carrot 1 
4 40 Hammer 1 
5 40 Tree 2 
6 80 Cage 3 
7 80 Cage Wire 3 
8 80 Camel Rope 3 
sqlite> select * from users;
id first_name last_name email 
---------- ---------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com 
3 porky pig ppig@disney.com 
4 pluto dog pdog@disney.com 
5 pluto pet pdog@disney.com 
6 tasmanian devil tdevil@disney.com
7 tweety bird tweety@disney.com
8 elmer fudd efudd@disney.com 
9 speedy gonzales gonzales@disney.c
10 yosemite sam sam@disney.com 
11 sylvester sam@disney.com 
12 bosko sam@disney.com
We are storing the name and email in it's own table instead of storing it in
products table. Updating the users information becomes easy since it is in just
one place. This also saves storage space. The users and products table are
related to each other through common values. In this case the user_id ties a
row in the product to a user in the users table. The id in users and products
uniquely identifies a row in that table. This id field is called a primary key.
The join is used to retrieve data from multiple tables with a single SELECT
statement. A single set of output is returned by matching the correct rows in
the multiple tables on the fly.
How to Join
The FROM clause in the SELECT select includes all the tables to be joined
and they are related to each other.
sqlite> select first_name as user_name, name as product_name, price as product_price from users, products 
user_name product_name product_price
---------- ------------ -------------
bugs Rock 10 
bugs Sand 20 
bugs Carrot 30 
bugs Hammer 40 
daffy Tree 40 
porky Cage 80 
porky Cage Wire 80 
porky Camel Rope 80
The first_name is in the users table whereas the product name and product
price is in the products table. The FROM clause has tables separated by
comma. These are the tables to be joined. The WHERE clause specifies that
primary key in users table (users.id) must match the foreign key of the user in
products table (products.user_id). The output shows data from two different
tables retrieved using a single SELECT statement. Let's see what happens
when we don't have the WHERE clause.
sqlite> select first_name, name, price from users, products;
first_name name price 
---------- ---------- ----------
bugs Rock 10 
bugs Sand 20 
bugs Carrot 30 
bugs Hammer 40 
bugs Tree 40 
bugs Cage 80 
bugs Cage Wire 80 
bugs Camel Rope 80 
daffy Rock 10 
daffy Sand 20 
daffy Carrot 30 
daffy Hammer 40 
daffy Tree 40 
daffy Cage 80 
daffy Cage Wire 80 
daffy Camel Rope 80 
porky Rock 10 
porky Sand 20 
porky Carrot 30 
porky Hammer 40 
porky Tree 40 
porky Cage 80 
porky Cage Wire 80 
porky Camel Rope 80 
pluto Rock 10 
pluto Sand 20 
pluto Carrot 30 
pluto Hammer 40 
pluto Tree 40 
pluto Cage 80 
pluto Cage Wire 80 
pluto Camel Rope 80 
pluto Rock 10 
pluto Sand 20 
pluto Carrot 30 
pluto Hammer 40 
pluto Tree 40 
pluto Cage 80 
pluto Cage Wire 80 
pluto Camel Rope 80 
tasmanian Rock 10 
tasmanian Sand 20 
tasmanian Carrot 30 
tasmanian Hammer 40 
tasmanian Tree 40 
tasmanian Cage 80 
tasmanian Cage Wire 80 
tasmanian Camel Rope 80 
tweety Rock 10 
tweety Sand 20 
tweety Carrot 30 
tweety Hammer 40 
tweety Tree 40 
tweety Cage 80 
tweety Cage Wire 80 
tweety Camel Rope 80 
elmer Rock 10 
elmer Sand 20 
elmer Carrot 30 
elmer Hammer 40 
elmer Tree 40 
elmer Cage 80 
elmer Cage Wire 80 
elmer Camel Rope 80 
speedy Rock 10 
speedy Sand 20 
speedy Carrot 30 
speedy Hammer 40 
speedy Tree 40 
speedy Cage 80 
speedy Cage Wire 80 
speedy Camel Rope 80 
yosemite Rock 10 
yosemite Sand 20 
yosemite Carrot 30 
yosemite Hammer 40 
yosemite Tree 40 
yosemite Cage 80yosemite Cage Wire 80 
yosemite Camel Rope 80 
sylvester Rock 10 
sylvester Sand 20 
sylvester Carrot 30 
sylvester Hammer 40 
sylvester Tree 40 
sylvester Cage 80 
sylvester Cage Wire 80 
sylvester Camel Rope 80 
bosko Rock 10 
bosko Sand 20 
bosko Carrot 30 
bosko Hammer 40 
bosko Tree 40 
bosko Cage 80 
bosko Cage Wire 80 
bosko Camel Rope 80
The output now has every row in the users table paired with every row in the
products table. They are paired without considering any logical relationship
between them exists or not. The is called the Cartesian Product and the
number of rows returned will be equal to the number of rows in users table
multiplied by the number of rows in the products table.
Summary
In this chapter you learned the basics of joins and how to use them. You also
learned about Cartesian Product. In the next chapter we will explore inner
joins.
Inner Join
In this chapter, you will learn how to use inner joins. In the previous chapter
on Joins, the join we used is called as equijoin or inner join. It is called inner
join because it is the intersection of the two tables. If you had two circles to
represent two tables, the common overlapping area is the inner join of those
two tables. Here is an example:
sqlite> select first_name as user_name, name as product_name, price as product_price from users INNER JOIN products ON users.id = products.user_id;
user_name product_name product_price
---------- ------------ -------------
bugs Rock 10 
bugs Sand 20 
bugs Carrot 30 
bugs Hammer 40 
daffy Tree 40 
porky Cage 80 
porky Cage Wire 80 
porky Camel Rope 80
Instead of comma separating the tables as we saw in the previous article on
join, here we have INNER JOIN between the table names. The WHERE
clause is replaced by ON keyword. Here is the query we used in the previous
article, you can compare both the queries and see the difference.
sqlite> select first_name as user_name, name as product_name, price as product_price from users, products WHERE users.id = products.user_id;
user_name product_name product_price
---------- ------------ -------------
bugs Rock 10 
bugs Sand 20 
bugs Carrot 30 
bugs Hammer 40 
daffy Tree 40 
porky Cage 80 
porky Cage Wire 80 
porky Camel Rope 80
The query is different, but the output is the same in both cases.
Joining Multiple Tables
You can join as many tables as you want. You have to list all the tables and
define the relationship between them. Let's consider an example where we
need to display product name, user name, product price and quantity for an
order with order_number = 1. We know that product name and product price
are stored in the products table. The user name is stored in the users table and
the quantity is in the line_items table. Before we join these tables, let's take a
look at the data to help us form the query.
sqlite> select * from line_items;
id product_id quantity price order_number
---------- ---------- ---------- ---------- ------------
1 1 4 10.0 1 
3 2 1 40.0 1 
2 3 10 10.0 1 
4 4 15 30.0 1 
sqlite> select * from products;
id price name user_id 
---------- ---------- ---------- ----------
1 10 Rock 1 
2 20 Sand 1 
3 30 Carrot 1 
4 40 Hammer 1 
5 40 Tree 2 
6 80 Cage 3 
7 80 Cage Wire 3 
8 80 Camel Rope 3 
sqlite> select * from users;
id first_name last_name email 
---------- ---------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com 
3 porky pig ppig@disney.com 
4 pluto dog pdog@disney.com 
5 pluto pet pdog@disney.com 
6 tasmanian devil tdevil@disney.com
7 tweety bird tweety@disney.com
8 elmer fudd efudd@disney.com 
9 speedy gonzales gonzales@disney.c
10 yosemite sam sam@disney.com 
11 sylvester sam@disney.com 
12 bosko sam@disney.com
Let's now join these three tables to get the data.
sqlite> select name, first_name, price, quantity from line_items, products, users 
 ...> where products.user_id = users.id
 ...> and line_items.product_id = products.id
 ...> and order_number = 1;
Error: ambiguous column name: price
We get this error because we have price column in line_times as well as the
products table. So we need to explicitly indicate which table the price column
we are interested in resides. We can do that by prefixing the price with the
name of the table as follows:
sqlite> select name, first_name, line_items.price, quantity from line_items, products, users
 ...> where products.user_id = users.id
 ...> and line_items.product_id = products.id
 ...> and order_number = 1;
name first_name price quantity 
---------- ---------- ---------- ----------
Rock bugs 10.0 4 
Sand bugs 40.0 1 
Carrot bugs 10.0 10 
Hammer bugs 30.0 15
We had to specify line_items.price to avoid ambiguity. The WHERE clauses
are separated by AND keyword to specify the relationships between the
tables. The foreign key in the products table user_id is the primary key in
users table id. This is the first condition of the WHERE clause. The condition
relates the foreign key product_id in the line_items table to the primary key
of products, id in the products table. The final order_number = 1 condition
filters the results only for that order.
You might be wondering why I picked line_items price column instead of
products' price column. The reason is that once the line_items is created the
price column will not change. The price column in the products table can be
changed by the user who owns it anytime. This avoids people who are
shopping our products getting confused by sudden changes in the price of the
product they are about to buy.
Exercise
The first column in the above output is the name of the product. Change the
name of the column from 'name' to 'product name'.
Using Joins Instead of Subqueries
In the previous chapter on subqueries we had this query:
sqlite> select name, email from customers where id IN (select customer_id from orders 
name email 
----------- --------------------
Big Spender bspender@example.com
Let's use joins instead of subqueries. To create this query, it helps to list all
the table to be joined so that you can see the relationship between them. This
will be used in the WHERE clauses.
sqlite> select * from customers;
id name email 
---------- ----------- --------------------
1 Big Spender bspender@example.com
2 Big Saver bsaver@example.com 
sqlite> select * fromorders;
id confirmation customer_id created_at 
---------- ------------ ----------- -------------------
1 1 1 2014-10-25 00:11:51
2 2 1 2014-10-25 00:14:29
3 3 2 2014-10-25 01:16:10
sqlite> select * from line_items;
id product_id quantity price order_number
---------- ---------- ---------- ---------- ------------
1 1 4 10.0 1 
3 2 1 40.0 1 
2 3 10 10.0 1 
4 4 15 30.0 1
Here is the query:
sqlite> select name, email 
 ...> from customers, orders, line_items
 ...> where customers.id = orders.customer_id
 ...> and line_items.order_number = orders.id
 ...> and product_id = 1;
name email 
----------- --------------------
Big Spender bspender@example.com
As you can see the result is the same as using the subquery. Which one
should you use? Prefer joins because it more efficient.
Summary
In this chapter you learned the basics of relationships between tables that is
required to use joins. You also learned the most frequently used joins, the
inner join. In the next chapter, we will explore other types of joins.
Advanced Joins
In this chapter, you will learn about outer joins and how to use them with
aggregate functions.
Data Population
Populate a few records into the customers table as follows:
sqlite> INSERT INTO customers(id, name, email)
 ...> VALUES(3, 'Big Saver', 'bsaver2@example.com');
sqlite> INSERT INTO customers(id, name, email)
 ...> VALUES(4, 'Big Browser', 'browser@example.com');
We now have four records in the customers table.
sqlite> select * from customers; 
id name email 
---------- ----------- --------------------
1 Big Spender bspender@example.com
2 Big Saver bsaver@example.com 
3 Big Saver bsaver2@example.com 
4 Big Browser browser@example.com
Self Join
Let's get a list of customers who work for the same company for which
'bsaver@example.com' works. The first query must find out the customer
name for 'bsaver@example.com', where he works. Then, find a list of
customers who work for the same company. Here is one way of solving this
problem using subquery:
sqlite> select id, name, email from customers 
where name = (select name from customers where email='bsaver@example.com');
id name email 
---------- ---------- ------------------
2 Big Saver bsaver@example.com
3 Big Saver bsaver2@example.com
Here is another way of solving the problem that uses join.
sqlite> select c1.id, c1.name, c1.email from customers as c1, customers as c2 
 ...> where c1.name = c2.name 
 ...> and c2.email = 'bsaver@example.com';
id name email 
---------- ---------- ------------------
2 Big Saver bsaver@example.com
3 Big Saver bsaver2@example.com
Instead of joining two different tables, in this case we are joining with the
same table. The c1 and c2 are aliases for the customers table. The WHERE
clause condition filters the customer name that match on both customers
table. The second condition of the WHERE clause finds records for the
company where 'bsaver@example.com' works.
Natural Joins
sqlite> select c.*, o.id, o.created_at, li.product_id, li.quantity, li.price
 ...> from customers as c, orders as o, line_items as li
 ...> where c.id = o.customer_id
 ...> and li.order_number = o.id
 ...> and product_id = 1;
id name email id created_at product_id quantity price 
---------- ----------- -------------------- ---------- ------------------- -
--------- ---------- ----------
1 Big Spender bspender@example.com 1 2014-10-
25 00:11:51 1 4 10.0
In this example, a wildcard is used for the customers table only. All other
columns are explicitly specified to avoid retrieving duplicate columns.
Outer Joins
Let's find out how many orders is placed by every customer. You can use
joins with aggregate functions like this:
sqlite> select customer_id, count(*) as number_of_order from orders group by customer_id;
customer_id number_of_order
----------- ---------------
1 2 
2 1
This is same as the following query:
sqlite> select customers.id, orders.id 
 ...> from customers INNER JOIN orders
 ...> ON customers.id = orders.customer_id;
customer_id order_id 
----------- ----------
1 1 
1 2 
2 3
We see only two customers who have placed orders. What if we want to
include customers who have not placed any orders yet?
sqlite> select customers.id as customer_id, orders.id as order_id
 ...> from customers LEFT OUTER JOIN orders
 ...> on customers.id = orders.customer_id;
customer_id order_id 
----------- ----------
1 1 
1 2 
2 3 
3 
4
The left outer join query returns customers who have not placed any order
yet. It includes the rows in the left of the LEFT OUTER JOIN, customers,
even if they don't have any corresponding value in the other table, in this
case, orders table. Let's combine the joins with aggregate functions to list the
number of orders placed by every customer.
sqlite> select customers.id as customer_id, count(orders.id) as number_of_orders
 ...> from customers LEFT OUTER JOIN orders 
 ...> on customers.id = orders.customer_id
 ...> group by customers.id;
customer_id number_of_orders
----------- ----------------
1 2 
2 1 
3 0 
4 0
If you find any of the query difficult to follow, list all the data in all tables
and refer them while you analyze the query. Here is the complete list of all
data. For subqueries, run one query at a time and then combine them
together.
sqlite> select * from orders;
id confirmation customer_id created_at 
---------- ------------ ----------- -------------------
1 1 1 2014-10-25 00:11:51
2 2 1 2014-10-25 00:14:29
3 3 2 2014-10-25 01:16:10
sqlite> select * from line_items;
id product_id quantity price order_number
---------- ---------- ---------- ---------- ------------
1 1 4 10.0 1 
3 2 1 40.0 1 
2 3 10 10.0 1 
4 4 15 30.0 1 
sqlite> select * from customers;
id name email 
---------- ----------- --------------------
1 Big Spender bspender@example.com
2 Big Saver bsaver@example.com 
3 Big Saver bsaver2@example.com 
4 Big Browser browser@example.com
Summary
In this chapter you learned about self join where we join with the same table,
outer join and natural joins. You also learned how to use them in combination
with aggregate functions.
Update and Delete
In this chapter, you will learn how to use UPDATE and DELETE statements
to update and delete rows in a table.
Update
To update data in a table the UPDATE statement is used. Let's take a look at
the existing data in the customers table.
sqlite> select * from customers;
id name email 
---------- ----------- --------------------
1 Big Spender bspender@example.com2 Big Saver bsaver@example.com 
3 Big Saver bsaver2@example.com 
4 Big Browser browser@example.com
Let's now update the email for the customer with id = 1.
sqlite> update customers
 ...> set email = 'bspender@spender.com'
 ...> where id = 1;
The update statement takes the name of table to be updated followed by the
SET keyword with the column name set to a new value. In this example we
are updating just one row because of the WHERE condition. Now the change
made can be verified by looking at the customers table.
sqlite> select * from customers;
id name email 
---------- ----------- --------------------
1 Big Spender bspender@spender.com
2 Big Saver bsaver@example.com 
3 Big Saver bsaver2@example.com 
4 Big Browser browser@example.com
Let's now look at updating more than one column.
sqlite> update customers
 ...> set name = 'Bugs Bunny',
 ...> email = 'bbunny@disney.com'
 ...> where id = 1;
The only difference here is that we specify multiple columns to be updated
separated by comma after the SET keyword.
sqlite> select * from customers;
id name email 
---------- ---------- -----------------
1 Bugs Bunny bbunny@disney.com
2 Big Saver bsaver@example.co
3 Big Saver bsaver2@example.c
4 Big Browse browser@example.c
We see that both both the name and email has been changed to the new
values. To display the entire value for email column, let's increase the width
of that column.
sqlite> .width 2 25 25
sqlite> select * from customers;
id name email 
-- ------------------------- -------------------------
1 Bugs Bunny bbunny@disney.com 
2 Big Saver bsaver@example.com 
3 Big Saver bsaver2@example.com 
4 Big Browser browser@example.com
Delete
The DELETE statement is used to remove data from a table. Here is the list
of customers before delete.
sqlite> select * from customers;
id name email 
-- ------------------------- -------------------------
1 Bugs Bunny bbunny@disney.com 
2 Big Saver bsaver@example.com 
3 Big Saver bsaver2@example.com 
4 Big Browser browser@example.com
Let's delete the last record in the customers table.
sqlite> delete from customers
 ...> where id = 4;
You can see that the customer 'Big Browser' is not in customers table.
sqlite> select * from customers;
id name email 
-- ------------------------- -------------------------
1 Bugs Bunny bbunny@disney.com 
2 Big Saver bsaver@example.com 
3 Big Saver bsaver2@example.com
If you don't provide the WHERE clause in the delete statement, all records
will be deleted.
sqlite> delete from customers;
sqlite> select count(*) from customers;
count
--
0
Summary
In this article you learned how to use the UPDATE and DELETE statements
for changing and deleting rows in a table.
Constraints
In this chapter, you will learn about constraints, primary keys, foreign keys
and referential integrity.
What are Constraints?
Relational databases store data into multiple tables. There are relationships
that exist between the tables. That's why it's called Relational database. Keys
reference from one table to another. This is called referential integrity. In
our example, when we store a record in the line_items table, the
order_number references the id column in the orders table. There cannot be a
line_items record that does not have a order_number or a order_number that
does not exist in the orders table. This would violate the referential integrity.
Relational databases enforce referential integrity by imposing constraints on
database tables. You can create the constraints when you use the CREATE
TABLE or ALTER TABLE commands.
Primary Keys
A primary key ensures that values in a column are unique. So, you can
uniquely identify each row in a table. This makes the UPDATE or DELETE
safe to use, ie., they don't affect other rows. Let's create a credit_cards table.
sqlite> CREATE TABLE credit_cards
 ...> (
 ...> id int primary key not null,
 ...> type char(10) not null,
 ...> user_id int not null
 ...> );
The keyword primary key in the table definition defines id as the primary
key of the credit_cards table. We can see the new table.
sqlite> .tables
credit_cards line_items products 
customers orders users
We can also see the table structure.
sqlite> .schema credit_cards
CREATE TABLE credit_cards
(
id int primary key not null,
type char(10) not null,
user_id int not null
);
Foreign Keys
A foreign key is a column in a table that references the primary key in
another table. They are used to enforce referential integrity.
Let's delete the table we just created.
sqlite> drop table credit_cards;
sqlite> .tables
customers line_items orders products users
Now lets create the credit_cards table again that will use a foreign key.
sqlite> CREATE TABLE credit_cards
 ...> (
 ...> id int primary key not null,
 ...> type char(10) not null,
 ...> user_id int not null,
 ...> FOREIGN KEY (user_id) REFERENCES users(id) 
 ...> );
We can see the schema of the new table.
sqlite> .schema credit_cards
CREATE TABLE credit_cards
(
id int primary key not null,
type char(10) not null,
user_id int not null,
FOREIGN KEY (user_id) REFERENCES users(id) 
);
Here the table definition uses the FOREIGN KEY keyword to make the
user_id field a foreign key, it uses REFERENCES to indicate that it
references the id field in users table. Now, we cannot create any records in
the credit_cards table that does not have a user_id value that does not exist in
the users table.
Here is an example that creates a new credit card for Yosemite Sam.
sqlite> INSERT INTO credit_cards(id, type, user_id)
 ...> VALUES (1, 'VISA', 10);
sqlite> select * from credit_cards;
id type user_id 
---------- ---------- ----------
1 VISA 10 
sqlite> select * from users;
id first_name last_name email 
---------- ---------- ---------- -----------------
1 bugs bunny bbunny@disney.com
2 daffy duck daffy@disney.com 
3 porky pig ppig@disney.com 
4 pluto dog pdog@disney.com 
5 pluto pet pdog@disney.com 
6 tasmanian devil tdevil@disney.com
7 tweety bird tweety@disney.com
8 elmer fudd efudd@disney.com 
9 speedy gonzales gonzales@disney.c
10 yosemite sam sam@disney.com 
11 sylvester sam@disney.com 
12 bosko sam@disney.com
Here is an example that shows creating a row that violates the foreign key
constraint results in an error.
sqlite> INSERT INTO credit_cards(id, type, user_id)
 ...> VALUES (1, 'VISA', 13);
Error: column id is not unique
We can verify that there is only one record in the credit_cards table.
sqlite> select * from credit_cards;
id type user_id 
---------- ---------- ----------
1 VISA 10
Summary
In this article you learned about constraints, primary key and foreign key.
You also learned about how they enforce referential integrity.
Indexes
In this chapter, you will learn how and when to use indexes on one or more
columns in a table.
What are Indexes?
Tables are sorted by primary key, so retrieving rows using primary key is
fast. If you want to retrieve records whereyou use a filter condition that
contains some other column other than the primary key, the database will
start at the first row and look for matches from all the rows in the table. This
is slow. Indexes improve searching and sorting speed. You can define an
index on one or more columns so that the database server keeps a sorted list
of the records for fast lookup.
But indexes come with a cost:
 
Indexes improve the lookup performance at the cost of degrading the
performance of insert, update and delete operations. When any of the
data manipulation operations are executed, the database server has to
update the index dynamically.
Indexed data can take up more storage space than non-indexed data.
Data that is not unique such as country names will not be suitable for
indexing.
If you frequently sort data in a specific order, it could be a good
candidate for indexing.
Create Index
CREATE INDEX statement is used to create indexes. Let's create an index
on the name column of the products table.
sqlite> create index product_name_index
 ...> on products (name);
Indexes must have unique name. In this example, product_name_index is the
index name, ON is used to specify the table name to be indexed followed by
the columns to include in the index inside parentheses. You can describe the
schema of the products table to check if the index has been defined.
sqlite> .schema products
CREATE TABLE products(
id int primary key not null,
price int,
name char(50),
user_id int not null);
CREATE INDEX product_name_index
on products (name);
You can see the index definition after the create table definition.
Summary
In this chapter you learned how and when to use indexes in a table.
Table of Contents
About the Author
Introduction
Create, Insert and Select Statements
Sorting
Search Conditions
Advanced Search Conditions
Wildcard Search
Calculated Fields
Aggregate Functions
Grouping Data
Subqueries
Joins
Inner Join
Advanced Joins
Update and Delete
Constraints
Indexes
	About the Author
	Introduction
	Create, Insert and Select Statements
	Sorting
	Search Conditions
	Advanced Search Conditions
	Wildcard Search
	Calculated Fields
	Aggregate Functions
	Grouping Data
	Subqueries
	Joins
	Inner Join
	Advanced Joins
	Update and Delete
	Constraints
	Indexes