Category: Queries

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. By default, some databases sort the query results in an ascending order.

In addition to that, ORDER BY clause can also sort the data in a database table in a preferred order. This case may not sort the records of a table in any standard order (like alphabetical or lexicographical), but, they could be sorted based on any external condition. For instance, in an ORDERS table containing the list of orders made by various customers of an organization, the details of orders placed can be sorted based on the dates on which those orders are made. This need not be alphabetically sorted, instead, it is based on “first come first serve”.

Syntax

The basic syntax of the ORDER BY clause which would be used to sort the result in an ascending or descending order is as follows −

SELECTcolumn-list 
FROM table_name 
[WHERE condition][ORDERBY column1, column2,.. columnN][ASC|DESC];

You can use more than one column in the ORDER BY clause. Make sure that whatever column you are using to sort, that column should be in the column-list.

Sorting Results in Ascending Order

Using Order By Clause in SQL, the records in a database table can be sorted in ascending order, either by default or by specifying the “ASC” keyword in the clause condition. Let us see an example to understand this.

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)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 −

ID	NAME	AGE	ADDRESS	SALARY
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

Following is an example, which would sort the result in an ascending order by NAME and SALARY.

SELECT*FROM CUSTOMERS ORDERBY NAME;

Output

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
3	kaushik	23	Kota	2000.00
2	Khilan	25	Delhi	1500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00
1	Ramesh	32	Ahmedabad	2000.00

Sorting Results in Descending Order

But, to sort the records in a database table in descending order, we need to specify the “DESC” keyword in the clause condition. Let us see an example to understand this.

Example

The following query sorts the records of the CUSTOMERS tables in descending order based on the column NAME.

SELECT*FROM CUSTOMERS ORDERBY NAME DESC;

Output

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
7	Muffy	24	Indore	10000.00
6	Komal	22	Hyderabad	4500.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
4	Chaitali	25	Mumbai	6500.00

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
ORDERBY(CASE ADDRESS
   WHEN'DELHI'THEN1WHEN'BHOPAL'THEN2WHEN'KOTA'THEN3WHEN'AHMEDABAD'THEN4WHEN'Hyderabad'THEN5ELSE100END)ASC, ADDRESS DESC;

Output

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
3	kaushik	23	Kota	2000.00
1	Ramesh	32	Ahmedabad	2000.00
6	Komal	22	Hyderabad	4500.00
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

This will sort the customers by ADDRESS in your own order of preference first, and in a natural order for the remaining addresses. Also, the remaining Addresses will be sorted in the reverse alphabetical order.

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 −

DELETEFROM 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 −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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 following query deletes the record of a customer, whose ID is 6.

DELETEFROM 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 −

ID	NAME	AGE	ADDRESS	SALARY
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
7	Muffy	24	Indore	10000.00

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.

DELETEFROM 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 −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

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 −

DELETEFROM 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.

CREATETABLE ORDERS (
   OID INTNOTNULL,DATEVARCHAR(20)NOTNULL,
   CUSTOMER_ID INTNOTNULL,
   AMOUNT DECIMAL(18,2));

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

INSERTINTO 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 −

OID	DATE	CUSTOMER_ID	AMOUNT
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

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
INNERJOIN 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.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	MP	4500.00
7	Muffy	24	Indore	10000.00

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 −

OID	DATE	CUSTOMER_ID	AMOUNT
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

The SQL UPDATE Statement

The SQL UPDATE Statement is used to modify the existing records in a table. This statement is a part of Data Manipulation Language (DML), as it only modifies the data present in a table without affecting the table’s structure.

To filter records that needs to be modified, you can use a WHERE clause with UPDATE statement. Using a WHERE clause, you can either update a single row or multiple rows.

Since it only interacts with the data of a table, the SQL UPDATE statement needs to used cautiously. If the rows to be modified aren’t selected properly, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted.

The SQL UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.

Syntax

The basic syntax of the SQL UPDATE statement with a WHERE clause is as follows −

UPDATE table_name
SET column1 = value1, column2 = value2,..., columnN = valueN
WHERE[condition];

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

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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 following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS SET ADDRESS ='Pune'WHERE ID =6;

Output

The query produces the following output −

Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT*FROM CUSTOMERS WHERE ID=6;

Now, the CUSTOMERS table would have the following records −

ID	NAME	AGE	ADDRESS	SALARY
6	Komal	22	Pune	4500.00

