SQL provides two ways to rename an MySQL table. You can use either SQL RENAME TABLE or ALTER TABLE statement to change a table name in MySQL RDBMS.

The SQL RENAME TABLE Statement

You can change a MySQL table name using SQL RENAME TABLE statement.

Syntax

Following is the syntax of the SQL RENAME TABLE Statement −

RENAME TABLE table_name TO new_table_name;

Where, table_name is the current name of an existing table and new_table_name is the new name of the table.

Example: SQL RENAME TABLE Statement

Let us with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert few records into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table will be created as follows −

Following SQL Query changes the name of the CUSTOMERS table to BUYERS −

RENAME TABLE CUSTOMERS to BUYERS;

Verification

Once you change the name of a table, you can start using the new table name in your SQL queries.

SELECT * FROM BUYERS;

If table name got changed successfully, then it should list down all the records which were available in CUSTOMERS table.

The SQL ALTER TABLE Statement

The ALTER TABLE statement can be used to change or modify the structure of an existing table i.e. using this statement you can add/delete columns, create/destroy indexes, change the of the existing columns, rename the columns and, we can even rename the table.

Syntax

Following is the syntax of the SQL ALTER TABLE statement to rename an existing table −

ALTER TABLE table_name RENAME [TO|AS] new_table_name

Example: SQL ALTER TABLE Statement

Following SQL ALTER TABLE statement will change the table name from BUYERS to CUSTOMERS.

ALTER TABLE BUYERS RENAME TO CUSTOMERS;

Verification

Once you change the name of the table to CUSTOMERS, you can start using this name in your SQL queries.

SELECT * FROM CUSTOMERS;

This will produce the following result:

Renaming a Table in SQL Server

There isn”t a query in SQL Server that can rename a table directly. However, it does give you access to a stored procedure called sp_rename that enables you to rename a table.

The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints.

Syntax

Following is the basic syntax to rename a table in SQL Server −

EXEC sp_rename ''old_table_name'', ''new_table_name''

Here, you must ensure that old table name is present in the database and that new table name does not already exist. Otherwise, it will issue a warning. Second important point is to make sure that the table is not locked and there is no active transaction involving this table.

Example: Renaming a Table in SQL Server

Assume we already have the CUSTOMERS table in our database. Now, we are going to rename this table from CUSTOMERS to WORKERS, using the following query −

EXEC sp_rename ''CUSTOMERS'', ''WORKERS

Output

The result obtained is as shown below −

Completion time: 2023-08-15T19:21:49.1144163+05:30

Verification

We can verify whether the changes are reflected by retrieving its contents using the SELECT statement as follows −

SELECT * FROM WORKERS;

This will list down all the records available in WORKERS table as follows −

Because you have renamed the table to WORKERS so if you try to get the details by using the old table name, it will throw an error showing that the table does not exist.

Rules to be followed while renaming tables

When renaming tables in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues.

• Avoid renaming system tables − System tables are tables that are created and used by the database management system itself. Renaming these tables can cause issues with the functioning of the database system, so it is generally not recommended to rename system tables.

• Update all references to the table − After renaming a table, any , , triggers, or other database objects that reference the table will need to be updated to use the new name of the table. Failure to update these references can result in errors or issues with the functioning of the database system.

• Test thoroughly − Before renaming a table in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the table have been updated correctly and that the database system continues to function as expected.

• Use a consistent naming convention − It is a good practice to use a consistent naming convention for tables and other database objects to make it easier to understand and maintain the database system. If you need to rename a table, consider following the same naming convention that you have used for other tables in the database.

• Backup the database − Before renaming a table, it is recommended to create a to ensure that you have a restore point; in case anything goes wrong during the renaming process.

There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table, you can create a clone of the existing table.

SQL Cloning Operation allows to create the exact copy of an existing table along with its definition. There are three types of cloning possible using SQL in various RDBMS; they are listed below −

• Simple Cloning
• Shallow Cloning
• Deep Cloning

Simple Cloning in MySQL

Simple cloning operation creates a new replica table from the existing table and copies all the records in newly created table. To break this process down, a new table is created using the CREATE TABLE statement; and the data from the existing table, as a result of SELECT statement, is copied into the new table.

Here, clone table inherits only the basic column definitions like the NULL settings and default values from the original table. It does not inherit the indices and AUTO_INCREMENT definitions.

