Category: mysql

MySQL – NOT LIKE Operator Table of content MySQL NOT LIKE Operator Using NOT LIKE Operator with Wildcards Using NOT LIKE Operator with AND/OR Operators NOT LIKE Operator on Strings NOT LIKE Operator Using a Client Program ”; Previous Next MySQL NOT LIKE Operator We have previously learned that the LIKE Operator in MySQL database is a logical operator used to perform pattern matching operation on a database table. And NOT LIKE Operator is defined as opposite of this LIKE operator. Both LIKE and NOT LIKE operators perform pattern matching in a database table. Thus, they both need wildcards and patterns to function. However, if the LIKE operator is used to find the similar patterns mentioned using the wildcards, NOT LIKE operator is used to find all the records that do not contain the specified pattern. The NOT LIKE operator is nothing but the amalgamation of two SQL operators, NOT and LIKE operators. Thus, having the combination of their functionalities. It is used to match a particular pattern in the given string and returns 0 in case of a match and returns 1 otherwise. If either of the two operands of this function is NULL, it returns NULL as result. This operator is useful for finding strings that do not match a specific pattern or do not have certain characteristics. Syntax Following is the basic syntax of MySQL NOT LIKE operator with a SELECT statement − SELECT column_name(s) FROM table_name WHERE column_name NOT LIKE [condition]; Using NOT LIKE Operator with Wildcards Wildcards are special characters used in SQL queries to match patterns in the data. Following wildcards can be used in conjunction with the NOT LIKE operator − S.No WildCard & Definition 1 % The percent sign represents zero, one or multiple characters. 2 _ The underscore represents a single number or character. 3 [] This matches any single character within the given range in the []. 4 [^] This matches any single character excluding the given range in the [^]. Note: In the NOT LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ”%” and ”_”. Example Let us begin by creating a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Using the below INSERT statements, we are inserting records into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the below query to display all the records present in the CUSTOMERS table − Select * from CUSTOMERS; Following is the CUSTOMERS table − 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 use the MySQL NOTLIKE operator to displays the all the records in CUSTOMERS table whose name doesn”t starts with ”k”. SELECT * FROM CUSTOMERS where NAME NOT LIKE ”k%”; Following are the records whose name doesn”t starts with ”k” − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 The following query displays the records of customers whose NAME doesn”t end with ”ik”. SELECT * FROM CUSTOMERS where NAME NOT LIKE ”%ik”; Following are the records whose name doesn”t ends with ”ik” − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Here, we are displaying all the records whose name does not contains the substring ”al”. SELECT * FROM CUSTOMERS where NAME NOT LIKE ”%al%”; Following are the records whose name doesn”t contains the substring ”al” − 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 7 Muffy 24 Indore 10000.00 The

