SQL Practice Queries - 2

SQL stands for Structured Query Language, and it is a language used to manage and manipulate data in relational databases. SQL is a widely used language in the world of data and is essential for anyone who wants to work with databases.

Today, I have crafted an another intricate SQL question that can prove to be beneficial in enhancing your SQL query formulation skills, as well as equipping you with the necessary expertise to tackle SQL interview questions. This question is designed to challenge your understanding of SQL concepts and command structures, enabling you to gain a more in-depth understanding of this powerful database management language. By attempting this question, you can refine your SQL proficiency and discover new strategies to solve complex problems. This advanced level SQL question will help you to build confidence in your abilities and improve your overall proficiency in SQL.

Assume you have a table called factory and shoe with the following columns:

factory Table Structure

shoe Table Structure

Here is the data for based on the above table structure.

factory Table data

shoe Table data

Here's the code below for you to create a table and insert data into it as a way to practice.

To Create factory Table

CREATE TABLE factory (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    location VARCHAR(50) NOT NULL,
    size INT NOT NULL
);

To Insert data into factory table

INSERT INTO factory (id, name, location, size) VALUES
(1, 'Nike Factory', 'Portland, OR', 50000),
(2, 'Adidas Factory', 'Berlin, Germany', 75000),
(3, 'New Balance Factory', 'Boston, MA', 40000),
(4, 'Puma Factory', 'Tokyo, Japan', 60000),
(5, 'Reebok Factory', 'Mumbai, India', 55000),
(6, 'Gucci Factory', 'Florence, Italy', 65000);

To Create shoe Table

CREATE TABLE shoe (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    color VARCHAR(20) NOT NULL,
    size INT NOT NULL,
    price DECIMAL(6, 2) NOT NULL,
    factory_id INT NOT NULL,
    FOREIGN KEY (factory_id) REFERENCES factory (id)
);

To Insert data into shoe table

INSERT INTO shoe (id, name, color, size, price, factory_id) VALUES
(1, 'Athletic', 'Red', 10, 75.00, 1),
(2, 'Dress', 'Black', 8, 100.00, 1),
(3, 'Casual', 'Brown', 11, 50.00, 2),
(4, 'Running', 'Blue', 9, 85.00, 2),
(5, 'Sandals', 'Beige', 7, 25.00, 3),
(6, 'Sneakers', 'White', 10, 60.00, 3),
(7, 'Hiking', 'Green', 12, 120.00, 4),
(8, 'Boots', 'Tan', 9, 90.00, 4),
(9, 'Flip Flops', 'Blue', 6, 15.00, 5),
(10, 'Loafers', 'Burgundy', 9, 80.00, 5),
(11, 'High Heels', 'Red', 7, 150.00, 6),
(12, 'Platform Shoes', 'Black', 8, 200.00, 6);

Question 1

Write a sql query to find the number of shoes each factory has produced, and sort the results in descending order?

Output should be in two columns factory_name, shoe_count

NOTE: Prior to reading the solution, it is suggested that you attempt to solve the problem independently.

Answer

SELECT f.name AS factory_name, COUNT(s.id) AS shoe_count
FROM factory f
LEFT JOIN shoe s
ON f.id = s.factory_id
GROUP BY f.name
ORDER BY shoe_count DESC;

This query uses a LEFT JOIN to ensure that factories without any shoe production are still included in the results. We alias the name column of the factory table as factory_name to avoid ambiguity with the name column of the shoe table. The COUNT function is used to count the number of shoes for each factory, and the results are sorted in descending order by shoe count.

The output table for this query would look something like this:

This shows the number of shoes produced by each factory, sorted by the number of shoes in descending order.

Question 2

Write an sql query to find the average price of shoes produced by each factory, along with the highest and lowest shoe prices produced by each factory.

the output column should be factory_name, avg_price, highest_price and lowest_price.

NOTE: Prior to reading the solution, it is suggested that you attempt to solve the problem independently.

Answer

To solve this problem, we'll need to use the AVG, MAX, and MIN functions to calculate the average, highest, and lowest shoe prices for each factory. We can do this by using a JOIN to combine the shoe and factory tables, and then grouping the results by factory.

Here's the SQL query:

SELECT 
  f.name AS factory_name, 
  AVG(s.price) AS avg_price,
  MAX(s.price) AS highest_price,
  MIN(s.price) AS lowest_price
FROM factory f
LEFT JOIN shoe s ON f.id = s.factory_id
GROUP BY f.name;

This query uses a LEFT JOIN to ensure that factories without any shoe production are still included in the results. We then use the AVG, MAX, and MIN functions to calculate the average, highest, and lowest shoe prices for each factory, respectively. The results are grouped by the name column of the factory table.

The output table for this query would look like this:

This shows the average, highest, and lowest shoe prices for each factory, which can provide valuable insights into the pricing strategies of each factory.

Question 3

Write a SQL query to find the top 3 factories that produce the most expensive shoes, along with the number of shoe color produced by each factory and the total revenue generated by each factory.

the output column should be factory_name, num_models and total_revenue.

NOTE: Prior to reading the solution, it is suggested that you attempt to solve the problem independently.

To solve this problem, we'll need to use a combination of JOIN, GROUP BY, and ORDER BY clauses to find the top 3 factories that produce the most expensive shoes, along with the number of shoe color produced by each factory and the total revenue generated by each factory.

Here's the SQL query:

SELECT 
  f.name AS factory_name, 
  COUNT(DISTINCT s.color) AS num_models,
  SUM(s.price) AS total_revenue
FROM factory f
LEFT JOIN shoe s ON f.id = s.factory_id
GROUP BY f.name
HAVING COUNT(DISTINCT s.color) > 0
ORDER BY MAX(s.price) DESC
LIMIT 3;

Answer

This query uses a LEFT JOIN to ensure that factories without any shoe production are still included in the results. We then use the COUNT function to count the number of distinct shoe models produced by each factory, and the SUM function to calculate the total revenue generated by each factory.

The results are grouped by the name column of the factory table and sorted in descending order by the maximum shoe price. We use the HAVING clause to ensure that only factories with at least one shoe model produced are included in the results.

The output table for this query would look something like this:

This shows the top 3 factories that produce the most expensive shoes, along with the number of shoe color produced by each factory and the total revenue generated by each factory.

I hope these examples helps you practice and develop your SQL skills!