Category: Advanced SQL

Transactions
SQL Transactions

A transaction is a unit or sequence of work that is performed on a database. Transactions are accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors.

Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID.
- Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
- Consistency − ensures that the database properly changes states upon a successfully committed transaction.
- Isolation − enables transactions to operate independently of and transparent to each other.
- Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.
Transactional Control Commands

Transactional control commands are only used with the DML Commands such as – INSERT, UPDATE and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. Following commands are used to control transactions.
- COMMIT − to save the changes.
- ROLLBACK − to roll back the changes.
- SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
- SET TRANSACTION − Places a name on a transaction.
The COMMIT Command

The COMMIT command is the transactional command used to save changes invoked by a transaction. It saves all the transactions occurred on the database since the last COMMIT or ROLLBACK.

The syntax for the COMMIT command is as follows.
```
COMMIT;
```
Example

Firstly, let us create a table names CUSTOMERS using the following query −
```
CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));
```
We are inserting some records into the above-created 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 follows −

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

Following query would delete those records from the table which have AGE as 25 and then COMMIT the changes in the database.
```
DELETEFROM CUSTOMERS WHERE AGE =25;COMMIT;
```
Verification

The two rows from the table would be deleted and if you verify the contents of the CUSTOMERS table using the SELECT statement as −
```
SELECT*FROM CUSTOMERS;
```
The table will be displayed as follows −

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

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only undo transactions since the last COMMIT or ROLLBACK.

The syntax for a ROLLBACK command is as follows −
```
ROLLBACK;
```
Example

Consider the CUSTOMERS table having the following records −

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

Following query would delete those records from the table where the AGE value is 25 and then ROLLBACK the changes in the database.
```
DELETEFROM CUSTOMERS WHERE AGE =25;ROLLBACK;
```
Verification

The delete operation would not impact the table and the SELECT statement would produce the following result.

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

The SAVEPOINT Command

A SAVEPOINT is a logical rollback point in a transaction.

Usually, when you execute the ROLLBACK command, it undoes the changes until the last COMMIT. But, if you create save points you can partially roll the transaction back to these points. You can create multiple save points between two commits.

The syntax to create a SAVEPOINT among the transactions is as shown below.
```
SAVEPOINT savepoint_name;
```
Then, to roll back to the SAVEPOINT created, you can use the following syntax −
```
ROLLBACKTO savepoint_name;
```
Example

Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

Consider the CUSTOMERS table having the following records.

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

The following code block contains the series of operations.
```
SAVEPOINT SP1;
Query OK,0rows affected (0.00 sec)DELETEFROM CUSTOMERS WHERE ID=1;
Query OK,1row affected (0.01 sec)SAVEPOINT SP2;
Query OK,0rows affected (0.00 sec)DELETEFROM CUSTOMERS WHERE ID=2;
Query OK,0rows affected (0.00 sec)SAVEPOINT SP3;
Query OK,0rows affected (0.00 sec)DELETEFROM CUSTOMERS WHERE ID=3;
Query OK,1row affected (0.01 sec)
```
Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone −
```
ROLLBACKTO SP2;
```
Verification

If you display the CUSTOMERS table, you can notice that only the first deletion took place since you rolled back to SP2.

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

The RELEASE SAVEPOINT command is used to remove an existing SAVEPOINT.

The syntax for a RELEASE SAVEPOINT command is as follows.
```
RELEASESAVEPOINT SAVEPOINT_NAME;
```
Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.

The SET TRANSACTION Command

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write.

Syntax

The syntax for a SET TRANSACTION command is as follows.
```
SETTRANSACTION[READWRITE|READ ONLY ];
```
August 18, 2025
NULL Values
SQL uses the term NULL to represent a non-existent data value in the database. These values are not the same as an empty string or a zero. They don’t hold any space in the database and are used to signify the absence of a value or the unknown value in a data field.

Some common reasons why a value may be NULL −
- The value may not be provided during the data entry.
- The value is not yet known.
Since the NULL values are basically non-existent, you cannot use comparison operators such as = , <, or > with them. However, you can check if a value is NULL using the IS NULL, “NOT NULL” or IS NOT NULL operators.

Creating a Table without NULL Values

NULL values can be inserted in any column of a table as they are not associated with any specific data type. However, when a column is defined with the “NOT NULL” keyword, an error is raised whenever you try to insert NULL values into that specific column.

Syntax

