SQL - CREATE Database

You are writing an automated deployment script that sets up a database named inventory_system. If you run the command CREATE DATABASE inventory_system; on a server where that database already exists, the script will crash with an error.

To prevent this, you should use the following syntax:

CREATE DATABASE IF NOT EXISTS inventory_system;

True or False: This command will skip the creation process if the database is already there, allowing your script to continue without failing.

You are tasked with creating a database for a high-security logging system. The lead architect suggests naming the database Select, as it will store logs specifically for selection queries. You attempt to run the following code in your SQL editor:

CREATE DATABASE Select;

Error Detected: The system returns a syntax error near the word 'Select'.

Based on standard SQL naming conventions, why did this command fail, and what is the most professional way to fix it without changing the name?

Your company is expanding to the Japanese market. You need to create a database named tokyo_sales that can store Kanji characters and perform case-insensitive searches. Examine the requirements table below:

Which of the following clauses would be valid parts of a single CREATE DATABASE statement to satisfy these technical requirements? (Select all that apply)

You have successfully executed three separate creation commands:

1. CREATE DATABASE finance_2023;
2. CREATE DATABASE finance_2024;
3. CREATE DATABASE employee_records;

You now need to create a table. However, a SQL server can have dozens of databases. If you do not specify which one you are working in, the server won't know where to place your data.

What is the full SQL statement required to tell the server that all subsequent commands should be executed inside the finance_2024 container?

In a production environment, automation scripts often "clean up" old environments before rebuilding them. You are reviewing a script that contains the following line:

DROP DATABASE IF EXISTS staging_env;

True or False: If the database staging_env does not exist on the server, this command will produce a "Critical Error" and stop the entire script from running.

You are establishing a naming convention for your team's local development databases. According to SQL standards (specifically for MySQL/PostgreSQL), which of the following database names would require special quoting (backticks or double quotes) to be valid? (Select all that apply)

A Senior DBA asks you to set up a new database for a project and ensure it is ready for the development team to add tables.

Arrange the following steps in the correct logical sequence to complete this task safely:

1. Execute USE project_alpha; to set the active context.
2. Execute SHOW DATABASES; to confirm the creation was successful.
3. Execute CREATE DATABASE IF NOT EXISTS project_alpha; to build the container.

You create a database named ClientData on a Windows server. Later, you migrate the entire server to a Linux (Ubuntu) environment.

True or False: On many Linux installations, SQL database names are case-sensitive because they correspond to actual folders on the disk. Therefore, a query trying to USE clientdata; (all lowercase) might fail even though it worked on Windows.

You need to find out exactly when a database was created and what its default character set is. While SHOW DATABASES; gives you a list of names, it doesn't provide this deep "metadata."

Which system-defined "virtual database" holds read-only tables containing all the technical details about every database on your server?

If you execute the command CREATE DATABASE app_db; without specifying a CHARACTER SET or COLLATE clause, the database will automatically be created using the Server’s Default settings.

• True or
• False

You are writing a "Setup" script for a new software installation. You want to ensure that every time the script runs, it starts with a completely fresh, empty database named test_suite, even if one existed before.

Arrange the following commands in the correct order to achieve a "Fresh Reinstall" effect:

1. CREATE DATABASE test_suite;
2. DROP DATABASE IF EXISTS test_suite;
3. USE test_suite;

In many SQL dialects, especially MySQL and MariaDB, the terms "Database" and "Schema" are technically synonymous. This means you can often use the keyword SCHEMA in place of DATABASE.

Examine the following command intended to create a database named inventory_v2:

CREATE SCHEMA inventory_v2;

If you execute this command, what will be the name of the resulting container created on the server? (Please provide only the name, no extra text)

A developer attempts to run a setup script to initialize a new environment, but the CREATE DATABASE operation fails repeatedly. Look at the server status table below:

Which of the following are valid technical reasons why the command CREATE DATABASE app_test; would fail based on this context? (Select all that apply)

You are using a SQL client that supports transactions. You decide to run the following block of code:

START TRANSACTION;
CREATE DATABASE temporary_records;
ROLLBACK;

True or False: Because you issued a ROLLBACK command, the temporary_records database will be "undone" and will no longer exist on the server.

