List of Queries and results for database coursework.

queryResults.txt

1.    View the payments for a specific year and month

select * from pay where (extract(month from dateOfPayment) = "02" and extract(year from dateOfPayment) = "2012");

+-----------+------------+---------------+-------------+-----------------+-------+-----------+
| paymentID | employeeID | dateOfPayment | hoursWorked | commissionSales | bonus | contactID |
+-----------+------------+---------------+-------------+-----------------+-------+-----------+
|         2 |          1 | 2012-02-01    |           0 |               0 |  1000 |      NULL | 
|         4 |          2 | 2012-02-01    |          40 |               0 |    50 |      NULL | 
|         8 |          3 | 2012-02-02    |          20 |               1 |     0 |      NULL | 
|         9 |          3 | 2012-02-09    |          60 |               1 |     0 |      NULL | 
|        11 |          4 | 2012-02-26    |          40 |              10 |     0 |      NULL | 
|        13 |          6 | 2012-02-01    |          12 |               8 |     0 |      NULL | 
|        14 |          6 | 2012-02-08    |          14 |              19 |     0 |      NULL | 
|        15 |       NULL | 2012-02-01    |           0 |               0 |   100 |         1 | 
|        16 |       NULL | 2012-02-01    |           0 |               0 |   250 |         3 | 
|        17 |       NULL | 2012-02-08    |           0 |               0 |   250 |         3 | 
|        18 |       NULL | 2012-02-01    |           0 |               0 |   270 |         2 | 
|        21 |       NULL | 2012-02-12    |           0 |               0 |   150 |         4 | 
+-----------+------------+---------------+-------------+-----------------+-------+-----------+

2.     View the total outgoing pay for a specific year and month


SELECT sum(salary/12) + sum(hoursWorked*payPerHour) + sum(bonus) + sum(commission*commissionSales) AS totalPay  FROM viewEmployeeWages WHERE dateOfPayment BETWEEN '2012-02-01' AND '2012-03-01';

+-----------+
| totalPay  |
+-----------+
| 8156.6667 | 
+-----------+

3. View the total commission, hourly based, bonuses and overall pay for an employee


select sum(salary/12) + sum(hoursWorked*payPerHour) + sum(bonus) + sum(commission*commissionSales) as" OverallPay" from viewEmployeeWages where name = "Daniel Bell";

+------------+
| OverallPay |
+------------+
|  6000.0000 | 
+------------+

4. View the details of each employee that belongs to a certain pay band


SELECT name, sum(salary/12) + sum(hoursWorked*payPerHour) + sum(bonus) + sum(commission*commissionSales) as OverallPay from viewEmployeeWages WHERE viewEmployeeWages.band = 2 GROUP BY employeeID;

+-----------------+------------+
| name            | OverallPay |
+-----------------+------------+
| Calum Gilchrist |  5883.3334 | 
| Jane Doe        |   875.0000 | 
| Steve Doe       |   602.5000 | 
| George Best     |   540.0000 | 
+-----------------+------------+

5. Rank employees based on their total overall pay

SELECT id, name, sum(salary) + sum(hoursWorked*payPerHour) + sum(bonus) + sum(commission*commissionSales) AS totalPay  FROM viewEmployeeWages WHERE dateOfPayment GROUP BY name ORDER BY totalPay DESC;

+------+-----------------+----------+
| id   | name            | totalPay |
+------+-----------------+----------+
|    2 | Calum Gilchrist | 70050.00 | 
|    1 | Daniel Bell     | 61000.00 | 
|    3 | John Doe        |  1650.00 | 
|    4 | Jane Doe        |   875.00 | 
|    5 | Steve Doe       |   602.50 | 
|    6 | George Best     |   540.00 | 
+------+-----------------+----------+

6. ist all employee details as well as the name and id of their manager

SELECT employees.* , man.id AS manID, man.name AS manager FROM employees AS man INNER JOIN employees ON employees.managerID = man.id;

