mem2.sql

/*
5. The total number of students travelling to a specific country in a multi city 
trip operated by a specified coach in given date. 
Result should contain both detailed breakup & summary for above mentioned 
categories along with overall summary.
*/

SELECT COUNT(student_id) AS `Total number of students`, country_name, vin AS `Coach No`
FROM country 
INNER JOIN tour ON tour.country_id=country.country_id
INNER JOIN trip ON trip.tour_id=tour.tour_id
INNER JOIN student ON student.tour_id=tour.tour_id
INNER JOIN coach ON coach.coach_id=tour.coach_id
WHERE vin = "EDV123" AND start_date > "2016-01-01" AND end_date < "2017-12-31"
GROUP BY student_id, country_name, vin
HAVING COUNT(trip_id)>1;

/*
6. Create a query which shows the age and home address of students within any time range.  
*/

SELECT CONCAT(first_name, ' ', last_name) AS `Full name`, 
TIMESTAMPDIFF(YEAR,dob,NOW()) AS `Age`, 
CONCAT(unit_no,', ',street_line,', ',city_name,', ',zip_code,', ',country_name) AS `Address`
FROM person
INNER JOIN student ON student.student_id=person.person_id
INNER JOIN address ON address.address_id=person.address_id
INNER JOIN city_country ON city_country.city_id=address.city_id
INNER JOIN country ON country.country_id=city_country.country_id;