The basic syntax of NOT NULL while creating a table is as follows −
```
CREATETABLEtable-name (
   column1 datatype NOTNULL,
   column2 datatype NOTNULL,...
   columnN datatype
);
```
Here, NOT NULL signifies that column should always accept an explicit value of the given data type. You can insert NULL values into the columns where we did not use NOT NULL.

Example

Let us create a table with the name CUSTOMERS in the SQL database using the CREATE statement as shown in the query below −
```
CREATETABLE CUSTOMERS(
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));
```
Let us insert some values into the above created table using the following query −
```
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',NULL),(7,'Muffy',24,'Indore',NULL);
```
The table is successfully created in the database.

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 NULL
7 Muffy 24 Indore NULL

Now, let us retrieve the records present in the table that are not null using the IS NOT NULL operator −
```
SELECT ID, NAME, AGE, ADDRESS, SALARY 
FROM CUSTOMERS 
WHERE SALARY ISNOTNULL;
```
The above query would produce the following result −

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

You can also retrieve the NULL records present in the table using IS NULL operator in the SELECT query as shown below −
```
SELECT ID, NAME, AGE, ADDRESS, SALARY 
FROM CUSTOMERS 
WHERE SALARY ISNULL;
```
The above query would produce the following result −

ID NAME AGE ADDRESS SALARY
6 Komal 22 Hyderabad NULL
7 Muffy 24 Indore NULL

Updating NULL Values in a Table

You can update the NULL values present in a table using the UPDATE statement in SQL. To do so, you can use the IS NULL operator in your WHERE clause to filter the rows containing NULL values and then set the new value using the SET keyword.

Example

Consider the previously created table and update the NULL value(s) present in the table using the UPDATE statement as shown below −
```
UPDATE CUSTOMERS SET SALARY =9000WHERE SALARY ISNULL;
```
Output

When you execute the above query, the output is obtained as follows −
```
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
```
Verification

Let us verify whether the specified record(s) in the table is updated or not using the following query −
```
SELECT*FROM CUSTOMERS;
```
On executing the above query, the output is displayed as follows −

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

Deleting Records with NULL Values

You can delete records containing NULL values from a table using the DELETE FROM statement. You first check whether the table consists of NULL values using the IS NULL operator in WHERE clause and delete the records that are filtered.

Example

Consider the previously created CUSTOMERS table and delete the NULL value(s) present in the table using the DELETE statement as shown below −
```
DELETEFROM CUSTOMERS WHERE SALARY ISNULL;
```
Output

When you execute the above query, the output is obtained as follows −
```
Query OK, 2 rows affected (0.01 sec)
```
Verification

Let us verify whether the filtered record(s) in the table is deleted or not, by displaying the table using a SELECT statement.
```
SELECT*FROM CUSTOMERS;
```
The table will be displayed 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
August 18, 2025

ID	NAME	AGE	ADDRESS	SALARY
6	Komal	22	Hyderabad	NULL
7	Muffy	24	Indore	NULL

Stored Procedures

SQL Stored Procedures

An SQL stored procedure is a group of pre-compiled SQL statements (prepared SQL code) that can be reused by simply calling it whenever needed.

It can be used to perform a wide range of database operations such as inserting, updating, or deleting data, generating reports, and performing complex calculations. Stored procedures are very useful because they allow you to encapsulate (bundle) a set of SQL statements as a single unit and execute them repeatedly with different parameters, making it easy to manage and reuse the code.

Procedures have similar structure as functions: they accept parameters and perform operations when we call them. But, the difference between them is that SQL stored procedures are simpler to write or create, whereas functions have a more rigid structure and support fewer clauses.

Syntax

The basic syntax to create an SQL stored procedure is as follows −

DELIMITER//CREATEPROCEDURE procedure_name(parameter1 datatype, parameter2 datatype,...)BEGIN-- SQL statements to be executedENDDELIMITER;

The CREATE PROCEDURE statement is used to create the procedure. We can define any number of input parameters as per the requirement.
The SQL statements that make up the procedure are placed between the BEGIN and END keywords.

Creating a Procedure

We can create a stored procedure using the CREATE PROCEDURE statement in SQL. Following are the simple steps for creating a stored procedure −

Choose a name for the procedure.
Write the SQL code for the procedure.
We can then test the stored procedure by executing it with different input parameters.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

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

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

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

The 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

Now, let us look at a simple example of creating a stored procedure that takes an input parameter and returns a result set.

In the following query, we are creating the stored procedure with the name GetCustomerInfo. then we provide it with a single input parameter called @CutomerAge. The stored procedure then selects all records from the CUSTOMERS table where the value of the CutomerAge matches the input parameter.

DELIMITER//CREATEPROCEDURE GetCustomerInfo(IN CustomerAge INT)BEGINSELECT*FROM CUSTOMERS WHERE AGE = CustomerAge;END//DELIMITER;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test the stored procedure by executing it using the CALL statement as shown below −

CALL GetCustomerInfo(25);

This will return all columns from the CUSTOMERS table where the customers age is 25.

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00

Stored Procedure Parameter Types

Stored procedures in a database system can have different types of parameters, which are placeholders for values that will be passed to the stored procedure when it is executed. Following are the different types of stored procedure parameters in SQL −

S.No.	Parameter & Description
1	Input parametersThese parameters are used to pass values from the calling statement to the stored procedure.
2	Output parametersThese parameters are used to return values from the stored procedure.
3	Input/Output parametersThese parameters allow a stored procedure to accept input values and return output values.

Procedure with IN parameter

IN is the default parameter of the procedure that will receive input values. We can pass the values as arguments when the stored procedure is being called.

These values are read-only, so they cannot be modified by the stored procedure.

Example

In the following query, we are creating a stored procedure that takes a customer ID as an input parameter and returns the corresponding customer salary.

The procedure body simply performs a SELECT statement to retrieve the “Salary” column from the “CUSTOMERS” table, where the “CustomerID” matches the input parameter.

DELIMITER//CREATEPROCEDURE GetCustomerSalary(IN CustomerID Int)BEGINSELECT SALARY FROM CUSTOMERS WHERE ID = CustomerID;END//DELIMITER;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test it by executing it with different ID as an input parameter as shown in the query below −

CALL GetCustomerSalary(6);

This will return the salary for the customer with an ID of 6, assuming there is a corresponding row in the CUSTOMERS table −

SALARY
4500.00

Procedure with OUT parameter

The OUT parameter is used to return the output value from the procedure.

Note that when using an OUT parameter, we must specify the keyword OUT before the parameter name when passing it to the stored procedure. This tells the SQL database that the parameter is an output parameter and should be assigned with a value in the stored procedure.

Example

In the following query we are creating a stored procedure that used to count the number of records of customer having same age and assign this count to the ‘total’ variable which holds the number of records.

The procedure body performs a SELECT statement to get the count of records having same age from the “CUSTOMERS” table

DELIMITER//CREATEPROCEDURE GetDetail(OUT total INT)BEGINSELECTCOUNT(AGE)INTO total FROM CUSTOMERS

  WHERE AGE =25;END//DELIMITER;</pre>

Calling the created procedure and passing the 'total' parameter

CALL GetDetail(@total);

Here, we are using the SELECT statement and getting the count −

SELECT@total;

Output

This would produce the following result −

@total
2

Verification

To verify weather the procedure is created, we can use the following query −

SHOWCREATEPROCEDURE GetDetails;

Procedure with INOUT parameter

The INOUT parameter is a combination of an IN parameter and an OUT parameter. You can pass data into the stored procedure and receive data from the stored procedure using the same parameter.

To declare an INOUT parameter in a stored procedure, we need to specify the INOUT keyword before the parameter name.

Example

In the following query, we provide two INOUT parameters to the stored procedure: cust_id and curr_Salary. These two are used as both an input and output parameters.

The stored procedure first retrieves the current salary of the customer from the database using the cust_id parameter. It then increases the salary by 10% and updates the customers salary in the database using the same parameter.

DELIMITER//CREATEPROCEDURE increaseSalary(INOUT Cust_Id Int,INOUT curr_Salary Int)BEGINSELECT SALARY INTO curr_Salary From CUSTOMERS Where ID = Cust_Id;SET curr_Salary = curr_Salary *1.1;Update CUSTOMERS SET SALARY = curr_Salary Where ID = Cust_Id;END//DELIMITER;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test it by executing it with different ID or input parameters as shown in the query below −

SET@customerID=1;SET@salary=0.0;CALL increaseSalary(@customerID,@salary);

Following is Query to select the updated salary from the stored procedure

SELECT@salaryAS updated_salary;

The result-set is obtained as −

updated_salary
2200

Advantages of Stored Procedures

Following are the advantages of stored procedures −

