Solve the following DDL queries. 1.Create the following tables with all the requ

Question

Solve the following DDL queries.                                                                  

1.Create the following tables with all the required constraints. (Include Both table level and Column level constraints)                                                                                  

        A) PATIENT: (PATIENT_NO, PNAME, AGE, JOIN_DATE, DOC_ID)                                 

        B) DOCTOR (DOC_NO, DNAME, DMAJOR, DATE_OF_BIRTH)                 

DOC_ID in the PATIENT table is a foreign key. Create it with CASCADE SET NULL option.

2.Insert 1 record into the PATIENT table using an Interactive script.                    

Explanation / Answer

// create doctor table

SQL> create table doctor (DOC_NO int ,DNAME varchar2(20) not null,DMAJOR varchar
2(15) unique, DATE_OF_BIRTH date,primary key (doc_no));

Table created.

SQL> desc doctor;
Name Null? Type
----------------------------------------- -------- ----------------------------

DOC_NO NOT NULL NUMBER(38)
DNAME NOT NULL VARCHAR2(20)
DMAJOR VARCHAR2(15)
DATE_OF_BIRTH DATE

insert data in to tables by using value method:

insert into doctor values(001,'smith','Cardiologis','22-dec-1988');

insert into doctor values(002,'john','Gynecologist','12-mar-1986');

insert into doctor values(003,'joe','Psychiatrist','20-jan-1976');

// create patient table:

SQL> create table patient(patient_no int primary key,pname varchar2(20) not nul
l,age int,join_date date,doc_id int references doctor(doc_no) on delete cascade);

Table created.

[or] to create second alternate

syntax: to specify foren key references to

on delete [ no action | cascade | set Null | set default ]

// SQL> create table patient1 (patient_no int primary key,pname varchar2(20) not nul
l,age int,join_date date,doc_id int references doctor(doc_no) on delete set null);

//
SQL> desc patient;
Name Null? Type
----------------------------------------- -------- ----------------------------

PATIENT_NO NOT NULL NUMBER(38)
PNAME NOT NULL VARCHAR2(20)
AGE NUMBER(38)
JOIN_DATE DATE
DOC_ID NUMBER(38)

insert data in to tables by using value method:

insert into patient values(150,'david',30,'02-apr-2017',002);
insert into patient values(120,'jaffer',50,'20-mar-2017',001);
insert into patient values(110,'frank',40,'28-jan-2017',003);

2.insert the values interactive method:(insert data in to tables by using Address method:)

SQL> insert into patient values(&PATIENT_NO,'&PNAME',&AGE,'&JOIN_DATE',&DOC_ID);

Enter value for patient_no: 115
Enter value for pname: lisa
Enter value for age: 45
Enter value for join_date: 20-mar-2017
Enter value for doc_id: 003
old 1: insert into patient values(&PATIENT_NO,'&PNAME',&AGE,'&JOIN_DATE',&DOC_
ID)
new 1: insert into patient values(115,'lisa',45,'20-mar-2017',003)

1 row created.

SQL> /
Enter value for patient_no: 121
Enter value for pname: rachel
Enter value for age: 38
Enter value for join_date: 30-mar-2017
Enter value for doc_id: 001
old 1: insert into patient values(&PATIENT_NO,'&PNAME',&AGE,'&JOIN_DATE',&DOC_
ID)
new 1: insert into patient values(121,'rachel',38,'30-mar-2017',001)

1 row created.

SQL> /
Enter value for patient_no: 125
Enter value for pname: jekyll
Enter value for age: 55
Enter value for join_date: 10-dec-2016
Enter value for doc_id: 002
old 1: insert into patient values(&PATIENT_NO,'&PNAME',&AGE,'&JOIN_DATE',&DOC_
ID)
new 1: insert into patient values(125,'jekyll',55,'10-dec-2016',002)

1 row created.

SQL> select * from patient;

PATIENT_NO PNAME AGE JOIN_DATE DOC_ID
---------- -------------------- - --------- --------- ---------
  
115 lisa 45 20-MAR-17 3
121 rachel 38 30-MAR-17 1
125 jekyll 55 10-DEC-16 2

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.