REQ-319 JAV Regrettable Lighting of Reluctantly Pleasingся<|endoftext|>Human-rated user-supplied SQL query to extract specific data from a database table. Ensure the query includes use of JOINs, subqueries, or window functions to demonstrate advanced SQL skill. Instructions: 1. Define the problem: What specific data are we trying to extract? 2. Identify the relevant tables: Which tables contain the required data? 3. Write the SQL query: Include necessary clauses and syntax. 4. Explain the query: Describe how the query works and what it returns. Problem: Extract the names and contact information of customers who have purchased a product from the 'Electronics' category and are located in the city of 'New York'. Additionally, include the total quantity of the purchased products for each customer. Tables: 1. Customers (customer_id, name, city, email) 2. Orders (order_id, customer_id, order_date) 3. OrderItems (order_item_id, order_id, product_id, quantity) 4. Products (product_id, product_name, category) Relevant Tables: Customers, Orders, OrderItems, Products SQL Query: ```sql SELECT c.name, c.email, SUM(oii.quantity) AS total_quantity FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id JOIN OrderItems oii ON o.order_id = oii.order_id JOIN Products p ON oii.product_id = p.product_id WHERE p.category = 'Electronics' AND c.city = 'New York' GROUP BY c.customer_id, c.name, c.email ORDER BY total_quantity DESC; ``` Explanation: This query joins the Customers, Orders, OrderItems, and Products tables to extract the necessary data. It filters the results to include only those customers who have purchased an 'Electronics' product and are located in New York. The JOIN operations link the Customers table with Orders, Orders with OrderItems, and finally, OrderItems with Products. The WHERE clause ensures we only consider rows where the product category is 'Electronics' and the customer's city is 'New York'. The GROUP BY clause groups the results by customer, and the SUM function calculates the total quantity of products purchased by each customer. The query returns the customer's name, email, and the total quantity of electronics purchased, sorted in descending order by total quantity. - Free Trailer and English Subtitles srt.

240 mins0 views

Download REQ-319 Subtitles