SQL Flashcards
- What is a Primary key?
PK is a column in the Table that uniquely identifies each record, and the value is never duplicated in the same table. PK cannot contain NULL Values
OR
A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records. A primary key’s main features are: It must contain a unique value for each row of data. It cannot contain null values.
- What is a Foreign key?
Is existence of PK in another table
Is a key used to link two tables together
It can accept Null Values
We can have more than one Foreign Key in a table.
OR
In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
- How would you avoid duplicate records in a SELECT query?
DISTINCT identifier eliminates duplicate records.
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;
- When and why do we use ORDER BY Clause?
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
ORDER BY 1 DESC;
- What is the difference between AND and OR operators?
The AND and OR operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND is TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90 AND LAST_NAME=’King’;
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90 OR LAST_NAME=’King’;
- What is the difference between IN and BETWEEN operators?
Both of these operators are used to find out the multiple values from the table.
Difference between these operators is that the BETWEEN operator is used to select a range of data between two values while the IN operator allows you to specify multiple values.
SELECT * FROM EMPLOYEES
WHERE SALARY IN (6000, 8000, 10000);
SELECT * FROM EMPLOYEES
WHERE SALARY BETWEEN 6000 AND 10000;
- Let’s say there are some null values in my table. How would you test those values?
A field with a NULL value is a field with no value. NULL value cannot be compared with other NULL values. Hence, it is not possible to test for NULL values with comparison operators, such as =, <, or <>. For this, we have to use the IS NULL operator.
SELECT * FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL;
SELECT * FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
- When and why do we use GROUP BY Clause?
Group by clause is used to group the results of a SELECT query based on one or more columns. The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
SELECT COUNT(*), DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN(10,50,80,100,110,120,150) GROUP BY DEPARTMENT_ID HAVING COUNT(*)>5;
- Are you familiar with functions in SQL? What type?
LOWER, UPPER, and INITCAP MAX, MIN, AVG
SELECT UPPER(COUNTRY_NAME) from COUNTRIES WHERE LENGTH(COUNTRY_NAME)>6;
SELECT * FROM EMPLOYEES
WHERE HIRE_DATE=(SELECT MIN(HIRE_DATE) FROM EMPLOYEES)
UNION
SELECT * FROM EMPLOYEES
WHERE HIRE_DATE=(SELECT MAX(HIRE_DATE) FROM EMPLOYEES);
- What is the difference between the WHERE and HAVING clauses?
WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
One syntax level difference between WHERE and HAVING clause is that, WHERE is used before GROUP BY clause, while HAVING is used after GROUP BY clause.
SELECT COUNT(*), DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN(50,80,100) GROUP BY DEPARTMENT_ID HAVING COUNT(*)>5;
- What is a Subquery?
A subquery is a query within another query, also known as a nested query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
SELECT * FROM EMPLOYEES
WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEES);
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM DEPARTMENTS
WHERE DEPARTMENT_NAME LIKE ‘A%’);
- How would you retrieve data that is located in 2 different tables?
Using the join of two tables based on condition we can retrieve data from two tables.
SELECT COUNT(EMPLOYEE_ID), E.DEPARTMENT_ID,
DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, DEPARTMENT_NAME;
- What is the difference between an inner and outer join?
An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared.
SELECT COUNT(EMPLOYEE_ID), E.DEPARTMENT_ID,
DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, DEPARTMENT_NAME;
An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.
SELECT COUNT(EMPLOYEE_ID), E.DEPARTMENT_ID,
DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, DEPARTMENT_NAME;
- What is Self Join?
A self JOIN is a regular join but the table is joined with itself.
SELECT E.FIRST_NAME AS EMPLOYEE_NAME, E.LAST_NAME AS EMPLOYEE_LASTNAME, M.FIRST_NAME AS MANAGER_NAME, M.LAST_NAME AS MANAGER_LASTNAME, M.MANAGER_ID FROM EMPLOYEES E JOIN EMPLOYEES M ON E.MANAGER_ID=M.EMPLOYEE_ID;
- How would you find a second highest salary from the table? Third highest?
SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES);
SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY < (SELECT MAX (SALARY) FROM EMPLOYEES WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEES));