Improved Performance: Stored procedures are pre-compiled and stored on the server, so they can be executed more quickly than SQL statements that are sent from client applications.
Code Reuse: Stored procedures can be called from different client applications, which means that the same code can be reused across different applications. This reduces development time and maintenance costs.
Reduced Network Traffic: Because stored procedures are executed on the server, only the results are returned to the client, which reduces network traffic and improves application performance.
Better Security: Stored procedures can be used to enforce security rules and prevent unauthorized access to sensitive data. They can also limit the actions that can be performed by users, making it easier to maintain data integrity and consistency.
Simplified Maintenance: By storing SQL code in a single location, it becomes easier to maintain and update the code. This makes it easier to fix bugs, add new functionality, and optimize performance.

Drawbacks of Stored Procedures

Following are the disadvantages of stored procedures −

Increased Overhead: Stored procedures can consume more server resources than simple SQL statements, particularly when they are used frequently or for complex operations.
Limited Portability: Stored procedures are often specific to a particular database management system (DBMS), which means they may not be easily portable to other DBMSs.
Debugging Challenges: Debugging stored procedures can be more challenging than debugging simple SQL statements, particularly when there are multiple layers of code involved.
Security Risks: If stored procedures are not written correctly, they can pose a security risk, particularly if they are used to access sensitive data or to perform actions that could compromise the integrity of the database.

August 18, 2025

Default Constraint
The SQL DEFAULT Constraint

The SQL DEFAULT Constraint is used to specify the default value for a column of a table. We usually set default value while creating the table.

The default values are treated as the column values if no values are provided while inserting the data, ensuring that the column will always have a value. We can specify default values for multiple columns in an SQL table.

Syntax

Following is the syntax of the SQL DEFAULT Constraint −
```
CREATETABLE table_name (
   column1 datatype DEFAULT default_value,
   column2 datatype DEFAULT default_value,
   column3 datatype,.....
   columnN datatype
);
```
Example

In the following query we are creating the CUSTOMERS table using the CREATE TABLE statement. Here, we are adding a default constraint to the columns NAME, AGE, ADDRESS, and SALARY −
```
CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULLDEFAULT'Ramesh',
   AGE INTNOTNULLDEFAULT'32',
   ADDRESS CHAR(25)DEFAULT'Ahmedabad',
   SALARY DECIMAL(18,2)DEFAULT'2000',PRIMARYKEY(ID));
```
Following query inserts values into this table using the INSERT statement −
```
INSERTINTO CUSTOMERS (ID)VALUES(1);INSERTINTO CUSTOMERS VALUES(2,'Khilan',25,'Delhi',1500.00);
```
The table is created with default values in the NAME, AGE, ADDRESS, and SALARY columns for the first row as shown below −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00

Passing “DEFAULT” as Value

While inserting data into a table, if the column names are not included in the INSERT query, to insert the default value into the record we need to pass “DEFAULT” as a value, as shown below −
```
INSERTINTO CUSTOMERS VALUES(3,'Kaushik',DEFAULT,DEFAULT,2000.00),(4,'Chaitali',DEFAULT,DEFAULT,DEFAULT);
```
The table obtained is as shown below −

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

Adding Default Constraint to an Existing Column

We can also add default constraints to an existing column of a table using the ALTER TABLE statement. This allows us to modify the structure of existing table by specifying default values, ensuring data consistency in the database.

Syntax

Following is the syntax for adding a default constraint to a column in an existing table −
```
ALTERTABLE table_name
ALTERCOLUMN column_name SETDEFAULT'default_value';
```
Example

Assume we have created another table named BUYERS using the CREATE TABLE statement as shown below −
```
CREATETABLE BUYERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));
```
Following query, we adds a default constraint to the ADDRESS column of the BUYERS table.
```
ALTERTABLE BUYERS ALTER ADDRESS SETDEFAULT'Delhi';
```
Following INSERT statement inserts a record into the BUYERS table by providing values to all the columns except ADDRESS −
```
INSERTINTO BUYERS (ID, NAME, AGE, SALARY)VALUES(01,'Rahul',27,50000);
```
Verification

After inserting the record if you retrieve it back, you can observe the default value (“Delhi”) in the address column −
```
SELECT*FROM BUYERS WHERE ID =01;
```
Output

The table obtained is as follows −

ID NAME AGE ADDRESS SALARY
01 Rahul 27 Delhi 50000.00

Dropping Default Constraint

We can delete the default constraint from a table using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax to delete the default constraint from a table −
```
ALTERTABLE table_name
ALTERCOLUMN column_name DROPDEFAULT;
```
Example

In here, we are removing the default constraint from the ADDRESS column of the CUSTOMERS table −
```
ALTERTABLE CUSTOMERS ALTER ADDRESS DROPDEFAULT;
```
Verification