You have inherited a database named legacy_system from a previous developer. You need to know exactly which Character Set and Collation were used when it was first created, but you don't have the original script.

What is the specific three-word SQL command used to display the original CREATE DATABASE statement that was used to generate legacy_system?

You are writing a Python script to automate the deployment of a local development environment. You want the script to be "Idempotent" (it can be run multiple times without causing errors).

Arrange the following SQL commands in the order they should appear in your script to ensure a clean, active database is ready for use:

1. CREATE DATABASE IF NOT EXISTS dev_sandbox;
2. USE dev_sandbox;
3. DROP DATABASE IF EXISTS dev_sandbox;

When defining a specific encoding during the creation of a database, you must follow a strict syntax pattern. Fill in the missing one-word keyword in the statement below:

CREATE DATABASE research_db 
DEFAULT __________ SET utf8mb4;

A developer is complaining that when they save an Emoji (like 🚀) into their new database, it shows up as a question mark ?. You check their creation script:

CREATE DATABASE mobile_app CHARACTER SET latin1;

Which of the following CHARACTER SET options would fix this issue and allow for full Unicode/Emoji support? (Select all that apply)

When you execute the command CREATE DATABASE new_project;, Python or your SQL editor will automatically switch your current session to that new database so you can start creating tables immediately.

• True or
• False

You are deploying a production database for a global banking app. You must ensure the database is created with the most secure settings and verified before the application goes live.

Arrange the steps in the correct logical order:

1. Execute SHOW DATABASES; to ensure the name is visible to the system.
2. Execute CREATE DATABASE IF NOT EXISTS bank_prod CHARACTER SET utf8mb4;
3. Execute USE bank_prod; to prepare for table migrations.

In almost every SQL installation (MySQL, MariaDB, PostgreSQL), there is a default, "Superuser" account that has the highest level of privileges, including the ability to create and drop any database.

What is the four-letter name of this default administrative user?

A database administrator (DBA) is auditing the server to find all databases that were created with a specific character set. Instead of using SHOW, they query the information_schema.SCHEMATA table.

Look at the partial results of their query:

Based on the table above, which SQL statement was likely used to generate this specific list of metadata?

You are performing a code review on several setup scripts. Which of the following SQL statements are syntactically correct and would successfully result in the creation of a database (or schema) in a standard MySQL/MariaDB environment? (Select all that apply)

-- Option 1
CREATE DATABASE `my-data`;

-- Option 2
CREATE SCHEMA IF NOT EXISTS 'sales_records';

-- Option 3
CREATE DATABASE travel_app CHARACTER SET = utf8mb4;

-- Option 4
CREATE SCHEMA social_net DEFAULT COLLATE utf8mb4_bin;

When you execute a command like CREATE DATABASE cloud_storage;, the SQL engine typically communicates with the operating system to create a new physical directory (folder) on the hard drive where all future table files for that database will be stored.

# Typical Linux path representation
/var/lib/mysql/cloud_storage/

True or False: Deleting this folder manually from the operating system's file explorer is the recommended and safest way to remove a database from the SQL server.

You attempted to create a database named Client_Data, but you accidentally typed Cleint_Data (a typo). You now have the wrong database on the server and need to fix it.

Arrange the following commands in the correct logical order to fix the typo and begin working in the correct database:

1. CREATE DATABASE Client_Data;
2. USE Client_Data;
3. DROP DATABASE Cleint_Data;

You are creating a database with a specific collation to handle sorting:

CREATE DATABASE research_archive 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

In the collation name utf8mb4_0900_ai_ci, the suffix "ci" is a very important technical indicator. What two-word phrase does "ci" stand for?

You are an administrator setting up a restricted environment for a new developer. You need to give them a specific database and the rights to use it.

Arrange the following administrative steps in the correct logical order:

1. CREATE DATABASE dev_workspace; (Create the container)
2. GRANT ALL PRIVILEGES ON dev_workspace.* TO 'dev_user'; (Assign rights)
3. CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'password123'; (Create the person)

A server has 500 databases. You only want to see the ones that start with the word "test". You decide to use the following command:

SHOW DATABASES LIKE 'test%';

If the server contains the following databases: test_ads, production_db, test_users, and beta_app, how many database names will be returned by this command?

Examine the comparison table below regarding database properties:

Which of the following correctly identifies Property A and Property B?