MySQL – Intersect Operator Table of content MySQL INTERSECT Operator INTERSECT with BETWEEN Operator INTERSECT with IN Operator INTERSECT with LIKE Operator Intersect Operator Using Client Program ”; Previous Next In mathematical set theory, the intersection of two sets is a set that contains only those elements that are common to both sets. In other words, the intersection of two sets is a set of elements that exist in both sets. If we perform the intersection operation on both sets using the INTERSECT operator, it displays the common rows from both tables. This operator removes the duplicate rows from the final result set. MySQL INTERSECT Operator In MySQL, the INTERSECT operator is used to return the records that are identical/common between the result sets of two SELECT (tables) statements. However, the INTERSECT operator works only if both the SELECT statements have an equal number of columns with same data types and names. Syntax Following is the syntax of INTERSECT operator in MySQL − SELECT column1, column2,…, columnN FROM table1, table2,…, tableN INTERSECT SELECT column1, column2,…, columnN FROM table1, table2,…, tableN Example First of all, let us create a table named STUDENTS using the following query − CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, HOBBY VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY(ID) ); Here, we are inserting some values into the table using the INSERT statement. INSERT INTO STUDENTS VALUES (1, ”Vijay”, ”Cricket”, 18), (2, ”Varun”, ”Football”, 26), (3, ”Surya”, ”Cricket”, 19), (4, ”Karthik”, ”Cricket”, 25), (5, ”Sunny”, ”Football”, 26), (6, ”Dev”, ”Cricket”, 23); The table is created as follows − ID NAME HOBBY AGE 1 Vijay Cricket 18 2 Varun Football 26 3 Surya Cricket 19 4 Karthik Cricket 25 5 Sunny Football 26 6 Dev Cricket 23 Now, let us create another table with name ASSOCIATES using the following query − CREATE TABLE ASSOCIATES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, SUBJECT VARCHAR(20) NOT NULL, AGE INT NOT NULL, HOBBY VARCHAR(20) NOT NULL, PRIMARY KEY(ID) ); Here, we are inserting some values into the table using the INSERT statement − INSERT INTO ASSOCIATES VALUES (1, ”Naina”, ”Maths”, 24, ”Cricket”), (2, ”Varun”, ”Physics”, 26, ”Football”), (3, ”Dev”, ”Maths”, 23, ”Cricket”), (4, ”Priya”, ”Physics”, 25, ”Cricket”), (5, ”Aditya”, ”Chemistry”, 21, ”Cricket”), (6, ”Kalyan”, ”Maths”, 30, ”Football”); The table is created as follows − ID NAME SUBJECT AGE HOBBY 1 Naina Maths 24 Cricket 2 Varun Physics 26 Football 3 Dev Maths 23 Cricket 4 Priya Physics 25 Cricket 5 Aditya Chemistry 21 Cricket 6 Kalyan Maths 30 Football Now, we return the common records from both the tables using the following query − SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES; Output The output is obtained as follows − NAME AGE HOBBY Varun 26 Football Dev 23 Cricket INTERSECT with BETWEEN Operator The MySQL INTERSECT operator can be used with the BETWEEN operator to find the rows that exist within the specified range. Example In the following query, we are retrieving the records that are common in both tables. In addition; we are retrieving the records who are aged between 25 and 30 − SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE AGE BETWEEN 25 AND 30 INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES WHERE AGE BETWEEN 20 AND 30; Output On executing the given program, the output is displayed as follows − NAME AGE HOBBY Varun 26 Football INTERSECT with IN Operator In MySQL, we can use the INTERSECT operator with IN operator to find the common rows that have the specified values. The IN operator is used to filter a result set based on a list of specified values. Example In the following query, we are trying to return the common records from both tables. In addition; we are using th IN operator to retrieve the records whose hobby is “Cricket”. SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE HOBBY IN(”Cricket”) INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES WHERE HOBBY IN(”Cricket”); Output The output for the program above is produced as given below − NAME AGE HOBBY Dev 23 Cricket INTERSECT with LIKE Operator The LIKE operator is used to perform pattern matching on a string value. We can use the LIKE operator with the INTERSECT operator in MySQL to find the common rows that match the specified pattern. Example In the following query, we are using the wildcard ”%” with the LIKE operator to fetch the names with ”v” from the common names of both tables. SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE NAME LIKE ”v%” INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES WHERE NAME LIKE ”v%”; Output Let us compile and run the program, to produce the following result − NAME AGE

MySQL – Create Index Table of content Creating Indexes on New Table Creating Indexes on Existing Table Simple and Unique Index Composite Indexes Creating an Index Using Client Program ”; Previous Next A database index improves the speed of operations in a database table. They can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records. Practically, indexes are a special type of lookup tables, that hold a pointer to each record into the actual table. We can create indexes on a MySQL table in two scenarios: while creating a new table and on an existing table. Creating Indexes on New Table If we want to define an index on a new table, we use the CREATE TABLE statement. Syntax Following is the syntax to create an index on a new table − CREATE TABLE( column1 datatype PRIMARY KEY, column2 datatype, column3 datatype, … INDEX(column_name) ); Example In this example, we are create a new table CUSTOMERS and adding an index to one of its columns using the following CREATE TABLE query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), INDEX(ID) ); To verify whether the index has been defined or not, we check the table definition using the following DESC statement. DESC CUSTOMERS; Output The table structure displayed will contain a MUL index on the ID column as shown − Field Type Null Key Default Extra ID int NO MUL NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Creating Indexes on Existing Table To create an index on existing table, we use the following SQL statements − With CREATE INDEX Statement With ALTER Command CREATE INDEX Statement The basic syntax of the CREATE INDEX statement is as follows − CREATE INDEX index_name ON table_name; In the following example, let us create an index on CUSTOMERS table. We are using CREATE INDEX statement here − CREATE INDEX NAME_INDEX ON CUSTOMERS (Name); To check if the index is created on the table or not, let us display the table structure using DESC statement as shown below − DESC CUSTOMERS; Output As we can see in the table below, a composite index is created on the ”NAME” column of CUSTOMERS table. Field Type Null Key Default Extra ID int NO MUL NULL NAME varchar(20) NO MUL NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL ALTER… ADD Command Following is the basic syntax of ALTER statement − ALTER TABLE tbl_name ADD INDEX index_name (column_list); Let us use ALTER TABLE… ADD INDEX statement in the following example to add an index to the CUSTOMERS table − ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE); Output As we can see in the table below, another composite index is created on the ”AGE” column of CUSTOMERS table. Field Type Null Key Default Extra ID int NO MUL NULL NAME varchar(20) NO MUL NULL AGE int NO MUL NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Simple and Unique Index A unique index is the one which cannot be created on two rows at once. Following is the syntax to create a unique index − CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,…); Example Following example creates a unique index on the table temp − CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name); Composite Indexes We can also create an index on more than one column and it is called a composite index the basic syntax to create a composite index is as follows − CREATE INDEX index_name on table_name (column1, column2); Example Following query creates a composite index on the ID and Name columns of the above created table − CREATE INDEX composite_index on CUSTOMERS (ID, Name); Creating an Index Using Client Program In addition to using SQL queries, we can also create an index on a table in a MySQL database using a client program. Syntax Following are the syntaxes to create an index in a MySQL database using various programming languages − PHP NodeJS Java Python The MySQL PHP connector mysqli provides a function named query() to execute the CREATE INDEX query in the MySQL database. $sql=” CREATE INDEX index_name ON table_name (column_name)”; $mysqli->query($sql); The MySQL NodeJS connector mysql2 provides a function named query() to execute the CREATE INDEX query in the MySQL database. sql = “CREATE INDEX index_name

