Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 263 of 547
How can we use two columns with MySQL WHERE clause?
It is very rarely used to use two columns of the same table in WHERE clause but still we can perform a query with two columns of the same table. Consider the below example −mysql> Select F_name, L_name -> From Customer -> where F_name = L_name; Empty set (0.00 sec)Here we are using both the columns(F_Name and L_Name) from the same table(Customer) hence the result is an Empty set.
Read MoreHow can we convert subqueries to LEFT JOIN?
To make it understand we are using the data from the following tables −mysql> Select * from customers; +-------------+----------+ | Customer_Id | Name | +-------------+----------+ | 1 | Rahul | | 2 | Yashpal | | 3 | Gaurav | | 4 | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from reserve; +------+------------+ | ID | Day | +------+------------+ | 1 | 2017-12-30 | | ...
Read MoreHow can we run a MySQL statement without termination semicolon?
With the help of \G or \g option just at the end of the MySQL statement, we can run it without the semicolon. Consider the example below −mysql> Select * from Stock_item\G *************************** 1. row *************************** item_name: Calculator Value: 15 Quantity: 89 *************************** 2. row *************************** item_name: Notebooks Value: 63 Quantity: 40 *************************** 3. row *************************** item_name: Pencil Value: 15 Quantity: 40 *************************** 4. row *************************** item_name: Pens Value : 65 Quantity: 32 *************************** 5. row *************************** item_name: Shirts Value: 13 Quantity: 29 *************************** 6. row *************************** item_name: Shoes ...
Read MoreHow can we convert subqueries to RIGHT JOIN?
To make it understand we are using the data from the following tables −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name | +-------------+----------+ | 1 | Rahul | | 2 | Yashpal | | 3 | Gaurav | | 4 | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from Reserve; +------+------------+ | ID | Day | +------+------------+ | 1 | 2017-12-30 | | ...
Read MoreWhat are the restrictions, in terms of a number of rows and columns, with MySQL query having no table list?
The restriction on MySQL query having a notable list is that it can return, as a result, exactly one row but that result can contain multiple columns.Examplemysql> Select 65/NULL,65+NULL,65*NULL,65-NULL,65%NULL; +------------+--------------+-------------+-------------+---------+ | 65/NULL | 65+NULL | 65*NULL | 65-NULL | 65%NULL | +------------+--------------+-------------+-------------+---------+ | NULL | NULL | NULL | NULL | NULL | +------------+--------------+-------------+-------------+---------+ 1 row in set (0.00 sec)In the above example, we can see that MySQL returns only one row with five columns, having the result of five expressions, as a result when we do not have any table list in the statement.
Read MoreIn a Multiple-line query, what is the significance of the change of MySQL prompt after the first line?
After writing the first line of multiple-line queries, MySQL changes promptly from ‘mysql>’ to ‘→’. It is significant because with the help of it we got an indication that MySQL has not seen a complete statement yet and is waiting for the rest. Consider the example below,mysql> Select * -> from -> stock_item;We know that after writing the first line i.e. ‘Select *’ Mysql changes its prompts which means that yet the state has not been completed. After the semicolon, MySQL considers the statement completed and throws the output.
Read MoreIn MySQL, how we can write Multiple-line statement?
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 MoreHow can create a table having the name like a^b along with same column name?nname?
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 ...
Read MoreHow can we test for the existence of any record in MySQL subquery?
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 MoreHow MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
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 ...
Read More