Top 50 SQL Queries That You Must Know With Examples

In this article, we’ll go over the most common SQL queries that you should know to be able to use SQL efficiently.

What SQL Queries Will Be Covered?

We’ll be going over queries that are used in the day-to-day situations of a database administrator. Commands by themselves will not really do anything unless they’re formed into a query. Let’s get right into this.

1. Create Databases and Tables

To start working with SQL tables, you’ll need to create them first. Tables reside within Databases and you can create both tables and databases using the CREATE keyword.

Let’s create a database, then enter into the database and then create a table.

2. SQL Queries to Insert Single Values into Tables

After creating a table, it’s time to add data to the tables. Let’s add some data to our authors table.

4. Insert Multiple Values into Tables

If you have to insert multiple values, the above method is inefficient and adding them with a single query will be much better. You can insert multiple values with the below query.

5. SQL Queries to Retrieve All Data From A Table

Now that you know how to create databases and tables and insert data into it, this query will help you display the data within the tables.

We use the select query to retrieve data from different tables. This is one of the queries that you’ll learn when you’re beginning with your SQL learning.

6. Retrieve Specific Columns From A Table

The above command will retrieve everything from the table. But if you want to retrieve only a specific column, you can use the below query.

7. Retrieve Specific Data with WHERE Keyword

If we use the * operator, the entire table is displayed. We can narrow down our results with the use of the WHERE keyword to display specific rows.

In the below query, I’ll extract the author with the email

8. SQL Queries to Update Single Rows

Inserted data may need updates and changes and we can use the UPDATE command to update any rows.

With a combination of UPDATE, SET and WHERE commands, we can update data for specific rows.

9. Update Multiple Rows

If we skip the WHERE keyword from the above command, we’ll be able to update all the rows in a specific table.

The command will update all the author’s names to Jordan.

10. Delete Single Row

You can delete single or multiple rows with the use of the DELETE command paired with the WHERE command.

Let’s delete the author with the email

11. Delete Multiple Rows

To delete multiple rows of the table, you can enter multiple WHERE conditions using the boolean AND or OR.

12. Counting Rows

We can count rows using the COUNT keyword. This will print the count of the author’s emails.

13. Get a Sum for Data

Similar to how we used the COUNT keyword above, we can use the SUM keyword to get the total for a specific column. Let’s get the total pay for authors.

14. Get Average Values for Data

Now that we know how to get the total, let’s get the average pay for our authors.

15. Creating Views

Views are a very interesting feature of SQL. They’re like virtual tables that contain specific data that we’ve selected. We can manipulate and view data from those views

This will create a virtual table named “high_pay_authors” which can be used as an independent table to view and manipulate data.

16. Add Columns to a Table

Using the ALTER keyword, we can add columns to a table.

This will add an integer column for the author’s age.

17. Remove Column From a Table

With the use of the DROP keyword, you can also remove columns from a table.

This will delete the column but make sure you have the required privileges to drop the columns.

18. SQL Queries to Search for Values

We already looked at the WHERE keyword to search for specific values within a database table and return the row that has the specific value. But we don’t always know the exact value.

For such situations, we can use pattern matching with the LIKE keyword.

This query will pattern match all the names that start with the letter “j” which in our case will return all the rows.

19. Swapping Data From Columns

To switch the data between two columns, you can directly use the command below.

This simple query will swap all the data from the author_email column to the author_name column and vice versa.

20. Rename Tables

Depending on your SQL version and privileges, you can use the below command to rename a table.

This command will rename our “authors” table to “authors_renamed”.

21. Return Unique Values from a Table

In our table above, we have multiple people but all of the names and emails are unique. If we had a table with multiple rows had columns with the same values, we use the DISTINCT keyword to return only unique values from the tables.

22. SQL Queries for Subsetting Tables

When working with larger databases, it only makes sense to view limited data at a time. The TOP command allows us to display only a specific number of rows from the table in the output.

23. SQL Queries to Backup Databases

This is one of the SQL queries that you must get used to or at least create a script to automate backups because backups are very important.

The above command will backup the database db_name to a file named db_name-backup.db.

24. Backup Only Updated Part of Database

Adding “DIFFERENTIAL” to the query will backup the “updated” parts or the parts that have changed since the last update. This reduces the time required to backup compared to a full backup

25. Restore Database Backups

We learned how to create backups for databases, now let’s learn to restore the backed-up file into our database.

26. Copy Results of a Query into a Table

We looked at the WHERE command to display the pay of the authors and figured which ones are highly paid. We also created a view that allowed us to create a virtual table to work with.

Now we’ll copy the entire set of data matching a specific query into another table that has the same columns.

27. Creating Stored Procedures

Stored procedures are SQL queries that can be run repeatedly. This saves you time when the queries are big. You can save them with a procedure name and then execute the procedure whenever required.

28. Boolean Operators for SQL Queries

If you need to provide multiple conditions within an SQL query, it’s good to know the Boolean Operators.

The above query will match either of the two emails and will return 2 rows. If we use the same query with the AND operator, it will return zero rows.

This is because the AND operator requires both the conditions to be true while the OR operator works with either of the conditions being true.

29. Find Values Between a Range

SQL provides a very easy to use BETWEEN keyword that helps us return the rows that have a value that’s between the specified range.

30. Negating Queries or Expressions in SQL Queries

Similar to the boolean operators AND and OR, we have the NOT keyword which negates any expression that follows and returns a value that’s opposite.

