The SQL ANY and ALL operators are used to perform a comparison between a single value and a range of values returned by the subquery.

The ANY and ALL operators must be preceded by a standard comparison operator i.e. >, >=, <, <=, =, <>, != and followed by a subquery. The main difference between ANY and ALL is that ANY returns true if any of the subquery values meet the condition whereas ALL returns true if all of the subquery values meet the condition.

The SQL ANY Operator

The ANY operator is used to verify if any single record of a query satisfies the required condition.

This operator returns a TRUE, if the given condition is satisfied for any of the values in the range. If none of the values in the specified range satisfy the given condition, this operator returns false. You can also use another query (subquery) along with this operator.

Syntax

The basic syntax of the SQL – ANY operator is as follows −

Column_name operator ANY (subquery);

Where,

• column_name is the name of a column in the main query.

• operator is a comparison operator such as =, <, >, <=, >=, or <>.

• subquery is a SELECT statement that returns a single column of values.

ANY with ”>” Operator

Typically, the ANY operator is used to compare a value with a set of values returned by a subquery, in such cases we can use it with the > (greater than) operator to verify if a particular column value is greater than column value of any of the records returned by the sub query.

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 −

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

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

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

The table will be created as follows −

Now, let us list out the details of all the CUSTOMERS whose SALARY is greater than the SALARY of any customer whose AGE is 32 i.e. Chaitali, Hardik, Komal and Muffy in this case −

SELECT * FROM CUSTOMERS
WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 32);

Output

The result obtained is as follows −

ANY with ”<” Operator

Similar to the ”>” operator, we can use the ”<” (less than) operator along with ANY to verify if a particular column value is less than column value of any of the records returned by the sub query.

Example

In here, we are finding the distinct/different age of customers having any salary less than the average salary of all the customers from the CUSTOMERS table previously created −

SELECT DISTINCT AGE FROM CUSTOMERS
WHERE SALARY < ANY (SELECT AVG(SALARY) FROM CUSTOMERS);

Output

We get the following output while executing the above query −

ANY with ”=” Operator

When we use the = (equal to) operator along with ANY, it verifies if a particular column value is equal to the column value of any of the records returned by the sub query.

Example

In the query given below, we are retrieving the details of all the customers whose age is equal to the age of any customer whose name starts with ”K” −

SELECT * FROM CUSTOMERS
WHERE AGE = ANY (SELECT AGE FROM CUSTOMERS WHERE NAME LIKE ''K%'');

Output

The result produced is as follows −

The SQL ALL Operator

The SQL ALL operator returns all the records of the SELECT statement.

• It returns TRUE if the given condition is satisfied for ALL the values in the range.

• It always returns a Boolean value.

• It is used with SELECT, WHERE and HAVING statements in SQL queries.

• The data type of the values returned from a subquery must be the same as the outer query expression data type.

Syntax

The basic syntax of the SQL ALL operator is as follows −

Column_name operator ALL (subquery);

Where,

• column_name − is the name of a column in the main query.

• operator − is a comparison operator such as =, <, >, <=, >=, or <>.

• subquery − is a SELECT statement that returns a single column of values.

ALL with WHERE Statement

When we use the ALL operator with a WHERE clause, it filters the results of the subquery based on the specified condition.

The WHERE clause in SQL is used to filter rows from a query based on specific conditions. It operates on individual rows in the table, and it allows you to specify conditions that must be met by each row in the data returned by the query.

Example

If we consider the CUSTOMERS table created above,the following query returns the details of all the customers whose salary is not equal to the salary of any customer whose age is 25 −

SELECT * FROM CUSTOMERS
WHERE SALARY <>
ALL (SELECT SALARY FROM CUSTOMERS WHERE AGE = 25);

Output

The output of the above query is as follows −

ALL with HAVING Clause

In SQL, the ALL operator can also be used with the HAVING clause to filter the results of a GROUP BY query based on a condition that applies to all the aggregated values in the group.

Example

The following SQL query is used to obtain the details of all the customers whose salary is less than the average salary −

SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
GROUP BY AGE, SALARY
HAVING SALARY < ALL (SELECT AVG(SALARY) FROM CUSTOMERS);

Output

Output of the above query is as follows −

The SQL EXISTS Operator

The SQL EXISTS operator is used to verify whether a particular record exists in a MySQL table. While using this operator we need to specify the record (for which you have to check the existence) using a subquery.