MySQL – SHOW TRIGGERS Table of content Show Triggers in MySQL With FROM or IN Clause With WHERE clause Showing Trigger Using Client Program ”; Previous Next Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked. MySQL provides a statement to list out all the existing triggers present in a database. Knowing the trigger information can be useful while creating new triggers, so that a user wouldn”t use the same name for multiple triggers. Show Triggers in MySQL The SHOW TRIGGERS Statement is used in MySQL to display information about all the triggers defined in the current database. Syntax Following is the syntax of the MySQL SHOW TRIGGERS Statement − SHOW TRIGGERS [{FROM | IN} db_name] [LIKE ”pattern” | WHERE expr] Example In this example, we are creating a table named STUDENT using the query below − CREATE TABLE STUDENT( Name varchar(35), Age INT, Score INT ); Following query creates a trigger sample_trigger which will set the score value to 0 if you enter a negative score value into the table. DELIMITER // CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END // DELIMITER ; Assume we have created another trigger using the AFTER clause − DELIMITER // CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW BEGIN INSERT INTO Student SET action = ”update”, Name = OLD.Name, Age = OLD.age, Score = OLD.score; END; END // DELIMITER ; Following query shows the existing triggers in the current database − SHOW TRIGGERS G; Output The list of triggers will be displayed as follows − *************************** 1. row *************************** Trigger: sample_trigger Event: INSERT Table: student Statement: BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END Timing: BEFORE Created: 2021-05-12 19:08:04.50 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: INSERT INTO Student SET Name = OLD.Name, Age = OLD.age, Score = OLD.score Timing: AFTER Created: 2021-05-12 19:10:44.49 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.00 sec) With FROM or IN Clause You can retrieve the information of triggers from a specific database using the FROM clause. Example Assume that the current database is named demo. Following query shows the triggers present in the database demo − SHOW TRIGGERS FROM demoG You can also use the IN clause instead of FROM, to get the same output. SHOW TRIGGERS IN demoG Output The existing triggers present in the demo database − *************************** 1. row *************************** Trigger: sample_trigger Event: INSERT Table: student Statement: BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END Timing: BEFORE Created: 2023-09-29 11:42:33.58 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: BEGIN INSERT INTO Student SET action = ”update”, Name = OLD.Name, Age = OLD.age, Score = OLD.score; END Timing: AFTER Created: 2023-09-29 11:43:10.27 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.00 sec) With WHERE Clause You can use the WHERE clause of the SHOW TRIGGERS statements to retrieve info about the triggers which match the specified condition. Example Following query retrieves the triggers in the current database whose event is update − SHOW TRIGGERS FROM demo WHERE Event = ”UPDATE” G; Output The required list of triggers is displayed as follows − *************************** 1. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: BEGIN INSERT INTO Student SET action = ”update”, Name = OLD.Name, Age = OLD.age, Score = OLD.score; END Timing: AFTER Created: 2023-09-29 11:43:10.27 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Showing Trigger Using Client Program We can also Show a trigger using a client program. Syntax PHP NodeJS Java Python To show a trigger through a PHP program, we need to execute the SHOW TRIGGERS statement using the mysqli function query() as follows − $sql = “Show TRIGGER”; $mysqli->query($sql); To show a trigger through a JavaScript program, we need to execute the SHOW TRIGGERS statement using the query() function of mysql2 library as follows − sql = “Show TRIGGER”; con.query(sql); To show a trigger through a Java program, we need to execute the SHOW TRIGGERS statement using the JDBC function executeQuery() as follows − String sql = “Show TRIGGER”; statement.executeQuery(sql); To show a trigger through a python program, we need to execute the SHOW TRIGGERS statement using the execute() function of the MySQL Connector/Python as follows − Show_trigger_query = ”SHOW TRIGGER” cursorObj.execute(Show_trigger_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $db = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } printf(”Connected successfully.”); // Create a trigger $sql = “CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ”update”, Name = OLD.Name, age = OLD.age, score = OLD.score”; if ($mysqli->query($sql)) { printf(“Trigger created successfully…!”); } else { printf(“Trigger creation failed: %s”, $mysqli->error);

