DBMS-Slip19

Slip19

Consider the following Entities and Relationships                                   
Person (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Person and Car is Many to Many with date and time as
descriptive attribute.Constraint: Primary key.

Solution:-

Create a Database in 3NF & write queries for following.
•Display details of all persons who are driving ‘Alto’ car.
SQL>Select dname from person,car ,pc where person.did=pc.did and car.lno=pc.lno and model=’alto’;

•Count the number of cars driven by each driver.
SQL>Select  count(model),dname from person,car ,pc where person.did=pc.did and car.lno=pc.lno group by dname;

•Display car details manufactured before year 2000.
SQL> Select * from car where year<2000;

•In which day ‘Mr. Ram’ drives maximum number of cars.
SQL>select count(car.model),dates,dname from person,car ,pc where person.did=pc.did and car.lno=pc.lno and dname=’ram’ group by dates,dname;

•Display total number of persons who drives car in each year.
SQL>Select count(person.did),year,dname  from person,car,pc where person.did=pc.did and car.lno=pc.lno group by year,dname;