What is SQL? Databases, tables and your first query

#Why it matters

What is SQL? It is the language you use to ask questions of data stored in tables, and it is the single most requested skill in data jobs: 88% of the data analyst postings we analysed for the Data Analyst roadmap list it, as do 80% of data engineer and 78% of software engineer postings. The first technical round for most analyst roles in India is a timed SQL test, so the habits you build in this lesson (inspect first, query second) pay off from week one.

#The concept

SQL (Structured Query Language, said "S-Q-L" or "sequel") is a declarative language. You describe the result you want; the database works out how to fetch it. You never write a loop over rows.

#Tables, rows, columns and schemas

A relational database stores data in tables. A table looks like a spreadsheet, but with rules a spreadsheet does not enforce:

Types are enforced because they make the data trustworthy. If fare_amount is declared as a number, nobody can type "twelve" into it, and every sum you run is a real sum.

A schema is a named group of tables (like a folder). In PostgreSQL the default is public, in SQL Server dbo, in DuckDB main. A database holds one or more schemas.

#Dialects: what changes between databases

Job descriptions name different databases. They all speak SQL, with small differences:

Around 90% of what you learn transfers unchanged: SELECT, WHERE, GROUP BY, joins and window functions are standard. What changes is function names (dates especially), quoting, and row limiting. The advanced lesson on dialect differences covers the full list.

#Three ways to run SQL with no admin rights

1. In the browser: open shell.duckdb.org. Nothing to install; good for a college or office laptop where you cannot install software.

2. The DuckDB CLI: pip install duckdb-cli (or download the binary from duckdb.org), then run duckdb. One file, no server, no password.

3. PostgreSQL in Docker: docker run -d --name pg -e POSTGRES_PASSWORD=pg -p 5432:5432 postgres:17, then connect with psql -h localhost -U postgres. This is a real database server like the one at work.

This shelf uses DuckDB for most examples and PostgreSQL whenever a server behaviour matters.

#Written order vs logical order

You write a query in this order:

select   payment_type, count(*) as trips   -- 5. pick and compute columns
from     trips                             -- 1. find the table
where    trip_distance > 0                 -- 2. keep matching rows
group by payment_type                      -- 3. form groups
having   count(*) > 1000                   -- 4. keep matching groups
order by trips desc                        -- 6. sort
limit    3;                                -- 7. cut

The database evaluates it in the numbered order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This one fact explains a lot of error messages. An alias created in SELECT (step 5) does not exist yet in WHERE (step 2), so standard SQL rejects it. ORDER BY runs after SELECT, so it can use the alias.

#Identifiers vs strings

• Double quotes wrap identifiers: names of tables and columns. "Customer ID" is a column with a space in its name.

• Single quotes wrap text values: 'Manhattan'.

Mixing them up does not always fail. select 'VendorID' from trips returns the literal text VendorID on every row, which looks like data until you notice it never changes. MySQL uses backticks for identifiers (`Customer ID`) and SQL Server uses square brackets ([Customer ID]), SQL Server also accepts double quotes by default; MySQL only does with the ANSI_QUOTES mode on.

#Case sensitivity

• Keywords are never case-sensitive: SELECT, select and SeLeCt are the same. This shelf writes them in lower case; many teams use upper case. Pick one per codebase.

• Unquoted identifiers are folded to one case. PostgreSQL folds to lower case, Snowflake and Oracle to upper case. DuckDB and SQL Server keep the case you wrote but match names case-insensitively.

• Quoted identifiers keep their exact case. If a table was created with "VendorID", PostgreSQL will only find it as "VendorID".

• String comparisons depend on the collation: 'Bronx' = 'bronx' is false in PostgreSQL and DuckDB, true under the default collations of MySQL and SQL Server.

#Comments and semicolons

-- comments to the end of a line; /* ... */ comments a block. A semicolon ends a statement. A single query in a GUI often runs without one, but a script of several statements needs them all, because without the semicolon the parser reads the next select as part of the current statement.

#LIMIT, TOP and FETCH FIRST

Tables have no inherent order. limit 10 without order by returns any 10 rows the engine finds first, and that can change between runs, versions and machines. Use it for a quick peek, never for an answer.

#Worked example: load and inspect 1.37 million taxi trips

New York's Taxi and Limousine Commission (TLC) publishes every yellow cab trip as open data (TLC trip records, free to use under the NYC Open Data terms). We use January 2021, mirrored as compressed CSV on GitHub by the DataTalksClub course, because it downloads anywhere. Download it and the zone lookup:

curl -LO https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz
curl -LO https://github.com/DataTalksClub/nyc-tlc-data/releases/download/misc/taxi_zone_lookup.csv

