Most Important SQL Queries for Beginners

Want to become proficient in SQL and pursue a rewarding career in data science? The most popular SQL queries for beginners are provided here to assist you in developing your data expertise.

SQL or Structured Query Language is probably one of the widely used programming languages, notably for its functionalities in the access and management of data in databases. The most important factor in SQL’s widespread use is its fundamental ability to interact with databases.

As a result, there has been a recent rise in interest in studying SQL. A SQL query is essentially a request or question for data. This tutorial contains some important SQL queries for beginners which might assist in getting familiar with the SQL language and will help beginners to ace the interviews.

Top 16 Most Important SQL Queries

SQL queries should be seen as more than just the fundamentals of SQL. SQL queries are your tools to completely manage the databases. You can use SQL queries for various activities without having to use complex methods. Let’s take a look!

1. Creating Database and Table

You can build databases, tables, and even databases within databases with the “CREATE” keyword. A database must first be created before we can interact with database tables.

Create Database

To build database tables, use the “CREATE DATABASE” statement.

Example:

CREATE DATABASE my_database;Code language: SQL (Structured Query Language) (sql)

Create Table

To create a database table, we use the SQL "CREATE TABLE" statement.

Example:

CREATE TABLE Organizations (
  id int,
  name varchar(50),
  address text,
  phone varchar(10)
);Code language: SQL (Structured Query Language) (sql)

Here we create a table named Organizations with the following fields id, name, address, and phone. The type of data that can be stored in that field is indicated by the data types: int, varchar (50), and text.

2. Display Data from Table

The simplest query to display all data from a table is this one. The query has only one character ” * “, followed by “SELECT”, pointing out to all the fields or columns in a table. So to retrieve all the columns of a table use * and then add “FROM” followed by the name of the table.

Example:

For example, if you want to obtain data from a table Student, then the query would be:

SELECT *
FROM Student;Code language: SQL (Structured Query Language) (sql)

3. Retrieving Data from Specific Columns

SQL also gives users the option to display data from particular columns of a table. This can be done by adding a list of the columns after “SELECT.”

Example:

Consider a database of “Students” as an example. Use the sample query below if you wish to retrieve information about “id” and “name“.

SELECT  id, name
FROM Students; Code language: SQL (Structured Query Language) (sql)

4. Filtering out Data using WHERE clause

One important SQL query for newcomers to grasp in order to comprehend the capabilities of SQL is the “WHERE” clause. Data can be filtered using specific criteria thanks to it. For instance, you could implement a condition like age>=15 in the “Students” databases. This example would seek out records where it has a value of 15 or more in the ‘age’ column.

Example:

SELECT name, age

FROM students

WHERE age>=15;Code language: SQL (Structured Query Language) (sql)

5. Filtering out Data using conditions joined by AND operator

The “AND” operator can be used to filter data based on multiple conditions.

Example:

SELECT firstName, lastName
FROM Organizations
WHERE country = 'INDIA' AND last_name = 'Pattinson'; Code language: SQL (Structured Query Language) (sql)

Here, the SQL command selects firstName and lastName from the Organizations table where the country is INDIA and lastName is Pattinson. You will understand better with an example.

Consider the given table Organization:

After using the above query, the output will be:

6. SQL DISTINCT to fetch Non-repeated Records

To retrieve just specific or one of each type of record, put “DISTINCT” after “SELECT“. It selects unique rows from the table.

Example:

SELECT DISTINCT country
FROM Customers;Code language: SQL (Structured Query Language) (sql)

Here, the SQL command selects unique countries from the Organizations table (Refer to the Organizations table). The output will be,

Example 2:

Let’s look into another example,

SELECT DISTINCT country, firstName
FROM Customers;Code language: SQL (Structured Query Language) (sql)

Here, the SQL statement chooses rows if the combination of country and firstName is distinct(Refer to Organizations table). The output will be,

7. IS NULL and IS NOT NULL condition

The “IS NULL” condition is used to select rows where the specified field is NULL.

Example:

SELECT *
FROM Customer
WHERE Mail_id IS NULL;Code language: SQL (Structured Query Language) (sql)

Let’s take a look at this table:

To get the rows with NULL values, we use the above query. Here, the SQL command selects customers who don’t have an email. The output will look like this,

And the “IS NOT NULL” condition in SQL, is used to select rows if the specified field is NOT NULL.

Example:

SELECT *
FROM Customer
WHERE Mail_id IS NOT NULL;Code language: SQL (Structured Query Language) (sql)

To get the rows where the field is not null, we use the above query. Here, the SQL command selects employees who have emails. The output will look like this,

8. Sorting Data using ORDER BY clause

The result set can be sorted in either ascending or descending order using the SQL “ORDER BY” clause. Put the column name after ORDER BY to sort data by that column. Alphabetical sorting is used by default.

