SQL Fundamental

We’ll learn table creation, inserts, selects, updates, deletes, aggregations, indexes, joins, and constraints. Along the way, we’ll model real-world problems.

https://macpostgresclient.com/

• 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

in PostgreSQL

Chapter 8. Data Types

• 2 Insert Data with Insert

When a table is created, it contains no data from the start. We will add data by using the insert command.

• 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 drop command.

Write an SQL query to report the names of the customer that are not referred by the customer with id = 2

SELECT name
FROM customer
WHERE referee_id != 2
OR referee_id IS NULL

How to TEST for NULL Values?

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 Customers
FROM Customers
WHERE id NOT IN (
SELECT customerId FROM Orders
)

The NOT operator displays a record if the condition(s) is NOT TRUE.

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

1699. Number of Calls Between Two Persons

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.

SELECT 
  from_id AS person1, to_id AS person2,
  COUNT(1) AS call_count, SUM(duration) AS total_duration
FROM calls
GROUP BY LEAST(from_id, to_id), GREATEST(from_id, to_id)

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

The COUNT() function returns the number of rows that matches a specified criterion.