ses( address_id varchar2(10) not null constraint addresses_pk primary key, city varchar2(20) not null, state varchar2(20) not null, country varchar2(20) not null, note varchar2(50) ); create table Doctors(
- List the name of doctors with the number of written prescriptions.
- List the customers and the number of prescriptions each has.
- List the most prescribed medicines in the ascending order.
- . What is the name of mostly prescribed medicine?
create table Addresses(
address_id varchar2(10) not null constraint addresses_pk primary key,
city varchar2(20) not null,
state varchar2(20) not null,
country varchar2(20) not null,
note varchar2(50)
);
create table Doctors(
doctor_id varchar2(10) not null constraint doctor_pk primary key,
address_id varchar2(10) not null,
first_name varchar2(20) not null,
last_name varchar2(20) not null,
phone varchar2(20),
email varchar2(20),
note varchar2(50),
constraint doctor_fk foreign key (address_id) references Addresses(address_id)
);
create table Customers(
customer_id varchar2(10) not null constraint customer_id primary key,
address_id varchar2(10) not null,
first_name varchar2(20) not null,
last_name varchar2(20) not null,
phone varchar2(10),
note varchar2(50),
constraint customer_fk foreign key (address_id) references Addresses(address_id)
);
create table Payment(
payment_method_id varchar2(10) not null constraint paymentmethod_pk primary key,
payment_name varchar2(10) not null,
note varchar2(50)
);
create table Prescription(
prescription_id varchar2(10) not null constraint prescription_pk primary key,
customer_id varchar2(10) not null,
doctor_id varchar2(10) not null,
payment_method_id varchar2(10) not null,
p_date date not null,
note varchar2(50),
constraint prescription_fk foreign key (customer_id) references Customers(customer_id),
constraint prescription1_fk foreign key (doctor_id) references Doctors(doctor_id),
constraint prescription2_fk foreign key (payment_method_id) references Payment(payment_method_id)
);
create table Medication(
medication_id varchar2(10) not null constraint medication_pk primary key,
medication_code varchar2(10) not null,
medication_name varchar2(10) not null,
medication_cost varchar2(10) not null,
note varchar2(50)
);
create table Prescription_items(
prescription_id varchar2(10) not null,
medication_id varchar2(10) not null,
quantity varchar2(10) not null,
note varchar2(50),
constraint prescriptionitems_fk foreign key (prescription_id) references Prescription(prescription_id),
constraint prescriptionitems1_fk foreign key (medication_id) references Medication(medication_id)
);
Step by step
Solved in 2 steps with 1 images
