Operators Flashcards
INTERSECT
Combines two SELECT statements and returns only the dataset that is common in both the statements
EXCEPT
Returns the distinct rows that are present in the result set of the first query but not in the result set of the second query.
LIKE
The LIKE command is used in a WHERE clause to search for a specified pattern in a column.
You can use two wildcards with LIKE:
% - Represents zero, one, or multiple characters
_ - Represents a single character (MS Access uses a question mark (?) instead)
REGEXP
Used to search for a specified pattern using regular expression pattern matches.
UNION
Combines the data from the result of two or more SELECT statements into a single result set. This operator removes any duplicates present in the results being combined.
UNION ALL
Combines the data from the result of two or more SELECT statements into a single result set. This operator DOES NOT REMOVE any duplicates present in the results being combined.
IS NULL
Used to test for empty values (NULL values).
AND
Displays a record if all the conditions stated in the query are TRUE.
Example:
SELECT * FROM Customers
WHERE Country = ‘Germany’
AND City = ‘Berlin’
AND PostalCode > 12000;
This will display all Customers that are located in Berlin, Germany with a postal code 12000.
OR
Displays a record in which any of the conditions stated in the query are TRUE.
Example:
SELECT * FROM Customers
WHERE Country = ‘Germany’
OR City = ‘Berlin’
OR PostalCode > 12000;
This will display all Customers that are located in any city named Berlin (regardless of the country it’s in), customer in any city in Germany and customers that have their postal code as 12000.
=
Returns values that are “Equal To” the value input in the query
>
Returns values that are “Greater Than” the value input in the query
<
Returns values that are “Less Than” the value input in the query
> =
Returns values that are “Equal To or Greater Than” the value input in the query
<=
Returns values that are “Equal To or Less Than” the value input in the query
<>
Returns values that are “NOT Equal To” the value input in the query
%
This represents the “modulo”. It is used to get the remainder from a division.
IN
The IN operator allows you to specify multiple values in a WHERE clause. Example:
SELECT customerName, state
FROM customers
WHERE state IN (‘MA’, ‘CA’, ‘PA’)
This will return all of the customers that are registered in the database “customers” as located in the states of MA (Massachussets), CA (California) or PA (Pennsylvania).
NOT IN
The NOT IN operator will return all records that are NOT any of the values in the list in a WHERE clause.
Example:
SELECT customerName, country
FROM customers
WHERE state NOT IN (‘USA’, ‘France’, ‘Germany’)
This will return all of the customers that are registered in the database “customers” and are NOT located in USA, France or Germany.
CROSS JOIN
Used to generate a paired combination of each row of the first table with each row of the second table.
INNER JOIN
Selects records that have matching values in both tables.
When using a JOIN operator, if both tables are using the same field name what syntax could you use to call for that specific field in each table?
USING (nameofthefield);
When using a JOIN operator, if the fields on each table do NOT share the same name, what syntax should you use to call for that specific field from each table?
ON table1.nameofthefield = table2.nameofthefieldonthistable
Which table will LEFT JOIN retrieve the information from?
The first table
Which table will RIGHT JOIN retrieve the information from?
The second table
LEFT JOIN or LEFT OUTER JOIN
Returns all records from the left table (table1), and the matching records from the right table (table2)
RIGHT JOIN or RIGHT OUTER JOIN
Returns all records from the right table (table2), and the matching records from the right table (table1)
SELF JOIN
Used to join a table to itself as if the table were two tables
What do you need to specify in a SELF JOIN?
The table aliases
ORDER BY
Used to sort the fetched data in either ascending or descending according to one or more columns. If not specified it will order in ascending order by default.
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
What keyword do you need to add to the ORDER BY syntax to organize the results in ascending order?
ASC
What keyword do you need to add to the ORDER BY syntax to organize the results in descending order?
DESC
What function can we use to sort the results in a customized sort order?
field
LIMIT
Used to return a limited number of records.
SELECT * FROM Customers
LIMIT 3;
You can also specify the starting point.
SELECT * FROM Customers
LIMIT 100, 3;
The ‘100’ is specifying the line number you want the query to start from. If not specified, it will automatically start from the first line.
GROUP BY
Groups rows that have the same values into summary rows, like “find the number of customers in each country”.
HAVING
Filters a result set to ONLY include records that fulfill a specified condition. It is used in aggregate functions instead of the clause WHERE, which doesn’t work in aggregate functions.
What keyword do we use instead of WHERE in an aggregate function to meet certain conditions?
HAVING
WHERE does not work in aggregate functions
WITH ROLLUP
Is an extension of the GROUP BY clause that produces sub-total of each group set (in addition to the grouped rows).
EXIST
