Since we cannot use INTERSECT query in MySQL, we will use IN operator to simulate the INTERSECT query. It can be understood with the help of the following example −ExampleIn this example, we are two tables namely Student_detail and Student_info having the following data −mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name    | Address    | Subject    | +-----------+---------+------------+------------+ | 101       | YashPal | Amritsar   | History    | | 105       | Gaurav  | Chandigarh | Literature | | 130       | Ram     | Jhansi ... Read More

To understand this concept, we are using the data from table ‘Details_city’ as follows −mysql> Select * from details_city; +--------+--------+ | City1  | City2  | +--------+--------+ | Delhi  | Nagpur | | Delhi  | Mumbai | | Nagpur | Delhi  | | Katak  | Delhi  | | Delhi  | Katak  | +--------+--------+ 5 rows in set (0.00 sec)Now, the following query will delete the reverse duplicate values from details_city table −mysql> Select a.city1,a.city2 from details_city a WHERE a.city1

To understand this concept, we are using the data from table ‘emp_tbl’ as follows −mysql> Select * from emp_tbl; +--------+------------+ | Name   | DOB        | +--------+------------+ | Gaurav | 1984-01-17 | | Gaurav | 1990-01-17 | | Rahul  | 1980-05-22 | | Gurdas | 1981-05-25 | | Naveen | 1991-04-25 | | Sohan  | 1987-12-26 | +--------+------------+ 6 rows in set (0.00 sec) mysql> SELECT Name, SYSDATE(), DOB, DATEDIFF(SYSDATE(), DOB)/365 AS AGE from emp_tbl WHERE(DATEDIFF(SYSDATE(), DOB)/365)>30; +--------+---------------------+------------+---------+ | Name   | SYSDATE()           | DOB        | AGE   ... Read More

To understand this concept, we are using the data from table ‘Salary’ as follows −mysql> Select * from Salary; +--------+--------+ | Name   | Salary | +--------+--------+ | Gaurav |  50000 | | Rahul  |  40000 | | Ram    |  45000 | | Raman  |  45000 | +--------+--------+ 4 rows in set (0.00 sec) mysql> Select * from salary12345 order by salary DESC limit 1 offset 1; +-------+--------+ | name  | Salary | +-------+--------+ | Raman |  45000 | +-------+--------+ 1 row in set (0.00 sec)

To understand this concept, we are using the data from table ‘Information’ as follows −mysql> Select * from Information; +----+---------+ | id | Name    | +----+---------+ | 1  | Gaurav  | | 2  | Ram     | | 3  | Rahul   | | 4  | Aarav   | | 5  | Aryan   | | 6  | Krishan | +----+---------+ 6 rows in set (0.00 sec)Now, the query below will fetch the alternate odd-numbered records from the above table ‘Information’ −mysql> Select id,Name from information group by id having mod(id,2) = 1; +----+--------+ | id | Name   | +----+--------+ | 1  | Gaurav | | 3  | Rahul  | | 5  | Aryan  | +----+--------+ 3 rows in set (0.09 sec)

As we know that an event is automatically dropped when it is expired and we would not be able to see it from SHOW EVENTS statement. To change such kind of behavior we can use ON COMPLETION PRESERVE while creating the event. It can be understood from the following example −Examplemysql> Create table event_messages(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, MESSAGE VARCHAR(255) NOT NULL, Generated_at DATETIME NOT NULL); Query OK, 0 rows affected (0.61 sec)The below query will create an event without the use of ON COMPLETION PRESERVE hence it would not be seen in the output of SHOW EVENTS ... Read More

As we know that recurring event means that it will be executed after regular time of interval and expires at the specified time. To illustrate the creation of such kind of events we are using the following example in which we are creating an event which will execute after every minute and expires after one hour −mysql> CREATE EVENT testing_event10 ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO event_message(message, generated_at) Values('Recrring evnts', NOW()); Query OK, 0 rows affected (0.00 sec) mysql> Select * from event_message; +----+----------------+---------------------+ | ID | MESSAGE   ... Read More

As we know a one-time event means the events that will be executed only once on a particular schedule. To illustrate the creation of such kind of events we are using the following example in which we are creating an event which will execute after some specified time interval −Examplemysql> CREATE EVENT testing_event5 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COMPLETION PRESERVE DO INSERT INTO event_message(message, generated_at) Values('Hi', NOW()); Query OK, 0 rows affected (0.06 sec) mysql> Select * from event_message; +----+---------+---------------------+ | ID | MESSAGE | Generated_at        | +----+---------+---------------------+ | 1  | Hello ... Read More

To understand this concept we are using the data from table ‘Information’ as follows −mysql> Select * from Information; +----+---------+ | id | Name    | +----+---------+ |  1 | Gaurav  | |  2 | Ram     | |  3 | Rahul   | |  4 | Aarav   | |  5 | Aryan   | |  6 | Krishan | +----+---------+ 6 rows in set (0.00 sec)Now, the query below will fetch the alternate even-numbered records from the above table ‘Information’ −mysql> Select id,Name from information group by id having mod(id,2) = 0; +----+---------+ | id | Name    | +----+---------+ |  2 | Ram     | |  4 | Aarav   | |  6 | Krishan | +----+---------+ 3 rows in set (0.00 sec)

For combining the values of two or more columns of MySQL table, we can use CONCAT() string function. Basically, MySQL CONCAT() function is used to combine two or more strings.SyntaxCONCAT(String1, String2, …, StringN)Here, the arguments of CONCAT functions are the strings that need to be combined.Examplemysql> select CONCAT('Ram', 'is', 'a', 'good', 'boy') AS Remarks; +---------------+ | Remarks       | +---------------+ | Ramisagoodboy | +---------------+ 1 row in set (0.00 sec)Similarly, we can use CONCAT() function to combine the values of two or more columns. For example, suppose we have a table named ‘Student’ and we want the name ... Read More