Suppose you are managing an e-commerce database for a company that sells products globally. The company's database schema includes multiple tables: Orders, Customers, Products, and OrderDetails. Your task is to extract information about orders that were placed for a specific product category, within a certain price range, and between two specific dates. Additionally, you need to include customer information and handle translations for product names.
Database Schema:
Orders:
OrderID (Primary Key)
CustomerID (Foreign Key)
OrderDate
Customers:
CustomerID (Primary Key)
CustomerName
Country
Products:
ProductID (Primary Key)
ProductName_EN (Product name in English)
ProductName_FR (Product name in French)
CategoryID (Foreign Key)
UnitPrice
OrderDetails:
OrderDetailID (Primary Key)
OrderID (Foreign Key)
ProductID (Foreign Key)
Quantity
Question:
Write an SQL query to retrieve the following information:
OrderID, OrderDate, CustomerName, Country, ProductName (in English), UnitPrice, and TotalPrice (Quantity * UnitPrice) for orders placed for products in the 'Electronics' category, with a UnitPrice between £500 and £1000, and OrderDate between 1st January 2023 and 31st March 2023.
Ensure that you handle translations for the product names, displaying them in English. If a product name is not available in English, display the French name. Include all orders, even if there are no details (i.e., use LEFT JOIN).
