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.
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", "email@example.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", "firstname.lastname@example.org", 50000), ("Jane", "email@example.com", 70000), ("John", "firstname.lastname@example.org", 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 email@example.com.
SELECT * FROM authors WHERE author_email = "firstname.lastname@example.org";
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="email@example.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 firstname.lastname@example.org.
DELETE FROM authors WHERE author_email="email@example.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="firstname.lastname@example.org" OR author_email="email@example.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="firstname.lastname@example.org" OR author_email="email@example.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="firstname.lastname@example.org" AND author_email="email@example.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:
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.
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"
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.
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.
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.
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.