Task 1
SELECT city_name, COUNT(customer_id) as customer_count
FROM customers
GROUP BY city_name
ORDER BY customer_count DESC;
This query retrieves the city names and the count of customers in each city from the customers table. The results are grouped by city name and ordered in descending order based on the customer count.
Task 2
SELECT p.product_name, c.category_name, p.unit_price
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.unit_price = (SELECT MAX(unit_price) FROM products);
This query selects the product name, category name, and unit price from the products table. It joins with the categories table to include the category name and filters for the product with the highest unit price using a subquery.
Task 3
SELECT e.employee_id, e.first_name, e.last_name, COUNT(et.employee_id) as territory_count
FROM employees e
LEFT JOIN employee_territories et ON e.employee_id = et.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name
ORDER BY territory_count DESC
LIMIT 1;
This query finds the employee who manages the highest number of territories. It retrieves the employee ID, first name, last name, and the count of territories managed from the employees and employee_territories tables using a LEFT JOIN. The results are grouped by employee details, ordered by the territory count in descending order, and limited to 1 row.
Task 4
UPDATE employees
SET title = 'Dr.'
WHERE first_name = 'Robert' AND last_name = 'King';
This statement updates the title of employee Robert King to 'Dr.' in the employees table.
UPDATE products
SET unit_price = 45.00
WHERE product_name = 'Perth Pasties';
This statement updates the unit price of the product 'Perth Pasties' to 45.00 in the products table.
UPDATE products
SET unit_price = 50.00
WHERE product_name = 'Vegie-spread';
This statement updates the unit price of the product 'Vegie-spread' to 50.00 in the products table.
UPDATE products
SET unit_price = 8.50
WHERE product_name = 'Filo Mix';
This statement updates the unit price of the product 'Filo Mix' to 8.50 in the products table.
INSERT INTO employees (first_name, last_name)
VALUES ('Frodo', 'Baggins');
This statement inserts a new employee record with the first name 'Frodo' and last name 'Baggins' into the employees table.
Task 1
SELECT city_name, COUNT(customer_id) as customer_count
FROM customers
GROUP BY city_name
ORDER BY customer_count DESC;
This query retrieves the city names and the count of customers in each city from the customers table. The results are grouped by city name and ordered in descending order based on the customer count.
Task 2
SELECT p.product_name, c.category_name, p.unit_price
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.unit_price = (SELECT MAX(unit_price) FROM products);
This query selects the product name, category name, and unit price from the products table. It joins with the categories table to include the category name and filters for the product with the highest unit price using a subquery.
Task 3
SELECT e.employee_id, e.first_name, e.last_name, COUNT(et.employee_id) as territory_count
FROM employees e
LEFT JOIN employee_territories et ON e.employee_id = et.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name
ORDER BY territory_count DESC
LIMIT 1;
This query finds the employee who manages the highest number of territories. It retrieves the employee ID, first name, last name, and the count of territories managed from the employees and employee_territories tables using a LEFT JOIN. The results are grouped by employee details, ordered by the territory count in descending order, and limited to 1 row.
Task 4
UPDATE employees
SET title = 'Dr.'
WHERE first_name = 'Robert' AND last_name = 'King';
This statement updates the title of employee Robert King to 'Dr.' in the employees table.
UPDATE products
SET unit_price = 45.00
WHERE product_name = 'Perth Pasties';
This statement updates the unit price of the product 'Perth Pasties' to 45.00 in the products table.
UPDATE products
SET unit_price = 50.00
WHERE product_name = 'Vegie-spread';
This statement updates the unit price of the product 'Vegie-spread' to 50.00 in the products table.
UPDATE products
SET unit_price = 8.50
WHERE product_name = 'Filo Mix';
This statement updates the unit price of the product 'Filo Mix' to 8.50 in the products table.
INSERT INTO employees (first_name, last_name)
VALUES ('Frodo', 'Baggins');
This statement inserts a new employee record with the first name 'Frodo' and last name 'Baggins' into the employees table.