-- Some example tasks:

-- Show the whole table "people", all rows and all fields
-- (hint: SELECT *).

-- Show the column "firstname" from the table "people".

-- Show all people who live in Berlin (hint: WHERE ...).

-- Show only the firstname of the people living in Berlin.

-- How many people are in the table "people"? (hint: COUNT(...))

-- How many people are living in Goerlitz?

-- Show a list of cities and how many people live in every city
-- (hint: GROUP BY ...).

-- Cross join the people and meals tables
-- (hint: INNER JOIN ... ON ...).

-- Add a WHERE clause (a join condition) to the JOIN above, where
-- the foreigt key in meals and the primary key in people match.

-- GROUP the people and show the number of meals they eat.

-- Replace the inner with an OUTER JOIN.

-- ...

-- An advanced one:
-- SELECT p.firstname,
--        COUNT(m.id) AS `Number of meals`,
--        COUNT(*) AS `Total number of records`,
--        MIN(m.date),
--        MAX(m.date)
--   FROM people AS p
--   LEFT OUTER JOIN meals AS m
--     ON p.id = m.people_id
--  GROUP BY p.id
--  ORDER BY `Number of meals` DESC;

#
# Tabellenstruktur für Tabelle `meals`
#

CREATE TABLE `meals` (
  `id`        int(10) unsigned NOT NULL auto_increment,
  `people_id` int(10) unsigned          default NULL,
  `meal`      varchar(255)              default NULL,
  `dateeaten` date                      default NULL,
  PRIMARY KEY  (`id`)
);

#
# Daten für Tabelle `meals`
#

INSERT INTO `meals` (`id`, `people_id`, `meal`, `dateeaten`) VALUES (21, 12, 'Bread', '2004-10-01');
INSERT INTO `meals` (`id`, `people_id`, `meal`, `dateeaten`) VALUES (22, 12, 'Cutled', '2004-10-02');
INSERT INTO `meals` (`id`, `people_id`, `meal`, `dateeaten`) VALUES (23, 12, 'Vegetables', '2004-10-03');
INSERT INTO `meals` (`id`, `people_id`, `meal`, `dateeaten`) VALUES (24, 14, 'Bread', '2004-09-15');
INSERT INTO `meals` (`id`, `people_id`, `meal`, `dateeaten`) VALUES (25, 11, 'Bread', '2004-09-03');
INSERT INTO `meals` (`id`, `people_id`, `meal`, `dateeaten`) VALUES (26, 14, 'Vegetables', '2004-09-17');

# --------------------------------------------------------

#
# Tabellenstruktur für Tabelle `people`
#

CREATE TABLE `people` (
  `id`        int(10) unsigned NOT NULL auto_increment,
  `firstname` varchar(255)              default NULL,
  `city`      varchar(255)     NOT NULL default 'Goerlitz',
  PRIMARY KEY  (`id`)
);

#
# Daten für Tabelle `people`
#

INSERT INTO `people` (`id`, `firstname`, `city`) VALUES (11, 'Max', 'Berlin');
INSERT INTO `people` (`id`, `firstname`, `city`) VALUES (12, 'Paul', 'Dresden');
INSERT INTO `people` (`id`, `firstname`, `city`) VALUES (13, 'Chris', 'Goerlitz');
INSERT INTO `people` (`id`, `firstname`, `city`) VALUES (14, 'Jon', 'Goerlitz');