MySQL Articles - Page 367 of 439

If a subquery is nested inside another subquery then it is called a nested subquery. To make it understand we are creating the nested subquery from the following tables data −mysql> Select * from Cars; +------+--------------+---------+ | ID   | Name         | Price   | +------+--------------+---------+ |    1 | Nexa         | 750000  | |    2 | Maruti Swift | 450000  | |    3 | BMW          | 4450000 | |    4 | VOLVO        | 2250000 | |    5 | Alto   ... Read More

If a subquery, used with EXIST operator, returns no rows, the expression EXIST returns FALSE and MySQL returns the empty set as output. It can be understood with the help of simple example using the following data from table ‘Customers’ −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ |           1 | Rahul    | |           2 | Yashpal  | |           3 | Gaurav   | |           4 | Virender | +-------------+----------+ 4 rows in ... Read More

If a subquery, used with EXIST operator, returns NULL, the expression EXIST NULL returns TRUE and MySQL returns the result based on an outer query. It can be understood with the help of simple example using the following data from table ‘Customers’ −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | Rahul    | | 2           | Yashpal  | | 3           | Gaurav   | | 4           | Virender | +-------------+----------+ 4 rows in ... Read More

We can use MySQL EXIST operator to test for the existence of a record in the subquery. In other words, we can say that EXIST operator checks if a subquery returns any rows. The syntax of using EXIST operator with MySQL subquery is as follows −SyntaxWHERE EXISTS (Subquery)The above EXIST (subquery) expression returns TRUE if the subquery returns at least one row, otherwise it returns false.ExampleTo make it understand we are using the data from the following tables −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | ... Read More

We can write multiple-line statements because MySQL determines the end of a statement by looking for the termination semicolon, not by looking for the end of the input line.Examplemysql> Select *     -> from     -> stock_item; +------------+-------+----------+ | item_name  | Value | Quantity | +------------+-------+----------+ | Calculator | 15    | 89      | | Notebooks  | 63    | 40      | | Pencil     | 15    | 40      | | Pens       | 65    | 32      | | Shirts     | 13   ... Read More

For creating a table with such kinds of names we must have to use quote character. The quotes can be single or double depends upon ANSI_QUOTES SQL mode. If this mode is disabled then the identifier quote character is the backtick (“`”). Consider the following example in which we created a table named ‘select’ −mysql> Create table `a^b`(`a^b` int); Query OK, 0 rows affected (0.19 sec) mysql> Create table "a^g"("a^g" int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"a^g" ("a^g" ... Read More

As we know that a query consists of MySQL statements followed by a semicolon. We can enter multiple MySQL statements, separated by semicolons, on a single line. Consider the following example −mysql> Select * from Student; Select * from Student ORDER BY Name; +--------+--------+--------+ | Name   | RollNo | Grade  | +--------+--------+--------+ | Gaurav | 100    | B.tech | | Aarav  | 150    | M.SC   | | Aryan  | 165    | M.tech | +--------+--------+--------+ 3 rows in set (0.00 sec) +--------+--------+--------+ | Name   | RollNo | Grade  | +--------+--------+--------+ | Aarav  | 150 ... Read More

For writing MySQL query to get all the tables having a particular column name, we can use LIKE operator. It can be understood with the help of an example as follows −ExampleFollowing is the MySQL query to get all the tables having columns name ‘ID’ in it −mysql> Select Column_name as 'ColumnName', Table_name As 'Tablename' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%ID%' ORDER BY Tablename LIMIT 10; +-------------+---------------+ | ColumnName  | Tablename     | +-------------+---------------+ | id          | arena         | | id          | arena1        | ... Read More

It can be done with the help of ALTER TABLE command of MySQL. Consider the table ‘Student’ in which the size of ‘Grade’ column is declared as Varchar(10), can be seen from the following query −mysql> DESCRIBE Student; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | Name   | varchar(20) | YES  |     | NULL    |       | | RollNo | int(11)     | YES  |     | NULL    |       | | Grade  | varchar(10) | YES  | ... Read More

We can sort multiple columns in a single query by giving more than one column name with ORDER BY Clause. The syntax of the above is as follows −SyntaxSelect Col1, Col2, … from table_name ORDER BY Col1, Col2, …ExampleSuppose we want to sort the table named ‘Student’ by columns ‘Name’ and ‘RollNo’ both then we can write the single query for this as follows −mysql> Select Name, RollNo from student order by name, rollno; +--------+--------+ | name   | rollno | +--------+--------+ | Aarav  |    150 | | Aryan  |    165 | | Gaurav |    100 | ... Read More