Intro to SQL and Relational Databases: Queries
-
Overview
- Definitions
- SELECT / UPDATE / DELETE
- WHERE
- AND / OR / NOT
- ORDER BY
- GROUP BY
- ALIASES
- LIKE / IN / BETWEEN
-
What is a Query?
A query is a request made to your database. This can be a request for data or information from a database table or combination of tables, or an action to be performed on the database.
-
Viewing the Data
-
Selects
We’ve now finished all the work we need to do for the devs and are free to explore our own database and come up with some queries that will be able to answer some questions about the students. These updates will keep our boss happy and help our teachers know what they need to do to give students the highest quality of education.
-
Selects
In order to get this done we’ll have to SELECT data out of our database. A SELECT statement will generally have at least two parts:
- A
SELECTclause where we say the columns we want to see - A
FROMclause where we specify which table to pull that data from.
-
SELECT/FROM
- How you ‘get’ the data from the database.
- Specify desired columns, or get all columns with
*
-
SELECT: syntax
SELECT * FROM table;
or
SELECT column1, column2
FROM table;
-
SELECT: example
SELECT * FROM cars;
returns
| ID | MAKE | MODEL | YEAR | MILEAGE |
|---|---|---|---|---|
| 1 | Honda | Civic | 2001 | 98765 |
| 2 | Mazda | 3 | 2017 | null |
| 3 | Volkswagen | Jetta | 2017 | null |
| 4 | Honda | Civic | 2017 | null |
-
SELECT: example
SELECT make, model FROM car;
returns
| MAKE | MODEL |
|---|---|
| Honda | Civic |
| Honda | Civic |
| Mazda | 3 |
| Volkswagen | Jetta |
-
Select: example
SELECT id, first_name, last_name, specialty
FROM zipcode.teachers;
| ID | First Name | Last Name | Specialty |
|---|---|---|---|
| 1 | John | Smith | FRONT END |
| 2 | Tabitha | Schultz | MIDDLE TIER |
| 3 | Jane | Herman | DATA TIER |
-
Where Clause
We’ve been informed by a higher-up that there is a very important project that needs a lead. Anyone who can work on front end development will be able to help immensely. Let’s try and SELECT from teachers again, but this time let’s add a WHERE statement to ensure we only pull a teacher with the FRONT END specialty.
-
Where Clause
- Helps to refine queries/filter out results
- Uses operators for the filtering
- Refine further with
ANDorORclauses
-
WHERE operators
| Symbol | Purpose |
|---|---|
| = | Equal |
| <> | Not equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| BETWEEN | Between an inclusive range |
| LIKE | Search for a pattern |
| IN | To specify multiple possible values for a column |
-
Where Clause: syntax
SELECT * FROM table
WHERE column_name {operator} bound
-
Where Clause: example
SELECT * FROM cars
WHERE make = 'Mazda';
SELECT make, model FROM cars
WHERE year < 2017;
SELECT COUNT(*) FROM cars
WHERE year > 2010;
-
Where Clause: example
SELECT id, first_name, last_name, specialty
FROM zipcode.teachers
WHERE specialty='FRONT END';
| ID | First Name | Last Name | Specialty |
|---|---|---|---|
| 1 | John | Smith | FRONT END |
-
Limit and Order Clauses
This will return a single row with the teacher John Smith who is our only FRONT END specialist. You tell the higher-ups that you think John would be up to the task. They say great, but ask who can take over that teacher’s spot. We’ll want to choose the teacher with the most experience, who isn’t John.
-
ORDER BY
- Orders the results
- Specify the column to sort on
- Can use multiple arguments to break ties
- Optional Ascending (
ASC) or Descending (DESC) operatorASCby default
-
ORDER BY: syntax
SELECT column1, column2
FROM table
ORDER BY column1 {ASC / DESC}
-
ORDER BY: examples
SELECT * FROM cars;
SELECT * FROM cars
ORDER BY model;
SELECT * FROM cars
ORDER BY model DESC;
SELECT * FROM cars
ORDER BY make, year;
SELECT * FROM cars
ORDER BY make DESC, year DESC;
SELECT make, year FROM cars
WHERE make IN ('Mazda', 'Honda')
ORDER BY model DESC;
-
Limit and Order Clauses
We check and see that John’s id is 1, so we’ll keep that in mind. Next we think of how we can find the teacher with the highest years of experience. To do this, we may use an ORDER BY statement. This kind of statement will take a list of fields that we will sort a table on, and the direction we want to sort them. The two directions are ASC and DESC for ascending and descending, respectively.
-
Limit and Order Clauses
SELECT id, room_number, years, teacher_id FROM zipcode.teacher_meta
WHERE teacher_id != 1
ORDER BY years DESC;
We specify DESC here, so that the table will be ordered by years from highest to lowest. This works, but we only want to find the one top teacher id. To do this we can use a LIMIT. The LIMIT clause will take the number of items you want to return.
-
Limit and Order Clauses: example
SELECT teacher_id FROM zipcode.teacher_meta
WHERE teacher_id != 1
ORDER BY years DESC
LIMIT 1;
| teacher_id | |:———-:| | 3 |
Select from the teachers table where id is 3 and find that Jane is the best person to take the extra classes on.
-
WHERE with AND / OR: syntax
SELECT * FROM table
WHERE column_name {operator} bound
{AND / OR} column_name {operator} bound
-
WHERE with AND / OR: example
SELECT * FROM cars
WHERE make = 'Mazda'
OR make = 'Volkswagen';
SELECT * FROM CARS
WHERE model = 'Civic'
AND year = '2017';
-
GROUP BY
- Group results together by a column value
- Allows category-specific aggregate calculations
- Must collapse the groups into the SELECTed values
-
GROUP BY: syntax
SELECT FUNC(column) --Standard SELECT stuff
FROM table
GROUP BY column
-
GROUP BY: example
SELECT make, model, COUNT(*)
FROM cars
GROUP BY make;
returns
| MAKE | MODEL | COUNT(*) |
|---|---|---|
| Honda | Civic | 2 |
| Mazda | 3 | 1 |
| Volkswagen | Jetta | 1 |
-
GROUP BY: example (broken)
SELECT make, model, year, COUNT(*)
FROM cars
GROUP BY make;
returns the error
Column "YEAR" must be in the GROUP BY list
because the Hondas have different years, so it can’t be just grouped by make.
-
UPDATE
- Changes existing values in database
- Affects all values in a column unless
WHEREis present
-
UPDATE: syntax
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
-
UPDATE: example
UPDATE cars
SET year = '2018'
WHERE make = 'Honda'
AND year = '2017';
The table now contains
| ID | MAKE | MODEL | YEAR | MILEAGE |
|---|---|---|---|---|
| 1 | Honda | Civic | 2001 | 98765 |
| 2 | Mazda | 3 | 2017 | null |
| 3 | Volkswagen | Jetta | 2017 | null |
| 4 | Honda | Civic | 2018 | null |
-
UPDATE (without WHERE): example
UPDATE cars
SET mileage = 0;
The table now contains
| ID | MAKE | MODEL | YEAR | MILEAGE |
|---|---|---|---|---|
| 1 | Honda | Civic | 2001 | 0 |
| 2 | Mazda | 3 | 2017 | 0 |
| 3 | Volkswagen | Jetta | 2017 | 0 |
| 4 | Honda | Civic | 2018 | 0 |
-
DELETE
- Removes all selected rows
- Doesn’t care if you didn’t mean to hit
Enter(No warning) - BE CAREFUL!
-
DELETE: syntax
DELETE FROM table
WHERE condition;
-
DELETE: example
DELETE FROM cars
WHERE make = 'Honda'
AND model = 'Civic'
AND year = 2018;
-
DELETE: bad example
DELETE FROM cars;
THIS WILL DELETE EVERYTHING IN THE TABLE!!!
-
Aliases (AS)
- Provides an alias for the named table or value
- Use it to make queries easier to read/shorter
- The AS is optional.
-
Aliases: syntax
SELECT column_name AS alias
FROM table;
SELECT column_name
FROM table AS t
SELECT column_name AS column_alias
FROM table t;
-
Aliases: example
SELECT c.make AS make, c.model AS model, c.year AS year,
c.mileage miles, p.package package, p.price dollarydoos
FROM cars AS c, auto_prices p
WHERE c.id = p.car_id;
returns
| MAKE | MODEL | YEAR | MILES | PACKAGE | DOLLARYDOOS |
|---|---|---|---|---|---|
| Honda | Civic | 2001 | 0 | EX | 2987.99 |
-
(Slightly)More advanced SQL
Everything that we’ve covered thus far is enough to get you started with SQL. This next part is touching on some more complex operations.
Realize that SQL stuff can get super complex, especially when it comes to query optimization and overall DB design. For now, we’ll give you some more tools to be a little better, but we’ve barely scratched the surface of SQL.
-
LIKE and wildcards
SQL lets you use a LIKE operator in WHERE clauses with wildcards.
-
LIKE and wildcards: syntax
SELECT * FROM table
WHERE column LIKE '%some%wildcard%matcher'
-
LIKE and wildcards: example
SELECT * FROM cars
WHERE make LIKE '%a'
returns the following (because their makes all end in a)
| ID | MAKE | MODEL | YEAR | MILEAGE |
|---|---|---|---|---|
| 1 | Honda | Civic | 2001 | 98765 |
| 2 | Mazda | 3 | 2017 | null |
-
BETWEEN
The BETWEEN operator is a logical operator that allows you to specify whether a value in a range or not. The BETWEEN operator is often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements.
-
BETWEEN: Syntax
The following illustrates the syntax of the BETWEEN operator:
expr [NOT] BETWEEN begin_expr AND end_expr;
The expr is the expression to test in the range defined by begin_expr and end_expr. All three expressions: expr, begin_expr, and end_expr must have the same data type.
-
BETWEEN: Syntax
The BETWEEN operator returns true if the value of the expr is greater than or equal to (>=) the value of begin_expr and less than or equal to (<= ) the value of the end_expr, otherwise, it returns zero.
The NOT BETWEEN returns true if the value of expr is less than (<) the value of the begin_expr or greater than (>)the value of the value of end_expr, otherwise, it returns 0.
If any expression is NULL, the BETWEEN operator returns NULL.
In case you want to specify an exclusive range, you can use the greater than (>) and less than (<) operators instead.
-
BETWEEN: Example
SELECT productCode, productName, buyPrice
FROM products
WHERE
buyPrice BETWEEN 90 AND 100;
-
