SQL

Hard

Skills

The

list

COMPLET E

INVENT ORY

Each

skill

below

expanded

with

the

specific

syntax

construct

operational

practice

that

demonstrates

skill

counts

hard

skill

when

can

verified

code

review

interview

exercise

production

incident

SELECT

and

projection

WHERE

filtering

and

predicates

ORDER

sorting

and

collation

GROUP

and

HAVING

DISTINCT

and

deduplication

LIMIT

OFFSET

pagination

CASE

expressions

NULL

handling

—

COALESCE

NULLIF

NULL

String

functions

Date

and

time

functions

Numeric

functions

and

rounding

CAST

and

type

conversion

Inner

joins

Left

and

right

outer

joins

Full

outer

joins

Cross

joins

Self

joins

Non

equi

and

range

joins

UNION

and

UNION

ALL

INTERSECT

and

EXCEPT

Scalar

subqueries

Correlated

subqueries

Derived

tables

and

inline

views

Common

table

expressions

(

WITH

)

Recursive

CTEs

Aggregate

functions

—

SUM

AVG

COUNT

MIN

MAX

Window

functions

and

OVER

clauses

Ranking

—

ROW

NUMBER

RANK

DENSE

RANK

NTILE

Offset

functions

—

LAG

LEAD

FIRST

VALUE

Running

totals

and

moving

averages

GROUPING

SETS

ROLLUP

CUBE

PIVOT

and

UNPIVOT

CREATE

ALTER

DROP

statements

Primary

and

foreign

keys

Constraints

—

UNIQUE

CHECK

NOT

NULL

DEFAULT

Normalization

through

)

Star

and

snowflake

schema

design

tree

composite

covering

and

partial

indexes

Query

execution

plans

and

EXPLAIN

Query

optimization

and

sargability

Statistics

collection

and

index

maintenance

Table

partitioning

INSERT

UPDATE

DELETE

MERGE

and

UPSERT

patterns

Bulk

—

COPY

BULK

INSERT

Transactions

—

BEGIN

COMMIT

ROLLBACK

Savepoints

and

partial

rollback

Isolation

levels

Locking

and

deadlock

analysis

MVCC

concepts

Views

and

materialized

views

Stored

procedures

and

user

defined

functions

Triggers

Temporary

and

staging

tables

GRANT

REVOKE

and

role

management

Row

level

security

Parameterized

queries

and

SQL

injection

prevention

JSON

and

semi

structured

functions

Full

text

Regular

expressions

SQL

ETL

and

ELT

pipeline

patterns

Incremental

and

change

data

capture

loads

Fact

and

dimension

modeling

Query

profiling

and

instrumentation

Dialect

fluency

across

engines

Data

validation

and

reconciliation

queries

Audit

and

slowly

changing

dimension

logic

1 / 8

Skills

detail

GROUPED

DOMAIN

Query

Fundamentals

Core

syntax

SELECT

and

projection

Naming

explicit

columns

instead

SELECT *

aliasing

with

and

controlling

output

order

and

cardinality

Filtering

and

predicate

logic

WHERE

with

AND

NOT

BETWEEN

and

correct

operator

precedence

Grouping

and

aggregation

GROUP BY

paired

with

HAVING

and

knowing

why

WHERE

clause

cannot

filter

aggregate

Sorting

and

pagination

ORDER BY … ASC/DESC

multi

key

sorts

and

keyset

pagination

replacement

for

deep

OFFSET

NULL

semantics

Three

valued

logic

IS NULL

versus

= NULL

and

defensive

use

COALESCE()

and

NULLIF()

Expression

functions

String

date

and

numeric

functions

—

SUBSTRING

TRIM

DATE_TRUNC

EXTRACT

ROUND

—

plus

conditional

CASE

logic

Type

conversion

Explicit

CAST(value AS type)

and

safe

conversion

avoiding

implicit

casts

that

silently

break

index

usage

Joins

Set

Operations

Relational

algebra

Join

types

INNER

LEFT

RIGHT

and

FULL OUTER JOIN

—

including

how

filter

the

WHERE

clause

silently

converts

outer

join

into

inner

one

Self

joins

and

hierarchies

Joining

table

itself

compare

rows

