1 How many employees work for the company?
Download 33.72 Kb.
|
| (1) How many employees work for the company?
mysql> select count(*) from employee; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) (2) How many employees have a firstname of Curtis? mysql> select count(*) from employee where firstname="Curtis"; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) (3) How many different firstnames do the employees have? mysql> select count(distinct firstname) from employee; +---------------------------+ | count(distinct firstname) | +---------------------------+ | 17 | +---------------------------+ 1 row in set (0.00 sec) (4) How many meetings were held (total)? mysql> select count(*) from meeting; +----------+ | count(*) | +----------+ | 282 | +----------+ 1 row in set (0.00 sec)
(5) How many meetings were held in each room (total)? mysql> select r.name, count(*) from room r, meeting m where r.room_id=m.room_id group by r.room_id order by r.name; +-----------------------------+----------+ | name | count(*) | +-----------------------------+----------+ | Atlantis collaboration room | 45 | | Canary consultation room | 80 | | Large boardroom B | 24 | | Marshall consultation room | 66 | | Pacifica collaboration room | 15 | | Small boardroom A | 52 | +-----------------------------+----------+ 6 rows in set (0.00 sec) (6) How many employees have a seniority of 6 or higher? mysql> select count(*) from employee where seniority>=6; +----------+ | count(*) | +----------+ | 12 | +----------+ 1 row in set (0.00 sec) (7) How many meetings were held on each date? Show the dates in chronological order. mysql> select date, count(*) from meeting group by date order by date; +------------+----------+ | date | count(*) | +------------+----------+ | 2008-01-01 | 11 | | 2008-01-02 | 11 | | 2008-01-03 | 14 | | 2008-01-04 | 13 | | 2008-01-07 | 11 | | 2008-01-08 | 9 | | 2008-01-09 | 7 | | 2008-01-10 | 9 | | 2008-01-11 | 11 | | 2008-01-14 | 11 | | 2008-01-15 | 16 | | 2008-01-16 | 10 | | 2008-01-17 | 9 | | 2008-01-18 | 16 | | 2008-01-21 | 13 | | 2008-01-22 | 16 | | 2008-01-23 | 10 | | 2008-01-24 | 17 | | 2008-01-25 | 13 | | 2008-01-28 | 10 | | 2008-01-29 | 13 | | 2008-01-30 | 12 | | 2008-01-31 | 20 | +------------+----------+ 23 rows in set (0.00 sec)
(8) How many meetings were held each day of the week? (BONUS: Show the names of the days in order: Monday, Tuesday, ... Friday) mysql> select dayname(m.date), count(*) from meeting m, room r where m.room_id=r.room_id group by weekday(m.date) order by weekday(m.date); +-----------------+----------+ | dayname(m.date) | count(*) | +-----------------+----------+ | Monday | 45 | | Tuesday | 65 | | Wednesday | 50 | | Thursday | 69 | | Friday | 53 | +-----------------+----------+ 5 rows in set (0.00 sec) (9) How many people were scheduled to attend meetings on January 10, 2008? Count each person once, even if they attended more than one meeting. mysql> select count(distinct p.staff_id) from meeting m, participant p where m.meeting_id=p.meeting_id and date="2008-01-10"; +----------------------------+ | count(distinct p.staff_id) | +----------------------------+ | 9 | +----------------------------+ 1 row in set (0.00 sec) (10) What were the names and dates of all meetings which Debra Futrell attended in the Canary consultation room? mysql> select m.name, m.date from meeting m, employee e, participant p, room r where m.meeting_id=p.meeting_id and p.staff_id=e.staff_id and r.room_id=m.room_id and e.lastname="Futrell" and e.firstname="Debra" and r.name="Canary consultation room"; +-----------------------+------------+ | name | date | +-----------------------+------------+ | Sales meeting | 2008-01-08 | | Project status review | 2008-01-17 | +-----------------------+------------+ 2 rows in set (0.01 sec) (11) How many people attended meetings in each room during the second week of January (January 7-11, 2008)? Show each room name and the number of people who attended meetings in that room. Count each person multiple times if they attended multiple meetings. mysql> select r.name, count(*) from meeting m, room r, participant p where m.room_id=r.room_id and p.meeting_id=m.meeting_id and m.date>="2008-01-07" and m.date<"2008-01-11" group by r.name order by r.name; +-----------------------------+----------+ | name | count(*) | +-----------------------------+----------+ | Atlantis collaboration room | 4 | | Canary consultation room | 18 | | Large boardroom B | 9 | | Marshall consultation room | 14 | | Pacifica collaboration room | 3 | | Small boardroom A | 1 | +-----------------------------+----------+ 6 rows in set (0.04 sec) (12) Which was the largest number of meetings attended by one person? mysql> select count(*) from participant group by staff_id order by count(*) desc limit 1; +----------+ | count(*) | +----------+ | 22 | +----------+ 1 row in set (0.00 sec) (13) What are the names of employees who attended the largest number of meetings? If several employees were tied, show all of their names. mysql> select e.lastname, e.firstname, count(*) as cnt from participant p, employee e where p.staff_id=e.staff_id group by p.staff_id having cnt=(select count(*) from participant p group by p.staff_id order by count(*) desc limit 1) order by e.lastname, e.firstname; +----------+-----------+-----+ | lastname | firstname | cnt | +----------+-----------+-----+ | Asbury | Harriet | 22 | +----------+-----------+-----+ 1 row in set (0.44 sec)
(14) What was the average number of meetings attended by the employees? Do not count employees who did not attend any meetings. mysql> select count(*)/count(distinct staff_id) as avg_meetings from participant; +--------------+ | avg_meetings | +--------------+ | 15.3000 | +--------------+ 1 row in set (0.44 sec)
(15) Display the name and number of meetings attended for each employee who attended less than the average number of meetings. mysql> select e.lastname, e.firstname, count(*) as cnt from participant p, employee e where p.staff_id=e.staff_id group by p.staff_id having cnt<(select count(*)/count(distinct staff_id) as avg_meetings from participant) order by e.lastname, e.firstname; +-----------+-----------+-----+ | lastname | firstname | cnt | +-----------+-----------+-----+ | Beall | Paul | 9 | | Begley | Florence | 14 | | Berrios | Curtis | 15 | | Chenoweth | Candace | 15 | | Clemmons | Martha | 15 | | Futrell | Debra | 10 | | Hu | Martin | 13 | | Lash | George | 14 | | Livesay | Dennis | 14 | | Moser | Anthony | 14 | +-----------+-----------+-----+ 10 rows in set (0.00 sec) Download 33.72 Kb. Share with your friends:
|