Year: 2025

Wildcards

SQL Wildcards

SQL Wildcards are special characters used as substitutes for one or more characters in a string. They are used with the LIKE operator in SQL, to search for specific patterns in character strings or compare various strings.

The LIKE operator in SQL is case-sensitive, so it will only match strings that have the exact same case as the specified pattern.

Following are the most commonly used wildcards in SQL −

S.No.	Wildcard & Description
1	The percent sign (%)Matches one or more characters.Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.
2	The underscore (_)Matches one character.Note − MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

The percent sign (%) represents zero, one, or multiple characters within a string. The underscore (_) represents a single character or number. These symbols can also be used in combination to perform complex pattern searching and matching in SQL queries.

Syntax

Following is the basic syntax to use wildcard characters −

SELECT*FROM table_name
WHERE column_name LIKE[wildcard_pattern];

We can combine N number of conditions using the AND or the OR operators. Here, the [wildcard_pattern] can represent any numeric or string value.

The following table demonstrates various ways of using wildcards in conjunction with the LIKE operator within a WHERE clause:

S.No.	Statement & Description
1	WHERE SALARY LIKE ‘200%’Finds any values that start with 200.
2	WHERE SALARY LIKE ‘%200%’Finds any values that have 200 in any position.
3	WHERE SALARY LIKE ‘_00%’Finds any values that have 00 in the second and third positions.
4	WHERE SALARY LIKE ‘2_%_%’Finds any values that start with 2 and are at least 3 characters in length.
5	WHERE SALARY LIKE ‘%2’Finds any values that end with 2.
6	WHERE SALARY LIKE ‘_2%3’Finds any values that have a 2 in the second position and end with a 3.
7	WHERE SALARY LIKE ‘2___3’Finds any values in a five-digit number that start with 2 and end with 3.

Example

Firstly, let us create a table named CUSTOMERS using the following query −

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

The following INSERT query adds records into the CUSTOMERS table −

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

Here, we are displaying all the records from the CUSTOMERS table where the SALARY starts with 200.

SELECT*FROM CUSTOMERS WHERE SALARY LIKE'200%';

Output

This would produce the following result.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00

August 18, 2025

Two-Way Data Binding
Two-Way Data Binding in Angular

Two-way data binding is a two-directional interaction in Angular. In this type of data binding, data flows in both ways, from the component to views and from the views back to the component. When you bind a value into an element then, two-way binding gives that element the ability to apply changes back to the source. It is the combination of property and event binding.

The term Data binding refers to a mechanism that allows flow of data between component class to template and template to component class.

Implementing Two-Way Binding

We can use the following ways to implement two-way binding in Angular −
- Using ngModel
- Using custom event and property binding
Two-Way Binding using ngModel

The ngModel is a directive in Angular that is used for two-way data binding between the component class and the HTML template. It helps to sync the data between the model in the component and the view in template. This directive is commonly used on form controls, such as <input>, <textarea>, and <select> to bind them to a component property.

When the user interacts with the input field of a form, the value of the input is automatically updated in the component property. Similarly, if the component property is changed by any means, the input field is automatically updated to reflect the new value.

Syntax

For two-way binding using ngModel, we use ‘banana-in-a-box syntax’ which is shown below −
```
<HTML[(ngModel)]="model.name"/>
```
Where,
- [(ngModel)]: The parentheses () represent the event binding and the square brackets [] represent property binding.
- model.name: This is the component property that will be linked to the form control.
Example

In this example, we are demonstrating two-way data binding in Angular. We bind a form control to a component property using the ngModel directive. This will allow the input field to update the component’s user property and reflect any changes made to this property in the view immediately.

Step 1: Create an Angular application named formApp using the command given below −
```
ng newformApp
```
Step 2: Import FormsModule first and then define a variable user inside src/app/app.component.ts. FormModule will do the necessary setup to enable two-way data binding.
```
import{ Component }from'@angular/core';import{ RouterOutlet }from'@angular/router';import{ FormsModule }from'@angular/forms';

@Component({
  selector:'app-root',
  standalone:true,
  imports:[RouterOutlet, FormsModule],
  templateUrl:'./app.component.html',
  styleUrl:'./app.component.css'})exportclassAppComponent{
  title ='Two Way Binding';
  user: string ='';}
```
Step 3: Update the view template, app.component.html as mentioned below −
```
<h3>{{ title }}</h3><input type="text"[(ngModel)]="user"/><p>Welcome {{ user }}!</p><router-outlet />
```
Here,

