Answer-1
CREATE DATABASE StoreDB;
USE StoreDB;
CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL,
Country VARCHAR(100)
);
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL
);
CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
OrderDate DATE NOT NULL,
CustomerID INT,
ProductID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
-- Insert sample customers
INSERT INTO Customers (CustomerName, Country) VALUES
('John Doe', 'USA'),
('Jane Smith', 'Canada'),
('Alice Johnson', 'UK'),
('Bob Brown', 'USA');
-- Insert sample products
INSERT INTO Products (ProductName, Price) VALUES
('Laptop', 999.99),
('Smartphone', 599.99),
('Tablet', 399.99),
('Monitor', 199.99);
-- Insert sample orders
INSERT INTO Orders (OrderDate, CustomerID, ProductID) VALUES
('2023-01-10', 1, 1), -- John Doe orders Laptop
('2023-02-15', 1, 2), -- John Doe orders Smartphone
('2023-03-20', 2, 3), -- Jane Smith orders Tablet
('2023-04-25', 4, 4); -- Bob Brown orders Monitor
1)
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Customers c
JOIN Orders o ON [Link] = [Link];
2)
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link];
3)
SELECT [Link], [Link], [Link]
FROM Orders o
LEFT JOIN Customers c ON [Link] = [Link];
4)
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
UNION ALL
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Orders o
LEFT JOIN Customers c ON [Link] = [Link]
WHERE [Link] IS NULL;
5)
SELECT [Link], [Link], SUM([Link]) AS TotalAmountSpent
FROM Customers c
JOIN Orders o ON [Link] = [Link]
JOIN Products p ON [Link] = [Link]
GROUP BY [Link], [Link];