Mastering SQL Through Practice: Exploring Sylvia Moestl Vasilik’s Exercises — Part 2
MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data. It is one of the most widely used databases in the world and is known for its speed, scalability, and reliability.
MySQL can be used for a variety of purposes, including storing data for websites and web applications, managing data for business applications, and more. It allows users to create, modify, and manage tables, columns, and relationships between data in a simple and efficient manner.
MySQL is supported on multiple platforms, including Windows, Linux, and macOS, and it is available in both community and enterprise editions.
We have solved few introductory problems for anyone who has recently started learning SQL and want to gain hands on experience on real life like data. We have also provided the solution along with each task, so if you get stuck in any of it, we can help you to successfully solve all of the queries. If you haven’t already installed SQL Workbench, go to my previous blog where I have explained in step by step manner to install the same.
Contents:
Which shippers do we have?
Certain fields from Categories.
Which employees are Sales Representatives?
Sales Representatives in the United States.
Orders placed by specific EmployeeID.
Whose Contact Title is not a Marketing Manager?
Products with “queso” in ProductName.
Let us take an sample database called Shippers, which has a table called Shippers with various details. Here is the first MySQL problem for you.
Problem 1: We have a table called Shippers. Write an MySQL query which can return all the fields from all the shippers table.
Solution Description: The standard format for a select statement that returns all columns and all rows is using * (Asterisk).
MySQL Code: Select * from TableName.
Let us consider an another table called Categories for the same Relational Database. Try to solve the below given problem.
As we have seen in problem 1, to get all columns and rows we uses * with the Select keyword. How about if we need to retrive only limited information from the table. Here is the Problem 2 for you,
Problem 2: In the Categories table, select two columns called CategoryName and Description.
Solution Description: Instead of * in the Select statement, specify the column names with a comma between them.
MySQL Code: Select CategoryName, Description from Categories;
Point to remember: SQL is not a case sensitive language, however to maintain coding hygine it is advisable to use the Column / Table name starting with capital letter.
In next problem we will use another table called Employees.
Problem 3: We’d like to retrive information such as FirstName, LastName, and HireDate of all the employees who are Sales Representative as mentioned in Title column of the employees table.
Solution Description:To filter out certain rows from a table, we use Where clause on column. Syntax for where case is: where FieldName = ‘Filter Text’
MySQL Code: Select FirstName, LastName, HireDate from employees where Title= ‘Sales Representative’;
Problem 4: Let us reterive the same attributes as above, but only for those employees who are Sales Representative and are in the United States as well.
Solution Description: To apply multiple filters in a where clause, we use “and” to separate the filters.
MySQL Code: Select FirstName, LastName, HireDate from employees
where Title = “Sales Representative” and Country = “USA”;
In the next problem we will use another table called Orders.
Problem 5: We want to reterive the information about all the orders placed by an employee who has EmployeeID as 5.
Solution Description: The EmployeeID is an integer field, and not a string field. So, EmployeeID which is 5 does not need to be surrounded by quotes in the where clause.
Problem 6: In the Suppliers table, let us retrieve SupplierID, ContactName, and ContactTitle for those Suppliers whose ContactTitle is not Marketing Manager.
Solution Description: We have to print the ContactTitle where it is not Marketing Manager. So we will simply use “not“ keyword in where clause.
Problem 7: In the products table, let us find out the ProductID and ProductName for those products where the ProductName includes the string “queso”.
Solution Description: In an earlier problem, we were looking for exact matches — where our filter matched the value in the field exactly. Here, we’re looking for rows where the ProductName field has the value “queso” somewhere in it. Use the “like” operator in our query.
I hope this article was informative and provided you with the details you required. If you have any questions related to any problems while reading this blog, message me on Instagram or LinkedIn. Special credits to my team member — Atharva.
Thank You…
