how can I write an SQL SELECT Queries for each of the information requests? List the Patients_Full_name, PatientsID, Patients_State, and Patients_Zipcode who has an Orders_No List the Patients_Full_Name, Patients_ID, Patientsstate who has the medicine price greater than 35.79 List the PatientsID, Patients_Street, Patient first name, and Patients last name for the patients whose first name starts with M and patients_zipcode is '19115' List the patients, Patients Fullname, PatientsAddress, Patients state, and Patients_Zipcode that live in NY or PA List the Patients_FName, Patients_LName, Patients_Address, Patients_City, Patients_Address that are in PA DROP DATABASE Patients_medicines; CREATE DATABASE Patients_medicines; USE Patients_medicines; CREATE TABLE Patients ( Patients_ID CHAR (8), Patients_DOB CHAR (10), Patients_FName VARCHAR (50), Patients_LName VARCHAR (50), Patients_Phonenumber CHAR (12), CONSTRAINT Patients PRIMARY KEY (Patients_ID) ); CREATE TABLE Patients_Address ( Patients_ID CHAR (8), Patients_Street VARCHAR (50), Patients_City VARCHAR (50), Patients_State VARCHAR (50), Patients_Zipcode CHAR (5), CONSTRAINT Patients_Address PRIMARY KEY (Patients_ID), CONSTRAINT Patients_Address_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID) ); CREATE TABLE Orders ( Patients_ID CHAR (8), Orders_ID CHAR (8), Orders_Date VARCHAR (50), Orders_No BOOLEAN, CONSTRAINT Orders_PK PRIMARY KEY (Orders_ID), CONSTRAINT Orders_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID) ); CREATE TABLE Makes ( Patients_ID CHAR (8), Orders_ID CHAR (8), CONSTRAINT Makes_PK PRIMARY KEY (Patients_ID, Orders_ID), CONSTRAINT Makes_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID), CONSTRAINT Makes_FK2 FOREIGN KEY (Orders_ID) REFERENCES Orders (Orders_ID) ); CREATE TABLE Medicines ( Medicines_ID CHAR (8), Medicines_Name VARCHAR (50), Medicines_Price DOUBLE, Orders_ID CHAR (8), CONSTRAINT Medicines_PK PRIMARY KEY (Medicines_ID), CONSTRAINT Medicines_FK1 FOREIGN KEY (Orders_ID) REFERENCES Orders (Orders_ID) ); insert into Patients ( Patients_ID, Patients_DOB, Patients_FName, Patients_LName, Patients_Phonenumber) values ('P1','1987/08/02','David','Charles','7894561230'), ('P2','1974/04/24','Michael','Luis','1456789023'), ('P3','1991/10/12','John','Smith','8888888888'), ('P4','1992/11/12','Don','Bosco','2675262981'), ('P5','1993/12/03','Michael','Linchen','2382952091') ; insert into Patients_Address (Patients_ID, Patients_Street, Patients_City, Patients_State, Patients_Zipcode) values ('P1','5678 Walnut Street','New York','NY','11104'), ('P2','6447 Rhawn Street','Philadelphia','PA','31145'), ('P3','3923 Spring Market Street','Oklahoma City','OK','73134'), ('P4','1010 Flourence Circle','Philadelphia','PA','19118'), ('P5','101 Philadelphia Pike','Philadelphia','PA','19115') ; insert into Orders (Patients_ID, Orders_ID, Orders_Date, Orders_No) values ('P3','1','2019/11/27', TRUE), ('P1','2','2019/11/28', FALSE), ('P2','3','2019/11/29', FALSE), ('P4','5','2019/11/30', TRUE), ('P5','4','2019/11/26', TRUE) ; insert into Makes (Patients_ID, Orders_ID) values ('P1','2'), ('P2','3'), ('P3','1'), ('P4','5'), ('P5','4') ; insert into Medicines (Medicines_ID, Medicines_Name, Medicines_Price, Orders_ID) values ('M1','Syrup',50.49,'4'), ('M2','Tablet',75.35,'1'), ('M3','Syrup',35.79,'2'), ('M4','Tablet',100,'3')

