Show
Very Important and basics SQL Queries for Testing: If you are looking for some solutions for the queries asked during the SQL round for developer and testing position, then you can check below a huge collection of SQL queries for the solution of the SQL DBMS queries related questions. SQL Interview QuestionsDisplay the details of all employees. Display the depart information from the department table. Display the name and job for all the employees. Display the name and salary for all the employees. Display the employee no and the total salary for all the employees. Display the employee name and annual salary for all employees. Display the names of all the employees who are working in depart number 10. Display the names of all the employees who are working as clerks and drawing a salary of more than 3000. Display the employee number and name who are earning comm. Display the employee number and name who do not earn any comm. Display the names of employees who are working as clerks, salesman, or analysts and drawing a salary of more than 3000. Display the names of the employees who are working in the company for the past 5 years. Display the list of employees who have joined the company before30-JUN-90 or after 31-DEC-90. Display current Date. Display the list of all users in your database (use catalog table). Display the names of all tables from the current user. Display the name of the current user. SQL Query Interview QuestionsDisplay the names of employees working in depart number 10 or 20 or 40 or employees working as CLERKS, SALESMAN, or ANALYST. Display the names of employees whose name starts with alphabet S. Display the Employee names for employees whose name ends with Alphabet S. Display the names of employees whose names have second alphabet A in their names. Select the names of the employee whose names are exactly five characters in length. Display the names of the employee who are not working as MANAGERS. Display the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST. Display all rows from the EMP table. The system should wait after every screen full of information. Display the total number of employees working for the company. Display the total salary begging paid to all employees. Display the maximum salary from emp table. Display the minimum salary from emp table. Display the average salary from emp table. Display the maximum salary being paid to the CLERK. Display the maximum salary being paid to depart number 20. Display the minimum salary being paid to any SALESMAN. Display the average salary drawn by MANAGERS. Display the total salary drawn by ANALYST working in depart number40. Display the names of the employee in order of salary, i.e., the name of the employee earning the lowest salary should salary appear first. Display the names of the employee in descending order of salary. Display the names of the employee in order of employee name. Display empno, ename, deptno, sal sort the output first base on the name and within name by deptno and within deptno by sal. Display the name of the employee along with their annual salary (sal*12).T the name of the employee earning the highest annual salary, should appear first. Display name, salary, hra, pf, da, total salary for each employee. The output should be in the order of total salary; hra 15% of salary, da 10%of salary, pf 5%salary, and total salary will be (salary+hra+da-pf). Display depart numbers and the total number of employees working in each department. Display the various jobs and the total number of employees within each job group. Display the depart numbers and total salary for each department. Display the depart numbers and max salary for each department. Display the various jobs and total salary for each job. Display the various jobs and total salary for each job. Display the depart numbers with more than three employees in each dept. Display the various jobs along with the total salary for each of the jobs where the total salary is greater than 40000. Display the various jobs along with the total number of employees in each job. The output should contain only those jobs with more than three employees. Display the name of the employee who earns the highest salary. Display the employee number and name for an employee working as a clerk and earning the highest salary among clerks. SQL Queries Interview QuestionsDisplay the names of a salesman who earns the salary more than the highest salary of any clerk. Display the names of clerks who earn a salary more than the lowest salary of any salesman. Display the names of employees who earn a salary more than that of Jones or that of salary greater than that of Scott. Display the names of the employees who earn the highest salary in their respective departments. Display the names of the employees who earn the highest salaries in their respective job groups. Display the employee names who are working in the accounting department. Display the employee names who are working in Chicago. Display the Job groups having a total salary greater than the maximum salary for managers. Display the names of employees from department number 10 with a salary greater than that of any employee working in other departments. Display the names of the employees from department number 10 with a salary greater than that of all employees working in other departments. Display the names of the employees in Uppercase. Display the names of the employees in Lowercase. Display the names of the employees in Proper case. Display the length of your name using an appropriate function. Display the length of all the employee names. Select the name of the employee to concatenate with the employee number. User appropriate function and extract 3characters starting from 2characters from the following string ‘Oracle.’ I.e., the output should be ‘ac.’ find the First occurrence of the character ‘a’ from the following string, i.e., ‘Computer Maintenance Corporation.’ Replace every occurrence of alphabet A with B in the string Allens (use translate function) Display the information from the emp table. Where the job manager is found, it should be displayed as boos (Use replace function). Display empno, ename, deptno from emp table. Instead of a display, department numbers display the related department name (Use decode function). Display your age in days. Display your age in months. Display the current date as 15th August Friday Nineteen Ninety Seven. Scott has joined the company on Wednesday, 13th August, nineteen ninety. Find the date for the nearest Saturday after the current date. Display the current time. Display the date three months before the current date. Display the common jobs from department number 10 and 20. Display the jobs found in department 10 and 20 Eliminate duplicate jobs. Display the jobs which are unique to department 10. Display the details of those who do not have any person working under them. Display the details of those employees who are in the sales department and grade is 3. Display those who are not managers and who are managers, anyone. i) Display the managers’ names. Ans: select distinct (m.ename) from emp e, emp mwhere m.empno=e.mgr; ii) Display the who are not managers.Ans: select ename from emp where ename not in (select distinct (m.ename) from emp e, emp m where m.empno=e.mgr); Display those employees whose name contains not less than 4characters. Display that department whose name starts with “S” while the location name ends with “K”. Display those employees whose manager’s name is JONES. Display those employees whose salary is more than 3000 after giving20% increment. Display all employees while their dept names; Display ename who are working in the sales dept. Display employee name, deptname, salary, and comm. For those Sal in between2000 to 5000 while the location in Chicago. Display those employees whose salary greater than his manager’s salary. Display those employees who are working in the same Dept where his manager is working. Display those employees who are not working under any manger. Display grade and employee name for the dept no 10 or 30, but the grade is not 4, while joined the company before 31-Dec-82. Update the salary of each employee by 10% increments that are not eligible for commission. delete those employees who joined the company before 31-Dec-82 while there dept location is ‘NEW YORK’ or ‘CHICAGO.’ Display employee name, job, deptname, location for all who are working as managers. Display those employees whose manager names are Jones, and also display their manager name. Display name and salary of ford if his Sal is equal to high Sal of his grade. Display employee name, his job, his dept name, his manager name, his grade and make out of an under department wise.break on deptno; List out all the employee’s name, job, and salary grade and department name for everyone in the company except ‘CLERK’. Sort on salary displays the highest salary. Display employee name, his job, and his manager. Display also employees’ who are without the manager. SQL Interview Questions And AnswersFind out the top 5 earners of the company. Display the name of those employees who are getting the highest salary. Display those employees whose salary is equal to an average of maximum and minimum. Display count of employees in each department where count greater than 3. Display dname where at least 3 are working and display the only dname. Display the name of those managers name whose salary is more than the average salary of a company. Display those manager’s names whose salary is more than the average salary of his employees. Display employee name, Sal, commission, and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company? Display those employees whose salary is less than his manager but more than the salary of any other managers. Display all employee’s names with total Sal of a company with each employee name. Find out the last 5(least) earner of the company? Find out the number of employees whose salary is greater than there manager salary? Display that manager who is not working under the president, but they are working under any other manager? Delete those departments where is no employee working? Delete those records from the EMP table whose deptno not available in the dept table? Display those earners whose salary is out of the grade available in the Sal grade table? Display employee name, Sal, commission. and whose net pay is greater than any other in the company? Display name of those employees who are going to retire 31-Dec-99. If the maximum job is period is 18 years? Display those employees whose salary is ODD value? Display those employees whose salary contains at least 4 digits? Display those employees who joined the company in the month of DEC? Display those employees whose name contains “A”? Display those employees whose deptno is available in salary? Display those employees whose first 2 characters from hire date-last 2characters of salary? Display those employees whose 10% of salary is equal to the year of joining? Display those employees who are working in sales or research? Display the grade of Jones? Display those employees who joined the company before the 15Th of the month? Delete those records where no of employee in a particular department is lessthan 3? Delete those employees who joined the company 21 years back from today? Ans: select * from emp where (to_char (sysdate, ‘yyyy’)-to_char (hiredate,’yyyy’))>21; Display the department name the no of characters of which is equal to no of employees in any other department? Display those employees who are working as a manager? Count the no of employees who are working as a manager (use set operation)? Display the name of then dept those employees who joined the company on the same date? Display the manager who is having a maximum number of employees working under him? List out employee’s name, and salary increased by 15% and expressed as the whole number of dollars? Produce the output of the EMP table “EMPLOYEE_AND_JOB” forename and job? List all employees with hire date in the format ‘June 4, 1988’? Print a list of employees displaying ‘Less Salary’ if less than 1500 if exactly1500 display as ‘Exact Salary’ and if greater than 1500 display ‘More Salary’? Write a query to calculate the length of the employee who has been with the company? Display those managers who are getting less than their employees Sal. Print the details of all the employees who are subordinate to Blake. Display those who work as a manager using a co-related subquery. Display those employees whose manager’s name is Jones and also with his manager’s name. Use the variable in a statement which finds all employees who can earn 30,000a year or more. Find out how many managers are there without listing them. Find out the avg sal and avg total remuneration for each job type remember salesmen earn the commission. Check whether all employees number are indeed unique. List out the lowest paid employees working for each manager, exclude anygroups where min sal is less than 1000 sort the output by sal. list ename, job, annual salary, deptno, dname, and grade who earn 30000 per year and who are not clerks. find out the all employees who joined the company before their manager. list out all employees by name and number along with their manager’s name and number also display ‘No Manager’ who has no manager. find out the employees who earned the highest Sal in each job typed sort in descending Sal order. find out the employees who earned the min Sal for their job in ascending order. find out the most recently hired employees in each dept order by hire date. display ename, sal, and deptno for each employee who earns a Sal greater than the avg of their department order by deptno. SQL Interview Questions For Experienceddisplay the department where there are no employees. Display the dept no with the highest annual remuneration bill as compensation. In which year did most people join the company. Display the year and number of employees. display avg sal figure for the dept. Write a query of display against the row of the most recently hired employee. Display ename hire date and column max date showing. display employees who can earn more than the lowest Sal in dept no 30. find employees who can earn more than every employee in dept no 30. find all dept’s which have more than 3 employees. Display half of the enames in the upper case and the remaining lower case. Select ename if ename exists more than once. display all enames in reverse order. Display that employee whose joining of month and grade is equal. Display that employee whose joining date is available in dept no. Display those employee’s names as follows A ALLEN, B BLAKE. List out the employees ename, sal, PF from emp. Create table emp with only one column empno. Add this column to emp table ename Varchar (20). OOPS! I forgot to give the primary key constraint. Add it now. now increase the length of the ename column to 30 characters. Add salary column to emp table. I want to give a validation saying that sal cannot be greater 10,000(note provide a name for this column). For the time being, I have decided that I will not impose this validation. My boss has agreed to pay more than 10,000. My boss has changed his mind. Now he doesn’t want to pay more than10, 000. So revoke that salary constraint Add a column called mgr to your emp table. Oh! This column should be related to the empno. Give a command to add this constraint. Add dept no column to your emp table. This dept no column should be related to a deptno column of the dept table. Create a table called newemp. Using a single command creates this table as well as to get data into this table (use create table as). Create a table called newemp. This table should contain only empno, ename, dname. Delete the rows of employees who are working in the company for more than 2years. Provide a commission to employees who are not earning any commission. If any employee has commissioned his commission should be incremented by10% of his salary. Display employee name and department name for each employee. Display employee number, name, and location of the department in which he is working. Display ename, dname even if there no employees working in a particular department (use outer join). Display employee name and his manager’s name. Display the department name along with the total salary in each department. Display the department name and the total number of employees in each department. Display the current date and time. Search Also: sql queries examples,sql queries interview questions,basic sql queries,sql queries tutorial,sql queries for practice,types of database Reference: ArticleSQL Reader Interactions |