In SQL, we have two commonly used clauses that help us to manipulate data; Group By clause and Order By clause.

A Group By clause is used to arrange the identical data/records into groups and the Order By clause is used to sort the data in ascending or descending order.

The SQL Group By Clause

Using the GROUP BY clause we can organize the data in a table into groups (based on a column) and perform required calculations on them.

This clause is often used with the aggregate functions such as MIN(), MAX(), SUM(), AVG(), and COUNT() etc.

It is often used with the SELECT statement, and it is placed after the WHERE clause or before the HAVING clause. If we use the Order By clause, the Group By clause should precede the Order By clause.

Syntax

Following is the syntax of the SQL Group By clause −

SELECT column_name, aggregate_function() FROM table_name
WHERE condition GROUP BY column_name;

The aggregate_function() and the WHERE clause are optional in the above syntax.

Example

Assume we have created a table named CUSTOMERS that contains records of customers such as NAME, AGE, ADDRESS, and SALARY etc.., using the following CREATE statement −

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, we are inserting 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 created as −

In the SQL query below, we are using the Group by clause to group the rows based on their salaries from the CUSTOMERS table and counting the number of records in each group −

SELECT SALARY, COUNT(SALARY) from CUSTOMERS GROUP BY SALARY;

Output

When we execute the above query, the following result will be displayed −

The SQL Order By Clause

The ORDER BY clause is used to sort the query results. This clause is used at the end of a SELECT statement, following the WHERE, HAVING and GROUP BY clauses. We can sort the table column in ascending or descending order with the by specifying the sort order as ASC and DESC respectively. If we do not specify any order, it defaults to ascending order.

Syntax

Following is the syntax to sort the column value in ascending/descending order using the SQL ORDER BY clause −

SELECT column_name FROM table_name ORDER BY ASC/DSC;

Example

In the following query, we are retrieving the ID and NAME from the CUSTOMERS table and using the ORDER BY clause, we are sorting the names in ascending order.

SELECT ID, NAME FROM CUSTOMERS ORDER BY NAME;

Output

When we run the above query, we can see that the resultant table is sorted by name in ascending order.

Example

In the following example, we are retrieving the NAME, calculating the AVG SALARY, and using the GROUP BY clause to group the table by NAME.

SELECT NAME, AVG(SALARY) FROM CUSTOMERS GROUP BY NAME;

Output

When we run the above query, we get the name and average salary. The average salary is the same as the actual salary because there are no two or more than two records with the same name. As a result, the average salary is the same as the actual salary, and the table is grouped by name. as shown in the table below.

Example

In the following example, we are retrieving, NAME, AGE, and SALARY and using the ORDER BY clause to sort the AGE in the ascending order.

SELECT NAME, AGE, SALARY FROM customers ORDER BY AGE;

Output

The table generated by the above query is as shown below −

Group by vs Order by

Following table summarizes the differences between the Group By clause and Order by clause −

In SQL, we use the IN operator to simplify queries and reduce the need for multiple OR conditions. It allows us to match a value against a list of values. On the other hand, the EXISTS operator checks whether one or more rows exist in a subquery and returns either true or false based on this condition. If the subquery finds at least one row, the EXISTS operator returns true; otherwise, it returns false.

The SQL IN Operator

The IN operator in SQL is used to check if a particular value matches any within a given set. This set of values can be specified individually or obtained from a subquery. We can use the IN operator with the WHERE clause to simplify queries and reduce the use of multiple OR conditions.

Suppose we have a table named CUSTOMERS and we want to retrieve customer details based on their IDs. In this scenario, we can use the IN operator with the WHERE clause to fetch the details of these specific IDs.

Syntax

Following is the syntax of the SQL IN operator −

SELECT column_name
FROM table_name
WHERE column_name
IN (value1, value2, valueN);

In the above syntax, the column_name matches every value (value1, value2, … valueN). If the matches occur, The IN operators returns true; otherwise, false.

Example