We can verify the table details (structure) and check whether there is a default constraint or not using the following query −
```
DESC CUSTOMERS;
```
The table obtained is as shown below −

Field Type Null Key Default Extra
ID int NO PRI NULL
NAME varchar(20) NO Ramesh
AGE int NO 32
ADDRESS char(25) YES NULL
SALARY decimal(18,2) YES 2000.00
August 18, 2025

ID	NAME	AGE	ADDRESS	SALARY
01	Rahul	27	Delhi	50000.00

Field	Type	Null	Key	Default
ID	int	NO	PRI	NULL
NAME	varchar(20)	NO		Ramesh
AGE	int	NO		32
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		2000.00

Check Constraint

The SQL CHECK Constraint

The SQL CHECK constraint is used to add conditions on a column of a table.

Once you add the check constraint on a column, it ensures that the data entered into the column meets the specified conditions. If a particular record does not meet the conditions, the database will prevent you from inserting or updating that record.

Suppose we have a table CUSTOMERS having a column AGE. We can add a CHECK constraint on this column to ensure that the age entered is always a positive number and not greater than 50 years. If someone tries to input a negative age or an age over 50, the database will reject it, ensuring that your data remains accurate and valid.

Check Constraint on Single Column

To add a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.

Syntax

Following is the syntax to specify the check constraint on a single column −

CREATETABLE table_name (
   column_name data_type CHECK(condition));

Example

In the following query, we are creating a table named CUSTOMERS. Here, we are specifying a column-level check constraint on the AGE column, that allows only those records to be inserted where the age value of the customer is greater than “20” −

Open Compiler

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

Verification

To verify whether the check constraint is added to the AGE column, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

The above query will show all the details of the CUSTOMERS table, including how many columns have check constraints and what constraints we have specified in the table as shown below −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	CHECK	employees_chk_1

Now, to verify if the CHECK constraint is working properly, let us insert a record into CUSTOMERS where AGE contains a value less than 20 (does not satisfy the given condition) −

INSERTINTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Ramesh',15,'Ahmedabad',2000.00);

The output of the above query is as shown below −

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

Check Constraint on Multiple Columns

We can also add check constraint on multiple columns of a table by specifying the conditions that must be met for the combination of values in those columns.

Suppose we have a table containing the details of products, including their start and end dates. We can add a CHECK constraint that ensures the end date is always greater than or equal to the start date. In this case, the constraint is checking the values in two columns (start date and end date) within the same row to make sure they follow a specific relationship.

Example

In the following example, we are specifying a column-level check constraint on multiple columns (AGE and SALARY) of the CUSTOMERS table. Here, the AGE column will allow only those records where the AGE is greater than or equal to 20, and the SALARY column will allow only those records where the SALARY is greater than 20000 −

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

Verification

To verify whether the check constraint is applied on both the columns, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	CHECK	customers_chk_1
customers	CHECK	customers_chk_2

Now, we are inserting values into the CUSTOMERS table where the age is less than 20 and the salary is less than 20000.

INSERTINTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Ramesh',15,'Ahmedabad',2000.00);

The above query throws an error because the values passed in the AGE and the SALARY columns are not satisfying the CHECK constraints −

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

Check Constraint at the Table Level

We must use the check constraint before completing the table creation in order to ensure the check constraint at the table level.

Syntax

Following is the syntax to specify the check constraint on the table level −

CREATETABLE table_name (
   column1 data_type,
   column2 data_type,...,CONSTRAINT constraint_name CHECK(column_name condition_value));

Example

In the following SQL query, we are creating a table PRODUCTS. In here, we are specifying a table level check constraint on the DATE_OF_ORDER column, that allows only those records to be inserted where the DATE_OF_ORDER is less than (before) “2023-02-09” −

CREATETABLE PRODUCTS(
   PID INTNOTNULL,
   PNAME VARCHAR(30),
   DELIVERY_CITY VARCHAR(20),
   DATE_OF_ORDER DateNOTNULL,
   PRICE INT,PRIMARYKEY(PID),CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <='2023-02-09'));

Verification

We can verify the CHECK constraint on the created table using the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='PRODUCTS';

Output

It will show all the details of the created table, including how many columns have check constraints on the table level as shown below −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
products	PRIMARY KEY	PRIMARY
products	CHECK	Constraint_DOO

In here, we are inserting values in the PRODUCTS which have the constraint less than “2023-02-09” on the column DATE_OF_ORDER −

