mySQL Flashcards
Introduction
1
Q
Get all columns from a table in MySQL
A
SELECT *
FROM table
2
Q
Get a column from a table in MySQL
A
SELECT column
FROM table
3
Q
Get multiple columns from table in MySQL
A
SELECT col1, col2
FROM table
4
Q
in MySQL Use alias names
A
AS
SELECT col1, col2 AS col2_new
FROM table
5
Q
Arrange the rows in ascending order of values in by a specific column in MySQL
A
ORDER BY
SELECT col1, col2
FROM table
ORDER BY col2
6
Q
Arrange the rows in descending order of values in column (in MySQL)
A
ORDER BY col DESC
SELECT col1, col2
FROM table
ORDER BY col2 DESC
7
Q
Limit the number of rows returned (in MySQL)
A
LIMIT
SELECT *
FROM table
LIMIT 2
8
Q
Get unique values, filtering out duplicate rows, returning only unique rows. (in MySQL)
A
DISTINCT
SELECT DISTINCT column
FROM table
9
Q
Get rows where a number is greater than a value (in MySQL)
A
WHERE col1 > n
SELECT col1
FROM table
WHERE col1 > value
10
Q
Get rows where a number is greater than or equal to a value (in MySQL)
A
WHERE col >=n
SELECT col1
FROM table
WHERE col1 >= value
11
Q
Visualize Concatenating Columns with a New Name (in MySQL)
A
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name
FROM employees;
12
Q
Visualize Using in Aggregations (in MySQL)
A
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
13
Q
Get rows where a number is equal to a value (in MySQL)
A
SELECT col1
FROM table
WHERE col1 = value
14
Q
Get rows where a number is not equal ( WHERE col != n) (in MySQL)
A
SELECT col1
FROM table
WHERE col1 <> value
15
Q
Get rows where a number is between two values (inclusive) (in MySQL)
A
SELECT col1
FROM table
WHERE col1 BETWEEN value1 AND value2
16
Q
Get rows where text is equal to a value (in MySQL)
A
SELECT col1, col2
FROM table
WHERE col1 = ‘string’
17
Q
Get rows where text is one of several values (in MySQL)
A
SELECT col1, col2
FROM table
WHERE x IN (‘string’, ‘string2’)
18
Q
(in MySQL) Get rows where text contains specific letters with WHERE col LIKE ‘%abc%’ (% represents any characters)
A
SELECT col1
FROM table
WHERE col1 LIKE ‘%abc%’
19
Q
Get the rows where one condition and another condition holds with WHERE condn1 AND condn2 (in MySQL)
A
SELECT col1, col2
FROM table
WHERE col1 < value
AND col2 > value2
20
Q
Get the rows where one condition or another condition holds with WHERE condn1 OR condn2 (in MySQL)
A
SELECT col1
FROM table
WHERE col1 < value
OR col2 > value
21
Q
Get rows where values are missing with WHERE col IS NULL (in MySQL)
A
SELECT col1, col2
FROM table
WHERE col1 IS NULL
22
Q
Get rows where values are not missing with WHERE col IS NOT NULL (in MySQL)
A
SELECT col1, col2
FROM table
WHERE col1 IS NOT NULL
23
Q
Get the total number of rows (in MySQL)
A
SELECT COUNT(*)
FROM table
24
Q
Get the total value of a column (in MySQL)
A
SELECT SUM(col)
FROM table
25
SQL: Get the mean value of a column (in MySQL)
SELECT AVG(col)
FROM table
26
Get the maximum value of a column (in MySQL)
SELECT MAX(col)
FROM table
27
Get summaries grouped by values (in MySQL)
SELECT col1, COUNT(*)
FROM table
GROUP BY col1
28
Get summaries grouped by values, in order of summaries (in MySQL)
SELECT col1, SUM(col2) AS totals
FROM table
GROUP BY col1
ORDER BY totals DESC
29
(in MySQL) 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
30
Filter before and after grouping with WHERE condn_before GROUP BY col HAVING condn_after (in MySQL)
SELECT col1, SUM(col2) AS totals
FROM table
WHERE col3 = 'string'
GROUP BY col1
ORDER BY totals DESC
HAVING totals > value
31
(in MySQL) Create a table
CREATE TABLE table_name (
column_name data_type constraints
);
32
(in MySQL) 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);
33
What are the data types? (in MySQL)
Numeric, string, date/time
34
(in MySQL) Numeric datatype
INT
FLOAT
DOUBLE
BIT
35
String data (in MySQL)
CHAR
VARCHAR
TEXT
JSON
36
(in MySQL) Date/Time
DATE
TIME
YEAR
DATETIME
37
Constraints (in MySQL)
Rules enforced on data to ensure accuracy, consistency, and integrity.
Primary key, Foreign key, Unique, Not Null, Default, Null, Check, Auto_Increment, Index
38
(in MySQL) 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)
);
39
(in MySQL) 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)
);
40
(in MySQL) 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
);
41
(in MySQL) NOT NULL
Prevents a column from having NULL values.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL
);
42
(in MySQL) 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'
);
43
(in MySQL) 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)
);
44
(in MySQL) 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)
);
45
(in MySQL) 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);
46
(in MySQL) 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
);
47
(in MySQL) 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);
48
Delete data (in MySQL) or row from table
DELETE FROM table
WHERE column = 'string';
49
Update data (in MySQL)
UPDATE table
SET column = TRUE
WHERE column2 = 'string';
50
WHERE clause (in MySQL)
A piece of conditional logic that limits the amount of data returned. Usually found after FROM clause
51
In and Not IN (in MySQL)
SELECT column
FROM table
WHERE value IN (val1,val2,val3)
52
(in MySQL) Visualize how to create a table
CREATE TABLE {table_name} (
{column1}{datatype1}{constraint1}
{column2}{datatype2}{constraint2}
{column3}{datatype3}{constraint3}
)
53
What are column constraints (in MySQL)
Keywords that give special properties to columns:
NOT NULL, NULL, UNIQUE, PRIMARY KEY, CHECK (col < num)
54
(in MySQL) 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.
55
(in MySQL) 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');
56
(in MySQL) UPDATE
UPDATE table_name
SET column1 = val1, col2 = val2
WHERE condition;
omitting where updates all rows in table
57
(in MySQL) 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)
58
(in MySQL) Visualize how to return NULL values with default values while querying a column
SELECT
COALESCE(phone_number, 'No Number') AS contact_info
FROM customers;
59
(in MySQL) Visualize how to return the first non-NULL value from several columns
SELECT
COALESCE(firstname, middlename,lastname, 'No names available') AS students
FROM university
60
(in MySQL) 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.
61
Normalization (in MySQL)
The process of organizing data into separate tables to minimize redundancy
62
(in MySQL) 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
63
Explain this JOIN code: (in MySQL)
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
64
(in MySQL) 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
65
INNER JOIN (in MySQL)
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;
66
OUTER JOIN (in MySQL)
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;
67
(in MySQL) 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;
68
(in MySQL) 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;
69
(in MySQL) 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;
70
Connect to a database (in MySQL)
USE database
71
(in MySQL) 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)
72
Create a database (in MySQL)
CREATE DATABASE my_database;
73
(in MySQL) Verify database
SHOW database;
74
Create A View (in MySQL)
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;
75
(in MySQL) 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;
76
ALTER VIEW (in MySQL)
ALTER VIEW view_name AS
SELECT new_columns
FROM table_name;
77
(in MySQL) DROP View
To delete a view, use the DROP VIEW statement.
DROP VIEW view_name;
78
(in MySQL) 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;
79
REFERENCES (in MySQL)
Creates the foreign key
FOREIGN KEY (fk_col_name)
REFERENCES target_table_name (pk_col_name);
80
The ON DELETE clause to maintain referential integrity (in MySQL)
ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
);
81
(in MySQL) ON DELETE CASCADE
Automatically deletes or updates rows in the child table when the corresponding row in the parent table is deleted or updated.
82
What prevents the deletion or update of a parent row if it has related rows in the child table. (in MySQL)
ON DELETE RESTRICT
83
(in MySQL) 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)
);
84
(in MySQL) 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.
85
(in MySQL) 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.
86
What (in MySQL) 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),
87
(in MySQL) 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
88
(in MySQL) For primary and foreign keys in 1:1 relationships when both sides are optional
Select the FK that causes the fewest nulls
89
Index (in MySQL)
CREATE INDEX idx_column_name ON table_name(column_name); -- Index to optimize queries on column_name
90
(in MySQL) Count() with Distinct
SELECT COUNT(DISTINCT value) AS total_value
FROM table;
91
(in MySQL) 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');
92
(in MySQL) 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'
93
(in MySQL) 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
94
(in MySQL) 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;
95
(in MySQL) 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
96
(in MySQL) %
Matches 0 or more characters
97
(in MySQL) _
Matches exactly 1 character
Can use multiple _ to represent more than 1 character
98
(in MySQL) REGEXP_REPLACE()
REGEXP_REPLACE(column, 'regrex', replacement, 'g')
'g' = global
99
(in MySQL) Visualize using the Like with % to find names in the people table that start with 'B'
SELECT name
FROM people
WHERE name LIKE 'B%'
100
(in MySQL) 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%'
101
(in MySQL) 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%'
102
(in MySQL) 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)
103
(in MySQL) T or F: Count(), Min(), and Max() only take numerical data.
False. They take data of various types.
104
(in MySQL) 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
105
(in MySQL) T or F: SELECT (4/3) will produce a float
False. You will have to write (4.0/3.0)
106
MySQL order of execution
1. FROM Clause: Determines the source table(s).
2. WHERE Clause: Filters rows before any grouping.
3. GROUP BY Clause: Groups rows.
4. HAVING Clause: Filters groups based on aggregates.
5. SELECT Clause: Calculates the final output
6. ORDER BY Clause: Orders the results.
7. LIMIT
107
(in MySQL) Order BY
SELECT title
FROM FILMS
WHERE title IS NOT NULL
ORDER BY release_year ASC
108
(in MySQL) 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
109
(in MySQL) 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
110
(in MySQL) HAVING vs WHERE
WHERE filters individual records, having filters grouped
111
(in MySQL) 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
112
(in MySQL) How do you import data into mySQL or Postgres?
1. Verify table exists with same data structure or create table with column names of data want to import
2. verify imported data location has permissions
3.
COPY my_table (col1,2,3,4)
FROM 'filepath'
DELIMITER ','
CSV HEADER;
113
(in MySQL) 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).
114
(in MySQL) 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.
115
(in MySQL) 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
116
(in MySQL) 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".
117
(in MySQL) Visualize how to replace NULL salaries with 0 in the employees table.
SELECT name, COALESCE(salary, 0) AS salary
FROM employees;
118
(in MySQL) FULL JOIN
Keeps all records from both tables even when there is no match.
119
(in MySQL) 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';
120
(in MySQL) 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');
121
(in MySQL) 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);
122
(in MySQL) 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');
123
(in MySQL) 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;
124
(in MySQL) 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
