mySQL

Question 1

Get all columns from a table

Answer 1

SELECT *
FROM table

Question 2

Get a column from a table

Answer 2

SELECT column
FROM table

Question 3

Get multiple columns from table

Answer 3

SELECT col1, col2
FROM table

Question 4

Use alias names

Answer 4

AS

SELECT col1, col2 AS col2_new
FROM table

Question 5

Arrange the rows in asscending order of values in a column

Answer 5

ORDER BY

SELECT col1, col2
FROM table
ORDER BY col2

Question 6

Arrange the rows in descending order of values in column

Answer 6

ORDER BY col DESC

SELECT col1, col2
FROM table
ORDER BY col2 DESC

Question 7

Limit the number of rows returned

Answer 7

LIMIT

SELECT *
FROM table
LIMIT 2

Question 8

Get unique values, filtering out duplicate rows, returning only unique rows.

Answer 8

DISTINCT

SELECT DISTINCT column
FROM table

Question 9

Get rows where a number is greater than a value

Answer 9

WHERE col1 > n

SELECT col1
FROM table
WHERE col1 > value

Question 10

Get rows where a number is greater than or equal to a value

Answer 10

WHERE col >=n
SELECT col1
FROM table
WHERE col1 >= value

Question 11

Visualize Concatenating Columns with a New Name

Answer 11

SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name
FROM employees;

Question 12

Visualize Using in Aggregations

Answer 12

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Question 13

Get rows where a number is equal to a value

Answer 13

SELECT col1
FROM table
WHERE col1 = value

Question 14

Get rows where a number is not equal ( WHERE col != n)

Answer 14

SELECT col1
FROM table
WHERE col1 <> value

Question 15

Get rows where a number is between two values (inclusive)

Answer 15

SELECT col1
FROM table
WHERE col1 BETWEEN value1 AND value2

Question 16

Get rows where text is equal to a value

Answer 16

SELECT col1, col2
FROM table
WHERE x = ‘string’

Question 17

Get rows where text is one of several values

Answer 17

SELECT col1, col2
FROM table
WHERE x IN (‘string’, ‘string2’)

Question 18

Get rows where text contains specific letters with WHERE col LIKE ‘%abc%’ (% represents any characters)

Answer 18

SELECT col1
FROM table
WHERE col1 LIKE ‘%abc%’

Question 19

Get the rows where one condition and another condition holds with WHERE condn1 AND condn2

Answer 19

SELECT col1, col2
FROM table
WHERE col1 < value
AND col2 > value2

Question 20

Get the rows where one condition or another condition holds with WHERE condn1 OR condn2

Answer 20

SELECT col1
FROM table
WHERE col1 < value
OR col2 > value

Question 21

Get rows where values are missing with WHERE col IS NULL

Answer 21

SELECT col1, col2
FROM table
WHERE col1 IS NULL

Question 22

Get rows where values are not missing with WHERE col IS NOT NULL

Answer 22

SELECT col1, col2
FROM table
WHERE col1 IS NOT NULL

Question 23

Get the total number of rows

Answer 23

SELECT COUNT(*)
FROM table

Question 24

Get the total value of a column

Answer 24

SELECT SUM(col)
FROM table

Question 25

Get the mean value of a column

Answer 25

SELECT AVG(col)
FROM table

Question 26

Get the maximum value of a column

Answer 26

SELECT MAX(col)
FROM table

Question 27

Get summaries grouped by values

Answer 27

SELECT col1, COUNT(*)
FROM table
GROUP BY col1

Question 28

Get summaries grouped by values, in order of summaries

Answer 28

SELECT col1, SUM(col2) AS totals
FROM table
GROUP BY col1
ORDER BY totals DESC

Question 29

Get rows where values in a group meet a criterion with GROUP BY col HAVING condn

Answer 29

SELECT col1, SUM(col2) AS totals
FROM table
GROUP BY col1
ORDER BY totals DESC
HAVING totals > value

Question 30

Filter before and after grouping with WHERE condn_before GROUP BY col HAVING condn_after

Answer 30

SELECT col1, SUM(col2) AS totals
FROM table
WHERE col3 = ‘string’
GROUP BY col1
ORDER BY totals DESC
HAVING totals > value

Question 31

Create a table

Answer 31

CREATE TABLE table_name (
column_name data_type constraints
);

Question 32

Designate a primary key

Answer 32

A. column_name data_type PRIMARY KEY
B. PRIMARY KEY (column_name)

Question 33

What are the data types?

Answer 33

Numeric, string, date/time

Question 34

Numeric datatype

Answer 34

INT
DECIMAL
FLOAT
DOUBLE
BIT

Question 35

String data

Answer 35

CHAR
VARCHAR
TEXT
JSON

Question 36

Date/Time

Answer 36

DATE
TIME
YEAR
DATETIME

Question 37

Constraints

Answer 37

Rules enforced on data to ensure accuracy, consistency, and integrity.
Primary key, Foreign key, Unique, Not Null, Default, Null, Check, Auto_Increment, Index

Question 38

Primary Key

Answer 38

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)
);

Question 39

FOREIGN KEY

Answer 39

Establishes a relationship between two tables
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Question 40

UNIQUE

Answer 40

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
);

Question 41

NOT NULL

Answer 41

Prevents a column from having NULL values.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL
);

Question 42

Default

Answer 42

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’
);

Question 43

CHECK (Available in MySQL 8.0+)

Answer 43

Ensures that all values in a column meet a specified condition.
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);

Question 44

AUTO_INCREMENT

Answer 44

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)
);

Question 45

INDEX

Answer 45

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_email ON users (email);

Question 46

Visualize combining multiple constraints

Answer 46

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
);

Question 47

Visualize insulting multiple rows

Answer 47

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);

Question 48

Delet data or row from table

Answer 48

DELET FROM table
WHERE column = ‘string’;

Question 49

Update data

Answer 49

UPDATE table
SET column = TRUE
WHERE column2 = ‘string’;

Question 50

WHERE clase

Answer 50

A piece of conditional logic that limits the amount of data returned. Usually found after FROM clause

Question 51

In and Not IN

Answer 51

SELECT column
FROM table
WHERE value IN (val1,val2,val3)

Question 52

Visualize how to create a table

Answer 52

CREATE TABLE {table_name} (
{column1}{datatype1}{constraint1}
{column2}{datatype2}{constraint2}
{column3}{datatype3}{constraint3}
)

Question 53

What are column constraints

Answer 53

Keywords that give special properties to columns:
NOT NULL, NULL, UNIQUE, PRIMARY KEY