Advanced SQL for Uber Ops
SQL Fundamentals
Talking to Databases
Imagine a library filled with perfectly organized filing cabinets. Each cabinet is for a specific category, like 'Customers' or 'Products'. Inside each cabinet, you have folders, and each folder represents a single customer or product. On each folder, you have neatly labeled fields: Name, Address, ID Number. This is the basic idea of a relational database.
Instead of filing cabinets, databases use tables. Each table holds a specific type of information. The rows in a table are like the individual folders (a specific customer), and the columns are the labeled fields (Name, Address). The 'relational' part just means the tables can be linked together. For example, a customer's ID can link them to all the orders they've placed in an 'Orders' table.
To get information out of these tables, you need to speak their language. That language is SQL, which stands for Structured Query Language. It’s a surprisingly straightforward language designed for one main purpose: managing and querying data in a relational database. When you write an SQL query, you're not telling the database how to find the information; you're just describing what you want. The database figures out the best way to get it for you.
SQL lets you ask questions of your data. Think of it as a universal translator for databases.
Asking the Right Questions
The most common thing you'll do with SQL is retrieve data. The command for this is SELECT. Let’s say we have a Customers table. If you want to see everything in it, you'd use an asterisk (*), which is a wildcard for 'all columns'.
SELECT * FROM Customers;
This query says, "Select all columns from the Customers table." Simple, right? But usually, you don't need every single piece of information. To be more specific, you can list the exact columns you want.
SELECT Name, City FROM Customers;
Now we're only asking for the Name and City of each customer. But what if we only want customers from a specific city? We can filter the results using the WHERE clause. This is how you narrow down your query to find exactly what you need.
SELECT Name, City FROM Customers
WHERE City = 'London';
This query will only return the names of customers who live in London. Notice that 'London' is in single quotes. Text values, often called strings, are always enclosed in quotes.
Data Types and Sorting
Every column in a database is designed to hold a specific type of data. This is part of the database's schema, or its blueprint. You can't store a customer's name in a column meant for numbers. This strictness ensures data integrity, meaning the information is reliable and consistent.
| Data Type | Description | Example |
|---|---|---|
INT | Integer (a whole number) | 101 |
VARCHAR(n) | Variable-length text (up to n characters) | 'John Smith' |
DECIMAL(p, s) | A number with a fixed decimal point | 45.99 |
DATE | A date value | '2023-10-26' |
Once you've retrieved your data, you might want to see it in a specific order. The ORDER BY clause lets you sort the results. By default, it sorts in ascending order (ASC), but you can specify descending order with DESC.
SELECT Name, City FROM Customers
WHERE City = 'London'
ORDER BY Name ASC;
This will give us a list of all customers from London, sorted alphabetically by name.
Summarizing Your Data
Sometimes you don't need a list of individual records. Instead, you need a summary. SQL has aggregate functions for this. They take in many values and return a single one.
For instance, to find out how many customers you have, you can use COUNT().
SELECT COUNT(*) FROM Customers;
This query returns a single number: the total number of rows in the Customers table. You can also use COUNT() with a WHERE clause to count a subset of your data, like the number of customers in London.
Other useful aggregate functions include SUM() and AVG(), which work on numerical columns. If we had an Orders table with an Amount column, we could find the total value of all orders or the average order amount.
-- Get the total value of all sales
SELECT SUM(Amount) FROM Orders;
-- Get the average sale amount
SELECT AVG(Amount) FROM Orders;
These basic commands are the building blocks of almost everything you'll do in SQL. By combining SELECT, WHERE, ORDER BY, and aggregate functions, you can start to pull powerful insights from your data.
In the analogy of a relational database as a filing cabinet, what does a single row within a table represent?
Which SQL query would correctly retrieve just the Name and City for all customers who live in 'Paris'?
Mastering these fundamentals is the first step toward becoming proficient with databases. With a solid foundation, you can start exploring more complex queries to answer even more interesting questions.