The SQL ORDER BY Clause

The SQL ORDER BY clause is used to sort the data in either ascending or descending order, based on one or more columns. This clause can sort data by a single column or by multiple columns. Sorting by multiple columns can be helpful when you need to sort data hierarchically, such as sorting by state, city, and then by the person”s name.

ORDER BY is used with the SQL SELECT statement and is usually specified after the WHERE, HAVING, and GROUP BY clauses.

Following are the important points about ORDER BY Clause −

• Some databases sort the query results in an ascending order by default.
• To sort the data in ascending order, we use the keyword ASC.
• To sort the data in descending order, we use the keyword DESC.

In addition to sorting records in ascending order or descending order, the ORDER BY clause can also sort the data in a database table in a preferred order.

This preferred order may not sort the records of a table in any standard order (like alphabetical or lexicographical), but they could be sorted based on external condition(s).

For instance, in the CUSTOMERS table containing the details of the customers of an organization, the records can be sorted based on the population of the cities they are from. This need not be alphabetically sorted, instead, we need to define the order manually using the statement.

Syntax

The basic syntax of the ORDER BY clause is as follows −

SELECT column-list
FROM table_name
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Where, column-list is list of the columns we want to retrieve; and ASC or DESC specifies the sort order.

Note: We can use more than one column in the ORDER BY clause, but we need to make sure that the column we are using to sort is specified in the column-list.

ORDER BY Clause with ASC

We can sort the result-set of a query in ascending order (based on one or more columns) using the SQL ORDER BY clause by specifying ASC as the sort order. ASC is the default sort order for this clause, i.e. while using the ORDER BY clause if you do not explicitly specify the sort order, the data will be sorted in ascending order.

Example

Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement 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)
);

Following query inserts values into this table using the INSERT statement −

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 obtained is as shown below −

In the following query, we are sorting the records of the CUSTOMERS table in ascending order based on the column NAME −

SELECT * FROM CUSTOMERS ORDER BY NAME ASC;

Output

This would produce the following result −

ORDER BY Clause with DESC

To sort the result-set of a query in descending order (based on one or more columns), we need to use the ORDER BY clause by specifying DESC as the sort order.

Example

The following query sorts the records of the CUSTOMER table based on the descending order of the name of the customers −

SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

Output

This would produce the result as follows −

ORDER BY Clause on Multiple Columns

We can use the ORDER BY clause to sort the result-set of a query by multiple (more than one) columns. When sorting by multiple columns, the sorting is done in the order that is specified in the ORDER BY clause. In other words, the table will be sorted based on the first column (specified in the query), then the second column, and so on.

Example

In the following query, we are retrieving all records from the CUSTOMERS table and sorting them first by their address in ascending order, and then by their salary in descending order −

SELECT * FROM CUSTOMERS ORDER BY AGE ASC, SALARY DESC;

Output

Following is the result produced −

ORDER BY with WHERE Clause

We can also use the WHERE clause with the ORDER BY clause to sort the rows that meet certain conditions. This can be useful when we want to sort a subset of the data in a table based on the specific criteria.

Example

Now, we are retrieving all records from the CUSTOMERS table where the age of the customer is 25, and sorting them as per the descending order of their names −

SELECT * FROM CUSTOMERS
WHERE AGE = 25 ORDER BY NAME DESC;

Output

Following is the output of the above query −

ORDER BY with LIMIT Clause

We can use the LIMIT clause with ORDER BY clause to limit the specified number of rows by sorting them either in ascending or in descending order.

Syntax

Following is the syntax of using the LIMIT clause with the ORDER BY clause in MySQL database −

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name1 [ASC | DESC], column_name2 [ASC | DESC], ...
LIMIT N;

Example

In here, we are retrieving the top 4 records from the CUSTOMERS table based on their salary, and sorting them in ascending order based on their name −

SELECT SALARY FROM CUSTOMERS ORDER BY NAME LIMIT 4;

Output

Following is the output of the above query −

Sorting Results in a Preferred Order

