Exercises

Sistem Basis Data

Email             : putriangelina865@gmail.com
Instagram     : https://www.instagram.com/putriangelinaw
RPubs            : https://rpubs.com/putriangelinaw/

---

1 Select Some attributes of suppliers in alphabetical order!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName

2 Some attributes of suppliers in reverse alphabetical order!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName DESC

3 Some attributes of suppliers ordered by country, then by city!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY Country, City

4 All atributes of suppliers and reverse alphabetical ordered by country, then by city!

SELECT *
FROM suppliers
ORDER BY Country DESC, City DESC

5 All orders, sorted by total amount, the largest first!

SELECT *,od.Quantity
FROM orders o
JOIN orderdetails od
ON o.OrderID = od.OrderID
ORDER BY od.Quantity DESC

6 Get all but the 10 most expensive products sorted by price!

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10

7 Get the 10th to 15th most expensive products sorted by price!

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10,15;

8 List all supplier countries in alphabetical order!

SELECT DISTINCT Country
FROM suppliers
ORDER BY Country

9 Find the cheapest product and Expensive Product!

9.1 The Cheapest Product

SELECT ProductName, Unit, Price
FROM products
ORDER BY Price
LIMIT 1

9.2 The Expensive Product

SELECT ProductName, Unit, Price
FROM products
ORDER BY Price DESC
LIMIT 1

10 Find the number of Supplier USA!

SELECT *
FROM suppliers
WHERE Country='USA'

11 Compute the total Quantity of orderitem!

SELECT SUM(Quantity) AS 'Total Quantity'
FROM orderdetails

12 Compute the average UnitPrice of all product!

SELECT AVG(Price) AS 'Average Price'
FROM products

13 Get all information about customer named Thomas Hardy!

SELECT *
FROM customers
WHERE ContactName='Thomas Hardy'

14 List all customers from Spain or France!

SELECT *
FROM customers
WHERE Country='Spain' OR Country='France'

15 List all customers that are not from the USA!

SELECT *
FROM customers
WHERE Country!='USA'

16 List all orders that not between \(\$50\) and \(\$15000\)!

SELECT o.OrderDate, p.ProductID, p.ProductName, p.Price
FROM orders o
JOIN orderdetails od
ON o.OrderID=od.OrderID
JOIN products p
ON od.ProductID=p.ProductID
WHERE Price NOT BETWEEN 50 AND 15000

17 List all products between \(\$10\) and \(\$20\)

SELECT ProductName, Unit, Price
FROM products
WHERE Price BETWEEN 10 AND 20
ORDER BY Price

18 List all products not between \(\$10\) and \(\$100\) sorted by price!

SELECT *
FROM products
WHERE Price NOT BETWEEN 10 AND 100
ORDER BY Price

19 Get the list of orders and amount sold between 1996 Jan 01 and 1996 Des 31!

SELECT *
FROM orders
WHERE OrderDate BETWEEN '1996-01-01' AND '1996-12-31'

20 List all suppliers from the USA, UK, OR Japan!

SELECT *
FROM suppliers
WHERE Country='USA' OR Country='UK' OR Country='Japan'

21 List all products that are not exactly \(\$10\), \(\$20\), \(\$30\), \(\$40\), or \(\$50\)!

SELECT *
FROM products
WHERE Price NOT IN(10,20,30,40,50)

22 List all customers that are from the same countries as the suppliers!

SELECT c.CustomerName, s.SupplierName, c.Country, s.Country
FROM customers c
JOIN orders o
ON c.CustomerID=o.CustomerID
JOIN orderdetails od
ON o.OrderID=od.OrderID
JOIN products p
ON od.ProductID=p.ProductID
JOIN suppliers s
ON p.SupplierID=s.SupplierID
WHERE c.Country=s.Country

23 List all products that start with ‘Cha’ or ‘Chan’ and have one more character!

SELECT *
FROM products
WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_'

24 List all suppliers that do have a fax number!

There’s no column fax number, so we assume they don’t have fax number.

25 List all customer with average orders between \(\$1000\) and \(\$1200\) !

SELECT CustomerName, AVG(TotalAmount) AverageOrders
FROM 
(
SELECT CustomerName, SUM(od.Quantity*p.Price) TotalAmount
FROM customers c
JOIN orders o
ON c.CustomerID=o.CustomerID
JOIN orderdetails od
ON o.OrderID=od.OrderID
JOIN products p
ON od.ProductID=p.ProductID
GROUP BY od.OrderID
) A
GROUP BY CustomerName
HAVING AverageOrders
BETWEEN 1000 AND 1200
ORDER BY AverageOrders

26 List total customers in each country.

SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY Country