Home‎ > ‎CIS 54‎ > ‎Midterm Review‎ > ‎

Practice Query Solutions

The following queries are for the public_art_class database.

Write a query that shows the courses that cost less than $400 
    select * from public_art_class.COURSE where Fee < 400; 

Write a query that selects only the Pastel courses from public_art_class
    select * from public_art_class.COURSE where Course like '%Pastel%'; 

Write a query that produces just one row, the course in public_art_class that has the latest starting date.
    SELECT * FROM public_art_class.COURSE order by CourseDate desc limit 1;

Write a query that prints how many people are enrolled in each course. You don't have to print the course name, only the course number. Name your columns CourseNumber and Enrollment
    select CourseNumber, count(*) as Enrollment from public_art_class.ENROLLMENT group by CourseNumber;

Write a query that prints all of the available services in the order from least to most expensive. 
    select * from public_garden_glory.GG_SERVICE order by CostPerHour;

Write a query that shows how many properties are in each city served by Garden Glory. 
    select City, State, count(*) from public_garden_glory.OWNED_PROPERTY group by City, State;

Write a query that shows service dates where there are three or more total combined hours worked. 
    select ServiceDate, sum(HoursWorked) from public_garden_glory.PROPERTY_SERVICE group by ServiceDate having sum(HoursWorked) > 3; 

Write a query that shows how many restaurants have more "Generals" on their current inspection than their previous inspection. 
    select count(*) from public_restaurant.webdata where CurrentGeneral > PrevGeneral; 

Write a query that sums each of the major and minor columns in the current inspection into the totals that you see in the previous inspection. 
    select 
    (CurrentMajorFT + CurrentMajorFP + CurrentMajorFS + CurrentMajorVI + CurrentMajorWS + CurrentMajorEH + CurrentMajorHW) as CurrentMajors,
    (CurrentMinorFT + CurrentMinorFP + CurrentMinorFS + CurrentMinorVI + CurrentMinorWS + CurrentMinorEH + CurrentMinorHW) as CurrentMinors
    from public_restaurant.webdata ; 
 
Write a query that shows restaurants that have more major AND minor violations on their current inspection than they did on their previous inspection
    select 
      RestaurantName, RestaurantAddr,
          (CurrentMajorFT + CurrentMajorFP + CurrentMajorFS + CurrentMajorVI + CurrentMajorWS + CurrentMajorEH + CurrentMajorHW) as CurrentMajor,
     (CurrentMinorFT + CurrentMinorFP + CurrentMinorFS + CurrentMinorVI + CurrentMinorWS + CurrentMinorEH + CurrentMinorHW) as CurrentMinor,
      PrevMajor, PrevMinor
    from public_restaurant.webdata 
    having CurrentMajor > PrevMajor and CurrentMinor > PrevMinor;
Comments