POSTGRES COMMANDS

 1. Create your own database on provided server. Name it DB_yourname.

 postgres=# create user db_rahul with password 'abcd';
CREATE ROLE
postgres=# 
postgres=# 
postgres=# create database db_rahul with owner db_rahul;
CREATE DATABASE
postgres=# 

2. Create table, where you have 3 columns:

'ID', 'Name', 'Organization', 'department'

Fill in this table with data - create 3 records.

 postgres=# \c db_rahul
You are now connected to database "db_rahul" as user "postgres".
db_rahul=# create table customer ( id int not null,
db_rahul(# name text not null,
db_rahul(# organization text not null,
db_rahul(# department text not null );
CREATE TABLE
db_rahul=# insert into customer (id,name,organization,department) values (1,'rahul','datacom','Cloud');
INSERT 0 1
db_rahul=# insert into customer (id,name,organization,department) values (2,'vishnu','datacom','Cloud');
INSERT 0 1
db_rahul=# insert into customer (id,name,organization,department) values (3,'ranjith','datacom','Cloud');
INSERT 0 1
db_rahul=# insert into customer (id,name,organization,department) values (4,'sreejith','syntrio','Level 3');
INSERT 0 1
db_rahul=# select * from customer;
 id |   name   | organization | department 
----+----------+--------------+------------
  1 | rahul    | datacom      | Cloud
  1 | vishnu   | datacom      | Cloud
  1 | ranjith  | datacom      | Cloud
  4 | sreejith | syntrio      | Level 3
(4 rows)

 3. Mark 'ID' column as 'Index' in your table.

 db_rahul=# create index id_number on customer(id);
CREATE INDEX
db_rahul=# \d customer;
      Table "public.customer"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 id           | integer | not null
 name         | text    | not null
 organization | text    | not null
 department   | text    | not null
Indexes:
    "id_number" btree (id)

 4. Create another table where you have 4 columns:

'ID', 'Name', 'City', 'Street'

 db_rahul=# create table sales ( id int not null,
name text not null,
city text not null,
street text not null );
CREATE TABLE
db_rahul=# insert into sales (id,name,city,street) values (1,'rahul','selangor','kaula lumpur');
INSERT 0 1
db_rahul=# insert into sales (id,name,city,street) values (2,'vishnu','selangor','kaula lumpur');
INSERT 0 1
db_rahul=# insert into sales (id,name,city,street) values (3,'sreejith','kilimanor','trivandrum');
INSERT 0 1
db_rahul=# select * from sales;
 id |   name   |   city    |    street    
----+----------+-----------+--------------
  1 | rahul    | selangor  | kaula lumpur
  2 | vishnu   | selangor  | kaula lumpur
  3 | sreejith | kilimanor | trivandrum
(3 rows)

 5. Create a foreign key that will connect 'ID' from 1st table with ID from 2nd table.

 db_rahul=# alter table customer add constraint id_detail unique (id);
ALTER TABLE
db_rahul=# alter table sales add FOREIGN KEY (id) references customer (id) on update cascade;
ALTER TABLE
db_rahul=# alter table sales add constraint id_details unique (id);
ALTER TABLE

db_rahul=# \d sales;
     Table "public.sales"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | not null
 city   | text    | not null
 street | text    | not null
Indexes:
    "id_details" UNIQUE CONSTRAINT, btree (id)
Foreign-key constraints:
    "sales_id_fkey" FOREIGN KEY (id) REFERENCES customer(id) ON UPDATE CASCADE

db_rahul=# \d customer;
      Table "public.customer"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 id           | integer | not null
 name         | text    | not null
 organization | text    | not null
 department   | text    | not null
Indexes:
    "id_detail" UNIQUE CONSTRAINT, btree (id)
    "id_number" btree (id)
Referenced by:
    TABLE "sales" CONSTRAINT "sales_id_fkey" FOREIGN KEY (id) REFERENCES customer(id) ON UPDATE CASCADE

 6. Try to test that it is working: try to update ID of the 1st or 2nd table (or try INSERT statement).
 You should get error.

 db_rahul=# update sales set id=6 where name='vishnu';
ERROR:  insert or update on table "sales" violates foreign key constraint "sales_id_fkey"
DETAIL:  Key (id)=(6) is not present in table "customer".

 7. Compose query that will show you all names from 1st table where column 'City' from 2nd table = Kuala Lumpur (or any other you have in your 2nd table) - use operator INNER JOIN. 

 db_rahul=# select * from customer inner join sales on (customer.name=sales.name) where street='kaula lumpur'
;
 id |  name  | organization | department | id |  name  |   city   |    street    
----+--------+--------------+------------+----+--------+----------+--------------
  1 | rahul  | datacom      | Cloud      |  1 | rahul  | selangor | kaula lumpur
  2 | vishnu | datacom      | Cloud      |  2 | vishnu | selangor | kaula lumpur
(2 rows)

 db_rahul=# select customer.name,sales.city from customer inner join sales on (customer.name=sales.name) where street='kaula lumpur'
;
  name  |   city   
--------+----------
 rahul  | selangor
 vishnu | selangor
(2 rows)

8. Compose query that will show you 'Organization' details from 1st table together with 'City', 'Street' from 2nd table. 

 db_rahul=# select customer.organization,sales.city,sales.street from customer full outer join sales on (customer.name=sales.name) ;
 organization |   city    |    street    
--------------+-----------+--------------
 datacom      | selangor  | kaula lumpur
 syntrio      | kilimanor | trivandrum
 datacom      | selangor  | kaula lumpur
 datacom      |           | 
(4 rows)

 Database administration task.

to copy the schema of a table

db_rahul=# select * into salesnew from sales where 1=2;
SELECT 0
db_rahul=# select * from sales;
 id |   name   |   city    |    street    
----+----------+-----------+--------------
  1 | rahul    | selangor  | kaula lumpur
  2 | vishnu   | selangor  | kaula lumpur
  3 | sreejith | kilimanor | trivandrum
(3 rows)

db_rahul=# select * from salesnew;
 id | name | city | street 
----+------+------+--------
(0 rows)

 

 1. Create a backup of your database. What is the size of it? Restore it to another name,

 postgres@rahul-Inspiron-3542:~$ pg_dump db_rahul > db_rahul
postgres@rahul-Inspiron-3542:~$ du -sch db_rahul
4.0K db_rahul
4.0K total
postgres@rahul-Inspiron-3542:~$ psql
psql (9.4.5)
Type "help" for help.

postgres=# create database db_rahulnew;
CREATE DATABASE
postgres=# \q
postgres@rahul-Inspiron-3542:~$ psql db_rahulnew < db_rahul 
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 4
COPY 3
COPY 0
ALTER TABLE
ALTER TABLE
CREATE INDEX
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
postgres@rahul-Inspiron-3542:~$ psql
psql (9.4.5)
Type "help" for help.

postgres=# \c db_rahulnew;
You are now connected to database "db_rahulnew" as user "postgres".
db_rahulnew=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | customer | table | postgres
 public | sales    | table | postgres
 public | salesnew | table | postgres
(3 rows)