SQL Query Examples for Beginners

Structured Query Language, also referred to as SQL was created to manage data contained in relational database management systems. (RDBMS). Relational databases can be created, modified, and queried using this standard language. Database administrators, data analysts, and developers all frequently utilize SQL. To learn more about SQL, refer to this article- SQL query interview questions and answers.

In this post, we’ll go over beginner-friendly SQL query examples that will help readers grasp the fundamentals of the language and demonstrate how to create straightforward queries that extract data from database tables. The SELECT statement, WHERE clause, ORDER BY clause, GROUP BY clause, and JOIN clause will all be covered along with examples to show how to use them. This tutorial will provide you with a firm grounding in SQL query writing, whether you are a novice or an experienced developer.

1. Getting all data from a table

SELECT statement is used to retrieve all of the data from a table in a database. Here is an illustration of how to get every piece of information from a table called “employee”:

SELECT * FROM employee;

This will return all fields of the employee table. You don’t have to mention column names in this query.

2. Getting specific columns from the table.

In the above query, instead of “*” use the column names which you want in your results. Here’s an example of how to get column id and name from a table called “employee”:

SELECT id, name FROM employee;

The above query will show only the id and names of employees. You can add any number of columns separated by “,” to see them in the results.

3. Getting filtered results using the Where clause

You can filter data in addition to getting results from specific columns by adding criteria after WHERE. For example, if you want to see the names of employees who are older than 25, run the following query:

SELECT id, name, age FROM employee WHERE age >= 25;

The names of all employees with ages more than or equal to 25 will be retrieved by this query. Asterisks (*) can be used in place of column names to retrieve all columns that match the condition.

4. Adding OR and AND with WHERE

You can use OR or AND in conjunction with WHERE to get more filtered results. For example, If you want to get the names of employees aged greater than 25 and who work in the IT department, run the following query:

SELECT id, name FROM employee WHERE age > 25 AND department = 'IT';

This will return all the employees who are older than 25 and work in the IT department.

Now suppose you want to get the names of employees who either work in IT or sales, write the following query:

SELECT name FROM employee WHERE department = 'IT' OR department = 'Sales';

The above query will give you all employees who either work in IT or in sales.

5. Using DISTINCT to get distinct values

You can use the distinct keyword to get distinct fields of a column. In this example, we want to get all the distinct values of the department (the department name will definitely be repeated in the table).

SELECT DISTINCT department FROM employee;

This will give you all the unique departments present in the table without repetition.

6. Retrieving data with IS NULL or IS NOT NULL

There might be certain columns in your table with null values. In this example, we want to get the names of employees who have no team lead.

SELECT name FROM employee WHERE lead IS NULL;

This will give us the names of employees who have no leads. You can also get the names of the employees whose team lead’s name is present in the table.

SELECT name FROM employee WHERE lead IS NOT NULL;

7. Sorting columns

You can use the ORDER BY clause in a SELECT statement to order the columns in a table of employees. You can use the ORDER BY clause to arrange the results in either ascending or descending order according to one or more columns. Here’s an illustration:

SELECT * FROM employee ORDER BY age ASC;

This will give you a table where the age column is sorted in an ascending manner (i.e., from lowest age to highest age). You can use the DESC keyword for descending sorting. By putting multiple columns in the ORDER BY clause and separating each one with a comma, you can additionally order the results by those columns. To illustrate, you can use the following search to organize the results first by salary, then by age, in that order:

SELECT * FROM employee ORDER BY age DESC, salary ASC;

This will give you all columns from the “employee” table and sort the results first by the “age” column in descending order and then by the “salary” column in ascending order.

8. Getting values that match a pattern

You can use the LIKE operator in a SELECT statement to look for values in a table that adhere to a specific pattern. You can use the LIKE operator to look for values that match a particular pattern, which includes wildcards. Here’s an illustration:

If you want to locate all employees with names that begin with “J,” you can use the SQL query shown below.

SELECT * FROM employee WHERE name LIKE 'J%';

This will retrieve every column whose “name” column begins with the letter “J”. This search will match names like “John”, “Jane”, “Jim”, etc. because the “%” symbol is a wildcard that matches any amount of characters.

Instead of putting the wildcard at the end of the search term, you could put it at the beginning if you wanted to look for values that ended with a particular pattern:

SELECT * FROM employee WHERE name LIKE '%son';

This will retrieve every column where the “name” column ends with the letters “son”. Names like “Johnson”, “Jackson”, “Wilson”, etc. will be matched by this query.

9. Using Mathematical operators

