Section 1
Preview this deck
What is the most common type of join?
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
3
All-time users
3
Favorites
0
Last updated
1 year ago
Date created
Mar 1, 2020
Cards (70)
Section 1
(50 cards)
What is the most common type of join?
inner join
For which operator is this statement true? If the test expression is equal to one of the expressions in the list, the row is included in the query results.
IN
LIMIT
specifies the number of rows to return
You can use ——-and———logical operators to creating compound conditions that consist of 2 or more conditions.
AND , OR
SELECT clause - 4 techniques you can use to specify the column for a result set
1. * to retrieve all columns in the base table 2. code a list of column names from the base table separated by commas 3. code an expression that uses arithmetic operations 4. code an expression that uses functions ie. CONCAT
expression
a combination of column names and operators that evaluate to a single value
equijoin
when you use the equal operator to join 2 tables on a common column
The WHERE clause specifies
a search condition that's used to filter the rows in the base table. Specifies the conditions that must be met for a row to be included in the result set.
What happens when you don't assign an alias to a calculated column?
MySQL automatically assigns an alias to the calculated column that is the same as the column's expression.
order of precedence
use this to evaluate arithmetic expressions. it goes from left to right, *,/,DIV,% (MOD), +, -
A row cannot be inserted if.....
the values aren't compatible with the corresponding column data types
left outer join result set includes
all the rows from the first, or left table. if the second table does not have a value, it displays as null
A ——-is simply a select statement within another statement.
Subquery
What clause is used to specify a column name?
AS
What operator allows you to search for complex string patterns?
REGEXP. (Regular expressions)
What is a table created using the CREATE statement missing?
definitions of PK, FK, and indexes
Alias is created by using what clause?
AS
DIV returns
the integer quotient of the two numbers (quotient: a result obtained by dividing one quantity by another.)
ORDER BY
specifies how to sot the rows in the result set
SELECT invoice_number AS "Invoice Number". Why is invoice number is in double quotes?
to include a space
SELECT
describes the columns in the result set
like a join a _____ combines data from 2 or more tables. instead of combining columns from base tables, it combines rows from 2 or more result sets.
union
What does the join condition indicate?
how 2 tables should be compared
T or F DEFAULT and NULL are used when inserting values if a column list isn't used?
True INSERT INTO color_sample VALUES (DEFAULT, DEFAULT, NULL)
T or F When you code a literal value for a number, you don't need to enclose it in quotes.
True
T or F you can only use the "USING" clause if the tables include a column with the same name.
true
Implicit Syntax vs Explicit Syntax
-explicit are easier to read because it lets you separate join conditions from search conditions -implicit is older and not as widely used -implicit can result in a cross join which returns a large number of rows -when you use implicit for an inner join, you code the tables in the FROM clause separated by commas. Then you code the JOIN conditions in the WHERE clause. ie. SELECT invoice_number, vendor_name FROM vendors v, invoices i WHERE v.vendor_id = i.vendor_id ORDER BY invoice_number Explicit this is written as SELECT invoice_number, vendor_name FROM vendors INNER JOIN invoices ON vendors.vendor_id = invoices.vendor_id ORDER BY invoice_number
What values can be omitted from a VALUES clause?
ones with a default, accepts null values, or are automatically generated
T or F Unless parenthesis are used, the operations in an expression take place from left to right in order of precedence.
T
Tables are typically joined on the relationship between the ________ and the ___________ of another.
primary key; foreign key
What do you need to include if you don't INSERT INTO using a column list?
the VALUES clause must include a value for every column in the table and they must be listed in the same sequence.
How do you code a qualified column when joining?
type the table name followed by a period followed by the column name. example the column name vendor_id is the same in both tables so you need to qualify it by adding the table name
SELECT * FROM - will select what?
all columns and rows of a table
what is the syntax for the INSERT clause?
specify the name of the table you want to add a row to (INSERT INTO invoices), along with an optional column list (vendor_id, invoice_number, invoice_total). INTO is optional. Follow it with a VALUES clause. VALUES (97, '456789', 8344.50) the values need to be in the same order as the columns list.
a _______ join produces a result set that includes each row from the first table joined with each from from the second table.
cross
if 2 tables have more than one column in common and a natural join is used......
the query would attempt to join both tables and yield an unexpected result
in a _______ join the database automatically joins the two tables based on all columns in the two tables that have the same name.
natural join
What operator allows you to search for simple string patterns?
Like
What rows are included when you inner join?
Only those rows that satisfy the join.
T or F The two expressions used in the between phrases for the range of values are inclusive.
T
The search condition of a WHERE clause consists of....
one or more Boolean expressions that result in a true, false, or null value.
MOD returns
the remainder of a division of two integers
When MySQL evaluates a compound condition, it evaluates the operators in wha t sequence?
NOT, AND, OR (parentheses can be used to override the order)
Default sort for ORDER BY clause
descending - largest to smallest
5 main clauses of SELECT statements
1. SELECT 2. FROM 3. WHERE 4. ORDER BY 5. LIMIT
how is the code different when doing an equijoin vs other joins?
you code a USING clause instead of an ON clause to specify the join
What kind of join returns all of the rows from one of the tables involved in the join, regardless of whether the join condition is true?
outer join
cartesian product
the result of a cross join
FROM
names the base table from which the query retrieves data
What is the MySQL default format for a date?
YYYY-MM-DD
Section 2
(20 cards)