Primary Key
A field (or group of fields) that uniquely identifies a row in a table. Primary keys cannot be NULL and cannot be modified or
... [Show More] deleted.
Unique Key
A field (or group of fields) that uniquely identifies a row in a table. Unique keys can be NULL and can be modified or deleted.
Foreign Key
A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables
SELECT
Retrieves data from one or more tables.
SELECT *
FROM orders
FROM
Specifies the table or tables to retrieve data from.
Example:
SELECT *
FROM employee
WHERE jobCode = 'FTE' AND lastname = 'James'
WHERE
Filters data based on a specified condition.
Example:
SELECT *
FROM employee
WHERE jobCode = 'FTE' AND lastname = 'James'
AND
A logical operator used to combine multiple conditions in a WHERE clause or JOIN function. This operator only returns values that fulfill all the criteria in the query.
Example:
SELECT *
FROM orders
WHERE quantity > 10 AND price > 100;
OR
SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id AND customers.country = 'USA' AND orders.order_date >= '2022-01-01';
OR
A logical operator used to combine multiple conditions in a WHERE clause or JOIN function. This operator filters data based on any of the criteria in the query.
Example:
SELECT *
FROM orders
WHERE quantity > 10 OR price > 100;
OR
SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id OR orders.order_date >= '2022-01-01';
LIKE
A logical operator used to search for a specific pattern in a column in a WHERE clause
SELECT Location, date, Population, total_cases, (total_cases/population)*100 as RollingTotalInfected
FROM PortfolioProject.CovidDeaths
WHERE location LIKE '%states%'
Wildcard
A special character used in a query string pattern, matching any character or characters in search results.
1. %: Represents zero, one, or multiple characters in a string
2. _: Represents a single character in a string
Example: 'J%' returns all values that begin with J, and '%J' returns all values that end with J
ORDER BY
Sorts data in ascending or descending order based on one or more columns.
SELECT department, COUNT(*) as total_employees
FROM employees
GROUP BY department
ORDER BY total_employees DESC;
GROUP BY
Groups data based on one or more columns.
SELECT department, COUNT(*) as total_employees
FROM employees
GROUP BY department
ORDER BY total_employees DESC;
GROUP BY vs ORDER BY
GROUP BY orders aggregate functions
ORDER BY orders a result set in either ascending (default) or descending
HAVING
Filters data based on a specified condition for groups created by GROUP BY.
SELECT customer_id, SUM(order_amount) as total_order_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) >= 1000;
COUNT
Returns the number of rows that match a specified condition.
SELECT addr_state, COUNT(*) as Total_Loans_By_State
FROM LoanStats_2018Q3
GROUP BY addr_state
ORDER BY 2 DESC;
SUM
Returns the sum of values in a specified column.
SELECT SUM(quantity)
FROM order_details
WHERE order_id = 10248;
AVG
Returns the average of values in a specified column.
SELECT AVG(price)
FROM products
WHERE category = 'Books';
MAX
Returns the maximum value in a specified column.
SELECT MAX(price)
FROM products
WHERE category = 'Electronics'
MIN
Returns the minimum value in a specified column.
SELECT MIN(price)
FROM products
WHERE category = 'Clothing'
DISTINCT
Returns unique values in a specified column.
SELECT DISTINCT category
FROM products;
INNER JOIN
Returns all rows from both tables where the join condition is true.
SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
RIGHT JOIN
Returns all rows from the right table and matching rows from the left table. The right table below will be "orders"
SELECT *
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
LEFT JOIN
Returns all rows from the left table and matching rows from the right table. The left table below will be "customers"
SELECT *
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
FULL OUTER JOIN
Returns all the rows from both tables whether it has been matched or not.
SELECT *
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id
UNION
Combines the result sets of two or more SELECT statements into a single result set.
SELECT product_name
FROM products
WHERE category = 'Books'
UNION
SELECT product_name
FROM products
WHERE category = 'Magazines'; [Show Less]