Syntax

Following is the basic syntax to perform simple cloning in MySQL−

CREATE TABLE new_table SELECT * FROM original_table;

Example

Consider the following existing CUSTOMERS table which will be cloned in next new few steps.

Now let”s use the following SQL statement to create NEW_CUSTOMERS table using the existing table CUSTOMERS.

CREATE TABLE NEW_CUSTOMERS SELECT * FROM CUSTOMERS;

Output

The output is displayed as −

Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify whether the table has been cloned successfully, we can use the following SELECT query −

SELECT * FROM NEW_CUSTOMERS;

If NEW_CUSTOMERS table is created successfully, then it should get all the records which are available in CUSTOMERS table.

Shallow Cloning in MySQL

Shallow cloning operation creates a new replica table from the existing table but does not copy any data records into newly created table, so only new but empty table is created.

Here, the clone table contains only the structure of the original table along with the column attributes including indices and AUTO_INCREMENT definition..

Syntax

Following is the basic syntax to perform shallow cloning in MySQL RDBMS −

CREATE TABLE new_table LIKE original_table;

Example

Following is an example to create a shallow clone copy of the existing table CUSTOMERS.

CREATE TABLE SHALL_CUSTOMERS LIKE CUSTOMERS;

Output

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

Verification

To verify whether the table has been cloned successfully, we can use the following DESC table_name query −

DESC SHALL_CUSTOMERS;

This will display the following information about the SHALL_CUSTOMERS table which is just a replica of CUSTOMERS table −

Deep Cloning in MySQL

Deep cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the contents from existing table and all the attributes including indices and the AUTO_INCREMENT definitions.

Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table.

Syntax

Following is the basic syntax to perform deep cloning in MySQL RDBMS −

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Example

Following is an example to create a deep clone copy of the existing table CUSTOMERS. First step is to create a shallow clone of the existing table.

CREATE TABLE DEEP_CUSTOMERS LIKE CUSTOMERS;

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

Now second step is to copy all the records from the CUSTOMERS table to DEEP_CUSTOMERS.

INSERT INTO DEEP_CUSTOMERS SELECT * FROM CUSTOMERS;

Output

The output is displayed as −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify whether the table has been cloned successfully, we can use the following SELECT query −

SELECT * FROM DEEP_CUSTOMERS;

If DEEP_CUSTOMERS table is cloned successfully, then it should get all the records which are available in CUSTOMERS.

Table Cloning in SQL Server

However, there is no direct way to fully clone a table in an SQL server. However, we have some work around to handle the situation.

SELECT…INTO STATEMENT

MS SQL Server can make use of the SELECT…INTO statement to create a new table and copies the data from an existing table into it. However, this command only copies the data and not the definition of it, thus, omitting constraints, indexes etc., if any. They need to be added separately if one wishes to have the exact same structure of the original table in their new table.

You can use the SELECT…INTO command to copy a table within the same database as well as across different databases.

Syntax

Following is the basic syntax of the SELECT…INTO statement −

SELECT * INTO new_table FROM original_table;

The above SQL command will create a table new_table using the structure of original_table and then it will copy all the data from original_table to new_table.

Example

Consider the following existing CUSTOMERS table which will be cloned in this section.

Now, if you want to clone the data of this table into a new table NEW_CUSTOMERS, let”s use the following SQL query as shown below −

SELECT * INTO NEW_CUSTOMERS FROM CUSTOMERS;

Output

The output will be displayed as −

(7 rows affected)

Verification

To verify whether all the data has been copied into the new table NEW_CUSTOMERS, we shall use the SQL SELECT statement as follows −

SELECT * FROM NEW_CUSTOMERS;

If NEW_CUSTOMERS table is created successfully, then it should get all the records which are available in CUSTOMERS table.

SQL provides command to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and cumbersome process.

The SQL TRUNCATE TABLE Statement

The SQL TRUNCATE TABLE command is used to empty a table. This command is a sequence of DROP TABLE and CREATE TABLE statements and requires the DROP privilege.

You can also use DROP TABLE command to but it will remove the complete table structure from the database and you would need to re-create this table once again if you wish you store some data again.

Syntax

The basic syntax of a TRUNCATE TABLE command is as follows.

TRUNCATE TABLE table_name;

Example

First let”s create a table CUSTOMERS which can store the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table will be created as follows −

