<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Foundations
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 1 of 54 14 minBeginner

What is a database? SQL Server, SSMS and the BuildMart sample

What a relational database is, how SQL Server organises one, how to install the free tools, and how to create the BuildMart database that every lesson uses.

A database is an organised store of data that many people and programs can read and change at the same time, safely. A relational database keeps that data in tables: named grids where every row is one thing (one product, one customer, one order) and every column is one fact about it (its name, its price, the date it was added).

SQL Server is Microsoft’s relational database engine. You talk to it in SQL (Structured Query Language), the standard language for relational databases. SQL Server’s version of SQL is called T-SQL (Transact-SQL): standard SQL plus extras such as variables, loops and error handling.

One SQL Server holds many databases, each database groups its tables into schemas, and each table holds rows. Click through the levels below. The tables are the ones you will create in a moment.

Interactive

Where your data lives

master tempdb
Table

Where the data actually lives: named columns, each with a data type, and any number of rows. Pick a table below to see its columns and first rows.

Three-part name, on server localhost

BuildMart.dbo.Products

dbo.Products: One row per thing the shop sells. CategoryId points at a row in Categories.
ProductId intName nvarchar(60)CategoryId intPrice decimal(10,2)Stock intUnit nvarchar(10)AddedOn date
ProductIdNameCategoryIdPriceStockUnitAddedOn
11Claw hammer118.5040each2023-01-15
22Cordless drill189.0012each2023-02-01
33Tape measure 8 m19.7565each2023-01-15

Showing 3 of 12 rows.

dbo.Products has 7 columns and 12 rows. Every row has the same columns; each column holds one type of value.

Why not just use a spreadsheet?

A spreadsheet is also a grid of rows and columns, and for a short list it is fine. A database earns its keep once data is shared, large or important:

SpreadsheetDatabase
TypesAny cell can hold anything: a price column can contain “TBC”Every column has a type; SQL Server rejects a price of “TBC”
Many usersOne person edits at a time, or copies drift apartHundreds of users and programs read and write at once
RelationshipsCopy the customer’s name onto every order rowStore the customer once; orders point at it by id
SizeSlows down at a few hundred thousand rowsBillions of rows, with indexes to find any of them fast
SafetyA crash half-way through a change can leave a messTransactions: a change happens completely or not at all
QuestionsFilters and formulas, by handOne SQL query, which a program can run too

Install the tools (free)

  1. SQL Server 2022 Developer (full features, free for learning and development) or Express (free, smaller). Download either from Microsoft’s SQL Server downloads page and choose the Basic installation.
  2. SQL Server Management Studio (SSMS), the main tool for writing queries and managing databases. VS Code with the mssql extension works too.
  3. Open SSMS. In Connect to Server, type localhost as the server name (or localhost\SQLEXPRESS for Express), choose Windows Authentication, tick Trust server certificate, and click Connect.
  4. Click New Query. You now have an editor connected to your server. Type T-SQL and press F5 (or click Execute) to run it.

Create the BuildMart database

Every lesson in this course queries one small database: BuildMart, a shop that sells building supplies. It has categories, products, customers, orders and order lines. Copy the script below into a new query window and run all of it with F5. You do not need to understand it yet: the Creating and changing data module explains every line.