MySQL – Insert Into Select Table of content The MySQL Insert Into Select Statement Inserting Required Data from one Table to Another Table Inserting the rows with LIMIT Inserting All Columns from one Table to Another Table INSERT INTO SELECT Using a Client Program ”; Previous Next The MySQL Insert Into Select Statement In MySQL, the INSERT INTO… SELECT statement is used to add/insert one or more rows from an existing table to target table. This statement is a combination of two different statements: INSERT INTO and SELECT. The MySQL INSERT INTO statement is a commonly used command in database management and it requires only the name of the table and the values to be inserted into a table. However, it is important to ensure that the data being inserted matches the structure and data types of the table columns. The SELECT statement is used to fetch data from an existing database table. When the above mentioned statements are used together, the SELECT statement first fetches 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 for using insert into select statement − INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE condition; Following are some important points that we have to consider before we execute the below queries − In the database where we are going to insert data, a table must already exist. Both the source and target tables must match its structure. Example First of all, let us create a table named CUSTOMERS 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) ); The following query inserts 7 records into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to retrieve all the records from CUSTOMERS table − Select * From CUSTOMERS; Following is the CUSTOMERS table − 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 Inserting Required Data from one Table to Another Table We may come across some instances where we only want to add small number of records to another table. This can be achieved by using a WHERE clause to select all the number of rows that the query returned. Example Before that, let us create a another table named CUSTOMERS_copy with similar structure of previously created CUSTOMERS table − CREATE TABLE CUSTOMERS_copy ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); In the following query, we are trying to fetch the records from the CUSTOMERS table and insert them into the CUSTOMERS_copy table. INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY) SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE AGE >= 25; Output The output for the program above is produced as given below − Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 Verification To confirm if the records from the ”CUSTOMERS” table, where the age is 25 or older, have been inserted to the target table ”CUSTOMERS_copy”, execute the following query − SELECT * FROM CUSTOMERS_copy; Following are the records whose age is 25 or older − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 Inserting the rows with LIMIT Using the MySQL LIMIT clause, we can specify the number of rows from the query that should be added to the target table. Example Before proceeding further, let us first truncate all rows in the CUSTOMERS_copy table using the following query − TRUNCATE TABLE CUSTOMERS_copy; Now, we are going to insert the top 3 records from CUSTOMERS table sorted by their AGE using the LIMIT clause − INSERT INTO CUSTOMERS_copy (ID, NAME, AGE, ADDRESS, SALARY) SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS ORDER BY AGE LIMIT 3; Output The output for the program above is produced as given below − Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 Verification Execute the following query to verify whether the records are reflected in the CUSTOMERS_copy table or not − SELECT * FROM CUSTOMERS_copy; Following are the records − ID NAME AGE ADDRESS SALARY

MySQL – Exists Operator Table of content MySQL Exists Operator EXISTS Operator with SELECT statement EXISTS Operator with UPDATE statement EXISTS Operator with DELETE statement NOT Operator with EXISTS Operator Exists Operator Using a Client Program ”; Previous Next MySQL Exists Operator The EXISTS operator in MySQL checks for the existence of a record in a table. It”s used in the WHERE clause of a SELECT statement to verify if a subquery returns any rows. It returns TRUE if the subquery returns at least one record, else false. We can also use the operator with the SQL statements such as SELECT, INSERT, UPDATE, and DELETE to verify the existence of the records in subqueries. Syntax Following is the syntax of the EXISTS operator in MySQL − SELECT column1, column2, … FROM table_name WHERE EXISTS (subquery); Example Before performing the EXISTS operator, let us first two different tables named CUSTOMERS and CARS. Here, we are creating the CUSTOMERS table − CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The following query uses INSERT INTO statement to add 7 records into the above-created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to fetch all the records present in the CUSTOMERS table − SELECT * FROM CUSTOMERS; Following is the CUSTOMERS table − 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 Let us create another table named CARS, which contains the details such as ID of the customer, NAME and PRICE of the car − CREATE TABLE CARS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, PRICE INT NOT NULL, PRIMARY KEY (ID) ); The following query inserts 3 records into the above-created table − INSERT INTO CARS (ID, NAME, PRICE) VALUES (2, ”Maruti Swift”, 450000), (4, ”VOLVO”, 2250000), (7, ”Toyota”, 2400000); Execute the below query to fetch all the records present in the CARS table − SELECT * FROM CARS; Following is the CARS table − ID NAME PRICE 2 Maruti Swift 450000 4 VOLVO 2250000 7 Toyota 2400000 EXISTS operator with SELECT statement The SELECT statement in MySQL is used to retrieve data from one or more tables. The EXISTS operator can be used with the SELECT statement to check if rows exist that match a specific condition. Example Now, let us fetch the list of the customers with the price of the car greater than 2,000,000 − SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000); Output On executing the given query, the output is displayed as follows − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.00 EXISTS Operator with UPDATE statement The MySQL EXISTS operator can be used with the UPDATE statement to update the rows in a table based on the existence of rows matching in another table. Example In this query, we are using the EXISTS operator to UPDATE the name ”Kushal” to all of the customers whose ID is equal to the ID of the CARS table − UPDATE CUSTOMERS SET NAME = ”Kushal” WHERE EXISTS (SELECT NAME FROM CARS WHERE CUSTOMERS.ID = CARS.ID); Output As we can observe the output, 3 rows have been modified − Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 Verification To verify whether the changes are reflected in the CUSTOMERS table, execute the following query − SELECT * FROM CUSTOMERS; The CUSTOMERS table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Kushal 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Kushal 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Kushal 24 Indore 10000.00 EXISTS Operator with DELETE statement The MySQL EXISTS operator is used with the DELETE statement to delete the rows in a table based on the existence of rows returned by a subquery. Example Here, we are deleting all the records from the CUSTOMERS table whose ID is equal to the

