DBMS (PostgreSQL)
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user
postgres:
1. Create a database in PostgreSQL.
postgres=# create
database school;
CREATE DATABASE
2. To view Database :
postgres=# \l
List of databases
Name | Owner |
Encoding
| Collate | Ctype | Access
privileges
-----------+----------+----------+----------------------------+----------------------------+-----------------------
college |
postgres | UTF8 | English_United States.1252 |
English_United States.1252 |
postgres |
postgres | UTF8 | English_United States.1252 |
English_United States.1252 |
school |
postgres | UTF8 | English_United States.1252 |
English_United States.1252 |
template0 |
postgres | UTF8 | English_United States.1252 |
English_United States.1252 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 |
postgres | UTF8 | English_United States.1252 |
English_United States.1252 |
=c/postgres +
| | | | |
postgres=CTc/postgres
(5 rows)
3. To connect Database :
postgres=# \c school;
You are now connected to
database "school" as user "postgres".
4. To create Table
school=# create table
student(rno integer,class text, div text, s_name text);
CREATE TABLE
5. To insert data into Table :
school=# insert into
student(rno,class,div,s_name) values(101,'XI','B','Sonakshi');
INSERT 0 1
school=# insert into
student(rno,class,div,s_name) values(102,'XI','B','Sonam');
INSERT 0 1
school=# insert into
student(rno,class,div,s_name) values(103,'XI','B','Kajol');
INSERT 0 1
school=# insert into
student(rno,class,div,s_name) values(104,'XI','A','Sonali');
INSERT 0 1
school=# insert into
student(rno,class,div,s_name) values(105,'XI','A','Priyanka');
INSERT 0 1
6. To view inserted data :
school=# select * from
student;
rno | class | div
| s_name
-----+-------+-----+----------
101 |
XI | B | Sonakshi
102 |
XI | B | Sonam
103 |
XI | B | Kajol
104 |
XI | A | Sonali
105 |
XI | A | Priyanka
(5 rows)
7. To update data in table :
school=# update student
set div='C' where rno='103';
UPDATE 1
school=# select * from
student;
rno | class | div
| s_name
-----+-------+-----+----------
101 |
XI | B | Sonakshi
102 |
XI | B | Sonam
104 |
XI | A | Sonali
105 |
XI | A | Priyanka
103 |
XI | C | Kajol
(5 rows)
8. To add primary key (Alter Command)
school=# alter table
student add primary key(rno);
ALTER TABLE
9. To create table :
school=# create table
exam(rno integer,exam_name text,total_marks integer);
CREATE TABLE
school=# insert into
exam(rno,exam_name,total_marks) values(101,'UT1',85);
INSERT 0 1
school=# insert into
exam(rno,exam_name,total_marks) values(102,'UT1',95);
INSERT 0 1
school=# insert into
exam(rno,exam_name,total_marks) values(103,'UT1',89);
INSERT 0 1
school=# insert into
exam(rno,exam_name,total_marks) values(104,'UT1',90);
INSERT 0 1
school=# insert into
exam(rno,exam_name,total_marks) values(105,'UT1',88);
INSERT 0 1
10.
To display records :
school=# select * from
exam;
rno | exam_name |
total_marks
-----+-----------+-------------
101 |
UT1 | 85
102 |
UT1 | 95
103 |
UT1 | 89
104 | UT1 | 90
105 |
UT1 | 88
(5 rows)
11.
To add foreign key :
school=# alter table
exam add foreign key(rno) references student(rno);
ALTER TABLE
Let’s see the result of
both table ‘student’ and ‘exam’ with one-to-one relationship.
school=# select
student.rno,student.class,student.div,exam.exam_name,exam.total_marks from
student,exam where student.rno=exam.rno;
rno | class | div
| exam_name | total_marks
-----+-------+-----+-----------+-------------
101 |
XI | B |
UT1 | 85
102 |
XI | B |
UT1 | 95
103 |
XI | C |
UT1 | 89
104 |
XI | A |
UT1 | 90
105 |
XI | A |
UT1 | 88
(5 rows)
school=# select
student.rno, student.class, student.div, student.s_name, exam.exam_name, exam.total_marks
from student,exam where student.rno=exam.rno;
rno | class | div
| s_name | exam_name | total_marks
-----+-------+-----+----------+-----------+-------------
101 |
XI | B | Sonakshi | UT1 | 85
102 |
XI | B | Sonam |
UT1 | 95
103 |
XI | C | Kajol |
UT1 | 89
104 |
XI | A | Sonali |
UT1 | 90
105 |
XI | A | Priyanka |
UT1 | 88
(5 rows)
No comments:
Post a Comment