Category: sql

SQL – TOP Clause Table of content The SQL TOP Clause TOP with ORDER BY Clause TOP Clause with PERCENT TOP with WHERE Clause TOP Clause With DELETE Statement TOP and WITH TIES Clause Uses of TOP Clause ”; Previous Next 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 − 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 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 − ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 10000.00 5 Hardik 27 Bhopal 8500.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.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 − 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 − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 1 Ramesh 32 Ahmedabad 2000.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 − SELECT TOP 2 * FROM CUSTOMERS WHERE NAME LIKE ”k%” Output Following result is produced − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.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 − 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 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 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

SQL – CASE Table of content The SQL CASE Statement CASE Statement with ORDER BY Clause CASE Statement with GROUP BY Clause CASE Statement with WHERE Clause CASE Statement with UPDATE CASE Statement with INSERT ”; Previous Next The SQL CASE Statement The SQL CASE statement is a conditional statement that helps us to make decisions based on a set of conditions. It evaluates the set of conditions and returns the respective values when a condition is satisfied. The CASE statement works like a simplified IF-THEN-ELSE statement and allows for multiple conditions to be tested. This starts with the keyword CASE followed by multiple conditionals statements. Each conditional statement consists of at least one pair of WHEN and THEN statements. Where WHEN specifies conditional statements and THEN specifies the actions to be taken. It is often used to create a new column with values based on the value of an existing column. Let us look at a simple scenario to understand this statement. For e.g. when the credit limit of a customer is above ”10,000”, then the customer will be recognized as a ”High value customer”; when the credit limit is above ”5000”, then the customer will be recognized as a ”Mid value customer”; otherwise the customer will be recognized as the ”Low value customer” as shown in the table below − Syntax Following is the syntax of SQL CASE statement − CASE WHEN condition1 THEN statement1, WHEN condition2 THEN statement2, WHEN condition THEN statementN ELSE result END; Where, condition1, condition2, etc. Are the conditional statements and statement1, statement2, etc.. are the actions to be taken when the condition is true. Once the condition is met, the CASE statement will stop verifying further and it will return the result. If none of the conditions are met (TRUE), then it returns the value mentioned in the ELSE clause. It returns NULL if the ELSE part is not mentioned and none of the conditions are TRUE. Example Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 In the following query, we are using multiple WHEN and THEN conditions to the CASE statement along with the ELSE clause. If the AGE of the customer is greater than 30, it returns Gen X otherwise moves to the further WHEN and THEN conditions. If none of the conditions is matched with the CUSTOMERS table, CASE returns the ”Gen Alpha” value as mentioned in the ELSE part of the query − SELECT NAME, AGE, CASE WHEN AGE > 30 THEN ”Gen X” WHEN AGE > 25 THEN ”Gen Y” WHEN AGE > 22 THEN ”Gen Z” ELSE ”Gen Alpha” END AS Generation FROM CUSTOMERS; Output The output produced is as follows − NAME AGE Generation Ramesh 32 Gen X Khilan 25 Gen Z Kaushik 23 Gen Z Chaitali 25 Gen Z Hardik 27 Gen Y Komal 22 Gen Alpha Muffy 24 Gen Z Example Let us take a look at another query where we want to provide a 25% increment to each customer if the amount is less than 4500 from the CUSTOMERS table previously created − SELECT *, CASE WHEN SALARY < 4500 THEN (SALARY + SALARY * 25/100) END AS INCREMENT FROM CUSTOMERS; Output Here, the SQL command checks if the salary is less than 4500. If this condition is satisfied, a new column ”INCREMENT” will contain the values that is equal to salary with 25% of increment. Since the ELSE part is not mentioned in the above query and none of the conditions are true for few CUSTOMERS, NULL is returned, which shows that they didn”t get any increment. ID NAME AGE ADDRESS SALARY INCREMENT 1 Ramesh 32 Ahmedabad 2000.00 2500.000000 2 Khilan 25 Delhi 1500.00 1875.000000 3 Kaushik 23 Kota 2000.00 2500.000000 4 Chaitali 25 Mumbai 6500.00 NULL 5 Hardik 27 Bhopal 8500.00 NULL

