ORACLE/MySQL Query List For University Exam & NET/GATE Exam practice queries

1. Query to display Employee Name, Job, Hire Date, Employee Number; for each employee with the Employee Number appearing first.

2. Query to display unique Jobs from the Employee Table.

3. Query to display the Employee Name concatenated by a Job separated by a comma.

CREATE TABLE

  Create Statements department 

( Dno int NOT NULL,

 Dname varchar2(50) DEFAULT NULL,

 Location varchar2(50) DEFAULT NULL,

 PRIMARY KEY (Dno));

CREATE TABLE EMPLOYEE 

( Eno CHAR(3) NOT NULL,

 Ename varchar2(50) NOT NULL,

 JOB_TYPE varchar2(50) NOT NULL,

 MANAGER CHAR(3) DEFAULT NULL, 

HIRE_DATE DATE NOT NULL,

 DNO INT DEFAULT NULL, 

 COMMISION decimal(10,2) DEFAULT NULL,

 SALARY decimal(10,2) NOT NULL,

 PRIMARY KEY(ENO),

 CONSTRAINT FK_DNO FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO),

 CONSTRAINT FK_MANAGER FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(ENO)); 

Insert Statements

 INSERT INTO department VALUES (101,'Accounting','New York'); 

INSERT INTO department VALUES (201,'Research','Paris'); 

INSERT INTO department VALUES (301,'Sales','Tokyo'); 

INSERT INTO department VALUES (401,'Operation','Beijing');

 INSERT INTO department VALUES (501,'Marketing','New Delhi');

INSERT INTO employee VALUES ('E33', 'Amit', 'Salesman', '', '02-FEB-1990', 301, 3000.00, 32000.00);

INSERT INTO employee VALUES ('E58', 'Arjun', 'Salesman', '', '21-MAR-1991', 301, 5000.00, 30000.00); 

INSERT INTO employee VALUES ('E56', 'Devendra', 'Manager', '', '02-JAN-1990', 201, '', 230000.00);

INSERT INTO employee VALUES ('E65', 'Umesh', 'Salesman', '', '04-JUN-1980', 301, 14000.00, 52500.00);

INSERT INTO employee VALUES ('E69', 'Rupesh', 'Manager', '', '05-NOV-1981', 301, '', 287000.00); 

INSERT INTO employee VALUES ('E76', 'Manish', 'Manager', '', '09-MAY-1981', 101, '', 290000.00);

INSERT INTO employee VALUES ('E83', 'Kunal', 'President', NULL, '11-OCT-1985', 101, '', 395000.00);

INSERT INTO employee VALUES ('E84', 'Tushar', 'Salesman', '', '09-JAN-2014', 301, '', 14500.00);

INSERT INTO employee VALUES ('E87', 'Arpit',' Clerk', '', '01-JAN-2015', 201, '', 11500.00);

INSERT INTO employee VALUES ('E88', 'Sameer', 'Analyst', '', '12-JULY-2005', 201, '', 28500.00);

INSERT INTO employee VALUES ('E90', 'Jatin', 'Clerk', '', '12-DEC-2010', 301, '', 19550.00); 

INSERT INTO employee VALUES ('E92', 'Farhan', 'Analyst', '', '12-APR-1981', 201, '', 260000.00);

INSERT INTO employee VALUES ('E93', 'Milind', 'Clerk', '', '25-JAN-1982', 401, '', 43000.00);

INSERT INTO employee VALUES ('E78', 'Karanveer', 'Salesman', '', '25-JAN-1999', 301, '', 15000.00); 

UPDATE employee SET Manager= 'E69' where eno = 'E33'; 

UPDATE employee SET Manager= 'E69' where eno = 'E58';

 UPDATE employee SET Manager= 'E83' where eno = 'E56'; 

UPDATE employee SET Manager= 'E84' where eno = 'E65'; 

UPDATE employee SET Manager= 'E83' where eno = 'E69'; 

UPDATE employee SET Manager= 'E83' where eno = 'E76'; 

UPDATE employee SET Manager= 'E69' where eno = 'E84'; 

UPDATE employee SET Manager= 'E76' where eno = 'E87'; 

UPDATE employee SET Manager= 'E56' where eno = 'E88'; 

UPDATE employee SET Manager= 'E69' where eno = 'E90'; 

UPDATE employee SET Manager= 'E56' where eno = 'E92'; 

UPDATE employee SET Manager= 'E88' where eno = 'E93'; 

UPDATE employee SET Manager= 'E83’ where eno = ‘E78’;

4. Query to display all the data from the Employee Table. Separate each Column by a comma and name the said column as THE_OUTPUT.

5. Query to display the Employee Name and Salary of all the employees earning more than ₹28500. 

