Database Sql Queries
Check out these great tips on database sql queries examples, database sql queries pdf, database sql queries interview questions, database sql queries, All command queries in sql queries!
It features all Computer Programming Languages about database sql query examples, database sql queries pdf, database sql queries interview questions, database sql queries, All command queries in sql queries questions and answers.
It features all Computer Programming Languages about database sql query examples, database sql queries pdf, database sql queries interview questions, database sql queries, All command queries in sql queries questions and answers.
(A) "Simple" SELECT Command Questions
1. Display all information in the tables EMP and DEPT. SELECT * FROM emp; SELECT * FROM dept; 2. Display only the hire date and employee name for each employee. SELECT hiredate, ename FROM emp; 3. Display the hire date, name and department number for all clerks. SELECT hiredate, ename, deptno FROM emp WHERE job = ’CLERK’; 4. Display the names and salaries of all employees with a salary greater than 2000. SELECT ename, sal FROM emp WHERE sal > 2000; 5. Display the names of all employees with an ‘A’ in their name. SELECT ename FROM emp WHERE ename LIKE ’%A%’; 6. Display the names of all employees with exactly 5 letters in their name. SELECT ename FROM emp WHERE ename LIKE ’ ’; 7. Display the names and hire dates of all employees hired in 1981 or 1982 SELECT ename, hiredate FROM emp WHERE hiredate LIKE ’%1981’ OR hiredate LIKE ’%1982’; —OR— SELECT ename, hiredate FROM emp WHERE hiredate >= ’1/1/1981’ AND hiredate <= ’31/12/1982’; 8. Display the names and dates of employees with the column headers "Name" and "Start Date" SELECT ename AS "Name", hiredate AS "Start Date" FROM emp; 9. Display the names and hire dates of all employees in the order they were hired. SELECT ename, hiredate FROM emp ORDER BY hiredate; 10. Display the names and salaries of all employees in reverse salary order. SELECT ename, sal FROM emp ORDER BY sal DESC; 11. Display ‘ename of department deptno earned commission rsquo; for each salesman in reverse salary order. SELECT ename || ’ of department ’ || deptno || ’ earned commission rsquo; || comm FROM emp WHERE job = ’SALESMAN’ ORDER BY sal DESC; 12. Display the department numbers of all departments employing a clerk. SELECT DISTINCT deptno FROM emp WHERE emp.job = ’CLERK’; |
|
|
(B) Group SELECT Command Questions
1. Display the maximum, minimum and average salary and commission earned. SELECT max(sal), min(sal), avg(sal), max(comm), min(comm), avg(comm) FROM emp; 2. Display the department number, total salary payout and total commission payout for each department. SELECT deptno, sum(sal), sum(comm) FROM emp GROUP BY deptno; 3. Display the department number, total salary payout and total commission payout for each department that pays at least one employee commission. SELECT deptno, sum(sal), sum(comm) FROM emp GROUP BY deptno HAVING sum(comm) > 0; 4. Display the department number and number of clerks in each department. SELECT deptno, count(job) FROM emp WHERE job = ’CLERK’ GROUP BY deptno; 5. Display the department number and total salary of employees in each department that employs four or more people. SELECT deptno, sum(sal) FROM emp GROUP BY deptno HAVING count(empno) >= 4; 6. Display the employee number of each employee who manages other employees with the number of people he or she manages. SELECT mgr, count(mgr) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr; (C) Join SELECT Command Questions 1. Display the name of each employee with his department name. SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; 2. Display a list of all departments with the employees in each department. SELECT dname, ename FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno; 3. Display all the departments with the manager for that department. SELECT dname, ename FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = ’MANAGER’; 4. Display the names of each employee with the name of his/her boss. SELECT s.ename, b.ename FROM emp s INNER JOIN emp b ON s.mgr = b.empno; 5. Display the names of each employee with the name of his/her boss with a blank for the boss of the president. SELECT s.ename, b.ename FROM emp s LEFT OUTER JOIN emp b ON s.mgr = b.empno; 6. Display the employee number and name of each employee who manages other employees with the number of people he or she manages. SELECT a.mgr, b.ename, count(a.mgr) FROM emp a INNER JOIN emp b ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename; 7. Repeat the display for the last question, but this time display the rows in descending order of the number of employees managed. SELECT a.mgr, b.ename, count(a.mgr) FROM emp a INNER JOIN emp b ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename ORDER BY count(a.mgr) DESC; |
(D) SELECT with Subqueries Questions
1. Display the names and job titles of all employees with the same job as Jones. SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = ’JONES’); 2. Display the names and department name of all employees working in the same city as Jones. SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno WHERE loc = (SELECT loc FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno WHERE ename = ’JONES’); 3. Display the name of the employee whose salary is the lowest. SELECT ename FROM emp WHERE sal = (SELECT min(sal) FROM emp); 4. Display the names of all employees except the lowest paid. SELECT ename FROM emp WHERE sal > (SELECT min(sal) FROM emp); 5. Display the names of all employees whose job title is the same as anyone in the sales dept. SELECT ename FROM emp WHERE job IN (SELECT DISTINCT job FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno WHERE dname = ’SALES’); 6. Display the names of all employees who work in a department that employs an analyst. SELECT ename FROM emp WHERE deptno IN (SELECT DISTINCT emp.deptno FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = ’ANALYST’); 7. Display the names of all employees with their job title, their current salary and their salary following : a 10% pay rise for clerks and a 7% pay rise for all other employees. SELECT ename, job, sal, 1.1 * sal AS "newsal" FROM emp WHERE job = ’CLERK’ UNION SELECT ename, job, sal, 1.07 * sal AS "newsal" FROM emp WHERE job <> ’CLERK’; 8. Display the names of all employees with their salary and commission earned. Employees with a null commission field should have 0 in the commission column. SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL UNION SELECT ename, sal, 0 FROM emp WHERE comm IS NULL; 9. Display the names of ALL employees with the total they have earned (ie. salary plus commission). SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL UNION SELECT ename, sal FROM emp WHERE comm IS NULL; 10. Repeat the display for the last question but this time display in descending order of earnings. SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL UNION SELECT ename, sal FROM emp WHERE comm IS NULL ORDER BY earnings DESC; (E) Creating and Modifying Tables 1. Add a new Department to the DEPT table, and add a Manager and two Clerks to the EMP table that will belong to the new department. INSERT INTO dept VALUES (50, ’NEWDEPT’, ’LONDON’); INSERT INTO emp VALUES (8001, ’FRED’, ’MANAGER’, 7839, ’14/01/1984’, 3100, null, 50); INSERT INTO emp VALUES (8002, ’JIM’, ’CLERK’, 8001, ’18/04/1984’, 1020, null, 50); INSERT INTO emp VALUES (8003, ’SHEILA’, ’CLERK’, 8001, ’08/12/1984’, 955, null, 50); 2. Transfer one of the new clerks to a different department and transfer one of the previously existing clerks to your new department. UPDATE emp SET deptno = 40, mgr = 7788 WHERE empno = 8002; UPDATE emp SET deptno = 50, mgr = 8001 WHERE empno = 7876; 3. Create a new table called JOBS with two fields, a SMALLINT called JOBNO and a 15 character text field called JOB. CREATE TABLE jobs ( jobno SMALLINT, job VARCHAR(15) ); 4. Fill your new JOBS table with null values for the JOBNO and the job values from the EMP table. There should be only one row with each job type (ie. no repeats). INSERT INTO jobs (job) SELECT DISTINCT job FROM emp; 5. Give a unique job number to each job type. UPDATE jobs SET jobno = 10 WHERE job = ’ANALYST’; UPDATE jobs SET jobno = 20 WHERE job = ’CLERK’; UPDATE jobs SET jobno = 30 WHERE job = ’MANAGER’; UPDATE jobs SET jobno = 40 WHERE job = ’SALESMAN’; UPDATE jobs SET jobno = 50 WHERE job = ’PRESIDENT’; 6. Create a new empty table called EMP1. This table should have the same fields as EMP but with an additional field called JOBNO of type SMALLINT. (Note—also make EMPNO and DEPTNO type SMALLINT.) CREATE TABLE "emp1" ( "empno" SMALLINT, "ename" VARCHAR(15), "job" VARCHAR(15), "mgr" SMALLINT, "hiredate" DATE, "sal" SMALLINT, "comm" SMALLINT, "deptno" SMALLINT, "jobno" SMALLINT); 7. Fill your new EMP1 table with the data from EMP and JOBS. INSERT INTO emp1 SELECT emp.*, jobno FROM emp INNER JOIN jobs ON emp.job = jobs.job; 8. Remove the JOB column from your EMP1 table. ALTER TABLE emp1 DROP COLUMN job; —OR-- SELECT empno, ename, mgr, hiredate, sal, comm, deptno, jobno INTO temp FROM emp1; DROP TABLE emp1; ALTER TABLE temp RENAME TO emp1; 9. Display the data from the EMP1 and JOBS tables so that the output is identical to the original EMP table. SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp1 INNER JOIN jobs ON emp1.jobno = jobs.jobno; |
|
|
More SQL-QUERIES
1. Display all the information of the EMP table?
ANS) select * from emp; 2. Display unique Jobs from EMP table? ANS) select distinct job from emp; 3. List the emps in the asc order of their Salaries? ANS) select empno from emp order by sal asc; 4. List the details of the emps in asc order of the Dptnos and desc of Jobs? ANS) SELECT * FROM ( SELECT * FROM EMP ORDER BY SAL DESC) EMP ORDER BY DEPTNO ASC;Select * from emp where order by deptno,job desc. 5. Display all the unique job groups in the descending order? ANS)SELECT DISTINCT JOB FROM EMP ORDER BY JOB DESC; 6. Display all the details of all ‘Mgrs’ ANS)SELECT * FROM EMP WHERE JOB = ‘MANAGER’; 7. List the emps who joined before 1981. ANS)SELECT * FROM EMP WHERE HIREDATE < ’01-JAN-1981’; 8. List the Empno, Ename, Sal, Daily sal of all emps in the asc order of Annsal. ANS)SELECT EMPNO,ENAME,SAL,SAL/30 FROM EMP 9. Display the Empno, Ename, job, Hiredate, Exp of all Mgrs Ans)SELECT EMPNO,ENAME,JOB,HIREDATE,(SYSDATE-HIREDATE)/365 FROM EMP WHERE JOB = ‘MANAGER’; 10. List the Empno, Ename, Sal, Exp of all emps working for Mgr 7698. ANS)SELECT EMPNO,ENAME,SAL,(SYSDATE-HIREDATE)/365 EXP FROM EMP WHERE MGR=7698; 11. Display all the details of the emps whose Comm. Is more than their Sal. ANS)SELECT * FROM EMP WHERE COMM > SAL; 12. List the emps in the asc order of Designations of those joined after the second half of 1981. ANS)select * from emp where hiredate between ’01-jul-1981’ and ’31-dec-1981’ ORDER BY MGR; 13. List the emps along with their Exp and Daily Sal is more than Rs.100. Ans)SELECT EMPNO,SYSDATE-HIREDATE FROM EMP WHERE SAL/30>100. 14. List the emps who are either ‘CLERK’ or ‘ANALYST’ in the Desc order. Ans) SELECT * FROM EMP WHERE LOWER(JOB) = ‘clerk’ OR LOWER(JOB) = ‘analyst’; 15. List the emps who joined on 1-MAY-81,3-DEC-81,17-DEC-81,19-JAN-80 in asc order of seniority. ANS)SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’DD-MON-YY’) IN (‘1-MAY-81’,’3-DEC-81’’17-DEC-81’’19-JAN-80’) ORDER BY HIREDATE; 16. List the emp who are working for the Deptno 10 or20. Ans) SELECT * FROM EMP WHERE DEPTNO IN (10,20); 17. List the emps who are joined in the year 81. Ans)SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YY’)=’81’; 18. List the emps who are joined in the month of Aug 1980. Ans)SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’MON-YY’)=’AUG-80’; 19. List the emps Who Annual sal ranging from 22000 and 45000. Ans)SELECT * FRM EMP WHERE SAL*12 BETWEEN 30000 AND 50000; 20. List the Enames those are having five characters in their Names. Ans)SELECT ENAME FROM EMP WHERE LENGTH(ENAME)=5 21. List the Enames those are starting with ‘S’ and with five characters. Ans)SELECT ENAME FROM EMP WHERE ENAME LIKE(‘%S’) AND LENGTH(ENAME)=5; 22. List the emps those are having four chars and third character must be ‘r’. Ans)SELECT ENAME FROM EMP WHERE LENGTH(ENAME)=4 AND ENAME LIKE(‘__r%’); 23. List the Five character names starting with ‘S’ and ending with ‘H’. Ans)SELECT ENAME FROM EMP WHERE ENAME LIKE ’S%H’ AND LENGTH(ENAME)=5; 24. List the emps who joined in January. Ans)SELECT EMPNO FROM EMP WHERE TO_CHAR(HIREDATE,’FMMONTH’) LIKE ’JANUARY’; 25. List the emps who joined in the month of which second character is ‘a’. Ans)SELECT * FRM EMP WHERE TO_CHAR(HIREDATE,’MONTH’) LIKE ‘_A%’); 26. List the emps whose Sal is four digit number ending with Zero. Ans)SELECT * FROM EMP WHERE TO_CHAR(SAL) LIKE ‘%00’; SELECT * FROM EMP WHERE SAL LIKE ‘%00’; 27. List the emps whose names having a character set ‘ll’ together. Ans) SELECT * FROM EMP WHERE ENAME LIKE ‘%LL%’; 28. List the emps those who joined in 80’s. Ans)SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YY’) LIKE ‘80’; 29. List the emps who does not belong to Deptno 20. Ans) SELECT * FROM EMP WHERE DEPTNO N0T<>’20’; 30. List all the emps except ‘PRESIDENT’ & ‘MGR” in asc order of Salaries. Ans) select * FROM EMP WHERE JOB NOT IN (‘PRESIDENT’,’MANAGER’) ORDER BY SAL; 31. List all the emps who joined before or after 1981. Ans)SELECT * FROM EMP WHERE T_CHAR(HIREDATE,’YYYY’) NOT LIKE ‘1981’; 32. List the emps whose Empno not starting with digit78. Ans)SELECT * FROM EMP WHERE EMPNO NOT LIKE ‘78%’; 33. List the emps who are working under ‘MGR’. Ans)select empno,ename from emp where mgr = (select distinct (mgr) from emp where lower(job)=’manager’; 34. List the emps who joined in any year but not belongs to the month of March. ANS)select * from emp where lower(to_char(hiredate,’fmmonth’)) <> ‘march’; 35. List all the Clerks of Deptno 20. ANS) select * from where deptno=20 and lower(job)=’clerk’; 36. List the emps of Deptno 30 or 10 joined in the year 1981. ANS)select * from emp where deptno in(10,30) and to_char(hiredate,’yyyy’) = 1981 37. Display the details of SMITH. ANS) select * from emp where lower(ename) like ‘smith’; 38. Display the location of SMITH. ANS)select loc from dept where deptno =(select deptno from emp where lower(ename) like ‘smith’ |