Read my whole experience at TCS here - My life with TCS - a Walkthrough
Warning To who ever is going to copy paste and submit the same copy as below, Answers can vary depending on thoughts. There is more than one way for a solution, please do NOT blindly Copy Paste.
EMP_NO NOT NULL NUMBER(4)
DEPT_NO NOT NULL NUMBER(2)
Create tables EMP, and DEPT, the structure for which are given above.
CREATE TABLE `EMP` (
`EMP_NO` INT( 4 ) NOT NULL ,
`EMP_NAME` VARCHAR( 25 ) NULL ,
`DESIGNATION` CHAR( 4 ) NULL ,
`JOINING_DATE` DATE NULL ,
`SALARY` INT( 7 ) NULL ,
`DEPT_NO` VARCHAR( 25 ) NULL
CREATE TABLE `DEPT` (
`DEPT_NO` INT(2) NOT NULL ,
`DEPT_NAME` VARCHAR(25) NULL,
`BUDGET` INT(15) NULL,
`MANAGER` VARCHAR(25) NULL
Write SQL queries for the following :
1. Display each employee’s name and date of joining.
SELECT EMP_NAME , JOINING_DATE FROM EMP;
2. Display employees earning more than Rs.5,000. Label the column name “Employee”.
SELECT EMP_NAME AS EMPLOYEE FROM EMP WHERE SALARY > 5000;
3. Display all employee names and employee numbers in the alphabetical order of names.
SELECT EMP_NAME , EMP_NO FROM EMP ORDER BY EMP_NAME ASC;
4. Display all employee names containing the letter “S”.
SELECT EMP_NAME FROM EMP WHERE EMP_NAME LIKE '%S%';
5. Display the employees hired in 1981.
SELECT EMP_NAME FROM EMP WHERE JOINING_DATE LIKE '1981';
6. Display the minimum and maximum salary.
SELECT MIN(SALARY) AS MINIMUM , MAX(SALARY) FROM EMP;
7. Display the list of employees along with their department name and its manager.
SELECT EMP_NAME, DEPT_NAME, MANAGER FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO;
8. Display the number of different employees listed for each department.
SELECT DEPT_NAME, COUNT(EMP_NO) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO GROUP BY DEPT_NAME;
9. Delete the records of all the employees hired in 1981 from EMP table.
DELETE FROM EMP WHERE JOINING_DATE LIKE '1981';
10. Update the salary of all the employees to Rs. 10,000.
UPDATE EMP SET SALARY=10000;