6. Query to display Employee Name and Department Number for the Employee No = E90.

7. Query to display Employee Name and Salary for all employees whose salary is not in the range of ₹15000 and ₹28500.

8. Query to display Employee Name and Department No of all the employees in Dept 101 and Dept 301 in the alphabetical order by name. 

9. Query to display Name and Hire Date of every Employee who was hired in 1981. 

10. Query to display Name and Job of all employees who don’t have a current Manager. 

11. Query to display the Name, Salary and Commission for all the employees who earn commission. Sort the data in descending order of Salary and Commission. 

12. Query to display Name of all the employees where the Second letter of their name is ‘a’. 

13. Query to display Name of all employees either have two ‘R’s or have two ‘A’s in their name and are either in Dept No = 301 or their Manger’s Employee No = E78.

14. Query to display Name, Salary and Commission for all employees whose Commission Amount is greater than their Salary increased by 5%. 

15. Query to display the Current Date.

16. Query to display Name, Hire Date and Salary Review Date which is the 1st Monday after six months of employment.

17. Query to display Name and calculate the number of months between today and the date each employee was hired.

18. Query to display the following for each employee:- earns monthly but wants < 3 * Current Salary >. Label the Column as Dream Salary.

19. Query to display Name with the 1st letter capitalized and all other letter lower case and length of their name of all the employees whose name starts with ‘J’, ’A’ and ‘M’.

20. Query to display Name, Hire Date and Day of the week on which the employee started.

21. Query to display Name, Department Name and Department No for all the employees.

22. Query to display Unique Listing of all Jobs that are in Department # 301. 

23. Query to display Name, Dept Name of all employees who have an ‘A’ in their name. 

24. Query to display Name, Job, Department No. And Department Name for all the employees working at the New Delhi location.

25. Query to display name and Employee no. along with their Manager’s Name and the Manager’s employee no.; Along with the Employee’s name who do not have a Manager. 

26. Query to display Name, Dept No. and Salary of any employee whose department No. and Salary match both the department no. and the salary of any employee who earns a commission. 

27. Query to display Name and Salaries represented by asterisks, where each asterisk (*) signifies ₹10,000. 

28. Query to display the Highest, Lowest, Sum and Average Salaries of all the employees.
 
29. Query to display the number of employees performing the same Job type functions. 

30. Query to display the no. of managers without listing their names.

31. Query to display the Department Name, Location Name, No. of Employees and the average salary for all employees in that department.

32. Query to display Name and Hire Date for all employees in the same dept. As Rupesh.

33. Query to display the Employee No. And Name for all employees who earn more than the average salary.

34. Query to display Employee Number and Name for all employees who work in a department with any employee whose name contains a ‘T’. 

35. Query to display the names and salaries of all employees who report to Kunal. 
36. Query to display the department no, name and job for all employees in the Sales department.

37. Create a query to display the Employee name and salary for all employees. Format the salary to be 15 characters long, left-padded with ₹. Label the column SALARY. 

38. Display the Employee name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday. 

39. List the employees in the ascending order of Designations of those, joined after the second half of 1981.

40. List the employees who are either ‘CLERK’ or ‘ANALYST’ in the Desc order. 
41. List the employees who joined on 25-JAN-81, 09-MAY-81, 05-NOV-81, 04- JUN-80 in asc order of seniority. 

42. List the employees who are joined in the month of Jan 1982.

43. List the Enames those are starting with ‘A’ and with five characters.

44. List the emps those are having six chars and fourth character must be ‘e’.

45. List the six character names starting with ‘R’ and ending with ‘h’.

46. List all the employees who joined before or after 1981. 

47. List the employees who are working under ‘Manager’. 

48. List the emps who joined in any year but not belongs to the month of January. 
49. List the emps of Department no 301 or 101 joined in the year 1981.

50. List the Eno, Ename, Salary, Dname of all the ‘Manager’ and ‘ANALYST’ working in Paris, Tokyo with an experience more than 7 years without receiving the Commission ascending order of Location.

51. Display the Employee no, Ename, Salary, Dname, Location, Department no, Job of all employees working at Tokyo or working for ACCOUNTING department with Annual Salary>28000, but the Salary should not be = 30000 or 28000 who doesn’t belongs to the Manager and whose eno is having a digit ‘7’ or ‘8’ in 3rd position in the ascending order of Dno and desc order of job.

52. List the employees who are senior to Karanveer working at Paris & Beijing. 

53. List the employees whose Jobs are same as Manish or Salary is more than Amit.

54. List the Employees whose Salary is greater than the total remuneration of the SALESMAN.

55. List the employees whose jobs same as Sameer or Arjun.