Oracle SQL Flashcards
What is SQL?
Structured Query Language (SQL) is a query language used for designing, processing and querying the data stored in RDBMS.
AUTHORS: AUTHOR_NAME, BOOK_NAME
BOOKS: BOOK_NAME, COPIES_SOLD
Write a query to show top 3 authors who sold most books in total.
- First we need to sum the COPIES_SOLD per author
SELECT A.AUTHOR_NAME, B.COPIES_SOLD
FROM AUTHOR A NATURAL JOIN BOOKS B
GROUP BY A.AUTHOR_NAME
- For maximum copies sold, we need to order it in descending order
ORDER BY 2 DESC
- And to see top 3, we need to fetch first 3 rows only
SELECT A.AUTHOR_NAME, B.COPIES_SOLD FROM AUTHOR A NATURAL JOIN BOOKS B GROUP BY A.AUTHOR_NAME ORDER BY 2 DESC FETCH FIRST 3 ROWS ONLY;
EMP: DEPT_NAME, EMP_ID, EMP_NAME
SAL: SALARY, EMP_ID, EMP_NAME
Write a query to print every department where average salary per employee is lower than $500.
SELECT E.DEPT_NAME, S.AVG(SALARY)
FROM EMP E NATURAL JOIN SAL S
GROUP BY E.DEPT_NAME
HAVING S.AVG(SALARY)<500;
EMP: EMP_ID, EMP_NAME, SAL
Write a query to find the 5th maximum salary.
- First arrange the table to show distinct salaries in descending order (because we want maximum)
SELECT DISTINCT SAL
FROM EMP
ORDER BY 1 DESC
- We need 5th maximum, so lets fetch only 5 rows
SELECT DISTINCT SAL
FROM EMP
ORDER BY 1 DESC
FETCH FIRST 5 ROWS ONLY
- Now if we take the minimum salary from resultset, we would get 5th maximum:
SELECT MIN(SAL) FROM ( SELECT DISTINCT SAL FROM EMP ORDER BY 1 DESC FETCH FIRST 5 ROWS ONLY );
EMP: EMP_ID, EMP_NAME, SAL
Write a query to find the 3rd minimum salary.
- First arrange the table to show distinct salaries in ascending order (because we want minimum)
SELECT DISTINCT SAL
FROM EMP
ORDER BY 1
- We need 3rd minimum, so lets fetch only 3 rows
SELECT DISTINCT SAL
FROM EMP
ORDER BY 1
FETCH FIRST 3 ROWS ONLY
- Now if we take the maximum salary from resultset, we would get 3rd minimum:
SELECT MAX(SAL) FROM ( SELECT DISTINCT SAL FROM EMP ORDER BY 1 FETCH FIRST 3 ROWS ONLY );
EMP: EMP_ID, EMP_NAME, ADDRESS, SAL, DEPTNO, HIRED_DATE, JOB
Write a query to find out number of columns in this table.
SELECT COUNT(COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMP';
Why doesn’t the aliasing work this way:
SELECT SAL AS SALARY
FROM EMP
WHERE SALARY<1000
but works this way:
SELECT * FROM ( SELECT SAL AS SALARY FROM EMP ) SUBQ WHERE SALARY<1000
WHERE clause is evaluated before SELECT clause hence SALARY does not exist when WHERE clause is processed.
FROM clause is evaluated before WHERE and by placing the original query in a FROM clause, we make sure that results are generated for outermost WHERE clause and it sees the alias.
How to limit the number of rows returned (nor concerned with order, any n rows will do)?
SELECT * FROM EMP
FETCH FIRST 5 ROWS ONLY;
SELECT * FROM EMP
WHERE ROWNUM<=5;
Why does this query work:
SELECT * FROM EMP
WHERE ROWNUM <= 5;
but not this one:
SELECT * FROM EMP
WHERE ROWNUM = 5;
When Oracle executes the query, it forms a resultset.
Then it fetches the first row, calls it row number 1 and checks if it has gotten past row number 5 yet. If not, it returns the row, as it matches with the condition “less than or equal to 5”. It fetches next row and increments its counter.
This shows that ROWNUM is assigned after each row is fetched.
Using an equality condition with ROWNUM does not work the same way, Oracle fetches a row from its resultset, calls it row number 1 and checks if it has gotten past row number 5. It is not, but it also doesn’t meet the criteria “equal to 5” and hence Oracle discards the row. It then fetches next row and calls it row number 1. This goes on until the resultset is exhuasted.
How to write below query such that it gives 5 random rows every time it is executed?
SELECT * FROM EMP
FETCH FIRST 5 ROWS ONLY;
With the help of built-in function DBMS_RANDOM.VALUE()
SELECT * FROM EMP
ORDER BY DBMS_RANDOM.VALUE()
FETCH FIRST 5 ROWS ONLY;
Why can’t we use below query to find if COMM is null?
SELECT * FROM EMP
WHERE COMM=NULL;
NULL is not equal to anything, not even itself and hence “=” or “!=” cannot be used to test if a column is null
Explain NVL.
NVL() replaces null value of given column with value given by user.
For eg. NVL( Commission, 0)
If Commission is null for a particular row, it will substitute it with 0 for the resultset. If it is not null, then the original value will be displayed.
NVL takes only two arguments and will evalute the second expression even when first argument is not null. This is time consuming and hence inefficient.
Explain COALESCE.
COALESCE() function accepts a list of arguments and returns the first one that evaluates to a non-null value.
For eg. COALESCE (Name, LAST_NAME, FULL_NAME, ‘NO NAME’)
If Name column is null, Oracle will check LAST_NAME column and put its value if it is not null. If LAST_NAME is null, then it will check FULL_NAME column and stop only when it comes across first non-null value.
In the event of all arguments being null, COALESCE will return null. COALESCE uses short-circuit evaluation meaning that the function stops evaluating the remaining expressions once it finds the first one evaluates to a non-null value.
COALESCE requires that all arguments be of same datatype and hence
COALESCE (Name, 1, ‘A’) will result in error.
What will be the output:
SELECT
COALESCE (1+1, 1/0)
FROM DUAL;
2
COALESCE will evaluate the first expression, see that its not null and will not evaluate second expression.
How to match any number to character and just one character?
Any number of characters: %
Just one character: _ (underscore)
