MySQL Flashcards
(305 cards)
1
Q
What SQL command is used to create a database?
A
CREATE DATABASE.
2
Q
How should SQL statements be concluded?
A
With a semicolon.
3
Q
To create an Index you would use:
A
4
Q
To create an composite Index you would use:
A
5
Q
To Delete an Index you would use:
A
6
Q
What happens when you refresh the Schemas tab after creating a database?
A
The newly created database will appear.
7
Q
What command is used to switch to a specific database?
A
USE [database_name];
8
Q
What command is used to drop a database?
A
DROP DATABASE [database_name];
9
Q
What SQL command is used to set a database to read-only mode?
A
ALTER DATABASE [database_name] READ ONLY = 1;
10
Q
What happens when a database is set to read-only mode?
A
Modifications aren’t allowed, but data can still be accessed.
11
Q
What SQL command is used to remove the read-only status of a database?
A
ALTER DATABASE [database_name] READ ONLY = 0;
12
Q
What is the SQL command to create a table?
A
CREATE TABLE [table_name];
13
Q
What are the components of a table in a relational database?
A
Rows and columns.
14
Q
How is MySQL designed to be flexible and user-friendly?
A
- In MySQL, keywords like
SELECT,FROM,WHERE, and others are not case-sensitive. - This means you can write them in uppercase, lowercase, or a mix of both, and MySQL will interpret them the same way.
15
Q
What should you do if you want to recreate a database after dropping it?
A
CREATE DATABASE [database_name];
16
Q
What are the two client and utility program types included in MySQL.
A
- Command-line programs (e.g., mysqldump, mysqladmin)
- Graphical programs (e.g., MySQL Workbench)
These programs assist in database management and operations.
17
Q
What built-in support does MySQL Server provide for table management?
A
SQL statements to check, optimize, and repair tables
These statements can be accessed through the mysqlcheck client.
18
Q
What command can be used to obtain online assistance for MySQL programs?
A
--help or -h
This option provides help information for using MySQL programs.
19
Q
What SQL statement is used to add a new column to an existing table?
Replace table_name with the name of your table, column_name with the desired name for the new column, data_type with the appropriate data type (e.g., VARCHAR, INT, DATE), and optionally add constraints.
A
ALTER TABLE table_name ADD COLUMN column_name data_type constraints;
20
Q
Provide an example of adding a new column to a table.
This example adds an ‘email’ column of type VARCHAR with a maximum length of 255 characters to the ‘Customers’ table.
A
ALTER TABLE Customers ADD COLUMN email VARCHAR(255);
This example adds an ‘email’ column of type VARCHAR with a maximum length of 255 characters to the ‘Customers’ table.
21
Q
What SQL statements is used to modify an existing column’s definition?
The choice between MODIFY COLUMN and ALTER COLUMN depends on the specific database system.
A
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type new_constraints;
or
ALTER TABLE table_name ALTER COLUMN column_name new_data_type new_constraints;
22
Q
Provide an example of modifying an existing column.
This example modifies the ‘price’ column in the ‘Products’ table to be of type DECIMAL with a precision of 10 and scale of 2.
A
ALTER TABLE Products MODIFY COLUMN price DECIMAL(10, 2);
This example modifies the ‘price’ column in the ‘Products’ table to be of type DECIMAL with a precision of 10 and scale of 2. 📖
23
Q
What SQL statement is used to delete a column from a table?
A
ALTER TABLE table_name DROP COLUMN column_name;
This statement removes the specified column from the table.
24
Q
Provide an example of deleting a column from a table.
This example deletes the ‘department’ column from the ‘Employees’ table.
A
ALTER TABLE Employees DROP COLUMN department;
This example deletes the ‘department’ column from the ‘Employees’ table.
25
What SQL statement is used to add a new _row_ to a table?
## Footnote
This statement is used to insert a new record into the specified table.
26
Provide an example of adding a new row to a table.
## Footnote
This example inserts a new product with the name 'Laptop', price of 1200, and category 'Electronics' into the 'Products' table.
INSERT INTO Products (name, price, category) VALUES ('Laptop', 1200, 'Electronics');
## Footnote
This example inserts a new product with the name 'Laptop', price of 1200, and category 'Electronics' into the 'Products' table.
27
Why isn't the syntax for `ALTER TABLE` consistent across all database systems?
The exact syntax for `ALTER TABLE` might vary slightly depending on the specific database system being used
## Footnote
(e.g., MySQL, PostgreSQL, SQL Server).
28
What should you do before making significant changes to a table?
Back up your data
## Footnote
It is recommended to back up your data before modifying existing tables to prevent data loss.
29
It is similar to asking someone to 'choose' or 'identify'.
## Footnote
Example: 'Pick your favorite fruits!'
What does the SQL command `SELECT` do?
## Footnote
Translates to the SQL command `SELECT fruits FROM table_name;`
30
It specifies 'where' something comes from in a conversation.
## Footnote
Example: 'Where did you get this?' or 'What place did you buy this item?''
What is the purpose of the `FROM` clause in SQL?
## Footnote
Example: 'From which store did you buy this item?' corresponds to `FROM store_table`.
31
It asks *for a **condition** in which you are using* to filter or join.
## Footnote
Example: 'Where can I find the red apples?'
What does the `WHERE` clause indicate in SQL?
## Footnote
Example: 'Where can I find the red apples?' translates to `WHERE color = 'red'`.
32
It fetches records that have matching values in both tables.
## Footnote
Comparable to a group coming together to share information.
How does `INNER JOIN` function in SQL?
33
It organizes data.
## Footnote
Example: 'Can you sort these books by title?'
What is the purpose of the `ORDER BY` clause in SQL?
## Footnote
Example: 'Can you sort these books by title?' translates to `ORDER BY title`.
34
It categorizes items.
## Footnote
[📖](https://app.milanote.com/1Tomje121e3t1E?p=N4oXWlErVzz)
What does `GROUP BY` do in SQL?
## Footnote
Example: 'Group the apples by their types' corresponds to `GROUP BY apple_type`.
35
It adds something into a list (*column*).
## Footnote
Example: 'Can you add this to my shopping list?'
What does the `INSERT INTO` command do in SQL?
## Footnote
Example: 'Add this item to my shopping list' translates to `INSERT INTO shopping_list VALUES (...)`.
36
It changes existing record data.
## Footnote
[📖😚](https://app.milanote.com/1UeQdo1ina668q?p=N4oXWlErVzz)
What is the function of the `UPDATE` command in SQL?
37
It removes data from a table.
## Footnote
Example: 'Remove this item from my cart'
What does the `DELETE` command do in SQL?
## Footnote
Example: 'Remove this item from my cart' translates to `DELETE FROM cart WHERE item_id = ...`[🙅🏽♂️Alter or Modify](https://g.co/gemini/share/a2ba34c28ac9)
38
Only those items that are unique
## Footnote
Example: `SELECT` a unique color `FROM` apples basket;
What does the SQL keyword `DISTINCT` represent?
## Footnote
Example: `SELECT DISTINCT color FROM apples_basket;`
39
1. Filter results *after [aggregation](https://app.milanote.com/1Tomje121e3t1E?p=N4oXWlErVzz)*
2. It's used after the `GROUP BY` clause and before the `ORDER BY` clause (if used) .
## Footnote
You have a Customers table and want to list countries with more than 5 customers:
What is the purpose of the `HAVING` clause in SQL?
## Footnote
Example: `HAVING COUNT(CustomerID) > 5;`
40
Find values resembling a pattern
What does the `LIKE` operator do in SQL?
## Footnote
Example: `WHERE name LIKE 'John%';`
41
Select values within a specified range
## Footnote
Example: `WHERE price $ 10 AND 20`
What does the `BETWEEN` operator specify in SQL?
## Footnote
Example: `WHERE price BETWEEN 10 AND 20`
42
Restrict the number of results returned
## Footnote
Example: Return 5 results
What does the `LIMIT` clause do in SQL?
## Footnote
Example: `LIMIT 5`
43
Combine results from two or more queries
## Footnote
Example: `SELECT item FROM list1 $ SELECT item FROM list2`
What does `UNION` do in SQL?
## Footnote
Example: `SELECT item FROM list1 UNION SELECT item FROM list2` [📖 DISTINCT?](https://g.co/gemini/share/f31347698eeb)
44
Include all items from one side regardless of matches
What is the function of an `OUTER JOIN` in SQL?
## Footnote
Example: `LEFT OUTER JOIN` or `RIGHT OUTER JOIN`
45
Join tables using: INNER, LEFT, RIGHT, or FULL OUTER JOIN along with an ON or WHERE clause that specifies equality between matching columns.
## Footnote
Example: `SELECT * FROM students INNER JOIN grades ON students.id = grades.student_id`
What is an `EQUIJOIN`?
## Footnote
Example:`table1.column_name = table2.column_name`. [📖](https://g.co/gemini/share/b4705830ad4a)
46
Relate rows within the same table
## Footnote
Example: `SELECT a.name, b.name FROM employees a, employees b WHERE a.supervisor_id = b.supervisor_id`
What does a `SELF JOIN` achieve in SQL?
47
Combine all possible pairs of rows from two tables
## Footnote
Example: `SELECT shirts.color, pants.color FROM shirts CROSS JOIN pants`
What is the purpose of a `CROSS JOIN`?
48
Evaluate different conditions and return values
## Footnote
Example: `$ WHEN weather = 'rainy' THEN 'bring umbrella' ELSE 'wear jacket' END`
What does the `CASE` statement do in SQL?
## Footnote
[📖](https://g.co/gemini/share/cebbac921100)
49
A query nested inside another query
## Footnote
Example: `SELECT name FROM customers WHERE id = (SELECT customer_id FROM orders GROUP BY customer_id ORDER BY COUNT(*) DESC LIMIT 1)`
What is a SUBQUERY?
50
Remove all records from a table
## Footnote
Example: `$ TABLE shopping_cart`
What does the `TRUNCATE` command do?
## Footnote
Example: `TRUNCATE TABLE shopping_cart`
51
Create a quick reference guide for faster searches
## Footnote
Example: `CREATE INDEX idx_favorite_recipes ON recipes (favorite)`
What is the purpose of an `INDEX` in SQL?
## Footnote
Example: `CREATE INDEX idx_favorite_recipes ON recipes (favorite)`
52
A reference that links rows in different tables
## Footnote
Example: Links information to a student
What is a `FOREIGN KEY`?
53
The main identifier for records in a table
## Footnote
Example: Uniquely identifies each user by email
What is a `PRIMARY KEY`?
54
Undo the last transaction
## Footnote
Example: Reverse changes if a mistake is made
What does `ROLLBACK` do in SQL?
55
Confirm and save changes made
## Footnote
Example: Save all changes made to the profile
What does `COMMIT` signify in SQL?
56
Remove a table or database entirely
## Footnote
Example: `DROP TABLE old_contacts`
What does `DROP` do in SQL?
57
To modify an existing database object
## Footnote
Example: Adding the column 'phone_number' to the 'contacts' but with 15 total characters.
What is the function of the `ALTER` command?
## Footnote
Example: `ALTER TABLE contacts ADD COLUMN phone_number VARCHAR(15);`
58
Change the data type of a value
## Footnote
Example: `$(number AS VARCHAR)`
What does `CAST` do in SQL?
## Footnote
Example: `CAST(number AS VARCHAR)`
59
What is a database system instance?
A *single executing copy* of a database system.
60
What is the purpose of the `CREATE DATABASE` statement?
Used to create a new database.
61
Deletes a specified database *along with* its tables.
What does the `DROP DATABASE` statement do?
62
What is the `SHOW DATABASES` command used for?
Lists all databases in the system.
63
What information does the `SHOW TABLES` command provide?
Lists tables in the selected database.
64
What does the `SHOW COLUMNS` command do?
Shows the columns in a specified table.
65
What is the purpose of the `SHOW CREATE TABLE` command?
Displays the SQL command used to create a particular table.
66
What is the function of the `USE` statement?
_Required_ to select the active database *before executing* other `SHOW` commands.
67
# Explain the meaning and associated SQL concept with the following.
"List the CustomerName and their TotalOrderAmount for all customers, including those who have placed no orders."
* M: You need to display all customers from the `Customers` table, even if they haven’t placed any orders. If a customer hasn’t placed any orders, their `TotalOrderAmount` should still be shown as `0`
* C: Use a `LEFT JOIN` between the `Customers` table and a subquery (or derived table) that calculates the total order amount for each customer. A `LEFT JOIN` ensures that all rows from the `Customers` table are included, even if there’s no match in the `Orders` table.
68
# Explain the meaning and associated SQL concept with the following.
"Write an SQL query that uses a subquery in the FROM clause."
* M: The query will include a subquery in the `FROM` clause. This subquery will act as a temporary table that the outer query will use.
* C: Subqueries in the `FROM` clause are often called derived tables. They must be given an alias so the outer query can reference them.
69
# Explain the meaning and associated SQL concept with the following.
"This subquery should calculate the count of orders for each customer."
* M: The subquery will count the number of orders (`COUNT`) for each
* C: Use `GROUP BY` and `COUNT` in the subquery.
70
# Explain the meaning and associated SQL concept with the following.
"Then, the outer query should display the `CustomerName` and the total number of orders."
* M: The outer query will select `CustomerName` from the Customers table and the total number of orders (calculated by the subquery).
* C: The outer query will join the `Customers` table with the **derived table** (subquery) to link customer names to their order counts.
71
# Explain the meaning and associated SQL concept with the following.
"For customers with no orders, their TotalOrderAmount should be 0."
* M: If a customer hasn’t placed any orders, their `TotalOrderAmount` should be displayed as `0` instead of `NULL`.
* C: Use `COALESCE(SUM(Amount), 0)` or `IFNULL(SUM(Amount), 0)` to ensure that `NULL` values are replaced with `0`.
72
# Explain the meaning and associated SQL concept with the following.
"Only include customers whose TotalOrderAmount (including the 0s) is greater than 50."
* M: After calculating the `TotalOrderAmount` for each customer (including those with `0`), filter the results to only show customers whose `TotalOrderAmount` is greater than `50`
* C: Since `TotalOrderAmount` is calculated using an aggregate function (`SUM`), you can use a `HAVING` clause to filter the results *based on this aggregate* value.
73
What algorithm does the InnoDB buffer use?
Least Recently Used (LRU) algorithm
## Footnote
This algorithm discards the block that has not been accessed for the longest time.
74
# What are the componets of the task:
"to find the `CustomerName` of all customers *whose total* order amount is "
## Footnote
Write an SQL query to find the `CustomerName` of all customers whose total order amount is greater than the average total order amount of all customers.
* Use the `SUM()` function on the `Amount` column in the `Orders` table [📖](https://app.milanote.com/1Tomje121e3t1E?p=N4oXWlErVzz).
* Group the results by `CustomerID` to get the total
75
# What are the componets of the task:
Filter Customers Based on the Condition
## Footnote
Write an SQL query to find the `CustomerName` of all customers whose total order amount is greater than the average total order amount of all customers.
The task involves finding customers whose total spending on orders exceeds the average spending of all customers. To achieve this, the query first calculates the total order amount for each customer and the average of these totals. It then filters and returns the names of customers whose total order amount is greater than this average.
76
# Why use a subquery here?
Goal:
- List all customers by their `CustomerName`.
- For each customer, display the most recent `OrderDate` from their orders in the Orders table.
Requirements:
- Use a subquery in the `SELECT` clause to find the most recent `OrderDate` for each customer.
- The subquery must correlate with the outer query to ensure it retrieves the correct data for each customer.
The subquery allows us to dynamically calculate the most recent `OrderDate` for each customer without needing to join the entire `Orders` table or use complex grouping.
77
Explain how the outer and subquery works?
## Footnote
FYI: The subquery references `c.CustomerID` from the outer query to ensure it retrieves the correct `OrderDate` for each customer. [Scalar📖](https://app.milanote.com/1UfZWM1GD3zkf7?p=N4oXWlErVzz)
Outer Query:
- Selects `CustomerName` from the `Customers` table.
- Includes a subquery in the `SELECT` clause to retrieve the most recent `OrderDate` for each customer.
Subquery:
- Selects the `MAX(OrderDate`) from the `Orders` table for the current customer being processed by the outer query.
- The `WHERE` clause correlates the `CustomerID` in the `Orders` table with the `CustomerID` in the `Customers` table.
78
What does architecture describe in the context of MySQL?
**Components** of a computer system and the *relationships between* components
## Footnote
Other relational databases have similar components, but details and relationships vary.
79
What is the primary role of *utility programs* in MySQL?
Perform various *administrative functions* such as upgrading databases, backing up, and importing data
80
# Querying tables
Get all the columns from a table
```
SELECT *
FROM airbnb_listings;
```
81
# Querying tables
Return the city column from the table
```
SELECT city
FROM airbnb_listings;
```
82
# Querying tables
Get the city and year_listed columns from the table
```
SELECT city, year_listed
FROM airbnb_listings;
```
83
# Querying tables
Get the listing id ('year_listed'), city, ordered by the `number_of_rooms` in ascending order
```
SELECT city, year_listed
FROM airbnb_listings
ORDER BY number_of_rooms ASC;
```
84
# Querying tables
Get the listing id ('year_listed'), city, ordered by the `number_of_rooms` in descending order
```
SELECT city, year_listed
FROM airbnb_listings
ORDER BY number_of_rooms DESC;
```
85
# Querying tables
Get the first 5 rows from `airbnb_listings`
```
SELECT *
FROM airbnb_listings
LIMIT 5;
```
86
# Querying tables
Get a unique list of cities where there are listings
```
SELECT DISTINCT city
FROM airbnb_listings;
```
87
# Querying tables
Get all the listings where number_of_rooms is more or equal to 3
```
SELECT *
FROM airbnb_listings
WHERE number_of_rooms >= 3;
```
88
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is more than 3
## Footnote
assume using only `airbnb_listing` table
```
SELECT *
FROM airbnb_listings
WHERE number_of_rooms > 3;
```
89
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is exactly 3
```
SELECT *
FROM airbnb_listings
WHERE number_of_rooms = 3;
```
90
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is lower or equal to 3
```
SELECT *
FROM airbnb_listings
WHERE number_of_rooms <= 3;
```
91
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is lower than 3
```
SELECT *
FROM airbnb_listings
WHERE number_of_rooms < 3;
```
92
# Filtering on numeric columns
Get all the listings with 3 to 6 rooms ('number_of_rooms')
```
SELECT * FROM airbnb_listings
WHERE number_of_rooms BETWEEN 3 AND 6;
```
93
# Filtering on text columns
Get all the listings that are based in 'Paris'
```
SELECT *
FROM airbnb_listings
WHERE city = 'Paris';
```
94
# Filtering on text columns
Get the listings based in the ('country') 'USA' and in 'France'
```
SELECT *
FROM airbnb_listings
WHERE country IN ('USA', 'France');
```
95
# Filtering on text columns
Get all listings where city starts with "j" and where it does not end with "t"
```
SELECT *
FROM airbnb_listings
WHERE city LIKE 'j%' AND city NOT LIKE '%t';
```
96
# Filtering on multiple columns
Get all the listings in "Paris" where number_of_rooms is bigger than 3
```
SELECT *
FROM airbnb_listings
WHERE city = 'Paris' AND number_of_rooms > 3;
```
97
# Filtering on multiple columns
Get all the listings in "Paris" OR the ones that were listed('year_listed') after 2012
```
SELECT *
FROM airbnb_listings
WHERE city = 'Paris' OR year_listed > 2012;
```
98
# Filtering on missing data
Get all the listings where `number_of_rooms` is missing
```
SELECT *
FROM airbnb_listings
WHERE number_of_rooms IS NULL;
```
99
# Filtering on missing data
Get all the listings where `number_of_rooms` is not missing
```
SELECT *
FROM airbnb_listings
WHERE number_of_rooms IS NOT NULL;
```
100
# Simple aggregations
Get the total number of rooms ('number_of_rooms') available across all listings
```
SELECT SUM(number_of_rooms)
FROM airbnb_listings;
```
## Footnote
[🤯](https://g.co/gemini/share/77a9a10ee012)
101
# Simple aggregations
Get the average number of rooms per listing across all listings
```
SELECT AVG(number_of_rooms)
FROM airbnb_listings;
```
102
# Simple aggregations
Get the listing with the highest number of rooms across all listings
```
SELECT MAX(number_of_rooms)
FROM airbnb_listings;
```
103
# Simple aggregations
Get the listing with the lowest number of rooms across all listings
```
SELECT MIN(number_of_rooms)
FROM airbnb_listings;
```
104
Get the total number of rooms for each country ('country')
```
SELECT country, SUM(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
105
Get the average number of rooms for each country
```
SELECT country, AVG(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
106
Get the listing with the maximum number of rooms for each country
```
SELECT country, MAX(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
107
Get the listing with the lowest amount of rooms per country('country')
```
SELECT country, MIN(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
108
# Grouping, filtering, and sorting
For each country, get the average number of rooms (`AS avg_rooms`) per listing, sorted by ascending order
## Footnote
[Group By 🤔](https://g.co/gemini/share/e173d83e33a3)
```
SELECT country, AVG(number_of_rooms) AS avg_rooms
FROM airbnb_listings
GROUP BY country
ORDER BY avg_rooms ASC;
```
## Footnote
[Not: Group By AVG.. ](https://g.co/gemini/share/ef53c04b12f3)
109
# Grouping, filtering, and sorting
For Japan and the USA, get the average number of rooms per listing in each country
```
SELECT country, AVG(number_of_rooms) FROM airbnb_listings
WHERE country IN ('USA', 'Japan')
GROUP BY country;
```
## Footnote
[📖](https://app.milanote.com/1TltKQ1AXG1Z48?p=N4oXWlErVzz)
110
# Grouping, filtering, and sorting
Get the number of listings per country
## Footnote
`AS number_of_listings`
```
SELECT country, COUNT(id) AS number_of_listings
FROM airbnb_listings
GROUP BY country;
```
## Footnote
[📖](https://g.co/gemini/share/e107e463ca1b)
111
# Grouping, filtering, and sorting
Get all the years(`year_listed`) where there were more than 100 listings(`id`) per year
```
SELECT year_listed
FROM airbnb_listings
GROUP BY year_listed
HAVING COUNT(id) > 100;
```
## Footnote
[🤔](https://g.co/gemini/share/6b09bb52b75c)
112
What SQL statement is used to remove rows from a table?
DELETE statement
## Footnote
The DELETE statement allows you to specify which rows to delete using a `WHERE` clause. [📖](https://g.co/gemini/share/9228c546c453)
113
What condition is used to delete a specific row from a table?
## Footnote
[Modify](https://app.milanote.com/1UeQdo1ina668q?p=N4oXWlErVzz)
`WHERE` clause
## Footnote
The WHERE clause specifies the condition for deleting rows, such as `ID = 3`.
114
What is the purpose of the ALTER TABLE statement in SQL?
To modify the structure of an existing table *which includes*:
- adding or removing columns
- changing column data types or constraints.
115
Which SQL statement is NOT used for modifying or deleting data *within the rows* of a table?
ALTER TABLE [📖](https://app.milanote.com/1UeQdo1ina668q?p=N4oXWlErVzz)
## Footnote
ALTER TABLE is specifically designed for structural changes to the table itself.
116
List three examples of actions that can be performed using `ALTER TABLE.`
* Adding a new column
* Changing the data type of a column
* _Dropping_ a column
117
When should you use the `DELETE` statement instead of ALTER TABLE?
When you need to change the data inside the table
## Footnote
The DELETE statement is appropriate for updating or deleting rows, while ALTER TABLE is for schema changes.
118
The `DELETE` statement allows you to specify which rows to delete using a _______.
`WHERE` clause
119
True or False: The ALTER TABLE statement can be used to delete a row from a table.
False
```
DELETE FROM Customers
WHERE id = 123;
```
## Footnote
[📖](https://app.milanote.com/1UeQdo1ina668q?p=N4oXWlErVzz)
120
How would you retrieve a list of unique cities from a table of customers?
The `DISTINCT` keyword is used to return unique values in a column:
```
SELECT DISTINCT city FROM customers;
```
## Footnote
`DISTINCT` filters duplicate values in the selected column(s).
121
What SQL statement would you use to delete the row with the ID value of 3 from the Movie table?
```
DELETE FROM Movie WHERE ID = 3
```
122
Explain how the `WHERE` clause is used in the various parts of a SQL statement (with subquery).
In the `WHERE` clause, it _filters_ the outer query based on a condition that *depends on the results* of the subquery. [📖](https://app.milanote.com/1UfZvF1GD3zkeW?p=N4oXWlErVzz)
123
Explain how the `FROM` clause is used in the various parts of a SQL statement (with subquery.
In the `FROM` clause, the subquery acts like a temporary table. [📖](https://app.milanote.com/1Ug0co1GD3zkfl?p=N4oXWlErVzz)
124
Explain how the `SELECT` clause is used in the various parts of a SQL statement. [📖](https://app.milanote.com/1UfZWM1GD3zkf7?p=N4oXWlErVzz)
## Footnote
When you use a subquery in the SELECT list, it's often called what?
In the `SELECT` Clause, it returns a single value for each row of the outer query (scalar subquery).
## Footnote
It's often called a **scalar subquery**
125
What is the `COALESCE` function in SQL?
It is used to return the first **non-NULL** expression from a list of expressions.
126
When is the `COALESCE` function commonly used?
To provide a default value when a column might contain NULL.
127
What is the general format of the `COALESCE` function?
```
COALESCE(expression1, expression2, expression3, ...)
```
128
Under what circumstances does an outer query need a `GROUP BY` clause?
If you have an aggregate functions such as `SUM(o.Amount)` directly in your outer `SELECT` list along *with a non-aggregate*.
129
How do you use the LIKE operator to find names that start with the letter 'J'?
The `LIKE` operator is used with a wildcard `%` to match patterns in a column. To find names that start with 'J', use:
```
SELECT *
FROM table
WHERE name LIKE 'J%';
```
## Footnote
`%` represents zero or more characters in the pattern. [📖](https://app.milanote.com/1TGPRV1iQVdWa0?p=N4oXWlErVzz)
130
Returns all rows from both tables where *the values in the specified columns* are equal
- What is an Equijoin
- Most INNER JOIN operations you'll see in practice are `EQUIJOIN`s because you typically want to match records *where specific values are identical*.
## Footnote
(e.g., students.id = grades.student_id).
131
What are the data types of the Integer Category
1. **TINYINT**: Typically 1 byte.
2. **SMALLINT**: Typically 2 bytes.
3. **MEDIUMINT**: Typically 3 bytes.
4. **INT**: Typically 4 bytes. Or Integer
5. **BIGINT**: Typically 8 bytes.
132
What are the data types of the Decimal Category
1. **FLOAT**
2. **DOUBLE**
3. **DECIMAL(M,D)**
133
What are the data types of the "times" Category
1. DATE
2. DATETIME
3. TIME
134
What are the data types of the Characters Category
1. TEXT
2. CHAR(N)
3. VARCHAR(N)
135
How would you write July 4th, 1776, at 1:45:22 PM in the Date and time format in SQL?
DATETIME('1776-07-04 13:45:22')
## Footnote
In SQL, DATETIME format is YYYY-MM-DD hh:mm:ss and covers a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
136
How would you write July 4th, 1776, in the Date format in SQL?
DATE('1776-07-04')
## Footnote
The DATE format in SQL is YYYY-MM-DD.
137
How do you find the total sales amount for each product?
## Footnote
`Sales` table
Use the `SUM()` function with `GROUP BY`:
```
SELECT product_id, SUM(sales_amount) FROM sales
GROUP BY product_id;
```
## Footnote
Use `SUM()` to aggregate numerical data and `GROUP BY` to organize them by a specific column.
138
How would you write 1:45:22 PM in the Time format in SQL?
TIME('13:45:22')
## Footnote
SQL stores TIME in hh:mm:ss format, using a 24-hour clock.
139
How would you write 123.45 using the DECIMAL format with precision 5 and scale 2 in SQL?
`DECIMAL(5,2)(123.45)`
## Footnote
The DECIMAL(M,D) format allows precise storage of decimal numbers. M is the total number of digits **precision**, and D is the number of digits after the decimal point **scale**.
140
How would you write 123.45 using the FLOAT format in SQL?
FLOAT(123.45)
## Footnote
FLOAT is used for approximate decimal numbers with range: -3.4E+38 to 3.4E+38.
141
How would you write 123.45 using the DOUBLE format in SQL?
DOUBLE(123.45)
## Footnote
DOUBLE provides double-precision for approximate decimal numbers with a wider range than FLOAT.
142
How would you store the grade 'A' using the CHAR format in SQL?
## Footnote
CHAR(N) is used for fixed-length strings, where N is the length of the string. [📖](https://app.milanote.com/1Tk4sP1HqCvA7i?p=N4oXWlErVzz)
143
How would you store an email 'example@example.com' using the VARCHAR format in SQL?
VARCHAR(50)('example@example.com')
## Footnote
VARCHAR(N) allows variable-length strings with a maximum length of N characters.
144
How would you store a long description using the TEXT format in SQL?
TEXT('This is a long description...')
## Footnote
TEXT can store variable-length strings with a maximum of 65,535 characters.
145
How would you store the number 120 using the TINYINT format in SQL?
1. TINYINT(120) SIGNED
2. TINYINT(120) UNSIGNED
## Footnote
TINYINT has a signed range of -128 to 127 and an unsigned range of 0 to 255.
146
How would you store the number 25,000 using the SMALLINT format in SQL?
1. SMALLINT(25000) SIGNED
2.
## Footnote
SMALLINT has a signed range of -32,768 to 32,767 and an unsigned range of 0 to 65,535.
147
How would you store the number 8,000,000 using the MEDIUMINT format in SQL?
MEDIUMINT(8000000)
## Footnote
MEDIUMINT has a signed range of -8,388,608 to 8,388,607.
148
How would you store the number 2,000,000,000 using the INTEGER format in SQL?
INTEGER(2000000000)
## Footnote
INTEGER has a signed range of -2,147,483,648 to 2,147,483,647.
149
How would you store the number 10,000,000,000 using the BIGINT format in SQL?
BIGINT(10000000000)
## Footnote
BIGINT has a signed range of -2^63 to 2^63-1.
150
How do you retrieve records of students who have taken lessons, showing their names and lesson dates?
Use `INNER JOIN` to match rows present in both tables:
```
SELECT students.first_name, students.last_name, lessons.date
FROM students
INNER JOIN lessons ON students.id = lessons.student_id;
```
## Footnote
`INNER JOIN` returns only the rows with matching keys from both tables.
151
How do you list all customers and their orders, including those with no orders?
## Footnote
Assume we're associating their names
Use `LEFT JOIN` to include all rows from the left table and matching rows from the right:
```
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
```
## Footnote
Rows from the left with no matches will have NULL in the right table columns.
152
How do you show all employee projects, including unassigned projects?
Use `RIGHT JOIN` to include all rows from the right table and matching rows from the left:
```
SELECT employees.name, projects.project_name
FROM employees
RIGHT JOIN projects ON employees.id = projects.employee_id;
```
## Footnote
Rows from the right with no matches will have NULL in columns from the left table.
153
How do you get *a complete list* of departments _and_ employees, including departments with no employees?
Use `FULL OUTER JOIN` to include all matching *and non-matching* rows from both tables:
```
SELECT departments.name, employees.name
FROM departments
FULL OUTER JOIN employees ON departments.id = employees.department_id;
```
## Footnote
This join returns all unmatched rows from both tables [🤔](https://g.co/gemini/share/7902e41f7918) with NULLs where applicable. [If I used aliases in my `ON` is that ok?](https://g.co/gemini/share/09431c31082f)
154
How do you count the number of orders for each customer?
## Footnote
`Orders` table
Use the `COUNT()` function with `GROUP BY` to aggregate results per customer:
```
SELECT customer_id, COUNT(order_id) FROM orders
GROUP BY customer_id;
```
## Footnote
`GROUP BY` is used to arrange identical data into groups, and `COUNT()` computes the number of rows per group.
155
How do you find employees who have the same manager?
Use `SELF JOIN` to join a table with itself:
```
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
SELF JOIN employees e2 ON e1.manager_id = e2.id;
```
## Footnote
`SELF JOIN` allows comparison of rows within the same table.
156
How do you create a combination of all departments with all employees?
Use `CROSS JOIN` to produce a Cartesian product:
```
SELECT departments.DepartmentName, employees.name
FROM departments
CROSS JOIN employees;
```
## Footnote
`CROSS JOIN` returns all possible combinations of rows from the two tables.
157
How do you perform a join based on all columns *with the same name* in both tables?
Use `NATURAL JOIN` to automatically join using all matching column names:
```
SELECT *
FROM table1
NATURAL JOIN table2;
```
## Footnote
`NATURAL JOIN` assumes common column names for matching. [📖](https://g.co/gemini/share/da30d8ee16a3)
158
How do you join three tables, such as orders, customers, and products, to show detailed order information?
Chain multiple joins to connect more tables: [📖INNER Join](https://g.co/gemini/share/b7af8df879c3)
```
SELECT customers.name, products.name, orders.quantity
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN products ON orders.product_id = products.id;
```
## Footnote
Ensure the join conditions correctly map each pair of tables.
159
How do you find all customers who have not placed any orders?
Use `LEFT JOIN` with `IS NULL` to implement an anti join pattern:
```
SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.customer_id IS NULL;
```
## Footnote
[Your goal is to return customers only 🤓](https://g.co/gemini/share/243f4ae1f302)
160
How do you find the highest-selling product using a join with a subquery for calculation?
Use a subquery with joins to aggregate and filter based on conditions:
## Footnote
Use subqueries for complex calculations and filtering within joins.
161
How do you select employees with a salary between $50,000 and $100,000?
Use the `BETWEEN` operator to filter values in a range:
```
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
```
## Footnote
`BETWEEN` cannot be used with `IT`; [here's why](https://g.co/gemini/share/5dcaf80face6)
162
How do you order customer records by their last name in descending order?
Use the `ORDER BY` clause to set sorting order, and specify `DESC` for descending:
```
SELECT *
FROM customers
ORDER BY last_name DESC;
```
## Footnote
Use `ASC` for ascending order, which is the default.
163
How do you find departments(`department`) with average salaries(`salary`) greater than $60,000?
## Footnote
Employees Table
Use `HAVING` with `GROUP BY` to filter groups based on aggregate conditions:
```
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
```
## Footnote
`HAVING` is used to filter groups, unlike `WHERE`, which filters rows.
164
How do you select products from categories(`category_id`) 1, 2, or 3?
## Footnote
products Table
Use the `IN` operator to match any value in a list:
```
SELECT *
FROM products
WHERE category_id IN (1, 2, 3);
```
## Footnote
`IN` simplifies multiple `OR` conditions.
165
How do you find all employees without a manager assigned?
## Footnote
Assume that this refers to a single table, employees, where manager_id is a column within that table.
Use `IS NULL` to find rows with NULL values in a specific column:
```
SELECT * FROM employees WHERE manager_id IS NULL;
```
## Footnote
`IS NULL` checks for NULL values, while `IS NOT NULL` checks for non-NULL values.
166
How do you combine results from two tables with the same structure?
## Footnote
["with the same structure" 🤔](https://g.co/gemini/share/ed4e753b93a7)
Use `UNION` to combine results while removing duplicates:
```
SELECT column FROM table1
UNION
SELECT column FROM table2;
```
## Footnote
Use `UNION ALL` to include duplicates.
167
How do you select customers who have placed orders?
## Footnote
[📖](https://app.milanote.com/1TrAiI10ItJUfs?p=N4oXWlErVzz)
168
What is the primary difference between a LEFT JOIN and an INNER JOIN?
In a `LEFT JOIN`, all rows from the left table are returned, potentially with NULL values where no matching rows exist in the right table. In contrast, an `INNER JOIN` only returns rows where there is a match in both tables.
169
What type of relationship does a primary key establish between tables?
Establishes a one-to-one or one-to-many relationship with foreign keys in other tables.
170
What type of relationship does a foreign key establish between tables?
Establishes a many-to-one relationship with the table containing the primary key.
171
What is the purpose of the WHERE clause in SQL?
**Filters records** based on a specified condition.
172
What SQL statement is used to *associate* rows that have the same values in specified columns?
GROUP BY
173
What is the purpose of the LIMIT clause in SQL?
Restricts the number of rows returned by the query.
174
What does the IN operator do in SQL?
Checks if a *column’s value* exists within a list of specified values.
## Footnote
[📖](https://app.milanote.com/1TltKQ1AXG1Z48?p=N4oXWlErVzz)
175
What is the purpose of the `EXISTS` operator in SQL?
Evaluates whether _a subquery_ returns *any* rows.
176
What is the syntax of the UPDATE statement in SQL?
```
UPDATE table_name
SET column1 = value1, column2 = value2, ... WHERE condition;
```
`UPDATE` table_name `SET` ... is the core. However, always use `WHERE` condition to specify which rows to update
177
What is the function of the EXISTS operator in SQL?
The EXISTS operator is used to check for the *existence of any* records in a subquery.
178
When is the EXISTS operator often used?
Frequently used with subqueries to filter records based on conditions in related tables.
179
Provide an example of a query using the EXISTS operator.
```
SELECT *
FROM products p
WHERE `EXISTS` (SELECT 1 FROM orders o WHERE o.product_id = p.product_id);
```
180
Which SQL functions are commonly used with `GROUP BY`?
COUNT, SUM, AVG
181
How do you define a `FOREIGN KEY` constraint in SQL?
A `FOREIGN KEY` constraint is defined in a separate clause of a `CREATE TABLE` statement.
```
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(255),
DepartmentCode INT,
FOREIGN KEY (DepartmentCode) REFERENCES Departments(Code)
);
```
## Footnote
[📖 Separate Clauses?](https://app.milanote.com/1TG7Rm1JAOKG0G?p=N4oXWlErVzz)
182
This statement provides an example with which types of constraints?
1. PRIMARY KEY: Uniquely identifies each row in the Employee table (ID column).
2. NOT NULL: Ensures the Name column always has a value.
3. FOREIGN KEY: Links Employee records to valid Department records (DepartmentCode referencing Code).
4. DEFAULT: Sets a default value of '999' for DepartmentCode if none is given.
## Footnote
These constraints ensure data integrity and consistency in the database.
183
Add a constraint CK_BirthDate to the existing or non-existent table `Employees` specifying that `BirthDate` must be greater than `HireDate` which satisfies data to be allowed in the table.
## Footnote
[📖 Countering?](https://g.co/gemini/share/088f996d7259)
184
How do you add a `CHECK` constraint in a `CREATE TABLE` statement?
It can be added in the column declaration or as a separate clause.
185
How can you name a constraint when creating a table?
186
What is the syntax to add a new `CHECK` constraint using `ALTER TABLE`?
Use the following syntax to add a `CHECK` constraint:
```
ALTER TABLE TableName
ADD CONSTRAINT ConstraintName
CHECK (condition);
```
187
What is the command to drop the `UNIQUE` constraint called `UniqueNameMgr` from a table called `DEPARTMENT`?
Use the following SQL command:
`ALTER TABLE DEPARTMENT DROP CONSTRAINT UniqueNameMgr;`
188
What is the syntax for adding a `PRIMARY KEY` constraint to an existing table?
189
What SQL command is used to add a `UNIQUE` constraint to an existing table?
## Footnote
Don't use quotes for the name
Use the `ALTER TABLE` command with `ADD CONSTRAINT`.
Example:
```
ALTER TABLE TableName
ADD CONSTRAINT UniqueConstraintName UNIQUE (ColumnName);
```
190
How can you enforce that a `HireDate` must be later than a `BirthDate` using a constraint?
You can use a `CHECK` constraint.
Example:
`CHECK (HireDate > BirthDate)`[📖](https://app.milanote.com/1UmWu81uQwid59?p=N4oXWlErVzz)
191
What is an example SQL command to drop a `PRIMARY KEY` constraint from a table?
Use the following SQL command to drop a primary key constraint:
```
ALTER TABLE TableName
DROP PRIMARY KEY;
```
## Footnote
[📖](https://g.co/gemini/share/f329e9dfdd92)
192
How do you alter an existing column to add a `DEFAULT` constraint?
You can alter the column with the `ALTER $` command & `SET DEFAULT`:
```
ALTER TABLE TableName
ALTER COLUMN ColumnName SET DEFAULT 'DefaultValue';
```
[📖](https://g.co/gemini/share/c278b1ff8c40)
193
How can you specify that *a column must be greater than a certain value* using a constraint?
You would use a `CHECK` constraint for this purpose.
Example:
```
CHECK (ColumnName > $)
```
## Footnote
[📖](https://app.milanote.com/1UmWu81uQwid59?p=N4oXWlErVzz)
194
What is the syntax for adding a foreign key constraint to an existing table?
Use the `ALTER TABLE` command with `ADD CONSTRAINT`.
Example:
```
ALTER TABLE TableName
ADD CONSTRAINT FK_Name FOREIGN KEY (ColumnName) REFERENCES OtherTable (OtherColumn);
```
## Footnote
[Quotes 🙅🏽♂️](https://g.co/gemini/share/0f1befa869fa)
195
What SQL statement **retrieves** the creation *details of a specified* table?
`SHOW CREATE TABLE TableName` *retrieves the SQL statement* that creates the specified table, including the definitions for all constraints that are associated with it.
## Footnote
This command is useful for understanding the structure and constraints of an existing table.
196
This displays what type of SQL syntax?
Literals
1. Explicit values that are *string, numeric, or binary*.
2. Strings must be surrounded by single quotes or double quotes.
3. Binary values are represented with x'0' where the 0 is any hex value.
197
The INSERT statement adds a student to the Student table. How many clauses are in the INSERT statement?
1. The INSERT INTO clause is followed by the VALUES clause.
2. This INSERT statement consists of two **main parts** rather than two distinct clauses.
198
The SQL statement below is used to select students with the last name "Smith". What is wrong with the statement?
1. 'Single' or "double" quotes must surround string literals.
2. The literal "Smith" must be surrounded by single or double quotes
199
In order to select the database called `world` you would?
USE world;
200
Which statement *must precede* a `SHOW TABLES` statement to see the tables from the `world` database?
USE world;
201
How would you shows all the columns in the `Country` table
`SHOW COLUMNS `
`FROM Country;`
202
Explain each 1,2,3
Table Name, Column Name, Column Values
203
Why would the statement `DROP Employee;` fail to delete the Employee table?
1. It does not include the keyword `TABLE`.
2. The correct syntax should be `DROP TABLE Employee;`
204
# Suppose the Table is ['Department']
How would add a column called `Description` to the `Department` table.
```
ALTER TABLE Department
ADD Description VARCHAR(50);
```
205
# Suppose the Table is `Department`
How would you rename column `Description` to `ShortDesc` & *change the data type*
```ALTER TABLE Department
CHANGE Description ShortDesc
VARCHAR(50);```
## Footnote
[📖 'Rename'](https://g.co/gemini/share/d4510e3a8187)
206
# Suppose the Table is `Department`
How would you change column `ShortDesc` to accept up to 80 characters.
```
ALTER TABLE Department
CHANGE ShortDesc ShortDesc VARCHAR(80);
```
207
# Suppose the Table is `Department`
How would you delete the column `ShortDesc`?
```
ALTER TABLE Department
DROP ShortDesc;
```
208
In a WHERE clause, how should string values be treated?
They should be quoted
209
In a WHERE clause, when comparing a column to a string value, ensure the string value is _______.
quoted
210
What SQL statement requires quotes for inserting string values?
INSERT and [UPDATE](https://app.milanote.com/1Ulr7R1IF77P5p?p=N4oXWlErVzz) statements
211
Changing a Column's Data Type you use:
212
Provide an example of an INSERT statement with a string value.
INSERT INTO Student (FirstName) VALUES ('John')
213
When specifying values for VARCHAR or CHAR data types, what must be included?
Quotes
214
If i wanted to ensure that `Budget` was positive you would do what?
To ensure this you would add a `CHECK` constraint
## Footnote
`CHECK (Budget >= 0)`
215
A positive integer with maximum value of `65,535 ` *means what*?
Using `UNSIGNED` for the `ID` column in your SQL table definition ensures that only non-negative values.
## Footnote
`ID SMALLINT UNSIGNED`
216
If i wanted to ensure that `ID` was a unique identifier column that's doesn't accept NULLs; how would I do this?
Add PK at the declaration
## Footnote
`ID SMALLINT UNSIGNED PRIMARY KEY`
217
To rename a *column & data type* use:
218
1. In regards to creating a table evaluate the following column scenario.
2. A **variable-length string** with max 30 chars would be represented as what?
VARCHAR(30)
## Footnote
Dont forget if u0nsigned or signed
219
1. In regards to creating a table evaluate the following column scenario.
2. A integer with range 0 to 65 thousand, auto increment, & primary key; would be represented as what?
`ID SMALLINT AUTO_INCREMENT PRIMARY KEY`
## Footnote
Dont forget if u0nsigned or signed
220
1. In regards to creating a table evaluate the following column scenario.
2. A **fixed-length string** with max 10 chars, not NULL would be represented as what?
`CHAR(10) NOT NULL`
## Footnote
Dont forget if u0nsigned or signed
221
How should aliases with special characters or spaces be enclosed?
In double quotes
222
What is the main situation where quotes are definitely required in SQL?
1. Involving string comparisons or mentions (*comment*
2. Possible in `ALTER`, `UPDATE`
3. Usually for `INSERT INTO`
4. Usually `WHERE` clauses/operator
223
We want to get a unique list of all product IDs that are either present in our Products table or have been ordered in our Orders table.
## Footnote
[📖](https://g.co/gemini/share/f31347698eeb)
224
This method is the common method of renaming a column in a table.
## Footnote
What do we use to change data structures?
What is the `RENAME` syntax .
```
ALTER TABLE MyBooks
RENAME COLUMN Publication_Year TO PublishYear;
```
## Footnote
We use [Change](https://tinyurl.com/299j762r)
225
List the 3 common DDL statements
## Footnote
Data definition language
1. `CREATE` TO Create tables, indexes, or databases.
2. `ALTER` To modity existing database structures, such as tables or columns.
3. `DROP`To delete database objects like tables or indexes.
## Footnote
DDL Statements are used to *define and manage* database structures.
226
List the 4 common DML statements
## Footnote
Data MANIPULATION Language
1. `SELECT` TO query data from one or more tables.
2. `INSERT` To add new rows to a table.
3. `UPDATE` TO modify existing data within a table.
4. `DELETE`To remove data from a table.
## Footnote
DML Statements are used to manipulate data within the database.
227
List the 2 common DCL statements
## Footnote
Data Control Language
1. `GRANT` TO Provide privileges to users or roles.
2. `REVOKE`To remove access privileges from users or roles.
## Footnote
DCL statements are used to control access to data within the database.
228
To Delete & modify views you use:
229
To create a view you use:
230
List the 3 common TCL statements
## Footnote
Transaction Control Language
1. `COMMIT` To save changes made during a transaction.
2. `ROLLBACK` To undo changes made during a transaction.
3. `SAVEPOINT`TO set a point within a transaction to which you can later roll back.
## Footnote
TCL statements are used to manage transactions in the database. [📖](https://app.milanote.com/1UomQo1GINKt5L?p=N4oXWlErVzz)
231
# Explain the SQL Example & it's conjuction.
```
SELECT p.productID, o.orderDate
FROM Products p INNER JOIN Orders o
ON p.productID = o.productID
WHERE o.quantity > 10;
```
## Footnote
Filter results based on conditions after combining tables.
`INNER JOIN` with `WHERE`
## Footnote
Filter results based on conditions after combining tables.
232
To Delete & modify views you use:
233
To create a view you use:
234
# Explain the SQL Example & it's conjuction.
```
SELECT p.productName, COALESCE(o.orderDate, 'No orders') AS orderDate
FROM Products p
LEFT JOIN Orders o ON p.productID = o.productID;
```
## Footnote
Handle NULL results in joined data fields.
Left Join with Coalesce
## Footnote
Handle NULL results in joined data fields.
235
# Explain the SQL Example & it's conjuction.
```
SELECT c.customerID, COUNT(o.orderID) AS orderCount
FROM Orders o RIGHT JOIN Customers c
ON c.customerID = o.customerID
GROUP BY c.customerID;
```
## Footnote
Show counts of related records even if no direct match.
Right Join with COUNT
## Footnote
Show counts of related records even if no direct match.
236
# Explain the SQL Example & it's conjuction.
```
SELECT p.productName, c.customerName
FROM Products p CROSS JOIN Customers c
LIMIT 10;
```
## Footnote
Pair each row from each table with every other row, limit results.
CROSS JOIN with Limit
## Footnote
Pair each row from each table with every other row, limit results.
237
# Explain the SQL Example & it's conjuction.
```
SELECT productID, SUM(quantity)
FROM Orders
GROUP BY productID
HAVING SUM(quantity) > 100;
```
## Footnote
Filter groups based on aggregated criteria.
HAVING with SUM
## Footnote
Filter groups based on aggregated criteria.
238
# Explain the SQL Example & it's conjuction.
```
SELECT COALESCE(p.productName, 'Unknown') AS productName, o.orderID
FROM Products p FULL OUTER JOIN Orders o
ON p.productID = o.productID;
```
## Footnote
View records from both tables, filling in gaps.
FULL OUTER JOIN with COALESCE
## Footnote
View records from both tables, filling in gaps.
239
# Explain the SQL Example & it's conjuction.
```
SELECT customerName
FROM Customers c
WHERE EXISTS ( SELECT 1 FROM Orders o WHERE c.customerID = o.customerID AND o.orderTotal > 500);
```
## Footnote
Check for existence of rows in a subquery.
EXIST with Subquery
## Footnote
Check for existence of rows in a subquery.
240
# Explain the SQL Example & it's conjuction.
```
SELECT
```
241
# Explain the SQL Example & it's conjuction.
```
SELECT h.RegisteredName, s.StableName
FROM Horse h
JOIN Stables s ON h.ID = s.HorseID
WHERE s.StableName IS NOT NULL;
```
## Footnote
Avoid NULL values when joining.
`IS NOT NULL` with `JOIN`
## Footnote
Avoid NULL values when joining.
242
# Explain the SQL Example & it's conjuction.
```
SELECT
```
## Footnote
FULL OUTER JOIN with COALESCE
## Footnote
FULL OUTER JOIN with COALESCE
243
# Explain the SQL Example & it's conjuction.
```
SELECT RegisteredName
FROM HorseWHERE ID IN ( SELECT HorseID FROM Races WHERE Finished = 1);
```
## Footnote
Filter by values from a subquery.
`Subquery` with `IN`
## Footnote
Filter by values from a subquery.
244
# Explain the SQL Example & it's conjuction.
```
SELECT h.RegisteredName, COUNT(r.RaceID)
FROM Horse h
LEFT JOIN Races r ON h.ID = r.HorseIDGROUP
BY h.RegisteredName;
```
## Footnote
Show all horses whether they have raced.
`LEFT JOIN` with `COUNT`
## Footnote
Show all horses whether they have raced.
245
# Explain the SQL Example & it's conjuction.
```
SELECT s.StableName, h.RegisteredName
FROM Stables s
RIGHT JOIN Horse h ON s.HorseID = h.ID;
```
## Footnote
Show the horses that have not raced.
`RIGHT JOIN` to *see unmatched rows in a table*.
## Footnote
See unmatched rows in a table.
246
# Explain the SQL Example & it's conjuction.
```
SELECT RegisteredName
FROM Horse WHERE Height > 16 UNION
SELECT RegisteredName
FROM Horse WHERE BirthDate > '2018-01-01';
```
## Footnote
Apply conditional logic without separate queries.
`Union` of Multiple `SELECT`s
## Footnote
Apply conditional logic without separate queries.
247
# Explain the referential action with FK
Scenario: Suppose you have `Orders` and `OrderItems` tables. If an order is deleted, you likely want all its items to be deleted automatically.
## Footnote
Automatically deletes child records when the corresponding parent record is deleted.
* `ON DELETE CASCADE`
* Employ this action when it's important that no orphaned records exist in the child table. It's useful in situations where child records have no meaning or function without their parent.
248
# Explain the referential action with FK
Scenario: Consider a `Books` and `Authors` relationship. If an author is deleted but their books can remain in the database, *this would* disassociate the books from the deleted author.
## Footnote
Sets the foreign key column to `NULL` in the child records when the parent record is deleted.
* `ON DELETE SET NULL`
* This is suitable when you want to keep child records but remove any association with the deleted parent. Use this when child records can stand independently or be re-associated later
249
# Explain the referential action with FK
Scenario: Suppose you have a `Tasks` table with a `StatusID` that defaults to a "pending" or similar status. If a status is deleted, tasks revert to this default.
## Footnote
Sets the foreign key column to a default value when the parent record is deleted.
1. `ON DELETE SET DEFAULT`
2. Opt for this if you have a default value that makes sense for records losing their parent link.
250
# Explain the referential action with FK
Scenario: In a `Departments` and `Employees` scenario, you might want to prevent the deletion of a department if it still has employees, requiring you to reassign or delete employees first.
## Footnote
Prevents any deletion of a parent record if there are associated child records, ensuring no orphaned data.
1. `ON DELETE RESTRICT`
2. Use this to enforce strict referential integrity without any automatic clean-up. It’s good when deletions should be carefully managed.
251
To rename a column in a table, use:
252
Changing a Column's Data Type you use:
253
Why is 2 not considered the number of attributes present in the address fragment 'Portland, OR 97212'?
2 is incorrect because a complete address fragment usually includes more than just two pieces of information, such as city, state, and zip code.
254
Why is 3 considered the correct number of attributes present in the address fragment 'Portland, OR 97212'?
3 is correct because the address fragment 'Portland, OR 97212' explicitly contains the city (Portland), state (OR), and zip code (97212), totaling three attributes.
255
Why is 'birthdate' not designated as the primary key for the Patient table?
'birthdate' is incorrect because multiple patients can share the same birthdate, meaning it cannot uniquely identify each record.
256
Why should '`patient_id`' be designated as the primary key for the Patient table?
'`patient_id`' should be designated as the primary key because it is an integer column specifically designed to hold a unique identifier for each patient record, ensuring that each record can be uniquely identified.
257
Why is '`exam_id`' not designated as the foreign key for the Exam table?
'`exam_id`' is incorrect because it is explicitly labeled as PK exam_id in the diagram, indicating it is the primary key of the Exam table itself, not a foreign key referencing another table.
258
Why should '`patient_id`' be designated as the foreign key for the Exam table?
'`patient_id`' (labeled as patient_number in the column list) should be designated as the foreign key because it is explicitly labeled as FK patient_id in the diagram, indicating its role in linking the Exam table to the Patient table by referencing the patient's primary key.
259
Why is '`Integer`' not the data type that represents numbers with fractional values?
'Integer' is incorrect because it is used for storing whole numbers without any decimal component.
260
Why is 'Binary' not the data type that represents numbers with fractional values?
'Binary' is incorrect because it is used for storing binary data, not numerical values with fractions.
261
Why is 'Decimal' the data type that represents numbers with fractional values?
'Decimal' is correct because the DECIMAL data type is specifically designed to store numbers with a fixed or specified number of decimal places, making it suitable for fractional values.
262
Why is 'SELECT' not a DDL command?
'SELECT' is incorrect because it is a DML (Data Manipulation Language) command used for retrieving data from a database, not for defining or modifying database structure.
263
Why is 'UPDATE' not a DDL command?
'UPDATE' is incorrect because it is a DML (Data Manipulation Language) command used for modifying existing data in a table, not for defining or modifying database structure.
264
Why is 'CREATE INDEX' a DDL command?
'CREATE INDEX' is correct because CREATE INDEX is a DDL (Data Definition Language) command used to define or create new database objects, specifically an index, which is part of the database schema.
265
Why is 'CREATE VIEW' not a DML command?
'CREATE VIEW' is incorrect because it is a DDL (Data Definition Language) command used for defining new database objects (views), not for manipulating data.
266
Why is 'CREATE TABLE' not a DML command?
'CREATE TABLE' is incorrect because it is a DDL (Data Definition Language) command used for defining new database objects (tables), not for manipulating data.
267
Why is 'ALTER INDEX' not a DML command?
'ALTER INDEX' is incorrect because it is a DDL (Data Definition Language) command used for modifying an existing database object (index), not for manipulating data.
268
Why is 'INSERT' a DML command?
'INSERT' is correct because it is a DML (Data Manipulation Language) command used for manipulating data within schema objects, specifically for adding new rows of data into a table.
269
Why would 'Those exams would remain in the database.' not happen if a patient linked to exams is deleted when ON DELETE CASCADE is specified?
'Those exams would remain in the database.' is incorrect because the ON DELETE CASCADE foreign key constraint explicitly means that if the parent record (patient) is deleted, the dependent child records (exams) will also be automatically deleted.
270
Why would 'The Patient ID for those exams would be changed to NULL.' not happen if a patient linked to exams is deleted when ON DELETE CASCADE is specified?
'The Patient ID for those exams would be changed to NULL.' is incorrect because ON DELETE CASCADE results in the deletion of the entire dependent row, not just setting the foreign key to NULL.
271
Why would 'Those exams would be deleted also.' happen if a patient linked to exams is deleted when ON DELETE CASCADE is specified?
'Those exams would be deleted also.' is correct because the ON DELETE CASCADE clause in the foreign key definition ensures that when a record in the referenced (parent) table (Patient) is deleted, all corresponding records in the referencing (child) table (Exam) are also deleted.
272
Why would 'Those invoices would remain in the database.' not be the full outcome if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?
'Those invoices would remain in the database.' is incorrect because while the invoices themselves aren't deleted, the deletion of the linked customer (patient) would be prevented if those invoices exist.
273
Why would 'Those invoices would be deleted also.' not happen if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?
'Those invoices would be deleted also.' is incorrect because ON DELETE RESTRICT specifically prevents the deletion of the parent record if dependent child records exist, rather than deleting the child records.
274
Why would 'The Customer ID for those invoices would be changed to NULL.' not happen if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?
'The Customer ID for those invoices would be changed to NULL.' is incorrect because ON DELETE RESTRICT does not modify the foreign key value; its purpose is to prevent the parent deletion.
275
Why would 'The delete of the Customer would not be allowed.' happen if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?
'The delete of the Customer would not be allowed.' is correct because the ON DELETE RESTRICT clause prevents the deletion of a record in the parent table (Customer in the explanation, Patient in the table schema) if there are any related records in the child table (Invoice in the explanation, Exam in the table schema) that reference it.
276
Why is 'It must be refreshed whenever the base table changes.' true about a materialized view?
A materialized view must be refreshed whenever the base table changes to ensure its data remains consistent and up-to-date with the underlying table.
277
Why is 'The results are stored as a temporary table.' not true about a materialized view?
'The results are stored as a temporary table.' is incorrect because a materialized view stores its results persistently in the database, unlike temporary tables which are transient.
278
Why is 'It is stored.' true about a materialized view?
'It is stored.' is correct because a materialized view creates and maintains a physical copy of the data on disk, unlike a regular view which is a virtual table defined by a query.
279
Why is '`SELECT M.Title, A.Actor FROM Movie M INNER JOIN Actor A ON M.ActorID = A.ActorID`' not a query illustrating an outer join?
This is incorrect because*it explicitly uses* the `INNER JOIN` keyword, which is a type of join that returns only matching rows from both tables, not all rows from one table as in an outer join.
280
Why is '`SELECT M.Title, A.Name FROM Movie M RIGHT JOIN Actor A ON M.ActorID = A.ActorID;`' the correct query illustrating an outer join?
Because a `RIGHT JOIN` is a type of outer join. It returns all rows from the right table (`Actor` in this case) and the matching rows from the left table (`Movie`). If there are actors without matching movies, their details will still appear with NULL for movie-related columns.
281
Why will 'All rows in Table B' not always be included in the result set if Table A is inner joined with Table B?
'All rows in Table B' is incorrect because an I`NNER JOIN `only includes rows from Table B that have a corresponding match in Table A based on the join condition. Rows in Table B without a match in Table A will be excluded.
282
Why will 'Only rows in Tables A and B that share the join condition' always be included in the result set if Table A is inner joined with Table B?
'Only rows in Tables A and B that share the join condition' is correct because an `INNER JOIN` returns only those rows where there is a match in both of the joined tables, based on the specified join condition.
283
Why is `SELECT * FROM Book;` the correct SQL query to return all data from the Book table without directly referencing any column names?
1. `SELECT *` is the standard SQL wildcard that specifies all columns from the table should be included in the result set, thereby returning all data without requiring individual column names to be listed.
2. `FROM Book;` indicates the table from which to retrieve the data.
284
Why would `SELECT * FROM Book WHERE Year = 2020`; not fully meet the requirements of the query?
`SELECT * FROM Book WHERE Year = 2020;` is incorrect because the requirement specifies retrieving only the Title and Genre values, whereas `SELECT *` returns all columns.
285
Why is `SELECT Title, Genre FROM Book WHERE Year = 2020;` the correct SQL query to retrieve the specified data?
1. `SELECT Title, Genre` correctly specifies the exact columns to be retrieved in the required order.
2. `FROM Book` indicates the table to query.
3. `WHERE Year = 2020` precisely filters the results to include only those records where the Year column has a value of 2020.
286
``` SELECT Book.Title, YearSales.TotalSales
FROM Book INNER JOIN YearSales ON Book.Year = YearSales.Year; ```
Why would the code not fulfill the 'if available' requirement for all books?
This is incorrect because an INNER JOIN would only return books that have a matching Year in the YearSales table, thereby excluding books for which sales data is not available.
287
```
SELECT Title, TotalSales
FROM Book LEFT JOIN YearSales
ON Book.Year = YearSales.Year;
```
Why is this code the correct SQL query to display both the Title and the TotalSales (if available) for all books?
1. SELECT Title, TotalSales correctly specifies the columns to be retrieved in the required order. FROM Book sets Book as the starting table.
2. LEFT JOIN YearSales ON Book.Year = YearSales.Year performs a LEFT JOIN, ensuring that all books from the Book table are included in the result set, and if a Year from Book has no match in YearSales, TotalSales will be NULL, thus satisfying the 'if available' condition.
288
Why are '`UPDATE TABLE`' and '`COMMIT TABLE`' not used for changes to a table structure?
'`UPDATE TABLE`' is for data modification, and '`COMMIT TABLE`' is for transaction management, neither of which alters the schema or definition of the table.
289
Why is '`ALTER` TABLE' the correct command for making all changes to a table structure?
'`ALTER TABLE`' is correct because it is the standard SQL DDL (Data Definition Language) command used to modify the structure of an existing table, allowing for operations such as adding, dropping, or modifying columns, or adding/removing constraints.
290
Why is 'SUM' not the SQL aggregate function that gives the number of rows containing non-null values for a given column?
'SUM' is incorrect because it calculates the sum of all numeric values in a column, not the count of rows.
291
Why is '`COUNT`' the SQL aggregate function that gives the number of rows containing non-null values for a given column?
'`COUNT`' is correct because the COUNT() aggregate function is specifically designed to count the number of rows. When given a column name `(COUNT(column_name))`, it counts only the rows where that column has a non-null value. `COUNT(*)` counts all rows.
292
Why is 'Unary many-to-many' not the kind of relationship displayed in the diagram?
'Unary many-to-many' is incorrect because the relationship involves two distinct entities (SALESPERSON and CUSTOMER), making it a binary relationship, not a unary (self-referencing) one.
293
Why is 'Binary one-to-many' the kind of relationship displayed in the entity-relationship diagram?
'Binary one-to-many' is correct because the diagram shows two entities (SALESPERSON and CUSTOMER), which is a binary relationship. The notation indicates that one SALESPERSON can 'Sells to' many CUSTOMERs, while each CUSTOMER 'Buys from' only one SALESPERSON, characteristic of a one-to-many relationship.
294
Why is 'Ternary one-to-one' not the kind of relationship displayed in the diagram?
'Ternary one-to-one' is incorrect because the relationship involves only two entities (SALESPERSON and PRODUCT), making it a binary relationship, not a ternary (involving three entities) one.
295
Why is '`Binary many-to-many`' the kind of relationship displayed in the entity-relationship diagram?
'Binary many-to-many' is correct because the diagram shows two entities (SALESPERSON and PRODUCT), making it a binary relationship. The crow's feet notation on both ends indicates that a SALESPERSON can 'Sells' many PRODUCTs, and a PRODUCT can be 'Sold by' many SALESPERSONs, characteristic of a many-to-many relationship.
296
Why is '`INTEGER`' not a data type that allows for storage of dates?
'`INTEGER`' is incorrect because it stores whole numbers. While dates could technically be stored as integers (as epoch timestamps), it's not the dedicated or most practical data type for date representation in SQL.
297
Why is '`TIMESTAMP/DATE`' a data type that can be designated to allow for storage of dates?
'`TIMESTAMP/DATE`' is correct because both `DATE` and `TIMESTAMP` are standard SQL data types specifically designed for storing temporal values. `DATE` stores only the date, while `TIMESTAMP` stores both date and time, providing appropriate formatting and functions for date/time operations.
298
Why would `DELETE` Customer `WHERE First Name = 'Amy' AND Last Name = 'Lin';` be a problematic way to delete a specific customer?
`DELETE Customer WHERE First Name = 'Amy' AND Last Name = 'Lin'`; is incorrect because it uses non-unique attributes (names) for deletion. If multiple customers named 'Amy Lin' exist, all of them would be deleted, which is likely unintended. Additionally, column names with spaces might require special handling (quoting) depending on the SQL dialect.
299
Why is `DELETE FROM Customer WHERE CustomerID = 101`; the correct query to delete Amy Lin from the Customer table?
`DELETE FROM Customer` specifies the table from which records are to be deleted. `WHERE CustomerID = 101 `
is the crucial WHERE clause that identifies the specific row to be deleted using the unique CustomerID (101 for Amy Lin), ensuring that only her record is removed.
300
How would you write a SQL statement to update Blanca Garcia's phone number to `555-222-1234`?
`UPDATE Customer SET Mobile Phone ='555-222-1234' WHERE ID=104;`
301
Why would SELECT CustomerID, City FROM Customer WHERE City = 'Seattle'; not fully retrieve 'all of the Customers' from Seattle?
SELECT CustomerID, City FROM Customer WHERE City = 'Seattle'; is incorrect because it only retrieves the CustomerID and City columns, whereas the request is to retrieve 'all of the Customers,' implying all columns for those customers.
302
Why is `SELECT * FROM Customer WHERE City = 'Seattle';` the correct SQL statement to retrieve all of the Customers from Seattle?
1. `SELECT *` retrieves all columns for each matching row, fulfilling the 'all of the Customers' part of the request.
2. `FROM Customer` specifies the table to query.
3. `WHERE City = 'Seattle'` precisely filters the records.
303
Why is `SELECT CustomerID, City FROM Customer WHERE City = 'Seattle';` incorrect?
It only retrieves the CustomerID and City columns, whereas the request is to retrieve 'all of the Customers,' implying all columns for those customers.
304
Why is `SELECT * FROM Customer WHERE City = 'Seattle';` the correct SQL statement?
1.` SELECT *` retrieves all columns for each matching row, fulfilling the 'all of the Customers' part of the request.
2. `FROM` Customer specifies the table to query.
3. `WHERE City = 'Seattle' `filters the records to include only those customers whose City column value is 'Seattle'.
305
How would you write a join statement to pull values from Customer, Invoice, and Invoice Item?
`SELECT Customer.CustomerID, Invoice.InvoiceID, Date, Invoice_Item.ProductID FROM Customer JOIN Invoice ON Customer.CustomerID=Invoice.CustomerID JOIN Invoice_Item ON Invoice.InvoiceID=Invoice_Item.InvoiceID;`
