Top 50 SQL Queries That You Must Know

Filed Under: SQL
Top 50 SQL Queries

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

Table of Contents

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.

CREATE db_name;
/* Use the specified database for executing further queries */
USE db_name;
CREATE TABLE authors (author_name VARCHAR(70), author_email VARCHAR(80), author_pay int);

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.

INSERT INTO authors (author_name, author_email) 
VALUES("Joe", "joe@journaldev.com", 50000);

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.

INSERT INTO authors (author_name, author_email) 
VALUES ("Joe", "joe@journaldev.com", 50000),
("Jane", "jane@journaldev.com", 70000),
("John", "john@journaldev.com", 20000);

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.

SELECT * FROM authors;

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.

SELECT author_name FROM authors;

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 jane@journaldev.com.

SELECT * FROM authors WHERE author_email = "jane@journaldev.com";

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.

UPDATE authors SET author_name="Jordan" where
author_email="joe@jornaldev.com";

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.

UPDATE authors SET author_name="Jordan";

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 john@jornaldev.com.

DELETE FROM authors WHERE author_email="john@journaldev.com";

11. Delete Multiple Rows

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

DELETE FROM authors WHERE author_email="john@journaldev.com" 
OR author_email="jane@journaldev.com";

12. Counting Rows

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

SELECT COUNT(author_email) FROM authors;

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.

SELECT SUM(author_pay) FROM 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.

SELECT AVG(author_pay) FROM 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

CREATE VIEW high_pay_authors AS SELECT * FROM authors 
WHERE author_pay > 50000;

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.

ALTER TABLE authors ADD author_age int;

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.

ALTER TABLE authors DROP COLUMN author_age;

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.

SELECT * FROM authors WHERE author_name LIKE "j%"

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.

UPDATE authors SET author_email=author_name, author_name=author_email

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.

sp_RENAME authors authors_renamed;

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.

SELECT DISTINCT author_name FROM authors;

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.

SELECT TOP 50 FROM authors;

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.

BACKUP DATABASE db_name 
TO DISK = "/home/databases/db_name-backup.db";

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

BACKUP DATABASE db_name 
TO DISK = "/home/databases/db_name-backup.bak" 
WITH DIFFERENTIAL;

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.

RESTORE DATABASE db_name 
TO DISK="/home/databases/db_name-backup.bak"

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.

INSERT INTO high_paid_authors 
SELECT * FROM authors WHERE author_pay > 50000;

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.

CREATE PROCEDURE findAuthors 
AS SELECT * FROM authors GO;

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.

DELETE FROM authors WHERE author_email="john@journaldev.com" 
OR author_email="jane@journaldev.com";

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.

DELETE FROM authors WHERE author_email="john@journaldev.com" 
AND author_email="jane@journaldev.com";

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.

SELECT *  FROM authors WHERE author_pay 
BETWEEN 50000 AND 100000

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.

SELECT *  FROM authors WHERE author_pay 
NOT BETWEEN 50000 AND 100000

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.

SELECT MIN(author_pay) FROM authors;

32. Finding Maximum Values

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

SELECT MAX(author_pay) FROM authors;

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.

SELECT author_name AS "Author Name", author_email 
AS "Author Emails" FROM authors;

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.

SELECT MIN(author_pay) AS "Lowest Salary" FROM authors;

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”

SELECT authors.author_name, authors.author_email, author_submissions.article_title FROM authors
INNER JOIN author_submissions ON authors.author_email=author_submissions.author_email;

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.

SELECT authors.author_name, authors.author_email, author_submissions.article_title FROM authors
LEFT JOIN author_submissions ON authors.author_email=author_submissions.author_email

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.

SELECT authors.author_name, authors.author_email, author_submissions.article_title FROM authors
RIGHT JOIN author_submissions ON authors.author_email=author_submissions.author_email

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.

SELECT authors.author_name, authors.author_email, author_submissions.article_title FROM authors
FULL OUTER JOIN author_submissions ON authors.author_email=author_submissions.author_email

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.

SELECT a1.author_name AS Author_A, a2.author_name 
AS Author_B, a1.author_email FROM authors a1, authors a2;

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

Author_AAuthor_Bauthor_email
JoeJoejoe@journaldev.com
JaneJanejane@journaldev.com
JohnJohnjohn@journaldev.com

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.

SELECT author_name, author_pay, 
CASE
WHEN author_pay < 30000 THEN "New author"
WHEN author_pay > 60000 THEN "Experienced Author"
ELSE "Budding author"
END AS "Author Experience"
FROM authors

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

Author_nameauthor_payAuthor Experience
Joe50000Budding Author
Jane70000Experienced Author
John20000New 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.

SELECT author_name, IFNULL(author_pay, 10000) 
AS "Pay" FROM authors;

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.

SELECT COALESCE(author_name, author_pay) 
AS "Coalesce Example"
Coalesce Example
Joe
Jane
John

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.

SEELCT CONCAT ('String 1', 'String2')

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.

SELECT TRANSLATE(author_name, 'j', 'c') from authors

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.

SELECT UPPER(author_name) FROM authors;
SELECT LOWER(author_name) FROM authors;

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.

SELECT CAST(<value> AS <datatype>);
SELECT CAST("hello" AS varchar(50));

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.

SELECT ISNUMERIC(52);

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.

SELECT ASCII("Hello");

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.

SELECT INSTR(author_name, author_email) 
AS MatchName from authors;

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.

SELECT TRIM(author_name) 
AS "Trimmed Names" FROM authors;

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

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages