DBMS - Select - 2

  • 1.	selects all customers that are located in "Germany", "France" or "UK":

					    						

    	SELECT * FROM Customers
	WHERE Country IN ('Germany', 'France', 'UK');
						

  • 2.	selects all customers that are NOT located in "Germany", "France" or "UK":

					    						

    	
	SELECT * FROM Customers
	WHERE Country NOT IN ('Germany', 'France', 'UK');
						

  • 3.	selects all customers that are from the same countries as the suppliers.

					    						

    	SELECT * FROM Customers
	WHERE Country IN (SELECT Country FROM Suppliers);
						

  • 4.	selects all products with a price BETWEEN 10 and 20:

					    						

    	SELECT * FROM Products
	WHERE Price BETWEEN 10 AND 20;
						

  • 	
5.	To display the products outside the range of the previous example, 
	use NOT BETWEEN:

					    						

    	SELECT * FROM Products
	WHERE Price NOT BETWEEN 10 AND 20;
						

  • 	
6.	selects all products with a price BETWEEN 10 and 20. 
	In addition; do not show products with a CategoryID of 1,2, or 3:

					    						

    	
	SELECT * FROM Products
	WHERE price between 10 and 20 and 
	cat_id not in (1,2,3);
						

  • 7.	selects all products with a ProductName BETWEEN Carnarvon Tigers and 
	Chef Anton's Cajun Seasoning.

					    						

    	SELECT * FROM Products
	WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
	ORDER BY ProductName;
						

  • 	
8.	selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996'

					    						

    	
	SELECT * FROM Orders
	WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
						

  • 9.	lists the number of customers in each country.

					    						

    	SELECT COUNT(CustomerID), Country
	FROM Customers
	GROUP BY Country;
						

  • 10.	lists the number of customers in each country, sorted high to low:

					    						

    	SELECT COUNT(CustomerID), Country
	FROM Customers
	GROUP BY Country
	ORDER BY COUNT(CustomerID) DESC;
						

  • 11.	lists the number of customers in each country. 
	Only include countries with more than 5 customers.

					    						

    	
	SELECT COUNT(CustomerID), Country
	FROM Customers
	GROUP BY Country
	HAVING COUNT(CustomerID) > 5;
						

  • 	
12.	lists the number of customers in each country, 
	sorted high to low (Only include countries with more than 5 customers):

					    						

    	SELECT COUNT(CustomerID), Country
	FROM Customers
	GROUP BY Country
	HAVING COUNT(CustomerID) > 5
	ORDER BY COUNT(CustomerID) DESC;