Clauses, Operators Flashcards
(56 cards)
DISTINCT
Removes duplicates. Ensures that each value only shows up once in the results.
SELECT DISTINCT id
FROM customers
TOP
Limits the data; restricts the number of rows
SELECT TOP 3 *
FROM customers
HAVING vs WHERE clause
If you use the HAVING clause, you need to filter data after aggregation.
If you use WHERE clause, the aggregation does not need to occur first
Example:
Find the avg score for each country, considering only customers with a score not equal to zero, and return only those countries with an avg score greater than 430.
SELECT
country,
avg (score) AS avg_score
FROM customers
WHERE score <> 0
GROUP BY country
HAVING avg (score) > 430
SQL pulls countries
THEN scores that are not equal to zero
THEN groups by country
THEN calculates the avg score
THEN filters out the scores that are greater than 430
*If didn’t use HAVING in above example could get the wrong results.
It would filter out any scores that were greater than 430 and THEN calculate avg.
Want it to calculate avg and THEN filter out scores greater than 430.
SQL Aggregate Functions
A function that performs a calculation on a set of values and returns a single value.
MIN (): smallest value in selected col
MAX (): largest value in selected col
COUNT (): number of rows in a set
SUM (): Total sum of a numerical col
AVG (): Avg value of numerical col
SQL arithmetic Operators
+ Add
- Subtract
* Multiply
/ Divide
% Modulo
SQL Comparison Operators
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than equal to
<> Not equal to
WHERE
Filters data based on a condition
Syntax:
SELECT
country
SUM (score)
FROM table
WHERE score>400
GROUP BY country
HAVING SUM (score)>800
NOTE: WHERE goes between FROM and GROUP BY
HAVING
Filters aggregated data
Syntax:
SELECT
country,
SUM (score)
FROM table
GROUP BY country
HAVING condition
GROUP BY
Aggregates data (i.e., combines 2 columns).
E.g., adds up scores for each country.
Ex//
SELECT country, SUM (score) AS Total_Score
FROM customers
GROUP BY country
ORDER BY
Sorts data. Use ASC (lowest to highest: 1, 2, 3) or DESC (highest to lowest: 5, 4, 3, 2, 1).
Default if not specified is standardly ASC.
COUNT
Count total number of something (e.g., to count the total number of customers).
SELECT COUNT (id)
FROM customers
WHERE condition
Coding Order of Queries
SELECT DISTINCT TOP 2
Col1,
SUM(col2)
FROM table
WHERE col=10
GROUP BY col1
HAVING SUM(col2)>30
ORDER BY col1 ASC
If you want to filter data before aggregation:
SELECT DISTINCT TOP 2
Col1,
Col2
FROM table
WHERE col=10
If you want to group the data:
SELECT DISTINCT TOP 2
Col1,
SUM(col2)
FROM table
WHERE col=10
GROUP BY col1
If you want to filter the data after aggregation:
SELECT DISTINCT TOP 2
Col1,
SUM(col2)
FROM table
WHERE col=10
GROUP BY col1
HAVING SUM(col2)>30
ORDER BY col1 ASC
INT
Integar
Only contains numbers
VARCHAR
String/text
Or numbers
Or symbols
Primary key
Every table has this–necessary to have one unique identifier (e.g., a customer ID for each customer).
UPDATE
DML
Change the content of already existing rows.
UPDATE customers
SET score
WHERE id = 6
*WARNING: if you execute the query without a WHERE, all rows will be updated.
INSERT
DML
Manipulate data (i.e., insert new rows or update rows)
2 methods: manually OR using a table
INSERT INTO table_name (col1, col2,col3)
VALUES (value1,value2,value3)
Columns: cols that data will be entered into
Values: data that will be entered
NOTE: if you don’t specify the cols, SQL will expect you to insert values into each col. *Sometimes you’ll just eant to insert values into a few cols (e.g., col1, col3, col7). In that case, you need to specify the cols.
RULE: match the number of cols and values.
Ex//
INSERT INTO customers (id, first name, country, score)
VALUES
(6, ‘Anna’, ‘USA’, NULL)
Id=6, first name=Anna, country=USA, score=NULL
What operator(s) should you use with NULL?
Always use IS NULL or IS NOT NULL
Ex//
SELECT *
FROM customers
WHERE score = NULL
*Above query will return no results, even when there is a NULL value
BUT below query will return results (if a NULL value is present).
SELECT *
FROM customers
WHERE score IS NULL
TRUNCATE
DML
Use if you want to delete everything from a table.
It is faster than using DELETE; use TRUNCATE when working with large tables.
Not SQL BUT order of operations in math
PEMDAS
Parentheses
Exponents
Multiplication
Division
Addition
Subtraction
SQL Logical Operators
AND: all conditions must be true
OR: At least one condition must be true
NOT: Excluded matching values
BETWEEN: Check if a value is within a range
IN: Check if a value exists in a list
LIKE: Search for a pattern in a text
BETWEEN
SQL logical operator
Syntax:
WHERE (col name) BETWEEN (value) AND (value)
*NOTE: BETWEEN is inclusive so if say between 100 and 500, the numbers 100 and 500 will be included.
If you don’t want to include either 100 or 500:
WHERE score >= 100 AND score <=500
SQL comparison operators
= equal to
<> or =! Not equal to
X > greater than
X >= greater than or equal to
X< less than
X<= less than or equal to
SQL logical operators
AND
OR
NOT
