1/122
Looks like no tags are added yet.
How do you create a copy of an existing table in MySQL?
Use the CREATE TABLE AS statement with a SELECT statement.
What happens if you use CREATE TABLE AS with a name that already exists?
The existing table is not automatically copied over.
What is the requirement for string and date values in an INSERT statement?
They must be enclosed within quotes.
How do you insert multiple rows in a single INSERT statement?
Separate each list of values with a comma.
What is a subquery in SQL?
A SELECT statement coded within another SQL statement.
How do you use a subquery in an INSERT statement?
Use a SELECT statement instead of a VALUES clause, without parentheses around the SELECT.
When should you use parentheses around a subquery in an UPDATE or INSERT statement?
When the subquery is in the WHERE clause.
What is the purpose of Boyce-Codd Normal Form?
To eliminate transitive dependencies.
What is denormalization?
A deliberate deviation from normal forms, typically occurring beyond the third normal form.
What is a database view?
A SELECT statement stored in the database as a virtual table object.
What command is used to delete a view?
DROP VIEW.
What are the primary advantages of using views?
Hiding query complexity, limiting table exposure, allowing data updates, and restricting database access.
Why is it necessary to change the default delimiter in MySQL?
To allow a CREATE PROCEDURE statement to contain multiple SQL statements.
What statement executes the code within a stored procedure?
The CALL statement.
What is the difference between DROP DATABASE and DROP DATABASE IF EXISTS?
DROP DATABASE throws an error if the database is missing, while IF EXISTS prevents that error.
What is the difference between the NOT NULL and UNIQUE constraints?
NOT NULL prevents NULL values, while UNIQUE ensures distinct values but allows NULLs.
What is a composite key?
A primary key that spans multiple columns and cannot be defined at the column level.
What is the purpose of the FOREIGN KEY constraint?
To ensure referential integrity between tables.
What happens if you attempt to insert a foreign key value that does not exist in the parent table?
MySQL rejects the insert with Error Code 1452.
What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?
CASCADE deletes related child rows, while SET NULL sets the foreign key in child rows to NULL.
What is the purpose of the AUTO_INCREMENT attribute?
To automatically generate unique integers for new rows in primary key columns.
What is the difference between CHAR and VARCHAR?
CHAR uses a fixed amount of storage for each value, while VARCHAR uses varying storage based on length.
What is the difference between scale and precision in a DECIMAL type?
Precision is the total number of digits, and scale is the number of digits to the right of the decimal point.
What is the purpose of an index in a database?
To speed up data retrieval by allowing quicker matching of rows.
What is the difference between CREATE INDEX and CREATE UNIQUE INDEX?
CREATE INDEX improves speed, while CREATE UNIQUE INDEX also enforces distinct values.
What is the requirement for updating rows in MySQL Workbench's Safe Update Mode?
The WHERE clause must refer to primary or foreign keys.
What are the three most widely used data type categories in MySQL?
Numeric, date and time, and character types.
What is the primary difference between CHAR and VARCHAR?
CHAR is for fixed-length strings, while VARCHAR is for variable-length strings.
How does storage differ between CHAR(10) and VARCHAR(10) when storing 'NE'?
CHAR(10) uses fixed space for 10 characters, while VARCHAR(10) uses space for the 2 characters plus 1 byte for length.
What is the maximum byte size for a character in the UTF8mb4 character set?
Up to 4 bytes.
What is the purpose of a primary key?
To uniquely identify each row in a table.
How are many-to-many relationships implemented in a database?
By creating a linking table that contains primary key columns from both related tables.
What is the primary benefit of database normalization?
It reduces data redundancy and related storage or maintenance issues.
What are the first three normal forms commonly applied in database design?
First, second, and third normal forms.
What is the function of an index in a database?
It allows for direct row location, preventing the need to search the entire table.
Which types of keys does MySQL index automatically?
Primary keys and foreign keys.
What criteria should be used when deciding to create an index on a column?
Columns that are frequently searched, have distinct values, are infrequently updated, or are used in joins.
Why is it not advisable to index every column in a table?
Because it can create unnecessary technical debt and overhead despite potential performance gains.
What is a composite index?
An index consisting of two or more columns.
What is the difference between TRUNCATE TABLE and DROP TABLE?
TRUNCATE deletes all rows but keeps the table structure; DROP removes the table entirely.
Why might a DROP TABLE command fail?
It fails if there are foreign key references to the table (Error Code 3730).
What is the purpose of the ALTER TABLE... MODIFY command?
To change a column's data type, length, or attributes without truncating existing data.
Why should telephone numbers be stored as character types rather than numeric?
Character types are preferred for values that do not require mathematical operations.
What attribute is used with integers to prevent negative values?
The UNSIGNED attribute.
What is the difference between scale and precision in decimal numbers?
Precision refers to the total number of digits, while scale refers to the number of digits after the decimal point.
What is the default format for a DATE type in MySQL?
yyyy-mm-dd.
What is the role of a composite key?
It establishes a unique primary identity using two or more columns.
What happens when a user attempts to insert an invalid foreign key value?
MySQL systematically blocks the attempt.
What is the standard practice for defining strings in SQL?
Using single quotes.
What is the consequence of deleting a parent row in a table with foreign key constraints?
It may require prior child row clean-up or depend on cascading behavior settings.
What are the six steps for designing a data structure?
1) Identify data elements, 2) Subdivide components, 3) Identify tables and columns, 4) Identify keys, 5) Review normalization, 6) Identify indexes.
What statement is used to create a copy of a table?
CREATE TABLE AS followed by a SELECT statement.
What does MySQL copy when using CREATE TABLE AS?
Column definitions and the data.
How can you create a partial copy of a table?
By using a search condition in the WHERE clause.
What happens if you use CREATE TABLE AS with a name that already exists?
The existing table is not automatically overwritten.
What clause is used with INSERT to specify values?
The VALUES clause.
What is the syntax rule for inserting multiple rows?
Separate each list of values with a comma.
What is a subquery?
A SELECT statement coded within another SQL statement.
How do you code a subquery in an INSERT statement?
Use a SELECT statement instead of a VALUES clause without parentheses.
When coding a subquery in a WHERE clause, are parentheses required?
Yes.
What is the Boyce-Codd normal form used to eliminate?
Transitive dependencies.
What is denormalization?
The deliberate deviation from normal forms, usually beyond the third normal form.
What is an EER model?
An enhanced entity-relationship model that represents database structure.
What is a database view?
A SELECT statement stored in the database as an object.
Do views store data themselves?
No, they refer back to the base tables.
How do you delete a view?
Use the DROP VIEW command.
What keywords allow you to automatically replace an existing view?
OR REPLACE.
What is the purpose of the DELIMITER statement in a stored procedure?
To change the default semicolon to another character to identify the end of the procedure.
What keywords define the code block within a procedure?
BEGIN and END.
What statement executes a stored procedure?
The CALL statement.
What is the purpose of IF NOT EXISTS in CREATE DATABASE?
It prevents an error if the database already exists.
What is the difference between DROP DATABASE and DROP DATABASE IF EXISTS?
DROP DATABASE throws an error if the database is missing, while IF EXISTS makes it safe to run.
What is a NOT NULL constraint?
A constraint that prevents a column from storing NULL values.
What is a UNIQUE constraint?
A constraint ensuring no two rows have the same value in that column.
Can a UNIQUE column store NULL values?
Yes, unless it is also marked NOT NULL.
What is the primary function of a UNIQUE constraint?
It ensures no two rows can have the same value in that column.
How does a UNIQUE constraint differ from a PRIMARY KEY regarding NULL values?
A UNIQUE column can store NULL unless marked NOT NULL, whereas a primary key cannot.
What is the difference between column-level and table-level constraints?
Column-level is defined inline after the column; table-level is defined separately after all columns and is required for multi-column constraints.
When is a table-level PRIMARY KEY constraint required?
When the primary key is a composite key spanning multiple columns.
Which SQL statements are used to add new rows and modify existing rows?
INSERT for new rows and UPDATE for modifying existing rows.
What are the roles of the UPDATE, SET, and WHERE clauses?
UPDATE names the table, SET defines columns and values, and WHERE specifies the condition for the rows to update.
What is the requirement for an UPDATE statement in MySQL Workbench's safe update mode?
The WHERE clause must refer to a primary or foreign key.
What is the recommended practice before executing an UPDATE statement?
Execute a SELECT statement with the same WHERE clause first to verify the correct rows are affected.
What happens if you disable safe update mode and omit the WHERE clause in an UPDATE statement?
All rows in the table will be updated.
What statement is used to remove rows from a table?
The DELETE statement.
How does CHAR storage differ from VARCHAR storage?
CHAR uses a fixed amount of storage for every value, while VARCHAR uses a varying amount based on string length.
Why are data types like telephone numbers and zip codes stored as character types instead of numeric?
They are identifiers rather than values used for mathematical calculations.
How do you prevent negative numbers from being stored in an integer column?
Include the UNSIGNED attribute.
What are fixed-point numbers?
Numbers that have a fixed number of digits to the right of the decimal point.
In a DECIMAL type, what is the difference between scale and precision?
Scale is the number of digits to the right of the decimal; precision is the total number of digits.
What is the default display format for the DATE type?
yyyy-mm-dd
In a data structure design, what do tables, columns, and rows represent?
Tables represent entities, columns represent attributes, and rows represent instances.
What is the purpose of a FOREIGN KEY constraint?
It enforces referential integrity by ensuring a column value matches an existing value in a parent table's primary key.
What error occurs if you insert a foreign key value that does not exist in the parent table?
Error Code 1452 (foreign key constraint violation).
What is the effect of the ON DELETE CASCADE action?
When a parent row is deleted, all related child rows are automatically deleted.
What is the effect of the ON DELETE SET NULL action?
When a parent row is deleted, the foreign key column in related child rows is set to NULL.
What is the purpose of the AUTO_INCREMENT attribute?
It automatically generates a unique numeric value for a column whenever a new row is inserted.
Can a subquery be used in the WHERE clause of an UPDATE or DELETE statement?
Yes, to identify the specific rows to be processed.
Which quote style is more typically used for strings in SQL?
Single quotes.
What is the purpose of an auto-incrementing integer in a database?
To automatically generate a unique ID for each new row, commonly used for primary key columns.