Update Multiple ROWS and COLUMNS

Using SQL UPDATE statement, multiple rows and columns in a table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it.

However, to update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns.

Syntax

Following is the syntax to update multiple rows and columns −

UPDATE table_name
SET column_name1 = new_value, column_name2 = new_value...WHERE condition(s)

Example

If you want to modify all the AGE and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough. Following query increases the age of all the customers by 5 years and adds 3000 to all the salary values −

UPDATE CUSTOMERS SET AGE = AGE+5, SALARY = SALARY+3000;

Output

The query produces the following output −

Query OK, 7 rows affected (0.12 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT*FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	37	Ahmedabad	5000.00
2	Khilan	30	Delhi	4500.00
3	Kaushik	28	Kota	5000.00
4	Chaitali	30	Mumbai	9500.00
5	Hardik	32	Bhopal	11500.00
6	Komal	27	Pune	7500.00
7	Muffy	29	Indore	13000.00

Example

But, if you want to modify the ADDRESS and the SALARY columns of selected records in the CUSTOMERS table, you need to specify a condition to filter the records to be modified, using the WHERE clause, as shown in the following query −

UPDATE CUSTOMERS 
SET ADDRESS ='Pune', SALARY =1000.00WHERE NAME ='Ramesh';

Output

This query produces the following output −

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT*FROM CUSTOMERS WHERE NAME ='Ramesh';

Now, CUSTOMERS table would have the following records −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	37	Pune	1000.00

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 −

INSERTINTO 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 −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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

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

CREATETABLE BUYERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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

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 −

CREATETABLE NAMESTARTSWITH_K (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

Open Compiler

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00

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 −

TRUNCATETABLE BUYERS;

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

INSERTINTO BUYERS 
SELECT*FROM CUSTOMERS 
ORDERBY ID ASCLIMIT3;

Verification

Let us verify the contents of the BUYERS table −

SELECT*FROM BUYERS;

The resultant table will be as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

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 −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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 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 −

ID	NAME	AGE	ADDRESS	SALARY
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

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 −

NAME	AGE	ADDRESS
Ramesh	32	Ahmedabad
Khilan	25	Delhi
Kaushik	23	Kota
Chaitali	25	Mumbai
Hardik	27	Bhopal
Komal	22	Hyderabad
Muffy	24	Indore

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 −

CREATETABLE ORDERS (
OID INTNOTNULL,DATEVARCHAR(20)NOTNULL,
CUSTOMER_ID INTNOTNULL,
AMOUNT DECIMAL(18,2));

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

INSERTINTO 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 −

OID	DATE	CUSTOMER_ID	AMOUNT
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

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
LEFTJOIN 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 −

NAME	customer_id
Ramesh	NULL
Khilan	2
Kaushik	3
Kaushik	3
Chailtali	4
Hardik	NULL
Komal	NULL
Muffy	NULL

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 −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00

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 −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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

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 −

ID	NAME	Salary
1	Ramesh	2000.00
2	Khilan	1500.00
3	Kaushik	2000.00
4	Chaitali	6500.00
5	Hardik	8500.00
6	Komal	4500.00
7	Muffy	10000.00

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 −

ID	NAME	AGE	ADDRESS	SALARY
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

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.

SELECT56*65;

The query above produces the following output −

56*65
3640

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 ORDERBY NAME;

The query above produces the following output −

DETAILS	ADDRESS
Chaitali 25	Mumbai
Hardik 27	Bhopal
Kaushik 23	Kota
Khilan 25	Delhi
Komal 22	Hyderabad
Muffy 24	Indore
Ramesh 32	Ahmedabad

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 −

INSERTINTO 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 −

INSERTINTO 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 −

CREATETABLE CUSTOMERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Ramesh',32,'Ahmedabad',2000.00);INSERTINTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES(2,'Khilan',25,'Delhi',1500.00);INSERTINTO 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 −

INSERTINTO 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 −

INSERTINTO 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.

ID	NAME	AGE	ADDRESS	SALARY
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

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 −

INSERTINTO 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 −

CREATETABLE BUYERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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 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 −

INSERTINTO 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 −

CREATETABLE SHOPPERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
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

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:

DELETEFROM BUYERS;

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

INSERTINTO 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 −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32
2	Khilan	25
3	Kaushik	23
4	Chaitali	25
5	Hardik	27
6	Komal	22
7	Muffy	24