One can also sort the records of a table in their own preferred order using the CASE statement within the ORDER BY clause. All the values are specified in the clause along with the position they are supposed to be sorted in; if the values are not given any number, they are automatically sorted in ascending order.

Example

To fetch the rows with their own preferred order, the SELECT query used would be as follows −

SELECT * FROM CUSTOMERS ORDER BY (
CASE ADDRESS
   WHEN ''MUMBAI'' THEN 1
   WHEN ''DELHI'' THEN 2
   WHEN ''HYDERABAD'' THEN 3
   WHEN ''AHMEDABAD'' THEN 4
   WHEN ''INDORE'' THEN 5
   WHEN ''BHOPAL'' THEN 6
   WHEN ''KOTA'' THEN 7
   ELSE 100 END
);

Output

The above query sorts the CUSTOMERS table based on the custom order defined using the CASE statement. Here, we are sorting the records based on the population of the cities specified in the ADDRESS column.

The SQL DELETE is a command of Data Manipulation Language (DML), so it does not delete or modify the table structure but it delete only the data contained within the table. Therefore, any constraints, indexes, or triggers defined in the table will still exist after you delete data from it.

SQL DELETE TABLE Statement

The SQL DELETE TABLE statement is used to delete the existing records from a table in a database. If you wish to delete only the specific number of rows from the table, you can use the WHERE clause with the DELETE statement. If you omit the WHERE clause, all rows in the table will be deleted. The SQL DELETE statement operates on a single table at a time.

Syntax

Following is the basic syntax for using the SQL DELETE command in SQL −

DELETE FROM table_name;

SQL DELETE TABLE with WHERE Clause

We can use the SQL DELETE statement to delete specific rows from a table based on a single condition using the WHERE clause.

Syntax

Following is the syntax for deleting specific rows based on single condition −

DELETE FROM table_name
WHERE condition;

Example

Assume we have creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

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 );

If you retrieve the contents of the above created table using the SELECT * FROM CUSTOMERS statement you will get the following output −

Now, let”s try to delete all the customers with the name ”Hardik” as shown in the query below −

DELETE FROM CUSTOMERS WHERE NAME=''Hardik

Output

We get the following result. We can observe that 1 row has been deleted.

Query OK, 1 row affected (0.05 sec)

Verification

Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −

Deleting rows based on multiple conditions

We can also use the SQL DELETE TABLE statement to delete specific rows from a table based on multiple conditions using the WHERE clause. This is useful when we want to remove a subset of rows from a table that meet a certain criterion.

When using multiple conditions, we can use the comparison operators such as AND, OR, and NOT to refine our conditions. This way, only rows that satisfy the conditions will be deleted.

Syntax

Following is the basic syntax for deleting specific rows based on multiple conditions which can be connected using either AND or OR operators −

DELETE FROM table_name
WHERE condition1 AND condition2 OR ... conditionN;

Here, table_name is the name of the table from which we want to delete rows, and condition1 through conditionN are the conditions that must be met for the rows to be deleted. The AND or OR operators can be used to join the conditions together.

Example

In the following query we are trying to delete all the customers whose name is either ”Komal” or their address is ”Mumbai” −

DELETE FROM CUSTOMERS
WHERE NAME=''Komal'' OR ADDRESS=''Mumbai

Output

We get the following result. We can observe that 2 rows has been deleted.

Query OK, 2 rows affected (0.03 sec)

Verification

Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −

Deleting all the records in a table

We can use the SQL DELETE TABLE statement without a WHERE clause to delete all records in a table in SQL. This statement will remove all the rows from the specified table, effectively resetting the table to its original state (containing only the structure and its constraints).

However, it”s important to note that this operation cannot be undone, and all the data in the table will be permanently deleted.

Example

In here, we are trying to delete all the records from the CUSTOMERS table −

DELETE FROM CUSTOMERS;

Output

Following is the result produced by executing the above query −

Query OK, 4 rows affected (0.13 sec)

Verification

Now, if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS statement you will get the following result −

Empty set (0.00 sec)

The SQL INSERT INTO Statement

The SQL INSERT INTO Statement is used to add new rows of data into a table in the database. Almost all the RDBMS provide this SQL query to add the records in database tables.

