All SQL Fundamentals Explained for Beginners in 10 Minutes

Mastering SQL Basics: A Step-by-Step Introduction for Beginners

Structured Query Language (SQL) is the lingua franca of data. It’s a standardized programming language widely used for managing relational databases and performing various data manipulation tasks. SQL is super essential for roles in data science, software development, and data analytics. If you’re a beginner, learning SQL is a pivotal step in your journey toward mastering data management.

This article demystifies all the major SQL concepts you need to understand in just 10 minutes. Let’s dive in!

TLDR; Don’t have time to read? Here’s a video to help you understand the all SQL Fundamentals in just 10 minutes in detail.

1. Relational Databases and SQL

The first concept to understand in SQL is the idea of a relational database. A database is an organized collection of data. In a relational database, this data is structured into tables, which can be related to each other based on common attributes.

SQL, a standardized programming language, is used to communicate with a relational database. It enables you to create, retrieve, update, and delete (commonly referred to as CRUD operations) records in these tables.

In SQL, we can create a database by simply using the following syntax,

CREATE DATABASE databasename;

2. Tables, Records, and Fields

A table, also known as a relation, is a structure comprising rows and columns. Each row in a table represents a record (or a tuple), while each column represents a field (or an attribute).

Each table in a relational database has a unique name. It is vital to plan your tables thoughtfully because they dictate the organization of your data.

The syntax to create a table in a database is,

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

3. SQL Data Types

In SQL, each column in a table is required to have a name and a data type. The data type indicates what kind of data the column can hold. Here are a few important SQL data types:

• Integer: used for whole numbers (i.e. numbers without decimal points)
• Decimal and Float: used for fractional numbers. The difference between the two lies in the exactness and storage space.
• Char and Varchar: used for characters and strings. CHAR is for fixed-length strings, while VARCHAR is for variable-length strings.
• Date and Time: as their names suggest, these types are used to store date and time, respectively.

For example, a ‘Users’ table might contain a ‘UserID’ column with the Integer data type. Here’s the syntax,

CREATE TABLE Users (
 UserID INT,
 UserName VARCHAR(100),
);

4. SQL Commands

SQL commands are instructions used to communicate with a database. They are divided into several types, including Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

DDL

These commands are used to define or modify the structure of database objects. They include CREATE (to create a new table or database), ALTER (to modify an existing database object), DROP (to delete a database object), and TRUNCATE (to remove all records from a table, but not the table itself).

DML

These commands are used to retrieve, add, modify, or delete the data in a database. They include SELECT (to select data from a database), INSERT INTO (to insert new data into a database), UPDATE (to modify existing data), and DELETE (to remove data).

DCL

These commands are used to create roles, permissions, and referential integrity in addition to controls like GRANT, REVOKE, and DENY.

5. Querying Data

One of the most common tasks in SQL is querying data, which involves using the SELECT command. With the SELECT command, you can retrieve one or more columns from a table. You can also use the WHERE clause with the SELECT command to filter rows based on certain conditions.

6. SELECT Statement

The SELECT statement is one of the most common SQL commands. It retrieves data from a database and allows you to filter, sort, aggregate, and group data. The basic syntax is:

SELECT column1, column2 FROM table_name;

To select all columns, we use ‘*’:

SELECT * FROM table_name;

7. WHERE Clause

The WHERE clause is used to filter records. The syntax is:

SELECT column1, column2 FROM table_name WHERE condition;

For example,

SELECT * FROM Customers WHERE Country='Germany';

8. JOIN Clause

JOIN clause is used to combine rows from two or more tables, based on a related column. There are different types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Example of an INNER JOIN, which returns records that have matching values in both tables:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

9. GROUP BY and HAVING Clauses

The GROUP BY statement groups rows that have the same values in specified columns into aggregated data.

HAVING is used to filter the result of a grouping, similar to how WHERE filters individual rows.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;

10. SQL Functions

SQL offers a lot of functions to manipulate data. Some commonly used functions are COUNT(), SUM(), AVG(), MIN(), MAX() etc.

Here’s an example with the COUNT() function:

SELECT COUNT(column_name)
FROM table_name;

11. SQL Constraints

Constraints are rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. The commonly used constraints are:

