SQL - SELECT Database, USE Statement

---

To work with a database in SQL, we need to first select the database we want to work with using the USE statement. The USE statement in SQL is used to specify the name of the database we want to select. After selecting the database, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data.

To retrieve the data from the database, we can use the SELECT statement in SQL, which is used to retrieve data from one or more tables.

Syntax

Following is the syntax of the USE statement in SQL −

USE DatabaseName;

Here, the “DatabaseName” is the name of the database that we want to create.

Always the database name should be unique within the RDBMS.

Example

First of all we are creating a database in the database system using the following query −

SQL> CREATE DATABASE testDB;

We can list all the available databases using the SELECT statement as shown below −

SQL> SELECT * FROM SYS.DATABASES;
+------------+
| Database   |
+------------+
| master     |
| tempdb     |
| model      |
| msdb       |
| testDB     |
+------------+

Following query is used to select/switch the current database to <testDB> −

SQL> USE testDB;

Output

Commands completed successfully.

Once we finish switching to the database <testDB> we can perform operations such as creating a table as shown below −.

SQL> CREATE TABLE CALENDAR(MONTHS DATE NOT NULL);

Now, let us insert some records in the CALENDAR table using INSERT statements as shown in the query below −

SQL> INSERT INTO CALENDAR(MONTHS) VALUES('2023-01-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-02-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-03-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-04-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-05-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-06-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-07-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-08-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-09-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-10-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-11-01');
INSERT INTO CALENDAR(MONTHS) VALUES('2023-12-01');

Let’s verify whether the table is created or not using the SELECT statement as shown below −

SQL> SELECT * FROM CALENDAR;

Output

The table CALENDAR is successfully created.

+-------------+
| MONTHS      |
+-------------+
| 2023-01-01  |
| 2023-02-01  |
| 2023-03-01  |
| 2023-04-01  |
| 2023-05-01  |
| 2023-06-01  |
| 2023-07-01  |
| 2023-08-01  |
| 2023-09-01  |
| 2023-10-01  |
| 2023-11-01  |
| 2023-12-01  |
+-------------+

In SSMS

The following image shows that the table (CALENDAR) we created right now is stored inside the database(testDB) that we made as default database.

Selecting a non existent database

An attempt to select a non-existent database will result in an error. In the following query we are trying to switch to the database which does not exist −

SQL> CREATE DATABASE unknownDatabase;

On executing the above query, the output is displayed as follows −

Database 'unknownDatabase' does not exist. Make sure that the name is entered correctly.