find

duplicates

walk

parent

child

reporting

structure

Non

equi

and

range

joins

Joining

inequalities

date

ranges

intervals

—

the

basis

and

point

time

joins

Set

operators

UNION

versus

UNION ALL

(

and

why

the

latter

faster

plus

INTERSECT

and

EXCEPT

for

set

comparison

and

reconciliation

Cardinality

control

Diagnosing

fan

out

and

row

multiplication

caused

one

many

joins

and

correcting

with

pre

aggregation

DISTINCT

2 / 8

Subqueries

CTEs

Query

composition

Scalar

and

derived

subqueries

Subqueries

the

SELECT

FROM

and

WHERE

clauses

including

inline

views

derived

tables

Correlated

subqueries

Subqueries

that

reference

the

outer

row

—

EXISTS

and

NOT EXISTS

for

anti

join

semantics

Common

table

expressions

WITH

blocks

that

break

query

into

readable

stages

plus

multiple

CTEs

chained

one

statement

Recursive

CTEs

WITH RECURSIVE

for

org

charts

bill

materials

and

graph

traversal

with

termination

condition

Aggregation

Window

Functions

Analytics

Aggregate

functions

COUNT(*)

versus

COUNT(column)

SUM

AVG

MIN

MAX

and

conditional

aggregation

with

CASE

Window

functions

OVER (PARTITION BY … ORDER BY …)

and

the

distinction

between

window

frame

and

GROUP BY

Ranking

functions

ROW_NUMBER()

RANK()

DENSE_RANK()

NTILE()

—

and

choosing

correctly

when

there

are

ties

Offset

and

running

calculations

LAG()

LEAD()

and

cumulative

sums

using

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Multi

level

aggregation

GROUPING SETS

ROLLUP

and

CUBE

for

subtotaled

reporting

output

single

pass

Pivot

and

unpivot

Reshaping

rows

into

columns

and

back

—

natively

some

engines

via

conditional

aggregation

others

3 / 8

Data

Definition

Modeling

Schema

DDL

statements

CREATE TABLE

ALTER TABLE

DROP

and

choosing

appropriate

data

types

for

each

column

Keys

and

constraints

Primary

keys

foreign

keys

with

referential

actions

UNIQUE

CHECK

NOT NULL

and

DEFAULT

Normalization

Applying

first

second

and

third

normal

form

remove

redundancy

—

and

knowing

when

denormalize

deliberately

Dimensional

modeling

Fact

and

dimension

tables

grain

definition

surrogate

keys

and

star

versus

snowflake

schemas

Partitioning

strategy

Range

list

and

hash

partitioning

for

large

tables

plus

partition

pruning

queries

Data

Manipulation

DML

INSERT

UPDATE

DELETE

Set

based

modification

with

WHERE

clause

INSERT … SELECT

and

the

danger

unqualified

updates

MERGE

and

upsert

MERGE INTO … USING … WHEN MATCHED / NOT MATCHED

and

PostgreSQL

ON CONFLICT DO UPDATE

Bulk

load

operations

COPY

BULK INSERT

and

staging

table

loads

with

post

load

validation

queries

Reconciliation

queries

Full

outer

join

and

checksum

comparisons

that

prove

load

produced

the

expected

row

counts

and

totals

4 / 8

Performance

Indexing

Tuning

Reading

execution

plans

EXPLAIN

and

EXPLAIN ANALYZE

—

sequential

scan

versus

index

scan

nested

loop

versus

hash

join

and

estimated

versus

actual

rows

Index

design

tree

composite

(

and

column

order

within

them

covering

partial

and

expression

indexes

Sargability

Writing

predicates

the

optimizer

can

push

down

—

avoiding

functions

indexed

columns

and

leading

wildcards

Statistics

and

maintenance

ANALYZE

VACUUM

and

index

rebuilds

that

keep

the

planner

estimates

accurate

Query

rewriting

Replacing

correlated

subqueries

with

joins

pushing

filters

earlier

and

eliminating

redundant

sorts

and

DISTINCTs

Instrumentation

Using

pg_stat_statements

slow

query

logs

and

wait

statistics

find

the

queries

that

actually

cost

time

Transactions

Concurrency

Reliability

Transaction

control

BEGIN

COMMIT

ROLLBACK

and

atomicity

guarantees

across

multiple

statements

Savepoints

SAVEPOINT

and

partial

rollback

for

multi

step

operations

with

recoverable

intermediate

failures

Isolation

levels

Read

committed

repeatable

read

and

serializable

—

and

which

anomalies

each

level

permits

Locking

and

deadlocks

Row

versus

table

locks

lock

ordering

and

diagnosing

deadlock

output

from

the

database

logs

MVCC

awareness

Understanding

snapshot

based

reads

why

readers

not

block

writers

and

how

long

transactions

bloat

tables

5 / 8

Programmability

Database

objects

Views

Standard

views

for

abstraction

and

access

control

and

materialized

views

with

scheduled

refresh

for

expensive

aggregates

Stored

procedures

Parameterized

server

side

routines

with

control

flow

error

handling

and

transaction

boundaries

(

pgSQL

SQL

User

defined

functions

Scalar

and

table

valued

functions

with

attention

how

they

affect

plan

caching

and

row

estimates

Triggers

Row

level

and

statement

level

triggers

for

auditing

derived

columns

and

enforcing

invariants

the

schema

cannot

express

Staging

patterns

Temporary

tables

and

session

scoped

CTEs

for

multi

stage

transforms

that

must

not

persist

Security

Access

Control

Governance

Privilege

management

GRANT

and

REVOKE

table

column

and

schema

level

plus

role

inheritance

and

least

privilege

design

Row

level

security

Policy

based

filtering

that

restricts

which

rows

connection

can

read

modify

independent

application

code

Injection

prevention

Parameterized

queries

and

prepared

statements

the

primary

defense

—

never

string

concatenation

into

SQL

Data

masking

and

audit

View

based

masking

sensitive

columns

and

audit

tables

that

record

who

queried

changed

what

6 / 8

Semi

Structured

Data

Modern

workloads

JSON

functions

Storing

and

querying

JSON

JSONB

path

extraction

containment

operators

and

indexing

JSON

expressions

Full

text

Text

indexes

tokenization

ranking

and

prefix

fuzzy

matching

inside

the

database

Regular

expressions

Pattern

matching

extraction

and

replacement

with

the

database

native

regex

operators

Array

and

range

types

Querying

array

columns

with

containment

operators

and

range

types

with

overlap

and

adjacency

predicates

Pipelines

Warehousing

Dialects

Scale

ETL

and

ELT

patterns

Extract

transform

load

versus

extract

load

transform

idempotent

loads

and

backfill

safe

job

design

Incremental

and

CDC

loads

High

water

mark

logic

merge

based

upserts

and

consuming

change

streams

without

duplicating

rows

Slowly

changing

dimensions

Type

and

Type

dimension

history

effective

date

ranges

and

current

flag

columns

Warehouse

scale

SQL

Columnar

engines

clustered

distribution

keys

cost

aware

queries

and

where

SQL

differs

Snowflake

and

BigQuery

Dialect

fluency

Portable

ANSI

SQL

plus

the

specifics

PostgreSQL

MySQL

SQL

and

SQLite

—

date

functions

LIMIT

versus

TOP

and

null

ordering

Version

controlled

transformations

Writing

SQL

reviewed

tested

versioned

artifacts

—

with

dbt

style

models

tests

and

documented

lineage

7 / 8

Proficiency

benchmark

Assessment

guide

LEVEL

DEMONSTRATED

EVIDENCE

WORKING

Writes

joins

GROUP BY

and

subqueries

unaided

;

reads

execution

plan

with

guidance

;

uses

transactions

correctly

ADVANCED

Designs

window

function

analytics

and

recursive

CTEs

;

tunes

slow

query

changing

indexes

rewriting

predicates

;

models

fact

dimension

schema

EXPERT

Diagnoses

production

deadlock

;

chooses

isolation

levels

deliberately

;

ships

idempotent

backfill

safe

pipelines

;

works

fluently

across

two

dialects

8 / 8

SQL Hard Skills

Prompt to Clone This SQL Hard Skills

Apply this example's core structure, strategic pacing, and structural hierarchy directly to your own topic.

What are you generating today?