Retrieving data from a single table Flashcards
True or False
SQL is case sensitive?
False
SQL is not case sensitive but keywords are usually CAPITALIZED.
USE
to select what database to query and must be terminated ;
SELECT
which columns to query or * for all columns
FROM
what table to query
MYSQL how to execute a high-lighted sub query (Windows)
shift + ctrl + enter
WHERE
used to filter the data on a conditional basis
True or False
This SQL query is in the correct order of operations?
USE
SELECT
FROM
WHERE
ORDER BY
True
ORDER BY
determines sort order ascending is the default
How do you make a comment in SQL
– this is a comment in SQL crtl + /
True or False
in SQL the FROM, WHERE, and ORDER BY clauses are optional?
True
What does this code produce?
USE
SELECT 1, 2
creates two columns 1 and 2
True or False
The order doesn’t matter in SQL?
False
order matters when querying a database
True or False
White space is ignored in SQL?
True
This code does what?
SELECT
last_name,
first_name,
points,
points + 10
FROM customers
selects columns last_name, first_name, and points columns from customers table and adds 10 points to the points column values then creates a column points + 10 to hold those values.
How do you create an alias in SQL
AS
FROM customers c
How would you change the order of operation in an expression?
with parathesis (points + 10) * 100
True or False
It’s a good idea to use an alias on SQL arithmetic expressions?
True
In SQL this “discount factor” is seen as a
string
SELECT DISTINCT does what?
removes or ignores duplicated values
What is the WHERE clause doing in this query?
- *SELECT** *
- *FROM** Customers
- *WHERE** points > 3000
filtering customers whose points are greater than 3000
What are the SQL comparison operators?
> greater than
>= greater than or equal
< less than
<= less than or equal
= equal
!=, <> not equal
What is the WHERE clause doing in this query?
SELECT *
FROM Customers
WHERE state = ‘VA’
filtering customers by the state of ‘VA’
What is the WHERE clause doing in this query?
- *SELECT** *
- *FROM** customers
- *WHERE** state = ! ‘VA’
filtering customers that are NOT in the state of ‘VA’
What is the WHERE clause doing in this query?
- *SELECT** *
- *FROM** customers
- *WHERE** birth_date > ‘1990-01-01’
filtering customer whose birth date is greater than 01-01-1990
When comparing multiple conditions in SQL what’s needed?
AND, OR, NOT operators
How many conditions does this SQL query possess?
- *SELECT** *
- *FROM** Customers
- *WHERE** birth_date > ‘1990-01-01’ AND points > 1000
there are two conditions (see the WHERE clause)
True or False
In SQL the AND operator is always executed first as a first-order operator?
True
In SQL the NOT clause does what?
- *SELECT** *
- *FROM** Customers
- *WHERE NOT** birth_date > ‘1990-01-01’ OR points > 1000
negates a condition
> negates to <
OR negates to AND
> negates to <
In SQL arithmetic expressions are?
simple calculations performed within a query
- *SELECT** *
- *FROM** order_items
- *WHERE** order_id = 6 AND (quantity * unit_price > 30)
Why can’t you do this in SQL?
WHERE state = ‘VA’ OR‘GA’ OR‘FL’?
the conditions are boolean values and OR‘GA’ OR‘FL’ are string values
How would you refactor this query?
- *SELECT** *
- *FROM** Customers
- *WHERE** state = ‘VA’ OR state = ‘GA’ OR state = ‘FL’
- *SELECT** *
- *FROM** Customers
- *WHERE** state IN (‘VA’, ‘GA’, ‘FL’)
similiar to the python membership operator IN
What is this SQL query doing?
- *SELECT** *
- *FROM** Customers
- *WHERE** state NOT IN (‘VA’, ‘FL’, ‘GA’)
selecting all records from the Customers table and filtering out states VA, GA, and FL
Refactor this code
- *SELECT** *
- *FROM** customers
- *WHERE** points >= 1000 AND points <= 3000
- *SELECT** *
- *FROM** customers
- *WHERE** points BETWEEN 1000 AND 3000
What type of data type is the birth_date column?
- *SELECT** *
- *FROM** customers
- *WHERE** birth_date BETWEEN‘1990-01-01’ AND‘2000-01-01’
string
This query is returning what?
- *SELECT** *
- *FROM** customers
- *WHERE** last_name LIKE‘%y’
all the last names from the customer’s table that end in a y
This query is returning what?
- *SELECT** *
- *FROM** customers
- *WHERE** last_name LIKE‘%y%’
all customers where the last name has a letter y in it
In MySQL following the LIKE statement what does ‘-‘ represent in a string?
a single character
what is any number of characters in MySql regex?
%
what is a represents a single character?
_
What is this query returning?
- *SELECT** *
- *FROM** customers
- *WHERE** phone NOT LIKE‘%9’
all customers whose phone numbers that do not end in 9
What MySQL statement is used for searching string values?
REGEXP regular expression
True or False
This:
WHERE last_name LIKE‘%field%’
is comparable to this:
WHERE last_name REGEXP‘field’
True
This ^ represents what in a MySQL REGEXP
the beginning of a string
‘^waters’
This $ represents what in a MySQL REGEXP
the end of a string
‘field$’
would return Brushfield.
This | represents what in a MySQL REGEXP
a separator to perform multiple string searches simultaneously
WHERE last_name REGEXP‘field | mac | rose’
True or False
This is ‘legal’ in MySQL
- *SELECT** *
- *FROM** customers
- *WHERE** last_name REGEXP‘field | mac | rose’
False
no spacing allowed between ‘field|mac|rose’
What is this query returning?
- *SELECT** *
- *FROM** customers
- *WHERE** last_name REGEXP‘[gim]e’
all customers whose last name ends with e but have a g, i, or m that precedes the e in it.
ge, ie, me
True or False
you can use a range of values in a MySQL REGEXP much like:
WHERE last_name REGEXP‘[a-h]e’
True
List some MySQL REGEXP?
^ beginning
$ end
| logical or (called a pipe)
[abcd]
[a-f] range
This query is returning what?
- *SELECT** *
- *FROM** customers
- *WHERE** first_name REGEXP‘elka|ambur’
all customers whose first name are Elka or Ambur
This query is returning what?
- *SELECT** *
- *FROM** customers
- *WHERE** last_name REGEXP‘ey$|on$’
all customers whose last name ends in ey or on.
This query is returning what?
- *SELECT** *
- *FROM** customers
- *WHERE** last_name REGEXP ‘^my|se’
all customers whose last name starts with my or contains se.
This query is returning what?
- *SELECT** *
- *FROM** customers
- *WHERE** last_name REGEXP‘b[ru]’
all customers whose last name has a b followed by r or u
This query is returning what?
- *SELECT** *
- *FROM** customers
- *WHERE** phone IS NULL
all the customers whose phones values are missing (null)
What is this query returning?
- *SELECT** *
- *FROM** orders
- *WHERE** shipped_date IS NULL
all orders that haven’t shipped
True or False
the ORDER BY clause changes a table’s default (primary key) sorting order
True
by ascending order unless DESC clause is used.
True or False
This query is legal in MySQL
- *SELECT** first_name, last_name
- *FROM** customers
- *ORDER BY** birth_date
True
ordering by a column not in the selection query!
True or False
Sorting data by columns positions is a good practice?
- *SELECT** first_name, last_name
- *FROM** customers
- *ORDER** BY 1, 2
False
if another column is added to the table, changing the order, you could be screwing yourself!
This query is returning what?
- *SELECT** *
- *FROM** customers
- *ORDER BY** points DESC
- *LIMIT** 3
the top three customers in desc order
True or False
the LIMIT clause should be the last line in a query if used.
True
limits the # of records to return
