SQL Fundamentals

Question 1

How do you concat column names in different database systems?

Answer 1

DB2, Oracle, PostgreSQL - These databases use the double vertical bar as the concatenation operator:
~~~
select ename||’ WORKS AS A ‘||job as msg
from emp
where deptno=10
~~~
MySQL - This database supports a function called CONCAT:
~~~
select concat(ename, ‘ WORKS AS A ‘,job) as msg
from emp
where deptno=10
~~~

Question 2

How do you use conditional logic in the SELECT statement?

Answer 2

Use the CASE expression to perform conditional logic directly in your SELECT
statement:

select ename,sal,
case when sal <= 2000 then 'UNDERPAID'
when sal >= 4000 then 'OVERPAID'
else 'OK'
end as status
from emp

Question 2

How do you limit the number of rows returned from a query in different databse systems?

Answer 2

MySQL and PostgreSQL - Do the same thing in MySQL and PostgreSQL using LIMIT:
~~~
select *
from emp limit 5
~~~
Oracle - In Oracle, place a restriction on the number of rows returned by restricting ROW‐
NUM in the WHERE clause:
~~~
select *
from emp
where rownum <= 5
~~~

Question 3

How do you transform NULL values into some real value?

Answer 3

Use the function COALESCE to substitute real values for nulls:
~~~
select coalesce(comm,0)
from emp
~~~

Question 4

How do you sort the result of a query in ascending order?

Answer 4

Use the ORDER BY clause:
~~~
select ename,job,sal
from emp
where deptno = 10
order by sal asc
~~~

Question 5

How do you sort the result of a query based on a substring?

Answer 5

DB2, MySQL, Oracle, and PostgreSQL - Use the SUBSTRING function in the ORDER BY clause:
~~~
select ename,job
from emp
order by substring(job,start position, how many chars);
~~~
Note: SQL strings index starts from 1.

Question 6

What does the ROW_NUMBER() window function do?

Answer 6

Assigns a unique identifier to each row by partition.
~~~
SELECT
ROW_NUMBER() OVER (ORDER BY column_name) AS row_num,
column1, column2
FROM your_table;
~~~

Question 7

What does RANK() and DENSE_RANK() window function do? What are the difference between them?

Answer 7

Assigns a unique rank to each value in the partition with the same values receiving the same rank. With RANK() if two values are tied, at rank 2, then the next rank will be 4 (gaps in ranks), but DENSE_RANK() does not have gaps even with ties.
~~~
SELECT
RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS ranking,
product_name, sales
FROM products;
~~~

Question 8

What does the NTILE(n) window function do?

Answer 8

The NTILE(n) window function divides the result set into n number of partitions.
~~~
SELECT
NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
employee_name, salary
FROM employees;
~~~

Question 9

What is an example of the SUM(), AVG(), MIN(), MAX(), COUNT() window functions?

Answer 9

SELECT 
    department, 
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

Question 10

What do the LEAD(column) and LAG(column) window functions do?

Answer 10

The LEAD() function access the next row in the column, and the LAG() function accesses the previous number in the column.
~~~
SELECT
employee_id,
salary,
LEAD(salary) OVER (ORDER BY hire_date) AS next_salary,
LAG(salary) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;
~~~

Question 11

What do the FIRST_VALUE() and LAST_VALUE() window functions do?

Answer 11

FIRST_VALUE() returns the first value in the window and LAST_VALUE() returns the last value in the window.
~~~
SELECT
employee_id,
salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) AS first_salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) AS last_salary
FROM employees;
~~~