how can I write an SQL SELECT Queries for each of the information requests? List the Patients_Full_name, PatientsID, Patients_State, and Patients_Zipcode who has an Orders_No List the Patients_Full_Name, Patients_ID, Patientsstate who has the medicine price greater than 35.79 List the PatientsID, Patients_Street, Patient first name, and Patients last name for the patients whose first name starts with M and patients_zipcode is '19115' List the patients, Patients Fullname, PatientsAddress, Patients state, and Patients_Zipcode that live in NY or PA List the Patients_FName, Patients_LName, Patients_Address, Patients_City, Patients_Address that are in PA DROP DATABASE Patients_medicines; CREATE DATABASE Patients_medicines; USE Patients_medicines; CREATE TABLE Patients ( Patients_ID CHAR (8), Patients_DOB CHAR (10), Patients_FName VARCHAR (50), Patients_LName VARCHAR (50), Patients_Phonenumber CHAR (12), CONSTRAINT Patients PRIMARY KEY (Patients_ID) ); CREATE TABLE Patients_Address ( Patients_ID CHAR (8), Patients_Street VARCHAR (50), Patients_City VARCHAR (50), Patients_State VARCHAR (50), Patients_Zipcode CHAR (5), CONSTRAINT Patients_Address PRIMARY KEY (Patients_ID), CONSTRAINT Patients_Address_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID) ); CREATE TABLE Orders ( Patients_ID CHAR (8), Orders_ID CHAR (8), Orders_Date VARCHAR (50), Orders_No BOOLEAN, CONSTRAINT Orders_PK PRIMARY KEY (Orders_ID), CONSTRAINT Orders_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID) ); CREATE TABLE Makes ( Patients_ID CHAR (8), Orders_ID CHAR (8), CONSTRAINT Makes_PK PRIMARY KEY (Patients_ID, Orders_ID), CONSTRAINT Makes_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID), CONSTRAINT Makes_FK2 FOREIGN KEY (Orders_ID) REFERENCES Orders (Orders_ID) ); CREATE TABLE Medicines ( Medicines_ID CHAR (8), Medicines_Name VARCHAR (50), Medicines_Price DOUBLE, Orders_ID CHAR (8), CONSTRAINT Medicines_PK PRIMARY KEY (Medicines_ID), CONSTRAINT Medicines_FK1 FOREIGN KEY (Orders_ID) REFERENCES Orders (Orders_ID) ); insert into Patients ( Patients_ID, Patients_DOB, Patients_FName, Patients_LName, Patients_Phonenumber) values ('P1','1987/08/02','David','Charles','7894561230'), ('P2','1974/04/24','Michael','Luis','1456789023'), ('P3','1991/10/12','John','Smith','8888888888'), ('P4','1992/11/12','Don','Bosco','2675262981'), ('P5','1993/12/03','Michael','Linchen','2382952091') ; insert into Patients_Address (Patients_ID, Patients_Street, Patients_City, Patients_State, Patients_Zipcode) values ('P1','5678 Walnut Street','New York','NY','11104'), ('P2','6447 Rhawn Street','Philadelphia','PA','31145'), ('P3','3923 Spring Market Street','Oklahoma City','OK','73134'), ('P4','1010 Flourence Circle','Philadelphia','PA','19118'), ('P5','101 Philadelphia Pike','Philadelphia','PA','19115') ; insert into Orders (Patients_ID, Orders_ID, Orders_Date, Orders_No) values ('P3','1','2019/11/27', TRUE), ('P1','2','2019/11/28', FALSE), ('P2','3','2019/11/29', FALSE), ('P4','5','2019/11/30', TRUE), ('P5','4','2019/11/26', TRUE) ; insert into Makes (Patients_ID, Orders_ID) values ('P1','2'), ('P2','3'), ('P3','1'), ('P4','5'), ('P5','4') ; insert into Medicines (Medicines_ID, Medicines_Name, Medicines_Price, Orders_ID) values ('M1','Syrup',50.49,'4'), ('M2','Tablet',75.35,'1'), ('M3','Syrup',35.79,'2'), ('M4','Tablet',100,'3')

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

how can I write an SQL SELECT Queries for each of the information requests?

List the Patients_Full_name, PatientsID, Patients_State, and Patients_Zipcode who has an Orders_No
List the Patients_Full_Name, Patients_ID, Patientsstate who has the medicine price greater than 35.79
List the PatientsID, Patients_Street, Patient first name, and Patients last name for the patients whose first name starts with M and patients_zipcode is '19115'
List the patients, Patients Fullname, PatientsAddress, Patients state, and Patients_Zipcode that live in NY or PA
List the Patients_FName, Patients_LName, Patients_Address, Patients_City, Patients_Address that are in PA