The EXISTS operator is used in the WHERE clause of a SELECT statement to filter records based on the existence of related records in another table.

• It is a logical operator.

• It returns a Boolean value TRUE or FALSE.

• It returns TRUE if the subquery returns at least one record.

• If the EXISTS operator returns TRUE, the outer query will get executed; otherwise not.

• It can be used in SELECT, UPDATE, DELETE or INSERT statements.

The EXISTS operator is more efficient than other operators, such as IN, because it only needs to determine whether any rows are returned by the subquery, rather than actually returning the data.

The use of the EXISTS operator is an efficient way to filter data in many real-life scenarios, including filtering records based on the existence of related data, aggregating data based on the existence of related records, and optimizing queries.

Syntax

The basic syntax of the SQL EXISTS operator is as follows −

WHERE EXISTS (subquery);

Where, the subquery used is the SELECT statement. The EXISTS operator will evaluate to TRUE if the subquery returns at least one record in its result set; otherwise FALSE.

EXISTS Operator with SELECT Statement

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. We can use the EXISTS operator with a SELECT statement to check for the existence of rows that meet a certain condition.

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 −

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

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

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

The table will be created as follows −

Let us create another table CARS, containing the details such as id of the customer, name and price of the car, using the following query −

create table CARS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   PRICE INT NOT NULL,
   PRIMARY KEY(ID)
);

Using the INSERT statement, let us insert values into this table −

insert INTO CARS VALUES
(2, ''Maruti Swift'', 450000),
(4, ''VOLVO'', 2250000),
(7, ''Toyota'', 2400000);

The ”CARS” table obtained is as follows −

Now, we are retrieving the lists of the customers with the price of the car greater than 2,000,000 −

SELECT * FROM CUSTOMERS WHERE
EXISTS (
   SELECT PRICE FROM CARS
   WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000
);

Output

The result produced is as follows −

EXISTS Operator with UPDATE Statement

We can also use the SQL EXISTS operator with an UPDATE statement. It helps us to update rows in a table based on the existence of matching rows in another table.

Example

Suppose if we want to change the name of certain customers from the CUSTOMERS and CARS tables previously created, then this can be done using UPDATE statement. Here, we are modifying the name ”Kushal” of all the customers whose id is equal to the id of the CARS table, using the EXISTS operator, as follows −

UPDATE CUSTOMERS SET NAME = ''Kushal''
WHERE EXISTS (
   SELECT NAME FROM CARS WHERE CUSTOMERS.ID = CARS.ID
);

Output

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

Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

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

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

As we can see in the above table, the NAME of ”Khilan”, ”Chaitali” and ”Muffy” has been updated to ”Kushal”.

EXISTS Operator with DELETE Statement

The EXISTS operator can also be used with a DELETE statement to delete rows based on the existence of rows returned by a subquery.

Example

In here, we are deleting the row in the CUSTOMERS table whose id is equal to the id of the CARS table having price equal to ”2250000” −

DELETE FROM CUSTOMERS WHERE
EXISTS (
   SELECT * FROM CARS
   WHERE CARS.ID = CUSTOMERS.ID AND CARS.PRICE = 2250000
);

Output

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

Query OK, 1 row affected (0.01 sec)

Verification

We can rectify the changes done in the CUSTOMERS table using the following query −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

As we can see in the above table, the row with the NAME ”Chaitali” has been deleted since the id of Chaitali in CUSTOMERS table was ”4”, which is equal to the id of the CARS table having price equal to ”2250000”.

NOT Operator with EXISTS Operator

In SQL, the NOT EXISTS operator is used to select records from one table that do not exist in another table.

Syntax

Following is the basic syntax of NOT EXISTS operator in SQL −

WHERE NOT EXISTS (subquery);

Where, the subquery used is the SELECT statement.

Example

The below query gives the names of the customers who have not bought any car −

SELECT * FROM CUSTOMERS WHERE NOT
EXISTS (
   SELECT * FROM CARS WHERE CUSTOMERS.ID = CARS.ID
);

Output

Following output is obtained by executing the above query −

Usage of SQL EXISTS Operator

The EXISTS operator in SQL is widely used in real-life scenarios to filter records based on the existence of related data in another table. Some common use cases include −

