SQL

Part I. Create a database 

 login into Postgres

sudo -u postgres psql

 show databases command to see all databases

\l

to create database shop just enter 

create database shop;

Now I want to see that we really have created the database shop. "shop" must be in the list

\l

Let's continue with the shop database we have just created. For that, we need to connect the shop database 

\c shop

See what is in the database. So far it should be "Did not find any relations" since we did not add anything to it. 

\d

Let's create our first table named CUSTOMER by typing following 

	create table customer(
	id serial primary key,
	name varchar(255),
	phone varchar(30),
	email varchar(255)
	);

You can type it in one line if you like.

Check if the table has been created. Now the list should contain created database.  

\d

Let's see more details about our first just created table CUSTOMER.

\d customer

Now it's time to create table PRODUCT

	create table product(
	id serial primary key,
	name varchar(255),
	description text,
	price integer);

let's see the details of the table and check whether everything is right. 

\d product

So far everything was very simple. But since now things are getting more interesting. 

We want to store photos of products. We want to have many photos of one product. We don't want to have any limitations on the number of photos per product. 

In that case, we need a separate table. This table will bind products and photos. Let's call it PRODUCT_PHOTO

	create table product_photo(
	id serial primary key,
	url varchar(255),
	product_id integer references product(id));

check whether it's right  

\d product_photo

great!

Now we need a cart, which will be associated with a customer. 

	create table cart(
	customer_id integer references customer(id),
	id serial primary key);

A card must be able to have products. We will store it in CART_PRODUCT

	create table cart_product(
	cart_id integer references cart(id),
	product_id integer references product(id));

Let's just see what we've done and enjoy it.  

\d 

Part II. Enter some data

We've worked on structure, now it's time to enter some data to it.  

Insert name, phone and email into table customer. Values of name, phone and email must be  'Ivan', '022' 'emailivan@gmail.com' respectively.

insert into customer(name, phone, email) values('Ivan', '022', 'emailivan@gmail.com');

insert into customer(name, phone, email) values('Petr', '023' , 'emailpetr@gmail.com');

if you didn't get errors, everything should be fine, but we also can see what is in this table now. 

 

SELECT * FROM customer;

That's easy to see our new entries since we have only two. If we had hundreds, it probably would not really handy to check it this way. 

Let's add something to PRODUCT. For that, we need to know the structure of the table. 

 

\d product

insert some entries in likewise we did it for COSTOMER
 

	insert into product (name, description, price) 
	values('iPhone', 'phone', 1200);

	insert into product (name, description, price)
	values('iwatch', 'whatch', 500);

repeat for PRODUCT_PHOTO

	\d product_photo

	insert into product_photo (url, product_id)
	values ('iphon_photo.html', 1);

	SELECT * FROM product_photo;

Part 3. Join tables to make data helpful

Now assume we want to see all product and their photos

  

SELECT * FROM product LEFT JOIN product_photo ON product.id = product_photo.product_id;

 

note, that for iwatch URL is missing since we have no photo for that product. 

 What if we want to see only products that do have photos? There are several ways to get it. Let's consider some of them. 

1. RIGHT JOIN 

  

SELECT * FROM product RIGHT JOIN product_photo ON product.id = product_photo.product_id;

 

2. LEFT JOIN and swap tables

  

SELECT * FROM product_photo LEFT JOIN product ON product.id = product_photo.product_id;

 

3. INNER JOIN  

SELECT * FROM product INNER JOIN product_photo ON product.id = product_photo.product_id;

4. WHERE clause

 

SELECT * 
FROM product LEFT JOIN product_photo ON product.id = product_photo.product_id
WHERE product_photo.product_id is not NULL;

  

See the picture for more details 

Part 4. Delete and update data.

let's see an example of how we can delete one single row in a table.

I'm going to delete iwatch from table PRODUCT

I can delete it by id or by value. Remember, that value might be not unique, while id is always unique. That means you cannot delete two rows by one id, but you can do it by value 

see the id of iwatch and delete it.

SELECT * FROM product;

DELETE FROM product WHERE id = 2;

Now I want to update photo in PRODUCT_PHOTO 

SELECT * FROM product_photo;
UPDATE product_photo SET url = 'new_photo.html' WHERE id = 1;
SELECT * FROM product_photo; 

That's enough so far. 

We still have CUSTOMER and CARD tables that we didn't use here. We will take care of it next time.