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)
);
![Customers (patients)
customer id
PK
Addresses
Doctors
address id
FK
address_id
PK
doctor id
PK
first_name
city
address_id
FK
last name
state
first _name
phone
country
last_name
note
note
phone
email
note
Prescription
prescription_id
PK
customer_id
EK
doctor_id
FK
Рayment
payment_method_id
FK
peyment_method_id
PK
date
payment_name
note
note
Prescription_items
Medication
prescription_id
medication_id
FK
medication_id
PK
FK
medication code
quantity
medication_name
note
medication_cost
note](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F1bbe4509-6423-4edd-be37-c9a25c16a7ef%2F339edfc2-469d-45ec-b48f-56442308efcd%2F4au2zm7_processed.png&w=3840&q=75)
![](/static/compass_v2/shared-icons/check-mark.png)
Step by step
Solved in 2 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Concepts of Database Management](https://www.bartleby.com/isbn_cover_images/9781337093422/9781337093422_smallCoverImage.gif)
![Prelude to Programming](https://www.bartleby.com/isbn_cover_images/9780133750423/9780133750423_smallCoverImage.jpg)
![Sc Business Data Communications and Networking, T…](https://www.bartleby.com/isbn_cover_images/9781119368830/9781119368830_smallCoverImage.gif)