• Checking for the existence of records in a many-to-many relationship − The EXISTS operator can be used to check whether a record exists in a join table for a many-to-many relationship, for example, finding all customers who have purchased a particular product.

• Filtering records based on the existence of related records − The EXISTS operator can be used to filter records based on the existence of related records in another table. For example, finding all orders that have associated order details.

• Aggregating data based on the existence of related records − The EXISTS operator can be used to aggregate data based on the existence of related records. For example, finding the number of customers who have placed an order.

• Optimizing queries − The EXISTS operator can be used to optimize queries by only returning the necessary data. For example, finding the first order for each customer without using a self-join.

These are just a few examples of how the EXISTS operator can be used in real-life scenarios. The specific use case will depend on the data and the requirements of the query.

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

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

Syntax

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

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

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

Sorting Results in Ascending Order

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

Example

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

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

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

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

The table will be created as −

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

SELECT * FROM CUSTOMERS ORDER BY NAME;

Output

This would produce the following result −

Sorting Results in Descending Order

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

Example

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

SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

Output

This would produce the following result −

Sorting Results in a Preferred Order

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

Example

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

SELECT * FROM CUSTOMERS
ORDER BY ( CASE ADDRESS
   WHEN ''DELHI'' 	 THEN 1
   WHEN ''BHOPAL'' 	 THEN 2
   WHEN ''KOTA'' 	 THEN 3
   WHEN ''AHMEDABAD'' THEN 4
   WHEN ''Hyderabad'' 	THEN 5
   ELSE 100 END) ASC, ADDRESS DESC;

Output

This would produce the following result −

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

SQL UPDATE View Statement

A view is a database object that can contain rows (all or selected) from an existing table. It can be created from one or many tables which depends on the provided SQL query to create a view.

Unlike CREATE VIEW and DROP VIEW there is no direct statement to update the records of an existing view. We can use the SQL UPDATE Statement to modify the existing records in a table or a view.

Syntax

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

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

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

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

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

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

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

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;

You can verify the contents of a view using the SELECT query as shown below −

SELECT * FROM CUSTOMERS_VIEW;

The view will be displayed as follows −

Following query updates the age of Ramesh to 35 in the above created CUSTOMERS_VIEW −

UPDATE CUSTOMERS_VIEW
SET AGE = 35 WHERE name = ''Ramesh

Verification

You can verify the contents of the CUSTOMERS_VIEW using the SELECT statement as follows −

SELECT * FROM CUSTOMERS_VIEW WHERE NAME =''Ramesh

The resultant view would have the following record(s) −

Example

The following query will update the ADDRESS of a customer whose ID is 6 in the CUSTOMERS_VIEW.

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

Output

The query produces the following output −

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

Verification

If you retrieve the record with ID value 6 using the SELECT statement as −

SELECT * FROM CUSTOMERS_VIEW WHERE ID=6;

The record returned would be −

Updating Multiple Rows and Columns

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

Example

Following query updates the NAME and AGE column values in the CUSTOMERS_VIEW of the record with ID value 3.

UPDATE CUSTOMERS_VIEW
SET NAME = ''Kaushik Ramanujan'', AGE = 24
WHERE ID = 3;

Output

The query produces the following output −

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

Verification

You can verify whether the record is updated or not, using the following query −

SELECT * FROM CUSTOMERS_VIEW WHERE ID = 3;

The record returned would be −

Example

But if you want to modify/update the age values of all the records in the CUSTOMERS_VIEW, there is no need to use the WHERE clause.

UPDATE CUSTOMERS_VIEW SET AGE = AGE+6;

Output

This query produces the following output −

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

Verification

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

SELECT * FROM CUSTOMERS_VIEW;

The resultant CUSTOMERS_VIEW would have the following records −

There are various SQL statements that perform different operations on database objects, such as creating, updating, deleting and also renaming a database object. And since a view is also a database object, all these operations can also be performed on a view, you can create a view, update a view, delete a view and also rename a view.

There is no direct query to rename a view in SQL. In MySQL we can rename a view using the RENAME TABLE statement and in MS SQL Server we can rename a view using the sp_rename procedure.

In many cases, deleting the existing view and then re-creating it with a new name is rather recommended.

Renaming a View in MySQL

The RENAME TABLE statement in MySQL database is used to rename views. You just have to make sure that the new name of the view does not overlap with the name of any existing views.

