MySQL Flashcards
1
Q
What connection method is used for a local MySQL server?
A
Standard TCP/IP.
2
Q
What is the default host name for a local MySQL connection?
A
localhost
3
Q
What is the default port number for MySQL?
A
3306.
4
Q
What SQL command is used to create a database?
A
CREATE DATABASE.
5
Q
How should SQL statements be concluded?
A
With a semicolon.
6
Q
What icon is used to execute a command in MySQL Workbench?
A
The lightning bolt icon.
7
Q
What happens when you refresh the Schemas tab after creating a database?
A
The newly created database will appear.
8
Q
How do you select set a database as the default schema?
A
- Right-click on the database
- Select ‘set as default schema’.
9
Q
What command is used to switch to a specific database?
A
USE [database_name];
10
Q
What command is used to drop a database?
A
DROP DATABASE [database_name];
11
Q
What does the ‘sys’ database represent in MySQL?
A
The internal database used by MySQL.
12
Q
What SQL command is used to set a database to read-only mode?
A
ALTER DATABASE [database_name] READ ONLY = 1;
13
Q
What happens when a database is set to read-only mode?
A
Modifications aren’t allowed, but data can still be accessed.
14
Q
What SQL command is used to remove the read-only status of a database?
A
ALTER DATABASE [database_name] READ ONLY = 0;
15
Q
What is the SQL command to create a table?
A
CREATE TABLE [table_name];
16
Q
What are the components of a table in a relational database?
A
Rows and columns.
17
Q
A folder acts as a ______.
A
container
Tables are like the files contained within that folder.
18
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.
19
Q
What should you do if you want to recreate a database after dropping it?
A
CREATE DATABASE [database_name];
20
Q
What protocols can clients use to connect to MySQL Server?
A
Clients can connect using:
- TCP/IP sockets
- named pipes (on Windows)
- shared memory (on Windows)
- Unix domain socket files
Named pipes and shared memory connections require specific server startup configurations.
21
Q
Which programming languages are supported for writing MySQL client programs?
A
C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, Tcl
Client libraries are available for various languages, including C and C++.
22
Q
What is the purpose of the Connector/ODBC interface?
A
(ODBC) is a protocol that you can use to connect a Microsoft Access database to an external data source such as M̶i̶c̶r̶o̶s̶o̶f̶t̶ SQL Server
Open Database Connectivity
23
Q
What does Connector/J support?
A
- Connector/J is a Java driver that supports connections to various database engines including MySQL.
- It implements the Java Database Connectivity (JDBC) API and extensions to the API
Can be run on both Windows and Unix.
24
Q
What is the function of MySQL Connector/NET?
A
It enables developers to create .NET applications requiring secure, high-performance data connectivity with MySQL implementation.
It is a fully managed ADO.NET driver written in 100% pure C#.
25
How does MySQL support localization?
The server can provide **error messages** in many languages and supports *various character sets*.
## Footnote
Includes support for Scandinavian characters and several Unicode character sets.
26
What happens to data in MySQL regarding character sets?
All data is saved in the chosen character set
## Footnote
Sorting and comparisons are done according to the default character set and collation.
27
How can the server time zone be managed in MySQL?
The server time zone can be changed dynamically, and individual clients can specify their own time zone
## Footnote
This provides flexibility in handling time-related data.
28
Name two types of client and utility programs included in MySQL.
Command-line programs (e.g., mysqldump, mysqladmin) and graphical programs (e.g., MySQL Workbench)
## Footnote
These programs assist in database management and operations.
29
What built-in support does MySQL Server provide for table management?
SQL statements to check, optimize, and repair tables
## Footnote
These statements can be accessed through the mysqlcheck client.
30
What utility is available for performing operations on MyISAM tables?
myisamchk
## Footnote
It is a fast command-line utility for managing MyISAM tables.
31
What command can be used to obtain online assistance for MySQL programs?
--help or -?
## Footnote
This option provides help information for using MySQL programs.
32
What are two tools that allow the user to type SQL statements?
MySQL Workbench and MySQL Command-Line Client
33
What feature does MySQL Workbench have to reduce typing?
Automatically generate some SQL statements
34
When are SQL statements executed in MySQL Workbench?
When the lightning bolt is clicked
35
What keyboard shortcut executes all SQL statements in MySQL Workbench on Windows?
Ctrl+Shift+Enter
36
What keyboard shortcut executes all SQL statements in MySQL Workbench on a Mac?
Command+Shift+Enter
37
What does the Navigator sidebar in MySQL Workbench show after connecting to the MySQL server?
Browse Documentation and Local instance MySQL8O
38
What administrative options are available in the Administration tab of MySQL Workbench?
Checking server's status, importing/exporting data, starting/stopping the MySQL server
39
What does the Schemas tab show in MySQL Workbench?
A list of available databases
40
Fill in the blank: A database can be expanded to show the database's _______.
tables
41
What SQL statement is used to add a new column to an existing table?
ALTER TABLE table_name ADD COLUMN column_name data_type constraints;
## Footnote
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.
42
Provide an example of adding a new column to a table.
ALTER TABLE Customers ADD COLUMN email VARCHAR(255);
## Footnote
This example adds an 'email' column of type VARCHAR with a maximum length of 255 characters to the 'Customers' table.
43
What SQL statements is used to modify an existing column's definition?
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;
## Footnote
The choice between MODIFY COLUMN and ALTER COLUMN depends on the specific database system.
44
Provide an example of modifying an existing column.
```
ALTER TABLE Products
MODIFY COLUMN price DECIMAL(10, 2);
```
## Footnote
This example modifies the 'price' column in the 'Products' table to be of type DECIMAL with a precision of 10 and scale of 2.
45
What SQL statement is used to delete a column from a table?
```
ALTER TABLE table_name
DROP COLUMN column_name;
```
## Footnote
This statement removes the specified column from the table.
46
Provide an example of deleting a column from a table.
```
ALTER TABLE Employees
DROP COLUMN department;
```
## Footnote
This example deletes the 'department' column from the 'Employees' table.
47
What SQL statement is used to add a new row to a table?
```
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
## Footnote
This statement is used to insert a new record into the specified table.
48
Provide an example of adding a new row to a 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.
49
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 (e.g., MySQL, PostgreSQL, SQL Server).
50
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.
51
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;`
52
It specifies 'where' something comes from in a conversation.
## Footnote
Example: 'Where did you get this?'
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`.
53
It asks for a condition.
## 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'`.
54
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?
55
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`.
56
It categorizes items.
## Footnote
Example: 'Group the apples by their types' corresponds to `GROUP BY apple_type`.
What does `GROUP BY` do in SQL?
57
It adds something into a list.
## 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 (...)`.
58
It changes existing data.
## Footnote
Example: 'Change the address for my subscription'
What is the function of the `UPDATE` command in SQL?
## Footnote
Example: 'Change the address for my subscription' relates to `UPDATE subscriptions SET address = 'new_address'`.
59
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 = ...`.
60
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 customer_name FROM customers;`
61
1. Filter results after aggregation
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;`
62
Find values resembling a pattern
What does the `LIKE` operator do in SQL?
## Footnote
Example: `WHERE name LIKE 'John%';`
63
Select values within a specified range
## Footnote
Example: `WHERE price BETWEEN 10 AND 20`
What does the `BETWEEN` operator specify in SQL?
## Footnote
Example: `WHERE price BETWEEN 10 AND 20`
64
Restrict the number of results returned
## Footnote
Example: `LIMIT 5`
What does the `LIMIT` clause do in SQL?
65
Combine results from two or more queries
## Footnote
Example: `SELECT item FROM list1 UNION SELECT item FROM list2`
What does `UNION` do in SQL?
66
Include all items from one side regardless of matches
## Footnote
Example: `LEFT OUTER JOIN` or `RIGHT OUTER JOIN`
What is the function of an `OUTER JOIN` in SQL?
67
Join tables based on equivalent values
## Footnote
Example: `SELECT * FROM students INNER JOIN grades ON students.id = grades.student_id`
What is an `EQUIJOIN`?
68
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?
69
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`?
70
Evaluate different conditions and return values
## Footnote
Example: `CASE WHEN weather = 'rainy' THEN 'bring umbrella' ELSE 'wear jacket' END`
What does the `CASE` statement do in SQL?
71
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?
72
Remove all records from a table
## Footnote
Example: `TRUNCATE TABLE shopping_cart`
What does the `TRUNCATE` command do?
73
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?
74
A reference that links rows in different tables
## Footnote
Example: Links information to a student
What is a `FOREIGN KEY`?
75
The main identifier for records in a table
## Footnote
Example: Uniquely identifies each user by email
What is a `PRIMARY KEY`?
76
Undo the last transaction
## Footnote
Example: Reverse changes if a mistake is made
What does `ROLLBACK` do in SQL?
77
Confirm and save changes made
## Footnote
Example: Save all changes made to the profile
What does `COMMIT` signify in SQL?
78
Remove a table or database entirely
## Footnote
Example: `DROP TABLE old_contacts`
What does `DROP` do in SQL?
79
Modify an existing database object
## Footnote
Example: `ALTER TABLE contacts ADD COLUMN phone_number VARCHAR(15);`
What is the function of the `ALTER` command?
80
Change the data type of a value
## Footnote
Example: `CAST(number AS VARCHAR)`
What does `CAST` do in SQL?
81
What is a database system instance?
A single executing copy of a database system.
82
What is the purpose of the CREATE DATABASE statement?
Used to create a new database.
83
What does the DROP DATABASE statement do?
Deletes a specified database along with its tables.
84
What is the SHOW DATABASES command used for?
Lists all databases in the system.
85
What information does the SHOW TABLES command provide?
Lists tables in the selected database.
86
What does the SHOW COLUMNS command do?
Shows the columns in a specified table.
87
What is the purpose of the SHOW CREATE TABLE command?
Displays the SQL command used to create a particular table.
88
What is the function of the USE statement?
Required to select the active database before executing other SHOW commands.
89
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.
90
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.
91
How is Oracle Database different from MySQL in terms of storage engines?
Oracle Database has a built-in storage engine that cannot be changed, while MySQL has multiple storage engines
## Footnote
This is considered unusual for relational databases.
92
What are the four main parts organized in MySQL architecture?
1. Tools Layer
2. Query Processor Layer
3. Storage Engine Layer
4. File System Layer
93
What is the role of the query processor?
Manages connections and compiles queries
## Footnote
It translates SQL into low-level storage engine instructions.
94
What does a connection in MySQL authorize?
A user to access a database
95
What is the main function of the storage engine?
Transaction management and data access
96
What does the audit feature in MySQL do?
Tracks all database changes, including the time of change and who made the change
97
What are the two main functions of the query processor layer?
1. Manage connections
2. Compile queries
98
What is an execution plan?
A detailed, low-level sequence of steps that specify how to process a query
99
What is the purpose of the cache manager in the query processor layer?
Stores reusable information in main memory for optimal performance
100
What components are included in transaction management?
1. Concurrency system
2. Recovery system
3. Lock manager
101
What types of data does the file system layer contain for each database?
1. User data
2. Log files
3. Data dictionary
102
What does the MySQL data dictionary include?
Roughly 30 tables, including tables, table_stats, columns, foreign_keys, indexes, routines, and triggers
103
InnoDB is the default
Default storage engine in MySQL
104
Fill in the blank: The _______ manager retains data blocks from the file system for possible reuse.
Buffer
105
What does the recovery system use to restore data in the event of a failure?
Log files
106
What are Connectors and APIs in MySQL?
Groups of application programming interfaces linking applications to the query processor layer
107
What is the function of the buffer manager?
Retains data blocks in an area of main memory called the buffer
108
What does the query optimizer do?
Generates alternative execution plans, estimates execution times, and selects the fastest plan
109
What is the primary role of utility programs in MySQL?
Perform various administrative functions such as upgrading databases, backing up, and importing data
110
What are the four main parts of MySQL architecture?
Layers, Tools, Query Processor, Storage Engine
## Footnote
Each part plays a crucial role in the overall functionality of MySQL.
111
What is the role of the Tools layer in MySQL?
Interacts directly with database users and administrators, sending queries to the query processor.
112
How do users interact with the Tools layer in MySQL?
Using forms, buttons, and menus to input commands without needing to write in a terminal.
113
What does the query processor manage in MySQL?
Connections from multiple users and compiles queries into low-level instructions for the storage engine.
114
What is the function of the storage engine in MySQL?
Executes instructions, manages indexes, and interacts with the file system.
115
What does the MySQL Enterprise Monitor do?
Collects and displays information on CPU, memory, and index utilization, as well as queries and results.
116
What is the purpose of the Audit feature in MySQL?
Tracks all database changes, including the time of change and who made the change.
117
What types of data does the file system layer in MySQL contain?
User data, log files, and a data dictionary.
118
What is a catalog in the context of MySQL?
A directory of tables, columns, keys, indexes, and other objects in a relational database.
119
What is the default storage engine in MySQL?
InnoDB.
120
What are the two main functions of the storage engine layer?
Transaction management and data access.
121
What does transaction management in MySQL include?
Concurrency system, recovery system, and lock manager.
122
What algorithm does the InnoDB buffer manager use?
Least Recently Used (LRU) algorithm.
123
How many storage engines does MySQL support?
Nine storage engines, including InnoDB, MyISAM, CSV, and MEMORY.
124
What is the function of the connection manager in the query processor?
Creates connections and manages communications between tools and the query parser.
125
What are the two types of execution plans in query optimization?
Estimated execution plans and actual execution plans.
126
What is the purpose of indexing in SQL query optimization?
To speed up data retrieval without scanning the entire table.
127
Fill in the blank: The tools layer includes Connectors and APIs, Workbench, and _______.
utility programs.
128
What are the five categories of utility programs in MySQL?
Installation, client, administrative, developer, and miscellaneous tools.
129
True or False: The recovery system uses log files to restore data in the event of a transaction failure.
True.
130
What is the role of the buffer manager in MySQL?
Retains data blocks from the file system for possible reuse.
131
What is the role of the query optimizer?
Calculates the estimated cost for executing each plan to select the most efficient one.
132
List some common optimization techniques for SQL queries.
* Indexing
* Query Rewriting
* Database Statistics
* Use of Joins
* Filtering Early
* Limiting Result Sets.
133
# Querying tables
Get all the columns from a table
```sql
SELECT *
FROM airbnb_listings;
```
134
# Querying tables
Return the city column from the table
```sql
SELECT city
FROM airbnb_listings;
```
135
# Querying tables
Get the city and year_listed columns from the table
```sql
SELECT city, year_listed
FROM airbnb_listings;
```
136
# Querying tables
Get the listing id, city, ordered by the `number_of_rooms` in ascending order
```sql
SELECT city, year_listed
FROM airbnb_listings
ORDER BY number_of_rooms ASC;
```
137
# Querying tables
Get the listing id, city, ordered by the `number_of_rooms` in descending order
```sql
SELECT city, year_listed
FROM airbnb_listings
ORDER BY number_of_rooms DESC;
```
138
# Querying tables
Get the first 5 rows from `airbnb_listings`
```sql
SELECT *
FROM airbnb_listings
LIMIT 5;
```
139
# Querying tables
Get a unique list of cities where there are listings
```sql
SELECT DISTINCT city
FROM airbnb_listings;
```
140
# Querying tables
Get all the listings where number_of_rooms is more or equal to 3
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms >= 3;
```
141
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is more than 3
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms > 3;
```
142
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is exactly 3
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms = 3;
```
143
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is lower or equal to 3
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms <= 3;
```
144
# Filtering on numeric columns
Get all the listings where `number_of_rooms` is lower than 3
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms < 3;
```
145
# Filtering on numeric columns
Get all the listings with 3 to 6 rooms
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms BETWEEN 3 AND 6;
```
146
# Filtering on text columns
Get all the listings that are based in 'Paris'
```sql
SELECT *
FROM airbnb_listings
WHERE city = 'Paris';
```
147
# Filtering on text columns
Get the listings based in the 'USA' and in 'France'
```sql
SELECT * FROM airbnb_listings
WHERE country IN ('USA', 'France');
```
148
# Filtering on text columns
Get all listings where city starts with "j" and where it does not end with "t"
```sql
SELECT *
FROM airbnb_listings
WHERE city LIKE 'j%' AND city NOT LIKE '%t';
```
149
# Filtering on multiple columns
Get all the listings in "Paris" where number_of_rooms is bigger than 3
```sql
SELECT * FROM airbnb_listings
WHERE city = 'Paris' AND number_of_rooms > 3;
```
150
# Filtering on multiple columns
Get all the listings in "Paris" OR the ones that were listed after 2012
```sql
SELECT *
FROM airbnb_listings
WHERE city = 'Paris' OR year_listed > 2012;
```
151
# Filtering on missing data
Get all the listings where `number_of_rooms` is missing
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms IS NULL;
```
152
# Filtering on missing data
Get all the listings where `number_of_rooms` is not missing
```sql
SELECT * FROM airbnb_listings
WHERE number_of_rooms IS NOT NULL;
```
153
# Simple aggregations
Get the total number of rooms available across all listings
```sql
SELECT SUM(number_of_rooms)
FROM airbnb_listings;
```
154
# Simple aggregations
Get the average number of rooms per listing across all listings
```sql
SELECT AVG(number_of_rooms)
FROM airbnb_listings;
```
155
# Simple aggregations
Get the listing with the highest number of rooms across all listings
```sql
SELECT MAX(number_of_rooms)
FROM airbnb_listings;
```
156
# Simple aggregations
Get the listing with the lowest number of rooms across all listings
```sql
SELECT MIN(number_of_rooms)
FROM airbnb_listings;
```
157
Get the total number of rooms for each country
```sql
SELECT country, SUM(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
158
Get the average number of rooms for each country
```sql
SELECT country, AVG(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
159
Get the listing with the maximum number of rooms for each country
```sql
SELECT country, MAX(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
160
Get the listing with the lowest amount of rooms per country
```sql
SELECT country, MIN(number_of_rooms) FROM airbnb_listings
GROUP BY country;
```
161
# Grouping, filtering, and sorting
For each country, get the average number of rooms per listing, sorted by ascending order
```sql
SELECT country, AVG(number_of_rooms) AS avg_rooms
FROM airbnb_listings
GROUP BY country
ORDER BY avg_rooms ASC;
```
162
# Grouping, filtering, and sorting
For Japan and the USA, get the average number of rooms per listing in each country
```sql
SELECT country, AVG(number_of_rooms) FROM airbnb_listings
WHERE country IN ('USA', 'Japan') GROUP BY country;
```
163
# Grouping, filtering, and sorting
Get the number of listings per country
```sql
SELECT country, COUNT(id) AS number_of_listings
FROM airbnb_listings
GROUP BY country;
```
164
# Grouping, filtering, and sorting
Get all the years where there were more than 100 listings per year
```sql
SELECT year_listed
FROM airbnb_listings
GROUP BY year_listed
HAVING COUNT(id) > 100;
```