The user property is bind to form control using ngModel directive. If you enter any text in the input box, it will update the user property.

Finally, start your application using the below command −
```
ng serve
```
After running your application, you could see a response as given below −

Try to change the input value to any string of your choice. You will get the output as per the given input.

NOTE: We will learn more about form controls in the upcoming chapters.

Two-Way Binding using custom Event and Property Binding

As mentioned earlier, if we combine event and property binding, we can achieve two-way binding. It is mainly used when we need to establish two-directional interaction between a parent and its child component. Compare to ngModel, using custom event and property binding requires more configuration.

To enable two-way binding between components, the @Input() property and corresponding @Output property along with an event emitter method that updates the value of the @Input() needs to defined inside child component.

Inside the parent component, wrap the @Input() property name in the ‘banana-in-a-box syntax’ and specify the corresponding property to which the updated value is assigned.

Example

In this example, we are illustrating two-way data binding by combining event and property binding together.

Step 1: Create an Angular application named customApp using the command given below −
```
ng newcustomApp
```
Step 2: Next, generate a child component named child-component.
```
ng generate component child-component
CREATE src/app/child-component/child-component.component.html(31 bytes)CREATE src/app/child-component/child-component.component.spec.ts(672 bytes)CREATE src/app/child-component/child-component.component.ts(281 bytes)CREATE src/app/child-component/child-component.component.css(0 bytes)
```
Step 3: Open child-component.component.ts file and add the following code −
```
import{ Component, EventEmitter, Input, Output }from'@angular/core';import{ FormsModule }from'@angular/forms';

@Component({
  selector:'app-child-component',
  standalone:true,
  imports:[FormsModule],
  template:'<input [value]="val" (input)="onValChange($event)" />',
  styleUrls:['./child-component.component.css']})exportclassChildComponentComponent{
  @Input() val: string ='';
  @Output() valChange: EventEmitter<string>=newEventEmitter<string>();onValChange(event: Event){// Cast the event target to HTMLInputElement and access the valueconst input = event.target as HTMLInputElement;if(input){this.valChange.emit(input.value);}}}
```
Step 4: Add the following code inside app.component.ts file −
```
import{ Component }from'@angular/core';import{ FormsModule }from'@angular/forms';import{ RouterOutlet }from'@angular/router';import{ ChildComponentComponent }from'./child-component/child-component.component';

@Component({
  selector:'app-root',
  standalone:true,
  imports:[RouterOutlet, FormsModule, ChildComponentComponent],
  template:`<h3>{{ title }}</h3>
         &lt;app-child-component [(val)]="user"&gt;&lt;/app-child-component&gt; 
         &lt;p&gt;Hello {{ user }}!&lt;/p&gt;`,  styleUrl:'./app.component.css'})exportclassAppComponent{
  title ='Two Way Binding Example';
  user: string ="";}
```
Now, start your application using the below command −
```
ng serve
```
After running your application, you could see the following output −
August 18, 2025
Style Binding
What is Style Binding?

A dynamic web application usually have dynamic styles and are set during the runtime of the application. Style binding is a special binding to bind a value to the style attribute of a HTML element dynamically.

Let us see more details about style binding in this chapter.

Ways to use Style Binding

Angular provides four different ways to implement style binding. Each type of style binding supports a special feature. The four ways are as follows:
Let us learn one by one in the upcoming sections.

Single Style Binding

In single style binding, the property name of a CSS style should be appended to style. string and should be surrounded by square bracket. For example, the width of a HTML element can be set using [style.width] as shown below −
```
<div [style.width]="<template variable>"></div>
```
Single Style Binding with Unit

In single style binding with unit, the property name of a CSS style should be appended to style. string, the unit (.px) should be appended to the property name of a CSS style and should be surrounded by square bracket. For example, the width of a HTML element in px using can be set using [style.width.px] as shown below −
```
<div [style.width.px]="<template variable>"></div>
```
Multiple Style Binding

In multiple style binding, style string should be surrounded by square bracket and the value should have proper CSS styles. For example, the width and height of a HTML element can be set using [style] as shown below −
```
<div [style]="<template variable>"></div>
```
Here, an example output of the template variable is width: 100px; height: 200px

Multiple Style Binding with Object

In multiple style binding with object, style string should be surrounded by square bracket and the value should be set with an object of type Record<string, string having keys and values with proper CSS property name (or converted to camelCase) and value respectively. For example, the width and height of a HTML element can be set using [style] as shown below −
```
<div [style]="<objects as template variable>"></div>
```
Here, an example object is as follows,
```
{width:'100px',height:'100px'}
```
Implementing Style Binding

Let us create a simple registration form to understand attribute binding. Our registration form will have three input field as shown below and a button to submit the registration form.
```
1. Username
2. Password
3. Confirm password
```
Step 1: Create a new application, my-app using angular CLI as shown below −
```
ng newmy-app
```
Step 2: Create a new registration form component, RegisterForm using angular CLI as shown below −
```
ng generate component RegisterForm
```
Step 3: Next, open the registration form component’s template and add a form with username, password and confirm password.
```
<div><form method="post"><div class="container"><label for="username"><b>Username</b></label><input type="text" name="username" required><label for="password"><b>Password</b></label><input type="password" name="password" required><label for="confirm_password"><b>Confirm Password</b></label><input type="password" name="confirm_password" required><button type="submit">Register</button></div></form></div>
```
Step 4: Open the registration form component’s CSS style file and style the form using CSS as shown below −
```
.container{padding: 15px;}input[type=text], input[type=password]{width: 100%;padding: 10px 20px;margin: 10px 0;display: inline-block;border: 1px solid #ccc;box-sizing: border-box;}button{background-color: blue;color: white;padding: 15px 20px;margin: 10px 0;border: none;cursor: pointer;width: 100%;}
```
Step 5: Include our registration form component in the app template file, app.component.html:
```
<app-register-form />
```
Step 6: Run the application and test the registration form.

Step 7: Next, let us try to apply the style for the button using style binding.

Step 8: Add an object in the component with necessary values as shown below −
```
btnStyle: Record<string, string> ={'backgroundColor':'purple','color':'white','padding':'15px 20px','margin':'10px 0','border':'none','cursor':'pointer','width':'100%'}
```
Here, we have changed the background color of the button from blue to purple. Also, note that the name of the background color style property, background-color is in camelCase, backgroundColor.

Step 9: Next, remove the button style in the components style file.

Step 10: Next, assign the style object to the button through style binding.
```
<button type="submit"[style]="btnStyle">Register</button>
```
Step 11: The complete listing of the component is as follows:
```
import{ Component }from'@angular/core';

@Component({
   selector:'app-login-form',
   templateUrl:'./register-form.component.html',
   styleUrls:['./register-form.component.css']})exportclassRegisterFormComponent{
   btnStyle: Record<string, string>={'backgroundColor':'purple','color':'white','padding':'15px 20px','margin':'10px 0','border':'none','cursor':'pointer','width':'100%'}}
```
Step 12: The complete listing of the component’s template is as follows:
```
<div><form method="post"><div class="container"><label for="username"><b>Username</b></label><input type="text" name="username" required><label for="password"><b>Password</b></label><input type="password" name="password" required><label for="confirm_password"><b>Confirm Password</b></label><input type="password" name="confirm_password" required><button type="submit" [style]="btnStyle" >Register</button></div></form></div>
```
Step 13: Run the application and check the output.
August 18, 2025
NonClustered Index
SQL Non-Clustered Indexes

The SQL Non-Clustered index is similar to the Clustered index. When defined on a column, it creates a special table which contains the copy of indexed columns along with a pointer that refers to the location of the actual data in the table. However, unlike Clustered indexes, a Non-Clustered index cannot physically sort the indexed columns.

Following are some of the key points of the Non-clustered index in SQL −
- The non-clustered indexes are a type of index used in databases to speed up the execution time of database queries.
- These indexes require less storage space than clustered indexes because they do not store the actual data rows.
- We can create multiple non-clustered indexes on a single table.
MySQL does not have the concept of Non-Clustered indexes. The PRIMARY KEY (if exists) and the first NOT NULL UNIQUE KEY(if PRIMARY KEY does not exist) are considered clustered indexes in MySQL; all the other indexes are called Secondary Indexes and are implicitly defined.

To get a better understanding, look at the following figure illustrating the working of non-clustered indexes −

Assume we have a sample database table with two columns named ID and NAME. If we create a non-clustered index on a column named ID in the above table, it will store a copy of the ID column with a pointer that points to the specific location of the actual data in the table.

Syntax

Following is the syntax to create a non-clustered index in SQL Server −
```
CREATENONCLUSTEREDINDEX index_name 
ON table_name (column_name)
```
Here,
- index_name: holds the name of non-clustered index.
- table_name: holds the name of the table where you want to create the non-clustered index.
- column_name: holds the name of the column that you want to define the non-clustered index on.
Example

Let us create a table named CUSTOMERS using the following query −
```
CREATETABLE CUSTOMERS(
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(20,2),);
```
Let us insert some values into the above-created table using the following query −
```
INSERTINTO CUSTOMERS VALUES(7,'Muffy','24','Indore',5500),(1,'Ramesh','32','Ahmedabad',2000),(6,'Komal','22','Hyderabad',9000),(2,'Khilan','25','Delhi',1500),(4,'Chaitali','25','Mumbai',6500),(5,'Hardik','27','Bhopal',8500),(3,'Kaushik','23','Kota',2000);
```
The table is successfully created in the SQL database.

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

Now, let us create a non-clustered index on a single column named ID using the following query −
```
CREATENONCLUSTEREDINDEX NON_CLU_ID 
ON customers (ID ASC);
```
Output

On executing the above query, the output is displayed as follows −
```
Commands Completed Successfully.
```
Verification

Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query −
```
EXEC sys.sp_helpindex @objname= N'CUSTOMERS';
```
As we observe, we can find the column named ID in the list of indexes.

index_name index_description index_keys
1 NON_CLU_ID nonclustered located on PRIMARY ID

Now, retrieve the CUSTOMERS table again using the following query to check whether the table is sorted or not −
```
SELECT*FROM CUSTOMERS;
```
As we observe, the non-clustered index does not sort the rows physically instead, it creates a separate key-value structure from the table data.

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

Creating Non-Clustered Index on Multiple Columns

Instead of creating a new table, let us consider the previously created CUSTOMERS table. Now, try to create a non-clustered index on multiple columns of the table such as ID, AGE and SALARY using the following query −
```
CREATENONCLUSTEREDINDEX NON_CLUSTERED_ID 
ON CUSTOMERS (ID, AGE, SALARY);
```
Output

The below query will create three separate non-clustered indexes for ID, AGE, and SALARY.
```
Commands Completed Successfully.
```
Verification

Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query −
```
EXEC sys.sp_helpindex @objname= N'CUSTOMERS';
```
As we observe, we can find the column names ID, AGE and SALARY columns in the list of indexes.

index_name index_description index_keys
1 NON_CLU_ID nonclustered located on PRIMARY ID, AGE, SALARY
August 18, 2025
Clustered Index
An index in a database is a data structure that helps to improve the speed of retrieving specific data from tables and views.

Data in a table is stored in the form of an unordered data structure called a “Heap”, where rows are placed without any specific order. Thus, when retrieving data from a table, the query optimizer must scan the entire table to locate the requested rows. This process can be time-consuming, especially when we are dealing with large tables. To speed up the data retrieval, SQL provides a data object called index that stores and organizes table data in a specific way, allowing faster data access.

SQL Clustered Indexes

A clustered index in SQL is a type of index that determines the physical order in which the data values will be stored in a table.

When a clustered index is defined on a specific column, during the creation of a new table, the data is inserted into that column in a sorted order. This helps in faster retrieval of data since it is stored in a specific order.
- It is recommended to have only one clustered index on a table. If we create multiple clustered indexes on the same table, the table will have to store the same data in multiple orders which is not possible.
- When we try to create a primary key constraint on a table, a unique clustered index is automatically created on the table. However, the clustered index is not the same as a primary key. A primary key is a constraint that imposes uniqueness on a column or set of columns, while a clustered index determines the physical order of the data in the table.
MySQL database does not have a separate provisions for Clustered and Non-Clustered indexes. Clustered indexes are automatically created when PRIMARY KEY is defined on a table. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is treated as a Clustered index.

Syntax

Following is the syntax to create a clustered index with SQL Server −
```
CREATEINDEX index_name ON table_name(column_name [asc|desc])
```
Where,
- index_name: specifies the name you want to give to the index being created.
- column_name: specifies the column(s) that will be indexed in the order specified.
- asc|desc: specifies the order (asc – ascending, desc – descending) in which the data should be sorted. The default sorting order is ascending order.
Example

