Databases MySQL Exam - 22 Oct 2017

01. DDL - Table Design (28/30)
CREATE TABLE users(
    id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30) UNIQUE,
    `password` VARCHAR(50) NOT NULL,
    name VARCHAR(50),
    gender VARCHAR(1),
    birthdate DATE,
    age INT(11),
    email VARCHAR(50) NOT NULL
);
CREATE TABLE departments(
    id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);
CREATE TABLE employees(
    id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    gender VARCHAR(1),
    birthdate DATE,
    age INT(11),
    department_id INT(11),
    CONSTRAINT fk_department_id
    FOREIGN KEY (department_id) REFERENCES departments(id)
);
CREATE TABLE categories(
    id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT(11),
    CONSTRAINT fk_categories_id
    FOREIGN KEY (department_id) REFERENCES departments(id)
);
CREATE TABLE `status`(
    id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
    label VARCHAR(30) NOT NULL
);
CREATE TABLE reports(
    id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
    category_id INT(11),
    status_id INT(11),
    open_date DATE,
    close_date DATE,
    description VARCHAR(200),
    user_id INT(11),
    employee_id INT(11),
    CONSTRAINT fk_category_id
    FOREIGN KEY (category_id) REFERENCES categories(id),
    CONSTRAINT fk_status_id
    FOREIGN KEY (status_id) REFERENCES `status`(id),
    CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_employee_id
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);
-----------------------
2. Insert
INSERT INTO employees(first_name,last_name,gender,birthdate,department_id)
VALUES ('Marlo','O''Malley','M','1958-09-21',1),('Niki','Stanaghan','F',
'1969-11-26',4),('Ayrton','Senna','M','1960-03-21',9),('Ronnie','Peterson',
'M','1944-02-14',9),('Giovanna','Amati','F','1959-07-20',5);
INSERT INTO reports(category_id,status_id,open_date,close_date,description,
user_id,employee_id) VALUES (1,1,'2017-04-13',NULL,'Stuck Road on Str.133',
6,2),(6,3,'2015-09-05','2015-12-06','Charity trail running',3,5),
(14,2,'2015-09-07',NULL,'Falling bricks on Str.58',5,2),(4,3,'2017-07-03',
'2017-07-06','Cut off streetlight on Str.11',1,1);
--------------------
3. Update
UPDATE reports
SET status_id = 2
WHERE status_id=1 AND category_id=4;
----------------------
4. Delete
DELETE FROM reports
WHERE status_id=4;
----------------
5. Users by Age
SELECT username,age FROM users
ORDER BY age ASC,username DESC;
---------------
6. Unassigned Reports
SELECT description,open_date FROM reports AS r
WHERE r.employee_id IS NULL ORDER BY r.open_date ASC, r.description ASC;
-------------------
7. Employees and Reports
SELECT first_name,last_name,description,
DATE_FORMAT(open_date,'%Y-%m-%d') AS open_date
FROM employees AS e JOIN reports AS r ON r.employee_id=e.id
ORDER BY e.id ASC,r.open_date ASC,r.id ASC;
----------------
8. Most Reported Category
SELECT c.name AS `category_name`,COUNT(r.id) AS `reports_number`
FROM categories AS c JOIN reports AS r ON r.category_id=c.id
GROUP BY c.id ORDER BY COUNT(r.id) ASC, name ASC;
---------------
9. One Category Employees
SELECT c.name AS `category_name`,COUNT(e.id) AS `employees_number`
FROM categories AS c JOIN employees AS e ON e.department_id=c.department_id
GROUP BY c.id ORDER BY name ASC;
-----------------
10. Birthday Report
SELECT DISTINCT c.name AS `category_name` FROM categories AS c
JOIN reports AS r ON r.category_id=c.id JOIN users AS u
ON u.id=r.user_id WHERE DAY(r.open_date)=DAY(u.birthdate)
ORDER BY c.name ASC;