50 MySQL Practice Questions with Answers (Northwind Database)
These questions cover basic SQL operations including creating databases, tables, inserting records, and performing SELECT queries using the Northwind database. Click below to view answers.
1. Create a database named ‘northwind’.
Click to View Answer
CREATE DATABASE northwind;
2. Select the ‘northwind’ database for use.
Click to View Answer
USE northwind;
3. Create a table named ‘Customers’ with columns CustomerID, CompanyName, ContactName, and Country.
Click to View Answer
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CompanyName VARCHAR(100), ContactName VARCHAR(100), Country VARCHAR(50));
4. Insert one record into the Customers table.
Click to View Answer
INSERT INTO Customers VALUES (1, ‘Alfreds Futterkiste’, ‘Maria Anders’, ‘Germany’);
5. Create a ‘Products’ table with columns ProductID, ProductName, SupplierID, CategoryID, and UnitPrice.
Click to View Answer
CREATE TABLE Products (ProductID INT PRIMARY KEY, ProductName VARCHAR(100), SupplierID INT, CategoryID INT, UnitPrice DECIMAL(10,2));
6. Insert multiple products into the Products table.
Click to View Answer
INSERT INTO Products VALUES (1, ‘Chai’, 1, 1, 18.00), (2, ‘Chang’, 1, 1, 19.00), (3, ‘Aniseed Syrup’, 1, 2, 10.00);
7. Retrieve all records from the Customers table.
Click to View Answer
SELECT * FROM Customers;
8. Retrieve all product names and prices.
Click to View Answer
SELECT ProductName, UnitPrice FROM Products;
9. Retrieve customers who are from Germany.
Click to View Answer
SELECT * FROM Customers WHERE Country = ‘Germany’;
10. Change the UnitPrice of ‘Chai’ to 20.00.
Click to View Answer
UPDATE Products SET UnitPrice = 20.00 WHERE ProductName = ‘Chai’;
11. Delete all customers from France.
Click to View Answer
DELETE FROM Customers WHERE Country = ‘France’;
12. Count the total number of customers.
Click to View Answer
SELECT COUNT(*) FROM Customers;
13. Get all distinct countries from the Customers table.
Click to View Answer
SELECT DISTINCT Country FROM Customers;
14. Create a table named ‘Orders’ with columns OrderID, CustomerID, EmployeeID, OrderDate, and Freight.
Click to View Answer
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, EmployeeID INT, OrderDate DATE, Freight DECIMAL(10,2));
15. Insert an order into the Orders table.
Click to View Answer
INSERT INTO Orders VALUES (10248, 1, 5, ‘1996-07-04’, 32.38);
16. Retrieve all orders with Freight greater than 30.
Click to View Answer
SELECT * FROM Orders WHERE Freight > 30;
17. Create a foreign key in Orders table referencing CustomerID from Customers table.
Click to View Answer
ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
18. Retrieve customer names with their order IDs using JOIN.
Click to View Answer
SELECT c.CompanyName, o.OrderID FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;
19. List all orders sorted by OrderDate (latest first).
Click to View Answer
SELECT * FROM Orders ORDER BY OrderDate DESC;
20. Create an Employees table with columns EmployeeID, FirstName, LastName, Title, and City.
Click to View Answer
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Title VARCHAR(50), City VARCHAR(50));
21. Insert an employee record into Employees table.
Click to View Answer
INSERT INTO Employees VALUES (1, ‘Andrew’, ‘Fuller’, ‘Sales Manager’, ‘London’);
22. Retrieve all employee names and cities.
Click to View Answer
SELECT FirstName, LastName, City FROM Employees;
23. Count how many employees are from London.
Click to View Answer
SELECT COUNT(*) FROM Employees WHERE City = ‘London’;
24. Create a Suppliers table with SupplierID, CompanyName, ContactName, and Country.
Click to View Answer
CREATE TABLE Suppliers (SupplierID INT PRIMARY KEY, CompanyName VARCHAR(100), ContactName VARCHAR(100), Country VARCHAR(50));
25. Insert sample suppliers into Suppliers table.
Click to View Answer
INSERT INTO Suppliers VALUES (1, ‘Exotic Liquids’, ‘Charlotte Cooper’, ‘UK’), (2, ‘New Orleans Cajun Delights’, ‘Shelley Burke’, ‘USA’);
26. Retrieve all suppliers from the USA.
Click to View Answer
SELECT * FROM Suppliers WHERE Country = ‘USA’;
27. Retrieve product names along with supplier names.
Click to View Answer
SELECT p.ProductName, s.CompanyName FROM Products p JOIN Suppliers s ON p.SupplierID = s.SupplierID;
28. Count total number of products in each category.
Click to View Answer
SELECT CategoryID, COUNT(*) FROM Products GROUP BY CategoryID;
29. Get all orders placed by CustomerID = 1.
Click to View Answer
SELECT * FROM Orders WHERE CustomerID = 1;
30. Retrieve all customers who have placed at least one order.
Click to View Answer
SELECT DISTINCT c.CompanyName FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;
31. Find the highest product price.
Click to View Answer
SELECT MAX(UnitPrice) FROM Products;
32. Find the average freight cost of all orders.
Click to View Answer
SELECT AVG(Freight) FROM Orders;
33. Get the total number of orders handled by EmployeeID = 5.
Click to View Answer
SELECT COUNT(*) FROM Orders WHERE EmployeeID = 5;
34. Retrieve orders placed between ‘1996-07-01’ and ‘1996-07-31’.
Click to View Answer
SELECT * FROM Orders WHERE OrderDate BETWEEN ‘1996-07-01’ AND ‘1996-07-31’;
35. Create an OrderDetails table with OrderID, ProductID, Quantity, and UnitPrice.
Click to View Answer
CREATE TABLE OrderDetails (OrderID INT, ProductID INT, Quantity INT, UnitPrice DECIMAL(10,2));
36. Insert a record into OrderDetails.
Click to View Answer
INSERT INTO OrderDetails VALUES (10248, 1, 12, 14.00);
37. Calculate total revenue for each product.
Click to View Answer
SELECT ProductID, SUM(Quantity * UnitPrice) AS TotalRevenue FROM OrderDetails GROUP BY ProductID;
38. Show top 3 most expensive products.
Click to View Answer
SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC LIMIT 3;
39. Retrieve products with UnitPrice greater than average price.
Click to View Answer
SELECT * FROM Products WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);
40. Create a view showing product names and supplier names.
Click to View Answer
CREATE VIEW ProductSupplierView AS SELECT p.ProductName, s.CompanyName FROM Products p JOIN Suppliers s ON p.SupplierID = s.SupplierID;
41. Retrieve all data from the view created above.
Click to View Answer
SELECT * FROM ProductSupplierView;
42. Get the number of products supplied by each supplier.
Click to View Answer
SELECT SupplierID, COUNT(ProductID) AS TotalProducts FROM Products GROUP BY SupplierID;
43. Retrieve product names starting with ‘C’.
Click to View Answer
SELECT ProductName FROM Products WHERE ProductName LIKE ‘C%’;
44. Retrieve customers whose contact name contains ‘Maria’.
Click to View Answer
SELECT * FROM Customers WHERE ContactName LIKE ‘%Maria%’;
45. Get the total number of suppliers.
Click to View Answer
SELECT COUNT(*) FROM Suppliers;
46. Find all orders that have Freight greater than 50.
Click to View Answer
SELECT * FROM Orders WHERE Freight > 50;
47. List all employees who are not from London.
Click to View Answer
SELECT * FROM Employees WHERE City != ‘London’;
48. Delete all discontinued products (if a column exists).
Click to View Answer
DELETE FROM Products WHERE Discontinued = 1;
49. Display customer names in ascending order.
Click to View Answer
SELECT CompanyName FROM Customers ORDER BY CompanyName ASC;
50. Retrieve the total revenue generated from all orders.
Click to View Answer
SELECT SUM(UnitPrice * Quantity) AS TotalRevenue FROM OrderDetails;