INSERTINTO PRODUCTS VALUES(001,'Nike Shoe','Ranchi','2023-01-11',2000);

Following is the output of the above query −

Query OK, 1 row affected (0.01 sec)

Check Constraint on an Existing Column

We can use the ALTER TABLE statement to add the check constraint on an existing column of the table.

Syntax

Following is the Syntax to add a check-constraint on an existing table −

ALTERTABLE table_name
ADDCONSTRAINT constraint_name CHECK(ColumnName condition_value);

Example

In the following query, we are creating a table named CUSTOMERS −

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

To add a check constraint on the AGE column, we are using the following query −

ALTERTABLE CUSTOMERS 
ADDCONSTRAINT Constraint_Age CHECK(AGE >=21);

Verification

To verify whether the check constraint is applied after the table creation, use the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

It will display all of the table’s information, including the constraint we added to the age column −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	CHECK	Constraint_Age

Removing a Check Constraint

If there is a way to add a constraint on a column, then you must also be able to remove the constraint from that column. To do that, you can use the ALTER DROP statement.

Syntax

Following is the syntax to remove a check constraint from the table −

ALTERTABLE table_name 
DROPCONSTRAINT constraint_name;

Example

Following example shows how to drop the check constraint from the CUSTOMERS table created above −

ALTERTABLE CUSTOMERS 
DROPCONSTRAINT Constraint_Age;

Verification

Using the following SQL query, we are verifying whether the constraint is removed −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

We can see that the check constraint added on the age column is removed −

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY
customers	PRIMARY KEY	PRIMARY

August 18, 2025

