- The subquery is never executed. Only the main query is executed.
- They are executed at the same time
- the main query
- the subquery
Q2. You need to export the entire database, including the database objects, in addition to the data. Which command-line tool do you use?
- mysqlexport
- mysqladmin
- mysqldump
- mysqld
Q3. You must ensure the accuracy and reliability of the data in your database. You assign some constraints to limit the type of data that can go into a table. What type of constraints are you assigning?
- row level
- database level
- column level
- function level
Q4. Which option of most MySQL command-line programs can be used to get a description of the program's different options?
- --options
- ?
- --help
- -h
Q5. MySQL uses environment variables in some of the programs and command-line operations. Which variable is used by the shell to find MySQL programs?
- DIR
- HOME
- PATH
- MYSQL_HOME
- A
1 CREATE PROCEDURE P () AS
2 BEGIN
3 END;
- B
1 CREATE PROCEDURE P ()
2 BEGIN
3 END
- C
1 CREATE PROCP
2 BEGIN
3 END;
- D
1 CREATE PROC P AS O
2 BEGIN
3 END;
Q7. If you were building a table schema to store student grades as a letter (A, B, C, D, or F) which column type would be the best choice?
- ENUM
- OTEXT
- VARCHAR
- LONGTEXT
Q8. Management has requested that you build an employee database. You start with the employee table. What is the correct syntax?
- A
CREATE TABLE employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY ON employeeID
);- B
CREATE TABLE employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY employeeID
);- C
CREATE TABLE IF EXISTS employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY (employeeID)
);- D
CREATE TABLE IF NOT EXISTS employee (
employeeID char(10),
firstName varchar(50),
lastName varchar(50),
phone varchar(20),
address varchar(50),
PRIMARY KEY (employeeID)
);Q9. You are working with the tables as shown in this diagram. You need to generate the list of customers who purchased certain car models. Which SQL clause do you use?
- LIKE
- IN
- BETWEEN
- HAVING
- USE db
- SELECT column FROM tbl
- SHOW COLUMNS FROM tbl
- SHOW TABLES
Q11. MySQL server can operate in different SQL modes, depending on the value of the sql_mode system variable. Which mode changes syntax and behavior to conform more closely to standard SQL?
- TRADITIONAL
- ANSI
- MSSQL
- STRICT
Q12. MySQL programs are a set of command-line utilities that are provided with typical MySQL distributions. MySQL is designed to be a database.
- database and programming
- user and administrator
- client and server
- syntax and objects
- INFO table;
- SHOW table;
- STRUCTURE table;
- DESCRIBE table;
Q14. MySQL uses security based on _ for all connections, queries, and other operations that users can attempt to perform.
- administrator schema
- encrypted algorithms
- user settings
- access control lists
- UPDATE
- MODIFY
- CHANGE
- ALTER
- a SQL query using partitions
- a SQL query using IS NULL
- a SQL query using a regular expression
- a SQL query using LTRIM Or RTRIM
- line break
- colon
- semicolon
- period
- DELETE
- DELETE FROM
- REMOVE
- REMOVE FROM
- GROUP BY
- WHERE
- LIMIT
- LIKE
SELECT * FROM Employees WHERE EmployeeName LIKE 'a%'
- It records in the Employees table where the value in the EmployeeName column doesn't have an "a".
- It records in the Employees table where the value in the EmployeeName column starts with "a".
- It records in the Employees table where the value in the EmployeeName column has an "a".
- It records in the Employees table where the value in the EmployeeName column ends with "a".
- a SQL query
- a SQL statement
- a database
- a table
- SQL is a standard language for retrieving and manipulating data from structured databases. MySQL is a nonrelational database management system that is used to manage SQL databases.
- SQL is a standard language for retrieving and manipulating data from structured databases. MySQL is a relational database management system that is used to manage SQL databases.
- They are not different. MySQL and SQL refer to the same thing.
- My SQL is a language, and SQL is a software application.
- SELECT * FROM movies GROUP BY name
- SELECT * FROM movies ORDER BY name
- SELECT * FROM movies ORDER TABLE by name
- SELECT * FROM movies FILTER BY name
Q24. A trigger is a database object that is associated with a table, and that activates when a particular event occurs for the table. Which three events are these?
- INSERT, UPDATE, DELETE
- CREATE, ALTER, DROP
- OPEN, FETCH, CLOSE
- DECLARE, SET, SELECT
Q25. You are working with very large tables in your database. Which SQL clause do you use to prevent exceedingly large query results?
- UNIQUE
- LIMIT
- DISTINCT
- CONSTRAINT
- 25
- 990
- 0
- 3306
- DISTINCT
- WHERE
- LIMIT
- AS
- They both refer to the same operation of deleting the table completely.
- They both refer to the same operation of clearing the table, but keeping its definition intact.
- TRUNCATE deletes table completely, removing its definition as well. DROP clears the table but does not delete the definition.
- DROP deletes table completely, removing its definition as well. TRUNCATE clears the table but does not delete the definition.
- SELECT all FROM inventory;
- FROM inventory SELECT all;
- FROM inventory SELECT *;
- SELECT * FROM inventory;
- set of triggers
- sequential id field
- minimum of three columns
- primary key
Q31. MySQL option files provide a way to specify commonly used options so that they need not be entered on the command line each time you run a program. What is another name for the option files?
- variable settings
- configuration files
- help files
- default settings
Q32. After installing MySQL, it may be necessary to initialize the _ which may be done automatically with some MySQL installation methods.
- storage engine
- user accounts
- grant tables
- data directory
Q33. You need to export the data in the customers table into a CSV file, with columns headers in the first row. Which clause do you add to your MySQL command?
- JOIN
- WITH HEADERS
- UNION
- WITH COLUMNS
Q34. One form of backup, replication, enables you to maintain identical data on multiple servers, as a _ configuration.
- remote-local
- parent-child
- master-slave
- logical-physical
- the subquery must use an aggregate function.
- the subquery must refer to the same table as the main query.
- the subquery must return a single value.
- the subquery must return at least one value.
Q36. Each time MySQL is upgraded, it is best to execute mysql_upgrade, which looks for incompatibilities with the upgraded MySQL server. What does this command do, upon finding a table with a possible incompatibility?
- it performs a table check and, if problems are found, attempts a table repair.
- it stops and notifies the server administrator that the upgrade cannot complete until the incompatibility issue are resolved.
- it provides a full report of the table specifications and the incompatibilities to the server administrator.
- it performs a table check and, if problems are found, displays the information for the server administrator to take action.
- show grants (displays the privileges and roles that are assigned to a MySQL user account or role)
- show privileges (shows the list of system privileges that the MySQL server supports)
- show access
- show user permissions
- temporary
- system
- large
- new
Q39. later versions of mysql support the native json data type for storing json documents. What is a drawback of json columns?
- inefficient for storing json documents
- cannot be indexed directly
- documents cannot be validated when stored in json columns
- cannot be normalized
- carid is the primary key for purchases
- carid is the foreign key for cars.carid
- customerid is the foreign key for customers.id
- customerid is the primary key for purchases
- cat file|mysql
- load data infile (correct if the file is already on the server)
- load data local infile (also correct but only if the file is from the client)
- extended insert statement
Q42. You are working with the tables as shown in this diagram. You need to make sure that any record added to the purchases table consists of a customerID, which already exists in the customers table, and a carID, which already exists in the cars table. You decide to use a trigger to do the validation. Which one do you use?
-
AFTER INSERT -
BEFORE INSERT -
CROSS JOIN -
IF EXISTS
IF EXISTS and CROSS JOIN are not valid for a trigger.
-
insert into cars (make, model, year) values ('Ford', 'Mustang', 2002) ('Mercedes', 'C', 2003)
-
insert into cars (make, model, year) values ('Ford', 'Mustang', 2002) values ('Mercedes', 'C', 2003)
-
insert into cars (make, model, year) extended ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)
-
insert into cars (make, model, year) values ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)
Q44. You need to make an exact copy of a table, with all columns and indexes. How can you get all of the information needed to accomplish this?
- create table
- clone table
- insert into
- show create table
Note that the question is about getting the data and not about the duplicating operation itself. And actually there is no need to run SHOW CREATE TABLE at all. To duplicate the table structure you can CREATE TABLE new_table LIKE original_table;.
- Run MySQL server as a normal user.
- Grant PROCESS or SUPER privilege to other users.
- Run MySQL server as the unix root user.
- Use the compressed protocol.
Q46. You manage a database with a table "customers". You created a temporary table also called "customers" with which you are working for the duration of your session. You need to recreate the temporary table with different specs. Which command do you need to run first?
-
create temporary table customers; -
drop temp table customers; -
drop table customers; -
drop temporary table customers;
Q47. You need to run a complex query with recursive subqueries, but without creating a stored procedure or a function. Which command or clause do you use?
- COLLATE
- UNION
- FULL JOIN
- WITH
This is exactly what WITH clause is designed for
- merge
- updatable
- temptable
- undefined
- to display your version of MySQL
- to display operating system error codes
- to display default settings that are in error
- to display storage error codes
Note: perror prints a description for a system error code or for a storage engine (table handler) error code - link
- SHOW table COLUMNS;
- SHOW COLUMNS FROM table;
- LIST table COLUMNS;
- SELECT COLUMNS FROM table;
Note: DESCRIBE tablename is a shortcut for this command
- SELECT * FROM DATABASE;
- SHOW TABLES;
- LIST TABLES;
- SELECT ALL TABLES;
- CHECK TABLE;
- CREATE TABLE;
- ANALYZE TABLE;
- OPTIMIZE TABLE;
- mysql.accounts;
- mysql.passwords;
- mysql.admin;
- mysql.user;
Q54. Management has requested that you build an employee database. You need to include each employee's current position and salary, as well as all prior positions and salaries with the company. You decide to use a one-to-many structure: an employee table with the main information such as name and address, and an employment table with position and salary history. You can use the employeeID field to connect them. What is employment.employeeID an example of?
- primary key;
- secondary key;
- foreign key;
- alternate key;
Q55. In recent versions of MySQL (8.0+), what's the correct syntax to declare a CTE (Common Table Expression)?
- WITH (SELECT id FROM users) as cte, SELECT ...
- WITH (SELECT id FROM users) as cte SELECT ...
- WITH cte as (SELECT id FROM users), SELECT ...
- WITH cte as (SELECT id FROM users) SELECT ...
- to reduce corruption in data
- to reduce storage space
- to make the system faster
- to prevent data anomalies
Note: "to make the system faster" can also be correct. For example we can calculate some heavy query in advance and store its result in some column (use it as a cache). So if "system" means "application which uses mysql" then it's correct too.
Q57. The code snippet below is used to read data from an XML file into a table. Which XML structure is _not_ supported by the statement?
LOAD XML LOCAL INFILE 'cars.xml'
INTO TABLE cars
ROWS IDENTIFIED BY `<car>`;- A
<car>
<field name="make"> Lexus </field>
<field name="model"> IS300 </field>
<field name="make"> 2016 </field>
</car>- B
<car name="make"> Dodge </car>
<car name="model"> Ram </car>
<car name="year"> 2000 </car>- C
<car make="Ford" model="Mustang" year="2002"/>- D
<car year="2010">
<make>Mercedes</make> <model> C-Class</model>
</car>Q58. You are loading data into a table. Which command can you use to make sure that all data is inserted and duplicates rows are discarded?
-
INSERT IGNORE -
INSERT UNIQUE -
INSERT INTO -
INSERT DISTINCT
- It will stop and issue an error when it encounters a row that is referenced by a row in a child table.
- It always first drops, then re-creates a new table.
- It deletes rows one by one on tables with foreign key constraints.
- It does not invoke the
DELETEtriggers associated with the table.
Note: both answers are correct - see TRUNCATE TABLE Statement in MySQL manual
Q60. You are working with the tables as shown in this diagram. You need to get the number of cars sold per the home state of each customer's residence. How can you accomplish this?
-
SELECT state, COUNT(*) FROM customers WHERE ID IN (SELECT customerID FROM purchases) GROUP BY state; -
SELECT state, COUNT(*) FROM customers c LEFT JOIN purchases p ON c.ID = p.customerID GROUP BY state; -
SELECT state, COUNT(*) FROM customers c, purchases p WHERE c.ID = p.customerID GROUP BY state; -
SELECT state, COUNT(*) FROM customers GROUP BY state;
Explanation: THe difference between 2 and 3 is that LEFT JOIN will return 1 row per customer before grouping. If replaced with RIGHT JOIN it would return the correct info.
Q61. In data migration, there is often a need to delete duplicate rows as part of data cleanup. Which statement works best?
-
DELETE DUPS -
DELETE DISTINCT -
DELETE JOIN -
DELETE WITH
-
DEFAULTvalue -
RETURNvariable -
SQLEXCEPTIONroutine -
NOT FOUNDhandler
Q63. Which type of backup includes all the changes made to the data since the last full backup was performed?
- snapshot
- logical
- differential
- incremental
Q64. You need to restore a MySQL database from a backup file. Which command-line tool do you use for the actual data import, after re-creating the database?
-
mysqld -
mysql -
mysqladmin -
mysqldump
Q65. You are importing data as JSON into a new table. You run CREATE TABLE json_data ( city JSON ); and insert rows into this table. What is the correct syntax to see the list of cities?
-
SELECT city FROM json_data; -
SELECT city->>'$.name' city FROM json_data; -
SELECT city.name city FROM json_data; -
SELECT city->'$.name' city FROM json_data;
Note: the last option is valid too but the results will be enclosed with quotation marks
Q66. If you want to use MyISAM instead of InnoDB, which option do you need to specify in the CREATE TABLE statement?
- ENGINE
- PARTITION
- STORAGE
- TABLESPACE
Q67. You are working with the table in this diagram. You want to use full-text search to find the customers who live on a street or a drive. What is the command to do that?
Table name: customers
| ID | lastname | firstname | phone | address | city | state | zip |
|---|---|---|---|---|---|---|---|
| A001 | Smith | Bob | 212-555-1212 | 1001 1st Street | New York | NY | 10001 |
| A002 | Chang | John | 213-555-5678 | 888 Rodeo Drive | Los Angeles | CA | 90210 |
| A003 | Smith | Mary | 999-999-9999 | 123 Main Street | Anytown | VA | 12345 |
| A004 | Johnson | Jack | 312-312-3120 | 1111 Chicago Avenue | Chicago | IL | 60606 |
| A005 | Lopez | Linda | 737-777-3333 | 123 Main Street | Austin | TX | 73344 |
- A
SELECT *
FROM customers
WHERE address MATCH 'Street' OR 'Drive';- B
SELECT *
FROM customers
WHERE MATCH(address) IN ('street, drive');- C
SELECT *
FROM customers
WHERE address MATCH 'Street' OR address MATCH 'Drive';- D
SELECT *
FROM customers
WHERE MATCH(address) AGAINST ('street, drive');- SHOW DATABASES;
- LIST ALL DATABASES;
- LIST DATABASES;
- SHOW DB;
- all tables, columns, data types, indexes and their relationships
- a list of entities, their relationship, and constraints
- all tables and their names, which are needed to implement the logical model
- a list of entities, their relationship, constraints, data types, and cardinalities
- INOUT
- IN
- OUT
- IN OUT
- The temporary table will be dropped when the database is restarted.
- Temporary tables can be shared among clients, which makes them more usable in group development environments.
- The temporary table will be dropped as soon as your session disconnects.
- Creating a temporary table does not require any special privileges.
-
public construct User() {} -
public User() {} -
public instance User() {} -
public init User() {}
- 2
- 4
- 8
- 16
-
DISPLAY TRIGGERS; -
SHOW TRIGGERS; -
SELECT ALL TRIGGERS; -
SELECT * FROM information_schema.triggers;
- TIMESTAMP values require more bytes for storage than DATETIME values.
- TIMESTAMP is stored without timezone, and DATETIME is stored in UTC values.
- TIMESTAMP and DATETIME are both stored without time zone.
- TIMESTAMP is stored in UTC values, and DATETIME is stored in without time zone.
-
mysqladmin flush-threads -
mysqladmin flush-tables -
mysqladmin flush-privileges -
mysqladmin flush-all
- Stored procedures are not secure, because they can be executed from the command line as the root user
- Stored procedures are secure, because the owner of the stored procedure can decide to whom access is granted
- Stored procedures are secure, because applications can be given access to stored procedures and not any underlying variables
- Stored procedures are not secure, because they can execute statements to drop tables or bulk delete data
-
SELECT * FROM customers WHERE PhoneNumber = NULL; -
SELECT * FROM customers WHERE PhoneNumber IS NOT VALID; -
SELECT * FROM customers WHERE PhoneNumber IS NULL; -
SELECT * FROM customers WHERE PhoneNumber IS UNKNOWN;
Q79. In the diagram below, the price field is declared as type DECIMAL. What would be a more efficient declaration for this field?
- FLOAT
- DECIMAL(10,2)
- NUMERIC
- DOUBLE
-
ENUM -
SET -
BIT -
CHAR
Explnation: BIT is not a string type
- one-to-many
- parent-child
- many-to-many
- many-to-one
Q82. A stored routine is a set of SQL statements stored on the server and takes form as either a procedure or a function. Which statement cannot be used inside stored routines?
-
SELECT -
USE -
SET -
DECLARE
Explanation: Both SET and DECLARE are used to create variables. Reference: MySQL STORED PROCEDURE Tutorial With Examples
Q83. When a new student is added to a new database, you want new records to be created in the related tables such as Exam, Score and Attendance. How would you accomplish this?
- trigger
- regular expression
- view
- index
Q84. In the diagram below, the ID fields are declared as type CHAR instead of INT . Which is NOT one of the possible reasons behind that decision?
- The ID field needs to include letters and not just numbers.
- You can have a consistent format across all of the tables that require ID fields.
- The ID field needs to have leading 0s, which the INT data type would truncate.
- The
CHAR(10)data type is more efficient and space-saving.
- To define queries for later reuse for the duration of the current session
- To create temporary tables that can be used to pre-select often-used result sets.
- To calculate a new single value from a result set and return it to the query parser.
- To break down complex queries and allow reuse within a query.
Explanation: CTEs do not create temporary tables, they only work within a signle query. Reference: 13.2.15 WITH (Common Table Expressions).
Q86. Which option modifier tells a program not to exit with an error if it does not recognize the option, but instead to issue a warning?
- --verbose
- --skip
- --skip-error
- --loose
Reference: 4.2.2.4 Program Option Modifiers
SELECT name FROM students WHERE name REGEXP '^to';
- all names starting with "to," such as Tommy or Tony
- all names with "to," such as Roberto and Tommy
- all names without "to," such as Samantha or Kathryn
- all names ending with "to," such as Roberto
Q88. You are working with the tables as shown in the diagram. You need to generate the list of price totals for each make and model of car, with subtotals for each make, and the grand total of all prices. Which SQL clause do you use?
- UNION
- SHOW TOTALS
- UNION ALL
- WITH ROLLUP
- Inner Join
- Natural Join
- Outer Join
- Cartesian Join
-
CREATE VIEW v1 SELECT * FROM t1 WHERE col1 > 10; -
CREATE VIEW v1 AS BEGIN SELECT * FROM t1 END; -
CREATE VIEW v1 BEGIN SELECT * FROM t1 END; -
CREATE VIEW v1 AS SELECT * FROM t1;
- encrypted algorithms
- access control lists
- user settings
- administrator schema
Q92. Inside a transaction, several operations need to be performed. What would you do if an exception happens during that transaction?
-
UNDO -
UNCOMMIT -
ROLLBACK -
REVERSE
- DATE()
- GETDATE()
- CURDATE()
- CURRENT()
-
Create table size (ENUM ('Small','Medium','Large')); -
Create table ENUM (name ('Small','Medium','Large')); -
Create table size (name: ENUM['Small','Medium','Large']); -
Create table size (name ENUM('Small','Medium','Large'));
- JSON
- CSV
- XML
- TXT
Q96. You are working with the tables as shown in this diagram. You need to generate the list of all cars, whether or not they had been sold, with the purchase date of the cars that were sold. Which statement accomplishes that?
- A
SELECT cars.*, purchases.date
FROM cars RIGHT JOIN purchases
ON cars.ID = purchases.carID;
- B
SELECT cars.*, purchases.date
FROM cars INNER JOIN purchases
ON cars.ID = purchases.carID;
- C
SELECT cars.*, purchases.date
FROM cars JOIN purchases
ON cars.ID = purchases.carID;
- D
SELECT cars.*, purchases.date FROM cars LEFT JOIN purchases ON cars.ID = purchases.carID;
- A
IF var1 THEN SET varA = var1;
ELSEIF var2 THEN SET varA = var2;
ELSEIF var3 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
- B
IF var1 = var2 THEN SET varA = var1;
ELSEIF var2 = var3 THEN SET varA = var2;
ELSEIF var3 = var4 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
- C
IF var1 = 1 THEN SET varA = var1;
ELSEIF var2 = 2 THEN SET varA = var2;
ELSEIF var3 = 3 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
- D
IF var1 = 1 THEN SET varA = var1;
ELSEIF var1 = 2 THEN SET varA = var2;
ELSEIF var1 = 3 THEN SET varA = var3;
ELSE SET varA = var4;
END IF;
- for formulas and business rules that you want to apply to columns in an SQL query
- for formulas and business rules that should be applied on a specific trigger event like on inserts
- to automatically modify the data of a table based on a query
- for reusing recurring queries
Table name: superheroes
| name | alias | power1 | power2 | power3 |
|---|---|---|---|---|
| Superman | Clark Kent | Flight | X-Ray Vision | Super Strength |
| Wonder Woman | Diana Prince | Force Fields | Reflexes | Telepathy |
| Spider-man | Peter Parker | Walcrawling | Web-making | Enhanced Senses |
| Aquaman | Arthur Curry | Underwater Breathing | Enhanced Sight | Stamina |
| Hulk | Bruce Banner | Super Strength | Radiation Immunity | Invulnerability |
- Create another table to serve as a lookup for powers with fields for code and description, as well as a junction table with superhero names and power codes.
- Add a column to this table to serve as a record identifier, and make it the primary key.
- Extend this table to have additional columns "power4," "power5," and so on, to allow additional powers for each superhero.
- Convert this table to have column called "power," and add one record for each superhero-power combination, for a total of 15 records in this example.
- 1 CREATE VIEW v1 2 SELECT * FROM t1 3 WHERE col1 > 10;
- 1 CREATE VIEW v1 2 BEGIN 3 SELECT * FROM t1 4 END
- 1 CREATE VIEW v1 AS 2 SELECT * FROM t1;
- 1 CREATE VIEW v1 AS 2 BEGIN 3 SELECT * FROM t1 4 END;
Q101. A table Item has a Boolean field endOfLife and a field makeYear of type YEAR(4). How can you set the Boolean to true for all Items that have been made before 2019?A table Item has a Boolean field endOfLife and a field makeYear of type YEAR(4). How can you set the Boolean to true for all Items that have been made before 2019?
- UPSERT Item SET endOfLife = true WHERE makeYear < 2019UPSERT Item SET endOfLife = true WHERE makeYear < 2019
- CHANGE Item SET endOfLife = true WHERE makeYear < 2019CHANGE Item SET endOfLife = true WHERE makeYear < 2019
- ALTER Item SET endOfLife = true WHERE makeYear < 2019ALTER Item SET endOfLife = true WHERE makeYear < 2019
- UPDATE Item SET endOfLife = true WHERE makeYear < 2019
Q102. Which choice is an example of an aggregate function?Which choice is an example of an aggregate function?
- NOW()NOW()
- MID()MID()
- FORMAT()FORMAT()
- COUNT()