MySQL- Warm up Project Query (feb 17,2023)

< Warm Up Project (1) >

 

5. Give details of employees who worked in at least two different facilities. Details include employee’s first-name, last-name, start date, end date, facility name, role (nurse, doctor, etc.), Medicare card number, telephone-number, and email address. Results should be displayed sorted in ascending order by first name, then by last name, then by start date.

 

QUERY) 

 

SELECT e.firstName, e.lastName, w.start_date, w.end_date, w.Fname AS 'facility_name', w.role, w.MedicareCardNumber, e.telephoneNumber, e.email

FROM Employee e INNER JOIN workAt w ON w.MedicareCardNumber = e.MedicareCardNumber

GROUP BY e.firstName

HAVING COUNT(w.MedicareCardNumber) > 1; 

 

 

6. For every vaccine type, give the total number of doses taken by employees in the system. Results should be displayed sorted in descending order by total number of doses

 

 

QUERY) 

SELECT e.firstName, COUNT(v.medicare_num) AS total_dose

FROM Employee e, Vaccinated v

WHERE e.MedicareCardNumber = v.medicare_num

GROUP BY e.firstName

ORDER BY  total_dose DESC;

 

 

 

employee name | dose (how many dose they had  in descending order) 

 

 

 

 

WINDOWS PowerShell -MySQL 

1) ssh student_id@login.encs.concordia.ca

2) yes -> passoword for encs account

3) mysql -h lac353.encs.concordia.ca

4) password