Example:

SELECT *
FROM Customers
ORDER BY firstName;Code language: SQL (Structured Query Language) (sql)

Here, the above query selects all customer and then sort them in ascending order by firstName. We can also use “ASC” and “DESC” keywords explicitly to sort the results. By default, it sorts in ascending order.

Consider the given table Customers,

After using the above query, the output will be,

9. Using ORDER BY with multiple columns

We can also use SQL “ORDER BY” clause with several columns.

Example:

SELECT *
FROM Customers
ORDER BY firstName, age;Code language: SQL (Structured Query Language) (sql)

Here, all the records are first sorted according to the column firstName. If the firstName repeats more than once, then it sorts according to age. The output will look like this(refer to table Customers),

10. BETWEEN and NOT BETWEEN in SQL

To obtain values in a range, we use the “BETWEEN” operator with “WHERE” clause.

Example:

SELECT product, price
FROM Orders
WHERE amount BETWEEN 250 AND 450;Code language: SQL (Structured Query Language) (sql)

Here, the SQL query picks all the data having prices between 250 and 450 (including both).

To eliminate rows that match values in the range, use the “NOT BETWEEN” operator. Except for the excluded rows, it returns every row. For instance,

SELECT product, price
FROM Orders
WHERE amount NOT BETWEEN 350 AND 500;Code language: SQL (Structured Query Language) (sql)

Consider the given table Orders,

The output for both queries will be as follows:

11. SQL COUNT() Function

The “COUNT()” function in SQL is an aggregate function that gives the total number of rows returned by a query. The SELECT statement’s COUNT function can be used to determine the total number of employees, the total number of employees in each department, the total number of employees with a certain position, etc.

Example:

SELECT COUNT(*)
FROM Customers;Code language: SQL (Structured Query Language) (sql)

Here, after using the above query, we get the total number of rows of the Customers table. Look at the below output(refer to the CUSTOMERS table above).

We can also use COUNT function with WHERE , DISTINCT and GROUP BY clause.

Example:

SELECT COUNT(country) 
FROM Organizations
WHERE country = 'INDIA';Code language: SQL (Structured Query Language) (sql)

(Refer to table Organizations.) The output will be,

12. Calculate the Average and Sum of the values in the field using SUM() and AVG() functions

“SUM()” and “AVG()” functions in SQL are used to compute the sum and average values in numeric columns, respectively.

Example:

SELECT SUM(price)
FROM Orders;

The output of the above query will be as follows, (refer to table Orders)

Let’s take an example for AVG() function,

SELECT AVG(age) AS average_age
FROM Customers;Code language: SQL (Structured Query Language) (sql)

The output of the above query will be as follows, (refer to table Orders)

13. Finding Minimum and Maximum values in a Column using MIN() and MAX()

“MIN()” can be used to locate the lowest value kept in a column.

Example:

The query in this instance returns the products’ lowest price from the Orders table (refer to the Orders table).

SELECT MIN(price)
FROM Orders;Code language: SQL (Structured Query Language) (sql)

“MAX()” can be used to determine the greatest value kept in a column. The query in this instance returns the highest price from the Orders table.

SELECT MAX(price)
FROM Orders;Code language: SQL (Structured Query Language) (sql)

The result of the above queries will be,

14. INSERT INTO Statement to Insert Data into a Table.

“INSERT INTO” can be used to add a new record to a table. The table name should come after INSERT INTO, followed by the names of the table’s columns in brackets. Put VALUES next, followed by the values for the columns in brackets.

Example:

INSERT INTO Orders(id, product, price)
VALUES(025, 'sofa', '550');Code language: SQL (Structured Query Language) (sql)

This query will insert 025 into id, ‘sofa‘ into product, and ‘550‘ into the price in the table Orders.

15. Deleting a Table from MySQL Database

“DROP TABLE” can be used to remove a table. Just follow the DROP TABLE with the name of the table you want to remove.

Example:

DROP TABLE Orders;Code language: SQL (Structured Query Language) (sql)

This command will delete the table named Orders.

16. Deleting Row from a Table

The “DELETE” statement in SQL is used to remove a record or rows from a database table.

Example:

DELETE FROM Orders
WHERE orderId = 025;Code language: SQL (Structured Query Language) (sql)

This SQL command will delete the row from the Orders table where the orderID will be equal to 025.

Also Read :

• Is it difficult to learn SQL?
• How to Prepare for MySQL Interview

Summary

In this tutorial, we have learned the 16 most important and fundamental SQL queries that we can use to perform different operations in MySQL databases. The best way to learn these SQL queries is by executing them yourself, so try them after re-reading this tutorial.

Reference

MySQL Official Documentation