In this example, let us create a clustered index on a table in SQL Server. For that, we need to first create a table named CUSTOMERS using the following query −
```
CREATETABLE CUSTOMERS( 
   ID INTNOTNULL, 
   NAME VARCHAR(20)NOTNULL, 
   AGE  INTNOTNULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(20,2));
```
Now, insert some values into the CUSTOMERS table using the following query −
```
INSERTINTO CUSTOMERS VALUES(7,'Muffy','24','Indore',5500),(1,'Ramesh','32','Ahmedabad',2000),(6,'Komal','22','Hyderabad',9000),(2,'Khilan','25','Delhi',1500),(4,'Chaitali','25','Mumbai',6500),(5,'Hardik','27','Bhopal',8500),(3,'Kaushik','23','Kota',2000);
```
The table is successfully created in the SQL Server database.

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

Now, let us create a clustered index on the column named ID using the following query −
```
CREATECLUSTEREDINDEX CLU_ID ON CUSTOMERS(ID ASC);
```
Output

On executing the above query, the output is displayed as follows −
```
Commands Completed Successfully.
```
Verification

To verify if the clustered index is defined on ID column, check whether the records of CUSTOMERS table are sorted by retrieving them using the following query −
```
SELECT*FROM CUSTOMERS;
```
The records of the table are sorted in ascending order based on values in the column named ID.

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

Creating Clustered Index on Multiple Columns

With the following example, let us understand how clustered index works when it is created on multiple columns of a table.

Instead of creating a new table, consider the previously created CUSTOMERS table and define a clustered index on multiple columns of this table, such as AGE and SALARY, using the following query −
```
CREATECLUSTEREDINDEX MUL_CLUS_ID 
ON CUSTOMERS (AGE, SALARY ASC);
```
Output

When we execute the above query, the output is obtained as follows −
```
Commands Completed Successfully.
```
Verification

Now, let us verify whether the values in the columns AGE and SALARY is sorted or not −
```
SELECT*FROM CUSTOMERS;
```
As we can observe in the table below, the records are sorted only based on the values in AGE column and not with the values in SALARY column. So, it is recommended to have only one clustered index on a table.

ID NAME AGE ADDRESS SALARY
6 Komal 22 Hyderabad 9000.00
3 Kaushik 23 Kota 2500.00
7 Muffy 24 Indore 5500.00
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
1 Ramesh 32 Ahmedabad 2000.00
August 18, 2025
Unique Indexes
SQL Unique Indexes

The SQL Unique Index ensures that no two rows in the indexed columns of a table have the same values (no duplicate values allowed).

A unique index can be created on one or more columns of a table using the CREATE UNIQUE INDEX statement in SQL.

Following are the points to be noted before creating a Unique Index on a table −
- If the unique index is only created on a single column, the rows in that column will be unique.
- If a single column contains NULL in multiple rows, we cannot create a unique index on that column.
- If the unique index is created on multiple columns, the combination of rows in these columns will be unique.
- We cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row.
Syntax

Following is the syntax for creating a UNIQUE INDEX in SQL −
```
CREATEUNIQUEINDEX index_name
ON table_name (column1, column2,..., columnN);
```
Here,
- index_name is the name of the index that you want to create.
- table_name is the name of the table on which you want to create the index.
- (column1, column2, …., columnN) are the names of one or more columns on which the unique index is being created.
Example

First of all, let us create a table named CUSTOMERS using the following query −
```
CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(15)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10,4),PRIMARYKEY(ID));
```
Insert some values into the above-created table using the following query −
```
INSERTINTO CUSTOMERS VALUES(1,'Ramesh','32','Ahmedabad',2000),(2,'Khilan','25','Delhi',1500),(3,'kaushik','23','Kota',2000),(4,'Chaitali','26','Mumbai',6500),(5,'Hardik','27','Bhopal',8500),(6,'Komal','22','Hyderabad',9000),(7,'Muffy','24','Indore',5500);
```
Once the table is created, let us create a unique index for the column named SALARY in the CUSTOMERS table using the following query −
```
CREATEUNIQUEINDEX UNIQUE_ID ON CUSTOMERS (SALARY);
```
But, when we execute the above query, the output is obtained as follows −
```
ERROR 1062 (23000): Duplicate entry '2000.00' for key 'customers.UNIQUE_ID'
```
Since a unique index could not be created on SALARY column (due to duplicate values), let us create Unique Index on the NAME column of the same table, using the following query −
```
CREATEUNIQUEINDEX UNIQUE_ID ON CUSTOMERS (NAME);
```
Output

When we execute the above query, the output is obtained as follows −
```
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
```
Verification

Let’s verify whether the unique index for the column NAME is created or not using the following query −
```
SHOWINDEXFROM CUSTOMERS;
```
As you observe the output below, you can find the column NAME along with the ID (PRIMARY KEY) in the list of indexes.