Syntax

Following is the basic syntax to rename a view in MySQL −

RENAME TABLE old_view_name To new_view_name;

Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Also before executing this statement, it is important to make sure that the table is not locked and there are no active transactions.

Example

In this example, let us first create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below −

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

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

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

The table will be created as follows −

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS
SELECT * FROM CUSTOMERS WHERE AGE > 25;

You can verify the contents of a view using the select query as shown below −

SELECT * from CUSTOMERS_VIEW;

The view is displayed as follows −

Now we know that a view with the name CUSTOMERS_VIEW exists in our database. So, we are directly going to rename this view to VIEW_CUSTOMERS, using the following query −

RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;

Output

The result obtained is as shown below −

Query OK, 0 rows affected (0.08 sec)

Verification

We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view −

SELECT * from VIEW_CUSTOMERS;

The view displayed is as follows −

Renaming a View in SQL Server

There isn”t a query in SQL Server that can rename a view directly. But, it does give you access to a stored procedure called sp_rename that can rename a view. You have to make sure there are no active transactions being performed on the view using its old name before renaming it.

The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints.

Syntax

Following is the basic syntax to rename a view in SQL −

EXEC sp_rename ''old_view_name'', ''new_view_name''

Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Before executing this statement, it is important to make sure that the table is not locked and there are no active transactions.

Example

In this example, let us first try to create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below −

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

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

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 );

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS
SELECT * FROM CUSTOMERS WHERE SALARY >2000;

You can verify the contents of a view using the select query as shown below −

SELECT * from CUSTOMERS_VIEW;

The view will be created as −

Now, we know that we have an existing view CUSTOMERS_VIEW in our database. So, we are going to rename this view to VIEW_CUSTOMERS, using the following query −

EXEC sp_rename CUSTOMERS_VIEW, VIEW_CUSTOMERS;

Verification

We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view −

SELECT * FROM VIEW_CUSTOMERS;

The view displayed is as follows −

We have renamed the view to VIEW_CUSTOMERS; if the user tries to get the details by using the old view name, it will throw an error showing that the view does not exist.

Rules to be followed while Renaming Views

When renaming views in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues.

Here are some general rules to keep in mind when renaming views in SQL −

• Avoid renaming system views − System views are views that contain all the information about the database management system. Renaming these views can cause issues with the functioning of the database system, so it is generally not recommended to rename system views.

• Update all references to the view − After renaming a view, any stored procedures, triggers, or other database objects that reference the view will need to be updated to use the new name of the view. Failure to update these references can result in errors or issues with the functioning of the database system.

• Test thoroughly − Before renaming a view in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the view have been updated correctly and that the database system continues to function as expected.

• Use a consistent naming convention − It is a good practice to use a consistent naming convention for views and other database objects to make it easier to understand and maintain the database system. If you need to rename a view, consider following the same naming convention that you have used for other views in the database.

• Backup the database − Before renaming a view, it is recommended to create a backup of the database to ensure that you have a restore point; in case anything goes wrong during the renaming process.

SQL allows you to drop an exiting view and delete records from a view in a database. SQL uses DROP statement to delete all the records from the view along with its definition and using the DELETE statement, only the records are deleted while the view definition of the view remains unchanged.

And note that if a record is deleted from a view, it is also deleted from its corresponding base table.

The DROP VIEW Statement

The SQL DROP VIEW statement is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also drop indexed views with this statement.

Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped explicitly using the DROP VIEW command.

• While trying to perform queries, the database engine checks all the objects referenced in that statement are valid and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error.

• To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.

Syntax

The basic syntax of this DROP VIEW statement is as follows −

DROP VIEW view_name;

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

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

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

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

Assume we have created 3 views using the CREATE VIEW statement as shown below −

CREATE VIEW CUSTOMERS_VIEW1 AS SELECT * FROM CUSTOMERS;
CREATE VIEW CUSTOMERS_VIEW2 AS SELECT * FROM CUSTOMERS;
CREATE VIEW CUSTOMERS_VIEW3 AS SELECT * FROM CUSTOMERS;

You can verify the list of all the views using the following query −

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA=''tutorials

This will display the list of views as follows −

Now, lets drop two views from the above created views using the DROP VIEW statement.

DROP VIEW CUSTOMERS_VIEW1;
DROP VIEW CUSTOMERS_VIEW2;

