Master SQL Basics and 50 Interview Questions: From SELECT to Advanced Queries

SQL Basics

Key clauses and typical usage:

SELECT – e.g., SELECT student_id, AVG(score) FROM – e.g., FROM score WHERE – e.g., WHERE course_id='0003' AND score>80 GROUP BY – e.g., GROUP BY student_id (required in Oracle/SQL Server, optional in MySQL)

HAVING – e.g., HAVING AVG(score) > 60 ORDER BY – e.g., ORDER BY score ASC/DESC LIMIT – e.g., LIMIT 2 (returns first two rows)

SELECT * FROM table LIMIT 2,1;  -- skip first 2 rows, return 1 row
SELECT * FROM table LIMIT 2 OFFSET 1;  -- start at row 1, return 2 rows

Aggregate Functions

DISTINCT, SUM, COUNT, AVG, MAX, MIN.

Multi‑Table Joins and Set Operators

INNER JOIN (default) – JOIN ... ON ... LEFT JOIN – LEFT JOIN B ON A.key = B.key RIGHT JOIN – RIGHT JOIN B ON A.key = B.key UNION – removes duplicates

UNION ALL – keeps duplicates

INTERSECT – intersection

EXCEPT / MINUS – difference

Oracle Specific Objects

Views

Simple view creation (supports DML):

CREATE VIEW emp_vw AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 90;

SELECT * FROM emp_vw;

UPDATE emp_vw SET last_name = 'HelloKitty' WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 100;

Complex view (cannot perform DML):

CREATE VIEW emp_vw2 AS
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id;

SELECT * FROM emp_vw2;

Sequences

CREATE SEQUENCE emp_seq1
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 10000
  MINVALUE 1
  CYCLE
  NOCACHE;

SELECT emp_seq1.CURRVAL FROM dual;
SELECT emp_seq1.NEXTVAL FROM dual;

Common pitfalls: sharing a sequence across tables, rollbacks, or exceptions can create gaps.

Indexes

Automatic creation for UNIQUE or PRIMARY KEY columns:

CREATE TABLE emp2(
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(30)
);

Manual creation:

CREATE INDEX emp_idx ON emp2(name);
CREATE INDEX emp_idx2 ON emp2(id, name);

Synonyms

CREATE SYNONYM d1 FOR departments;
SELECT * FROM d1;

Tables (DDL/DML)

DDL examples:

CREATE TABLE ...;
DROP TABLE ...;
RENAME TABLE old_name TO new_name;
TRUNCATE TABLE ...;
ALTER TABLE ...;

DML examples:

INSERT INTO ... VALUES ...;
UPDATE ... SET ... WHERE ...;
DELETE FROM ... WHERE ...;

Important Query Patterns

SELECT ... FROM ... JOIN ... ON ...

– joins (including left/right outer joins) WHERE ... – row filtering GROUP BY ... – required for non‑aggregated columns in Oracle/SQL Server HAVING ... – filter groups

ORDER BY ... ASC/DESC

LIMIT (offset, count)

– top‑N results

Common Interview Questions (Schema & Sample Queries)

Schema:

student(student_id, name, birth_date, gender)

score(student_id, course_id, score)

course(course_id, course_name, teacher_id)

teacher(teacher_id, teacher_name)

Typical queries:

-- Total score for course 0002
SELECT SUM(score) FROM score WHERE course_id = '0002';

-- Number of distinct students who selected a course
SELECT COUNT(DISTINCT student_id) AS student_count FROM score;

-- Highest and lowest score per course
SELECT course_id, MAX(score) AS max_score, MIN(score) AS min_score
FROM score
GROUP BY course_id;

-- Average score per student > 60
SELECT student_id, AVG(score) AS avg_score
FROM score
GROUP BY student_id
HAVING AVG(score) > 60;

-- Students who have taken at least two courses
SELECT student_id, COUNT(course_id) AS course_cnt
FROM score
GROUP BY student_id
HAVING COUNT(course_id) >= 2;

-- Same‑name, same‑gender students
SELECT name, COUNT(*) AS cnt
FROM student
GROUP BY name, gender
HAVING COUNT(*) >= 2;

Advanced Query Techniques

Subqueries – e.g., find students whose scores are below 60 using IN or EXISTS.

EXISTS vs IN – EXISTS often yields better performance because it stops at the first match.

Top‑N per group – retrieve the highest two scores for each course_id:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) AS rn
  FROM score
) t
WHERE rn <= 2;

(Alternatively, use ORDER BY score DESC LIMIT 2 per group combined with UNION ALL.)

Pivot (row‑to‑column) – transform the score table into a wide format:

SELECT student_id,
       MAX(CASE WHEN course_id='0001' THEN score ELSE 0 END) AS course_0001,
       MAX(CASE WHEN course_id='0002' THEN score ELSE 0 END) AS course_0002,
       MAX(CASE WHEN course_id='0003' THEN score ELSE 0 END) AS course_0003
FROM score
GROUP BY student_id;

CASE WHEN – conditional calculations, e.g., salary adjustments:

SELECT employee_id, last_name, salary,
       CASE department_id
         WHEN 10 THEN salary * 1.1
         WHEN 20 THEN salary * 1.2
         ELSE salary * 1.3
       END AS new_salary
FROM employees;

Oracle DECODE can express the same logic.

Update based on aggregates – set an employee's salary to the department maximum and change the job to the one with the lowest average salary:

UPDATE employees e1
SET salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id),
    job_id = (SELECT job_id
              FROM employees
              GROUP BY job_id
              HAVING AVG(salary) = (
                SELECT MIN(avg_sal)
                FROM (SELECT AVG(salary) AS avg_sal FROM employees GROUP BY job_id)
              ))
WHERE employee_id = 108;

Delete based on aggregates – remove the lowest‑salary employee in the same department as employee 108:

DELETE FROM employees
WHERE salary = (
  SELECT MIN(salary)
  FROM employees
  WHERE department_id = (SELECT department_id FROM employees WHERE employee_id = 108)
);

SQL Optimization Tips

Prefer EXISTS over IN for correlated subqueries.

Understand when indexes are created automatically (UNIQUE/PRIMARY KEY constraints).

Be aware that sequences can have gaps due to rollbacks or shared usage.