Tables and Indexes
44 important questions on Tables and Indexes
What are the 2 types of tables that exist in MySQL?
View: a virtual table
What is stored in a format file in a database directory?
What is the suffix of a format file?
- Higher grades + faster learning
- Never study anything twice
- 100% sure, 100% understanding
What is the suffix of a MyISAM data file?
What is the suffix of a MyISAM index file?
Which database engine stores table data of multiple tables in the same file?
What database engine does not store database information on disk?
What is the maximum amount of tables for the InnoDB engine?
Name 3 external factors that may influence the table limit of MySQL?
2. Filesystem load time with lots of subdirectories (practical limit)
3. Disk space available
Name 3 methods to overcome the table size limits
2. Convert to storage engine with greater limits
3. Change the operating system
What are the 3 forms of creating a table supported by MySQL?
table
2. Create a table populated from a SELECT statement
3. Create a temporary table
What is the format of the CREATE TABLE command?
What 3 storage engines are always available?
What command displays the available storage engines?
How do you change a tables engine to MyISAM?
What system variable defines the default storage engine?
What is the default built-in storage engine of MySQL?
Name 3 ways to change the default storage engine
2. SET GLOBAL storage_engine = MyISAM
3. SET [SESSION] storage_engine = MyISAM
What command displays the warnings issued?
Is the AUTO_INCREMENT option copied with a CREATE TABLE ... SELECT statement?
Is the storage engine copied with a CREATE TABLE ... SELECT statement?
Is the default CHARSET copied with a CREATE TABLE...SELECT statement?
What 2 item types are not copied with a CREATE TABLE...LIKE statement?
2. Foreign key definitions
Name 4 properties of a TEMPORARY table?
2. It exists only for the duration of the connection of the client who created it
3. It may have the same name as a temporary table
4. It can be renamed only with ALTER TABLE
Does the table contents of a MEMORY table persist between server restarts?
Name 4 functions of the ALTER TABLE statement
2. Changing the name or definition of a column
3. Adding or dropping indexes
4. Renaming the table
Name 2 keywords for specifying the position of a column
Are column names case sensitive in MySQL?
What 2 keywords can change a column definition?
2. ALTER TABLE `table` CHANGE `old_colname` `new_colname` TYPE
What is the statement to rename a table?
What does not work with TEMPORARY tables RENAME TABLE or ALTER TABLE?
How do you swap the names of two tables in one command?
Can the DROP TABLE command drop multiple tables in one command?
What 2 statements delete all contents from a table?
TRUNCATE TABLE t;
Name 2 reasons for applying indexes on a table?
2. Unique constraint on a column
What are the 3 general index types in MySQL?
2. Unique, can allows null
3. Non-unique index, allows anything
Does a UNIQUE index allow multiple null values if the column allows it?
What is the syntax of the CREATE INDEX command?
What is the practical difference between ALTER TABLE and CREATE INDEX?
2. ALTER TABLE supports PRIMARY KEY
What storage engine supports index algorithm selection?
What is the default index algorithm for MEMORY tables?
Under what circumstances is a BTREE index better than HASH?
What is the syntax of the DROP INDEX statement?
Name a synonym for DESCRIBE `table_name`?
The question on the page originate from the summary of the following study material:
- A unique study and practice tool
- Never study anything twice again
- Get the grades you hope for
- 100% sure, 100% understanding
![Trustpilot Logo](/images/design_2020/lp/clp-trustpilot-en.png)