SQL Flashcards

Keyword to retrieve data

SELECT

Clause that specifies table(s) to query

FROM

Clause to filter rows

WHERE

Clause to sort results

ORDER BY ASC|DESC

Keyword to remove duplicate rows

DISTINCT

Limit number of rows returned (MySQL/Postgres)

LIMIT

Wildcard operator for pattern matching

LIKE

Operator to test range inclusively

BETWEEN AND

Operator to test membership in list

IN (val1, val2, …)

Predicate to test missing values

IS NULL / IS NOT NULL

Syntax to alias a column

SELECT column AS alias

Concatenate two conditions AND / OR

WHERE cond1 AND cond2

Negate a condition

NOT condition

Return only unique combinations of columns

SELECT DISTINCT col1, col2

Select all columns

SELECT *

Retrieve current date in SQL Standard

CURRENT_DATE

Retrieve number of rows in result

COUNT(*)

Arithmetic operator for integer division in SQL Server

/

Concatenate strings in Postgres

||

Function to convert string to upper case

UPPER(string)

Aggregate function to sum numeric column

SUM(column)

Aggregate function to calculate average

AVG(column)

Find minimum value in column

MIN(column)

Find maximum value in column

MAX(column)

Group rows that share same values

GROUP BY column

Filter groups after aggregation

HAVING condition

Count non-NULL values

COUNT(column)

Count distinct values in column

COUNT(DISTINCT column)

Alias aggregate results

SELECT AVG(price) AS avg_price

Place HAVING clause

After GROUP BY, before ORDER BY

Join returning matching rows in both tables

INNER JOIN

Join returning all left table rows plus matches

LEFT (OUTER) JOIN

Join returning all right table rows plus matches

RIGHT (OUTER) JOIN

Join returning all rows from both, NULL when no match

FULL (OUTER) JOIN

Join that pairs every row of two tables

CROSS JOIN

Keyword to specify join condition

ON

Join using columns with same name automatically

NATURAL JOIN

Self‑join aliasing same table twice

SELECT … FROM table t1 JOIN table t2 ON …

Using clause when join columns share name

USING (column)

Difference between INNER and LEFT JOIN

LEFT keeps unmatched left rows, INNER drops them

Combine results eliminating duplicates

UNION

Combine results keeping duplicates

UNION ALL

Return rows common to both result sets

INTERSECT

Return rows in first result not in second

EXCEPT (Postgres) / MINUS (Oracle)

Requirement for set ops columns

Same number and compatible data types

Subquery in SELECT list

Scalar subquery

Subquery in FROM clause

Derived table / inline view

Subquery depending on outer query

Correlated subquery

Check existence of rows in subquery

EXISTS (subquery)

Syntax to compare value to all from subquery

value > ALL (subquery)

Create a new table

CREATE TABLE table_name (…);

Primary key constraint

PRIMARY KEY (column)

Auto‑increment integer column MySQL

INT AUTO_INCREMENT PRIMARY KEY

Ensure column cannot be NULL

NOT NULL

Ensure unique values in column

UNIQUE (column)

Referential link to another table

FOREIGN KEY (col) REFERENCES ref_table(col)

Default value for column

DEFAULT

Add a column to existing table

ALTER TABLE table_name ADD column datatype;

Modify data type of column

ALTER TABLE tablename ALTER COLUMN col TYPE newtype;

Remove a table

DROP TABLE table_name;

Rename a table (Postgres)

ALTER TABLE oldname RENAME TO newname;

Create table from select results

CREATE TABLE new AS SELECT …;

Truncate table data quickly

TRUNCATE TABLE table_name;

Variable‑length character data

VARCHAR(n)

Fixed‑length character data

CHAR(n)

Integer data type standard

INT or INTEGER

Exact numeric with scale and precision

DECIMAL(p,s)

Date without time

DATE

Timestamp with time zone Postgres

TIMESTAMPTZ

Boolean literals in SQL

TRUE / FALSE

Binary large object in MySQL

BLOB

UUID type purpose

Store unique identifiers

Insert new row

INSERT INTO table (columns) VALUES (values);

Insert from another table

INSERT INTO table SELECT …;

Update existing rows

UPDATE table SET column=value WHERE condition;

Delete rows

DELETE FROM table WHERE condition;

Copy table structure only

CREATE TABLE new LIKE existing;

Upsert in Postgres

INSERT … ON CONFLICT (key) DO UPDATE

Merge command standard SQL

MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE SET … WHEN NOT MATCHED THEN INSERT …;

Return generated keys after insert (MySQL)

INSERT … RETURNING id; (Postgres)

Start a transaction

BEGIN; or START TRANSACTION;

Save changes in transaction

COMMIT;

Undo uncommitted changes

ROLLBACK;

Savepoint purpose

Partial rollback marker within transaction

Set transaction isolation level

SET TRANSACTION ISOLATION LEVEL ;

Default isolation in Postgres

Read Committed

Create an index on column

CREATE INDEX idx_name ON table(column);

Unique index ensures

No duplicate values in indexed column(s)

Remove an index

DROP INDEX idx_name;

Composite index

Index on multiple columns

B‑tree index suited for

Equality and range queries

Create a view

CREATE VIEW view_name AS SELECT …;

Drop a view

DROP VIEW view_name;

Temporary result named in WITH clause

Common Table Expression (CTE)

Recursive CTE keyword

WITH RECURSIVE

Materialized view difference

Stores data physically, needs refresh

Syntax to define window frame

OVER (PARTITION BY … ORDER BY …)

Assign sequential number to rows

ROW_NUMBER() OVER (…)

Rank with gaps for ties

RANK() OVER (…)

Aggregate without collapsing rows

SUM(column) OVER (…)