Open a DuckDB database file (it is created if it does not exist) and load both files into tables:

-- run with: duckdb taxi.duckdb
create or replace table trips as select * from read_csv('yellow_tripdata_2021-01.csv.gz');
create or replace table zones as select * from read_csv('taxi_zone_lookup.csv');
select count(*) from trips;

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      1369765 │
└──────────────┘

Every later lesson on this shelf uses this taxi.duckdb file. If you prefer the newest month, the TLC publishes Parquet files you can read by URL (select * from 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'); the queries work the same, only the numbers change.

#Step 1: what columns and types are there?

describe zones;

┌──────────────────────┐
│        zones         │
│                      │
│ LocationID   bigint  │
│ Borough      varchar │
│ Zone         varchar │
│ service_zone varchar │
└──────────────────────┘

describe trips lists 18 columns. The portable way to see the same thing, which works in PostgreSQL, MySQL, SQL Server, Snowflake and DuckDB, is the information_schema:

select table_name, column_name, data_type
from information_schema.columns
where table_name = 'zones';

┌────────────┬──────────────┬───────────┐
│ table_name │ column_name  │ data_type │
│  varchar   │   varchar    │  varchar  │
├────────────┼──────────────┼───────────┤
│ zones      │ LocationID   │ BIGINT    │
│ zones      │ Borough      │ VARCHAR   │
│ zones      │ Zone         │ VARCHAR   │
│ zones      │ service_zone │ VARCHAR   │
└────────────┴──────────────┴───────────┘

#Step 2: profile the table with SUMMARIZE

DuckDB's summarize computes min, max, approximate distinct count, average, quartiles and the share of NULLs for every column in one pass. You can query its output like a table:

select column_name, column_type, null_percentage
from (summarize trips)
where null_percentage > 0;

┌────────────────────┬─────────────┬─────────────────┐
│    column_name     │ column_type │ null_percentage │
│      varchar       │   varchar   │  decimal(9,2)   │
├────────────────────┼─────────────┼─────────────────┤
│ VendorID           │ BIGINT      │            7.18 │
│ passenger_count    │ BIGINT      │            7.18 │
│ RatecodeID         │ BIGINT      │            7.18 │
│ store_and_fwd_flag │ VARCHAR     │            7.18 │
│ payment_type       │ BIGINT      │            7.18 │
└────────────────────┴─────────────┴─────────────────┘

Five columns are missing on exactly the same 7.18% of rows. That pattern (several columns empty together) usually means a whole class of records arrived through a different path, without the fields the meter normally fills in. Note it now; the NULL handling lesson decides what to do about it.

The full summarize trips output also shows fare_amount ranging from -490.0 to 6960.5 and tpep_pickup_datetime starting at 2008-12-31. Neither belongs in a January 2021 taxi file. You found two data quality issues before writing a single real query.

#Step 3: answer the three questions

How many trips, and over what period?

select count(*) as trips,
       min(tpep_pickup_datetime) as first_pickup,
       max(tpep_pickup_datetime) as last_pickup
from trips;

┌─────────┬─────────────────────┬─────────────────────┐
│  trips  │    first_pickup     │     last_pickup     │
│  int64  │      timestamp      │      timestamp      │
├─────────┼─────────────────────┼─────────────────────┤
│ 1369765 │ 2008-12-31 23:05:14 │ 2021-02-22 16:52:16 │
└─────────┴─────────────────────┴─────────────────────┘

select count(*) filter (where tpep_pickup_datetime <  '2021-01-01'
                           or tpep_pickup_datetime >= '2021-02-01') as outside_january
from trips;

┌─────────────────┐
│ outside_january │
│      int64      │
├─────────────────┤
│              24 │
└─────────────────┘

So: 1,369,765 rows, of which 24 have pickup times outside January 2021. When someone asks "how many trips in January", the honest answer is 1,369,741, with a note about the 24 bad clock readings.

Which columns hold money? Read the names against the TLC data dictionary: fare_amount, extra, mta_tax, tip_amount, tolls_amount, improvement_surcharge, congestion_surcharge and total_amount. All are DOUBLE, a floating-point type. That is acceptable for analysis, and the data types lesson shows why a billing system would store them as DECIMAL instead.

Which columns have NULLs? The five listed by summarize, each with 98,352 missing values:

select count(*) filter (where VendorID is null) as no_vendor from trips;

┌───────────┐
│ no_vendor │
│   int64   │
├───────────┤
│     98352 │
└───────────┘

#Step 4: the same query in PostgreSQL

Load the file into the PostgreSQL container. Unlike DuckDB, PostgreSQL cannot query a CSV file directly: you create the table with explicit types and copy the data in.

create table trips (
  "VendorID" bigint, tpep_pickup_datetime timestamp, tpep_dropoff_datetime timestamp,
  passenger_count bigint, trip_distance double precision, "RatecodeID" bigint,
  store_and_fwd_flag text, "PULocationID" bigint, "DOLocationID" bigint,
  payment_type bigint, fare_amount double precision, extra double precision,
  mta_tax double precision, tip_amount double precision, tolls_amount double precision,
  improvement_surcharge double precision, total_amount double precision,
  congestion_surcharge double precision
);
\copy trips from program 'gunzip -c yellow_tripdata_2021-01.csv.gz' with (format csv, header true)

CREATE TABLE
COPY 1369765

Now run a count per vendor, first the way you would in DuckDB:

select VendorID from trips limit 1;

ERROR:  column "vendorid" does not exist
LINE 1: select VendorID from trips limit 1;
               ^
HINT:  Perhaps you meant to reference the column "trips.VendorID".

PostgreSQL folded the unquoted VendorID to vendorid, but the table was created with the quoted, mixed-case name. Quote it:

select "VendorID", count(*) as trips from trips group by "VendorID" order by "VendorID";

 VendorID | trips
----------+--------
        1 | 410762
        2 | 860651
          |  98352
(3 rows)

The numbers match DuckDB exactly. Notice that psql prints NULL as an empty cell, where DuckDB prints NULL.

The second difference is the alias rule. DuckDB is lenient and lets you filter on a select alias; PostgreSQL follows the standard:

select fare_amount + tip_amount as fare_plus_tip
from trips
where fare_plus_tip > 500
limit 3;

ERROR:  column "fare_plus_tip" does not exist
LINE 1: ...nt + tip_amount as fare_plus_tip from trips where fare_plus_...
                                                             ^

The same query succeeds in DuckDB. Write the portable version (where fare_amount + tip_amount > 500) and it runs everywhere.

A third difference, which the data types lesson returns to: select 7/2 gives 3 in PostgreSQL (integer division) and 3.5 in DuckDB.

#Step 5: row limiting in SQL Server

The same zone lookup, loaded into SQL Server 2022 in Docker:

select top 3 LocationID, Borough, Zone from zones order by LocationID;
select LocationID, Borough, Zone from zones order by LocationID
offset 0 rows fetch first 3 rows only;

LocationID Borough Zone
---------- ------- ----
1 EWR Newark Airport
2 Queens Jamaica Bay
3 Bronx Allerton/Pelham Gardens
 
(3 rows affected)

Both statements return the same three rows. select ... limit 3 fails in SQL Server with Incorrect syntax near 'limit'.

#Comments and the missing semicolon

select count(*) from trips
select count(*) from zones;

Parser Error: syntax error at or near "select"
 
LINE 2: select count(*) from zones;
        ^

The parser saw one long statement. Add the semicolon after trips and both run.

#Common mistakes

• Querying before inspecting. Running sum(fare_amount) before summarize means you never see the negative fares or the 2008 pickups. Spend the first five minutes on describe, summarize and count(*).

• Single quotes around column names. select 'fare_amount' from trips returns the word fare_amount 1.37 million times. Identifiers take double quotes (or none).

• Creating mixed-case names in PostgreSQL. Once a column is created as "VendorID", every query forever needs the quotes. When you design tables yourself, use snake_case names (vendor_id) and never quote them.

• Trusting limit to give "the first" rows. Without order by, there is no first. Add order by whenever the rows you get matter.

• Assuming the dialect. Before a test, ask which database it uses. top vs limit and date functions are where people lose time.

#Interview angle

#Practice task

1. Download the two files and build taxi.duckdb as shown.

2. Run summarize trips and write down three columns whose minimum or maximum looks impossible, with one sentence each on why.

3. Count trips whose tpep_dropoff_datetime is before tpep_pickup_datetime.

4. Count how many zones each borough has in zones.

5. Repeat task 4 in PostgreSQL in Docker (load taxi_zone_lookup.csv with \copy) and note two syntax differences you hit.

Done when you have four numbers and a short list of differences.

-- 3. dropoff before pickup
select count(*) from trips where tpep_dropoff_datetime < tpep_pickup_datetime;

-- 4. zones per borough
select Borough, count(*) as zones from zones group by Borough order by zones desc;

#Further reading

• DuckDB: SUMMARIZE: what every column of the profile means.

• PostgreSQL tutorial: the SQL language: the official, short introduction.

• PostgreSQL: identifiers and key words: the exact case-folding rules.

• TLC yellow taxi data dictionary: what every column and code means.