Each value in the records we are inserting in a table using this statement should be of the same datatype as the respective column and satisfy the constraints of the column (if any). The values passed using an insert statement should match the number of columns in the table or, the number of columns mentioned in the current query. If any of these conditions are not satisfied, this statement generates an error.

Syntax

There are two basic syntaxes of the SQL INSERT INTO statement which are shown below −

INSERT INTO TABLE_NAME (column1, column2...columnN)
VALUES (value1, value2...valueN);

Here, column1, column2, column3,…columnN are the names of the columns in the table into which you want to insert the data.

There is another syntax of INSERT INTO statement where you can specify only column values without column names. But, make sure the order of the values is in the same order as the columns in the table.

Following is second syntax of the SQL INSERT Query −

INSERT INTO TABLE_NAME
VALUES (value1,value2...valueN);

Example

To see an example, let us create a table with name CUSTOMERS in the MySQL database using the CREATE TABLE statement 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)
);

The following SQL INSERT INTO statements will create three records in the empty CUSTOMERS table.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, ''Khilan'', 25, ''Delhi'', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, ''Kaushik'', 23, ''Kota'', 2000.00 );

We can also insert multiple rows at once using the following query as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 );

Following query adds another record in the CUSTOMERS table using the second syntax as shown below −

INSERT INTO CUSTOMERS
VALUES (7, ''Muffy'', 24, ''Indore'', 10000.00 );

Verification

To check if the records are inserted into the CUSTOMERS table, use the SELECT query −

SELECT * FROM CUSTOMERS;

The table will be displayed with all the records included in it.

Inserting Data into a Table Using Another

Sometimes, you just need to copy the data from an existing table to another table in the same database. SQL provides convenient ways to do so −

• Using INSERT… SELECT
• Using INSERT… TABLE

The INSERT… SELECT Statement

You can populate the data into a table through the select statement using an already existing another table; provided the other table has a set of fields, which are required to populate the first table.

Here is the syntax −

INSERT INTO first_table_name [(column_name(s))]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

Example

The following statement would create another table named BUYERS with the same structure as CUSTOMERS table −

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

Now using the INSERT… INTO statement, let us insert all the records from the CUSTOMERS table into the BUYERS table.

INSERT INTO BUYERS (ID, NAME, AGE, ADDRESS, SALARY)
SELECT * FROM CUSTOMERS;

Output

The output will be displayed as −

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

Verification

To verify if the records are inserted properly or not, use the following SELECT query −

SELECT * FROM BUYERS;

The table will be displayed containing the same records as CUSTOMERS −

The INSERT… TABLE Statement

If you have two tables structure exactly same, then instead of selecting specific columns you can insert the contents of one table into another using the INSERT…TABLE statement.

Following is the syntax to do so −

INSERT INTO first_table_name TABLE second_table_name;

Example

In this example, let us use the same CUSTOMERS table we have created in the previous example and copy its contents into another table named SHOPPERS. For that, let”s create the table SHOPPERS with the same structure as CUSTOMERS table −

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

Now use the following statement to insert all the records from the CUSTOMERS table into SHOPPERS table −

INSERT INTO SHOPPERS TABLE CUSTOMERS;

Output

This query will generate the following output −

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

Verification

If you verify the contents of the SHOPPERS table using the SELECT statement shown below −

SELECT * FROM SHOPPERS;

The table will be displayed with the newly inserted values as −

Insert Data Only in Specified Columns

You can select only particular columns from a table to insert into another table. The following SQL statement will insert a new record into BUYERS table with “ID”, “NAME” and “AGE” from CUSTOMERS table.

We can skip only fields which are not defined as NOT NULL, but if we have defined a column as NOT NULL, then we need to provide a value to this column otherwise it will raise an error and record will not be inserted.

Before we proceed further let”s clean all the records from BUYERS table as follows:

DELETE FROM BUYERS;

Now we have empty BUYERS table, let”s use the following SQL statement:

INSERT INTO BUYERS (ID, NAME, AGE)
SELECT ID, NAME, AGE FROM CUSTOMERS;