BuildMart.sql
-- BuildMart: the sample database used in every lesson.-- Run the whole script in SSMS (F5). Run it again at any time to reset the data.IF DB_ID(N'BuildMart') IS NULL    CREATE DATABASE BuildMart;GOUSE BuildMart;GODROP TABLE IF EXISTS dbo.OrderItems, dbo.Orders, dbo.Customers, dbo.Products, dbo.Categories;GOCREATE TABLE dbo.Categories (    CategoryId int          NOT NULL PRIMARY KEY,    Name       nvarchar(40) NOT NULL); CREATE TABLE dbo.Products (    ProductId  int           NOT NULL IDENTITY PRIMARY KEY,    Name       nvarchar(60)  NOT NULL,    CategoryId int           NULL REFERENCES dbo.Categories (CategoryId),    Price      decimal(10,2) NOT NULL,    Stock      int           NOT NULL,    Unit       nvarchar(10)  NOT NULL,    AddedOn    date          NOT NULL); CREATE TABLE dbo.Customers (    CustomerId int           NOT NULL IDENTITY PRIMARY KEY,    FirstName  nvarchar(40)  NOT NULL,    LastName   nvarchar(40)  NOT NULL,    City       nvarchar(40)  NULL,    Email      nvarchar(100) NULL,    Phone      varchar(20)   NULL,    JoinedOn   date          NOT NULL); CREATE TABLE dbo.Orders (    OrderId    int         NOT NULL IDENTITY PRIMARY KEY,    CustomerId int         NOT NULL REFERENCES dbo.Customers (CustomerId),    OrderDate  date        NOT NULL,    Status     varchar(10) NOT NULL); CREATE TABLE dbo.OrderItems (    OrderId   int           NOT NULL REFERENCES dbo.Orders (OrderId),    ProductId int           NOT NULL REFERENCES dbo.Products (ProductId),    Quantity  int           NOT NULL,    UnitPrice decimal(10,2) NOT NULL,    PRIMARY KEY (OrderId, ProductId));GOINSERT INTO dbo.Categories (CategoryId, Name) VALUES    (1, N'Tools'), (2, N'Building'), (3, N'Timber'), (4, N'Fixings'),    (5, N'Paint'), (6, N'Safety'), (7, N'Plumbing'); INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn) VALUES    (N'Claw hammer',          1, 18.50,  40, N'each', '2023-01-15'),    (N'Cordless drill',       1, 89.00,  12, N'each', '2023-02-01'),    (N'Tape measure 8 m',     1,  9.75,  65, N'each', '2023-01-15'),    (N'Cement 25 kg',         2,  7.20, 300, N'bag',  '2023-03-10'),    (N'Sand 25 kg',           2,  4.10, 220, N'bag',  '2023-03-10'),    (N'Timber 2x4 2.4 m',     3,  6.80, 500, N'each', '2023-04-05'),    (N'Plywood sheet 18 mm',  3, 42.00,  80, N'sheet','2023-04-05'),    (N'Wood screws (200)',    4, 11.90, 150, N'box',  '2023-05-20'),    (N'Wall plugs (100)',     4,  4.50,   0, N'box',  '2023-05-20'),    (N'Masonry paint 10 L',   5, 54.00,  25, N'tin',  '2023-06-12'),    (N'Paint roller set',     5, 12.50,  30, N'each', '2023-06-12'),    (N'Safety goggles',       6,  6.80,   0, N'each', '2023-07-01'); INSERT INTO dbo.Customers (FirstName, LastName, City, Email, Phone, JoinedOn) VALUES    (N'Asha',  N'Patel',  N'Leeds',     N'asha@example.com',  '0113 496 0001', '2023-03-02'),    (N'Ben',   N'Carter', N'York',      NULL,                 '01904 555 010', '2023-05-19'),    (N'Chloe', N'Evans',  N'Leeds',     N'chloe@example.com', NULL,            '2023-06-07'),    (N'Dev',   N'Singh',  N'Bradford',  N'dev@example.com',   '01274 555 220', '2023-08-23'),    (N'Ella',  N'Brown',  NULL,         N'ella@example.com',  NULL,            '2023-09-14'),    (N'Farid', N'Khan',   N'York',      N'farid@example.com', '01904 555 777', '2023-11-30'),    (N'Grace', N'Lee',    N'Leeds',     NULL,                 NULL,            '2024-01-08'),    (N'Zoë',   N'Müller', N'Harrogate', N'zoe@example.com',   '01423 555 123', '2024-02-15'); INSERT INTO dbo.Orders (CustomerId, OrderDate, Status) VALUES    (1, '2024-02-03', 'Shipped'), (2, '2024-02-10', 'Shipped'), (1, '2024-03-01', 'Shipped'),    (4, '2024-03-15', 'Shipped'), (6, '2024-04-02', 'Shipped'), (3, '2024-04-20', 'Cancelled'),    (4, '2024-05-05', 'Shipped'), (8, '2024-05-18', 'Pending'), (2, '2024-06-01', 'Pending'),    (6, '2024-06-12', 'Shipped'); INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice) VALUES    (1, 1, 1, 18.50), (1, 3, 2, 9.75),    (2, 4, 10, 7.20), (2, 5, 10, 4.10),    (3, 2, 1, 89.00), (3, 8, 2, 11.90),    (4, 6, 40, 6.80), (4, 7, 6, 42.00),    (5, 10, 2, 54.00), (5, 11, 1, 12.50),    (6, 12, 2, 6.80),    (7, 4, 20, 7.00), (7, 8, 5, 11.90),    (8, 7, 2, 42.00), (8, 1, 1, 18.50),    (9, 9, 4, 4.50),    (10, 3, 1, 9.75), (10, 6, 12, 6.80);GOSELECT 'BuildMart is ready' AS Result;
Output
Result
------------------
BuildMart is ready