+----+-------------+-------------+-----------------+------------+-----------+-------+-----------+---------+---------+-------+-----------------+
| id | name        | dateOfBirth | address         | startDate  | phone     | email | managerID | worksIn | payBand | manID | manager         |
+----+-------------+-------------+-----------------+------------+-----------+-------+-----------+---------+---------+-------+-----------------+
|  4 | Jane Doe    | 1991-04-10  | 32 Doe street   | 2012-01-30 | 876555333 | NULL  |         2 |       3 |       3 |     2 | Calum Gilchrist | 
|  5 | Steve Doe   | 1990-02-23  | 33 Doe street   | 2011-11-11 | 845333888 | NULL  |         2 |       2 |       3 |     2 | Calum Gilchrist | 
|  6 | George Best | 1965-07-18  | 310 Best street | 2011-06-15 | 987564372 | NULL  |         1 |       3 |       6 |     1 | Daniel Bell     | 
+----+-------------+-------------+-----------------+------------+-----------+-------+-----------+---------+---------+-------+-----------------+

7. Rank employees by their start date

select * from employees order by startDate;

+----+-----------------+-------------+--------------------+------------+-----------+----------------+-----------+---------+---------+
| id | name            | dateOfBirth | address            | startDate  | phone     | email          | managerID | worksIn | payBand |
+----+-----------------+-------------+--------------------+------------+-----------+----------------+-----------+---------+---------+
|  6 | George Best     | 1965-07-18  | 310 Best street    | 2011-06-15 | 987564372 | NULL           |         1 |       3 |       6 | 
|  1 | Daniel Bell     | 1992-04-10  | 123 Random Address | 2011-07-06 | 765447362 | [email protected] |      NULL |       2 |       1 | 
|  2 | Calum Gilchrist | 1987-01-01  | 256 Random Address | 2011-08-22 | 131454342 | [email protected] |         0 |       2 |       4 | 
|  5 | Steve Doe       | 1990-02-23  | 33 Doe street      | 2011-11-11 | 845333888 | NULL           |         2 |       2 |       3 | 
|  4 | Jane Doe        | 1991-04-10  | 32 Doe street      | 2012-01-30 | 876555333 | NULL           |         2 |       3 |       3 | 
|  3 | John Doe        | 1986-07-30  | 33 Doe street      | 2012-02-06 | 131666333 | NULL           |      NULL |       1 |       2 | 
+----+-----------------+-------------+--------------------+------------+-----------+----------------+-----------+---------+---------+

8. Count how many tickets there are for an event and how many are left over

SELECT eventName, COUNT(*) AS ticketsSold, amountOfTickets-COUNT(*) AS ticketsLeft FROM ticket JOIN event ON eventID = event.id GROUP BY(amountOfTickets);

+----------------------------------+-------------+-------------+
| eventName                        | ticketsSold | ticketsLeft |
+----------------------------------+-------------+-------------+
| Geordie Folkmen Play Heavy Metal |           8 |          42 | 
| The Three Scotsman in Scotland   |           7 |          93 | 
+----------------------------------+-------------+-------------+

9. List all the tickets that belong to a customer as well as the event details


select barCode,eventName,name from ticket LEFT JOIN event ON ticket.eventID = event.id LEFT JOIN customer ON customerID = customer.id WHERE name = "John Customer";

+---------+----------------------------------+---------------+
| barCode | eventName                        | name          |
+---------+----------------------------------+---------------+
|       1 | The Three Scotsman in Scotland   | John Customer | 
|       3 | The Three Scotsman in Scotland   | John Customer | 
|      11 | Geordie Folkmen Play Heavy Metal | John Customer | 
+---------+----------------------------------+---------------+

10. List all of the events that an entertainer played at


SELECT eventName, dateTime, name
FROM event
INNER JOIN playsAt ON playsAt.id = event.id
LEFT JOIN entertainer ON playsAt.entID = entertainer.contactID
LEFT JOIN contact ON contact.id = entertainer.contactID
WHERE name = "Geordie Folkmen"

+----------------------------------+---------------------+-----------------+
| eventName                        | dateTime            | name            |
+----------------------------------+---------------------+-----------------+
| Geordie Folkmen Play Heavy Metal | 2012-02-05 18:00:00 | Geordie Folkmen | 
+----------------------------------+---------------------+-----------------+