Null Functions
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 −
```
CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));
```
Now, insert records into this table using the INSERT INTO statement as follows −
```
INSERTINTO 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 −

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

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

SALARY Null_value
2000.00 0
1500.00 0
NULL 1
6500.00 0
8500.00 0
NULL 1
10000.00 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 −
```
SELECTCOALESCE(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 −

Result
welcome

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 −

NAME SALARY AGE Result
Ramesh 2000.00 32 2000.00
Khilan 1500.00 25 1500.00
Kaushik NULL 23 23.00
Chaitali 6500.00 25 6500.00
Hardik 8500.00 27 8500.00
Komal NULL 22 22.00
Indore 10000.00 24 10000.00

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 −

NAME ADDRESS Result
Ramesh Ahmedabad Ramesh
Khilan Delhi Khilan
Kaushik Kota Kaushik
Chaitali Mumbai Chaitali
Hardik Bhopal Hardik
Komal Hyderabad Komal
Indore Indore NULL

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 −

NAME SALARY Result
Ramesh 2000.00 2000.00
Khilan 1500.00 1500.00
Kaushik NULL 5500.00
Chaitali 6500.00 6500.00
Hardik 8500.00 8500.00
Komal NULL 5500.00
Indore 10000.00 10000.00
August 18, 2025
MIN MAX function
The MIN() and MAX() functions in SQL are aggregate functions. They are used to compare values in a set and, retrieve the maximum and minimum values respectively.

An aggregate function is a mathematical computation that takes a range of values as input and yields a single value expression, representing the significance of the provided data.

MAX() and MIN() aggregate functions are generally used in two ways:
- As functions, they are used with the GROUP BY clause of the SELECT statement.
- As expressions, they are used with a subquery and HAVING clause of SELECT statement.
The SQL MAX() Function

The MAX() function compares the values in a column and returns the largest value among them.

Syntax

Following is the syntax of SQL MAX() function −
```
MAX(column_name);
```
Example

In the following example, we are running a query for MAX() function on a table named CUSTOMERS. The objective is to retrieve the maximum salary value from this table. First of all, let us create the CUSTOMERS table using the following query −
```
CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));
```
Now, insert values into this table using the INSERT statement as follows −
```
INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);
```
The CUSTOMERS table will be 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 comparing the salaries of CUSTOMERS and retrieving the maximum salary using the following query −
```
SELECTMAX(SALARY)FROM CUSTOMERS;
```
When the above query is executed, the result is displayed as −

MAX(SALARY)
10000.0000

HAVING with MAX() Function

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MAX() function along with HAVING clause.
```
SELECT ID, NAME, SALARY 
FROM CUSTOMERS 
GROUPBY NAME, ID 
HAVINGMAX(SALARY)<8000;
```
When the above query is executed, we get the details of the employees whose maximum salary is less than 8000 −

ID NAME SALARY
1 Ramesh 2000.00
2 Khilan 1500.00
3 Kaushik 2000.00
4 Chaitali 6500.00
6 Komal 4500.00

MAX() Function in Subqueries

In the following example, we are using the MAX() function in a subquery to retrieve the record with maximum salary, from the CUSTOMERS table.
```
SELECT*FROM CUSTOMERS 
WHERE SALARY =(SELECTMAX(SALARY)FROM CUSTOMERS);
```
When we execute the above query, we will get the following result −

ID NAME AGE ADDRESS SALARY
7 Muffy 24 Indore 10000.00

MAX() Function with Strings

This query retrieves the maximum value (alphabetically) among the names of customers in the CUSTOMERS table using the MAX() function −
```
SELECTMAX(NAME)AS max_name FROM CUSTOMERS;
```
Following is the result of the above query −

max_name
Ramesh

Aliases with MAX() Function

In the following example, we use the MAX() function to retrieve the record containing maximum age from the CUSTOMERS table. We are displaying the results as a new column with the alias “max_age”.
```
SELECTMAX(age)AS'max_age'FROM CUSTOMERS;
```
Following is the output of the above query −

max_age
32

The SQL MIN() Function

The MIN() function compares values in a column and returns the smallest value among them.

Syntax

Following is the syntax of SQL MIN() function −
```
MIN(column_name);
```
Example

In this example, we are comparing values in the SALARY column of CUSTOMERS table and displaying the minimum salary using the following query −
```
SELECTMIN(SALARY)FROM CUSTOMERS;
```
When the above query is executed, the result is displayed as −

MIN(SALARY)
1500.0000

HAVING with MIN() Function

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MIN() function along with HAVING clause.
```
SELECT ID, NAME, SALARY 
FROM CUSTOMERS 
GROUPBY NAME, ID 
HAVINGMIN(SALARY)>5000;
```
When the above query is executed, we get the details of the maximum salary for employees whose minimum salary is more than 5000, as we can see in the table that follows −

ID NAME MAX_Salary
4 Chaitali 6500.0000
5 Hardik 8500.0000
7 Muffy 10000.0000

MIN() Function in Subqueries

In the following example, we are using the MIN() function in a subquery to retrieve the record with minimum salary, from the CUSTOMERS table.
```
SELECT*FROM CUSTOMERS 
WHERE SALARY =(SELECTMIN(SALARY)FROM CUSTOMERS);
```
When we execute the above query, we will get the following result −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00

MIN() Function with Strings

Following is the query to retrieve the minimum value (alphabetically) among the names of customers in the CUSTOMERS table using the MIN() function −
```
SELECTMIN(NAME)AS min_first_name FROM CUSTOMERS;
```
Following is the result of the above query −

min_first_name
Chaitali

Aliases with MIN() Function

Following is the SQL query that will fetch the minimum age from the CUSTOMERS table using the MIN() function −
```
SELECTMIN(age)AS'min_age'FROM CUSTOMERS;
```
When we execute the above query, the minimum value in the age field is displayed as shown below.

min_age
22
August 18, 2025
Hosting
SQL Hosting

SQL Hosting is nothing but a means to manage any RDBMS linked to your website using SQL. If the website has an access to a RDBMS, any data from the website you created will be stored and retrieved from this database.

There are various SQL hosting plans available if your web server is hosted by an Internet Service Provider (ISP).

Following are the most common SQL hosting databases −
- MS SQL Server
- Oracle
- MySQL
- MS Access
MS SQL Server

MS SQL Server is created and developed by Microsoft. It is compatible with both virtual and cloud servers. It is very efficient to use with database-driven websites having high traffic.

MS SQL Server’s features include −
- Maximum scalability and security
- Integrated reporting capabilities
- Easy to use
- Powerful
- Robust
- Offers more diverse features while hosting
Oracle

Oracle is a popular database which is suitable to use with high-traffic websites. This database offers various features like,
- Cost-effective product
- High-performance
- Converged, multi-model database management system
- In-memory MySQL databases
Oracle is also a very powerful, robust and full featured SQL database system.

MySQL

MySQL is one of the most popular RDBMS in the world used to store and manage data. It is compatible with any type of server, say cloud, virtual or dedicated. Features of MySQL are as follows −
- Easy to use
- High performance
- Excellent security
- Improved speed
- Cost effective
MS Access

Microsoft Access is a very simple database which can be used for simple websites. MS Access is neither as powerful as MySQL, MS SQL Server or Oracle. Thus, it is not effective for websites with higher traffic.
August 18, 2025

SALARY	Null_value
2000.00	0
1500.00	0
NULL	1
6500.00	0
8500.00	0
NULL	1
10000.00	0

Result
welcome

NAME	SALARY	AGE	Result
Ramesh	2000.00	32	2000.00
Khilan	1500.00	25	1500.00
Kaushik	NULL	23	23.00
Chaitali	6500.00	25	6500.00
Hardik	8500.00	27	8500.00
Komal	NULL	22	22.00
Indore	10000.00	24	10000.00

NAME	ADDRESS	Result
Ramesh	Ahmedabad	Ramesh
Khilan	Delhi	Khilan
Kaushik	Kota	Kaushik
Chaitali	Mumbai	Chaitali
Hardik	Bhopal	Hardik
Komal	Hyderabad	Komal
Indore	Indore	NULL

NAME	SALARY	Result
Ramesh	2000.00	2000.00
Khilan	1500.00	1500.00
Kaushik	NULL	5500.00
Chaitali	6500.00	6500.00
Hardik	8500.00	8500.00
Komal	NULL	5500.00
Indore	10000.00	10000.00

MAX(SALARY)
10000.0000

ID	NAME	SALARY
1	Ramesh	2000.00
2	Khilan	1500.00
3	Kaushik	2000.00
4	Chaitali	6500.00
6	Komal	4500.00

ID	NAME	AGE	ADDRESS	SALARY
7	Muffy	24	Indore	10000.00

max_name
Ramesh

max_age
32

MIN(SALARY)
1500.0000

ID	NAME	MAX_Salary
4	Chaitali	6500.0000
5	Hardik	8500.0000
7	Muffy	10000.0000

min_first_name
Chaitali

min_age
22

Injection

If you take a user input through a webpage and insert it into an SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script.

SQL Injection

SQL Injection is a type of security attack that exploits a vulnerability in a database by executing malicious queries. This will allow attackers to access sensitive data, tamper it and also delete it permanently.

Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching.

Example

In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (you can modify these rules as needed).

if(preg_match("/^\w{8,20}$/",$_GET['username'],$matches)){$result=mysqli_query("SELECT * FROM CUSTOMERS 
  WHERE name = $matches[0]");}else{echo"user name not accepted";}</pre>


To demonstrate the problem, consider this excerpt −



// supposed input$name="Qadir'; DELETE FROM CUSTOMERS;";mysqli_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'");



The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table.



Fortunately, if you use MySQL, the mysqli_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails.



However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem.



Preventing SQL Injection



You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.



if(get_magic_quotes_gpc()){$name=stripslashes($name);}$name=mysql_real_escape_string($name);mysqli_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");



The LIKE Quandary



To address the LIKE quandary, a custom escaping mechanism must convert user-supplied '%' and '_' characters to literals. Use addcslashes(), a function that lets you specify a character range to escape.



$sub=addcslashes(mysql_real_escape_string("%str"),"%_");// $sub == \%str\_mysqli_query("SELECT * FROM messages 
   WHERE subject LIKE '{$sub}%'");

August 18, 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

Category: Advanced SQL

SQL Transactions

Properties of Transactions

Transactional Control Commands

The COMMIT Command

Example

Verification

The ROLLBACK Command

Example

Verification

The SAVEPOINT Command

Example

Verification

The RELEASE SAVEPOINT Command

The SET TRANSACTION Command

Syntax

Creating a Table without NULL Values

Syntax

Example

Updating NULL Values in a Table

Example

Output

Verification

Deleting Records with NULL Values

Example

Output

Verification

SQL Stored Procedures

Syntax

Creating a Procedure

Example

Output

Verification

Stored Procedure Parameter Types

Procedure with IN parameter

Example

Output

Verification

Procedure with OUT parameter

Example

Output

Verification

Procedure with INOUT parameter

Example

Output

Verification

Advantages of Stored Procedures

Drawbacks of Stored Procedures

The SQL DEFAULT Constraint

Syntax

Example

Passing “DEFAULT” as Value

Adding Default Constraint to an Existing Column

Syntax

Example

Verification

Output

Dropping Default Constraint

Syntax

Example

Verification

The SQL CHECK Constraint

Check Constraint on Single Column

Syntax

Example

Verification

Output

Check Constraint on Multiple Columns

Example

Verification

Output

Check Constraint at the Table Level

Syntax

Example

Verification

Output

Check Constraint on an Existing Column

Syntax

Example

Verification