DROP DATABASE Patients_medicines;

CREATE DATABASE Patients_medicines;

USE Patients_medicines;

CREATE TABLE Patients

(

Patients_ID CHAR (8),

Patients_DOB CHAR (10),

Patients_FName VARCHAR (50),

Patients_LName VARCHAR (50),

Patients_Phonenumber CHAR (12),

CONSTRAINT Patients PRIMARY KEY (Patients_ID)

);

CREATE TABLE Patients_Address

(

Patients_ID CHAR (8),

Patients_Street VARCHAR (50),

Patients_City VARCHAR (50),

Patients_State VARCHAR (50),

Patients_Zipcode CHAR (5),

CONSTRAINT Patients_Address PRIMARY KEY (Patients_ID),

CONSTRAINT Patients_Address_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID)

);

CREATE TABLE Orders

(

Patients_ID CHAR (8),

Orders_ID CHAR (8),

Orders_Date VARCHAR (50),

Orders_No BOOLEAN,

CONSTRAINT Orders_PK PRIMARY KEY (Orders_ID),

CONSTRAINT Orders_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID)

);

CREATE TABLE Makes

(

Patients_ID CHAR (8),

Orders_ID CHAR (8),

CONSTRAINT Makes_PK PRIMARY KEY (Patients_ID, Orders_ID),

CONSTRAINT Makes_FK1 FOREIGN KEY (Patients_ID) REFERENCES Patients (Patients_ID),

CONSTRAINT Makes_FK2 FOREIGN KEY (Orders_ID) REFERENCES Orders (Orders_ID)

);

CREATE TABLE Medicines

(

Medicines_ID CHAR (8),

Medicines_Name VARCHAR (50),

Medicines_Price DOUBLE,

Orders_ID CHAR (8),

CONSTRAINT Medicines_PK PRIMARY KEY (Medicines_ID),

CONSTRAINT Medicines_FK1 FOREIGN KEY (Orders_ID) REFERENCES Orders (Orders_ID)

);

insert into Patients ( Patients_ID, Patients_DOB, Patients_FName, Patients_LName, Patients_Phonenumber)

values ('P1','1987/08/02','David','Charles','7894561230'),

('P2','1974/04/24','Michael','Luis','1456789023'),

('P3','1991/10/12','John','Smith','8888888888'),

('P4','1992/11/12','Don','Bosco','2675262981'),

('P5','1993/12/03','Michael','Linchen','2382952091')

;

insert into Patients_Address (Patients_ID, Patients_Street, Patients_City, Patients_State, Patients_Zipcode)

values ('P1','5678 Walnut Street','New York','NY','11104'),

('P2','6447 Rhawn Street','Philadelphia','PA','31145'),

('P3','3923 Spring Market Street','Oklahoma City','OK','73134'),

('P4','1010 Flourence Circle','Philadelphia','PA','19118'),

('P5','101 Philadelphia Pike','Philadelphia','PA','19115')

;

insert into Orders (Patients_ID, Orders_ID, Orders_Date, Orders_No)

values ('P3','1','2019/11/27', TRUE),

('P1','2','2019/11/28', FALSE),

('P2','3','2019/11/29', FALSE),

('P4','5','2019/11/30', TRUE),

('P5','4','2019/11/26', TRUE)

;

insert into Makes (Patients_ID, Orders_ID)

values ('P1','2'),

('P2','3'),

('P3','1'),

('P4','5'),

('P5','4')

;

insert into Medicines (Medicines_ID, Medicines_Name, Medicines_Price, Orders_ID)

values ('M1','Syrup',50.49,'4'),

('M2','Tablet',75.35,'1'),

('M3','Syrup',35.79,'2'),

('M4','Tablet',100,'3')

;

Patients_DOB
Patients ID FK
Orders ID FK
Orders_ID
Patients_ID
Patients_ID_FK
PatientsFNAME
Patients
-M-
Makes
-M-
Orders
Orders_No
PatientsLName
Orders_Date
PatientsPhn_No
has
contains
Patients_DOB
M
Medicines ID
Patients ID FK
Medicines_Name
PatientsFNAME
Patients_Addres
Medicines
Orders_ID_FK
PatientsLName
Medicines_price
PatientsPhn_No

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.