SELECT DATABASE and RENAME DATABASE in SQL

SQL

SELECT DATABASE statement is used to switch to a specific database, allowing subsequent SQL commands to be executed within that selected database.

  • SELECT Statement:
    • A fundamental SQL command used for querying databases.
    • Allows us to retrieve data based on specified criteria.

Syntax

Now, let’s break down the syntax of the SELECT DATABASE statement:

USE database_name;
  • USE: Keyword indicating the intention to switch to a specific database.
  • database_name: Replace this with the name of the target database.

Practical Example: Creating a Sample Database

To illustrate the concepts, let’s create a simple database named “school” with two tables: “students” and “courses.”

Sample Database:

students
student_id (PK)
name
age
course_id (FK)
courses
course_id (PK)
course_name
instructor
credits

Selecting the “school” Database:

USE school;

Output:

Database changed

RENAME the Database

In SQL the following syntax is used for renaming the database:

ALTER DATABASE old_database MODIFY NAME = new_database;

Replace old_database with the current name of the database, and new_database with the desired new name.

MySQL / MariaDB:

To rename a database in MySQL or MariaDB, you can use the RENAME DATABASE statement. Note that this operation might require special privileges.

RENAME DATABASE old_database TO new_database;

PostgreSQL:

In PostgreSQL, there is no direct SQL command to rename a database. However, you can achieve this by using the pg_dump and pg_restore utilities to create a backup of the original database and restore it with a new name.

  1. Dump the original database:
pg_dump -U username -d old_database > dump.sql
  1. Create a new database with the desired name:
CREATE DATABASE new_database;
  1. Restore the dump into the new database:
psql -U username -d new_database < dump.sql

Microsoft SQL Server:

In SQL Server, you can use the ALTER DATABASE statement to rename a database.

ALTER DATABASE old_database MODIFY NAME = new_database;

SQLite:

SQLite does not have a direct SQL statement to rename a database. To achieve this, you can attach the database with a new name and then detach the original one.

ATTACH DATABASE 'new_database.db' AS newdb;
-- Copy data from olddb to newdb
DETACH DATABASE olddb;

Oracle:

To rename a database in Oracle, you can use the NID utility.

nid TARGET=SYS/oracle@old_database DBNAME=new_database