Comprehensive SQL Basics, Interview Questions, and Advanced Query Techniques

SQL Basics Overview

The article begins with a concise review of core SQL clauses: select for selecting columns, from for specifying tables, where for filtering rows, group by for grouping, having for group filters, order by for sorting, and limit for restricting result size.

Aggregate Functions and Set Operations

Common aggregate functions such as COUNT() , SUM() , AVG() , MAX() , MIN() , and DISTINCT() are listed. Set operations UNION , UNION ALL , INTERSECT , and MINUS (or EXCEPT ) are also described.

Oracle Specific Objects

Key Oracle database objects are introduced: tables, views, sequences, indexes, and synonyms. Example view creation:

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

Updating a simple view:

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

Creating a sequence:

create sequence emp_seq1 start with 1 increment by 1 maxvalue 10000 minvalue 1 cycle nocache;

Using the sequence:

select emp_seq1.currval from dual;

select emp_seq1.nextval from dual;

Index creation examples:

create index emp_idx on emp2(name);

create index emp_idx2 on emp2(id, name);

Synonym creation:

create synonym d1 for departments;

Data Definition and Manipulation Language

DDL commands ( create table , drop table , alter table , etc.) and DML commands ( insert , update , delete ) are summarized, emphasizing the use of aggregate functions and clauses in typical queries.

Important Query Patterns

select ... from ... where ... group by ... having ... order by ... limit ...

Using exists instead of in for better performance.

Updating an employee's salary to the department maximum and setting the job to the lowest‑average salary job.

Deleting the lowest‑salary employee in a department.

Common SQL Interview Questions (50 Classic Problems)

The article lists 50 typical interview questions covering table creation, data insertion, aggregation, grouping, subqueries, and complex joins. Sample questions include:

Query students with surname "猴".

Count teachers whose name starts with "孟".

Calculate total score for a specific course.

Find students whose average score exceeds 60.

Retrieve students who have not taken all courses.

Pivot a score table from rows to columns using CASE expressions and aggregation.

Top‑N Queries

Techniques for retrieving the top N rows per group are demonstrated using order by ... desc limit N combined with union all , as well as correlated subqueries to fetch rows with maximum or minimum values per group.

Row‑to‑Column (Pivot) Transformation

Using CASE statements to turn course scores from rows into separate columns per student, followed by max aggregation to collapse duplicate rows.

Additional Resources and Promotions

The article contains promotional notes encouraging readers to follow a public account for Java interview materials and to click “在看” and “星标” for updates.

Source: blog.csdn.net/u010565545/article/details/100785261