First of all, let us create a table named CUSTOMERS using the following query −

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, add records into the above created table using the INSERT INTO statement as shown below −

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 −

The following query retrieves the NAME and SALARY columns from the CUSTOMERS table for rows where the ID is 1, 2, or 3.

SELECT NAME, SALARY FROM CUSTOMERS WHERE ID IN(1, 2, 3);

Output

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

The SQL EXISTS Operator

The EXISTS operator is used to look for the existence of a row in a given table that satisfies a set of criteria. It is a Boolean operator that compares the result of the subquery to an existing record and returns true or false.

The returned value is true, if the subquery fetches single or multiple records; and false, if no record is matched. EXISTS operator follows the querys efficiency features, i.e. when the first true event is detected, it will automatically stop processing further.

We can use the EXISTS operator with the SELECT, UPDATE, INSERT and DELETE queries.

Syntax

Following is the basic syntax of SQL EXISTS operator −

SELECT column_name FROM table_name
WHERE EXISTS (
   SELECT column_name FROM table_name
   WHERE condition
);

Example

First of all, consider the CUSTOMERS table, and create another table named EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   EID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   CITY CHAR (25),
   CONTACT INT,
   PRIMARY KEY (EID)
);

Now, let us insert some records into the EMPLOYEES table using the INSERT INTO statement as shown below −

INSERT INTO EMPLOYEES VALUES
(1, ''Varun'', 32, ''Ahmedabad'', 12345),
(2, ''Mahesh'', 22, ''Kashmir'', 34235 ),
(3, ''Suresh'', 43, ''Kerala'', 12355 );

The table will be created as follows −

In the following query, we are using the EXISTS operator to fetch the names and ages of CUSTOMERS whose AGE is same as the AGE in the EMPLOYEES table.

SELECT NAME, AGE
FROM CUSTOMERS
WHERE EXISTS(
   SELECT * FROM EMPLOYEES
   WHERE CUSTOMERS.AGE = EMPLOYEES.AGE
);

Output

Following is the output of the above query −

IN vs EXISTS

Following table summarizes all the differences between IN and EXISTS −

SQL NULL functions are used to perform operations on NULL values that are stored in the database tables.

A NULL value serves as a placeholder in the database when data is absent or the required information is unavailable. It is a flexible value not associated to any specific data type and can be used in columns of various data types, including string, int, varchar, and more.

Following are the various features of a NULL value −

• The NULL value is different from a zero value or a field containing a space. A record with a NULL value is one that has been left empty or unspecified during record creation.

• The NULL value assists us in removing ambiguity from data. Thus, maintaining the uniform datatype across the column.

SQL NULL Functions

To handle these NULL values in a database table, SQL provides various NULL functions. They are listed as follows −

• ISNULL()
• COALESCE()
• NULLIF()
• IFNULL()

The ISNULL() Function

The SQL ISNULL() function returns 0 and 1 depending on whether the expression is null or not. If the expression is null, then this function returns 1; otherwise, it returns 0.

Syntax

Following is the syntax for the ISNULL() function −

ISNULL(column_name)

Example

First of all let us create a table named CUSTOMERS, containing the personal details of customers including their name, age, address and salary etc., using the following query −

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 records into this table using the INSERT INTO statement as follows −

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

The table will be created as −

Following is the query to check whether SALARY is NULL or not −

SELECT SALARY, ISNULL(SALARY) AS Null_value FROM CUSTOMERS;

Output

On execution of the above query, we get the column “SALARY” and Null_value. If the SALARY is NULL, then their null value is 1; otherwise, it is 0. −

The COALESCE() Function

The SQL COALESCE() function returns the first occurred NON-NULL expression among its arguments. If all the expressions are NULL, then the COALESCE() function will return NULL.

An integer is evaluated first in the COALESCE() function, and an integer followed by a character expression always produces an integer as the output.

Syntax

Following is the syntax for the COALESCE() function −

COALESCE(expression_1, expression_2, expression_n);