SQL – NOT EQUAL Table of content The SQL NOT EQUAL Operator NOT EQUAL with Text NOT EQUAL with GROUP BY Clause NOT EQUAL with Multiple Conditions Negating a Condition Using NOT EQUAL ”; Previous Next The SQL NOT EQUAL Operator The SQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn”t. So, it is recommended to use the <> operator. We can use the NOT EQUAL operator in WHERE clause to filter records based on a specific condition and in GROUP BY clause to group the results. The comparison is case-sensitive by default, while using the NOT EQUAL operator with text values. Syntax Following is the syntax of the NOT EQUAL operator in SQL − WHERE expression1 <> expression2; 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 NOT EQUAL with Text We can use the NOT EQUAL operator with text in SQL to compare two text values and return. We can use “<>” or “!=” in the WHERE clause of a SQL statement and exclude rows that match a specific text value. Example In the following query, we are retrieving all the records from the CUSTOMERS table whose NAME is not ”Ramesh” − SELECT * FROM CUSTOMERS WHERE NAME <> ”Ramesh”; Output The output of the above code is as shown below − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 NOT EQUAL with GROUP BY Clause We can use the NOT EQUAL operator with the GROUP BY clause to group the results by the values that are not equal to the specified text value. The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement. Example Here, we are retrieving the number of records with distinct ages (excluding ”22”) in the ”CUSTOMERS” table and grouping them by age value − SELECT COUNT(ID), AGE FROM CUSTOMERS WHERE AGE <> ”22” GROUP BY AGE; Output On executing the above query, it will generate the output as shown below − COUNT(id) AGE 1 32 2 25 1 23 1 27 1 24 NOT EQUAL with Multiple Conditions The not equal operator can also be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria. Example Now, we are retrieving all the customers whose salary is either “>2000” or “=2000“. At the same time, the customer must not be from “Bhopal” − SELECT * FROM CUSTOMERS WHERE ADDRESS <> ”Bhopal” AND (SALARY>”2000” OR SALARY=”2000”); Output Following is the output of the above code − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Negating a Condition Using NOT EQUAL In SQL, the NOT EQUAL operator can also be combined with the NOT Operator to negate a condition. It filters out the rows that meet a specific condition. Example In the following query, we are retrieving all rows from the “CUSTOMERS” table where the “SALARY” is equal to ”2000” − SELECT * FROM CUSTOMERS WHERE NOT SALARY != ”2000”; Output After executing the above code, we get the following output − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 Print Page Previous Next Advertisements ”;

SQL – CREATE View Table of content What is SQL View The SQL CREATE VIEW Statement Create View With WHERE Clause The WITH CHECK OPTION Clause ”; Previous Next 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 − CREATE VIEW view_name AS SELECT 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 − 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; 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 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 − CREATE VIEW BUYERS_VIEW as SELECT * 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 − ID NAME AGE ADDRESS SALARY 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 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. CREATE VIEW MY_VIEW AS SELECT name, age FROM CUSTOMERS WHERE age >= 25 WITH CHECK OPTION; 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 − NAME AGE Ramesh 32 Khilan 25 Chaitali 25 Hardik 27 Print Page Previous Next Advertisements ”;

