SQL Exercise 1 Name Student No

Sql Exercise 1name Student No

SQL Exercise 1: Name___________________Student No._______________________ Go to the following link and type SQL statements to finish the following questions. 1) List full details of all customer whose country is UK. 2) List all products’ ID with their supplier ID and category ID. 3) List all shipper IDs in all orders. 4) List all shipper IDs in all orders, with duplicates eliminated. 5) Display the total quantity of product 51 in all orders. 6) List IDs and names for all customers whose first name in their contact name is ‘Elizabeth’. 7) List all suppliers in UK or USA. 8) List the IDs of all orders whose quantity is no less than 40. 9) List all products, arranged in descending order of price. 10) Produce a list of orders in order of quantity. If quantity is same for some orders, then sort by product ID. 11) Find how many products whose ID is 51 are ordered totally. 12) Find how many orders that ordered product 71. 13) Find the average price of the products in category 2. 14) Find how many products whose prices is between 10 and 30, and the sum of their prices. 15) Find the number of orders placed in August 1996. 16) List the details of the most expensive product in category 2. 17) Find the highest price, the lowest price, and the average price of products in category 3. 18) Find the number of USA suppliers. 19) Find how many orders are shipped by shipper 2 in 1997. 20) Find the details of the order that has the minimum quantity.

Paper For Above instruction

Introduction

Structured Query Language (SQL) is essential for managing and querying relational databases. It allows users to perform data retrieval, updates, and management tasks efficiently. The following exercises illustrate fundamental SQL query techniques, focusing on retrieving specific data based on varied conditions across different database tables related to customers, products, orders, suppliers, and shippers.

Exercise 1: Listing Customer Details from the UK

The first task requires retrieving complete information of customers whose country is the UK. Using the 'Customers' table, the SQL statement employs the SELECT statement combined with the WHERE clause to filter records based on country. This demonstrates the basic query structure for filtering data from a specific nation.

Exercise 2: Listing Product IDs with Supplier and Category IDs

This exercise involves selecting specific columns, namely product ID, supplier ID, and category ID from the 'Products' table. It highlights the importance of precise column selection in SQL queries for extracting relevant data efficiently, often used for inventory and categorization analysis.

Exercise 3 & 4: Extracting Shipper IDs from Orders with and without Duplicates

Listing all shipper IDs associated with orders involves querying the 'Orders' table. To eliminate duplicate entries, the DISTINCT keyword is used, illustrating how to derive unique values from potentially repetitive data. These tasks are essential for understanding distribution channels and logistics.

Exercise 5: Total Quantity of a Specific Product

Calculating the total ordered quantity of product 51 utilizes the SUM aggregate function combined with WHERE to filter for the specific product. This approach is critical in sales and inventory management for tracking product performance.

Exercise 6: Customers Contact Name Analysis

Filtering customers based on the first name ‘Elizabeth’ within their contact names demonstrates string matching capabilities in SQL, utilizing the LIKE operator. It is helpful for targeted marketing or customer segmentation.

Exercise 7: Suppliers from UK or USA

Using the IN clause, this query retrieves suppliers located in either of the two countries. This exemplifies selecting data based on multiple conditions, common in regional reporting.

Exercise 8: Orders with Quantity >= 40

Order IDs with quantities less than or equal to 40 are selected with a simple comparison operator, illustrating range-based filtering used in inventory and order management.

Exercise 9: Listing Products Ordered by Price

Ordering products in descending order of price utilizes the ORDER BY clause with DESC. Sorting data helps in pricing analysis and identifying premium products.

Exercise 10: Orders Sorted by Quantity and Product ID

This task involves multi-level sorting, first by quantity, then by product ID if quantities are equal, demonstrating complex ordering for better data analysis.

Exercise 11 & 12: Counting Specific Product Orders

Using COUNT with filtering conditions, these queries find the total number of times a specific product (ID 51 and 71) has been ordered, useful for demand forecasting.

Exercise 13: Average Price in a Category

Calculating the average price of products within category 2 employs the AVG aggregate function, which aids in pricing strategy and market analysis.

Exercise 14: Count and Sum of Product Prices Between Ranges

These queries involve filtering products based on price ranges and calculating the total sum, important for assessing product value and profit margins.

Exercise 15: Orders in August 1996

Filtering by date component demonstrates date functions and filtering orders within a specific month and year, vital for temporal sales analysis.

Exercise 16: Most Expensive Product in Category 2

Identifying the product with the maximum price in a category employs the MAX function, assisting in luxury segment analysis.

Exercise 17: Price Extremes and Averages in Category 3

Calculating maximum, minimum, and average prices within a category provides a comprehensive pricing overview.

Exercise 18: Number of Suppliers in the USA

Counting suppliers located in the USA involves COUNT with a specific filter, useful for regional supplier analysis.

Exercise 19: Orders Shipped by Shipper 2 in 1997

This query filters orders based on shipper ID and date, covering logistics performance over a specific year.

Exercise 20: Order with Minimum Quantity

Finding the order with the least quantity uses the MIN function, useful for identifying low-volume or special orders.

Conclusion

These exercises comprehensively cover basic to advanced SQL querying techniques crucial for effective data management, analysis, and reporting in relational databases. Mastery of these commands enables database administrators, analysts, and developers to retrieve and manipulate data accurately for decision-making processes.

References

• Elmasri, R., & Navathe, S. B. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Pearson Education.
• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.