Output

This query will generate the following output −

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

Verification

If you verify the contents of the BUYERS table using the SELECT statement shown below −

SELECT * FROM BUYERS;

The table will be displayed with the newly inserted values as −

The SQL SELECT Statement

The SQL SELECT Statement is used to fetch the data from a database table which returns this data in the form of a table. These tables are called result-sets.

CLAUSES and OPERATORS available in SQL can be used with the SELECT statement in order to retrieve the filtered records of a database table.

Syntax

The basic syntax of the SELECT Query is as follows −

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2… are the fields of a table whose values you want to fetch. If you want to fetch all the columns available in a table, then you can use the following syntax −

SELECT * FROM table_name;

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement 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 −

Retrieving Selected Fields/Columns

The following statement fetches the ID, Name and Salary fields of the records available in CUSTOMERS table.

SELECT ID, NAME, SALARY FROM CUSTOMERS;

Output

The above query would produce the following table −

Retrieving All Fields/Columns

If you want to fetch all the fields of the CUSTOMERS table, then you should use the query of SELECT statement with an Asterisk (*) instead of the column names, as shown below −

SELECT * FROM CUSTOMERS;

Output

The resultant table will be −

Computing Using SELECT

The SQL SELECT statement can also be used to retrieve the results of various mathematical computations in the form of a table. In such cases, you do not need to specify any database table in the statement.

Following is the syntax to do so −

SELECT mathematical_expression;

Example

Following is an example which multiply two given numbers using SQL statement.

SELECT 56*65;

The query above produces the following output −

Aliasing a Column in SELECT Statement

Whenever a column name in a table is too difficult to read and understand, SQL provides a method to alias this column name into another understandable and relative name. This is done using the AS keyword. You can use the AS keyword in a SELECT statement to display the column names of a table as an alias name.

Following is the syntax to do so −

SELECT column_name
AS alias_name
FROM table_name;

You can also use an alias to display SELECT expressions with the same syntax; you should use a mathematical statement instead of column_name.

Example

In the example below, we are trying to retrieve customer details NAME and AGE in a single column of the resultant table using the concat() expression and aliasing the column as DETAILS along with the customer addresses from the CUSTOMERS table. This will be done using SELECT statement in the following query −

SELECT CONCAT(NAME,'' '',AGE)
AS DETAILS, ADDRESS
FROM CUSTOMERS ORDER BY NAME;

The query above produces the following output −

The SQL Select Into Statement

The SQL SELECT INTO Statement creates a new table and inserts data from an existing table into the newly created table. The new table is automatically created based on the structure of the columns in the SELECT statement and can be created in the same database or in a different database.

However, it”s important to note that the SELECT INTO statement does not preserve any indexes, constraints, or other properties of the original table, and the new table will not have any primary keys or foreign keys defined by default. Therefore, you may need to add these properties to the new table manually if necessary.

MySQL doesn”t support the SELECT … INTO TABLE Sybase SQL extension i.e. in MySQL you cannot use the SELECT … INTO statement to insert data from one table to another. Instead of this, we can use INSERT INTO … SELECT statement or, CREATE TABLE … SELECT.

Syntax

Following is the basic syntax of the SQL SELECT INTO statement in SQL Server −

SELECT * INTO new_table_name FROM existing_table_name

Example

Let us create the CUSTOMERS table 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, 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 CUSTOMERS table will be creates as follows −

The following SELECT INTO statement creates a new table called CUSTOMER_BACKUP and copies the data from the CUSTOMERS table into it −

SELECT * INTO CUSTOMER_BACKUP FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_BACKUP table −

SELECT * from CUSTOMER_BACKUP;

The table displayed is as follows −

Copying Data From Specific Columns

We can also copy data from specific columns from an existing table into the new table using the SQL SELECT INTO statement. To do so, we just need to include the required column names after the select keyword.

Syntax

Following is the syntax −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM existing_table_name;

Example

In the following query, we are creating a new table called CUSTOMER_DETAILS with only the NAME, AGE, and ADDRESS columns from the CUSTOMERS table, and populate it with the corresponding data.

SELECT name, age, address
INTO CUSTOMER_DETAILS
FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_DETAILS table −

SELECT * from CUSTOMER_DETAILS;

The table displayed is as follows −

Note: The new table will not include any other columns from the original table. Also the original table remains unchanged.

Copying Data From Multiple Tables

Using the SQL SELECT INTO statement we can also copy data from multiple tables to a new table. This is accomplished using the JOIN clause which combines the data from multiple tables (based on a common column).

Syntax

Following is the syntax to copy data from multiple tables using the SELECT INTO statement −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM table1
JOIN table2 ON table1.column = table2.column

Example

First of all, let us create another table named ORDERS −

CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2));

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES
(102, ''2009-10-08 00:00:00'', 3, 3000.00),
(100, ''2009-10-08 00:00:00'', 3, 1500.00),
(101, ''2009-11-20 00:00:00'', 2, 1560.00),
(103, ''2008-05-20 00:00:00'', 4, 2060.00);

The table is created as −

Now, we are creating a new table called CUSTOMER_ORDERS that includes the customer name from the CUSTOMERS table and the customer id from the ORDERS table, where the id of customers from the CUSTOMERS table matches with the id of customers from the ORDERS table −

SELECT CUSTOMERS.Name, ORDERS.customer_id
INTO CUSTOMER_ORDERS
FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.customer_id;

Output

We get the following result. We can observe that 8 rows have been modified.