Example

In the following query, we are returning the first occurred NON-NULL value −

SELECT COALESCE (NULL, ''welcome'', ''tutorialspoint'') AS Result;

Output

On executing the above query, we get “welcome” as a result, because it is the first NON-NULL value −

Example

In the following query, we are using the COALESCE() function on the SALARY and AGE columns of CUSTOMERS table. The first NON-NULL values evaluated from these two columns are displayed in another column named “Result”.

SELECT NAME, SALARY, AGE, COALESCE(SALARY, AGE) AS Result FROM CUSTOMERS;

Output

When you execute the above query, we get the following table as a result −

The NULLIF() Function

The SQL NULLIF() function compares two expressions. If both expressions are the same, it returns NULL. Otherwise, it returns the first expression. This function can be used directly with clauses like SELECT, WHERE, and GROUP BY.

Syntax

Following is the syntax of NULLIF() function −

NULLIF(expression_1, expression_2);

Example

The following SQL query uses NULLIF() function to compare values in NAME and ADDRESS columns of the CUSTOMERS table. If the NAME value matches the ADDRESS value, the result is NULL; otherwise, it returns the NAME value. The result values are stored in another column called “Result”.

SELECT NAME, ADDRESS, NULLIF(NAME, ADDRESS) AS Result FROM CUSTOMERS;

Output

When you execute the above query, we get the following table as a result −

The IFNULL() Function

The IFNULL() function replaces the NULL values in a database table with a specific value. This function accepts two arguments. If the first argument is a NULL value, it is replaced with the second argument. Otherwise, the first argument is returned as it is.

This function does not work in the SQL Server database.

If both the arguments are NULL, the result of this function is also NULL.

Syntax

Following is the syntax for IFNULL() function −

IFNULL(column_name, value_to_replace);

Example

The following query evaluates the values in SALARY column of the CUSTOMERS table. Using the IFNULL() function, we are replacing the NULL values in this column (if any) with the value 5500 −

SELECT NAME, SALARY, IFNULL(SALARY, 5500) AS Result FROM CUSTOMERS;

Output

Following is the output of the above query −

The SQL Primary Key

The SQL Primary Key is a column (or combination of columns) that uniquely identifies each record in a database table. The Primary Key also speeds up data access and is used to establish a relationship between tables.

Even though a table can only have one Primary Key, it can be defined on one or more fields. When a primary key is created on multiple fields of a table, it is called a .

Let us say, you are developing an application called “Customer Management System” to handle all the customer data of a member-only resort. This data can include their personal details, assigned member IDs, other details of the membership they opted, etc. And in all the tables created within this database, the member ID is used to distinguish the customers from each other. So, this field will be the Primary Key.

Following is the diagram of a CUSTOMERS table that holds the personal details of the customers. And as we can observe, the primary key is defined on the CUST_ID column. Using this primary key, we can retrieve a unique record of any customer.

Points to Remember

Here are some key points of the PRIMARY KEY −

• It contains only a unique value.

• It can not be null.

• One table can have only one Primary Key.

• A primary key length cannot be more than 900 bytes.

Creating an SQL Primary Key

While creating a table using the CREATE TABLE statement, you can add the primary key constraint on a particular column of the table just by to specifying the name of the column along with the keyword “PRIMARY KEY”.

Syntax

Following is the syntax to define a column of a table as a primary key −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY(column_name)
);

Example

In the following example, we are trying to create a table with the name CUSTOMERS with various fields in an SQL database. While creating the table, we will add the constraint “PRIMARY KEY” on the column named ID.

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

Output

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.03 sec)

Verification

As we know primary key value must be unique, so you can not insert the record with the same ID. Here, we will verify the constraint created on the ID column, by inserting records with duplicate ID values.

First of all, let”s insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(3, ''Kaushik'', 23, ''Kota'', 2000.00);

Now, let”s insert one more record with same ID −

