SQL Cookbook with Recipes for Success

The internet holds a lot of information and can provide solutions to various problems. SQL users, both beginners and advanced, often turn to the internet for help with SQL queries. This article will help you find the right SQL queries to solve your problems. Learn what the SQL Cookbook is and why you should use it. You will also find a list of the most important recipes, from which you will prepare a great SQL dish with the taste of success.

What Is the SQL Cookbook, and Why Did We Create It?

The SQL Cookbook is a collection of short articles about different SQL problems and their solutions. Each article shows you how to write SQL queries to achieve specific results.

For example, you might want to know how to write a query that selects distinct records from a database. You can try to do it a hard way, with trial and error. Or, you can reach for a ready-to-use solution from our SQL Cookbook. Simply paste the SQL query into the code, then change the names of the tables and columns. Voila! Your code is ready.

Now do you understand why we created the SQL Cookbook? We wanted to save you the time and effort of searching through online forums and articles. The LearnSQL.com library has everything you need in one place.

Do you have an SQL problem you don’t know how to solve? Check if we have already created a solution. Go ahead. They are available to all our users. What's more, our experts continue to work on different SQL scenarios. The SQL Cookbook will grow to address all of your SQL questions.

Reading through SQL queries can also be a great way to consolidate knowledge. Have you finished our courses and are looking for additional materials? Reach for the SQL Cookbook.

Do you want even more SQL practice? We recommend the SQL Practice path. There you will be learning by doing. This means that with this SQL course you will gain practical knowledge and a lot of practice. You will use the code editor and real datasets to solve real-world problems.

To find our SQL Cookbook, go to SQL cookbook section on our website.

The Most Popular Topics in the SQL Cookbook

Our SQL Cookbook contains recipes ready for use with popular database engines, including PostgreSQL, SQL Server, MySQL, and SQLite. Our solutions are usually written in the SQL standard and are supported by all database engines.

If you are a non-technical person who wants to better understand the basic SQL terminology and definitions, read our article "Essential SQL conditions for beginners and Pros".

Without further ado, here is a list of the five most-searched SQL queries from our SQL Cookbook.

1. How to Find Records with NULL in a Column

The article includes an SQL query which selects records from the table children that don’t store a value in the column middle_name.

SELECT id,
  first_name,
  last_name
FROM children
WHERE middle_name IS NULL;

Do you want to write a similar query for your database? After WHERE, type the name of the column in your table, and after FROM, type the name of your table. Also, change the names of the columns in the SELECT clause.

In this short article, there is also a step-by-step explanation of the SQL query above.

2. How to Find Duplicate Rows in SQL

Here, you will learn how to find duplicate records in your table. It is a common problem when selecting rows from a database. The solution is simple. Simply adjust our query for your database by changing the names of the table and columns. Sound good?

SELECT  name,
 category,
FROM product
GROUP BY name, category
HAVING COUNT(id) >1;

In this case, you must use the COUNT aggregate function to filter rows in the HAVING clause. You can learn more about the differences between WHERE and HAVING in the article “HAVING vs. WHERE in SQL: What You Should Know”. The COUNT function with id as the argument and the GROUP BY clause counts the number of rows in each group. The number of rows >1 in the condition finds the repeated records.

3. How to Add Ranking Positions of Rows in SQL with DENSE_RANK()

It doesn't matter if you are a beginner or an advanced SQL user, it's likely that you will face the problem of ranking rows. Don't worry though. We also have a simple way to do it.

SELECT DENSE_RANK() OVER(ORDER BY score DESC) 
  AS rank_place,
  user_name,
  score
FROM championship;

Change the names of the table and columns, and you’re ready to go. Remember to change the column for sorting records in OVER after the ORDER BY clause.

4. How to Filter Records with Aggregate Function AVG

Writing complex queries may be a challenge even for advanced SQL users. One of these complex problems would be filtering records based on an average value calculated on groups of rows.

SELECT name, AVG(price)
FROM product
GROUP BY name
HAVING AVG(price)>3.00;

This SQL query selects the name and the average price for each product in a group of rows and returns only the records with an average price higher than $3.00.

It is a convenient way for business specialists or marketers to resolve this problem with the help of our SQL Cookbook.

5. How to Find the Number of Days Between Two Dates in MySQL

Another popular problem is how to find the number of days between two dates.

SELECT name,
       DATEDIFF(expiration_date, purchase_date) 
         AS days
FROM food;

You can use this simple solution. As always, you need only to change the names of the table and columns for your MySQL database. In most databases, we can use the function DATEDIFF or something similar to find the number of days between two dates. In this example, it is the number of days between the expiration and purchase date of the product.

6. How to Floor Numbers in SQL

This is one of the queries that specialists use when preparing financial reports. Finding the floor number is possible thanks to the FLOOR function. It rounds the decimal number down to the nearest integer and returns this integer.

SELECT last_name, 
  first_name, 
  FLOOR(hours_worked/8) 
    AS days_worked
FROM employee;

In this example, FLOOR calculates the hours worked by employees divided by eight to get the number of days worked.

Conclusion

LearnSQL.com is a platform with best online SQL courses out there. The SQL Cookbook was designed by for marketers, analysts, and other specialists to make their work more efficient. It is a collection of ready-to-use SQL queries that will save your time. If you have an SQL problem, we have the answer. We will quickly explain our code, including why you should use a specific function or clause.

For a complete learning experience, check out our online learning paths like “SQL Reporting” or “Advanced SQL”. You will learn how to create useful SQL reports for everyday business situations. Soon you will be analyzing trends and writing more complex and sophisticated SQL queries.