Calculating numerical values in your tables can be done using mathematical operators in SQL. You can employ the following fundamental mathematical operators:

Subtraction (-), addition (+) , Division (/) and multiplication (*).

Suppose you want to update the salary of every employee by 10%, You can write the following query:

UPDATE employee SET salary = salary * 1.1;

10. UNION and UINIONALL

A single result set can be created by combining the output of two or more SELECT statements using the SQL operator UNION.

Get Yash Vishwakarma’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Let’s say you have two tables called “employee” and “customers”, and you want to get a list of everyone in the database, including both employees and clients, along with their names and email addresses. The SQL query below can be used:

SELECT name, email FROM employee
UNION
SELECT name, email FROM customers;

The “employees” and “customers” databases will be queried for a list of all distinct names and email addresses, which will then be combined into a single result set.

Note that the ALL keyword can also be used in conjunction with the UNION operator to include duplicate entries in the result set. The syntax is as follows:

SELECT column1, column2, … FROM table1
UNION ALL
SELECT column1, column2, … FROM table2

11. INTERSECT Operator

INTERSECT is a SQL operator that allows you to combine the results of two or more SELECT statements and return only the rows that appear in all result sets.

SELECT name, email FROM employee
INTERSECT
SELECT name, email FROM customers;

This will retrieve a list of all unique names and email addresses that appear in both the “employee” and “customers” tables.

12. Joining data from different tables

With the help of the operator JOIN, you can merge rows from two or more tables based on a shared column. SQL supports several different JOIN operator types, including FULL OUTER JOIN, LEFT JOIN, RIGHT JOIN, and INNER JOIN.

To see how to utilize the INNER JOIN operator, consider the following example:

Let’s say you have two tables called “employee” and “departments” and you want to get a list of all employees together with the names of their departments. The SQL query below can be used-

SELECT employee.name, departments.department_name 
FROM employee 
INNER JOIN departments 
ON employees.department_id = departments.department_id;

This will retrieve a list of all employees and their corresponding department names based on the matching department IDs in both the “employee” and “departments” tables.

13. Getting the number of rows

You can use COUNT to get the number of rows based on a column.

SELECT COUNT(id)
FROM employee;

14. Getting the average of values of a column

Use AVG(column_name) to get the average of values of that column:

SELECT AVG(salary)
FROM employee;

15. Getting the max and min values of a column

Use MAX(column_name) to get the max value of a column:

SELECT MAX(age)
FROM employee;

Note- You can use min to get the min value of a column.

16. Aggregating results using GROUPBY

Using the SQL operation GROUP BY, you can group together entries in a result set based on one or more columns.

Let’s say you have a database called “employee” with the columns “employee_id”, “first_name”, “last_name”, “age”, “department_id”, and “salary” and you want to get the total salary for each department. The SQL query below can be used:

SELECT department_id, SUM(salary) as total_salary
FROM employee
GROUP BY department_id;

This will group the employees by department_id and calculate the total salary for each department. The result set will include one row for each department_id and the corresponding total salary.

17. Deleting Data from Table

If you want to delete all data from the table:

DELETE FROM employee;

If you want to delete based on certain conditions:

DELETE FROM employee
WHERE id = 4;

18. Updating values in the table

You can use the UPDATE keyword to update data.

For example, look at the following query:

UPDATE employee SET company = "xyz";

The above query will set all company rows to xyz.

You can also use UPDATE with WHERE to update only selected values:

UPDATE employee
SET name = "John"
WHERE id = 5

The above query will change the name of the employee whose id is 5.

19. Creating a table

Here’s an example of how to create an “employee” table with columns for employee information:

CREATE TABLE employee (
 employee_id INT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 age INT,
 department_id INT,
 salary DECIMAL(10,2)
);

With the following columns: employee_id, first_name, last_name, age, department_id, and salary, the new table “employees” will be created. employee_id is set as a primary key for the table because it uniquely identifies each row of the employee table.

Depending on your unique needs, you can modify the data types and constraints for each column.

20. Inserting values in the table

Once the table is created you can use the following query to insert a row:

INSERT INTO employees (employee_id, first_name, last_name, age, department_id, salary)
VALUES (1, 'John', 'Wick', 30, 101, 50000.00);

Conclusion

A relational database management system can manage data effectively using the SQL language. Developers, data analysts, and database administrators all need to be proficient at writing effective SQL queries. The SELECT statement, WHERE clause, ORDER BY clause, GROUP BY clause, and JOIN clause, as well as examples to show their usage, are covered in this article. Beginners can build a strong foundation in SQL and be prepared to write more complex queries by mastering these simple SQL queries.