DBMS (PostgreSQL)
SOP 1: Create a
database, using postgreSQL named hospital.
·
In this database, create a table of patients with the following
fields Patient_Id, Patients_Name, Address, Room_number and Doctor’s_name.
·
Give appropriate data type for each field.
Ans :-
1. Create database hospital.
postgres=# create
database hospital;
CREATE DATABASE
2. To connect database
hospital.
postgres=# \c hospital;
You are now connected to
database "hospital" as user "postgres".
3. Create table patients.
hospital=# create table
patients(patient_id integer primary key, patients_name text, address text,
room_number integer, doctors_name text);
CREATE TABLE
4. To show structure of the
table patients.
hospital=# \d patients;
Table
"public.patients"
Column | Type |
Collation | Nullable | Default
---------------+---------+-----------+----------+---------
patient_id |
integer
| | |
patients_name |
text | | |
address |
text | | |
room_number |
integer
| | |
doctors_name |
text | | |
SOP 2: Create a
database, using postgreSQL named School_master.
·
In this database, create a table of students with the following
fields – Student_ID, Student_name, Address, Phone_number, Date_of_Birth.
·
Give appropriate data type for each field. Enter at least 5
records.
Ans :-
1. Create a database
school_master.
postgres=# create
database school_master;
CREATE DATABASE
2. To connect database
school_master.
postgres=# \c
school_master;
You are now connected to
database "school_master" as user "postgres".
3. To create table students.
school_master=# create
table students(student_id smallint primary key,student_name varchar(30),address
varchar(40),phone_number integer,Date_of_birth date);
CREATE TABLE
4. To insert records into
table students.
school_master=# insert
into students(student_id,student_name,address,phone_number,Date_of_birth)
values(101,'Jitendra','Pune',1234567891,'1999-05-07');
INSERT 0 1
school_master=# insert
into students(student_id,student_name,address,phone_number,Date_of_birth)
values(102,'Hitendra','Malad',234567891,'1998-08-05');
INSERT 0 1
school_master=# insert
into students(student_id,student_name,address,phone_number,Date_of_birth)
values(103,'Rahul','Kolhapur',345678945,'1997-06-01');
INSERT 0 1
school_master=# insert
into students(student_id,student_name,address,phone_number,Date_of_birth)
values(104,'Mamata','Satara',564123789,'1999-06-01');
INSERT 0 1
school_master=# insert
into students(student_id,student_name,address,phone_number,Date_of_birth)
values(105,'Sonali','Sangali',897456123,'2000-05-07');
INSERT 0 1
school_master=# select *
from students;
student_id |
student_name | address | phone_number | date_of_birth
------------+--------------+----------+--------------+---------------
101
| Jitendra |
Pune | 1234567891 | 1999-05-07
102
| Hitendra |
Malad | 234567891 | 1998-08-05
103
| Rahul | Kolhapur
| 345678945 | 1997-06-01
104
| Mamata |
Satara | 564123789 | 1999-06-01
105
| Sonali |
Sangali | 897456123 | 2000-05-07
(5 rows)
SOP 3: Given the list of
fields – EmpId, EmpName, EmpDepartment, SalaryId, SalaryAmount, Bonus in
the tables Employee and Salary respectively. Define primary key, foreign key
and segregate for above fields into employee and salary table. Also create
one-to-one relationship between Employee and Salary table.
Ans :-
1. To create database
company.
postgres=# create
database company;
CREATE DATABASE
2. To connect database
company.
postgres=# \c company;
You are now connected to
database "company" as user "postgres".
3. To create table Employee
and Salary.
postgres=# create table
employee(Empid smallint,Empname varchar(30),Empdepartment varchar(40));
CREATE TABLE
postgres=# create table
salary(SalaryId smallint,Salaryamount integer,Bonus smallint);
CREATE TABLE
4. To display structure of
table employee and salary.
postgres=# \d employee;
Table
"public.employee"
Column | Type |
Collation | Nullable | Default
---------------+-----------------------+-----------+----------+---------
empid |
smallint | | |
empname |
character varying(30)
| | |
empdepartment |
character varying(40)
| | |
postgres=# \d salary;
Table
"public.salary"
Column | Type |
Collation | Nullable | Default
--------------+----------+-----------+----------+---------
salaryid |
smallint
| | |
salaryamount |
integer | | |
bonus |
smallint
| | |
5. To insert record into
table employee.
postgres=# insert into
employee(empid,empname,empdepartment) values(1001,'Nilesh','Sales');
INSERT 0 1
postgres=# insert into
employee(empid,empname,empdepartment) values(1002,'Sandip','Purchase');
INSERT 0 1
postgres=# insert into
employee(empid,empname,empdepartment) values(1003,'Pankaj','HR');
INSERT 0 1
6. To insert record into
table salary.
postgres=# insert into
salary(salaryid,salaryamount,bonus) values(1001,20000,2000);
INSERT 0 1
postgres=# insert into
salary(salaryid,salaryamount,bonus) values(1002,25000,2500);
INSERT 0 1
postgres=# insert into
salary(salaryid,salaryamount,bonus) values(1003,12000,1500);
INSERT 0 1
7. To display records from
table employee and Salary.
postgres=# select * from
employee;
empid | empname |
empdepartment
-------+---------+---------------
1001 |
Nilesh | Sales
1002 |
Sandip | Purchase
1003 |
Pankaj | HR
(3 rows)
postgres=# select * from
salary;
salaryid |
salaryamount | bonus
----------+--------------+-------
1001
| 20000 | 2000
1002
| 25000 | 2500
1003
| 12000 | 1500
(3 rows)
8. Add primary key to table
employee.
postgres=# alter table
employee add primary key(empid);
ALTER TABLE
9. To add foreign key to
table salary.
postgres=# alter table
salary add foreign key(salaryid) references employee(empid);
ALTER TABLE
10.
To set one-to-one
relationship between employee and salary table and display records form both
table.
postgres=# select
employee.empid,employee.empname,salary.salaryamount,salary.bonus from
employee,salary where employee.empid=salary.salaryid;
empid | empname |
salaryamount | bonus
-------+---------+--------------+-------
1001 |
Nilesh | 20000
| 2000
1002 |
Sandip | 25000
| 2500
1003 |
Pankaj | 12000
| 1500
(3 rows)
No comments:
Post a Comment