Back to articles list April 3, 2020 - 6 minutes read SQL Cookbook with Recipes for Success Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: sql learn sql SQL cookbook behind the scenes 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 LearnSQL.com. Click on Library, then Start Reading under Cookbooks. 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 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 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. Tags: sql learn sql SQL cookbook behind the scenes You may also like You Want to Learn SQL? You've Come to the Right Place! If you want to learn SQL basics or enhance your SQL skills, check out LearnSQL.com for a wide range of SQL courses and tracks. Read more What Is the Difference Between a GROUP BY and a PARTITION BY? What is the difference between a GROUP BY and a PARTITION BY in SQL queries? When should you use which? You can find the answers in today's article. Read more SQL Window Functions vs. GROUP BY: What’s the Difference? Window functions and GROUP BY may seem similar at first, but they’re quite different. Learn how window functions differ from GROUP BY and aggregate functions. Read more HAVING vs. WHERE in SQL: What You Should Know To take advantage of SQL’s great power, you must understand HAVING vs. WHERE clauses. How do you use them? What are their differences? Read more Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more SQL Window Functions Cheat Sheet This 2-page SQL Window Functions Cheat Sheet covers the syntax of window functions and a list of window functions. Download it in PDF or PNG format. Read more LearnSQL.com Review: “SQL Cookbook” by Anthony Molinaro Check out the first LearnSQL.com Review to see if “SQL Cookbook” by Anthony Molinaro is the best SQL read for you! Read more Should I Learn SQL? 4 Convincing Reasons There are 250+ computer programming languages in popular use. However, there are good reasons why you should learn SQL, or Structured Query Language. Read more The Man Behind LearnSQL.com Learn more about Jarosław Błąd, the guy that created the world's best SQL learning platform. This is his story. Read more What Is Vertabelo’s SQL Cheat Sheet? Rock the SQL! You don’t have to be a programmer to master SQL. Download the SQL Cheat Sheet and find quick answers for the common problems with SQL queries. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.