Font size
WorksheetsSQL & MySQL REVIEW QUIZ
Total questions: 51
Worksheet time: 36mins
Which three elements of a relational database are defined in the schema? (Choose 3.)
relationships
attributes
tables
SQL keywords
aliases
In the SQL statement " SELECT genre FROM movies" what is specified by "genre"?
the table
the column
the database
the folder
What is a flat file database?
a database that stores records in a single file with no hierarchical structure
a database that requires SQL to store, manipulate, and retrieve data
a database that is used to store large amounts of complex data
a database that stores data in a sheet using a hierarchical tabular format of columns and rows
What is used to show how the tables in a database are related to one another?
ERD
CSV
DML
DDL
Which commonly used SQL statement is necessary to retrieve data from one or more tables in a database?
INSERT
TRUNCATE
CREATE
SELECT
Which structure in a table represents an individual record contained in the database?
relationship
row
column
foreign key
Which SQL statement is used to filter data in a database?
DISTINCT
ORDER BY
WHERE
FROM
What is represented by the comma-separated list of items following the SELECT command in this example?
records
aliases
columns (attributes)
table names
Refer to the exhibit. The exhibit shows the structures of the Drivers and Orders data tables for a pizza delivery service. The same driver may deliver multiple orders on an average day. What type of relationship exists between the Drivers table and the Orders table?
The two tables have a one-to-one relationship because both have DriverID as the primary key.
There is a one-to-many relationship between the two tables because one driver may have many orders, but an order can have only one driver.
There is a many-to-many relationship between the two tables because there are many drivers and many orders in the tables.
There is a one-to-one relationship between the two tables because DriverID is a common identifier for the records in both tables.
Which two SQL statements are used to create and change a database schema? (Choose two.)
ALTER
CREATE
DELETE
INSERT
SELECT
Refer to the exhibit. You need to get information about customer purchases that is stored in the two tables. You would like to see data from the OrderID, CustomerName, OrderAmount, and OrderDate columns. To obtain this information, how would you complete the following partial SQL query?
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount, Orders.OrderDate
FROM Orders
INNER JOIN _______ ON _____________ = _____________;
INNER JOIN Orders ON Orders.OrderID=Orders.CustomerID;
INNER JOIN Customers ON Customer.CustomerID=Customers.CustomerID;
INNER JOIN Orders ON Orders.CustomerID=Customers.CustomerID;
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
In combination with primary and foreign keys, which SQL statement is the most common method for joining fields from multiple tables?
BETWEEN
LIKE
JOIN
WHERE
Refer to the exhibit. Which type of SQL JOIN operation would return all the rows from Table 1 and Table 2?
LEFT JOIN
RIGHT JOIN
FULL JOIN
INNER JOIN
Which JOIN clause would join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables?
SELECT *
FROM Orders
LEFT JOIN Customers ON ____________ = ______________;
ON Customers.CustomerID=Orders.CustomerID
ON Orders.CustomerID=Customers.CustomerID
ON Orders.CustomerID=Orders.CustomerID
ON Customers.CustomerID=Customers.CustomerID
Which type of key uniquely defines each table row in a relational database?
foreign key
primary key
principal key
JOIN key
What is the function of a foreign key in a relational database table?
to define a relationship with a primary key in a different table
to uniquely identify a row within the table
to create a many-to-one relationship within the table
to add additional unique columns to the data table
Review the columns and descriptions in each table, Movie, and Award. Data from the two tables need to be combined to extract the title and cost of movies that won awards. Can a NATURAL JOIN be used to accomplish the desired result?
Movie table:
MovieID- primary key
Title- name of the movie
Date- date the movie was released
Cost- total cost of production
Award table:
AwardID- primary key
MovieID- foreign key
Category- award category
Name- name of the award
Date: day the award was received
Yes, because both tables have two common columns.
Yes, because both tables have the MovieID column.
No, because the Date column contains different data types in each table.
No, because an OUTER JOIN should be used to extract all of the information.
What is the result of the query shown in the example?
UPDATE Review
SET Comment = 'This is the new comment'
WHERE MovieId = 3456 AND AuthorName = 'PuzoFan76';
The comment made by PuzoFan76 for the movie with the ID 3456 will be modified to say, “This is a new comment.”
A new comment will be added to the movie with the ID 3456 for the author PuzoFan76.
All of the comments for the movie with the ID 3456 will be modified to say, “This is a new comment.”
A new row will be added to the table Review with the MovieId= 3456 and the AuthorName =PuzoFan76.
What are two types of data that benefit from NoSQL data storage? (Choose two.)
data with flexible formats that can change over time
high volumes of unstructured data
data easily organized in tables of columns and rows
data that conforms to a structured schema
form data requiring input validation
A collection of fields and records in a specific category
Table
Field
Record
column
Rows in database tables are called...
Tables
Records
Fields
What are some best practices when creating tables in SQL?
Using long and descriptive column names
Including NULL values in all columns
Avoiding the use of primary keys
Normalizing data to reduce redundancy
What type of backup and recovery can be performed on a database management system (DBMS)?
Incremental backup
Full backup only
Recovery without backups
Backup with no recovery option
A server with active transactions restarts after a failure. What type of recovery would you recommend?
Redo recovery
Undo recovery
Differential recovery
Partial recovery
Why is MySQL server administration important for database management?
It ensures data is always stored in plaintext format
It guarantees zero downtime during maintenance
It optimizes database performance and security
It simplifies complex SQL queries
How does MySQL server administration contribute to the scalability and performance of database systems?
By limiting the number of concurrent user connections
By reducing the storage capacity of the database
By optimizing indexes and query execution plans
By disabling caching mechanisms for faster access
Which of the following statements correctly illustrates the usage of the SQL ALTER TABLE command?
ALTER TABLE Customers ADD COLUMN Email VARCHAR(50);
UPDATE TABLE Customers SET Email = 'example@email.com';
MODIFY TABLE Customers ADD Email VARCHAR(50);
CREATE TABLE Customers MODIFY Email VARCHAR(50);
What is the primary function of the SQL ALTER TABLE command?
To delete existing tables from the database
To insert new records into a table
To modify the structure of an existing table
To perform complex queries on multiple tables
Which option correctly demonstrates using ALTER TABLE to rename a column in SQL?
ALTER TABLE Employees CHANGE COLUMN OldName NewName VARCHAR(50);
ALTER TABLE Employees MODIFY COLUMN OldName TO NewName VARCHAR(50);
ALTER TABLE Employees RENAME COLUMN OldName TO NewName VARCHAR(50)
ALTER TABLE Employees RENAME COLUMN OldName NewName VARCHAR(50);
How does the SQL ALTER TABLE command contribute to database scalability?
It automatically creates backups of modified tables
It is used to restore databases after a failure
It allows for structural changes to be backed up and recovered
It has no direct impact on backup and recovery processes
In terms of SQL best practices, when should the ALTER TABLE command be used?
Only when creating a new table
As a primary method for inserting data into a table
Whenever modifications to the table structure are necessary
Only when deleting columns from a table
Which action can be performed using the ALTER TABLE command to enhance database security?
Encrypting all data in the table
Adding or removing columns with sensitive information
Restricting access to specific rows in the table
Automatically blocking suspicious IP addresses
What precaution should be taken when using the ALTER TABLE command to maintain data integrity?
Always execute the command during peak usage hours
Ensure there are no active transactions on the table
Modify all columns in the table simultaneously
Use it only on tables with a small number of rows
How does the ALTER TABLE command impact database performance?
It significantly slows down query execution
It has no effect on database performance
It can improve performance by optimizing table structure
It increases latency by introducing unnecessary complexity
What is a primary consideration when designing a database table?
Including redundant data to speed up queries
Choosing arbitrary data types for columns
Normalizing data to minimize redundancy
Limiting the number of rows in each table
When creating a new table in SQL, what is the purpose of defining primary keys?
To allow NULL values in the primary key columns
To enforce data uniqueness and provide fast data retrieval
To restrict access to the table
To increase storage space
Which SQL command is used to import data from a CSV file into a database table?
LOAD DATA
IMPORT TABLE
INSERT INTO
COPY TABLE
What type of backup includes only the data that has changed since the last backup?
Full backup
Differential backup
Incremental backup
Redo backup
Which action helps in cleaning data to ensure consistency and accuracy?
Adding duplicate entries
Removing NULL values
Mixing data types in columns
Ignoring data anomalies
When altering a table, what is a primary consideration to ensure data integrity?
Changing column data types frequently
Dropping all constraints before making changes
Performing alterations during peak usage hours
Backing up data before making alterations
What is the purpose of altering a database table?
To decrease database performance
To remove all existing data
To modify the structure or properties of the table
To limit access to the table
Which MySQL feature allows horizontal partitioning of tables across multiple servers to distribute the load evenly?
Clustering
Partitioning
Replication
Sharding
Why is MySQL server administration important for database management?
It ensures compatibility with other database management systems.
It automates data entry processes.
It helps optimize database performance and ensures its availability.
It primarily focuses on front-end user interface design.
How does MySQL server administration contribute to data integrity and reliability?
By encrypting all data stored in the database.
By automatically detecting and correcting errors in data entries.
By implementing backup and recovery strategies to prevent data loss.
By providing real-time data analytics tools.
What are the security implications of inadequate MySQL server administration?
Increased risk of unauthorized access, data breaches, and data loss.
Enhanced performance and scalability of the database system.
Improved compatibility with third-party applications.
Better data visualization and reporting capabilities.
How does MySQL server administration contribute to the scalability and performance of database systems?
By limiting the number of concurrent user connections.
By enforcing strict data validation rules.
By implementing techniques such as partitioning and sharding to distribute data and workload efficiently.
By focusing solely on data storage without optimizing query execution.
What is the purpose of locking in MySQL databases?
To prevent unauthorized access to the database.
To ensure data consistency and integrity during concurrent access.
To optimize query performance by caching frequently accessed data.
To automate routine maintenance tasks.
What strategies can be employed to minimize locking contention in a MySQL database?
Increasing the number of locks allowed per transaction.
Optimizing queries to reduce the duration of transactions.
Enforcing strict data validation rules.
Disabling locking mechanisms altogether.
Explain the difference between implicit and explicit locking in MySQL.
Implicit locking is automatically applied by MySQL during data retrieval operations, while explicit locking requires manual commands to lock specific resources.
Implicit locking requires explicit commands to lock resources, while explicit locking is automatically applied by MySQL during data retrieval operations.
Both implicit and explicit locking require manual commands to lock specific resources.
There is no difference between implicit and explicit locking in MySQL.
What are the potential consequences of long-held locks in a MySQL database?
Improved data consistency and integrity.
Reduced concurrency and performance degradation.
Enhanced security against unauthorized access.
Increased scalability and fault tolerance.
How can you use MySQL Workbench to analyze and optimize locking operations in a database?
By executing the OPTIMIZE LOCKS command in the SQL Editor.
By configuring custom locking profiles in the Performance Dashboard.
By analyzing the Performance Schema tables related to locking.
By adjusting the locking settings in the Server Administration section.
