SELECT & basic queries

Ok so this is where everything starts. Every query you'll ever write has a SELECT in it somewhere, it's basically you telling the database "hey, give me these columns from this table".

all examples use the tables from sample-data.sql, run that once and you're good.

the basic shape

SELECT column1, column2
FROM table_name;

That's it. SELECT is what you want, FROM is where it lives. The semicolon at the end just means "query over".

get everything

SELECT * FROM customers;

* means all columns. It's fine when you're just poking around a table to see what's inside, but don't put it in real code. See, if someone adds 20 columns to that table tomorrow your query suddenly pulls all of them, slower and messier.

get only what you need

SELECT first_name, email
FROM customers;

first_name	email
Aman	aman@mail.com
Priya	priya@mail.com
...	...

The order you write the columns in is the order you get them back, so SELECT email, first_name flips them.

renaming columns (aliases)

SELECT
  first_name AS name,
  email      AS contact
FROM customers;

AS just changes the name in the output, the actual table doesn't change. You'll use this a lot once you start doing calculations because otherwise the column comes out named something ugly like ?column?.

If your alias has a space in it, wrap it in double quotes like AS "full name". Honestly just use underscores, saves headache.

calculated columns

You're not stuck with what's in the table, you can do math or join text right inside SELECT.

SELECT
  product_name,
  price,
  price * 0.18 AS gst,
  price * 1.18 AS price_with_gst
FROM products;

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

|| sticks strings together in postgres. In mysql you'd use CONCAT(first_name, ' ', last_name), which also works in postgres btw.

SELECT without a table

Kinda random but useful for quick testing.

SELECT 2 + 2;
SELECT NOW();

the order sql actually runs things in

This one's important and nobody tells you early. You write SELECT first but the database runs FROM first.

FROM  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  ORDER BY  ->  LIMIT

That's why you can't use an alias you made in SELECT inside WHERE, WHERE already ran before the alias existed. Keep this in mind, it'll explain like half the errors you hit later.

stuff that trips people up

• forgetting the comma between columns, SELECT first_name email doesn't error, it renames first_name to email. Fun one to debug.
• putting a comma after the last column, SELECT first_name, email, FROM ... errors out.
• using SELECT * everywhere out of habit.
• single quotes are for text values ('Delhi'), double quotes are for names ("full name"). Mixing them up gives weird errors.

practice

Questions with answers are in queries.sql. Try them before peeking.

-- SELECT & basic queries
-- run sample-data.sql first

-- examples ---------------------------------------------------------

-- everything from customers
SELECT * FROM customers;

-- just names and emails
SELECT first_name, email FROM customers;

-- renaming columns
SELECT first_name AS name, email AS contact FROM customers;

-- full name in one column
SELECT first_name || ' ' || last_name AS full_name FROM customers;

-- price with 18% gst
SELECT product_name, price, price * 1.18 AS price_with_gst FROM products;


-- practice ---------------------------------------------------------
-- try these on your own first, answers are at the bottom

-- 1. get product_name and price from products
-- 2. get every column from orders
-- 3. get order_id and amount, but call amount "order_value"
-- 4. show each product with its price after a 10% discount, name that column discounted_price
-- 5. show customers as "Sharma, Aman" in one column called display_name
-- 6. show each employee's name and their yearly salary divided into monthly, call it monthly_salary


-- answers ----------------------------------------------------------

-- 1
SELECT product_name, price FROM products;

-- 2
SELECT * FROM orders;

-- 3
SELECT order_id, amount AS order_value FROM orders;

-- 4
SELECT product_name, price, price * 0.9 AS discounted_price FROM products;

-- 5
SELECT last_name || ', ' || first_name AS display_name FROM customers;

-- 6
SELECT name, salary / 12 AS monthly_salary FROM employees;
-- heads up, salary is an INT so 250000 / 12 gives 20833 not 20833.33
-- if you want decimals do salary / 12.0