Write an SQL query for the following questions: 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) );
- Write an SQL query for the following questions:
- 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
