Answered step by step
Verified Expert Solution
Question
1 Approved Answer
MySQL Question (please answer all parts, screenshot of table appointments.sql is included). TASK1: create schemas below Constraint Primary key Table Doctors Attributes Data Type Doctor,
MySQL Question (please answer all parts, screenshot of table appointments.sql is included).
TASK1: create schemas below Constraint Primary key Table Doctors Attributes Data Type Doctor, id Integer De Name Varchar(50) Date joined Date Gender Char(1) Speciality Varchar(50) Office Phone number Char(9) Work email address Varchar(50) Check if only numbers are inserted and if it starts with 04 Check if proper format of work email address which is 2 letters, followed by 3 numbers and then the @ symbol followed by hosp.ae Example: zh445@hosp.ae Constraint Primary key Table Patients Attributes Data Type Patient id Integer Patient.pame Varchar(50) Registration date date Gender Char(1) Dob date Phone number Char(10) Email address Varchar(50) Check if only numbers are inserted and if it starts with 05 Check if proper format of personal email address which is a series of lettersumbers followed by the @ symbol and a series of letters followed by :' Followed by more letters Example: suraj99@gmail.com Table Appointments Attributes Data Type Patient.id Integer Doctor, id Integer Appointment date time datetime Status enum Constraint References patients table References doctors table Confirmed or waiting Task 2: Run Lab3appointments.sql to populate tables (change as needed to make it work] NOTE: Make sure to convert strings into date or timestamp where needed Task 4: run queries below display all appointments in the future display details of the doctor who has been working in the hosp for the longest time list all patients who registered in 2021 in the month of august list the names of patient and their doctors whose appointment is after 12pm with doctors whose name ends with the letters 'ia' show all patients who registered as a patient in the 3rd quarter of 2021 show doctors who do not have a general specialty and show all their appointment dates in USA date format, name the column as USA date list patient names and how many days it has been since their registration date and today for patients who have more than 4 characters in their name show all patients with an email that does not start with s and does not end with com show the age of each patient with their name show patients whose birthday is on 10th of Oct, and they have an appointment with a doctor whose specialty starts with o . appointments* 1 . 2. , Za o Limit to 1000 rows insert into patients values (1, 'Saira', '2020-06-06', '7', '2000-10-10','0567758464', 'saira@gmail.com'); insert into patients values (2, 'Sana', '2021-08-06', '7', '1998-10-10', '0567758484', 'sana@gmail.com'); insert into patients values (3, 'Saad', '2021-07-06', 'M', '1991-01-10','0507758464', 'saad@uowd. ae'); insert into patients values (4, 'Said', '2021-08-16', 'M', '2005-10-10', '0567758464', 'dd@uow.au'); insert into patients values (5, 'Alina', '2021-1218', 'F','2002-10-10','0587758464', 'alina@gmail.com'); 3. 1 1 4 . 5. 6 7. 8. 9. insert into doctors values (1, 'Nadia', '2020-09-11','F', 'Pediatry','043458464', 'nd955@hosp.ae'); insert into doctors values (2, 'Hania', '2005-06-06','F','Orthopedy','043459464', 'hn991@hosp.ae'); insert into doctors values (3,'Jacob', '2020-06-06', 'M', 'General', '042458464', 'jc@hosp.ae'); insert into doctors values (4, 'Rayan', '2018-06-01','M', 'General','043125844', 'rd122@hosp.ae'); insert into doctors values (5, 'Jack', '2006-06-06', 'M', 'Ophthalmology','043459464', 'jk899@hosp.ae'); 10. 11 . 12 13. 14 . 15 16. insert into appointments values (3,5,'2022-01-26 12:20:00','confirmed'); insert into appointments values (1,1,'2022-01-12 13:30:00','confirmed'); insert into appointments values (2,4,'2022-01-22 17:20:00','confirmed'); insert into appointments values (2,5, '2022-02-30 09:20:00', 'waiting'); insert into appointments values (1,1,'2022-02-28 17:00:00','confirmed'); insert into appointments values (3,2,'2022-02-29 12:20:00',' , 'waiting'); insert into appointments values (4,3,'2022-01-21 16:30:00','confirmed'); insert into appointments values (2,5, '2022-01-10 15:20:00','confirmed'); 17 . 18. 19. 20. 21Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started