Skip to content

Most important MySql queries for Practice

  • by
mysql query

Create Sample Database To Practice MySQL query.

1- Create Database : quickmysupport
2- Create Table Employee
3- Create table Incentive
4- Create table Role

CREATE DATABASE AND TABLES

CREATE TABLE IF NOT EXISTS `employee` (
`Emp_ID` int(11) NOT NULL AUTO_INCREMENT,
`FIRST_NAME` char(25) DEFAULT NULL,
`LAST_NAME` char(25) DEFAULT NULL,
`SALARY` int(15) DEFAULT NULL,
`JOINING_DATE` datetime DEFAULT NULL,
`DEPARTMENT` char(25) DEFAULT NULL,
PRIMARY KEY (`Emp_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
INSERT INTO `employee` (`Emp_ID`, `FIRST_NAME`, `LAST_NAME`, `SALARY`, `JOINING_DATE`, `DEPARTMENT`) VALUES
(1, ‘Mahesh’, ‘Josi’, 100000, ‘2014-02-20 09:00:00’, ‘HR’),
(2, ‘Nishant’, ‘Sharma’, 80000, ‘2014-06-11 09:00:00’, ‘Admin’),
(3, ‘Vimal’, ‘Singh’, 300000, ‘2014-02-20 09:00:00’, ‘HR’),
(4, ‘Amit’, ‘Singh’, 500000, ‘2014-02-20 09:00:00’, ‘Admin’),
(5, ‘Vikas’, ‘Gupta’, 500000, ‘2014-06-11 09:00:00’, ‘Admin’),
(6, ‘Jitendra’, ‘Duha’, 200000, ‘2014-06-11 09:00:00’, ‘Account’),
(7, ‘Sumit’, ‘Kumar’, 75000, ‘2014-01-20 09:00:00’, ‘Account’),
(8, ‘Preeti’, ‘Singh’, 90000, ‘2014-04-11 09:00:00’, ‘Admin’);
CREATE TABLE IF NOT EXISTS `incentive` (
`Emp_REF_ID` int(11) DEFAULT NULL,
`Incentive_AMOUNT` int(10) DEFAULT NULL,
`Incentive_DATE` datetime DEFAULT NULL,
KEY `Emp_REF_ID` (`Emp_REF_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `incentive` (`Emp_REF_ID`, `Incentive_AMOUNT`, `Incentive_DATE`) VALUES
(1, 5000, ‘2016-02-20 00:00:00’),
(2, 3000, ‘2016-06-11 00:00:00’),
(3, 4000, ‘2016-02-20 00:00:00’),
(1, 4500, ‘2016-02-20 00:00:00’),
(2, 3500, ‘2016-06-11 00:00:00’);
CREATE TABLE IF NOT EXISTS `role` (
`Emp_REF_ID` int(11) DEFAULT NULL,
`Emp_TITLE` char(25) DEFAULT NULL,
`AFFECTED_FROM` datetime DEFAULT NULL,
KEY `Emp_REF_ID` (`Emp_REF_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `role` (`Emp_REF_ID`, `Emp_TITLE`, `AFFECTED_FROM`) VALUES
(1, ‘Manager’, ‘2016-02-20 00:00:00’),
(2, ‘Executive’, ‘2016-06-11 00:00:00’),
(8, ‘Executive’, ‘2016-06-11 00:00:00’),
(5, ‘Manager’, ‘2016-06-11 00:00:00’),
(4, ‘Asst. Manager’, ‘2016-06-11 00:00:00’),
(7, ‘Executive’, ‘2016-06-11 00:00:00’),
(6, ‘Lead’, ‘2016-06-11 00:00:00’),
(3, ‘Lead’, ‘2016-06-11 00:00:00’);

Table Employee

Most important MySql queries for Practice

Table Incentive

Most important MySql queries for Practice

Table Role

Most important MySql queries for Practice

MYSQL Queries

Question 1 -Write an MySQL query to print “FIRST_NAME” from employee table using the alias name as Employee_Name.

SELECT FIRST_NAME AS Employee_Name FROM employee

Result

Employee_Name
Mahesh
Nishant
Vimal
Amit
Vikas
Jitendra
Sumit
Preeti

Question 2 –

Write an MySQL query to Print “DEPARTMENT” from employee table in upper case.

SELECT UPPER( DEPARTMENT ) FROM employee

Result

UPPER( DEPARTMENT )
HR
ADMIN
HR
ADMIN
ADMIN
ACCOUNT
ACCOUNT
ADMIN

Question 3 –

Write an MySQL query to Print unique values of DEPARTMENT from employee table.

SELECT DISTINCT Department FROM `employee`

Result :

Department
HR
ADMIN
ACCOUNT

Question 3 –

Write an MySQL query to print first four characters of LAST_NAME from employee table.

SELECT SUBSTR( LAST_NAME, 1, 4 ) FROM `employee`

Result :

<table>
<tr>
<th>SUBSTR( LAST_NAME, 1, 4 )</th>
</tr>
<tr>
<td>Josi</td>
</tr>
<tr>
<td>Shar</td>
</tr>
<tr>
<td>Sing</td>
</tr>
<tr>
<td>Sing</td>
</tr>
<tr>
<td>Gupt</td>
</tr>
<tr>
<td>Duha</td>
</tr>
<tr>
<td>Kuma</td>
</tr>
<tr>
<td>Sing</td>
</tr>
</table>

Question 5 –

Write an MySQL query to find the position of the alphabet (‘d’) in the FIRST_NAME column ‘Jitendra’ from employee table.

SELECT INSTR( FIRST_NAME, BINARY ‘d’ ) FROM employee WHERE FIRST_NAME = ‘Jitendra’

Result

INSTR( FIRST_NAME, BINARY ‘d’ )
6

Question 6 –

Write an MySQL query to print the FIRST_NAME from employee table after replacing ‘i’ with ‘I’.

SELECT REPLACE( FIRST_NAME, ‘i’, ‘I’ ) FROM employee

Result

REPLACE( FIRST_NAME, ‘i’, ‘I’ )
Mahesh
NIshant
VImal
AmIt
VIkas
JItendra
SumIt
PreetI

Question 7 –

Write an MySQL query to print the FIRST_NAME from employee table after removing white spaces from the right side.

SELECT RTRIM( FIRST_NAME ) FROM employee

Result

RTRIM( FIRST_NAME )
Mahesh
Nishant
Vimal
Amit
Vikas
Jitendra
Sumit
Preeti

Question 8 –

Write an MySQL query to print the FIRST_NAME from employee table after removing white spaces from the left side.

SELECT LTRIM( FIRST_NAME ) FROM employee

Result

LTRIM( FIRST_NAME )
Mahesh
Nishant
Vimal
Amit
Vikas
Jitendra
Sumit
Preeti

Question 9 –

Write an MySQL query to print the FIRST_NAME and LAST_NAME from employee table into a single column FULL_NAME.

SELECT CONCAT( FIRST_NAME, ‘ ‘, LAST_NAME ) AS ‘FULL_NAME’ FROM employee

Result

FULL_NAME
Mahesh Josi
Nishant Sharma
Vimal Singh
Amit Singh
Vikas Gupta
Jitendra Duha
Sumit Kumar
Preeti Singh

Question 10 –

Write an MySQL query to print all employee details from the employee table order by FIRST_NAME Ascending order.

SELECT * FROM employee ORDER BY FIRST_NAME ASC

Result

Emp_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
4 Amit Singh 500000 2014-02-20 09:00:00 Admin
6 Jitendra Duha 200000 2014-06-11 09:00:00 Account
1 Mahesh Josi 100000 2014-02-20 09:00:00 HR
2 Nishant Sharma 80000 2014-06-11 09:00:00 Admin
8 Preeti Singh 90000 2014-04-11 09:00:00 Admin
7 Sumit Kumar 75000 2014-01-20 09:00:00 Account
5 Vikas Gupta 500000 2014-06-11 09:00:00 Admin
3 Vimal Singh 300000 2014-02-20 09:00:00 HR

Question 11 –

Write an MySQL query to print employee name who earn the highest salary.

SELECT FIRST_NAME, SALARY FROM employee WHERE SALARY = (SELECT max( SALARY ) FROM employee )

Result

FIRST_NAMESALARY
Amit500000
Vikas500000

Question 12 –

Second maximum salary using sub query and IN clause.

SELECT MAX( salary ) FROM employee WHERE salary NOT IN ( SELECT Max( salary ) FROM employee )

Result

FIRST_NAMEMAX( salary )
Amit300000

Question 13 –

Print Second maximum salary Using sub query and < operator instead of IN clause.

SELECT MAX( salary ) FROM employee WHERE salary < ( SELECT Max( salary ) FROM employee )

Result

FIRST_NAMEMAX( salary )
Amit300000

Question 14 –

Write an SQL query to print details for employee with the last name as “Singh” AND “Kumar” from employee table.

SELECT * FROM employee WHERE LAST_NAME IN (‘singh’, ‘kumar’)

Result

Emp_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
3Vimal Singh 300000 2014-02-20 09:00:00 HR
4 Amit Singh 500000 2014-02-20 09:00:00 Admin
7 Sumit Kumar 75000 2014-01-20 09:00:00 Account
8 Preeti Singh 90000 2014-04-11 09:00:00 Admin

Question 15 –

Write an MySQL query to print details of the employee whose FIRST_NAME contains ‘m’.

SELECT * FROM employee WHERE FIRST_NAME LIKE ‘%m%’

Result

Emp_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
1 Mahesh Josi 100000 2014-02-20 09:00:00 HR
3Vimal Singh 300000 2014-02-20 09:00:00 HR
4 Amit Singh 500000 2014-02-20 09:00:00 Admin
7 Sumit Kumar 75000 2014-01-20 09:00:00 Account

Question 16 –

Write an MySQL query to print details of the employee whose FIRST_NAME ends with ‘t’.

SELECT * FROM employee WHERE FIRST_NAME LIKE ‘%t’

Result

Emp_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
2Nishant Sharma 80000 2014-06-11 09:00:00 Admin
4 Amit Singh 500000 2014-02-20 09:00:00 Admin
7 Sumit Kumar 75000 2014-01-20 09:00:00 Account

Question 17 –

Write an MySQL query to print details of the employee whose FIRST_NAME ends with ‘t’ and contains seven alphabets.

SELECT * FROM employee WHERE FIRST_NAME LIKE ‘______t’

Result

Emp_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
2Nishant Sharma 80000 2014-06-11 09:00:00 Admin

Question 18 –

Write an query to print details of the employee whose SALARY between 300000 and 500000.

SELECT * FROM employee WHERE SALARY BETWEEN 300000 AND 500000

Result

Emp_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
3VimalSingh 3000002014-02-20 09:00:00 HR
4AmitSingh 5000002014-02-20 09:00:00 Admin
5VikasGupta 5000002014-06-11 09:00:00 Admin

Question 19 –

Write an SQL query to print details of the employee who have joined in Feb’2015.

SELECT * FROM employee WHERE year( JOINING_DATE ) =2014 AND month( JOINING_DATE ) =2

Result

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

Emp_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
1MaheshJosi 1000002014-02-20 09:00:00 HR
3VimalSingh 3000002014-02-20 09:00:00 HR
4AmitSingh 5000002014-02-20 09:00:00 Admin