The Database
The database is where an app keeps its data so it can be found again in an instant, and in this course you'll design SQL Server databases and question them in T-SQL.
Novice · Database · Web Development · Updated
After this lesson you can
- Describe what a database does for an app
- Explain why an app keeps its data in a database instead of in its own memory
Key takeaways
- A database keeps an app's data organized so it can be saved, found and changed quickly, and it is still there after the app closes.
- A relational database such as SQL Server keeps data in tables of rows and columns, and the tables link to each other.
- SQL is the language you use to ask a database questions and change its data. SQL Server's version is called T-SQL.
- A database developer designs the tables, writes the questions, and keeps the data correct, safe and fast to reach.
Your bank remembers every payment you've ever made. A streaming service remembers where you stopped watching, on every device. A game remembers your level, your coins and your friends. None of that memory lives in the app on your phone: it lives in a database. Every app that remembers anything has one, and it's often the most valuable thing the business owns.
What a database is
A database is an organized store of data that an app can save to, search and change, and that keeps the data safe after the app closes, the server restarts or the power goes out.
Why not keep the data in the back end's own memory? Because a program's memory is like a whiteboard: it's fast, but it's wiped when the program stops. A database is like a filing cabinet with a perfect librarian. Everything is put away in its place, it's still there tomorrow, and you can ask for exactly what you need: every order from last Friday with extra cheese.
Tables, rows and columns
The kind of database you'll learn in this course is a relational database. It keeps data in tables, which look a lot like spreadsheets:
| PizzaId | Customer | Size | Crust | Price | OrderedAt |
|---|---|---|---|---|---|
| 1 | Maria | Large | Classic | 15.50 | Fri 6:05 PM |
| 2 | Maria | Small | Thin | 10.00 | Fri 6:05 PM |
| 3 | Sam | Medium | Stuffed | 14.50 | Fri 6:40 PM |
| 4 | Sam | Medium | Stuffed | 14.50 | Fri 6:40 PM |
| 5 | Maria | Large | Thin | 16.00 | Sat 12:15 PM |
Each row is one thing (one pizza), and each column is one fact about it (its size, its price). This table is kept simple, with the customer's name written on each pizza. A real database links its tables instead: Build-It Pizzeria keeps its customers in one table, its pizzas in another and its toppings in a third, and connects them, so Maria's name is stored once even though she orders every week. You'll design exactly this database yourself in Database Design in SQL Server, in Module 2.
Asking questions in SQL
To get data out of a database, you ask it a question, called a query, in a language called SQL (Structured Query Language, often said "sequel"). The database system you'll use is SQL Server, and its version of SQL is called T-SQL. Here's a real query you'll be able to run in Module 1, against Northwind, a sample store's database that comes with the course: show me our five most expensive products.
SELECT TOP 5 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;
Read it out loud and it's almost English: select the top five product names and prices, from the products, ordered by price, highest first. A database can answer a question like this across millions of rows in less time than it takes to blink.
What a database can do
The database is the memory of the business, and good questions turn that memory into answers:
- Remember everything: every customer, order and payment, safe after a restart.
- Find one thing among millions in a fraction of a second: your order, out of every order the pizzeria has ever taken.
- Answer the owner's questions: Which topping sells best on Fridays? Which customers haven't ordered in a month? In the pizzeria's first orders, the Friday answer is pepperoni: it's on three pizzas, Maria's large and Sam's two mediums.
- Keep the data right: never an order for a pizza size that doesn't exist, never two customers with the same ID.
- Handle many people at once: a hundred orders saved in the same second, none of them lost.
What a database developer does
A database developer designs how the data is stored and writes the queries that read and change it. On a typical day they might add a table for a new feature (favorite pizzas), write a report the owner asked for, find out why a query takes ten seconds and make it take a tenth of one, or make sure a mistake can't leave half an order saved. Some teams also have database administrators (DBAs), who keep the database servers running, backed up and secure. In this course you'll meet SQL Server in Module 1, learn joins, views, stored procedures and database design in Module 2, and connect C# to the database with ADO.NET, Dapper and Entity Framework Core along the way.
Try it. Think of an app you use and the "tables" it must keep. A music app might keep songs, artists, playlists and plays. Write down three tables, and two columns each one would need.
Check yourself
2 questions to try on the lesson page.
So what
The database is the app's memory, and learning to design it and question it is one of the most useful skills a developer can have.