Master SQL Basics and Interview Questions: Essential Queries, Functions, and Tips

SQL Basics Overview

Explanation of SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT clauses with examples.

[StudentID, AVG(Score): aggregate function avg(Score)]

[Score Table: score]

[b.course_id='0003' and b.score>80]

[Group by StudentID for average]

[Having condition >60]

[ORDER BY Score ASC/DESC]

[LIMIT 2 -> read 2 rows starting from index 0]

select * from table limit 2,1;        
-- skip 2 rows, take 1 row (read the 3rd row)

Aggregate Functions: DISTINCT, SUM, COUNT, AVG, MAX, MIN

Joins: INNER JOIN (default), LEFT JOIN, RIGHT JOIN, UNION (remove duplicates), UNION ALL (keep duplicates)

UNION (set union) UNION ALL (allow duplicates) Oracle (SQL Server) databases

INTERSECT (intersection)

MINUS / EXCEPT (difference)

Oracle

Database Objects: Table, View, Sequence, Index, Synonym

1. View: stored SELECT statement

create view emp_vw as
select employee_id, last_name, salary
from employees
where department_id = 90;

select * from emp_vw;

Simple views can be DML operated.

update emp_vw
set last_name = 'HelloKitty'
where employee_id = 100;

select * from employees
where employee_id = 100;

1) Complex View

create view emp_vw2 as
select department_id, avg(salary) avg_sal
from employees
group by department_id;

select * from emp_vw2;

Complex views cannot be DML operated.

update emp_vw2
set avg_sal = 10000
where department_id = 100;

2. Sequence: generate ordered numbers, often for primary keys

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;

Issues when multiple tables share the same sequence, rollback, or exceptions.

create table emp1(
    id number(10),
    name varchar2(30)
);
insert into emp1
values(emp_seq1.nextval, '张三');
select * from emp1;

3. Index: improve query efficiency

Automatic creation: Oracle creates an index for columns with UNIQUE or PRIMARY KEY constraints.

create table emp2(
    id number(10) primary key,
    name varchar2(30)
);
create index emp_idx on emp2(name);
create index emp_idx2 on emp2(id, name);

4. Synonym

create synonym d1 for departments;
select * from d1;

5. Table DDL and DML

DDL: create table, drop table, rename, truncate, alter table.

DML: insert, update set, delete where.

Important: select ... aggregate functions (MIN/MAX/SUM/AVG/COUNT) from ... join ... on ... left/right joins

where ...

group by ...

(non‑aggregated columns must appear in GROUP BY for Oracle/SQL Server) having ... filter groups

order by ... asc/desc

limit (0,4)

top N rows

UNION (set union) UNION ALL (allow duplicates) INTERSECT (intersection) MINUS (difference)

DCL: COMMIT, ROLLBACK, GRANT, REVOKE.

When to create indexes (illustrated).

Common SQL Interview Questions: Classic 50

Four tables: student (学号, 姓名, 出生日期, 性别), score (学号, 课程号, 成绩), course (课程号, 课程名称, 教师号), teacher (教师号, 教师姓名).

1. Creating Database and Tables

Steps to create student, score, course, and teacher tables with primary keys and constraints (screenshots omitted).

2. Summary Statistics and Group Analysis

Examples include total score for a course, number of students selecting a course, highest/lowest scores per course, student count per course, gender count, average scores >60, students with at least two courses, duplicate names, failing courses, ordering by average score, etc.

3. Complex Queries

Subquery examples: students with any failing grade, students not enrolled in all courses, students enrolled in exactly two courses, students born in 1990, top‑N per group using UNION ALL, etc.

4. Top‑N Problems

Finding top products per category, using GROUP BY with MAX, correlated subqueries, and UNION ALL to combine per‑group results.

5. Multi‑table Queries

Examples: student course count and total score, average score >85, student‑course list, pass/fail counts per course using CASE, score ranges per course, specific course and score condition, pivoting rows to columns using CASE and aggregation.

select student_id,
       max(case course_id when '0001' then score else 0 end) as 'Course0001',
       max(case course_id when '0002' then score else 0 end) as 'Course0002',
       max(case course_id when '0003' then score else 0 end) as 'Course0003'
from score
group by student_id;

Resulting pivot table shown.

Author: sh_c_2450957609 (source reference omitted).