Following SQL TRUNCATE TABLE CUSTOMER statement will remove all the records of the CUSTOMERS table −

TRUNCATE TABLE CUSTOMERS;

Verification

Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as shown in the code block below −

SELECT * FROM CUSTOMERS;

Following will be the output −

Empty set (0.00 sec)

TRUNCATE vs DELETE

Even though the TRUNCATE and DELETE commands work similar logically, there are some major differences that exist between them. They are detailed in the table below.

TRUNCATE vs DROP

Unlike TRUNCATE that resets the table structure, completely frees the table space from the memory. They are both Data Definition Language (DDL) operations as they interact with the definitions of database objects; which allows the database to automatically commit once these commands are executed with no chance to roll back.

However, there are still some differences exist between these two commands, which have been summarized in the following table −

There can be several reasons to rename a database name. One of the reasons could be to avoid naming conflicts or to separate different types of data into different databases. Another reason can be to arrange them in an organized way which makes them more descriptive and easier to manage.

The ALTER DATABASE…MODIFY Statement

The ALTER DATABASE…MODIFY statement in SQL is used to rename the name of an existing database name in SQL Database Server. Please note that this command does not work in MySQL database.

Syntax

Following is the syntax of the ALTER DATABASE…MODIFY command −

ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName;

Example

Following is the SQL command in SQL Server to rename the database testDB to tutorialsDB:

ALTER DATABASE testDB MODIFY NAME = tutorialsDB ;

Rename Database using Dump and Reimport

If you are willing to rename a database name in , then simple way is to dump the complete database in an SQL file and then re-import it into a new database. This is three step process which we will follow in this tutorial:

Step 1 – Dump Old Database

Consider you want to rename testDB database to tutorialsDB. So first we will dump it in a simple SQL file using MySQL mysqldump command. This operation will be performed from command line and will require a database user name and password, preferably admin privilege.

$ mysqldump -u username -p"password" -R testDB > testDB.sql

We are using the -p flag immediately followed by our password to connect to the database with no space between. The -R is required to tell mysqldump to copy stored procedures and functions along with the normal data from the database.

Step 2 – Create New Database

Next step is to using mysqladmin prompt command as follows:

$ mysqladmin -u username -p"password" create tutorialsDB;

Step 3 – Import Old Database

The final step is to import old database into new database as follwing:

$ mysql -u username -p"password" tutorialsDB < testDB.sql;

Step 4 – Verification (Optional)

Now you can verify the changes by listing down all the available databases:

SHOW DATABASES;

Output

The output will be displayed as −

Step 5 – Verification (Optional)

Once you are satisfied with your changes, you can delete your old database as follows:

DROP DATABASE testDB;

Rename Database in SQL using RENAME DATABASE…TO (obsoleted)

SQL provides a simple RENAME DATABASE…TO statement to rename an existing database. If you want to rename a database, make sure there is no active transaction in progress otherwise the complete operation might halt once you rename the database.

Note: The RENAME DATABASE…TO is obsoleted.

Syntax

Following is the syntax of the RENAME DATABASE…TO statement −

RENAME DATABASE OldDatabaseName TO NewDatabaseName;

Example

Before renaming a database, let us list down all the available databases −

SHOW DATABASES;

The output will be displayed as −

Now, issue the following command to rename the database testDB to tutorialsDB:

RENAME DATABASE testDB TO tutorialsDB;

There used to be a simple RENAME DATABASE command in older versions of MySQL which was intended to rename database but RENAME DATABASE command has been removed from all newer versions to avoid security risks.

There are several instances when you need to retrieve a list of tables from your database. This could be done for testing purposes, to identify any existing tables before adding or removing any, or for any other reason. This tutorial will discuss how we can list down all the table in MySQL, SQL Server and Oracle using simple SQL commands.

MySQL – Listing Tables

You can use SQL SHOW TABLES statements in MySQL to list down all the tables available in a selected database.

Syntax

Following is the syntax to list all the tables in SQL in MySQL −

SHOW TABLES;

Example

Following is an example which will list down all the tables from a testDB database.

USE testDB;

SHOW TABLES;

This will display the following output depending on the number of tables available in your database.

SQL Server – Listing Tables

SQL Server does not provide SHOW TABLE command in an SQL Server. Instead, we can use the “SELECT” statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database −

• sys.tables

• information_schema.tables

• sysobjects

The SYS.TABLES View