INSERT INTO CUSTOMERS VALUES
(3, ''Chaitali'', 25, ''Mumbai'', 6500.00);

As we have mentioned above, if any field/column is defined as Primary Key in a database table, two records can not have the same value in that column/field. Therefore, the second insert statement generates the following error −

ERROR 1062 (23000): Duplicate entry ''3'' for key ''customers.PRIMARY''

Similarly, a primary key column cannot contain null values. Here, using the INSERT statement we are passing a NULL value to the primary key column (ID).

INSERT INTO CUSTOMERS VALUES
(NULL, ''Komal'', 22, ''Hyderabad'', 4500.00);

This statement generates the following error −

ERROR 1048 (23000): Column ''ID'' cannot be null

Creating Primary Key on an Existing Column

We can also add the PRIMARY KEY constraint on an existing column of a table using the ALTER TABLE statement.

Syntax

Following is the syntax to create a primary constraint on existing columns of a table −

ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (column_name);

Example

In this example, we are adding the PRIMARY KEY constraint on the NAME column of the existing CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD CONSTRAINT PRIMARY KEY(NAME);

Output

Following is the output of the above statement −

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

Dropping an SQL Primary Key

If you can add a Primary Key Constraint to a column in the table, you can drop it as well. This is done by using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax of the ALTER TABLE statement to can drop the Primary key constraints from the column of a table −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

Let us consider the CUSTOMERS table where we have created a primary key constraint on a column named ID. You can drop this constraint from the column ID by executing the following statement −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

The above SQL query produces the following output −

Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verification

As we have dropped the Primary key from the column named ID, we can insert multiple records with the same ID. Following statement inserts four records with the same ID −

INSERT INTO CUSTOMERS VALUES
(3, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(3, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(3, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(3, ''Muffy'', 24, ''Indore'', 10000.00 );

If you verify the content of this table, you can find multiple records with same ID −

SELECT * FROM CUSTOMERS;

The table will be displayed as −

The SQL Composite Key

An SQL Composite Key is a key that can be defined on two or more columns in a table to uniquely identify any record. It can also be described as a Primary Key created on multiple columns.

Composite Keys are necessary in scenarios where a database table does not have a single column that can uniquely identify each row from the table. In such cases, we might need to use the combination of columns to ensure that each record in the table is distinct and identifiable.

Let us understand the composite keys with an example. Suppose if we have a table named CUSTOMERS with various fields like ID, NAME, AGE, AADHAAR_ID, MOBILE_NO and SALARY as shown below −

We can select the two columns AADHAAR_ID and MOBILE_NO and define a Composite key on them, and it can be used to fetch the records of the CUSTOMERS table uniquely.

Features of Composite Keys

Following are some important features of the SQL Composite Key −

• A Composite Key can be created by combining more than one Candidate Key.
• Each Candidate Key (or column) that makes up a Composite Key may or may not be a Foreign Key. However, if all the columns of the Composite Key are Foreign Keys in their own right, then the Composite Key is known as a Compound Key.
• A Composite Key cannot be NULL; i.e. any column of the Composite Key must not contain NULL values.
• The individual columns making up the Composite Key can contain duplicate values, but, the combination of these columns must be unique across the database table.

Syntax

Following is the syntax to create an SQL Composite Key while creating a table −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   CONSTRAINT composite_key_name,
   PRIMARY KEY(column_name)
);

Here, the composite_key_name is the optional placeholder which holds the name of a Composite Key in a table. It is used while dropping the constraint from a table in some databases.

Example

In the following example, we are creating a table named CUSTOMERS with multiple columns. The Composite Key is created when a PRIMARY KEY is defined on ID and NAME columns together. Look at the query below −

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

Where, ck_customers is the name of a composite key of this table.

Output

Following is the output of the above statement −

Query OK, 0 rows affected (0.02 sec)

Verification

As we have created a Composite Key on the columns ID and NAME of the CUSTOMERS table, the combination of values in these columns can not be duplicated. To verify it, let us insert two records with same values in these columns into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(1, ''Ramesh'', 25, ''Delhi'', 1500.00 );

You can observe that the second INSERT statement generates an error message saying “Duplicate entry” as shown below −

ERROR 1062 (23000): Duplicate entry ''1-Ramesh'' for key ''customers.PRIMARY''

Dropping a Composite Key in MySQL

You can drop the composite key from a table in MySQL database using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax to drop the Composite Key in MySQL −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

Using the following SQL statement, we can drop the Composite Key constraint from the CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

The above SQL statement produces the following output −

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verification

Since, we have dropped the composite from the CUSTOMERS table, so now you can insert the duplicate values in the columns ID and NAME.

Let us insert two records with the same ID and NAME into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 25, ''Delhi'', 1500.00 ),
(1, ''Ramesh'', 23, ''Kota'', 2000.00 );

If you retrieve the contents the CUSTOMERS table you can find the records with same ID and NAME as −

Dropping a Composite Key in SQL Server

In SQL Server, we have a different syntax to drop a composite key of a table. The syntax is almost similar, but we just need to specify the composite key name in order to drop it, rather than the keyword PRIMARY KEY.

Syntax

Following is the syntax to drop a composite key in SQL Server −

ALTER TABLE table_name DROP composite_key_name;

Example

Assuming that a composite key “ck_customers” is created on ID and NAME columns of the CUSTOMERS table, we will use the following query to drop it −

ALTER TABLE CUSTOMERS DROP ck_customers;

Output

When we execute the above query, the composite key will be dropped.

Commands completed successfully.

Verification

To verify whether we have removed the composite key from the CUSTOMERS table or not, insert duplicate values into the ID and NAME columns using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 25, ''Delhi'', 1500.00 ),
(1, ''Ramesh'', 23, ''Kota'', 2000.00 );

