Introduction to
SQL Updating a Table
To change some of the values of columns of a table, you
can use the UPDATE command. Changes are provided as a
comma-separated list of column/value pairs.
For example, to add one to the weight of an observation
in the kids table where id is 101311 and age is between 9
and 10, we could use:
Introduction to
SQL Updating a Table
To change some of the values of columns of a table, you
can use the UPDATE command. Changes are provided as a
comma-separated list of column/value pairs.
For example, to add one to the weight of an observation
in the kids table where id is 101311 and age is between 9
and 10, we could use:
UPDATE kids SET weight=weight + 1
WHERE id=’101311’ AND
age BETWEEN 9 and 10;
Introduction to
SQL Updating a Table
To change some of the values of columns of a table, you
can use the UPDATE command. Changes are provided as a
comma-separated list of column/value pairs.
For example, to add one to the weight of an observation
in the kids table where id is 101311 and age is between 9
and 10, we could use:
UPDATE kids SET weight=weight + 1
WHERE id=’101311’ AND
age BETWEEN 9 and 10;
Be careful with UPDATE, because if you don’t provide a
WHERE clause, all the rows of the table will be changed.
Introduction to
SQL The SELECT statement
For many of the modern uses of databases, all you’ll need
to do with the database is to select some subset of the
variables and/or observations from a table, and let some
other program manipulate them. In SQL the SELECT
statement is the workhorse for these operations.
Introduction to
SQL The SELECT statement
For many of the modern uses of databases, all you’ll need
to do with the database is to select some subset of the
variables and/or observations from a table, and let some
other program manipulate them. In SQL the SELECT
statement is the workhorse for these operations.
SELECT columns or computations
FROM table
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY column [ASC | DESC]
LIMIT offset,count;
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
SELECT * FROM kids; View
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
SELECT * FROM kids; View
Find the age, race, height and weight for any observations
with weight greater than 80kg and height less than 150cm:
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
SELECT * FROM kids; View
Find the age, race, height and weight for any observations
with weight greater than 80kg and height less than 150cm:
SELECT age,race,height,weight FROM kids View
WHERE weight > 80 AND height < 150;
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
SELECT * FROM kids; View
Find the age, race, height and weight for any observations
with weight greater than 80kg and height less than 150cm:
SELECT age,race,height,weight FROM kids View
WHERE weight > 80 AND height < 150;
Find all information about the 10 tallest observations:
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
SELECT * FROM kids; View
Find the age, race, height and weight for any observations
with weight greater than 80kg and height less than 150cm:
SELECT age,race,height,weight FROM kids View
WHERE weight > 80 AND height < 150;
Find all information about the 10 tallest observations:
SELECT * FROM kids View
ORDER BY height DESC limit 1,10;
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
SELECT * FROM kids; View
Find the age, race, height and weight for any observations
with weight greater than 80kg and height less than 150cm:
SELECT age,race,height,weight FROM kids View
WHERE weight > 80 AND height < 150;
Find all information about the 10 tallest observations:
SELECT * FROM kids View
ORDER BY height DESC limit 1,10;
Find all information about observations where age is from
17 to 18 and weight is from 180 to 185:
Introduction to
SQL Examples of SELECT queries
Suppose we wish to simply see all of the data:
SELECT * FROM kids; View
Find the age, race, height and weight for any observations
with weight greater than 80kg and height less than 150cm:
SELECT age,race,height,weight FROM kids View
WHERE weight > 80 AND height < 150;
Find all information about the 10 tallest observations:
SELECT * FROM kids View
ORDER BY height DESC limit 1,10;
Find all information about observations where age is from
17 to 18 and weight is from 180 to 185:
SELECT * FROM kids WHERE age BETWEEN 17 AND 18
AND weight BETWEEN 180 AND 185; View
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
I COUNT()
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
I COUNT()
I AVG() (mean)
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
I COUNT()
I AVG() (mean)
I SUM()
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
I COUNT()
I AVG() (mean)
I SUM()
I MIN()
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
I COUNT()
I AVG() (mean)
I SUM()
I MIN()
I MAX()
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
I COUNT()
I AVG() (mean)
I SUM()
I MIN()
I MAX()
Since the COUNT for all columns is the same, the form
COUNT(*) is often used.
Introduction to
SQL Summaries and Computations
SQL supports basic arithmetic operations to create new
columns, as well as some summarization functions which
include
I COUNT()
I AVG() (mean)
I SUM()
I MIN()
I MAX()
Since the COUNT for all columns is the same, the form
COUNT(*) is often used.
Other functions (ABS(), FLOOR(), ROUND(), SQRT(), etc.)
may also be available.
Introduction to
SQL Summary and Computation examples
Introduction to
SQL Summary and Computation examples
Find max. height for age between 10 and 11 and race=1:
Introduction to
SQL Summary and Computation examples
Find max. height for age between 10 and 11 and race=1:
SELECT MAX(height) FROM kids View
WHERE age BETWEEN 10 AND 11 AND race = 1 ;
Introduction to
SQL Summary and Computation examples
Find max. height for age between 10 and 11 and race=1:
SELECT MAX(height) FROM kids View
WHERE age BETWEEN 10 AND 11 AND race = 1 ;
By combining with the GROUP BY command, useful
summaries can be obtained.
Introduction to
SQL Summary and Computation examples
Find max. height for age between 10 and 11 and race=1:
SELECT MAX(height) FROM kids View
WHERE age BETWEEN 10 AND 11 AND race = 1 ;
By combining with the GROUP BY command, useful
summaries can be obtained.
Find the average BMI (weight/height2 ∗ 10000) by sex
and race:
Introduction to
SQL Summary and Computation examples
Find max. height for age between 10 and 11 and race=1:
SELECT MAX(height) FROM kids View
WHERE age BETWEEN 10 AND 11 AND race = 1 ;
By combining with the GROUP BY command, useful
summaries can be obtained.
Find the average BMI (weight/height2 ∗ 10000) by sex
and race:
SELECT sex,race,count(*) AS n, View
AVG(weight/(height*height)*10000) AS bmi
FROM kids GROUP BY sex,race;
Introduction to
SQL Summary and Computation examples
Find max. height for age between 10 and 11 and race=1:
SELECT MAX(height) FROM kids View
WHERE age BETWEEN 10 AND 11 AND race = 1 ;
By combining with the GROUP BY command, useful
summaries can be obtained.
Find the average BMI (weight/height2 ∗ 10000) by sex
and race:
SELECT sex,race,count(*) AS n, View
AVG(weight/(height*height)*10000) AS bmi
FROM kids GROUP BY sex,race;
The SUM function can count logical expressions:
Introduction to
SQL Summary and Computation examples
Find max. height for age between 10 and 11 and race=1:
SELECT MAX(height) FROM kids View
WHERE age BETWEEN 10 AND 11 AND race = 1 ;
By combining with the GROUP BY command, useful
summaries can be obtained.
Find the average BMI (weight/height2 ∗ 10000) by sex
and race:
SELECT sex,race,count(*) AS n, View
AVG(weight/(height*height)*10000) AS bmi
FROM kids GROUP BY sex,race;
The SUM function can count logical expressions:
SELECT race,SUM(height > 150)/COUNT(*) View
FROM kids GROUP BY race;
Introduction to
SQL Selecting based on Summaries
Summaries can’t be used in the WHERE clause, but they
can be used in the HAVING clause. For example, suppose
we wanted to find all the IDs in the kids database for
which there were less than 2 observations:
Introduction to
SQL Selecting based on Summaries
Summaries can’t be used in the WHERE clause, but they
can be used in the HAVING clause. For example, suppose
we wanted to find all the IDs in the kids database for
which there were less than 2 observations:
SELECT id FROM kids View
GROUP BY id HAVING COUNT(*) < 2;
Introduction to
SQL Selecting based on Summaries
Summaries can’t be used in the WHERE clause, but they
can be used in the HAVING clause. For example, suppose
we wanted to find all the IDs in the kids database for
which there were less than 2 observations:
SELECT id FROM kids View
GROUP BY id HAVING COUNT(*) < 2;
Get all information about ids that have exactly ten
observations:
Introduction to
SQL Selecting based on Summaries
Summaries can’t be used in the WHERE clause, but they
can be used in the HAVING clause. For example, suppose
we wanted to find all the IDs in the kids database for
which there were less than 2 observations:
SELECT id FROM kids View
GROUP BY id HAVING COUNT(*) < 2;
Get all information about ids that have exactly ten
observations:
SELECT * FROM kids View
GROUP BY id HAVING COUNT(*) = 10;
Introduction to
SQL Selecting based on Summaries
Summaries can’t be used in the WHERE clause, but they
can be used in the HAVING clause. For example, suppose
we wanted to find all the IDs in the kids database for
which there were less than 2 observations:
SELECT id FROM kids View
GROUP BY id HAVING COUNT(*) < 2;
Get all information about ids that have exactly ten
observations:
SELECT * FROM kids View
GROUP BY id HAVING COUNT(*) = 10;
This doesn’t work - it only gives the first observation for
each id.
Introduction to
SQL Subqueries
By putting a SELECT statement in parentheses, you can
use it in other SELECT statements as if it were another
table.
Introduction to
SQL Subqueries
By putting a SELECT statement in parentheses, you can
use it in other SELECT statements as if it were another
table.
SELECT * FROM kids View
WHERE id IN
(SELECT id FROM kids
GROUP BY id
HAVING COUNT(*) = 10);