Verification

Once we have deleted all the views if you try to retrieve the list of views you will get an empty set as shown below −

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA=''tutorials

The remaining list of views is as follows −

The IF EXISTS clause

While deleting an existing view, you can use the IF EXISTS clause in the DROP VIEW statement. This clause, when specified in the DROP VIEW query, will automatically check whether the view exists in the current database and then drops it, if yes. If the view does not exist in the database, the query will be ignored.

Syntax

Following is the basic syntax of DROP VIEW IF EXISTS −

DROP VIEW [IF EXISTS] view_name;

Example

If you try to drop a view that doesn”t exist in the database, without using the IF EXISTS clause, as shown below −

DROP VIEW DEMO_VIEW;

An error will be generated −

ERROR 1051 (42S02): Unknown table ''tutorials.demo_view''

But if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the specified event will be dropped; and if a view with the given name doesn”t exist the query will be ignored.

DROP VIEW IF EXISTS DEMO_VIEW;

The query will be ignored with the following output displayed −

Query OK, 0 rows affected, 1 warning (0.04 sec)

Deleting Rows from a View

Instead of removing an entire view, we can also delete selected rows of a view using the DELETE statement.

Syntax

Following is the syntax of the DELETE statement −

DELETE FROM view_name WHERE condition;

Example

Following query deletes a record from the third_view created on the CUSTOMERS table created above. The changes made to the data in view will finally be reflected in the base table CUSTOMERS.

DELETE FROM CUSTOMERS_VIEW3 WHERE AGE = 22;

This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the view itself. Now, query the base table and the SELECT statement would produce the following result −

SELECT * FROM CUSTOMERS;

The CUSTOMERS table is displayed as −

The SQL Where Clause

The SQL WHERE clause is used to filter the results obtained by the DML statements such as SELECT, UPDATE and DELETE etc. We can retrieve the data from a single table or multiple tables(after join operation) using the WHERE clause.

For instance, you can use the WHERE clause to retrieve details of employees of a department in an organization, or employees earning salary above/below certain amount, or details of students eligible for scholarships etc. This clause basically provides the specification of which records to be retrieved and which are to be to be neglected.

Syntax

The basic syntax of the SQL WHERE clause is as shown below −

DML_Statement column1, column2,... columnN
FROM table_name
WHERE [condition];

Here, the DML_Statement can be any statement, such as SELECT, UPDATE, DELETE etc.

You can specify a condition using the such as, >, <, =, LIKE, NOT, etc.

WHERE Clause with SELECT Statement

Typically, the SELECT statement is used to retrieve data from a table. If we use the WHERE clause with the SELECT statement, we can filter the rows to be retrieved based on a specific condition (or expression). Following is the syntax for it −

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

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

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

Following INSERT query inserts 7 records into this table −

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

The table created is as shown below −

In the following query, we are fetching the ID, NAME and SALARY fields from the CUSTOMERS table for the records where the SALARY is greater than 2000 −

SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000;

Output

This would produce the following result −

WHERE Clause with UPDATE Statement

The UPDATE statement is used to modify the existing records in a table. Using the SQL WHERE clause with the UPDATE statement, we can update particular records. If the WHERE clause is not used, the UPDATE statement would affect all the records of a table. Following is the syntax −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

In the following query, we are incrementing the salary of the customer named Ramesh by 10000 by using the WHERE clause along with the UPDATE statement −

UPDATE CUSTOMERS set SALARY = SALARY+10000
where NAME = ''Ramesh

Output

We get the following result. We can observe that the age of 2 customers have been modified −

Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

To verify if the changes are reflected in the table, we can use SELECT statement as shown in the following query −

SELECT * FROM CUSTOMERS WHERE NAME = ''Ramesh

The table is displayed as follows −

WHERE Clause with IN Operator

Using the IN operator you can specify the list of values or sub query in the where clause. If you use WHERE and IN with the SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified list. Following is the syntax for it −

WHERE column_name IN (value1, value2, ...);

Where, the column_name is the column of a table and value1, value2, etc. are the list of values that we want to compare with the column_name.

Example

Suppose you want to display records with NAME values Khilan, Hardik and Muffy from the CUSTOMERS table, you can use the following query −

SELECT * from CUSTOMERS
WHERE NAME IN (''Khilan'', ''Hardik'', ''Muffy'');