As we can see in the table below, both the customers have the same ID and NAME −

The SQL Alternate Key

SQL Alternate Keys in a database table are candidate keys that are not currently selected as a primary key. They can be used to uniquely identify a tuple(or a record) in a table.

There is no specific query or syntax to set the alternate key in a table. It is just a column that is a close second candidate which could be selected as a primary key. Hence, they are also called secondary candidate keys.

If a database table consists of only one candidate key, that is treated as the primary key of the table, then there is no alternate key in that table.

Let us understand the concept of alternate key with an example. Suppose we have a table named CUSTOMERS with various fields like ID, NAME, AGE, AADHAAR_ID, MOBILE_NO and SALARY as shown below.

The details like id, mobile number and aadhaar number of a customer are unique, and we can identify the records from the CUSTOMERS table uniquely using their respective fields; ID, AADHAAR_ID and MOBILE_NO. Therefore, these three fields can be treated as candidate keys.

And among them, if one is declared as the primary key of the CUSTOMERS table then the remaining two would be alternate keys.

Features of Alternate Keys

Following are some important properties/features of alternate keys −

• The alternate key does not allow duplicate values.
• A table can have more than one alternate keys.
• The alternate key can contain NULL values unless the NOT NULL constraint is set explicitly.
• All alternate keys can be candidate keys, but all candidate keys can not be alternate keys.
• The primary key, which is also a candidate key, can not be considered as an alternate key.

Example

For a better understanding, let us create the above discussed table demonstrating the usage of the various keys and illustrating the fields that can be considered as alternate keys.

CREATE TABLE CUSTOMERS(
   ID INT,
   NAME VARCHAR (20),
   AGE INT,
   AADHAAR_ID BIGINT,
   MOBILE_NO BIGINT,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

Now, insert some records into the CUSTOMERS table using the INSERT statement as shown below −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, 90123498456, 9023456789, 22000.00 ),
(2, ''Khilan'', 25, 91123249545, 9032456578, 24500.34 ),
(3, ''Kaushik'', 23, 91223242546, 9012436789, 20000.12 );

The table will be created as −

Keys in a table

