mySQL Flashcards
Introduction
Get all columns from a table
SELECT *
FROM table
Get a column from a table
SELECT column
FROM table
Get multiple columns from table
SELECT col1, col2
FROM table
Use alias names
AS
SELECT col1, col2 AS col2_new
FROM table
Arrange the rows in ascending order of values in by a specific column
ORDER BY
SELECT col1, col2
FROM table
ORDER BY col2
Arrange the rows in descending order of values in column
ORDER BY col DESC
SELECT col1, col2
FROM table
ORDER BY col2 DESC
Limit the number of rows returned
LIMIT
SELECT *
FROM table
LIMIT 2
Get unique values, filtering out duplicate rows, returning only unique rows.
DISTINCT
SELECT DISTINCT column
FROM table
Get rows where a number is greater than a value
WHERE col1 > n
SELECT col1
FROM table
WHERE col1 > value
Get rows where a number is greater than or equal to a value
WHERE col >=n
SELECT col1
FROM table
WHERE col1 >= value
Visualize Concatenating Columns with a New Name
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name
FROM employees;
Visualize Using in Aggregations
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
Get rows where a number is equal to a value
SELECT col1
FROM table
WHERE col1 = value
Get rows where a number is not equal ( WHERE col != n)
SELECT col1
FROM table
WHERE col1 <> value
Get rows where a number is between two values (inclusive)
SELECT col1
FROM table
WHERE col1 BETWEEN value1 AND value2
Get rows where text is equal to a value
SELECT col1, col2
FROM table
WHERE col1 = ‘string’
Get rows where text is one of several values
SELECT col1, col2
FROM table
WHERE x IN (‘string’, ‘string2’)
Get rows where text contains specific letters with WHERE col LIKE ‘%abc%’ (% represents any characters)
SELECT col1
FROM table
WHERE col1 LIKE ‘%abc%’
Get the rows where one condition and another condition holds with WHERE condn1 AND condn2
SELECT col1, col2
FROM table
WHERE col1 < value
AND col2 > value2
Get the rows where one condition or another condition holds with WHERE condn1 OR condn2
SELECT col1
FROM table
WHERE col1 < value
OR col2 > value
Get rows where values are missing with WHERE col IS NULL
SELECT col1, col2
FROM table
WHERE col1 IS NULL
Get rows where values are not missing with WHERE col IS NOT NULL
SELECT col1, col2
FROM table
WHERE col1 IS NOT NULL
Get the total number of rows
SELECT COUNT(*)
FROM table
Get the total value of a column
SELECT SUM(col)
FROM table
SQL: Get the mean value of a column
SELECT AVG(col)
FROM table
Get the maximum value of a column
SELECT MAX(col)
FROM table
Get summaries grouped by values
SELECT col1, COUNT(*)
FROM table
GROUP BY col1
Get summaries grouped by values, in order of summaries
SELECT col1, SUM(col2) AS totals
FROM table
GROUP BY col1
ORDER BY totals DESC
Get rows where values in a group meet a criterion with GROUP BY col HAVING condn
SELECT SUM(col) AS totals
FROM table
GROUP BY totals
ORDER BY totals DESC
HAVING totals > value
Filter before and after grouping with WHERE condn_before GROUP BY col HAVING condn_after
SELECT col1, SUM(col2) AS totals
FROM table
WHERE col3 = ‘string’
GROUP BY col1
ORDER BY totals DESC
HAVING totals > value
Create a table
CREATE TABLE table_name (
column_name data_type constraints
);
Designate a primary key
A. column_name data_type PRIMARY KEY
B. PRIMARY KEY (column_name)
A. CREATE TABLE table_name(
col CONSTRAINT PRIMARY KEY);
B. ALTER TABLE my_table
ADD CONSTRAINT pk_my_table PRIMARY KEY (col);
What are the data types?
Numeric, string, date/time
Numeric datatype
INT
FLOAT
DOUBLE
BIT
String data
CHAR
VARCHAR
TEXT
JSON
Date/Time
DATE
TIME
YEAR
DATETIME
Constraints
Rules enforced on data to ensure accuracy, consistency, and integrity.
Primary key, Foreign key, Unique, Not Null, Default, Null, Check, Auto_Increment, Index
Primary Key
Ensures that a column (or combination of columns) has unique values and cannot contain NULL values.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);
FOREIGN KEY
Establishes a relationship between two tables
CREATE TABLE table_name (
col1 INT PRIMARY KEY,
col2 INT,
FOREIGN KEY (col3) REFERENCES other_table(col)
);
UNIQUE
Ensures that all values in a column are unique but allows a single NULL value
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
NOT NULL
Prevents a column from having NULL values.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL
);
Default
Sets a default value for a column if no value is specified during an INSERT
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_status VARCHAR(50) DEFAULT ‘Pending’
);
CHECK (Available in MySQL 8.0+)
Ensures that all values in a column meet a specified condition.
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);
AUTO_INCREMENT
Automatically generates a unique number for a column (often used with PRIMARY KEY)
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
INDEX
Improves query performance by creating a fast lookup for columns. Not a direct constraint, but often used alongside UNIQUE or FOREIGN KEY
CREATE INDEX idx_name ON table(column);
Visualize combining multiple constraints
CREATE TABLE marvel_superheroes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
alias VARCHAR(100) NOT NULL,
first_appearance YEAR NOT NULL,
team VARCHAR(100),
active_status BOOLEAN DEFAULT TRUE
);
Visualize inserting multiple rows
INSERT INTO marvel_superheroes (name, alias, first_appearance, team, active_status)
VALUES
(‘Steve Rogers’, ‘Captain America’, 1941, ‘Avengers’, TRUE),
(‘Tony Stark’, ‘Iron Man’, 1963, ‘Avengers’, FALSE),
(‘Peter Parker’, ‘Spider-Man’, 1962, ‘Avengers’, TRUE),
(‘T'Challa’, ‘Black Panther’, 1966, ‘Avengers’, TRUE),
(‘Logan’, ‘Wolverine’, 1974, ‘X-Men’, TRUE),
(‘Wanda Maximoff’, ‘Scarlet Witch’, 1964, ‘Avengers’, TRUE),
(‘Natasha Romanoff’, ‘Black Widow’, 1964, ‘Avengers’, FALSE),
(‘Scott Summers’, ‘Cyclops’, 1963, ‘X-Men’, TRUE);
Delete data or row from table
DELETE FROM table
WHERE column = ‘string’;
Update data
UPDATE table
SET column = TRUE
WHERE column2 = ‘string’;
WHERE clause
A piece of conditional logic that limits the amount of data returned. Usually found after FROM clause
In and Not IN
SELECT column
FROM table
WHERE value IN (val1,val2,val3)
Visualize how to create a table
CREATE TABLE {table_name} (
{column1}{datatype1}{constraint1}
{column2}{datatype2}{constraint2}
{column3}{datatype3}{constraint3}
)
What are column constraints
Keywords that give special properties to columns:
NOT NULL, NULL, UNIQUE, PRIMARY KEY, CHECK (col < num)
INSERT INTO
INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);
table_name: The name of the table where you want to insert data.
(column1, column2, column3, …): A list of columns where the values will be inserted.
VALUES (value1, value2, value3, …): The values to insert into the specified columns.
What happens when you omit column names in INSERT INTO
The values must match the order of the columns in the table schema.
INSERT INTO customers
VALUES (5, ‘Emily’, ‘Clark’, ‘ec@example.com’);
UPDATE
UPDATE table_name
SET column1 = val1, col2 = val2
WHERE condition;
omitting where updates all rows in table
COALESCE
Used to return the first non-NULL value from a list of arguments, to replace NULL values in queries or reports, or to use alternative data when primary data is unavailable
COALESCE(val1, val2, val3)
Visualize how to return NULL values with default values while querying a column
SELECT
COALESCE(phone_number, ‘No Number’) AS contact_info
FROM customers;
Visualize how to return the first non-NULL value from several columns
SELECT
COALESCE(firstname, middlename,lastname, ‘No names available’) AS students
FROM university
Cartesian Product
occurs when every row from one table is combined with every row from another table because there’s no condition to limit which rows are matched.
Normalization
The process of organizing data into separate tables to minimize redundancy
Denormalization
combining data from multiple tables into one for easier querying or reporting.
JOIN dept AS d ON sp.dept_no = d.dept_no
JOIN sale AS sa ON sa.emp_no = sp.emp_no
Explain this JOIN code:
SELECT col1, col2, col3
FROM table1 AS t1
JOIN table2 AS t2 ON t1.column = t2.column;
SELECT col1, co2, col3 Specifies which columns to include in the result set.
FROM table1 AS t1 Indicates the base table with the alias
t1.column = t2.column; Specifies the condition for the JOIN, ensuring that rows from table1 and table2 are matched based on the equality of t1.column and t2.column
Cross Join
SELECT table1.col1, table2.col2
FROM table1
CROSS JOIN table2;
combines every row from one table with every row from another table. It produces the Cartesian product
INNER JOIN
retrieves rows from both tables that satisfy the join condition. If you simply say, “Join this table to that table,” you will do what is known as an inner join. It combines each row from the first table with every row from the second table, keeping only the rows in which the JOIN condition evaluates to true.
SELECT
table1.col1,
table2.col2 AS ‘Column Alias’
FROM table1
JOIN table2 ON table2.primary_key = table1.foreign_key;
OUTER JOIN
An OUTER JOIN retrieves all rows from one or both tables, depending on the type of join, and fills unmatched rows with NULL.
SELECT table1.col1, table2.col2
FROM table1
FULL OUTER JOIN table2
ON table1.foreign_key = table2.primary_key;
LEFT JOIN (LEFT OUTER JOIN)
returns all rows from the left table, and matched rows from the right table. If there’s no match, NULL is returned for the right table’s columns.
SELECT table1.col1, table2.col2
FROM table1
LEFT JOIN table2
ON table1.foreign_key = table2.primary_key;
RIGHT OUTER JOIN
returns all rows from the right table, and matched rows from the left table. If there’s no match, NULL is returned for the left table’s columns.
SELECT table1.col1, table2.col2
FROM table1
RIGHT JOIN table2
ON table1.foreign_key = table2.primary_key;
FULL OUTER JOIN
combines the results of a LEFT JOIN and a RIGHT JOIN. It returns all rows from both tables, filling NULL for unmatched rows.
SELECT *
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
Connect to a database
USE database
Add a Primary key
CONSTRAINT name_of_constraint PRIMARY KEY (column)
place this line at the bottom of the table
or
CREATE TABLE table (
column char(5) NOT NULL PRIMARY KEY
)
or
ALTER TABLE table
ADD CONSTRAINT name_of_constraint PRIMARY KEY (column)
Create a database
CREATE DATABASE my_database;
Verify database
SHOW database;
Create A View
CREATE VIEW view_name (col1, alt_col2_name, calc_col)
AS
SELECT col1, col2 AS alt_col2_name, col1 + col2 AS calc_col
FROM table_name;
Aliases to avoid duplicates
CREATE VIEW order_summary (order_id, customer_name, total_price)
AS
SELECT o.id AS order_id, c.name AS customer_name, o.price + o.tax AS total_price
FROM orders o
JOIN customers c ON o.customer_id = c.id;
ALTER VIEW
ALTER VIEW view_name AS
SELECT new_columns
FROM table_name;
DROP View
To delete a view, use the DROP VIEW statement.
DROP VIEW view_name;
CHECK OPTION
The WITH CHECK OPTION ensures that any updates or inserts through the view comply with the conditions in the view’s query.
CREATE VIEW view_name
AS
SELECT columns
FROM table_name
WHERE condition
WITH CHECK OPTION;
REFERENCES
Creates the foreign key
FOREIGN KEY (fk_col_name)
REFERENCES target_table_name (pk_col_name);
The ON DELETE clause to maintain referential integrity
ON DELETE CASCADE
ON DELETE SET NULL
ON DELETE RESTRICT
ON DELETE CASCADE
Automatically deletes or updates rows in the child table when the corresponding row in the parent table is deleted or updated.
What prevents the deletion or update of a parent row if it has related rows in the child table.
ON DELETE RESTRICT
Visualize how to create a joins or cross-reference table
CREATE TABLE table1 (
id1 INT PRIMARY KEY,
column1 VARCHAR(100)
);
CREATE TABLE table2 (
id2 INT PRIMARY KEY,
column2 VARCHAR(100)
);
CREATE TABLE relationship_table (
id1 INT,
id2 INT,
relationship_date DATE,
PRIMARY KEY (id1, id2),
FOREIGN KEY (id1) REFERENCES table1(id1),
FOREIGN KEY (id2) REFERENCES table2(id2)
);
SUBSTRING
SUBSTRING(string, start_position, length)
col: The column or string from which the substring is being extracted.
1 (start_position): Extraction starts at the first character of the string.
1 (length): Only 1 character is extracted.
DATEPART
DATEPART(part, date_column)
part: Specifies which part of the date you want to extract (e.g., year, month, day, hour, etc.).
date: A column, variable, or literal containing a date or datetime value.
What is the basic rule for primary and foreign keys in M:N relationships
Put the primary key of the “one” side (the parent entity) and the foreign key on the “many” side (the dependent entity),
For primary and foreign keys in 1:1 relationships when one side is mandatory and the other side is optional
Place the PK of the entity on the mandatory side and on the optional side as an FK
For primary and foreign keys in 1:1 relationships when both sides are optional
Select the FK that causes the fewest nulls
Index
CREATE INDEX idx_column_name ON table_name(column_name); – Index to optimize queries on column_name
Count() with Distinct
SELECT COUNT(DISTINCT value) AS total_value
FROM table;
Visualize a query that filters films released in 1994 or 1995 and certified PG or R
SELECT title
From films
WHERE (release_year = 1994 OR release_year = 1995)
AND (certification = ‘PG’ or certification = ‘R’);
Visualize a query that selects a coat where the color is yellow or the length is short
SELECT *
FROM coats
WHERE color = ‘yellow’ OR length = ‘short’
Visualize a query that selects a title from a film that was released after 1994 and before 2000
SELECT title
FROM films
WHERE release_year > 1994 AND release_year < 2000
Convert this SELECT statement to use a BETWEEN, AND
SELECT title
FROM films
WHERE year >= 1994
AND year <= 2000;
SELECT title
FROM films
WHERE year
BETWEEN 1994 AND 2000;
What steps do you want for cleaning string data?
Restrict capitalization in column names
Remove extra divider spaces in column names
make column names uniform
%
Matches 0 or more characters
_
Matches exactly 1 character
Can use multiple _ to represent more than 1 character
REGEXP_REPLACE()
REGEXP_REPLACE(column, ‘regrex’, replacement, ‘g’)
‘g’ = global
Visualize using the Like with % to find names in the people table that start with ‘B’
SELECT name
FROM people
WHERE name LIKE ‘B%’
Visualize selecting the names from the people table where the names have ‘r’ as the second letter
SELECT name
FROM people
WHERE name LIKE ‘_r%’
Visualize selecting the names from the people table where the names do not start with ‘A’
SELECT name
FROM people
WHERE name NOT LIKE ‘A%’
Select the title and release_year from film table of all films released in 1990 or 2000 that were longer than two hours.
SELECT title, release_year
FROM films
WHERE duration > 120
AND release_year IN (1990, 2000)
T or F: Count(), Min(), and Max() only take numerical data.
False. They take data of various types.
ROUND()
ROUND(number_to_round, decimal_places)
A. SELECT ROUND(avg(budget)) AS avg_budget
FROM films
B. SELECT ROUND(AVG(budget), -5) AS avg_budget
FROM films
T or F: SELECT (4/3) will produce a float
False. You will have to write (4.0/3.0)
MySQL order of execution
- FROM Clause: Determines the source table(s).
- WHERE Clause: Filters rows before any grouping.
- GROUP BY Clause: Groups rows.
- HAVING Clause: Filters groups based on aggregates.
- SELECT Clause: Calculates the final output
- ORDER BY Clause: Orders the results.
- LIMIT
Order BY
SELECT title
FROM FILMS
WHERE title IS NOT NULL
ORDER BY release_year ASC
Visualize finding the release_year and film_count of each year
SELECT release_year, COUNT(title) AS film_count
FROM films
GROUP BY release_year
Visualize how to count the unique number of films released in a language
SELECT release_year, COUNT(DISTINCT language) AS lang_diversity
FROM films
GROUP BY release_year
ORDER BY lang_diversity DESC
HAVING vs WHERE
WHERE filters individual records, having filters grouped
What is the order of execution of the below code:
SELECT certification, COUNT(title)
FROM films
Where certification IN (‘G’, ‘PG’)
GROUP BY certification
HAVING COUNT(title) > 500
ORDER BY title_count DESC
Limit 3
FROM
WHERE
GROUp By
HAVING
SELECT
ORDER BY
LIMIT
How do you import data into mySQL or Postgres?
- Verify table exists with same data structure or create table with column names of data want to import
- verify imported data location has permissions
- COPY my_table (col1,2,3,4)
FROM ‘filepath’
DELIMITER ‘,’
CSV HEADER;
What makes HAVING Different from Group by
Filters groups of data after GROUP BY has occurred.
Used with aggregate functions (SUM(), COUNT(), AVG(), etc.).
Cannot be used without GROUP BY (except when using aggregates over the whole dataset).
Visualize how to count the number of employees in departments in the employee table Having greater than 5 employees
SELECT department, COUNT() AS num_employees
FROM employees
GROUP BY department
HAVING COUNT() > 5;
Groups employees by department.
Counts the number of employees in each department.
Filters out departments that have 5 or fewer employees.
Visualize code that:
Filters individual employees with salary > 50000
Groups remaining employees by department.
Filters departments where the average salary is greater than 70,000.
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000 – Filters individual salaries
GROUP BY department
HAVING AVG(salary) > 70000; – Filters grouped results
Visualize how to return the first available phone number (work, home, or mobile) from the customer table:
SELECT name, COALESCE(work_phone, home_phone, mobile_phone, ‘No Phone’) AS contact_number
FROM customers;
What It Does:
Checks work_phone, then home_phone, then mobile_phone.
If all are NULL, it returns “No Phone”.
Visualize how to replace NULL salaries with 0 in the employees table.
SELECT name, COALESCE(salary, 0) AS salary
FROM employees;
FULL JOIN
Keeps all records from both tables even when there is no match.
Write a code that is the same as this
SELECT name, department
FROM employees
WHERE department NOT IN (‘HR’, ‘IT’);
SELECT name, department
FROM employees
WHERE department <> ‘HR’ AND department <> ‘IT’;
Write a code that is the same as this:
SELECT name, department
FROM employees
WHERE department = ‘HR’ OR department = ‘IT’;
SELECT name, department
FROM employees
WHERE department IN (‘HR’, ‘IT’);
Visualize a query where you could achieve the following:
Use an Inner query to SELECT dept_name FROM departments
Retrieves all department names from the departments table.
Runs an Outer Query Filters Employees
WHERE department IN Keeps only employees whose department matches a valid department from the inner query.
SELECT name, department
FROM employees
WHERE department IN (SELECT dept_name FROM departments);
Complete the code to perform an INNER JOIN of countries AS c with languages AS l using the code field to obtain the languages currently spoken in the two countries.
SELECT c.name AS country, l.name AS language
– Inner join countries as c with languages as l on code
FROM countries AS c
INNER JOIN languages AS l
USING(code)
WHERE c.code IN (‘PAK’,’IND’)
AND l.code in (‘PAK’,’IND’);
Visualize a self-join that:
Retrieves the country_code (to identify the country).
Retrieves p1.size (the population in 2010, labeled as size2010).
Retrieves p2.size (the population in 2015, labeled as size2015).
SELECT
p1.country_code,
p1.size AS size2010,
p2.size AS size2015
FROM populations AS p1
INNER JOIN populations AS p2
ON p1.country_code = p2.country_code
WHERE p1.year = 2010 AND p2.year = 2015;
SELF JOIN
No implicit JOIN statement
SELECT t1.col, t2.col
FROM table AS t1
INNER JOIN table AS t2
ON t1.primary_key = t2.primary_key
WHERE condition