Your first query

With BuildMart created, ask it a question. Which products cost more than 40?

Query.sql
USE BuildMart; SELECT Name, PriceFROM dbo.ProductsWHERE Price > 40;
Output
Name                 Price
-------------------  -----
Cordless drill       89.00
Plywood sheet 18 mm  42.00
Masonry paint 10 L   54.00

Read it almost as English: select the name and price from the products table where the price is over 40. Notice what you did not say: which file to open, how to loop over rows, or how to test each one. SQL is declarative. You describe the result you want, and SQL Server works out how to produce it.

USE BuildMart; makes BuildMart the current database for the rest of the window. Every later sample assumes you have run it once. The name dbo.Products is the schema (dbo) and the table (Products).

What SQL Server does with your query

  1. Parse: check the text is valid T-SQL. A typo such as SELEC stops here.
  2. Bind: look up every name. A table or column that does not exist stops here.
  3. Optimise: choose a plan: which index to use, in what order to read tables. For big queries this is where the cleverness is.
  4. Execute: run the plan and stream the rows back to SSMS, which shows them in the Results tab.

The kinds of SQL statement

FamilyStatementsWhat they doWhere in this course
QuerySELECTRead dataFoundations onwards
Data manipulation (DML)INSERT, UPDATE, DELETE, MERGEChange the rows in tablesCreating and changing data
Data definition (DDL)CREATE, ALTER, DROPCreate and change tables and other objectsCreating and changing data
Access control (DCL)GRANT, REVOKE, DENYDecide who may do whatMaster level
TransactionsBEGIN TRAN, COMMIT, ROLLBACKGroup changes so they succeed or fail togetherMaster level

The database describes itself

SQL Server keeps information about every object in system views. sys.tables lists the tables in the current database, so you can query it like any other table:

Query.sql
SELECT nameFROM sys.tablesORDER BY name;
Output
name
----------
Categories
Customers
OrderItems
Orders
Products
Quick check

What is T-SQL?

Quick check

In dbo.Products, what is dbo?

Quick check

SQL is called declarative because…

Practice

Write a query that returns the FirstName, LastName and City of every customer in BuildMart.

Show a hint

List the three columns after SELECT, separated by commas, and read FROM dbo.Customers. No WHERE is needed.

Show the solution
Solution.sql
SELECT FirstName, LastName, CityFROM dbo.Customers;
Output
FirstName  LastName  City
---------  --------  ---------
Asha       Patel     Leeds
Ben        Carter    York
Chloe      Evans     Leeds
Dev        Singh     Bradford
Ella       Brown     NULL
Farid      Khan      York
Grace      Lee       Leeds
Zoë        Müller    Harrogate

Remember

  • A relational database stores data in tables of rows and columns; every column has a type.
  • SQL Server holds databases, databases hold schemas, schemas hold tables: BuildMart.dbo.Products.
  • T-SQL is SQL Server’s dialect of SQL. SSMS is the main tool for writing and running it; F5 runs the window or the selection.
  • SQL is declarative: you describe the result, the optimiser picks the plan.
  • Run the BuildMart script once; every lesson’s queries run against it, and running it again resets the data.
0 of 54 lessons completed