As a summary lets revisit all the keys in a database table −

Candidate Key

A Candidate key is a subset of super keys that is used to uniquely identify records of a table. It can be a single field or multiple fields. The primary keys, alternate keys, foreign keys in a table are all types of candidate key.

A Primary Key is a main key that is used to retrieve records from a table. It is a single column or field in a table that uniquely identifies each record in a database table.

It can be set using the PRIMARY KEY keyword while creating a table using the CREATE TABLE statement. Following is the basic syntax to create primary key constraint on a column in a table −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY(column_name)
);

The Primary key of one table will be the Foreign key in another table. While inserting values into these tables, values in the primary key field must match the values in the foreign key field; otherwise, the foreign key column will not accept the INSERT query and throws an error.

In SQL server, the syntax to set a foreign key field in a table is −

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT fk_name
	FOREIGN KEY (column_name)
	REFERENCES referenced_table(referenced_column)
);

Alternate Key

An Alternate key is a candidate key that could be a primary key but is not. Like primary key, it also uniquely identifies the records in a field of a table to retrieve row tuples from the said table. There can be a single or multiple fields identifying as alternate keys in a table.

The SQL Indexes

SQL Indexes are special lookup tables that are used to speed up the process of data retrieval. They hold pointers that refer to the data stored in a database, which makes it easier to locate the required data records in a database table.

SQL Indexes work similar to the index of a book or a journal.

While an index speeds up the performance of data retrieval queries (SELECT statement), it slows down the performance of data input queries (UPDATE and INSERT statements). However, these indexes do not have any effect on the data.

SQL Indexes need their own storage space within the database. Despite that, the users cannot view them physically as they are just performance tools.

The CREATE INDEX Statement

An index in SQL can be created using the CREATE INDEX statement. This statement allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

Preferably, an index must be created on column(s) of a large table that are frequently queried for data retrieval.

Syntax

The basic syntax of a CREATE INDEX is as follows −

CREATE INDEX index_name ON table_name;

Types of Indexes

There are various types of indexes that can be created using the CREATE INDEX statement. They are:

• Unique Index

• Single-Column Index

• Composite Index

• Implicit Index

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. It is automatically created by PRIMARY and UNIQUE constraints when they are applied on a database table, in order to prevent the user from inserting duplicate values into the indexed table column(s). The basic syntax is as follows.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Single-Column Indexes

A single-column index is created only on one table column. The syntax is as follows.

CREATE INDEX index_name
ON table_name (column_name);

Composite Indexes

A composite index is an index that can be created on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. For example, indexes are automatically created when primary key and unique constraints are created on a table in MySQL database.

The DROP INDEX Statement

An index can be dropped using SQL DROP command. Dropping an index can effect the query performance in a database. Thus, an index needs to be dropped only when it is absolutely necessary.

The basic syntax is as follows −

DROP INDEX index_name;

When should indexes be avoided?

Although indexes are intended to enhance a database”s performance, there are times when they should be avoided.

The following guidelines indicate when the use of an index should be reconsidered.

• Indexes should not be used on small tables.

• They should not be used on tables that have frequent, large batch updates or insert operations.

• Indexes should not be used on columns that contain a high number of NULL values.

• Columns that are frequently manipulated should not be indexed.

An index is an effective way to quickly retrieve data from an SQL database. It is a database object that references the data stored in a table, which significantly improves query and application performance of a database.

The process of indexing in SQL is similar to that of an index in a book: it is a database object in the form of a table, contains details of data location, and holds a separate storage space.

Even though indexes help accelerate search queries, users are not able to directly see these indexes in action.

What is SQL Index?

An SQL index is a special lookup table that helps in efficiently searching or querying database tables to retrieve required data. For example, when we try to retrieve data from multiple tables using joins, indexes improve the query performance.

Indexes are used to optimize the query performance of any Relational Database Management System (RDBMS) as data volumes grow. Hence, they are preferred to be used on frequently queried large database tables.

