We’ll learn table creation, inserts, selects, updates, deletes, aggregations, indexes, joins, and constraints. Along the way, we’ll model real-world problems.
- 1 Create a Postgres Table
The main principle to keep in mind is the
create statement. This tells your database that we will be creating something new. Inside the parentheses, we will define all of the columns within our new table and list the type of data that will live within this column.
Data types in MySQL
MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types
MySQL supports SQL data types in several categories: numeric types, date and time types, string (character and byte)…
Chapter 8. Data Types
has a rich set of native data types available to users. Users can add new types to PostgreSQL using the CREATE TYPE…
- 2 Insert Data with Insert
When a table is created, it contains no data from the start. We will add data by using the
- 3 Filter Data in a Postgres Table with Query Statements
With the data inner table the quickest way to pull it all out is to write
select * from Users;
The select statement has lots of clauses and functions that can be used with it. We can also alias our columns within the query.
Alias is critical to know when working with packages within languages like C#. We need the column name to match the properties of classes.
- 4 Update Data in Table with update
Postgres has a handy
create extension we can bring in that will automatically generate a random
uuid for us.
update Users set user_handle = uuid_generate_v4() where last_name = 'clark';
- 5 Removing Data with Delete, Truncate and Drop
Make sure you have a conditional clause that targets only the rows you want to delete, before running the delete command on your table.
Using truncate is a more performant way to delete everything in the table.
If we are trying to remove the table completely from our database, we use the
Write an SQL query to report the names of the customer that are not referred by the customer with
id = 2
SELECT nameFROM customerWHERE referee_id != 2OR referee_id IS NULL
It’s not possible to test for NULL values with comparison operators, such as =, <, or <>.
We have to use the
IS NULL and
IS NOT NULL operators instead.
Tip: Always use IS NULL to look for NULL values.
Write an SQL query to report all customers who never order anything.
SELECT name CustomersFROM CustomersWHERE id NOT IN (SELECT customerId FROM Orders)
NOT operator displays a record if the condition(s) is NOT TRUE.
IN operator allows you to specify multiple values in a
IN operator is a shorthand for multiple
WHERE column_name IN (SELECT STATEMENT);
Write an SQL query to report the number of calls and the total call duration between each pair of distinct persons (person1, person2) where person1 < person2.
from_id AS person1, to_id AS person2,
COUNT(1) AS call_count, SUM(duration) AS total_duration
GROUP BY LEAST(from_id, to_id), GREATEST(from_id, to_id)
GROUP BY statement is often used with aggregate functions (
AVG()) to group the result-set by one or more columns.
COUNT() function returns the number of rows that matches a specified criterion.