1 / 11

SQL Examples

SQL Examples. Person. Car. Owns. Accident. Participated. List all drivers who live on Main Street. select driver_id , name from person where address like '%Main Street%';. List all drivers who own more than one car. select p.driver_id , p.name from person p, owns o

nerys
Download Presentation

SQL Examples

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Examples

  2. Person Car Owns Accident Participated

  3. List all drivers who live on Main Street. select driver_id, name from person where address like '%Main Street%';

  4. List all drivers who own more than one car. select p.driver_id, p.name from person p, owns o where p.driver_id = o.driver_id group by p.driver_id, p.name having count(*)>1;

  5. List all drivers who own more than one car. select p.driver_id, p.name from person p where p.driver_id in (select o.driver_id from owns o group by o.driver_id having count(*)>1 );

  6. List all drivers who were involved in more than one accident. select p.driver_id, p.name from person p where p.driver_id in (select pa.driver_id from participated pa group by pa.driver_id having count(car) > 1 );

  7. Find the driver with the highest payout. The insurance payout for a driver is the total damage amount in all the accidents in which the driver participated. select pa.driver_id, sum(damage_amount) as totalamount from participated pa group by pa.driver_id;

  8. Find the driver with the highest payout. select pa.driver_id, sum(damage_amount) as totalamount from participated pa group by pa.driver_id having sum(damage_amount) >= ALL (select sum(damage_amount) as total from participated p group by p.driver_id );

  9. List all the cars that were involved in more than one accident. select car from participated group by car having count(car)>1;

  10. List all the pairs of drivers and cars that were involved in more than one accident. select driver_id, car from participated group by driver_id, car having count(*)>1;

  11. What was the date of the latest accident involving driver whose driver_id is 10? select p.driver_id, max(a.adate) from participated p, accident a where p.report= a.report_number and p.driver_id=10 group by driver_id;

More Related