Database creation and selection in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query?

Given the following SQL commands:

CREATE DATABASE IF NOT EXISTS shop;
USE shop;
SHOW DATABASES LIKE 'shop';

What will be the output of the last command?

MySQL

CREATE DATABASE IF NOT EXISTS shop;
USE shop;
SHOW DATABASES LIKE 'shop';

A An empty result set

B A single row with the database name 'shop'

C A syntax error due to missing semicolon

D All databases including 'shop' and others

💡 Hint

SHOW DATABASES LIKE 'pattern' returns databases matching the pattern.

The command SHOW DATABASES LIKE 'shop' returns only databases named exactly 'shop'. Since it was created, it appears as one row.

📝 Syntax

intermediate

Which option correctly creates a database named 'library'?

Choose the correct SQL statement to create a database named 'library' only if it does not already exist.

A CREATE DATABASE IF NOT EXISTS library;

B CREATE DB IF NOT EXISTS library;

C CREATE DATABASE IF EXIST library;

D CREATE DATABASE library;

💡 Hint

The correct syntax uses IF NOT EXISTS to avoid errors if the database exists.

Option A uses the correct syntax to create the database only if it does not exist. Options B and C are invalid syntax, and A will error if the database exists.

❓ query_result

advanced

What is the result of this sequence of commands?

Consider these commands:

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
SHOW TABLES;

What will SHOW TABLES return?

MySQL

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
SHOW TABLES;

A A syntax error due to missing semicolon

B An empty list because no tables exist

C A list containing one table named 'users'

D A list of all tables in all databases

💡 Hint

SHOW TABLES shows tables in the current database.

After creating the 'users' table in 'testdb' and selecting it with USE, SHOW TABLES returns the list of tables in 'testdb', which includes 'users'.

🔧 Debug

advanced

Why does this command fail?

Given the command:

USE shop_db;

It produces an error. What is the most likely cause?

MySQL

USE shop_db;

A Incorrect database name syntax

B Missing semicolon at the end of the statement

C USE command is not supported in MySQL

D Database 'shop_db' does not exist

💡 Hint

SQL statements must end with a semicolon.

If the command includes a semicolon, the most likely cause of error is that the database 'shop_db' does not exist. USE is valid and database names can have underscores.

🧠 Conceptual

expert

Which statement about database selection is true?

Select the correct statement about the USE command in MySQL.

A USE changes the current database for the session until changed again

B USE creates a new database if it does not exist

C USE lists all available databases

D USE deletes the current database

💡 Hint

Think about what USE does to your working environment.

The USE command sets the current database for the session. It does not create, list, or delete databases.