Following is the syntax to list down all the tables in SQL using the SYS.TABLES view −

SELECT * FROM SYS.TABLES;

Following is the output of the above query −

The INFORMATION_SCHEMA.TABLES View

Following is the syntax to list down all the tables in SQL using the INFORMATION_SCHEMA.TABLES view −

SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES;

Following is the output of the above query −

The SYSOBJECTS View

You can use SYSOBJECTS view to retrieve the information of all the objects created in SQL Server database, including stored procedures, views, system tables and user-defined tables. Following is the basic syntax of using sysobjects view −

SELECT name, id, xtype FROM sysobjects WHERE xtype = ''U

Click here to see the different value of xtype

Value	Meaning
AF	Aggregate function (CLR)
C	CHECK constraint
D	Default or DEFAULT constraint
F	FOREIGN KEY constraint
L	Log
FN	Scalar function
FS	Assembly (CLR) scalar-function
FT	Assembly (CLR) table-valued function
IF	In-lined table-function
IT	Internal table
P	Stored procedure
PC	Assembly (CLR) stored-procedure
PK	PRIMARY KEY constraint (type is K)
RF	Replication filter stored procedure
S	System table
SN	Synonym
SQ	Service queue
TA	Assembly (CLR) DML trigger
TF	Table function
TR	SQL DML Trigger
TT	Table type
U	User table
UQ	UNIQUE constraint (type is K)
V	View
X	Extended stored procedure

This will produce following result −

Oracle – Listing Tables

There are following three SQL SELECT statements which you can use to list down the tables available in Oracle.

Listing ALL Tables

Following is the SQL SELECT statement which will list down all the available tables in an Oracle Database.

SELECT owner, table_name FROM ALL_TABLES

Listing DBA Tables

Following is the SQL SELECT statement which will list down all the DBA related tables in an Oracle Database.

SELECT owner, table_name FROM DBA_TABLES

Listing USER Tables

Following is the SQL SELECT statement which will list down all the USER created tables in an Oracle Database.

SELECT owner, table_name FROM USER_TABLES

Listing ALL Views

Following is the SQL SELECT statement which will list down all the views available in an Oracle Database.

SELECT view_name FROM ALL_VIEWS;

This tutorial will teach you how to use SQL to create tables in RDBMS. We use CREATE TABLE command to create a Table in a Database.

In RDBMS, Database tables are used to store the data in the form of some structures (fields and records). Here, a field is a column defining the type of data to be stored in a table and record is a row containing actual data. In simple words, we can say a Table is a combination of rows and columns.

provides various queries to interact with the data in a convenient way. We can use SQL statements to create and delete tables, inserting, updating and deleting data in these tables.

For a more detail on different concepts related to RDBMS please check tutorial.

The SQL CREATE TABLE Statement

SQL provides the CREATE TABLE statement to create a new table in a given database. An SQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column”s data type. Note that each table must be uniquely named in a database.

Syntax

CREATE TABLE statement is used to create a new table in a database. −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Here are the key points-

• CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.
• The column parameters (e.g. column1, column2, column3, etc.) specify the names of the columns of the table.
• The datatype parameter specifies the type of data the column can hold (e.g. integer, varchar, string, etc.).
• PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.

Example: Creating Table in SQL

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here are the key points-

• The following code block is an example, which creates a CUSTOMERS table with column name ID, NAME, AGE, ADDRESS and, SALARY and ID as a primary key.
• NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table.

Verification

Once your table is created, you can check if it has been created successfully or not. You can use SQL DESC table_name command to list down the description of the table as follows:

DESC CUSTOMERS;

This will display the structure of the table created: column names, their respective data types, constraints (if any) etc.

Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers.

SQL CREATE TABLE IF NOT EXISTS

Consider a situation where you will try to create a table which already exists, in such situation will throw the following error.

ERROR 1050 (42S01): Table ''CUSTOMERS'' already exists

So to avoid such error we can use SQL command CREATE TABLE IF NOT EXISTS to create a table.

Syntax

Following is the basic syntax of a CREATE TABLE IF NOT EXISTS statement −

CREATE TABLE IF NOT EXISTS table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example: Creating Table if Not Exists

The following SQL command will create the CUSTOMERS table only when there is no table exists with the same name otherwise it will exit without any error.

CREATE TABLE IF NOT EXISTS CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Creating a Table from an Existing Table

