SELECT
Display employee number(eno), employee name(ename), gender(gen) and 30% of salary(sal) as HRA for all the employees in EMP table
SELECT ENO,ENAME,GEN,SAL*.30 HRA
FROM EMP;
Display employee number(eno), employee name(ename) and city for all employees if name contains 'r'
SELECT ENO,ENAME,CITY
FROM EMP
WHERE ENAME LIKE '%r%' ;
Display employee name(ename) and date of joining(doj) in alphabetic order of their names
SELECT ENAME, DOJ
FROM EMP
ORDER BY ENAME;
Find the average salary(sal) of all employees in emp table
SELECT AVG(SAL)
FROM EMP;
display average salary(sal) citywise
SELECT CITY, AVG(SAL)
FROM EMP
GROUP BY CITY;
Display employee number(eno), employee name(ename), gender(gen) and date of joining(doj) for employees residing(city) in Banagalore
SELECT ENO,ENAME,GEN, DOJ
FROM EMP
WHERE CITY='BANGALORE';
Display employee number(eno), employee name(ename) and city for all employees if city has 2nd last character as 'z'
SELECT ENO,ENAME,CITY
FROM EMP
WHERE CITY LIKE '%z_';
Display ename, did, sal in ascending order of did and within did in the descending order of sal
SELECT ENAME,DID,SAL
FROM EMP
ORDER BY DID, SAL DESC;
Find the total salary(sal) of all employees in emp table
SELECT SUM(SAL)
FFROM EMP;
to find city wise sum of salary(sal) and average salary(sal)
SELECT CITY, SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CITY;
Display employee number(eno), employee name(ename) and salary(sal) for employees having date of joining(doj) after 31st March1998 and city is Mumbai
SELECT ENO,ENAME,SAL
FROM EMP
WHERE DOJ>'1998-03-31' AND CITY='MUMBAI';
display rows 5 to 9 from emp table
SELECT * FROM EMP
LIMIT 4,5;
Display the emp table in alphabetic order of city where sal is below 30000
SELECT * FROM EMP
WHERE SAL < 30000
ORDER BY CITY;
Find the city, eno, and minimum salary(sal) of all employees in emp table
SELECT CITY,ENO,MIN(SAL)
FROM EMP;
display total number of employees for each department(did)
SELECT DID, COUNT(*)
FROM EMP
GROUP BY DID;
display all the data from emp table for employees having age less than 40
SELECT * FROM EMP
WHERE AGE<40;
Display city name without any duplicates
SELECT DISTINCT CITY
FROM EMP;
Display the emp table in ascending order of city(column 4) and descending order of sal(column 6)
SELECT * FROM EMP
ORDER BY 4,6;
Find the Oldest department id(did) in emp table
SELECT MIN(DID)
FROM EMP;
Display eno, ename, maximum salary grouped city wise
SELECT CITY, ENO, ENAME, MAX(SAL)
FROM EMP
GROUP BY CITY;
display all data from emp table for employees having 'A' as 4th character in their names (use inbuilt function)
SELECT * FROM EMP
WHERE MID(ENAME,4,1)='L';
Display all data from emp table for salary(sal) in the range of 200000 and 500000 (both included)
SELECT * FROM EMP
WHERE SAL BETWEEN 200000 AND 500000;
Display all data from emp table for empno greater than 4 arranged in descending order of did
SELECT * FROM EMP
WHERE EMPNO>4
ORDER BY DID DESC;
Find the total number of employees in emp table
SELECT COUNT(*) FROM EMP;
Display highest employee number(eno) from emp table for each year
SELECT YEAR(DOJ), MAX(ENO)
FROM EMP
GROUP BY YEAR(DOJ);