SQL – IN Operator Table of content The SQL IN Operator IN Operator with SELECT Statement IN Operator with UPDATE Statement IN Operator with NOT IN Operator with Column Name Subquery with IN Operator ”; Previous Next The SQL IN Operator The SQL IN Operator is used to specify multiple values or sub query in the WHERE clause. It returns all rows in which the specified column matches one of the values in the list. The list of values or sub query must be specified in the parenthesis e.g. IN (select query) or IN (Value1, Value2, Value3, …). In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. Alternatively, we can use the IN operator instead of multiples OR statements. The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values. The IN operator is useful when you want to select all rows that match one of a specific set of values. While the OR operator is useful when you want to select all rows that match any one of multiple conditions. Syntax The basic syntax of the SQL IN operator to specify multiple values is as follows − WHERE column_name IN (value1, value2, value3, …); Where, value1, value2, value3, … are the values in the list to be tested against the expression. The IN operator returns TRUE if any of these values is found in the list, and FALSE if it is not. IN Operator with SELECT Statement We can use the SQL IN operator to specify multiple values in a WHERE clause, and we can also use it in a SELECT statement to retrieve data that matches any of the specified values. Here, we are using the IN operator to specify multiple values in SELECT statement. Example In this example, we are using the IN operator to specify multiple values in SELECT statement 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Suppose based on the above table we want to display records with NAME equal to ”Khilan”, ”Hardik” and ”Muffy”(string values). This can be achieved using IN operator as follows − SELECT * FROM CUSTOMERS WHERE NAME IN (”Khilan”, ”Hardik”, ”Muffy”); Output The result obtained is as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 Example The above query can also be done using OR operator. Following is an example − SELECT * FROM CUSTOMERS WHERE NAME = ”Khilan” OR NAME = ”Hardik” OR NAME = ”Muffy”; Output ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 IN Operator with UPDATE Statement We can also use the SQL IN operator in an UPDATE statement to update rows that match any of the specified values in a WHERE clause. The UPDATE statement is used to modify existing data in a database table. Example Here, we are using the IN operator to specify multiple values in the UPDATE statement and updating the CUSTOMERS table previously created. Here, we are changing the records of the customers with age ”25” or ”27” and updating the age value to ”30” − UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27); Output We get the following result. We can observe that the age of 3 customers has 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 30 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 30 Mumbai 6500.00 5 Hardik 30 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 As we can see in the above table, the AGE of ”Khilan”, ”Chaitali” and ”Hardik” has been updated to ”30”. IN Operator with NOT To negate a condition, we use the NOT operator. The SQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause. In other words, the absence of a list from an expression will be checked. Syntax Following is the basic syntax of NOT IN operator − WHERE column_name NOT IN (value1, value2, …); Example Now, we are displaying all the records from the CUSTOMERS table, where the 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 IN Operator with Column Name We can also use the SQL IN operator with a column name to compare the values of one column to another. It

SQL – Like Operator Table of content The SQL LIKE Operator What are wild cards? The ”%” Wildcard character The ”_” wildcard character LIKE operator with OR NOT operator with the LIKE condition Escape characters with LIKE operator Uses of LIKE Operator in SQL ”; Previous Next The SQL LIKE Operator The SQL LIKE operator is used to retrieve the data in a column of a table, based on a specified pattern. It is used along with the WHERE clause of the UPDATE, DELETE and SELECT statements, to filter the rows based on the given pattern. These patterns are specified using Wildcards. Suppose we need to submit the list of all the students whose name starts with ”K”. We can obtain this with the help of the LIKE operator as follows − WHERE student_name LIKE ”K%”; Here, the % is a wild card which represents zero, one or multiple characters. And the expression K% specifies that it will display the list of all the students whose name starts with ”k”. The LIKE operator can be used with strings, numbers, or date values. However, using the string values is recommended. Syntax The basic syntax of the SQL LIKE operator is as follows − SELECT column1, column2, … FROM table_name WHERE columnn LIKE specified_pattern; What are wild cards? SQL wildcards are special characters used in SQL queries to match patterns in the data. Following are the wildcards used in conjunction with the LIKE operator in MySQL database − S.No WildCard & Definition 1 % The percent sign represents zero, one or multiple characters. 2 _ The underscore represents a single number or character. In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The table given below has a few examples showing the WHERE clause having different LIKE operators with ”%” and ”_” − S.No Statement & Description 1 WHERE SALARY LIKE ”200%” Finds any values that start with 200. 2 WHERE SALARY LIKE ”%200%” Finds any values that have 200 in any position. 3 WHERE SALARY LIKE ”_00%” Finds any values that have 00 in the second and third positions. 4 WHERE SALARY LIKE ”2_%_%” Finds any values that start with 2 and are at least 3 characters in length. 5 WHERE SALARY LIKE ”%2” Finds any values that end with 2. 6 WHERE SALARY LIKE ”_2%3” Finds any values that have a 2 in the second position and end with a 3. 7 WHERE SALARY LIKE ”2___3” Finds any values in a five-digit number that start with 2 and end with 3. The ”%” Wildcard character The % sign represents zero or multiple characters. The ”%” wildcard matches any length of a string which even includes the zero length. 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, let us display all the records from the CUSTOMERS table, where the SALARY starts with 200 − SELECT * FROM CUSTOMERS WHERE SALARY LIKE ”200%”; Output This would produce the following result − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 Example Below is the query that displays all the records from the CUSTOMERS table previously created with the NAME that has ”al” in any position. Here, we are using multiple ”%” wildcards in the LIKE condition − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”%al%”; Output The following result is produced − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 The ”_” wildcard character The underscore wild card represents a single number or character. A single ”_” looks for exactly one character similar to the ”%” wildcard. Example Following is the query which would display all the records from the CUSTOMERS table previously created, 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 − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 6 Komal 22 Hyderabad 4500.00 Example Following is the query to display all the records from the CUSTOMERS table, where the NAME has ”m” in the third position − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”__m%”; Output We get the following result on executing the above query − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 6 Komal 22 Hyderabad 4500.00 LIKE operator with OR We can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators. Syntax Following is the basic syntax of using LIKE operator with OR operator − SELECT column1, column2, … FROM table_name WHERE column1 LIKE pattern1 OR column2 LIKE pattern2 OR …; Example Here, the SQL query retrieves the records of the customers whose name starts with C and ends with i, or customers whose name ends with k − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”C%i” OR NAME LIKE ”%k”; Output This will produce the following result

