Top 50 Fresher MYSQL QUERY

Ouery:-add New Colonm into exiting Table.

ALTER TABLE 
emp ADD COLUMN 
date varchar( 100 ) NULL AFTER salary;

Ouery:-Drop a column from exiting Table.

ALTER TABLE emp
DROP COLUMN date;

Ouery:-Renaming table Name of exiting Table Name.

ALTER TABLE emp
RENAME TO employee

Query:- Get all employee details from the employee table

SELECT * FROM emp;

Query:- Get all Distinct(remove duplicate) Record from the employee table

SELECT DISTINCT salary
FROM emp;

Query:- Get all Distinctrow(remove duplicate) Record from the employee table

SELECT DISTINCTROW salary
FROM emp;

Query:-Get employee details from employee table whose Salary between 15000 and 25000.

SELECT *
FROM emp
WHERE salary
BETWEEN 15000
AND 25000

Query:-Get employee details from employee table whose Salary less than 25000

SELECT *
FROM emp
WHERE salary<25000;

Query:- Get First_Name from employee table in upper case.Using upper() function.

SELECT upper( firstName )
FROM emp;

Query:- Get First_Name from employee table in lower case.Using lower() function.

SELECT lower( firstName )
FROM emp;

Query:- Get First_Name from employee table using alias name “Employee Name”

SELECT firstName Employee
FROM emp

Here:-Employee is an alias name.
Query:- Get current Date & Time of Mysql.Using now() funtion.

 SELECT now( );

Query:- Select first 3 characters of FIRST_NAME from EMPLOYEE.

 SELECT substring( firstName, 1, 3 )
FROM emp

Query:- Get length of firstname from employee table.

select length(firstName) from emp

Query:-Get firstname from employee table after removing white spaces from right side.

SELECT RTRIM( firstName )
FROM emp

Query:-Get first Name from employee table after removing white spaces from left side.

select LTRIM(firstName) from emp

Query:-Get First_Name from employee table after replacing 'o' with '$'.

SELECT REPLACE( firstName, 'o', '$' )
FROM emp

Query:- Get all Record from the employee table between 0 to 5

 SELECT empid, lastName, firstName
FROM emp
LIMIT 0 , 5 

Query:- Get 4 Records from the employee table.

SELECT *
FROM emp
LIMIT 4  

Query:- Get 6 Records from the employee table start from record no. 4 .

SELECT *
FROM emp
LIMIT 4 ,6 

Query:-Get all employee details from the employee table order by First Name Ascending order

SELECT *
FROM emp
ORDER BY firstName ASC;

Query:-Get all employee details from the employee table order by First name descending

SELECT *
FROM emp
ORDER BY firstName DESC;

Query:-Get all employee details from the employee table where salary> 1000 and empid in descending order

SELECT *
FROM emp 
WHERE salary >1000 
ORDER BY empid DESC ;

Query:-Get Total Salary of all employee from emp table as Group By Salary using(sum() function)

SELECT salary, SUM( salary ) AS "Total Salary"
FROM emp
GROUP BY salary;

Query:-Get Total Salary of all employee from emp table where salary>1000 as Group By manager

SELECT manager, sum( salary ) AS "Total Salary"
FROM emp
WHERE salary >1000
GROUP BY manager

Query:-Get Total Number Of Employee from emp table where salary>1000 as Group By manager

SELECT manager, COUNT( * ) AS "Number of Employee"
FROM emp
WHERE salary >=1000
GROUP BY manager

Query:-Get Total Number Of Employee from emp table where salary>1000 as Group By manager using(count Function)

SELECT manager, COUNT( * ) AS "Number of Employee"
FROM emp
WHERE salary >=1000
GROUP BY manager

Query:-Get Lowest Salary from emp table as Group By manager using(min ()Function)

SELECT empid, MIN( salary ) AS "Lowest salary"
FROM emp
GROUP BY manager;

Query:-Get Highest Salary from emp table using(max()Function)

SELECT empid, MAX( salary ) AS "Highest salary"
FROM emp

Query:-Get Highest Salary from emp table as Group By manager using(max()Function)

SELECT manager, MAX( salary ) AS "Highest salary"
FROM emp
GROUP BY manager

Query:-Get managerwise average salary from employee table order by salary ascending

SELECT manager, avg( SALARY ) AvgSalary
FROM emp
GROUP BY manager
ORDER BY AvgSalary ASC

Query:-Get Managerwise maximum salary from employee table order by salary ascending

SELECT manager, max( SALARY ) MaxSalary
FROM emp
GROUP BY manager
ORDER BY MaxSalary ASC

Query:-Get all record of employee manager where manager id =1001 if that employee having manager.

SELECT empid
FROM emp
GROUP BY manager =1001
HAVING empid

Query:-Count all records of employee from emp table where salary>=15000 if that employee having empid.

SELECT empid, COUNT( empid ) AS "Number of Employee"
FROM emp
WHERE salary >=20000
GROUP BY empid
HAVING COUNT( empid ) ;

Query:-Get Minimum Salary from emp table group by Manager And if that employee having empid.

SELECT empid, min( salary ) AS "Lowest Salary"
FROM emp
GROUP BY manager
HAVING empid;

Query:-Get Hieght Salary from emp table group by Manager And if that employee having empid.

SELECT empid, max( salary ) AS "Height Salary"
FROM emp
GROUP BY manager
HAVING empid;

Query:- Get employee details from employee table whose first name starts with 'J'

SELECT *
FROM emp
WHERE firstName LIKE 'J%'

Query:-Get employee details from employee table whose first name contains 'o'

SELECT *
FROM emp
WHERE firstName LIKE '%o%'

Query:-Get employee details from employee table whose first name ends with 'e'

SELECT *
FROM emp
WHERE firstName LIKE '%e'

Query:-Get all employees details from employee table whose salary ends with ''_00%'.

SELECT *
FROM emp
WHERE salary LIKE '_00%'

Query:-Get all employees details Finds any values that start with 2 and are at least 3 characters in length

SELECT *
FROM emp
WHERE salary LIKE '2_%_%'

To calculate the number of days between required date and shipped date for orders in the orders table.

SELECT orderNumber,
       DATEDIFF(requiredDate,shippedDate) daysLeft
FROM orders
ORDER BY daysLeft DESC;

Using STR_TO_DATE() function to convert strings into a date and/or time values

SELECT STR_TO_DATE( '21,5,2013', '%d,%m,%Y' ) ;

The STR_TO_DATE() function ignores extra characters at the end of the input string when it parses the input string based on the format string. See the following example:

SELECT STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y');

Admin has written 171 articles