Week 1 · SQL Basics / Day 1
Goal: get AdventureWorks running, then read data with SELECT: every column, chosen columns, DISTINCT, arithmetic, aliases and string concatenation.
Read: W3Schools: SQL Intro, SQL Syntax, SQL Select, SQL Select Distinct. Create DB and Create Table are skipped for now and come back on Day 7.
The practice database is AdventureWorks2022, Microsoft's sample database for a bicycle company. It was restored on localhost\SQLEXPRESS from the official backup
(install guide).
Table names have two parts, Schema.Table, for example Production.Product. The schema must be included in every query.
tinyint smallint int bigint whole numbers of growing sizedecimal / numeric exact decimalsfloat / real approximate decimalsmoney / smallmoney currency
char fixed lengthvarchar variable lengthnchar / nvarchar Unicode versions, for any language
date timedatetime smalldatetimedatetime2 more precisedatetimeoffset includes the time zone
Rules that keep data valid: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
SELECT TOP 10 * FROM Production.Product;SELECT * means 'give me every column'. Production.Product is the table: Production is the schema (a folder of related tables) and Product is the table name. TOP 10 limits the output to the first 10 rows so the grid stays readable; without it you get all 504 products. The row count appears at the bottom right of the SSMS status bar.
SELECT * FROM Sales.Customer;The same SELECT * pattern on a different schema. Sales.Customer has 19,820 rows. Notice PersonID is NULL for many rows: those customers are stores, not individual people. SELECT * is fine for exploring a table, but in real queries you should list the columns you need so the query is faster and does not break when a column is added.
SELECT * FROM Sales.SalesTerritory;A small lookup table with 10 rows, one per sales territory. Small tables like this are good for getting a feel for the data: each row is a region (Northwest, Canada, France...) with its year-to-date sales figures.
SELECT FirstName, LastName FROM Person.Person;Instead of *, list the exact columns you want, separated by commas. The query returns only those two columns for all 19,972 people. This is the normal way to write a SELECT: you read less data and the result is easier to work with.
SELECT Name, ListPrice FROM Production.Product;Name comes first, ListPrice second, because that is the order written in the SELECT list.
SELECT ListPrice, Name FROM Production.Product;Swapping the column names swaps the column order in the result. The order of columns in the output is always decided by your SELECT list, never by how the table was created.
SELECT DISTINCT Color FROM Production.Product;DISTINCT removes duplicate rows from the result. 504 products collapse into 10 unique colours. One of the rows is NULL: products with no colour are grouped together and NULL is treated as one distinct value.
Returns a NULL row. NULL counts as one distinct value.
SELECT DISTINCT Class FROM Production.Product;The same idea on the Class column: only 4 values come back (NULL, H, L, M). H, M and L stand for High, Medium and Low class products.
SELECT DISTINCT Color, Class FROM Production.Product;DISTINCT works on the whole row, not on each column separately. With two columns you get one row per unique (Color, Class) pair. Red can appear with class H, M and L, so Red alone becomes several rows. Think of SQL gluing the values together like 'Red|L', 'Red|M', 'Red|H' and removing duplicates of those. That is why this returns more rows than DISTINCT Color or DISTINCT Class alone.
DISTINCT works on the whole row you select, so two columns give one row per unique pair.
SELECT COUNT(*) FROM (SELECT DISTINCT Color FROM Production.Product) t;A check on the numbers. The inner query (a subquery) returns the distinct colours; the outer COUNT(*) counts those rows. The 't' after the brackets is an alias: SQL Server requires a name for a subquery used in FROM.
SELECT COUNT(*) FROM (SELECT DISTINCT Class FROM Production.Product) t;Counts the distinct classes in the same way.
SELECT COUNT(*) FROM (SELECT DISTINCT Color, Class FROM Production.Product) t;Counts the distinct (Color, Class) pairs. Compare the three numbers: the pair count is bigger than either single-column count, which proves DISTINCT works on whole rows.
SELECT COUNT(DISTINCT Color) FROM Production.Product;COUNT(DISTINCT Color) returns 9, while SELECT DISTINCT Color returned 10 rows. The difference is NULL: aggregate functions such as COUNT ignore NULL values, but DISTINCT in a SELECT list keeps NULL as one row.
COUNT(DISTINCT) does not count NULL. Compare with the 10 rows from drill 4.
SELECT Name, ListPrice, ListPrice * 1.17 AS PriceWithTax FROM Production.Product;You can do maths in the SELECT list. ListPrice * 1.17 adds 17% tax to every price. AS PriceWithTax gives the calculated column a name (an alias); without it SSMS shows '(No column name)'. Products with ListPrice 0 are components used to build bikes, not items sold to customers.
Some products have ListPrice 0. Those are components, not sold items.
SELECT FirstName + ' ' + LastName AS FullName FROM Person.Person;The + operator joins text together. Here the first name, a space, and the last name become one FullName column.
SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS FullName FROM Person.Person;Adding MiddleName turns many full names into NULL. MiddleName is NULL for lots of people, and with the + operator anything combined with NULL becomes NULL. This is the NULL rule. Day 3 shows the fix: CONCAT() treats NULL as an empty string.
MiddleName is NULL for many people, and anything + NULL is NULL.
SELECT 'Hello' AS Greeting, 2 + 2 AS Answer;In SQL Server a SELECT does not need a FROM clause. This is handy for testing expressions, functions or calculations quickly. It returns exactly one row.
SELECT TOP 3 * FROM Production.Product;Other databases (MySQL, PostgreSQL) use LIMIT 3 at the end of the query. SQL Server uses TOP 3 straight after SELECT instead. Without an ORDER BY the 'top' rows are simply whichever rows SQL Server reads first, so the order is not guaranteed.
SELECT TOP 10 PERCENT * FROM Production.Product;TOP n PERCENT returns a percentage of the rows instead of a fixed number. 10 percent of 504 products is 50.4, which SQL Server rounds up to 51 rows.
Shows 10 percent of the rows.
SELECT * FROM Production.Prodct;Error 208: Invalid object name. The table name is misspelled (Prodct instead of Product), so SQL Server cannot find it. Reading error messages carefully is a key skill: the message tells you exactly which name is wrong.
SELECT Nmae FROM Production.Product;Error 207: Invalid column name. The table exists, but it has no column called Nmae. A 207 error always means a column problem; a 208 error means a table or view problem.
SELECT * FROM Product;Error 208 again, but this time the spelling is right. The schema is missing. Without a schema SQL Server looks in dbo (the default schema), and there is no dbo.Product. In AdventureWorks you must always write Production.Product.
EXEC sp_help 'Production.Product';sp_help is a built-in stored procedure that describes an object. For a table it returns several result sets: the owner and creation date, every column with its data type and whether it allows NULL, the identity column, indexes and constraints. It is the quickest way to learn a table's structure.
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Production' AND TABLE_NAME = 'Product';INFORMATION_SCHEMA views are standard system views that describe the database. COLUMNS has one row per column of every table; the WHERE clause narrows it to Production.Product. This returns the 25 columns and their data types as a normal result you can filter and sort.
SELECT TABLE_SCHEMA, COUNT(*) AS Tables
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA;INFORMATION_SCHEMA.TABLES lists every table and view. GROUP BY TABLE_SCHEMA puts the rows into one group per schema and COUNT(*) counts each group, so you see how many objects each schema holds. GROUP BY is covered properly later in the course.