SOP 1: Create a database, using Postgres SQL 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.
Answer:
Step 1: Open Command Terminal. Switch over to the Postgres account on your server by typing.
$ sudo -i -u Postgres
Step 2: You can now access a Postgres prompt immediately by typing.
$ psql
Step 3: To create a database hospital;
create database hospital;
Step 4: Connect to Database using \c
\c hospital;
Step 5: Create a table in the database. Create Table Command is used.
create table patients(patients_Id Integer,patients_name text,Address text,Room_number integer,Doctor_name text);
Step 6: Let’s see the result of the patient’s table.
select * from patients;
or
\d patients;
SOP 2: Create a database using PostgreSQL named Schoolmaster.
- 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 types for each field. Enter at least 5 records.
Answer:
Step 1: Create a database School-Master.
create database school_master;
Step 2: Now To connect the database use \c Command.
\c database school_master;
Step 3: Create a table of students with the following fields.Give appropriate data type for each Field.
student_ID, student_name, Address. Phone_number, Date_of_Birth.
create table students(student_ID integer, student_name text,Address_text,Phone_number integer,Date_of_Birth date);
Step 4: Enter at least 5 records.
Insert into students values(001,’ZAHRA LALANIVMAZGAON’,123456789,’20-08-2000’);
Insert into students values(002,’MUHAMMAD LALANI’/BYCULLA’, 987654210,’30-01-2000’);
Insert into students values(003,’KUNAL KAPOOR’,’WALKESHWAR’, 987224210,T5-7-2000’);
Insert into students values(004,’AKSHAY SINGH’,’CHARNI ROAD’. 937224210,’19-6-2000’);
Insert into students values(005,’RUKHSHAR BANU ’,’DIWANPARA’, 937226210,’18-8-2000’);
Step 5: Show all records using select command
seleet*from students:
SOP 3: Given the list of fields: Empld, EmpName, EmpDepartment, Salaryld, Salary Amount, 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 a one-to-one relationship between Employee and Salary Table.
Answer:
Step 1: Create a school database
Create database school;
Step 2: connect to database \c databasename;
\c school;
Step 3: In this database create two tables Employee and Salary with the following fields. Define primary key, foreign key and segregate for above fields into employee and salary table. Empld, EmpName, EmpDepartment, Salaryld, SalaryAmount, Bonus.
Create table salary(salaryld Integer PRIMARY KEY,Salaryamount integer,Bonus integer);
Create table employeefEmpId integer PRIMARY KEY,EmpName text,EmpDept text,Salaryld integer,FOREIGN KEY(salary ID)”REFERENCES Salaryfsalary id));
Step 4: See both tables
select*from salary;
select*from employee;