In this demonstration, the values which are not between 50,000 and 100,000 will be returned.

31. Finding Minimum Values

SQL provides a very easy to use function to help find the minimum value of a column from the entire table.

32. Finding Maximum Values

Similar to the function above, we also have the MAX function to find the maximum value from a specific column.

33. Creating Aliases

With the use of the AS keyword, you can change the name of the columns for display. Have a look at the example below to understand better.

This will change the display column names to Author Name and Author Emails. You can use the AS keyword for columns that result from a function operation too.

34. Inner Join Two Tables

We’ve seen joins in better detail in a previous tutorial so we’ll briefly go over the joins here. Inner Joins will return all the matched values from both the tables. We’ll look at other joins briefly in the coming few points.

Let’s assume we also have another database that keeps a track of the articles that are written by our authors and the common column between them is the author’s email. We’ll call that table “author_submissions”

Seems complicated? It really isn’t. We’re simply combining the columns from both the tables that we want to display, then matching them based on the “email” in both the tables. Have a read through the joins article and you’ll get a good understanding of this concept.

35. Left Outer Join

Compared to the Inner join above, Left Outer Joins will return all the values from the left table and only the matching values from the right table.

For rows from the right table which don’t get matched, the left join will mark them as NULL.

36. Right Outer Join

The right outer join, in contrast to the left outer join, will return all values from the right table while only returning the matched values from the left table and displaying NULL for empty rows.

The queries remain the same, but what changes is the keyword that’s being used.

37. Full Outer Joins

The Full outer join combines the functionality of the Right and the Left Join in one. It returns all the values from both the tables and maks NULL for rows that don’t have a match.

38. Self Join

The self-join is a very simple join but might be confusing at first. We join the columns of the same table for display based on specific conditions that we provide.

This will create a table that looks similar to the one below:

Author_A Author_B author_email
Joe Joe
Jane Jane
John John

But what’s the use of this join? In our case, nothing. But when you’re working with products and orders, it helps you visually represent all the orders and products in the format while matching customers.

For example, if you have a table with CustomerID, ProductID, City, Price in the same table, you can self join the table to display the City data in one column and match the CustomerID in two columns. This will help you understand how many customers are from the same city in a tabular form.

40. Case Statements in SQL Queries

When listing data from a table, you can add a custom column that displays information conditionally based on the data that’s being compared with.

Complicated? Think of it this way. Suppose you have grades of 1000s of students to work with and you need to figure out how many of them passed and how a man of them failed. It would be really useful if SQL could, based on a condition, print a “Pass” or “Fail” right after the marks, wouldn’t it?

That’s one of the uses of the Case statements.

For our table, the above query will give an output like the one below.

Author_name author_pay Author Experience
Joe 50000 Budding Author
Jane 70000 Experienced Author
John 20000 New Author

41. Handling NULL Values in Table Outputs

When using the SELECT keyword to return table values, you might need to handle the NULL values that come up within the table. That can be handled with the IFNULL keyword.

The above query will return the values of the authors and the salaries that are being paid to them. If an author doesn’t have a salary listed, it will automatically display 10,000 for the NULL value.

42. Testing For NULL Values

If you have a list of values that are being passed to the SQL database and you need to test them for NULL-ness, you can use the COALESCE function.

Why would you need it? Here’s an example. Suppose you want to display a bunch of data. Many of the data rows could have NULL values. You can test for the values by passing a column list to the COALESCE function and get the first non-null value outputted.

Coalesce Example

The above table is a representation of what the SQL query output would be. Why? Because none of the “author_name” rows are NULL.

If either of the author_name values were NULL, we’d have the author_pay take place of the NULL value.

43. Joining Two Strings

You can concatenate two strings with the help of the CONCAT function.

Output: String 1String 2

You can add functions or queries in place of those strings to concatenate and use the AS keyword to name the newly formed column.

44. Replace Characters

Looking for a way to use Regex to replace characters with other characters? This is the section that answers your question.

This query will replace all the occurrences of the letter “j” in the entire column with the letter “c”.

You can replace all the parameters with regex as per your requirements.

45. Change String Case

To display the output of a table in upper or lowercase, you can use the UPPER() and LOWER() functions.

This will display the author names in the upper and lower case respectively.

46. Typecasting Input Data

We obviously want the data that’s being stored in our databases to be clean and error-free. And we can do our part by at least making sure that the data is of the correct type.

47. Check if Values are Numeric

If you do not want to typecast and only want to check if the value being passed is numeric or not, we can use the ISNUMERIC function.

Returns a 1 if the value is numeric, and a 0 if the value is not numeric.

48. Return ASCII Values

You can also use the ASCII() function to return ASCII values of any character that is passed to it.

If the string passed to the ASCII function as a parameter is longer than 1 character, the returned value is for the first character in the string.

The above will return “72” as the output because uppercase H has an ASCII value of 72.

49. Find a String in Another String

To check for occurrences of values between two columns, you can use the INSTR function in MySQL.

This will return the character position of the matched string. The “author_name” will be searched within “author_email”.

50. Trim Whitespace

Ever stuck in a situation where you have a lot of values with whitespaces that you need to remove?

Fret not, because the TRIM() function solves this problem for us.

Other functions that can be used in a similar fashion are RTRIM and LTRIM to trim right and left trailing spaces respectively.


We hope that you learned some new functions or queries from our tutorial above. If you have any questions, let us know in the comments below.

By admin

Leave a Reply

%d bloggers like this: