DBMS (PostgreSQL)
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
·
Create a database in PostgreSQL.
postgres=# create database school;
CREATE DATABASE
·
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)
·
To connect Database :
postgres=# \c school;
You are now connected to database "school"
as user "postgres".
·
To create Table
school=# create table student(rno integer,class
text, div text, s_name text);
CREATE TABLE
·
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
·
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)
·
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)
·
To add primary key (Alter Command)
school=# alter table student add primary key(rno);
ALTER TABLE
·
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
·
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)
·
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)
0 comments:
Post a Comment