Mastering SQL Through Practice: Exploring Sylvia Moestl Vasilik’s Exercises — Part 3
Previously, we tackled MySQL problems that were introductory in nature. In this part 2 of Basic MySQL, we’ll solve more interesting problems. If you haven't gone through Basic SQL Queries (Part 1), here's the link.
Contents:
8. Which orders are shipping to France or Belgium?
9. Orders shipping to any country in Latin America.
10. Employees, in order of age.
11. Showing only the Date with a DateTime field
12. Employees full name — Join Fname + Lname.
13. OrderDetails amount per line item.
14. How many customers in one table?
In the below exercise we will try to reterive various attributes of a shipment ordered to France or Belgium using Order table.
Problem 8: From the Orders table, using field called ShipCountry. Write a query that shows the OrderID, CustomerID, and ShipCountry for the orders where the ShipCountry is either France or Belgium.
Solution Description: In the where clause , instead of combining the filters with an “and” use the “or”.
My SQL Code: Select OrderID, CustomerID, ShipCountry from Orders
where ShipCountry = ‘France’ or ShipCountry = ‘Belgium’;
In practicle senario, we would require to retrieve data from mutiple countries. Below problem based on such scenarios.
Problem 9: Lets retrieve data for orders from Latin American country. Since we we don’t have a list of Latin American countries in a table in the Northwind database. So, we’re going to just use this list of Latin American countries that happen to be in the Orders table: Brazil, Mexico, Argentina, Venezuela.
Solution Description: Here’s an example of the previous questions, about orders shipping to France or Belgium, done as an In statement instead of using multiple Where clauses.
MySQL Code: Select OrderID, CustomerID, ShipCountry from Orders where ShipCountry in (‘Brazil’, ‘Mexico’, ‘Argentina’, ‘Venezuela’);
Note: If you have noticed that we haven’t used “or” keyword. In case of mutiple “or”, its recommended to use “In” statement.
When we handle real life data with SQL, it is require to sort result in ascending and descending order for better analysis and visibility. Below problem shows how to do it.
Problem 10: For all the employees in the Employees table, show the FirstName, LastName, Title, and BirthDate. Order the results by BirthDate, so we have the oldest employees first.
Solution Description: We have used the Order by clause for sorting the results. ASC is used for the Ascending Order.
My SQL Code: Select FirstName, LastName, Title, BirthDate from employes
order by BirthDate Asc;
Now we use special function to redefine the stored values in different format such as time format to date format. We will use the same above example.
Problem 11: In the output of the query above, showing the Employees in order of BirthDate, we see the time of the BirthDate field, which we don’t want. Show only the date portion of the BirthDate field.
Solution Description: We have used the “Cast” function to convert the BirthDate column (originally a DateTime column) to a Date column.
MySQL Code: Select FirstName, LastName, Cast(BirthDate as date)
from employees order by BirthDate;
Its is necessary to learn how to modify or add an additional column using the data in the existing columns. Below few problems are based on such requirement.
Problem 12: Show the FirstName and LastName columns from the Employees table, and then create a new column called FullName, showing FirstName and LastName joined together in one column, with a space in-between.
Solution Description: Joining two fields like this is called concatenation. Here we have used the concat() function.
MySQL Code: Select * , concat(FirstName, Space(1), LastName) as FullName from employees;
Problem 13: In the OrderDetails table, we have the fields UnitPrice and Quantity. Create a new field, TotalPrice, that multiplies these two together. We’ll ignore the Discount field for now. In addition, show the OrderID, ProductID, UnitPrice, and Quantity. Order by OrderID and ProductID.
Description: In this computed column, you need to use the arithmetic operator for multiplication. We use “as” to give a uinque name to the resulted column.
My SQL Code: Select *, OrderID, ProductID, UnitPrice, Quantity, UnitPrice*Quantity as TotalPrice from orderdetails;
We uses mutiple commands to use various mathemathical funcations such as Min, Max, Avg, Count, Distinct Count, etc. Below problem uses the count option.
Problem 14: Let us reterieve total count of customers we have on the Customers table. We would like to see one value only, and wouldn’t want to rely on getting the record count at the end of a resultset.
Solution Description: In order to get the total number of customers, we need to use count() function.
My SQL Code: Select count(CustomerID) as TotalCustomers from customers;
Like count(), we can use other funcations in order to perform different arithmetic operations.