Instead of creating a new table every time, one can also copy an existing table and its contents including its structure, into a new table. This can be done using a combination of the CREATE TABLE statement and the SELECT statement. Since its structure is copied, the new table will have the same column definitions as the original table. Furthermore, the new table would be populated using the existing values from the old table.

Syntax

The basic syntax for creating a table from another table is as follows −

CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2...columnN]
FROM EXISTING_TABLE_NAME
WHERE Condition;

Here, column1, column2… are the fields of the existing table and the same would be used to create fields of the new table.

Example: Creating Table from an Existing Table

Following is an example, which would create a table SALARY using the CUSTOMERS table and having the fields customer ID and customer SALARY −

CREATE TABLE SALARY AS
SELECT ID, SALARY
FROM CUSTOMERS;

This will create a new table SALARY which will have the following structure −

To work with a database in SQL, we need to first select the database we want to work with. After selecting the database, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data.

The USE DATABASE Statement

The SQL USE DATABASE statement is used to select a database from a list of databases available in the system. Once a database is selected, we can perform various operations on it such as , , updating data, and .

Syntax

Following is the syntax of the USE DATABASE statement in SQL −

USE DatabaseName;

Here, the DatabaseName is the name of the database that we want to select. The database name is always unique within the RDBMS.

Example

First of all we will create a database using the following SQL CREATE DATABASE query −

CREATE DATABASE testDB;

Now, we can list all the available databases as follws −

SHOW DATABASES;

The output will be displayed as −

Example: Select/Switch Database

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

USE testDB;

Output

Database changed

Once we finish switching to the database testDB we can perform operations such as creating a table, and inserting data in that table as shown below −.

CREATE TABLE CALENDAR(MONTHS DATE NOT NULL);

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

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-12-01'');

Let”s verify the operation by listing all the records from CALENDAR table using SQL SELECT statement as shown below −

SELECT * FROM CALENDAR;

Output

The output will be displayed as −

Selecting a Non Existing 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 −

Example

USE unknownDatabase;

Output

On executing the above query, the output will be displayed as −

ERROR 1049 (42000): Unknown database ''unknownDatabase''

Many a times you face a situation where you need to list down all the available databases. MySQL provides a convenient way to list down all the databases using SHOW DATABASES command where as there is no particular command in MS SQL Server to show or list the databases but, you can use the SELECT…FROM command as a work around list down available databases.

List Databases using SQL

The SQL SHOW DATABASES statement is used to list down all the available databases in MySQL database. You can use SHOW SCHEMAS as an alternate command for SHOW DATABASES.

Syntax

Following is the syntax of SQL SHOW DATABASES to list down all the available databases in MySQL −

SHOW DATABASES [LIKE ''pattern'' | WHERE expr] ;

We can use LIKE or WHERE clause along with SHOW DATABASES to filter out a list of databases.

Example

Following is an example to list down all the available databases.

SHOW DATABASES;

The output will be displayed as follows. This output depends on the number of databases available in the system −

Following is an example to list down all the databases whose name starts with test.

SHOW DATABASES LIKE ''test%

The output will be displayed as follows −

The SHOW SCHEMAS Statement

You can use the SHOW SCHEMAS statement as an alternate for the SHOW DATABASES statement.

Syntax

Following is the syntax of the SQL SHOW SCHEMAS statement to list down all the available databases in MySQL −

SHOW SCHEMAS [LIKE ''pattern'' | WHERE expr] ;

We can use LIKE or WHERE clause along with SHOW SCHEMAS to filter out a list of databases.

Example

Following is an example to list down all the available databases.

SHOW SCHEMAS;

The output will be displayed as follows. This output depends on the number of databases available in the system −

Following is an example to list down all the databases whose name starts with test.

SHOW SCHEMAS LIKE ''test%

The output will be displayed as follows −

The SELECT…FROM Statement

If you are working with MS SQL Server then you can use the SELECT…FROM statement to list down all the available databases as shown below.

SQL> SELECT * FROM sys.databases;

Output

If we execute the above query, it returns a table that lists down all the databases and associated information about the databases.

The EXEC sp_databases Statement

If you are using MS SQL Server then you can use the following EXEC sp_databases statement to list down all the databases −

SQL> EXEC sp_databases;

Output

This will display the same result as we got from SELECT…FROM statement.

What are SQL Data types?

