Mysql Nested Case
Mysql Nested Case is a Select Query that is nested inside select, update,
delete and delete SQL query.
Understand with Example
The Tutorial illustrate an example from 'MySQL Nested Case'. To understand
and elaborate example we create a table 'MyTable' that has the required
fieldnames and datatypes respectively.
Query for creating table:
The Query create table is used to create a table 'MyTable' that
has the required fieldnames and datatypes respectively.
mysql> CREATE TABLE MyTable (
-> Empid int(10),
-> Empname varchar(60)
-> Salary int(90)
-> );
Query OK, 0 rows affected (0.13 sec)
|
Query for inserting data in table:
The Query insert into is used to add the records to table 'MyTable'.
mysql> insert into MyTable values(01,'Girish','20000');
Query OK, 1 row affected (0.02 sec)
mysql> insert into MyTable values(02,'A','21000');
Query OK, 1 row affected (0.01 sec)
mysql> insert into MyTable values(03,'C','22000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into MyTable values(04,'V','23000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into MyTable values(05,'B','24000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into MyTable values(06,'E','25000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into MyTable values(07,'Q','26000');
Query OK, 1 row affected (0.01 sec)
mysql> insert into MyTable values(08,'W','27000');
Query OK, 1 row affected (0.01 sec)
mysql> insert into MyTable values(09,'AS','28000');
Query OK, 1 row affected (0.00 sec)`
|
Query to view data inserted in table:
mysql> select * from mytable;
+-------+---------+--------+
| Empid | Empname | Salary |
+-------+---------+--------+
| 1 | Girish | 20000 |
| 2 | A | 21000 |
| 3 | C | 22000 |
| 4 | V | 23000 |
| 5 | B | 24000 |
| 6 | E | 25000 |
| 7 | Q | 26000 |
| 8 | W | 27000 |
| 9 | AS | 28000 |
+-------+---------+--------+
9 rows in set (0.01 sec)
|
Query for creating table name Employee:
We create another table 'Employee' that has the required fieldnames
and datatypes respectively.
mysql> CREATE TABLE Employee(
-> Empid int(10),
-> Empname varchar(60),
-> Salary int(90),
-> DOB date
-> );
Query OK, 0 rows affected (0.01 sec)
|
Query for insertion of data in table:
The insert into is used to add the records or rows to the table
'Employee'.
mysql> INSERT INTO Employee (Empid,Empname,Salary,DOB) VALUES
-> (01,'Habib',2014,20041202),
-> (02,'Karan',4021,20030411),
-> (03,'Samia', 22,20080223),
-> (04,'Hui Ling', 25,20081015),
-> (05,'Yumie', 29,19990126);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
|
Query to view data inserted in table:
mysql> select * from employee;
|
Output:-
+-------+----------+--------+------------+
| Empid | Empname | Salary | DOB |
+-------+----------+--------+------------+
| 1 | Habib | 2014 | 2004-12-02 |
| 2 | Karan | 4021 | 2003-04-11 |
| 3 | Samia | 22 | 2008-02-23 |
| 4 | Hui Ling | 25 | 2008-10-15 |
| 5 | Yumie | 29 | 1999-01-26 |
+-------+----------+--------+------------+
5 rows in set (0.00 sec)
|
Query for Mysql nested select:
The Query below is used to returns only those records from table 'mytable'
which are nested in the select query of table 'employee'.
mysql> SELECT * FROM mytable WHERE Empid in(SELECT Empid FROM employee);
|
Output:-
+-------+---------+--------+
| Empid | Empname | Salary |
+-------+---------+--------+
| 1 | Girish | 20000 |
| 2 | A | 21000 |
| 3 | C | 22000 |
| 4 | V | 23000 |
| 5 | B | 24000 |
+-------+---------+--------+
5 rows in set (0.00 sec)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
