My Blog

Author: saqibkhan

  • Top Clause

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

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

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

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

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

    SELECTTOP4*FROM CUSTOMERS;

    Output

    This would produce the following result −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00

    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 −

    SELECTTOP4*FROM CUSTOMERS ORDERBY SALARY DESC;

    Output

    We obtain the result as follows −

    IDNAMEAGEADDRESSSALARY
    7Muffy24Indore10000.00
    5Hardik27Bhopal8500.00
    4Chaitali25Mumbai6500.00
    6Komal22Hyderabad4500.00

    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 −

    SELECTTOP40PERCENT*FROM CUSTOMERS ORDERBY 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 −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    1Ramesh32Ahmedabad2000.00

    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 −

    SELECTTOP2*FROM CUSTOMERS WHERE NAME LIKE'k%'

    Output

    Following result is produced −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00

    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 −

    DELETETOP(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 −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

    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 −

    SELECTTOP2*FROM CUSTOMERS ORDERBY SALARY;

    The resultant table would be −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00

    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 −

    SELECTTOP2WITH TIES *FROM CUSTOMERS ORDERBY SALARY;

    Output

    The resultant table would be −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    1Ramesh32Ahmedabad2000.00

    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.

  • Where Clause

    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 comparison or logical operators 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 −

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

    Following INSERT query inserts 7 records into this 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 created is as shown below −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

    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 −

    IDNAMESALARY
    4Chaitali6500.00
    5Hardik8500.00
    6Komal4500.00
    7Muffy10000.00

    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+10000where 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 −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad12000.00

    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 −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    5Hardik27Bhopal8500.00
    7Muffy24Indore10000.00

    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 NOTIN(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 NOTIN(25,23,22);

    Output

    We obtain the result as given below −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad12000.00
    5Hardik27Bhopal8500.00
    7Muffy24Indore10000.00

    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 −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    6Komal22Hyderabad4500.00

    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 =25OR salary <4500)AND(name ='Komal'OR name ='Kaushik');

    Output

    This would produce the following result −

    IDNAMEAGEADDRESSSALARY
    3Kaushik23Kota2000.00

  • Rename View

    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 −

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

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

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

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

    CREATEVIEW CUSTOMERS_VIEW ASSELECT*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 −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    5Hardik27Bhopal8500.00

    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 −

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

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    5Hardik27Bhopal8500.00

    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 −

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

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

    CREATEVIEW CUSTOMERS_VIEW ASSELECT*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 −

    IDNAMEAGEADDRESSSALARY
    4Chaitali25Mumbai6500.00

    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 −

    IDNAMEAGEADDRESSSALARY
    4Chaitali25Mumbai6500.00

    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.

  • Drop or Delete View

    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 −

    DROPVIEW view_name;

    Example

    Assume we have created a table named CUSTOMERS using the CREATE TABLE statement 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);

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

    CREATEVIEW CUSTOMERS_VIEW1 ASSELECT*FROM CUSTOMERS;CREATEVIEW CUSTOMERS_VIEW2 ASSELECT*FROM CUSTOMERS;CREATEVIEW CUSTOMERS_VIEW3 ASSELECT*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 −

    TABLE_SCHEMATABLE_NAME
    tutorialsCUSTOMERS_VIEW1
    tutorialsCUSTOMERS_VIEW2
    tutorialsCUSTOMERS_VIEW3

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

    DROPVIEW CUSTOMERS_VIEW1;DROPVIEW 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 −

    TABLE_SCHEMATABLE_NAME
    tutorialsCUSTOMERS_VIEW3

    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 −

    DROPVIEW[IFEXISTS] 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 −

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

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

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

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

    IDNAMEAGEADDRESSSALARY
    1Ramesh35Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    7Muffy24Indore10000.00

  • Update View

    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 −

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

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

    CREATEVIEW CUSTOMERS_VIEW ASSELECT*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 −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

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

    UPDATE CUSTOMERS_VIEW 