Table Non_unique Key_name Seq_in_index Column_name
customers 0 PRIMARY 1 ID
customers 0 UNIQUE_ID 1 NAME

Updating with Duplicate Values

If we try to update the columns that have unique index with duplicate values, the database engine generates an error.

Example

Assume the previously created CUSTOMERS table and create a unique index on the column named ADDRESS using the following query −
```
CREATEUNIQUEINDEX ADD_UNIQUE_INDEX ON CUSTOMERS(ADDRESS);
```
Now, let us update the value in the column named ADDRESS with a duplicate (already existing data) value using the following query −
```
UPDATE CUSTOMERS SET ADDRESS ='Mumbai'WHERE ADDRESS ='Delhi';
```
Output

On executing the above query, the output is displayed as follows −
```
ERROR 1062 (23000): Duplicate entry 'Mumbai' for key 'customers.ADD_UNIQUE_INDEX'
```
Creating a unique index on Multiple Fields

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

Example

Instead of creating a new table, let us consider the previously created CUSTOMERS table. We will create a unique index on the columns NAME and AGE using the following query −
```
CREATEUNIQUEINDEX MUL_UNIQUE_INDEX 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 query −
```
SHOWINDEXFROM CUSTOMERS;
```
As you observe you can find the column names NAME, and AGE along with the ID (PRIMARY KEY) in the list of indexes.

Table Non_unique Key_name Seq_in_index Column_name
customers 0 PRIMARY 1 ID
customers 0 MUL_UNIQUE_INDEX 1 NAME
customers 0 MUL_UNIQUE_INDEX 2 AGE
August 18, 2025
Show Indexes
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 −
```
SHOWINDEXFROM 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 −
```
CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(20,2),PRIMARYKEY(ID));
```
Let us now insert some values into the above created table using the following query −
```
INSERTINTO 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 −
```
CREATEINDEX INDEX_NAME ON CUSTOMERS(NAME);
```
Now, you can list all the indexes that are defined on the CUSTOMERS table using the following query −
```
SHOWINDEXFROM CUSTOMERS;
```
Output

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

Table Non_unique Key_name Seq_in_index Column_name
customers 0 PRIMARY 1 ID
customers 1 index_name 1 NAME

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
```
CREATEINDEX 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 −

index_name index_description index_keys
INDEX_NAME nonclustered located on PRIMARY NAME
PK__CUSTOMER__ 3214EC27755869D9 clustered, unique, primary key located on PRIMARY ID
August 18, 2025
Drop Index
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 −
```
DROPINDEX 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 −
```
CREATETABLE CUSTOMERS(
   ID INTNOTNULL,
   NAME VARCHAR(15)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10,4),PRIMARYKEY(ID)););
```
Now, insert some values into the above created table using the following query −
```
INSERTINTO 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 −
```
CREATEINDEX INDEX_NAME on CUSTOMERS(NAME);
```
Now, verify if the index is created on the CUSTOMERS table using the following SHOW INDEX query −
```
SHOWINDEXFROM CUSTOMERS;
```
On executing the above query, the index list is displayed as follows −

Table Non_unique Key_name Seq_in_index Column_name
customers 0 PRIMARY 1 ID
customers 1 index_name 1 NAME

Then, drop the same index INDEX_NAME in the CUSTOMERS table using the following DROP INDEX statement −
```
DROPINDEX 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 −
```
SHOWINDEXFROM CUSTOMERS;
```
In the following list of indexes, you can observe that name of the column Name is missing.

Table Non_unique Key_name Seq_in_index Column_name
customers 0 PRIMARY 1 ID

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 −
```
DROPINDEXIFEXISTS 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 −
```
CREATEINDEX INDEX_NAME on CUSTOMERS(NAME);
```
Then, let us drop it using the following query −
```
DROPINDEXIFEXISTS 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.

index_name index_description index_keys
PK__CUSTOMER__3214EC27CB063BB7 clustered, unique, primary key locatedPRIMARY on PRIMARY ID

Example

Now, let us delete an index that doesn’t exist in the CUSTOMERS table using the following query −
```
DROPINDEXIFEXISTS 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 −
```
ALTERTABLE table_name
DROPCONSTRAINT 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 −

index_name index_description index_keys
PK__CUSTOMER__3214EC27CB063BB7 nonclustered located on PRIMARYID ID

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.
```
ALTERTABLE customers
DROPCONSTRAINT 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.
```
August 18, 2025
Create Index
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 −
```
CREATEINDEX 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 −
```
CREATETABLE CUSTOMERS(
   ID INTNOTNULL,
   NAME VARCHAR(15)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10,4),PRIMARYKEY(ID)););
```
Then, insert some values into the CUSTOMERS table using the following query −
```
INSERTINTO 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 −
```
CREATEINDEX 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.
```
SHOWINDEXFROM CUSTOMERS;
```
In the list obtained, you can find the column name NAME, along with the ID in the list of indexes.