An SQL data type refers to the type of data which can be stored in a column of a database table. In a column, the user can store numeric, string, binary, etc by defining data types. For example integer data, character data, monetary data, date and time data, binary strings, and so on.

While creating a in a database, we need to specify following two attributes to define a table column:

• Name of the column
• Data type of the column

A database table”s column defines the data, whereas database table rows populate data into the table.

For example, if you want to store student name in a column then you should give column name something like student_name and it”s data type will be char(50) which means it can store a string of characters up to 50 characters.

The data type provide guidelines for to understand what type of data is expected inside each column, and hence, prevents the user from entering any unexpected or invalid data in a column. For example, if we want a column to store only integer values, we can specify its data types as INT. SQL will show an error if any other value apart from an integer is inserted into that particular column.

Different RDBMS supports different type of data types to define their tables. This SQL Data types tutorial will list down different data types available in MySQL, Oracle, MS SQL Server, and MS Access databases.

Defining a Data Type

SQL Data types are defined during the creation of a table in a database. While creating a table, it is required to specify its respective data type and size along with the name of the column.

Following is the syntax to specify a data type in MySQL −

CREATE TABLE table_name(column1 datatype, column2 datatype....)

Let us look at an example query below to understand better.

CREATE TABLE Customers (Name VARCHAR (25), Age INT);

In the above SQL query, we are creating a table Customers. And since the Name column only stores string values, we are specifying its data type as “VARCHAR”. The VARCHAR data type represents string values in SQL. Similarly, we define the Age column with the integer data type, “INT”.

When we assign the appropriate data type to a column, we can make efficient use of memory by allocating only the amount of system memory required for the data in the relevant column.

Types of SQL Data Types

There are three main types of SQL data types available in any RDBMS. They are listed below −

• String
• Numeric
• Date and Time

Data Types in MySQL, SQL Server, Oracle, and MS Access Databases

Let”s discuss the data types in MySQL, SQL Server, Oracle (PL/SQL) and MS Access Databases in detail. All the three main types (String, Numeric, Date and Time) of SQL data types explained below-

MySQL Data Types

There are three main data types available in Database: string, numeric, and date and time. Following section lists down all the data types available in MySQL Version 8.0

MySQL – String Data Types

MySQL – Numeric Data Types

MySQL – Date and Time Data Types

MS SQL Server Data Types

As we have previously discussed in this chapter, there are three main data types in MS SQL server. They are: string, numeric, and date and time.

MS SQL Server – String Data Types

String data types in SQL allow us to store a group of characters, enclosed in single quotes, in a record of a table column. These characters can be of any type: numerals, letters, symbols etc.

Users can either store a fixed number of characters or a variable number of characters, depending on their preferences.

Following is the list of the data types that are included under the string data types in SQL.

Example

In the following example, we are creating a table “students” with only string data types values: varchar and char.

CREATE TABLE students (
  name varchar(20) NOT NULL,
  gender char(6) NOT NULL,
  city text NOT NULL
);

Output

On executing the query, the output will be displayed as −

(0 rows affected)

Verification

On the execution of the SQL queries “EXEC sp_help ”dbo.students” we get the details of the table and the data types of each column.

MS SQL Server – Numeric Data Types

Numeric data types are one of the most widely used data types in SQL. They are used to store numeric values only.

Following is the list of data types that are included under the numeric data types in SQL.

Example

In the following example, we are creating a table named employees with only numeric data type values.

CREATE TABLE employees (
  ID int NOT NULL,
  myBoolean bit NOT NULL,
  Fee money,
  celsius float NOT NULL
);

Output

On executing the query, the output will be displayed as −

(0 rows affected)

Verification

On the execution of the SQL queries “EXEC sp_help ”dbo.employees;” we get the details of the table and the data types of each column.

MS SQL Server – Date and Time Data Types

datetime data types are used in SQL for values that contain both dates and times. datetime and time values are defined in the formats: yyyy-mm-dd, hh:mm:ss.nnnnnnn (n is dependent on the column definition) respectively.

Following is the list of data types that are included under the date and times data types in SQL.

Note − Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.

Example

In the following example, we are creating a table named Cust_details with only date and time data types values.

CREATE TABLE Cust_details (
  HolidayDate DATE NOT NULL,
  OrderDateTime DATETIME,
  ScheduleFrom TIME NOT NULL,
  ShippingDateTime DATETIME2
);

