Remember: using the RPostgreSQL library, commands will generally look like:

• dbSendQuery(connection, “your sql statement”) – for commands that DON’T bring back data
• dbGetQuery(connection, “your sql statement”) – for commands that DO bring back data

The examples here go in to the “your sql statement” part

Create a table named “customers” with three columns

create table customers (customer_no integer, first_name text, last_name text)

Delete a table named “customers”

drop table customers

Insert into a table named “customers”

insert into customers (customer_no, first_name, last_name) values (1, 'MC', 'Hammer')

Select all rows from a table named “customers”

select *
from customers

Select filtered rows from a table named “customers” where “first_name” equals a specific value

select *
from customers
where last_name = 'Hammer'

Update values in a table named “customers” where “customer_no” equals a specific value

update customers
set first_name = 'Ralph'
where customer_no = 1

Update values in a table named “customers” for ALL rows (be careful with this!)

update customers
set first_name = 'Ralph'

Select distinct (unique) values of a column (here “order_no”) from the table “orders”

select distinct(order_no)
from orders

Count distinct (unique) values of a column (here “order_no”) from the table “orders”

select count(distinct(order_no))
from orders

Create a new calculated column “total” in a select statement from the table “orders”

select quantity, price, quantity*price total
from orders

A Join (aka “Inner Join”) between two tables “orders” and “products” – An Inner Join will only return records that have a match

select *
from orders
join products on orders.product_no = products.product_no

A Join (aka “Inner Join”) between three tables “customers”, “orders” and “products”

select *
from customers
join orders on customers.customer_no = orders.customer_no
join products on orders.product_no = products.product_no

A Join (aka “Inner Join”) between three tables “customers”, “orders” and “products” filtered by customer “last_name”

select *
from customers
join orders on customers.customer_no = orders.customer_no
join products on orders.product_no = products.product_no
where customers.last_name = 'Hammer'

A Join (aka “Inner Join”) between three tables “customers”, “orders” and “products” filtered by customer “last_name”, using aliases

select *
from customers c
join orders o on c.customer_no = o.customer_no
join products p on o.product_no = p.product_no
where c.last_name = 'Hammer'

A Left Join between two tables “customers” and “orders” – a Left Join will give you all records from the table on the left whether they have a match in the table on the right or not

select *
from customers c
left join orders o on c.customer_no = o.customer_no

Writing the result of a query on “orders” to a new dynamically created table “my_new_table”)

select *
into my_new_table
from orders

Appending the result of a query on “orders” to an already existing table “order_archive”

insert into order_archive (order_no, customer_no, product_no) (
select *
from orders
)