Table Non_unique Key_name Seq_in_index Column_name
customers 0 PRIMARY 1 ID
customers 1 index_name 1 NAME

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 −
```
CREATEINDEX 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 −
```
SHOWINDEXFROM CUSTOMERS;
```
As you observe, you can find the column names NAME, and AGE along with ID (PRIMARY KEY), in the list of indexes.

Table Non_unique Key_name Seq_in_index Column_name
customers 0 PRIMARY 1 ID
customers 1 index_name 1 NAME
customers 1 mult_index_data 1 NAME
customers 1 mult_index_data 2 AGE
August 18, 2025
Class and Style Binding
What is Class Binding?

A dynamic web application usually have dynamic styles and are set during the runtime of the application. Class binding is a special binding to bind a dynamic value to the class attribute of a HTML element.

Let us see more details about class binding in this chapter.

Ways to use Class Binding

Angular provides four different ways to implement class binding. Each of them supports a special feature. The four ways are as follows:
Let us learn one by one in the upcoming sections.

Single Class Binding

In single class binding, class string should be surrounded by square bracket and a template variable should be set as it’s value.
```
<div [class]="<template variable>"></div>
```
Here, the template variable holds the class name for the specific HTML element.

Single Class Binding with on/off Feature

In single class binding with on/off feature, class style should be appended by the actual class name of the given HTML element and a template variable with boolean value should be set as it’s value. The boolean value determines the availability of the specific class to the HTML element.
```
<div [class.<class name>]="template variable">
   </div>
```
Here, the template variable outputs either true or false.

Let us consider a class with name red, used to set the text of the HTML element to red color.
```
.red{color: red;}
```
Consider a member variable, isRedEnabled available in the component.
```
isRedEnabled: boolean =true
```
Then, the class binding can be set in a HTML element as shown below −
```
<div [class.red]="isRedEnabled"></div>
```
Multiple Class Binding

In multiple class binding, class string should be surrounded by square bracket and the value should be set with one of more existing class name separated by space. For example, two class (myClass and myAnotherClass) for a HTML element can be set using [class] as shown below −
```
<div [class]="<template variable>"></div>
```
Here, the template variable will emit myClass myAnotherClass string.

Multiple Class Binding through an Object with on/off Feature

In multiple class binding through an object with on/off feature, class string should be surrounded by square bracket and the value should be set with an object of type Record<string, boolean> having keys and values with class name and boolean value respectively. The boolean value of a key determine whether the corresponding key will be set a class of the given HTML element.
```
<div [class]="<objects as template variable>"></div>
```
Let as consider an object with multiple keys representing class name and have boolean values as shown below −
```
// in component
myClass: Record<string, boolean>={ 
   c1:true,
   c2:false
   c3:true}