MySQL – TINYINT Table of content The MySQL TINYINT Data Type TINYINT Datatype Using a Client Program ”; Previous Next The MySQL TINYINT Data Type The MySQL TINYINT data type is used to store integer values within a very small range. It occupies just 1 byte (8 bits) of storage and can hold values from -128 to 127 for signed TINYINT or 0 to 255 for unsigned TINYINT. When you define a TINYINT column in MySQL, by default it is considered as SIGNED. This means it can hold both positive and negative numbers within a specific range. Additionally, you can use either “TINYINT” or “INT1” to define such a column because they work the same way. Syntax Following is the syntax of the MySQL TINYINT data type − TINYINT(M) [SIGNED | UNSIGNED | ZEROFILL] Example First, let us create a table with the name tinyint_table using the below query − CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL ); Following is the output obtained − Query OK, 0 rows affected, 1 warning (0.03 sec) Now, let us try to insert some values (128, 128, 128) into these columns as shown below − INSERT INTO tinyint_table VALUES (128, 128, 128); An error is generated for the value in col1 because the value we inserted is out of range − ERROR 1264 (22003): Out of range value for column ”col1” at row 1 Next, if we try to insert a negative value into the TINYINT UNSIGNED column (“col2”), it will result in an error because UNSIGNED values cannot be negative − INSERT INTO tinyint_table VALUES (127, -120, 128); The error message displayed is as follows − ERROR 1264 (22003): Out of range value for column ”col2” at row 1 Similarly, if we insert -128 into the TINYINT ZEROFILL column (“col3”), an error will be generated − INSERT INTO tinyint_table VALUES (127, 128, -128); The output is as shown below − ERROR 1264 (22003): Out of range value for column ”col3” at row 1 However, if we insert values within the valid range, the insertion will succeed as shown below − INSERT INTO tinyint_table VALUES (127, 128, 128); Following is the output of the above code − Query OK, 1 row affected (0.01 sec) Finally, we can retrieve all the records present in the table using the following SELECT query − SELECT * FROM tinyint_table; This query will display the following result − col1 col2 col3 127 128 128 TINYINT Datatype Using a Client Program We can also create column of the TINYINT datatype using the client program. Syntax PHP NodeJS Java Python To create a column of TINYINT datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows − $sql = ”CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )”; $mysqli->query($sql); To create a column of TINYINT datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows − sql = “CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )”; con.query(sql); To create a column of TINYINT datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows − String sql = “CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )”; statement.execute(sql); To create a column of TINYINT datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows − sql = ”CREATE TABLE tinyint_table (col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL)” cursorObj.execute(sql) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $dbname = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } //printf(”Connected successfully.”); $sql = ”CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )”; $result = $mysqli->query($sql); if ($result) { printf(“Table created successfully…!n”); } // insert data into created table $q = ” INSERT INTO tinyint_table (col1, col2, col3) VALUES (100, 105, 110)”; if ($res = $mysqli->query($q)) { printf(“Data inserted successfully…!n”); } //now display the table records $s = “SELECT * FROM tinyint_table”; if ($r = $mysqli->query($s)) { printf(“Table Records: n”); while ($row = $r->fetch_assoc()) { printf(” Col_1: %s, Col_2: %s, Col_3: %s”, $row[“col1”], $row[“col2”], $row[“col3”]); printf(“n”); } } else { printf(”Failed”); } $mysqli->close(); Output The output obtained is as follows − Table created successfully…! Data inserted successfully…! Table Records: Col_1: 100, Col_2: 105, Col_3: 110 var mysql = require(“mysql2”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “password”, }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; // console.log(“Connected successfully…!”); // console.log(“————————–“); sql = “USE TUTORIALS”; con.query(sql); //create a tinyint_table table, that accepts one column of tinyint type. sql = “CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )”; con.query(sql); //insert data into created table sql = “INSERT INTO tinyint_table (col1, col2, col3) VALUES (100, 105, 110)”; con.query(sql); //select datatypes of salary sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ”tinyint_table” AND COLUMN_NAME = ”col2”`; con.query(sql, function (err, result) { if