SET AGE =35WHERE 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) −

    IDNAMEAGEADDRESSSALARY
    1Ramesh35Ahmedabad2000.00

    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 −

    IDNAMEAGEADDRESSSALARY
    6Komal22Hyderabad4500.00

    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 =24WHERE 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 −

    IDNAMEAGEADDRESSSALARY
    3Kaushik Ramanujan24Kota2000.00

    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 −

    IDNAMEAGE
    1Ramesh41
    2Khilan31
    3Kaushik Ramanujan30
    4Chaitali31
    5Hardik33
    6Komal28
    7Muffy30

  • Create View

    What is SQL View

    A view in SQL is a virtual table that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query. A view can contain rows from an existing table (all or selected). A view can be created from one or many tables. Unless indexed, a view does not exist in a database.

    The data in the view does not exist in the database physically. A view is typically created by the database administrator and is used to −

    • Structure data in a way that users or classes of users find natural or intuitive.
    • Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
    • Summarize data from various tables which can be used to generate reports.

    The SQL CREATE VIEW Statement

    To create a view in a database, you can use the SQL CREATE VIEW statement.

    Syntax

    Following is the syntax of the SQL CREATE VIEW statement −

    CREATEVIEW view_name ASSELECT column1, column2....FROM table_name
WHERE[condition];

    Example

    Assume we have created a table named CUSTOMERS using the CREATE TABLE statement 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);

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

    CREATEVIEW CUSTOMERS_VIEW ASSELECT*FROM CUSTOMERS;

    Verification

    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 −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

    Create View With WHERE Clause

    We can also create a view with only specific records from a table using the where clause along with the SQL CREATE VIEW statement as shown below −

    CREATEVIEW BUYERS_VIEW asSELECT*FROM CUSTOMERS 
WHERE SALARY >3000;

    Verification

    Following are the contents of the above created view −

    SELECT*FROM BUYERS_VIEW;

    The view is displayed as follows −

    IDNAMEAGEADDRESSSALARY
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

    The WITH CHECK OPTION Clause

    The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) specified by the WHERE clause.

    If they do not satisfy the condition(s), the UPDATE or INSERT statements return an error. The following example creates the view named BUYERS_VIEW with the WITH CHECK OPTION clause.

    CREATEVIEW MY_VIEW ASSELECT name, age
FROM  CUSTOMERS
WHERE age >=25WITHCHECKOPTION;

    The WITH CHECK OPTION in this case should deny the entry and updates of the of records whose age value is greater than or equal to 25.

    Verification

    Following are the contents of the above created view −

    SELECT*FROM MY_VIEW;

    The view is displayed as follows −

    NAMEAGE
    Ramesh32
    Khilan25
    Chaitali25
    Hardik27

  • Sorting Results

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

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

    Syntax

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

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

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

    Sorting Results in Ascending Order

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

    Example

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

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

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

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

    The table will be created as −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

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

    SELECT*FROM CUSTOMERS ORDERBY NAME;

    Output

    This would produce the following result −

    IDNAMEAGEADDRESSSALARY
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    3kaushik23Kota2000.00
    2Khilan25Delhi1500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00
    1Ramesh32Ahmedabad2000.00

    Sorting Results in Descending Order

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

    Example

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

    SELECT*FROM CUSTOMERS ORDERBY NAME DESC;

    Output

    This would produce the following result −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    7Muffy24Indore10000.00
    6Komal22Hyderabad4500.00
    2Khilan25Delhi1500.00
    3kaushik23Kota2000.00
    5Hardik27Bhopal8500.00
    4Chaitali25Mumbai6500.00

    Sorting Results in a Preferred Order

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

    Example

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

    SELECT*FROM CUSTOMERS