```
Apply the class binding in the template as shown below −
```
// in template
<div [class]="myClass"></div>
```
Then the output will have c1 and c3 class because both of these classes have true value in the object.
```
// output
<div class="c1 c3"></div>
```
Implementing Class Binding

Let us create a simple registration form to understand class binding. Our registration form will have three input field as shown below and a button to submit the registration form.
```
1. Username
2. Password
3. Confirm password
```
Step 1: Create a new application, my-app using angular CLI as shown below −
```
ng newmy-app
```
Step 2: Create a new registration form component, RegisterForm using angular CLI as shown below −
```
ng generate component RegisterForm
```
Step 3: Next, open the registration form component’s template and add a form with username, password and confirm password.
```
<div><form method="post"><div class="container"><label for="username"><b>Username</b></label><input type="text" name="username" required><label for="password"><b>Password</b></label><input type="password" name="password" required><label for="confirm_password"><b>Confirm Password</b></label><input type="password" name="confirm_password" required><button type="submit">Register</button></div></form></div>
```
Step 4: Open the registration form component’s CSS style and style the form using CSS as shown below −
```
.container{padding: 15px;}input[type=text], input[type=password]{width: 100%;padding: 10px 20px;margin: 10px 0;display: inline-block;border: 1px solid #ccc;box-sizing: border-box;}button{background-color: blue;color: white;padding: 15px 20px;margin: 10px 0;border: none;cursor: pointer;width: 100%;}
```
Step 5: Include our registration form component in the app template file, app.component.html
```
<app-register-form />
```
Step 6: Run the application and test the registration form.

Step 7: Next, let us create few classes in the style file and apply our new class for the button using class binding.

Step 8: Next, add two class, purple and smallcaps in the component’s style file.
```
.purple{background-color: purple;}.smallcaps{font-variant: small-caps;}
```
Step 9: Add a member variable, isPurple in the component as shown below −
```
isPurple: boolean =true
```
Step 10: Next, add an object in the component with purple and smallcaps class as keys as shown below −
```
btnClass: Record<string, boolean>={'purple':true,'smallcaps':true}
```
Step11: Next, assign the variable, isPurple to the button through class binding.
```
<button type="submit"[class.purple]="isPurple">Register</button>
```
Step12: Run the application and check the output. Output will show the button with purple color.

Step13: Next, reassign the object, btnClass to the buttons class through class binding.
```
<button type="submit"[class]="btnClass">Register</button>
```
Here, both purple and small caps will be applied.

Step14: Run the application and check the output. Output will show the button with purple color and Register text is small caps format.

Step15: The complete listing of the component is as follows,
```
import{ Component }from'@angular/core';

@Component({
   selector:'app-login-form',
   templateUrl:'./register-form.component.html',
   styleUrls:['./register-form.component.css']})exportclassRegisterFormComponent{
   isPurple: boolean =true
   
   btnClass: Record<string, boolean>={'purple':true,'smallcaps':true}}
```
Step16: The complete listing of the component’s template is as follows,
```
<div><form method="post"><div class="container"><label for="username"><b>Username</b></label><input type="text" name="username" required><label for="password"><b>Password</b></label><input type="password" name="password" required><label for="confirm_password"><b>Confirm Password</b></label><input type="password" name="confirm_password" required><button type="submit" [class]="btnClass">Register</button></div></form></div>
```
Style binding

Style binding in Angular allows you to dynamically set inline CSS styles on an HTML element based on the component’s properties or template variables.

Syntax of Style Binding

Angular provides four different syntax in style binding. Each type of style binding supports a special feature. The four syntax as are follows:
- Single style binding
- Single style binding with unit
- Multiple style binding
- Multiple style binding through a custom style object
August 18, 2025

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

Table	Key_name	Seq_in_index	Column_name
customers	PRIMARY	1	ID
customers	MUL_UNIQUE_INDEX	1	NAME
customers	MUL_UNIQUE_INDEX	2	AGE

index_name	index_description	index_keys
INDEX_NAME	nonclustered located on PRIMARY	NAME
PK__CUSTOMER__ 3214EC27755869D9	clustered, unique, primary key located on PRIMARY	ID

Year: 2025

SQL Wildcards

Syntax

Example

Output

Two-Way Data Binding in Angular

Implementing Two-Way Binding

Two-Way Binding using ngModel

Syntax

Example

Two-Way Binding using custom Event and Property Binding

Example

What is Style Binding?

Ways to use Style Binding

Single Style Binding

Single Style Binding with Unit

Multiple Style Binding

Multiple Style Binding with Object

Implementing Style Binding

SQL Non-Clustered Indexes

Syntax

Example

Output

Verification

Creating Non-Clustered Index on Multiple Columns

Output

Verification

SQL Clustered Indexes

Syntax

Example

Output

Verification

Creating Clustered Index on Multiple Columns

Output

Verification

SQL Unique Indexes

Syntax

Example

Output

Verification

Updating with Duplicate Values

Example

Output

Creating a unique index on Multiple Fields

Example

Output

Verification

The SQL Show Index Statement

Syntax

Example

Output

Showing Indexes in SQL Server

Syntax

Example

Output

Dropping an SQL Index

Syntax

Example

Output

Verification

DROP INDEX with IF EXISTS

Syntax

Example

Output

Verification

Example

Output

Removing indexes created by PRIMARY KEY or UNIQUE

Syntax

Example

Output

Verification

What is SQL Index?

Creating an SQL Index

Syntax

Example

Output

Verification

Creating an Index on Multiple Fields

Example