70.110
Practice Queries for SQL II

Introduction

Practice SQL with the following SQLite database orgdb.db created with the script orgdb-creation.sql. You can either download and run the script and and then connect to it from R (or any other language) or download the already created database and connect to it. By using the script you learn how to create new databases (often used for creating testing and development databases).

The script has been tested in SQLite but should work with no or minor modifications also on MySQL, SQL Server, and Oracle. To write the SQL queries you can use a query console or embed the queries into {sql} code chunks in an R Notebook – of course, after creating and then connecting to the database.

To connect to the database (once you have created or downloaded it) and execute the queries, use this R Notebook: orgdb-starter.Rmd. Of course, you can also run the queries from a query console.

Solving practice questions is the fastest way to learn any subject. The same is true for SQL. You learn SQL by doing; by writing lots of queries. So, build the queries yourself; don’t look at the solution right away. That way you will master SQL quickly.

Data Model: ERD

To create complex queries and joins, you will need a data model; if none is provided you must create one. So, inspect the database (or the database creation script) and build an ERD to help you with the queries. Alternatively, you can use a tool such as TOAD, ERwin, MySQL Workbench, or even LucidChart to build an ERD automatically.

OrgDB ERD

Practice Queries

1. Retrieve the last names of all workers under the new column name LastName.
2. Retrieve the first names of all workers in upper case.
3. Retrieve the names of all departments without duplicates.
4. Retrieve the first three characters of all last names of all workers.
5. Which worker’s last names end with the character ‘a’?
6. Retrieve the first names of all workers and remove any potential leading whitespace (Hint: use ltrim()).
7. Assume that some department names have trailing whitespace. Retrieve the unique names of all departments with any trailing whitespace removed.
8. Write an SQL query to list the FIRST_NAME and LAST_NAME from the Worker table into a single column COMPLETE_NAME in the form last, first, e.g., Patel, Vivek. (Hint: SQLite does not support the concat() function; instead it uses the || operator).
9. Retrieve all Worker information sorted by LAST_NAME in ascending order.
10. Retrieve the details for all workers with the first name of “Vipul” or “Satish”.
11. Retrieve the details for all workers that do not have the first name of either “Vipul” or “Satish”.
12. Which workers have a salary between 100,000 and 500,000? List their last name, first name, and salary in thousands (e.g., 100 rather than 100,000), in that order.
13. How many workers have a last name that starts with ‘S’?
14. Which employees joined the company in February 2014? Note that SQLite does not support the year() and month() SQL functions but does have a strftime() function. See How to Extract the Day, Month, and Year from a Date in SQLite. Or use the substr() function (See Substrings in SQLite)
15. How many employees work in the department ‘Admin’?
16. How many workers work for each department? List the department and number of workers in the descending order of numbers.
17. List the first and last name of all workers who are managers.
18. Retrieve the first five records (rows) from the Worker table sorted by salary from highest to smallest.
19. Which employees have the same salary? List their last name and salary.
20. Which departments that have fewer than three people in it?
21. Which employees have the highest salary in each department?
22. What is the total salary paid out to all employees for each department?
23. Who are the employees who earn the highest salary?
24. Who are the employees who earn below the average salary?

Solutions

The solutions can be found in orgdb-sql-solutions.Rmd. Of course, only look after you have exerted diligent effort in solving the queries.

To download the solution, you need to save the .Rmd file by right clicking on the link.

Disconnect from database

dbDisconnect(dbcon)

## Warning in connection_release(conn@ptr): Already disconnected

Conclusion

This tutorial explained the most common SQL query structures using examples.

---

Files & Resources

All Files for Lesson 70.110

---

References

Agarwal, M. (n.d.) 50 SQL Query Questions You Should Practice for Interview. link

Errata

None collected yet. Let us know.