The column you want to filter on (e.g.The WHERE clause lets you filter the rows that you want without it, your query will return every single row in the table. Each query can only have one source table, but you can join other tables onto it: more on that later. FROMĪ database usually has a bunch of tables, so you need to specify which table you want to pull your data from. We’ll cover that in the “everything else” section. The SELECT part of your query isn’t just where you pick columns, though part of what makes SQL powerful is what you can do with the columns you’re selecting, like adding them together or transforming them. You might hear something like “select star isn’t working” which means that a table is down and not queryable because you can’t select anything from it (i.e. The phrase “select star” is pretty common in conversation among data and engineering teams. If you want to return all columns in a table, you can write SELECT * instead of spelling out all of the individual column names. Tables will usually have more columns than you need for your analysis, so you’ll pick a subset of them in your query. The SELECT keyword designates which columns of data you want in your results. Let’s run through these keywords in a bit more depth: SELECT Every SQL query will start with a SELECT and a FROM, and most of them will use a WHERE. You can probably get the gist of what this is doing: SELECTing the columns that we want FROM the table we want (it’s called “orders”) and filtering for WHERE the order_date is today ( CURRENT_DATE()). This SQL query gets us the order ID, order date, and user ID for all orders that were made today. We work at a DTC floss company named Flossier, and we want to analyze our order volume. Let’s start with a query and work backwards. Here’s what a schema might look like you’ll notice there’s an entry for each column in the table and information about data types.Ī SQL query is just telling the database what you want, but you need to do it in a specific order and format, plus use specific keywords. Poking through schemas are a useful way to better understand the data you’re working with and avoiding confusion down the road. Most of the time you hear the word though, it will be in reference to database structure. A schema usually says something like “this database has these tables in it, each table has these columns, and these tables are related to each other in these ways.”įor reasons beyond me, PostgreSQL - one of the most popular relational databases – uses the word “schema” to refer to something else: a collection of tables. A schema is just a description of the structure of a database. You’ll often hear people (yes, they're people) refer to “schemas” in data related conversations. So if we have a database full of orders that our customers made, one row would represent one order, while a column might be “order type” or “order date.” A row is a single “data point” and a column is a type of data. If you’re querying a database with SQL, chances are that database is organized as a series of tables, each with columns - think of a spreadsheet in Excel. Database schemasīefore you understand how SQL works, you need to understand how data is stored. You’ll usually mess up the first few times before you get it right. It’s sort of like making an order at a restaurant: you need to tell the database exactly what you want, and it will serve up the data you asked for. A SQL query can be as short as a few words, or as long as hundreds of lines. When you write SQL, you’re building what’s called a query: every “piece” of SQL you write will return one set of data. So the specific syntax of how you write SQL depends on which database you’re using. Each database like PostgreSQL or MySQL has its own lil’ flavor of SQL, and that’s what’s more analogous to a language. It’s a standard (like a blueprint) for how to query data, sort of like directions for building a language. SQL stands for Structured Query Language, but that’s a misnomer SQL isn’t actually a programming language like Javascript or Python. SQL is a programming language that lets you pull that data and rearrange it: add things together, group over time, replace dollar signs, you name it. What is SQL, exactly?ĭata in a database is almost never in the format you need it to be in. But that’s mostly for engineers and database admins, so this post will focus on reading data – probably 99% of what you want to do. One very, very important caveat: SQL queries can write data to a database, not just read it.
0 Comments
Leave a Reply. |