Output

On executing the query, the output will be displayed as −

(0 rows affected)

Verification

On the execution of the SQL queries “EXEC sp_help ”dbo.Cust_details;” we get the details of the table and the data types of each column.

Note:

• If you are using the MySQL workbench to run the SQL data types and their queries, then there are some SQL data types and formats for date and time that won”t work; like “money”, “datetime2”, “yyyy/mm/dd” and “time AM”. All these data types specified are compatible only with the SQL server.
• The size of these data types may change in the future updates keep checking the SQL documentation.

Oracle Data Types

There are four main types of data types available in : string, numeric, date & time and large object data types. Following section lists down all the data types available in Oracle Database.

Oracle – String Data Types

Oracle – Numeric Data Types

Oracle – Date and Time Data Types

Oracle – Large Object Data Types (LOB Types)

MS Access Data Types

The MS Access database also offers four categories of data types: String, Numeric, Date and Time, and other specialized data types.

Following are all data types that are provided by MS Access 2013 version and later.

MS Access – String Data Types

MS Access – Numeric Data Types

MS Access – Date and Time Data Types

MS Access – Specialized Data Types

What is SQL Expression?

An SQL expression is a combination of one or more values, operators and SQL functions that are all evaluated to a value. These SQL EXPRESSION(s) are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.

Expressions are used in WHERE clause of an SQL query. As you might have already known, a WHERE clause specifies a condition that needs to be satisfied for the purpose of filtering records from a database table. This condition is comprised of either single or multiple expressions. These expressions are further classified into three types −

• Boolean Expressions
• Numeric Expressions
• Date and time Expressions

Let us discuss each of these expressions in detail further in this chapter.

Syntax

Consider the basic syntax of the SELECT statement containing some expressions as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];

SQL Boolean Expressions

SQL Boolean Expressions are SQL expressions that return only Boolean Datatype as a result. These expressions can be of two types −

• Boolean Expressions that check for equality of two values using SQL comparison operators. Here, equality of these values is a condition.
• Boolean Expressions can also contain one value paired with an SQL logical operator. In this case, the logic specified acts like a condition.

They return either TRUE, FALSE or UNKNOWN as the result. If the condition is met, these expressions return TRUE; and FALSE otherwise. UNKNOWN is returned when either of the operands in the expression is a NULL value.

Syntax

Following is the syntax of Boolean Expression −

SELECT column1, column2, columnN
FROM table_name
WHERE BOOLEAN EXPRESSION;

Example

Consider the CUSTOMERS table having the following records −

The following query is a simple example showing the usage of an SQL Boolean Expression −

SELECT * FROM CUSTOMERS WHERE SALARY = 10000;

Output

The output will be displayed as −

SQL Numeric Expressions

SQL Numeric expressions are comprised of two operands and an SQL Arithmetic Operator. These expressions are used to perform any mathematical operation in any query. Hence, the operands must always be numerals and the return value will always be a number as well.

Syntax

Following is the syntax −

SELECT numerical_expression as OPERATION_NAME
FROM table_name
WHERE NUMERICAL EXPRESSION ;

Here, the numerical_expression is used for a mathematical expression or any formula.

Example

Following is a simple example showing the usage of SQL Numeric Expressions −

SELECT 15 + 6;

Output

The output table is retrieved as −

21

Example

There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as the aggregate data calculations against a table or a specific table column.

SELECT COUNT(*) FROM CUSTOMERS;

Output

The output is displayed as follows −

7

SQL Date Expressions

SQL Date Expressions are used to compare date related values with current system date and time values. For instance, in a manufacturing company, items manufactured per year can be segregated by using date expressions in a WHERE clause. Counting from the first day of an year to the last day, the count of each item will be retrieved; once the required information is gathered, the company can use this information for their own purposes.

Syntax

Following is the syntax −

SELECT column_name(s)
FROM table_name
WHERE DATE EXPRESSION ;

Example

In this example we are trying to simply retrieve the current timestamp of the system using CURRENT_TIMESTAMP.

SELECT CURRENT_TIMESTAMP;

Output

The output table is displayed as −

Example

Consider the following Customer Order records in an ORDERS table:

Now let”s retrieve the records before 1st June, 2008:

SELECT * FROM ORDERS WHERE DATE < ''2008/06/01

Output

The output table is displayed as −