SQL
Schema tab shows the different databases
icon w a table and a thunder shows all the data in the table
Retrieving data from a table
to choose a database:
“USE [database name]”
(ideal to use upper case for the sql commands and lower case for anything else)
to choose a column
“SELECT [column name]”
for multiple and specific columns separate column names with a comma
to select all columns use and asterisks “*” (star)
to choose from a table
“SELECT *
FROM [table name]”
to choose with a specific restraint
“WHERE [restraint or specific requirement]
ex: “WHERE customer_id = 1”
to have the data in a specific order
“ORDER BY [how you want it ordered]”
ex: “ORDER BY first_name”
Terminate statements with a “;'“
Change command to a comment use two hyphens “--”
Better to put each clause on one line
Select Clause in Detail
To display multiple and specific columns, separate the column names with a comma
the order of the column names typed out will be the order that it displays in
you can also include mathematical equations/arithmetic expression if needed
ex: “SELECT last_name, first_name, points + 10”
when a command line is gets too long, you can break them into their own line
ex: SELECT
last_name,
first_name,
points,
points * 10 + 100
any arithmetic expression will follow the orders of operation in math
change the order of operations with parentheses
ex: (points + 10) * 100
to give a command a name use “AS [‘name’]”
ex: (points + 10) * 100 AS ‘discount factor’
to filter out any duplicates use “SELECT DISTINCT”
Practice Exercise -
— return all the products
— name
— unit price
— new price (unit price * 1.1)
Solution:
SELECT
name,
unit_price,
unit_price * 1.1 AS ‘new price’ (or unit_price * 1.1 AS new_price)
FROM products
WHERE Clause
a clause to filter out unneeded information / obtain information under certain conditions
comparison operators
greater than = ‘>’
greater than or equal to = ‘>=’
less than = ‘<‘
less than or equal to = ‘<=’
equal = ‘=’
not equal = ‘!=’ or ‘<>’
example using comparison operators
ex: finding customers with more than 3000 points
SELECT *
FROM customers
WHERE points > 3000
ex: finding customers from the state of Virginia
SELECT *
FROM Customers
WHERE state = ‘VA’ or “VA”
ex: finding customers not from the state of Virginia
SELECT *
FROM Customers
WHERE state <> ‘va’ or != ‘va’
ex: finding customers born after 1990
SELECT *
FROM Customers
WHERE birth_date > ‘1990-01-01’
make sure that the format of the date is ‘YYYY-MM-DD’
Practice Exercise -
— Get the orders placed this year (2024)
Solution:
SELECT *
FROM orders
WHERE order_date >= ‘2024-01-01’
AND, OR, and NOT Operators
the “AND” operator can be used to add onto a condition
the query will display information that matches BOTH conditions
ex: using “AND” operator
SELECT *
FROM customers
WHERE birth_date > ‘1990-01-01’ AND points > 1000
the “OR” operator
the query will display information that matches at least one of the conditions
ex: using “OR” operator
SELECT *
FROM customers
WHERE birth_date > ‘1990-01-01’ OR points > 1000
combining both OR and AND
the query will display information that will match at least one of the "OR” conditions and while satisfying the “AND“ condition
ex: using “OR” operator
SELECT *
FROM customers
WHERE birth_date > ‘1990-01-01’ OR points > 1000 AND
state = ‘VA’
the ‘AND’ command is evaluated first before ‘OR’
can change the order using parentheses
ex: changing the order of operation
SELECT *
FROM customers
WHERE birth_date > ‘1990-01-01’ OR
(points > 1000 AND state = ‘VA’)
the “NOT” operator
will negate and not show the information that is presented on the command lines
preferably best to have parentheses around the entire condition
ex: changing the order of operation
SELECT *
FROM customers
WHERE NOT (birth_date > ‘1990-01-01’ OR points > 1000)
information displayed will show customers born BEFORE 1990 and has LESS than 1000 points
w/out the NOT command it becomes
SELECT *
FROM customers
WHERE (birth_date <= ‘1990-01-01’ AND points <= 1000)
this command would display the same information as the previous
Practice Exercise -
— From the order_items table, get the items
— for order #6
— where the total price is greater than 30
— where total price = unit_price * quantity
Solution:
SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price * quantity > 30
IN Operator
SELECT *
FROM Customers
WHERE state = ‘VA’ OR state = ‘GA’ OR state = ‘FL’
The example above cannot be written without restating the conditions “state =”:
SELECT *
FROM Customers
WHERE state = ‘VA’ OR ‘GA’ OR ‘FL’
Because OR is meant to combine conditions (in this case, state =) with a string (‘VA’, ‘GA’, ‘FL’) you need to include the condition of “state =”
You can shorten this query by using the IN operator
SELECT *
FROM Customers
WHERE state IN (‘VA’, ‘GA’, ‘FL’)
When using IN the order of the list doesn’t matter
To exclude the items on the list from your query use “NOT IN”
SELECT *
FROM Customers
WHERE state NOT IN (‘VA’ , ‘GA’ , ‘FL’)
This will show data of customers that are in other states than VA, GA, or FL
Practice Exercise -
SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72)
BETWEEN Operator
Use the BETWEEN operator when searching for a value within a certain constraint range
One way using AND
SELECT *
FROM customers
WHERE points >= 1000 AND points <= 3000
to simplify - used BETWEEN
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
Practice Exercise -
SELECT *
FROM customers
WHERE birth_date BETWEEN ‘1990-01-01’ AND ‘2000-01-01’
LIKE Operator
SELECT *
FROM customers
WHERE last_name LIKE ‘b%’
% represents any number of characters after the b
the percentage could be placed before or after the character
ex: %b%
this would mean that there could be any number of characters before or after b
Each “_” underscore would signify a single character
ex: WHERE last_name LIKE ‘_y’
the underscore means that there should be one character before a y in the name
y has to be the second character
you can place underscores in between two characters
Practice Exercise -
Get the customers whose
addresses contain TRAIL or AVENUE
phone numbers end with 9
SELECT*
FROM customers
WHERE addresses LIKE ‘%trail%’ OR
address LIKE ‘%avenue%’ AND
phone_numbers ‘%9’
REGEXP Operator
stands for regular expression
instead of using LIKE ‘%field%’
use
WHERE last_name REGEXP ‘field’
in this case, the % is not needed and it will still look for information that contains the word field
using the ^ symbol would signify the beginning of a string
ex: WHERE last_name REGEXP ‘^field’ would mean that field has to be the first part of the last name
using the $ symbol would signify the end of a string
ex: WHERE last_name REGEXP ‘field$’ field would need to be at the end of the last name
you could used a bar ‘|’ to look for another pattern essentially means “or”
ex: WHERE last_name REGEXP ‘field|mac|rose’
to complicate it more
WHERE last_name REGEXP ‘^field|^mac|^rose’
this would mean that the last name should start with field, mac, or rose
instead of a ^ you could add $ at the end of field
if you want to look for names that start or come after with a certain character before a specific character use [ ]
ex: WHERE last_name REGEXP ‘[gim]e’
this would mean names that contain ge, ie, me
ex: WHERE last_name REGEXP ‘e[fmq]’
this would mean names that contain ef, em, eq
by using a hyphen, it can give you a range of letters rather than listing it out
ex: WHERE last_name REGEXP ‘[a-h]e’
recap
^ - beginning of a string
$ - end of a string
| - logical or to supply multiple search patterns
[abcd] - to match any single characters listed in the brackets
[a-f] - hyphen inside a bracket to represent a range of characters
Practice Exercise -
get the customers whose
first names are ELKA or AMBUR
SELECT *
FROM customers
WHERE first_name REGEXP ‘elka|ambur’
last names end with EY or ON
SELECT *
FROM customers
WHERE last_name REGEXP ‘ey$|on’
last names start with MY or contains SE
SELECT *
FROM customers
WHERE last_name REGEXP ‘^my|^se’
last names contain B followed by R or U
SELECT *
FROM customers
WHERE last_name REGEXP ‘b[ru]’ or ‘br|bu’
how would you retrieve product names that contain ‘tent’
SELECT *
FROM product
WHERE product_name REGEXP ‘tent’