(8 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_ORDERS table −

SELECT * FROM CUSTOMER_ORDERS;

The table displayed is as follows −

Copying Specific Records

We can also use the SQL SELECT INTO statement with a WHERE clause to create a new table and copy specific rows from an existing table into it.

Syntax

Following is the syntax for using SELECT INTO statement with a WHERE clause −

SELECT *
INTO new_table_name
FROM existing_table_name
WHERE condition;

Example

Using the following query we are creating a new table called NameStartsWith_K that includes all columns from the CUSTOMERS table, but it only stores the records of the customers whose name starts with “k”.

SELECT *
INTO NameStartsWith_K
FROM CUSTOMERS
WHERE NAME LIKE ''k%

Output

We get the following result. We can observe that 3 rows have been modified.

(3 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement.

SELECT * from NameStartsWith_K;

The table displayed is as follows −

The Insert Into… Select Statement

The SQL INSERT INTO… SELECT statement is used to add/insert one or more new rows from an existing table to another table. This statement is a combination of two different statements: INSERT INTO and SELECT.

• The INSERT INTO statement is one of the most fundamental and frequently used statements in database management and requires only the name of the table and the values to be inserted. However, it is important to ensure that the data being inserted satisfies the constraints if the columns of a table (if any) and its type matches the data types of the table columns.

• The SELECT statement is used to retrieve data from an existing database table.

When these statements are used together, the SELECT statement first retrieves the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).

Syntax

Following is the syntax of the SQL INSERT INTO… SELECT statement −

INSERT INTO table_new
SELECT (column1, column2, ...columnN)
FROM table_old;

Before using this query, we have to make sure that −

• In the database where we are going to insert data, source and target tables already exist.

• The structure of the source and target tables are same.

Example

Assume we have created a table named 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, 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 −

Create another table named BUYERS with same structure as the CUSTOMERS table.

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

Following query copies all the records from the CUSTOMERS table to BUYERS −

INSERT INTO BUYERS SELECT * FROM CUSTOMERS;

Verification

If you verify the contents of the BUYERS table using the SELECT statement as −

SELECT * FROM BUYERS;

The table will be created as −

SQL – Inserting Specific Records

Sometimes we only need to add a small number of records to another table. This can be accomplished by using a WHERE clause along with the SQL INSERT INTO… SELECT statement.

Example

Let us create a table named NAMESTARTSWITH_K with the same structure as the CUSTOMER table using the CREATE statement as −

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

Following query inserts the records of the customers whose name starts with the letter k from the CUSTOMERS table to the BUYERS table −

INSERT INTO NAMESTARTSWITH_K
SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''k%

Verification

Following is the SELECT statement to verify the contents of the above created table −

SELECT * FROM NAMESTARTSWITH_K;

The table will be created as −

SQL – Inserting Top N Rows

The LIMIT clause filters the number of rows from the query. You can use this to filter the top N records that should be added to the target table.

Example

But, before proceeding further, let us truncate all rows in the BUYERS table using the following statement −

TRUNCATE TABLE BUYERS;

Following query inserts the top 3 records from the CUSTOMERS table to the BUYERS table −

INSERT INTO BUYERS
SELECT * FROM CUSTOMERS
ORDER BY ID ASC LIMIT 3;

Verification

Let us verify the contents of the BUYERS table −

SELECT * FROM BUYERS;

The resultant table will be as follows −

The SQL DELETE Statement

The SQL DELETE Statement is used to delete the records from an existing table. In order to filter the records to be deleted (or, delete particular records), we need to use the WHERE clause along with the DELETE statement.

If you execute DELETE statement without a WHERE clause, it will delete all the records from the table.

Using the DELETE statement, we can delete one or more rows of a single table and records across multiple tables.

Syntax

The basic syntax of the SQL DELETE Query with the WHERE clause is as follows −

DELETE FROM table_name WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example

Assume we have created a table named 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, 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 −

The following query deletes the record of a customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

Output

The output will be displayed as −

Query OK, 1 row affected (0.10 sec)

Verification

To verify whether the record(s) have been deleted from the table, we need to retrieve the modified table using the SELECT query, as shown below −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would have the following records −

Deleting Multiple Rows

To delete multiple rows from a table, we need to specify the required condition(s), that is satisfied by all the rows to be deleted, using the WHERE clause. Let us look at an example −

Example

From the same Customers table, let us try to delete the records of customers who are over 25 years of age.

DELETE FROM CUSTOMERS WHERE AGE > 25;

Output

The output will be displayed as −

Query OK, 2 rows affected (0.06 sec)

Verification

To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

SELECT * FROM CUSTOMERS;

The query above will produce the following table −

Deleting All The Records From a Table

If we want to DELETE all the records from an existing table (truncate it) using the DELETE query, we simply need to run it without using the WHERE clause.

Example

Following SQL query removes all the records from the CUSTOMERS table −

DELETE FROM CUSTOMERS;

Output

The output will be displayed as −

Query OK, 4 rows affected (0.13 sec)

Verification

To verify whether all the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would not have any record and will show the following output −

Empty set (0.00 sec)

Delete Records in Multiple Tables

SQL allows us to delete the records from multiple tables using the DELETE query. In here, we will use the JOIN clause to combine data from multiple tables (based on a common column).

Example

Let us create another table with name ORDERS which contains the details of the orders made by the customers.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into this table as follows

INSERT INTO ORDERS VALUES
(102, ''2009-10-08 00:00:00'', 3, 3000.00),
(100, ''2009-10-08 00:00:00'', 3, 1500.00),
(101, ''2009-11-20 00:00:00'', 2, 1560.00),
(103, ''2008-05-20 00:00:00'', 4, 2060.00);

The table created is as shown below −

Following SQL query deletes the records of the customers (from the tables CUSTOMERS and ORDERS) who earn more than 2000 and have placed orders −

DELETE CUSTOMERS, ORDERS FROM CUSTOMERS
INNER JOIN ORDERS ON ORDERS.CUSTOMER_ID = CUSTOMERS.ID
WHERE CUSTOMERS.SALARY > 2000;

Output

The output will be displayed as −

Query OK, 2 rows affected (0.01 sec)

Verification

To verify whether the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

SELECT * FROM CUSTOMERS;

The CUSTOMERS table would not have the record(s) where salary is greater than 2000 and the ID matches with the CUSTOMER_ID column in the ORDERS table.

Similarly, if you verify the ORDERS table as shown below −

SELECT * FROM ORDERS;

Since salary is greater than 2000 and the CUSTOMER_ID matches with the ID value in the CUSTOMERS table, the last record (OID 103) of the ORDERS table will be deleted −

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;

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.

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 −