ORDERBY(CASE ADDRESS
   WHEN'DELHI'THEN1WHEN'BHOPAL'THEN2WHEN'KOTA'THEN3WHEN'AHMEDABAD'THEN4WHEN'Hyderabad'THEN5ELSE100END)ASC, ADDRESS DESC;

    Output

    This would produce the following result −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    5Hardik27Bhopal8500.00
    3kaushik23Kota2000.00
    1Ramesh32Ahmedabad2000.00
    6Komal22Hyderabad4500.00
    4Chaitali25Mumbai6500.00
    7Muffy24Indore10000.00

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

  •  Delete Query

    The SQL DELETE Statement

    The SQL DELETE Statement is used to delete the records from an existing table. In order to filter the records to be deleted (or, delete particular records), we need to use the WHERE clause along with the DELETE statement.

    If you execute DELETE statement without a WHERE clause, it will delete all the records from the table.

    Using the DELETE statement, we can delete one or more rows of a single table and records across multiple tables.

    Syntax

    The basic syntax of the SQL DELETE Query with the WHERE clause is as follows −

    DELETEFROM table_name WHERE[condition];

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

    Example

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

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

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

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

    The table will be created as −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

    The following query deletes the record of a customer, whose ID is 6.

    DELETEFROM CUSTOMERS WHERE ID =6;

    Output

    The output will be displayed as −

    Query OK, 1 row affected (0.10 sec)

    Verification

    To verify whether the record(s) have been deleted from the table, we need to retrieve the modified table using the SELECT query, as shown below −

    SELECT*FROM CUSTOMERS;

    Now, the CUSTOMERS table would have the following records −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    7Muffy24Indore10000.00

    Deleting Multiple Rows

    To delete multiple rows from a table, we need to specify the required condition(s), that is satisfied by all the rows to be deleted, using the WHERE clause. Let us look at an example −

    Example

    From the same Customers table, let us try to delete the records of customers who are over 25 years of age.

    DELETEFROM CUSTOMERS WHERE AGE >25;

    Output

    The output will be displayed as −

    Query OK, 2 rows affected (0.06 sec)

    Verification

    To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

    SELECT*FROM CUSTOMERS;

    The query above will produce the following table −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    7Muffy24Indore10000.00

    Deleting All The Records From a Table

    If we want to DELETE all the records from an existing table (truncate it) using the DELETE query, we simply need to run it without using the WHERE clause.

    Example

    Following SQL query removes all the records from the CUSTOMERS table −

    DELETEFROM CUSTOMERS;

    Output

    The output will be displayed as −

    Query OK, 4 rows affected (0.13 sec)

    Verification

    To verify whether all the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

    SELECT*FROM CUSTOMERS;

    Now, the CUSTOMERS table would not have any record and will show the following output −

    Empty set (0.00 sec)

    Delete Records in Multiple Tables

    SQL allows us to delete the records from multiple tables using the DELETE query. In here, we will use the JOIN clause to combine data from multiple tables (based on a common column).

    Example

    Let us create another table with name ORDERS which contains the details of the orders made by the customers.

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

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

    INSERTINTO ORDERS VALUES(102,'2009-10-08 00:00:00',3,3000.00),(100,'2009-10-08 00:00:00',3,1500.00),(101,'2009-11-20 00:00:00',2,1560.00),(103,'2008-05-20 00:00:00',4,2060.00);

    The table created is as shown below −

    OIDDATECUSTOMER_IDAMOUNT
    1022009-10-08 00:00:0033000.00
    1002009-10-08 00:00:0031500.00
    1012009-11-20 00:00:0021560.00
    1032008-05-20 00:00:0042060.00

    Following SQL query deletes the records of the customers (from the tables CUSTOMERS and ORDERS) who earn more than 2000 and have placed orders −

    DELETE CUSTOMERS, ORDERS FROM CUSTOMERS
INNERJOIN ORDERS ON ORDERS.CUSTOMER_ID = CUSTOMERS.ID
WHERE CUSTOMERS.SALARY >2000;

    Output

    The output will be displayed as −

    Query OK, 2 rows affected (0.01 sec)

    Verification

    To verify whether the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

    SELECT*FROM CUSTOMERS;

    The CUSTOMERS table would not have the record(s) where salary is greater than 2000 and the ID matches with the CUSTOMER_ID column in the ORDERS table.

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    5Hardik27Bhopal8500.00
    6Komal22MP4500.00
    7Muffy24Indore10000.00

    Similarly, if you verify the ORDERS table as shown below −

    SELECT*FROM ORDERS;

    Since salary is greater than 2000 and the CUSTOMER_ID matches with the ID value in the CUSTOMERS table, the last record (OID 103) of the ORDERS table will be deleted −

    OIDDATECUSTOMER_IDAMOUNT
    1022009-10-08 00:00:0033000.00
    1002009-10-08 00:00:0031500.00
    1012009-11-20 00:00:0021560.00

  • Update Query

    The SQL UPDATE Statement

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

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

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

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

    Syntax

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

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

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

    Example

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

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

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

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

    The table will be created as −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

    The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

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

    Output

    The query produces the following output −

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

    Verification

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

    SELECT*FROM CUSTOMERS WHERE ID=6;

    Now, the CUSTOMERS table would have the following records −

    IDNAMEAGEADDRESSSALARY
    6Komal22Pune4500.00

    Update Multiple ROWS and COLUMNS

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

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

    Syntax

    Following is the syntax to update multiple rows and columns −

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

    Example

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

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

    Output

    The query produces the following output −

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

    Verification

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

    SELECT*FROM CUSTOMERS;

    Now, CUSTOMERS table would have the following records −

    IDNAMEAGEADDRESSSALARY
    1Ramesh37Ahmedabad5000.00
    2Khilan30Delhi4500.00
    3Kaushik28Kota5000.00
    4Chaitali30Mumbai9500.00
    5Hardik32Bhopal11500.00
    6Komal27Pune7500.00
    7Muffy29Indore13000.00

    Example

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

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

    Output

    This query produces the following output −

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

    Verification

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

    SELECT*FROM CUSTOMERS WHERE NAME ='Ramesh';

    Now, CUSTOMERS table would have the following records −

    IDNAMEAGEADDRESSSALARY
    1Ramesh37Pune1000.00

  • Insert Into… Select Statement

    The Insert Into… Select Statement

    The SQL INSERT INTO… SELECT statement is used to add/insert one or more new rows from an existing table to another table. This statement is a combination of two different statements: INSERT INTO and SELECT.

    • The INSERT INTO statement is one of the most fundamental and frequently used statements in database management and requires only the name of the table and the values to be inserted. However, it is important to ensure that the data being inserted satisfies the constraints if the columns of a table (if any) and its type matches the data types of the table columns.
    • The SELECT statement is used to retrieve data from an existing database table.

    When these statements are used together, the SELECT statement first retrieves the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).

    Syntax

    Following is the syntax of the SQL INSERT INTO… SELECT statement −

    INSERTINTO table_new 
SELECT(column1, column2,...columnN)FROM table_old;

    Before using this query, we have to make sure that −

    • In the database where we are going to insert data, source and target tables already exist.
    • The structure of the source and target tables are same.

    Example

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

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

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

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

    The table will be created as −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

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

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

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

    INSERTINTO BUYERS SELECT*FROM CUSTOMERS;

    Verification

    If you verify the contents of the BUYERS table using the SELECT statement as −

    SELECT*FROM BUYERS;

    The table will be created as −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    4Chaitali25Mumbai6500.00
    5Hardik27Bhopal8500.00
    6Komal22Hyderabad4500.00
    7Muffy24Indore10000.00

    SQL – Inserting Specific Records

    Sometimes we only need to add a small number of records to another table. This can be accomplished by using a WHERE clause along with the SQL INSERT INTO… SELECT statement.

    Example

    Let us create a table named NAMESTARTSWITH_K with the same structure as the CUSTOMER table using the CREATE statement as −

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

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

    Open Compiler

    INSERTINTO NAMESTARTSWITH_K
SELECT*FROM CUSTOMERS
WHERE NAME LIKE'k%';

    Verification

    Following is the SELECT statement to verify the contents of the above created table −

    SELECT*FROM NAMESTARTSWITH_K;

    The table will be created as −

    IDNAMEAGEADDRESSSALARY
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00
    6Komal22Hyderabad4500.00

    SQL – Inserting Top N Rows

    The LIMIT clause filters the number of rows from the query. You can use this to filter the top N records that should be added to the target table.

    Example

    But, before proceeding further, let us truncate all rows in the BUYERS table using the following statement −

    TRUNCATETABLE BUYERS;

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

    INSERTINTO BUYERS 
SELECT*FROM CUSTOMERS 
ORDERBY ID ASCLIMIT3;

    Verification

    Let us verify the contents of the BUYERS table −

    SELECT*FROM BUYERS;

    The resultant table will be as follows −

    IDNAMEAGEADDRESSSALARY
    1Ramesh32Ahmedabad2000.00
    2Khilan25Delhi1500.00
    3Kaushik23Kota2000.00