Creating an SQL Index

An index can be created on one or more columns of a table in an SQL database using the CREATE INDEX statement.

Syntax

Following is the syntax of CREATE INDEX statement in SQL −

CREATE INDEX index_name
ON table_name (column_name1, column_name2,... column_nameN);

Here,

• index_name This specifies the name of the index that you want to create.
• table_name This specifies the name of the table on which you want to create the index.
• (column_name1, column_name2…column_nameN) are the names of one or more columns on which the index is being created.

Example

To create an index on a database table, we first need to create a table. So, in this example, we are creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

Then, insert some values into the CUSTOMERS table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2000),
(4, ''Chaitali'', ''25'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Once the table is created, create an index for the column named NAME in the CUSTOMERS table using the following query −

CREATE INDEX index_name ON CUSTOMERS(NAME);

Output

When we execute the above query, the output is obtained as follows −

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

Verification

The following SHOW INDEX query is used to display all the indexes created on an existing table.

SHOW INDEX FROM CUSTOMERS;

In the list obtained, you can find the column name NAME, along with the ID in the list of indexes.

Creating an Index on Multiple Fields

We can also create an index on multiple fields (or columns) of a table using the CREATE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on).

Example

Instead of creating a new table, let us consider the previously created CUSTOMERS table. Here, we are creating an index on the columns NAME and AGE using the following query −

CREATE INDEX mult_index_data on CUSTOMERS(NAME, AGE);

Output

When we execute the above query, the output is obtained as follows −

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

Verification

Now, let us list all the indexes that are created on the CUSTOMERS table using the following SHOW INDEX query −

SHOW INDEX FROM CUSTOMERS;

As you observe, you can find the column names NAME, and AGE along with ID (PRIMARY KEY), in the list of indexes.

The DROP statement in SQL is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, it will remove them permanently along with their associated data.

And when that database object is an index, the DROP INDEX statement in SQL is used.

Dropping an SQL Index

An SQL Index can be dropped from a database table using the DROP INDEX statement.

It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required.

Note − We cannot delete the indexes created by PRIMARY KEY or UNIQUE constraints. In order to delete them, you need to drop the constraints entirely using ALTER TABLE statement.

Syntax

Following is the syntax of the DROP INDEX command in SQL −

DROP INDEX index_name ON table_name;

Here,

• index_name is the name of the index that you want to drop.
• table_name is the name of the table that the index is associated with.

Example

In this example, we will learn how to drop an index on a table named CUSTOMERS, which can be created using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

