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

Table Incentive

Table Role

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

Question 2 –

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

SELECT UPPER( DEPARTMENT ) FROM employee

Result

Question 3 –

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

SELECT DISTINCT Department FROM `employee`

Result :

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Leave a Reply Cancel reply

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

Name *

Email *

Website

Comment *

Save my name, email, and website in this browser for the next time I comment.

Select language:Hindi English (Press F12 to change language) Show Keyboard