MySQL – Cross Join Table of content MySQL Cross Join Joining Multiple Tables with Cross Join Cross Join Using Client Program ”; Previous Next MySQL Cross Join A MySQL Cross Join combines each row of the first table with each row of second table. It is a basic type of inner join that is used to retrieve the Cartesian product (or cross product) of two individual tables (i.e. permutations). A Cartesian product, or a cross product, is the result achieved from multiplication of two sets. This is done by multiplying all the possible pairs from both the sets. The sample figure below illustrates the cross join in a simple manner. Syntax Following is the basic syntax of the Cross Join query in MySQL − SELECT column_name(s) FROM table1 CROSS JOIN table2 Example In this example of cross join, let us first create a table named CUSTOMERS, which contains personal details of customers including their name, age, address and salary etc. CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 Let us create another table ORDERS, containing the details of orders made and the date they are made on. 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 (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 Now, if we execute the following Cross Join query on these two tables given above, the cross join combines each row in CUSTOMERS table with each row in ORDERS table. SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS; Output The resultant table is as follows − ID NAME AMOUNT DATE 2 Khilan 1500.00 2009-10-08 00:00:00 1 Ramesh 1560 2009-11-20 00:00:00 2 Khilan 1560 2009-11-20 00:00:00 1 Ramesh 1500.00 2009-10-08 00:00:00 Joining Multiple Tables with Cross Join We can also join more than two tables using cross join. In this case, multiple-way permutations are displayed and the resultant table is expected to contain way more records than the individual tables. Syntax Following is the syntax to join multiple tables using cross join in MySQL − SELECT column_name(s) FROM table1 CROSS JOIN table2 CROSS JOIN table3 CROSS JOIN table4 . . . Example Let us now combine three tables CUSTOMERS, ORDERS and ORDER_RANGE, to demonstrate combining multiple tables using cross join. We will create the ORDER_RANGE table using the query below − CREATE TABLE ORDER_RANGE ( SNO INT NOT NULL, ORDER_RANGE VARCHAR (20) NOT NULL, ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO ORDER_RANGE VALUES (1, ”1-100”), (2, ”100-200”), (3, ”200-300”); The ORDER_RANGE table is as follows − SNO ORDER_RANGE 1 1-100 2 100-200 3 200-300 Now we use the following cross join query on the given tables, SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE FROM CUSTOMERS CROSS JOIN ORDERS CROSS JOIN ORDER_RANGE; Output The resultant table is given below − ID NAME AMOUNT DATE ORDER_RANGE 2 Khilan 1560 2009-11-20 00:00:00 1-100 1 Ramesh 1560 2009-11-20 00:00:00 1-100 2 Khilan 1500.00 2009-10-08 00:00:00 1-100 1 Ramesh 1500.00 2009-10-08 00:00:00 1-100 2 Khilan 1560 2009-11-20 00:00:00 100-200 1 Ramesh 1560 2009-11-20 00:00:00 100-200 2 Khilan 1500.00 2009-10-08 00:00:00 100-200 1 Ramesh 1500.00 2009-10-08 00:00:00 100-200 2 Khilan 1560 2009-11-20 00:00:00 200-300 1 Ramesh 1560 2009-11-20 00:00:00 200-300 2 Khilan 1500.00 2009-10-08 00:00:00 200-300 1 Ramesh 1500.00 2009-10-08 00:00:00 200-300 Cross Join Using Client Program We can also perform the Cross join operation on one or more tables using a client program. Syntax PHP NodeJS Java Python To perform cross Join through a PHP program, we need to execute the SQL query with CROSS JOIN clause using the mysqli function query() as follows − $sql = “SELECT

MySQL – IS NOT NULL Operator Table of content MySQL IS NOT NULL Operator IS NOT NULL with COUNT() function IS NOT NULL with UPDATE statement IS NOT NULL with DELETE statement IS NOT NULL Operator Using Client Program ”; Previous Next A NULL value in a MySQL table indicates a missing or unknown value. It appears to be blank and does not contain any data. This is different from zero values. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. To check for NULL values in a table column, we can use two basic operators: IS NULL IS NOT NULL MySQL IS NOT NULL Operator The MySQL IS NOT NULL operator is used to verify whether a particular column has a non-null value or not. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE. By using the IS NOT NULL operator in a conditional clause, we can only fetch the records that contain valid data in a particular column. Syntax Following is the syntax of IS NOT NULL in MySQL − SELECT column_name1, column_name2, … FROM table_name WHERE column_name IS NOT NULL; Example Firstly, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); In the following query, we are using the INSERT statement to insert values to the table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, NULL), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, NULL, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, NULL), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, NULL, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table is created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 2 Khilan 25 Delhi 1500.00 3 Kaushik NULL Kota 2000.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Bhopal 8500.00 6 Komal NULL Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Example In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is not NULL. SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL; Output Following output is produced − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 IS NOT NULL with COUNT() function We can use the IS NOT NULL operator along with the MySQL COUNT() function to count only the non-null values in a specific column(s). Syntax Following is the syntax of the IS NOT NULL with COUNT() function in MySQL − SELECT COUNT(column_name1, column_name2, …) FROM table_name WHERE condition IS NOT NULL; Example The following query returns the count of all rows in the CUSTOMERS table where the ADDRESS column is not NULL. SELECT COUNT(*) FROM CUSTOMERS WHERE ADDRESS IS NOT NULL; Output On executing the above query, it will generate an output as shown below − COUNT(*) 5 IS NOT NULL with UPDATE statement In MySQL, we can update all the non-null rows in a specific column(s) using the UPDATE statement with IS NOT NULL operator. Syntax Following is the syntax of the IS NOT NULL operator with the UPDATE statement in MySQL – UPDATE table_name SET column1 = value1, column2 = value2, … WHERE columnname1, columnname2, … IS NOT NULL; Example In the following query, we will update the SALARY column to a value of 9000 for all records where the SALARY column is not NULL − UPDATE CUSTOMERS SET SALARY = 20000 WHERE SALARY IS NOT NULL; Verification To check whether the table has been updated or not, execute the SELECT query to display the CUSTOMERS table. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 2 Khilan 25 Delhi 20000.00 3 Kaushik NULL Kota 20000.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Bhopal 20000.00 6 Komal NULL Hyderabad 20000.00 7 Muffy 24 Indore 20000.00 IS NOT NULL with DELETE statement In MySQL, we can delete all the non-null rows in a specific column(s) using the DELETE statement with IS NOT NULL operator. Syntax Following is the syntax of the IS NOT NULL operator with the DELETE statement in MySQL – DELETE FROM table_name WHERE columnname1, columnname2, … IS NOT NULL; Example In the following query, we are trying to delete records which are not null in the

