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)
Comments
Post a Comment