Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
databse university final class project submission
Typology: Assignments
1 / 25
This page cannot be seen from the preview
Don't miss anything!
-patient can order the medicine online from the website and it will be delivered to his door and the payment is also submitted online. -the website helps you enhance your profile through many other services, like identifying any allergies you might have listing all symptoms. -doctors always check the drugs inventory to pick a substitute to a missing drug since there is a lack in the drugs market.
1- A registered authorized user has 3 fail attempts to login to the system. 2- An item inventory can be supplied from different suppliers. 3- The patient can send more than 1 message. 4- A patient could have 1 or more Drug inventory items per description.
The database was created using mysql workbench The Schema was created using mysql workbench The screenshots were take using Sniping Tool.
FROM patient,allergies,patient_has_allergies WHERE patient.P_ID=patient_has_allergies.patient_P_ID AND allergies.A_ID= patient_has_allergies.allergies_A_ID GROUP BY patient.P_ID , patient.P_FirstName , patient.P_LastName; Query 3 : who are the suppliers that supply the drug inventory with more than 1 item SELECT supplier.S_Name, COUNT(supplier.S_SupplierID) FROM drugs_inventory , supplier, drugs_inventory_has_supplier
WHERE supplier.S_SupplierID = drugs_inventory_has_supplier.supplier_S_SupplierID AND drugs_inventory.DRG_nmbr = drugs_inventory_has_supplier.drugs_inventory_DRG_nmbr GROUP BY supplier.S_Name HAVING COUNT (supplier.S_supplierID) > 1; Query 4: Return the list of patients who has a greater ID than Ahmad and who live in Beirut and married. SELECT patient.P_ID, patient.P_FirstNAme, patient.P_LastName From patient WHERE patient.P_ID > (SELECT patient.P_ID from patient WHERE patient.P_FirstNAme-"Ahmad") AND patient.P_Address = "Beirut" AND patient.P_MaritalStatus = "married";
Query 6 : _Return the list of patients who sent a message to Dr.Fahim and lives at the same address of elie Select patient.P_ID,patients.P_FirstName,patient.P_LastName, Messages.M_ID From patient,patient_has_messages,messages, doctors,dotcors_receive_messages Where doctors.D_FirstName="fahim" And patient.P_address= ( select patient.P_address from patient where patient.P_FirstName= "elie";) And patient.P_ID=Patient-has_messages.patient_P_ID And messages.M_ID=patient_has_messages.messages_M_ID And doctors.D_ID=doctors_receive_messages.doctors_D_ID; ORDER BY patiend.P_FirstName;
Query 7 : return the list of drugs that needs a refill order from the suppliers Select drugs_inventory.DRG_nmbr, drugs_inventory.DRG_desc From drugs_inventory Where drugs_inventory.DRG_qutity=0;
Query 9 : list all the patients who live outside beirut SELECT patients.P_ID,patients.p_FirstName,patients.P_LastName From patients Where patients.P_address<>"beirut"
Query 10 :
Query 12: Return the list of banks who’s located in California SELECT bank.B_ID, bank.B_Name, bank.B_Country, bank.B_Address FROM bank WHERE bank.B_Address = 'california';
Query 13 : Return the list of doctors who was born before 01/12/ SELECT doctors.D_ID, doctors.D_FirstName, doctors.D_LastName, doctors.D_DOB FROM doctors WHERE doctors.D_DOB < '01/12/1970';
Query 15 : Retrun the count of patients SELECT COUNT(P_ID) FROM Paitent; Query 16 : Return the list of patient who does not live in Beirut SELECT patient.P_ID,patient.P_FirstName,patient.P_LastName,patient.P_Addree ss FROM Patient Where patient.P_Address <> ‘beirut’
Query 17 : Return the list of specialty who’s specialty number is greater then 786547 select specialty.S_Name , specialty.S_Description,specialty.S_SpecialtyNumber from specialty where specialty.S_SpecialtyNumber > 786547;