MySQL – Boolean Datatype Table of content Boolean in MySQL Replacing BOOLEAN 0,1 with TRUE and FALSE Boolean Datatype Using a Client Program ”; Previous Next A Boolean data type is used to represent truth values of logic and Boolean algebra. It has two possible values: either true or false. For example, if a customer wants to see all the bikes that are black in colour, we can filter them using BOOLEAN operator, as given in the following table − Here, ”IS_BLACK” is the BOOLEAN column that returns either true or false values based on the colours of the bikes. Boolean in MySQL In MySQL, there is no built-in Boolean or Bool data type. Instead MySQL provides us with the TINYINT datatype to store the Boolean values. MySQL considers the value 0 as FALSE and 1 as TRUE. We can also store NULL values using the TINYINT datatype. The Boolean values (such as TRUE and FALSE) are not case-sensitive. Syntax Following is the syntax of the BOOLEAN operator in MySQL − CREATE TABLE table_name ( Column_name BOOLEAN ); Example In MySQL, 0 is defined as FALSE and any non-zero values are defined as TRUE − SELECT TRUE, FALSE; Output As we can see in the output below, TRUE and FALSE are represented as 1 and 0 − TRUE FALSE 1 0 Example In MySQL, the Boolean values (TRUE and FALSE) are case-insensitive − SELECT true, false, TRUE, FALSE, True, False; Output The output produced is as given below − true false TRUE FALSE True False 1 0 1 0 1 0 Example Now, let”s create a table with the name CUSTOMERS using the following query. Here, the AVAILABILITY column specifies whether the customer is available or not. If the bit value is 0 (FALSE), the customer is not available. If it is 1(TRUE), the customer is available − CREATE TABLE CUSTOMERS ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(40), AVAILABILITY BOOLEAN ); Following is the output produced − Query OK, 0 rows affected (0.02 sec) To get the information about the CUSTOMERS table, use the following query − DESCRIBE CUSTOMERS; If we look at the AVAILABILITY column, which has been set to BOOLEAN while creating the table, it now shows type of TINYINT − Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(40) YES NULL AVAILABILITY tinyint(1) YES NULL Now, let us insert some records into the CUSTOMERS table using the following INSERT query − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, FALSE), (2, ”Khilan”, TRUE), (4, ”Kaushik”, NULL); Using the below query, we can display all the values in the table − SELECT * FROM CUSTOMERS; We can see the values in the AVAILABILITY column are set to 0 and 1 respectively. − ID NAME AVAILABILITY 1 Ramesh 0 2 Khilan 1 4 Kaushik NULL Replacing BOOLEAN 0,1 with TRUE and FALSE As we can see in the above CUSTOMERS table, the BOOLEAN data type shows 0 and 1 values instead of TRUE and FALSE. In MySQL, we can convert BOOLEAN data type to TRUE and FALSE values using the CASE statement. The MySQL CASE statement is a conditional statement that goes through conditions and return a values when the first condition is met. Therefore, once a condition is true, it will stop reading the next piece of code and return the result. If no conditions are true, it will return the value in the ELSE clause. If no ELSE clause is present and no conditions are true, it returns NULL. Syntax Following is the syntax of CASE statement in MySQL − CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result END; Example To get a better understanding, let us consider the BIKES table created using the query below − CREATE TABLE BIKES ( S_NO INT AUTO_INCREMENT PRIMARY KEY, COMPANY VARCHAR(40) NOT NULL, PRICE INT NOT NULL, COLOUR VARCHAR(40) NOT NULL, IS_BLACK BOOLEAN ); Example Output of the above code is as follows − Query OK, 0 rows affected (0.03 sec) Now, let us insert values into the BIKES table using the INSERT statement as shown below − INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) VALUES (”Royal Enfield”, 300000, ”Black”, 1); INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) VALUES (”BMW”, 900000, ”Blue”, 0); INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) VALUES (”Jawa”, 150000, ”Black”, 1); INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) VALUES (”Triumph”, 1200000, ”Red”, 0); The BIKES table obtained is as follows − S_NO COMPANY PRICE COLOUR IS_BLACK 1 Royal Enfield 300000 Black 1 2 BMW 900000 Blue 0 3 Jawa 150000 Black 1 4 Triumph 1200000 Red 0 Now, let us display all the records from the BIKES table, where the colour BLACK is represented by