SELECT paired with the noble WHERE clause. Here are 50 MySQL practice exercises that use only SELECT with WHERE operators. No joins, no groupings—just pure filtering logic. This will hone your instinct like a whetstone sharpens a blade.
🔹 Basic Comparisons
-
Select customers from the USA.
SELECT * FROM Customers WHERE Country = 'USA'; -
List products with price > 100.
SELECT * FROM Products WHERE UnitPrice > 100; -
Show employees hired before 1995.
SELECT * FROM Employees WHERE HireDate < '1995-01-01'; -
Get orders shipped after 1997-01-01.
SELECT * FROM Orders WHERE ShippedDate > '1997-01-01'; -
Products with stock less than 10.
SELECT * FROM Products WHERE UnitsInStock < 10;
🔹 Logical Operators (AND, OR, NOT)
-
Customers from France and Paris.
SELECT * FROM Customers WHERE Country = 'France' AND City = 'Paris'; -
Suppliers from Germany or Japan.
SELECT * FROM Suppliers WHERE Country = 'Germany' OR Country = 'Japan'; -
Products not discontinued.
SELECT * FROM Products WHERE Discontinued = 0; -
Orders not shipped to Brazil.
SELECT * FROM Orders WHERE ShipCountry != 'Brazil'; -
Employees not from USA or UK.
SELECT * FROM Employees WHERE NOT (Country = 'USA' OR Country = 'UK');
🔹 LIKE and Pattern Matching
-
Customer names starting with 'A'.
SELECT * FROM Customers WHERE CustomerName LIKE 'A%';
-
Products containing 'chocolate'.
SELECT * FROM Products WHERE ProductName LIKE '%chocolate%';
-
Contact names ending in 'son'.
SELECT * FROM Customers WHERE ContactName LIKE '%son';
-
City names with 5 characters.
SELECT * FROM Customers WHERE City LIKE '_____';
-
Customers whose name includes two 'e's.
SELECT * FROM Customers WHERE CustomerName LIKE '%e%e%';
🔹 IN, NOT IN
-
Customers from UK, USA, or France.
SELECT * FROM Customers WHERE Country IN ('UK', 'USA', 'France');
-
Products in categories 1, 3, 5.
SELECT * FROM Products WHERE CategoryID IN (1, 3, 5);
-
Employees not in titles 'Sales Rep', 'Manager'.
SELECT * FROM Employees WHERE Title NOT IN ('Sales Representative', 'Manager');
-
Orders not to countries Canada, Brazil.
SELECT * FROM Orders WHERE ShipCountry NOT IN ('Canada', 'Brazil');
-
Customers not from Berlin, Madrid, or Rome.
SELECT * FROM Customers WHERE City NOT IN ('Berlin', 'Madrid', 'Rome');
🔹 BETWEEN, NOT BETWEEN
-
Products priced between 20 and 50.
SELECT * FROM Products WHERE UnitPrice BETWEEN 20 AND 50;
-
Orders shipped between Jan and Mar 1997.
SELECT * FROM Orders WHERE ShippedDate BETWEEN '1997-01-01' AND '1997-03-31';
-
Employees born between 1960 and 1970.
SELECT * FROM Employees WHERE BirthDate BETWEEN '1960-01-01' AND '1970-12-31';
-
Products with stock not between 10 and 100.
SELECT * FROM Products WHERE UnitsInStock NOT BETWEEN 10 AND 100;
-
Freight charges between 50 and 200.
SELECT * FROM Orders WHERE Freight BETWEEN 50 AND 200;
🔹 NULL and NOT NULL
-
Customers without fax numbers.
SELECT * FROM Customers WHERE Fax IS NULL;
-
Orders that have no shipped date yet.
SELECT * FROM Orders WHERE ShippedDate IS NULL;
-
Employees with extension number.
SELECT * FROM Employees WHERE Extension IS NOT NULL;
-
Products that have no reorder level set.
SELECT * FROM Products WHERE ReorderLevel IS NULL;
-
Suppliers without homepages.
SELECT * FROM Suppliers WHERE HomePage IS NULL;
🔹 Date Functions with WHERE
-
Orders placed in 1996.
SELECT * FROM Orders WHERE YEAR(OrderDate) = 1996;
-
Orders shipped in December.
SELECT * FROM Orders WHERE MONTH(ShippedDate) = 12;
-
Employees born in April.
SELECT * FROM Employees WHERE MONTH(BirthDate) = 4;
-
Customers registered before 1990 (assuming a field).
SELECT * FROM Customers WHERE YEAR(RegistrationDate) < 1990;
-
Orders on weekends (MySQL 8+).
SELECT * FROM Orders WHERE DAYOFWEEK(OrderDate) IN (1, 7);
🔹 Complex WHERE with AND/OR
-
Products under $10 and in stock.
SELECT * FROM Products WHERE UnitPrice < 10 AND UnitsInStock > 0;
-
Orders shipped to Germany or France in 1998.
SELECT * FROM Orders
WHERE YEAR(ShippedDate) = 1998 AND ShipCountry IN ('Germany', 'France');
-
Customers from UK with fax.
SELECT * FROM Customers WHERE Country = 'UK' AND Fax IS NOT NULL;
-
Orders to USA in Dec 1997.
SELECT * FROM Orders
WHERE ShipCountry = 'USA' AND OrderDate BETWEEN '1997-12-01' AND '1997-12-31';
-
Employees not from USA hired after 1990.
SELECT * FROM Employees WHERE Country != 'USA' AND YEAR(HireDate) > 1990;
🔹 Field-Based Conditions
-
Products where units in stock < reorder level.
SELECT * FROM Products WHERE UnitsInStock < ReorderLevel;
-
Orders where freight > total price of products (if custom field exists).
SELECT * FROM Orders WHERE Freight > 100;
-
Orders where required date < shipped date.
SELECT * FROM Orders WHERE RequiredDate < ShippedDate;
-
Products with more units on order than in stock.
SELECT * FROM Products WHERE UnitsOnOrder > UnitsInStock;
-
Employees with more than 10 years in company.
SELECT * FROM Employees WHERE YEAR(CURDATE()) - YEAR(HireDate) > 10;
🔹 Case-Specific Checks
-
City names in lowercase.
SELECT * FROM Customers WHERE BINARY City = LOWER(City);
-
Phone numbers that start with '('.
SELECT * FROM Customers WHERE Phone LIKE '(%';
-
Company names that include numbers.
SELECT * FROM Customers WHERE CompanyName REGEXP '[0-9]';
-
Postal codes of exact 5 digits.
SELECT * FROM Customers WHERE PostalCode REGEXP '^[0-9]{5}$';
-
Employee title that starts with ‘Sales’.
SELECT * FROM Employees WHERE Title LIKE 'Sales%';