SQL Query Explorations

SQL Query Explorations

watch the video explanation here

1. Categories by Name

SELECT category_name, description FROM categories ORDER BY category_name;

This query retrieves two columns: category_name and description from the categories table. By using ORDER BY category_name, it ensures the results are sorted alphabetically by the category name. This query is useful for getting an overview of the available categories and their descriptions in an organized manner.

2. Customers Outside Specific Countries

SELECT contact_name, address, city FROM customers WHERE country NOT IN ('Germany', 'Mexico', 'Spain');

Here, the objective is to list the contact_name, address, and city of all customers not located in Germany, Mexico, or Spain. The WHERE clause with NOT IN is used to exclude customers from these countries, focusing on the global reach of Northwind's clientele beyond these locations.

3. Orders Placed on a Specific Date

SELECT order_date, shipped_date, customer_id, freight FROM orders WHERE order_date = '2018-02-26';

This query aims to find orders placed on February 26, 2018, showcasing the use of a specific order_date in the WHERE clause to filter results. It provides insights into the shipping details, including the shipped_date, customer_id, and freight costs associated with these orders.

4. Late Shipments

SELECT employee_id, order_id, customer_id, required_date, shipped_date FROM orders WHERE shipped_date > required_date;

To identify delays in the shipping process, this query selects orders where the shipped_date is later than the required_date. By retrieving the employee_id, order_id, and customer_id, along with the dates, it helps in pinpointing the orders that did not meet the shipping deadlines, potentially indicating areas for operational improvement.

5. Even Numbered Orders

SELECT order_id FROM orders WHERE mod(order_id, 2) = 0;

Focusing on orders with even-numbered IDs, this query uses the mod(order_id, 2) = 0 condition to filter for orders where the ID is divisible by 2 with no remainder. It's a simple way to select a subset of orders based on a numerical pattern.

6. Customers in 'L' Cities

SELECT city, company_name, contact_name FROM customers WHERE city LIKE '%L%' ORDER BY contact_name;

This query searches for customers located in cities containing the letter 'L', using the LIKE '%L%' pattern. The results are sorted by contact_name, highlighting Northwind's customer distribution in these specific geographical areas.

7. Customers with Fax Numbers

SELECT company_name, contact_name, fax FROM customers WHERE fax IS NOT NULL;

By selecting customers who have a fax number (fax IS NOT NULL), this query identifies those reachable via fax. It retrieves the company_name, contact_name, and fax number, focusing on the mode of communication available for these customers.

8. Most Recently Hired Employee

SELECT first_name, last_name, max(hire_date) AS hire_date FROM employees;

To find the most recently hired employee, this query selects the maximum hire_date from the employees table. It provides the first_name and last_name of the employee who was the last addition to the Northwind team, offering insights into the company's hiring timeline.

9. Product Inventory Analysis

SELECT round(avg(unit_price), 2) AS avg_price, SUM(units_in_stock) as total_stock, SUM(discontinued) AS total_discontinued FROM products;

This query calculates the average unit_price (rounded to two decimal places), the total units_in_stock, and the total number of discontinued products from the products table. It gives a comprehensive overview of product pricing, stock levels, and the extent of product discontinuation within Northwind's inventory.

If you would like to connect on Linkedin