11. List all of the entertainers that an agent represents 

DROP VIEW IF EXISTS viewContact;

CREATE VIEW viewContact AS SELECT contact.*, agent.contactID AS agentConID , agent.company, entertainer.type, entertainer.contactID AS entertainerID, entertainer.agentID, entertainer.genre FROM contact, agent, entertainer WHERE contact.id=entertainer.contactID OR agent.contactID=contact.id GROUP BY id;

SELECT viewContact.* FROM viewContact AS agent INNER JOIN viewContact ON viewContact.agentID = agent.agentconID GROUP BY viewContact.id ORDER BY agent.agentConID;

+----+-----------------+-------------+-----------------+----------------------+------------+------------------+------+---------------+---------+-------+
| id | name            | phone       | address         | email                | agentConID | company          | type | entertainerID | agentID | genre |
+----+-----------------+-------------+-----------------+----------------------+------------+------------------+------+---------------+---------+-------+
|  4 | Geordie Folkmen | 07425654371 | 13 Leith Street | [email protected] |          1 | Edinburgh Talent | Band |             4 |       1 | Folk  | 
+----+-----------------+-------------+-----------------+----------------------+------------+------------------+------+---------------+---------+-------+


12. List entertainers and their pay 

SELECT contact.*, entertainer.*, sum(bonus) AS totalPay FROM entertainer LEFT JOIN contact ON entertainer.contactID = contact.id LEFT JOIN pay ON pay.contactID=contact.id WHERE pay.contactID IS NOT NULL GROUP BY contact.id;

+------+--------------------+-------------+-----------------+----------------------+-----------+--------+---------+---------+----------+
| id   | name               | phone       | address         | email                | contactID | type   | genre   | agentID | totalPay |
+------+--------------------+-------------+-----------------+----------------------+-----------+--------+---------+---------+----------+
|    3 | The Three Scotsmen | 07425654371 | 13 Leith Street | [email protected] |         3 | Comedy | Standup |    NULL |      500 | 
|    4 | Geordie Folkmen    | 07425654371 | 13 Leith Street | [email protected] |         4 | Band   | Folk    |       1 |      450 | 
+------+--------------------+-------------+-----------------+----------------------+-----------+--------+---------+---------+----------+


13. Order all customers by city


select * from viewRestrictedCustomers order by city;

+----+---------------+-------------------+--------------+------------+------------------+-------------+--------+
| id | name          | address1          | address2     | city       | creditCardNumber | dateOfBirth | gender |
+----+---------------+-------------------+--------------+------------+------------------+-------------+--------+
|  4 | Emma Florence | 23 Wayland Ave    | High Park    | Aberdeen   | 5555888844446666 | 1989-04-13  | Female | 
|  1 | John Customer | 123 Random Street | NULL         | Edinburgh  | 4444555566667777 | 1991-05-06  | Male   | 
|  3 | Jane Customer | 256 Random Street | NULL         | Edinburgh  | 2222333344445555 | 1986-03-09  | Female | 
|  5 | Tom Grant     | 73 Sycamore Drive | NULL         | Glasgow    | 4444555544444444 | 1969-04-24  | Male   | 
|  2 | Ethan Grey    | 67 New Street     | Newfoundland | Nottingham | 2222333344445555 | 1955-12-04  | Male   | 
+----+---------------+-------------------+--------------+------------+------------------+-------------+--------+

14. View all of the tickets purchased by at the box office on the night of the event ordered by event


select barcode,price,eventName from ticket left join event on ticket.eventID = event.id WHERE (extract(year from ticket.dateOfPurchase) = extract(year from dateTime) and extract(month from ticket.dateOfPurchase) = extract(month from dateTime) and extract(day from ticket.dateOfPurchase) = extract(day from dateTime));