Output

The result obtained is as follows −

WHERE Clause with NOT IN Operator

The WHERE clause with NOT IN operator is the negation of WHERE clause with the IN operator.

• If you use WHERE with the IN operator, the DML statement will act on the the list of values (of a column) specified
• Whereas, if you use WHERE with the NOT IN operator, the DML operation is performed on the values (of a column) that are not there in the specified list.

Hence, if you use WHERE Clause with NOT IN Operator along with the SELECT statement, the rows that do not match the list of values are retrieved. Following is the syntax −

WHERE column_name NOT IN (value1, value2, ...);

Example

In this example, we are displaying the records from CUSTOMERS table, where AGE is NOT equal to 25, 23 and 22.

SELECT * from CUSTOMERS WHERE AGE NOT IN (25, 23, 22);

Output

We obtain the result as given below −

WHERE Clause with LIKE Operator

The WHERE clause with LIKE operator allows us to filter rows that matches a specific pattern. This specific pattern is represented by wildcards (such as %, _, [] etc). Following is the syntax −

WHERE column_name LIKE pattern;

Where, column_name is the column that we want to compare the pattern against and pattern is a string that can contain wildcards (such as %, _, [] etc).

Example

Following is the query which would display all the records where the name starts with K and is at least 4 characters in length −

SELECT * FROM CUSTOMERS WHERE NAME LIKE ''K___%

Output

The result obtained is given below −

WHERE Clause with AND, OR Operators

We can use AND and OR operators together in SQL to combine multiple conditions in a WHERE clause to filter rows that meets the specified criteria. The AND operator will make sure only those rows are filtered that satisfy all the conditions and the OR operator will filter records that satisfy any one of the specified conditions. However, this is only used when specifying one condition is not enough to filter all the required rows.

Following is the syntax for using the AND and OR operators in a WHERE clause −

WHERE (condition1 OR condition2) AND condition3;

Example

In the following query, we are retrieving all rows from the CUSTOMERS table based on some conditions. The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator −

SELECT * FROM CUSTOMERS
WHERE (AGE = 25 OR salary < 4500)
AND (name = ''Komal'' OR name = ''Kaushik'');

Output

This would produce the following result −

The SQL TOP Clause

While we are retrieving data from an SQL table, the SQL TOP clause is used to restrict the number of rows returned by a SELECT query in SQL server. In addition, we can also use it with UPDATE and DELETE statements to limit (restrict) the resultant records.

For instance, if you have a large number of data stored in a database table, and you only want to perform operations on first N rows, you can use the TOP clause in your SQL server query.

MySQL database does not support TOP clause instead of this, we can use the clause to select a limited number of records from a MySQL table. Similarly, Oracle supports the ROWNUM clause to restrict the records of a table. The TOP clause is similar to the LIMIT clause.

Syntax

The basic syntax of the SQL TOP clause is as follows −

SELECT TOP value column_name(s)
FROM table_name
WHERE [condition]

Where, value is the number/ percentage of number of rows to return from the top.

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 −

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

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

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

The table will be created as follows −

Now, we are using the TOP clause to fetch the top 4 records from the CUSTOMERS table without specifying any conditional clauses such as WHERE, ORDER BY, etc. −

SELECT TOP 4 * FROM CUSTOMERS;

Output

This would produce the following result −

TOP with ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result-set of a query in ascending or descending order. We can use it along with the TOP clause to retrieve (or, update or, delete etc.) first N records in sorted order.

Example

Using the following query we are retrieving the top 4 records of the CUSTOMERS table in a sorted order. Here, we are sorting the table in descending order based on the SALARY column −

SELECT TOP 4 * FROM CUSTOMERS ORDER BY SALARY DESC;

Output

We obtain the result as follows −

Note − By default, the ORDER BY clause sorts the data in ascending order. So, if we need to sort the data in descending order, we must use the DESC keyword.

TOP Clause with PERCENT

We can also restrict the records by specifying percentage value instead of number, using the PERCENT clause along with the TOP clause.

Example

The following query selects the first 40% of the records from the CUSTOMERS table sorted in the ascending order by their SALARY −

SELECT TOP 40 PERCENT * FROM CUSTOMERS ORDER BY SALARY

Output

We have the total of 7 records in our table. So 40% of 7 is 2.8. Therefore, SQL server rounds the result to three rows (the next whole number) as shown in the output below −

TOP with WHERE Clause

We can use the TOP clause with the WHERE clause to limit the given number of rows and filter them based on a specified condition.

Example

Following is the query to show the details of the first two customers whose name starts with K from the CUSTOMERS table −

SELECT TOP 2 * FROM CUSTOMERS WHERE NAME LIKE ''k%''

Output

Following result is produced −

TOP Clause With DELETE Statement

The TOP clause can be used with the DELETE statement to delete a specific number of rows that meet the given criteria.

Example

In the following query, we are using DELETE statement with TOP clause. Here, we are deleting the top 2 customers whose NAME starts with K −

DELETE TOP(2) FROM CUSTOMERS WHERE NAME LIKE ''K%

Output

We get the output as shown below −

(2 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as shown below −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

TOP and WITH TIES Clause

While sorting the data in a table using the ORDER BY clause based on a column, some times multiple rows may contain same values in the column(s) specified in the ORDER BY clause.

If you try to restrict the number of records using the TOP clause, all the eligible columns may not be filtered.

The WITH TIES clause is used to ensure that the records having the same values (records with “tied” values) are included in the query results.

Example

Consider the above created table CUSTOMERS. If we need to retrieve the top 2 customers sorted by the ascending order of their SALARY values, the query would be −

SELECT TOP 2 * FROM CUSTOMERS ORDER BY SALARY;

The resultant table would be −

But, the first two salary values (in ascending order) in the table are 1500 and 2000 and there is another column in the CUSTOMERS table with salary value 2000 which is not included in the result.

If you want to retrieve all the columns with first two salary values (when arranged in the ascending order). We need to use the WITH TIES clause as showb below −

SELECT TOP 2 WITH TIES * FROM CUSTOMERS ORDER BY SALARY;

Output

The resultant table would be −

Uses of TOP Clause

Some common use cases for the TOP clause include −

• Pagination − When displaying a large number of records, the TOP clause can be used to return only a certain number of records at a time, allowing for more manageable and efficient pagination of the data.

• Sampling data − The TOP clause can be used to quickly retrieve a sample of data from a table for testing or analysis.

• Improving performance − By limiting the number of rows returned, the TOP clause can help improve the performance of a query, especially when dealing with large tables.

• Debugging − When developing or debugging a query, the TOP clause can be used to quickly return a small number of rows to test the correctness of the query.

• Data visualization − The TOP clause can be used to limit the number of rows returned for visualization purposes, such as creating charts or graphs.

The SQL ORDER BY Clause

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

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

Following are the important points about ORDER BY Clause −

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

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

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

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

Syntax

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

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

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

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

ORDER BY Clause with ASC

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

Example

Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement as shown below −

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

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

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

The table obtained is as shown below −

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

SELECT * FROM CUSTOMERS ORDER BY NAME ASC;

Output

This would produce the following result −

ORDER BY Clause with DESC

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

Example

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

SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

Output

This would produce the result as follows −

ORDER BY Clause on Multiple Columns

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

Example

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

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

Output

Following is the result produced −

ORDER BY with WHERE Clause

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

Example

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

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

Output

Following is the output of the above query −

ORDER BY with LIMIT Clause

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

Syntax

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

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

Example

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

SELECT SALARY FROM CUSTOMERS ORDER BY NAME LIMIT 4;

Output

Following is the output of the above query −

Sorting Results in a Preferred Order

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

Example

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

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

Output

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

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

SQL DELETE TABLE Statement

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

Syntax

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

DELETE FROM table_name;

SQL DELETE TABLE with WHERE Clause

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

Syntax

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

DELETE FROM table_name
WHERE condition;

Example

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

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

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

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

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

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

DELETE FROM CUSTOMERS WHERE NAME=''Hardik

Output

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

Query OK, 1 row affected (0.05 sec)

Verification

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

Deleting rows based on multiple conditions

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

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

Syntax

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

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

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

Example

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

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

Output

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

Query OK, 2 rows affected (0.03 sec)

Verification

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

Deleting all the records in a table

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

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

Example

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

DELETE FROM CUSTOMERS;

Output

Following is the result produced by executing the above query −

Query OK, 4 rows affected (0.13 sec)

Verification

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

Empty set (0.00 sec)