Now, insert some values into the above created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2000),
(4, ''Chaitali'', ''25'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Once the table is created, create an index on the column NAME in the CUSTOMERS table using the following query −

CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

Now, verify if the index is created on the CUSTOMERS table using the following SHOW INDEX query −

SHOW INDEX FROM CUSTOMERS;

On executing the above query, the index list is displayed as follows −

Then, drop the same index INDEX_NAME in the CUSTOMERS table using the following DROP INDEX statement −

DROP INDEX INDEX_NAME ON CUSTOMERS;

Output

If we compile and run the above query, the result is produced as follows −

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

Verification

Verify if the index for the column NAME is dropped using the following query −

SHOW INDEX FROM CUSTOMERS;

In the following list of indexes, you can observe that name of the column Name is missing.

DROP INDEX with IF EXISTS

The DROP INDEX IF EXISTS statement in SQL is used to drop an index only if it exists in the table. This statement is specifically useful when you want to drop an index, but you are not sure if the index exists. This clause is not supported by MySQL.

The IF EXISTS clause ensures that the statement only removes the index if it exists. If the index does not exist, it simply terminates the execution.

Syntax

Following is the syntax of the DROP INDEX IF EXISTS in SQL −

DROP INDEX IF EXISTS index_name
ON table_name;

Here,

• index_name is the name of the index that you want to drop.
• table_name is the name of the table that the index is associated with.

Example

In this example, let us try to drop an index in the SQL Server database.

Let us consider the previously created table CUSTOMERS and let us create an index for the NAME column in the table using the following query −

CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

Then, let us drop it using the following query −

DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Output

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

Let”s verify whether the index for the NAME is dropped or not using the following query −

EXEC sys.sp_helpindex @objname = N''CUSTOMERS

As you observe, the column NAME is deleted from the list of indexes.

Example

Now, let us delete an index that doesn”t exist in the CUSTOMERS table using the following query −

DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Output

Since no indexes with the specified name exist in the database, so the above query simply terminates the execution without giving any error.

Commands completed successfully.

Removing indexes created by PRIMARY KEY or UNIQUE

The DROP INDEX statement does not drop indexes created by PRIMARY KEY or UNIQUE constraints. To drop indexes associated with them, we need to drop these constraints entirely. And it is done using the ALTER TABLE… DROP CONSTRAINT statement.

Syntax

Following is the syntax of the ALTER TABLE… DROP CONSTRAINT statement in SQL −

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Here,

• table_name is the name of the table that contains the PRIMARY KEY constraint.
• constraint_name is the name of the PRIMARY KEY constraint that you want to drop.

Example

Assume the previously created table (CUSTOMERS) and let us first list all the indexes that are created on the table using the following query −

EXEC sys.sp_helpindex @objname = N''CUSTOMERS

The list is displayed as follows −

Here, the PK__CUSTOMER__3214EC27CB063BB7 is the name of the PRIMARY KEY constraint that was created on the ID column of the CUSTOMERS table.

Now, let us drop the index created by the PRIMARY KEY constraint.

ALTER TABLE customers
DROP CONSTRAINT PK__CUSTOMER__3214EC27CB063BB7;

Output

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

Verify whether it is dropped or not by listing the existing indexes using the following query −

EXEC sys.sp_helpindex @objname = N''CUSTOMERS

The following error is displayed because the list of indexes is empty.

The object ''CUSTOMERS'' does not have any indexes, or you do not have permissions.

The SQL Show Index Statement

The SHOW INDEX is the basic SQL statement to retrieve the information about the indexes that have been defined on a table. However, the SHOW INDEX statement only works on MySQL RDBMS and is not a valid statement in the SQL Server.

To list the indexes created on a table in SQL Server, a system stored procedure sp_helpindex is used.

The result-set obtained from querying the SHOW INDEX statement on a MySQL table contains the index information.

Syntax

Following is the syntax of the SHOW INDEX statement in MySQL −

SHOW INDEX FROM table_name;

Example

Following example demonstrates the working of SHOW INDEX statement in MySQL. First, create a table with the name CUSTOMERS in the MySQL database using the CREATE query below −

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

Let us now insert some values into the above created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2000),
(4, ''Chaitali'', ''25'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Once the data is inserted, create an index for the column NAME in the CUSTOMERS table using the following query −

CREATE INDEX INDEX_NAME ON CUSTOMERS(NAME);

Now, you can list all the indexes that are defined on the CUSTOMERS table using the following query −

SHOW INDEX FROM CUSTOMERS;

Output

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

Showing Indexes in SQL Server

In SQL server, the system stored procedure sp_helpindex is used to retrieve the information about the indexes that have been defined on a table. It returns the result as a table that contains detailed information about each index, including the name, type, and columns.

Syntax

Following is the basic syntax to list indexes defined on a table in SQL Server −

sp_helpindex [ @objname = ] ''name''

Here, [ @objname = ] ”name” specifies the name of the table for which the index information is being retrieved. The index information includes −

• index_name is the names of the columns that are included in index.
• index_description is the brief description of the index such as the type of index (like clustered or non-clustered).
• index_keys is the keys that are included in the index.

Example

CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

Now, let us list all the indexes that are created on the CUSTOMERS table using the system stored procedure sp_helpindex as shown below −

EXEC sys.sp_helpindex @objname = N''CUSTOMERS

Output

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