• NOT NULL: This constraint ensures that a specific column must contain a value and cannot be left empty (null) when inserting or updating data in the table.
• UNIQUE: The UNIQUE constraint ensures that the values in the specified column are all distinct, meaning no duplicate values are allowed within that column.
• PRIMARY KEY: This constraint uniquely identifies each record in the table and ensures that the chosen column(s) have unique and non-null values, serving as a unique identifier for the table.
• FOREIGN KEY: The FOREIGN KEY constraint establishes a link between two tables, enforcing referential integrity by ensuring that values in one table’s column match those in another table’s primary key column.
• CHECK: The CHECK constraint allows you to specify a condition that must be true for the data in a column, ensuring data integrity by limiting the acceptable range or values.
• DEFAULT: This constraint assigns a default value to a column when no explicit value is provided during an INSERT operation, ensuring that the column always has a value even if it is not specified explicitly.

Here’s an example syntax,

CREATE TABLE Orders (
 OrderID int NOT NULL,
 OrderNumber int NOT NULL UNIQUE,
 OrderDate date DEFAULT GETDATE()
);

12. Indexing

An index is used to speed up the performance of queries. It makes the record search faster. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Here’s an example,

CREATE INDEX idx_orderID
ON Orders (OrderID);

13. SQL Subqueries

A subquery is a query within another query. Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements, along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

Here’s the basic syntax for a subquery:

SELECT column1, column2, ...
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM another_table WHERE condition);

In this syntax:

• column1, column2, ... are the columns you want to retrieve from the main table.
• table_name is the name of the main table you are querying.
• column_name is the column used for comparison in the subquery.
• OPERATOR is a comparison operator such as "=", ">", "<", "IN", "NOT IN", etc., used to link the main query and the subquery.
• another_table is the name of the table used in the subquery.
• condition is the condition used to filter the data in the subquery.

Keep in mind that subqueries can be powerful but may also impact the performance of your SQL queries. It’s essential to use them judiciously and optimize your queries as needed.

14. SQL Views

A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table, and is a great way to encapsulate your queries.

To create a view, you use the CREATE VIEW statement with the following syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

In this syntax:

• view_name: The name of the view you want to create.
• column1, column2, ...: The list of columns you want the view to include. These columns can be selected from one or more tables.
• table_name: The name of the table(s) from which the view derives data.
• condition: An optional condition that filters the data in the view. It uses the same syntax as the WHERE clause in a regular SQL query.

Once the view is created, you can use it like a regular table in your SQL queries. However, it’s essential to note that views only store the query logic, not the data itself. The data is fetched dynamically from the underlying table(s) every time the view is referenced in a query.

15. Transactions

A transaction is a unit of work performed against a database. Transactions in SQL follow the ACID properties, ensuring the reliability of data in all transactions.

Transactions ensure that either all the operations within the unit are successfully completed, or none of them take effect. The standard syntax for SQL transactions is as follows:

BEGIN TRANSACTION; -- Start the transaction

-- SQL statements to perform various database operations
-- For example, INSERT, UPDATE, DELETE, etc.

COMMIT; -- Commit the transaction, making all changes permanent

-- If any issue arises during the transaction, use ROLLBACK to undo the changes:

ROLLBACK; -- Rollback the transaction, undoing all the changes

Some database systems, like MySQL, PostgreSQL, and SQL Server, also support implicit transactions, which means each SQL statement is considered a separate transaction unless specified otherwise using the BEGIN TRANSACTION, COMMIT, or ROLLBACK commands.

However, it is recommended to use explicit transactions (as shown above) when working with critical or complex operations to maintain data integrity and consistency.

16. Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused repeatedly. It can be considered as a batch file that contains a series of SQL commands.

Below are the general syntax examples for creating a simple SQL Stored Procedure in MySQL and SQL Server:

MySQL Syntax:

DELIMITER //
CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...)
BEGIN
    -- SQL statements and logic for the procedure
    -- You can use the parameters in your SQL queries and operations
END //
DELIMITER ;

SQL Server Syntax:

CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype,
    -- Define other parameters as needed
AS
BEGIN
    -- SQL statements and logic for the procedure
    -- You can use the parameters in your SQL queries and operations
END;

In both cases, you define the procedure’s name, input parameters (if any), and then write the SQL statements and logic inside the BEGIN and END block. After creating the stored procedure, you can call it by its name with appropriate arguments to execute the defined SQL logic.

Conclusion

In just about 12 minutes, you have familiarized yourself with the major SQL concepts, from understanding databases and SQL, to various SQL commands, functions, constraints, and indexing. You’ve taken a big step towards SQL proficiency. Practice these concepts and delve deeper to become a true SQL maestro!

Remember, the key to mastering SQL, like any language, is practice. Use these concepts as a foundation and continue to expand your knowledge by practicing SQL queries and commands.

Happy querying!

You may also like,