- Fork this repository.
- You can either work on your clone in GitHub, or open your clone in visual studio.
- Use the Doctors Office ERD above to answer the following questions:
-
How many patients can each doctor have? Each doctor can have many patients.
-
How many doctors can each patient have? Each patient can have one doctor.
-
How would you describe the relationship between patients and tests? Be sure to use either one-to-one, one-to-many, or many-to-many in your answer. One-to-many
-
What are the foreign keys in this diagram? patients.doctor_id and tests.patient_id
-
What is the primary key for the Tests table. tests.id
-
What query would return the number of doctors who have a specialization in "pediatrics"? SELECT COUNT(*) FROM doctors WHERE specialization = 'pediatrics'
-
-
What does a join table do? Why would we need one? A join table hold foreign keys that connect two other teables. We need to do this to create a many-to-many relationship
-
What is a question that the following query helps answer?
SELECT hometown, COUNT(name) FROM artists
GROUP BY hometown;
How many artists are from each hometown?
- I'm trying to write a query to find the average age of all patients, but it's not working. How would you modify this query to get it to work as expected?
SELECT age FROM AVERAGE(patients);
SELECT AVG(age) FROM patients
- How would you describe the difference between a
LEFT JOIN
and anINNER JOIN
An inner join shows data from two tables where a primary key connects to a foreign key. A left join does that, but also shows all the information from the left table, even if it doesnt connect to the other table.
Follow these steps to setup the assessment:
- Create a new database named
flight_db
using pgAdmin. - Copy this script and paste it into the query tool to insert records into your database.
- Run the following
SELECT
queries individually to get an understanding of the data:
SELECT * FROM airlines;
SELECT * FROM flights;
** If you need help setting up the database, reach out to an instructor! **
Please provide the QUERY (not the answer) that returns each of the following:
-
The flight numbers for all delayed flights (i.e. not on time). SELECT flight_number, on_time FROM flights WHERE on_time = false;
-
The count of delayed flights. SELECT COUNT(*) FROM flights WHERE on_time = false;
-
The sum of prices for all flights arriving to Raleigh-Durham (
RDU
). SELECT SUM(price) FROM flights WHERE arrive_city = 'RDU'; -
The average price for all flights in the database. SELECT AVG(price) FROM flights;
-
The average price for all flights arriving to Raleigh-Durham. SELECT AVG(price) FROM flights WHERE arrive_city = 'RDU';
-
The departure city and number of flights departing from each city. SELECT depart_city, COUNT(*) FROM flights GROUP BY depart_city;
-
The count of airlines in the database. SELECT COUNT(*) FROM airlines;
-
The count of flights in the database. SELECT COUNT(*) FROM flights;
-
The flight number, departure city, arrival city, price, and airline name of each flight. Do not return the airline ID number. SELECT flights.flight_number, flights.arrive_city, flights.price, airlines.airline_name FROM flights JOIN airlines ON flights.airline_id = airlines.id;
-
The airline name, flight number, and price of each flight on the Delta airline. (Assume that you do not know the ID number of the Delta airline. In a larger database, you would be expected to memorize ID numbers). SELECT airlines.airline_name, flights.flight_number, flights.price FROM flights JOIN airlines ON flights.airline_id = airlines.id WHERE airlines.airline_name = 'Delta';
Submit the Assessment Submission form linked in your cohort slack channel!
This assessment has a total of 20 Points. Earning 14 or more points is a pass and will indicate that you are progressing well with the material.
As a reminder, this assessment is for students and instructors to determine if there are any areas that need additional reinforcement!