DBMS (PostgreSQL)

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)