+---------+-------+----------------------------------+
| barcode | price | eventName                        |
+---------+-------+----------------------------------+
|       3 | 18.50 | The Three Scotsman in Scotland   | 
|       4 | 18.50 | The Three Scotsman in Scotland   | 
|       5 | 18.50 | The Three Scotsman in Scotland   | 
|       6 | 18.50 | The Three Scotsman in Scotland   | 
|       7 | 18.50 | The Three Scotsman in Scotland   | 
|       9 | 25.00 | Geordie Folkmen Play Heavy Metal | 
|      10 | 25.00 | Geordie Folkmen Play Heavy Metal | 
|      11 | 25.00 | Geordie Folkmen Play Heavy Metal | 
|      12 | 25.00 | Geordie Folkmen Play Heavy Metal | 
|      13 | 25.00 | Geordie Folkmen Play Heavy Metal | 
|      14 | 25.00 | Geordie Folkmen Play Heavy Metal | 
|      15 | 25.00 | Geordie Folkmen Play Heavy Metal | 
+---------+-------+----------------------------------+

15. Show type of equipment that is not being used in any building


SELECT * FROM equipment WHERE buildingID=0;

+----------+-------------+------+-----------------+--------------+---------------+-----------------+------------+
| itemCode | productCode | name | type            | manufacturer | supplier      | lastSafetyCheck | buildingID |
+----------+-------------+------+-----------------+--------------+---------------+-----------------+------------+
|        6 |         113 | AC2  | Air Conditioner | General Air  | AC Units R Us | 2010-11-19      |          0 | 
+----------+-------------+------+-----------------+--------------+---------------+-----------------+------------+


16. Show equpiment that hasn’t been safety checked for 6 months

SELECT *, MONTH(FROM_DAYS(DATEDIFF(CURDATE(), DATE(lastSafetyCheck)))) AS monthsSinceCheck FROM equipment HAVING monthsSinceCheck > 6;

+----------+-------------+------+------------+--------------+-------------------+-----------------+------------+------------------+
| itemCode | productCode | name | type       | manufacturer | supplier          | lastSafetyCheck | buildingID | monthsSinceCheck |
+----------+-------------+------+------------+--------------+-------------------+-----------------+------------+------------------+
|        1 |         111 | Mic1 | Microphone | Samsung      | General Electrics | 2010-05-07      |          1 |               10 | 
|        2 |         112 | Amp1 | Amplifier  | Fender       | Stan's Amps       | 2010-06-23      |          1 |                9 | 
|        3 |         111 | Mic2 | Microphone | Samsung      | General Electrics | 2010-05-07      |          2 |               10 | 
|        4 |         112 | Amp2 | Amplifier  | Fender       | Stan's Amps       | 2010-06-23      |          2 |                9 | 
+----------+-------------+------+------------+--------------+-------------------+-----------------+------------+------------------+

17. Create a new pay slip for an employee

INSERT INTO pay (employeeID, dateOfPayment, hoursWorked, commissionSales, bonus) 
VALUES (
( SELECT id FROM employees WHERE name='Daniel Bell' ),
CURDATE(), 0, 0, 0
);

+-----------+------------+---------------+-------------+-----------------+-------+-----------+
| paymentID | employeeID | dateOfPayment | hoursWorked | commissionSales | bonus | contactID |
+-----------+------------+---------------+-------------+-----------------+-------+-----------+
|        22 |          1 | 2012-02-28    |           0 |               0 |     0 |      NULL | 
+-----------+------------+---------------+-------------+-----------------+-------+-----------+

18. Add an entertainer to the database

INSERT INTO contact(name, phone,address,email) VALUES ( 'Engelbert and Humperdink', 05678746321, '56 Etcetra Street', '[email protected]' );
INSERT INTO entertainer ( contactID, type, genre, agentID ) VALUES ( LAST_INSERT_ID(),  'Magic', 'Illusionist', 2);

+----+--------------------------+-------------+---------------------+-------------------------+
| id | name                     | phone       | address             | email                   |
+----+--------------------------+-------------+---------------------+-------------------------+
|  5 | Engelbert and Humperdink | 5678746321  | 56 Etcetra Street   | [email protected] | 
+----+--------------------------+-------------+---------------------+-------------------------+

+-----------+--------+-------------+---------+
| contactID | type   | genre       | agentID |
+-----------+--------+-------------+---------+
|         5 | Magic  | Illusionist |       2 | 
+-----------+--------+-------------+---------+