The foundation of relational database data management is Structured Query Language(SQL). SQL is an essential Skill for data warehousing, web development, and data analysis. With the help of this article, you will be able to write your first SQL queries with ease. You will understand the basic SQL operations like querying, filtering, sorting and data manipulation by reading this article.
1. What is SQL?
One powerful tool for interacting with the database is SQL(Structured Query Language). SQL facilitates the efficient retrieval, insertion, update and deletion of data by users. SQL works silently in the background to retrieve and process the necessary data whenever you visit an online store, log into a social media account, or check your online bank balance.
For example a company can use a simple SQL query like this to retrieve all employee data from their database:
This command retrieves all records from the employees table. The * symbol means all columns, so it will return every piece of information stored for each employee.
2. SQL Syntax
SQL follows a structured syntax, consisting of various commands and clauses that allow users to manipulate data. The core commands include:
- SELECT – Used to retrieve data from a table.
- FROM – Specifies the table from which data is retrieved.
- WHERE – Filters the results based on conditions.
- ORDER BY – Sorts the result set.
Select and SELECT would be handled similarly because SQL is case-insensitive. It is customary to write SQL keywords in uppercase for readability.
Here’s an example of a query that retrieves names and salaries of employees earning more than 50,000:
This query filters data using the WHERE clause to only return employees who meet the salary condition.
3. SQL Data Types
Every database stores data in different data types based on the nature of the information. The most commonly used SQL data types include:
INT – Used for whole numbers (e.g., 100, 200).
VARCHAR(n)/TEXT – Stores text values (e.g., 'John Doe').
DATE – Stores date values (e.g., '2024-04-01').
DECIMAL(10,2) – Used for decimal numbers, often for financial calculations (e.g., 9999.99).
When creating a table, we define these data types for each column:
This will ensure that data is stored in an organised and structured way, maintaining accuracy and consistency.
4. Basic SQL Queries
Now that will understand the structure of SQL, let’s see some basic queries used to retrieve and manipulate data.
Retrieving Data (SELECT)
The SELECT Statements fetch data from a table. If we want to retrieve only specific columns, we specify them instead of suing *:
Filtering Data (WHERE)
The WHERE clause allows filtering data based on specific conditions:
This query returns only those employees earning more than 50,000.
Sorting Data (ORDER BY)
The ORDER BY clause sorts data in ascending (ASC) or descending (DESC) order. To display employees with the highest salary first, we use:
Limiting Results (LIMIT)
To retrieve only a specific number of records, we use LIMIT:
This fetches only the first 5 records from the table.
5. Filtering Data with the WHERE Clause
The WHERE clause can be used with different operators:
- = (Equal to)
- > (Greater than)
- < (Less than)
- LIKE (Pattern matching for text search) For example, if we want to find employees whose names start with the letter ‘J’:
Here, the % symbol acts as a wildcard, meaning any number of characters can follow the letter 'J'.
6. Aggregating Data
SQL provides powerful aggregate functions to perform calculations on multiple rows at once.
Counting Rows (COUNT) – Counts the number of records:
Summing Values (SUM) – Calculates the total salary:
Calculating Average (AVG) – Finds the average salary:
Grouping Data (GROUP BY)
– Finds the average salary for each department:
This groups employees by department and calculates the average salary for each department.
7. Joins in SQL
Mostly, we store data in more than one table, and we have to merge them. SQL joins assist us in combining data from various tables on the basis of shared columns.
Inner Join
An INNER JOIN retrieves matching rows from both tables:
Left Join
A LEFT JOIN retrieves all rows from the left table and matching rows from the right table:
This ensures that all employees are listed, even if they are not assigned to a department.
8. Inserting Data
To add new data to a table, we use the INSERT INTO statement:
This adds a new employee record to the employees table.
9. Updating Data
We use the UPDATE statement to modify existing records. For example, to increase John Doe’s salary:
10. Deleting Data
To remove records, we use the DELETE statement:
Conclusion
When working with databases, SQL is an essential tool. You can easily manage and analyze data by learning how to query, filter, aggregate, join and manipulate data. The fundamentals were covered in this lesson, and you have now begun using SQL.
We will talk about SQL Constraints and Keys in the next article. These are crucial for preserving database relationships and data integrity. Until we meet again!