SQL – Insert Into… Select Statement Table of content The Insert Into… Select Statement SQL – Inserting Specific Records SQL – Inserting Top N Rows ”; Previous Next 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 − INSERT INTO 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 − 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Create another table named BUYERS with same structure as the CUSTOMERS table. CREATE TABLE BUYERS ( 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 copies all the records from the CUSTOMERS table to BUYERS − INSERT INTO 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 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 − CREATE TABLE NAMESTARTSWITH_K ( 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 the records of the customers whose name starts with the letter k from the CUSTOMERS table to the BUYERS table − INSERT INTO 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 − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 6 Komal 22 Hyderabad 4500.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 − TRUNCATE TABLE BUYERS; Following query inserts the top 3 records from the CUSTOMERS table to the BUYERS table − INSERT INTO BUYERS SELECT * FROM CUSTOMERS ORDER BY ID ASC LIMIT 3; Verification Let us verify the contents of the BUYERS table − SELECT * FROM BUYERS; The resultant table will be as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 Print Page Previous Next Advertisements ”;

SQL – DELETE Query Table of content The SQL DELETE Statement Deleting Multiple Rows Deleting All The Records From a Table Delete Records in Multiple Tables ”; Previous Next 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 − DELETE FROM 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 − 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following query deletes the record of a customer, whose ID is 6. DELETE FROM 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.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. DELETE FROM 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 − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.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 − DELETE FROM 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. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows INSERT INTO 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 − OID DATE CUSTOMER_ID AMOUNT 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 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 INNER JOIN 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. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 MP 4500.00 7 Muffy 24 Indore 10000.00 Similarly, if you verify the ORDERS table as shown below − SELECT * FROM ORDERS; Since salary is greater

SQL – UPDATE Query Table of content The SQL UPDATE Statement Update Multiple ROWS and COLUMNS ”; Previous Next 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 − 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following 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 − ID NAME AGE ADDRESS SALARY 6 Komal 22 Pune 4500.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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 37 Ahmedabad 5000.00 2 Khilan 30 Delhi 4500.00 3 Kaushik 28 Kota 5000.00 4 Chaitali 30 Mumbai 9500.00 5 Hardik 32 Bhopal 11500.00 6 Komal 27 Pune 7500.00 7 Muffy 29 Indore 13000.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.00 WHERE 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 37 Pune 1000.00 Print Page Previous Next Advertisements ”;

SQL – UPDATE View Table of content SQL UPDATE View Statement Updating Multiple Rows and Columns ”; Previous Next 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 − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Following query 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) − ID NAME AGE ADDRESS SALARY 1 Ramesh 35 Ahmedabad 2000.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 − ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad 4500.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 = 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 − ID NAME AGE ADDRESS SALARY 3 Kaushik Ramanujan 24 Kota 2000.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 − ID NAME AGE 1 Ramesh 41 2 Khilan 31 3 Kaushik Ramanujan 30 4 Chaitali 31 5 Hardik 33 6 Komal 28 7 Muffy 30 Print Page Previous Next Advertisements ”;