SQL 101: The Ultimate Beginner’s Guide to the World of Relational Databases

SQL (Structured Query Language) is a programming language used to manage and manipulate data stored in relational databases. It is the standard language for interacting with databases, and is used to create, modify, and query databases.

Basic Syntax

SQL statements are made up of a combination of keywords, clauses, and functions. Keywords are used to specify actions, such as SELECT, FROM, WHERE, and UPDATE. Clauses are used to specify the objects that the action will be applied to, such as table names and column names. Functions are used to perform calculations and manipulate data, such as SUM, AVG, and CONCAT.

Here is an example of a simple SQL statement:

SELECT column1, column2
FROM table_name
WHERE condition;

This statement selects the data in the columns column1 and column2 from the table table_name and returns only the rows that meet the specified condition.

Creating Tables

To create a new table in a database, you can use the CREATE TABLE statement.

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

This creates a new table named table_name with three columns, column1, column2, and column3, each with a specified data type.

Inserting Data

To insert data into a table, you can use the INSERT INTO statement. Here is an example:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

This inserts a new row into the table table_name, with the values value1, value2, and value3 in the columns column1, column2, and column3, respectively.

Updating Data

To update data in a table, you can use the UPDATE statement.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

This updates the values in the columns column1 and column2 to value1 and value2, respectively, for all rows that meet the specified condition.

Deleting Data

To delete data from a table, you can use the DELETE FROM statement.

DELETE FROM table_name
WHERE condition;

This deletes all rows from the table table_name that meet the specified condition.

JOIN

The JOIN command in SQL is used to combine rows from two or more tables based on a related column between them. There are several different types of JOINs in SQL, including INNER JOIN, OUTER JOIN, and CROSS JOIN.

INNER JOIN: An INNER JOIN returns only the rows that match the join condition in both tables. It is the default type of JOIN if no specific type is specified.

SELECT *
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

This statement returns all rows from both tables that have a matching value in the common_column column.

OUTER JOIN: An OUTER JOIN returns all rows from both tables, whether or not there is a match in the join condition. There are two types of OUTER JOINs: LEFT JOIN and RIGHT JOIN. A LEFT JOIN returns all rows from the left table (table1) and any matching rows from the right table (table2). A RIGHT JOIN returns all rows from the right table and any matching rows from the left table. Here is an example of a LEFT JOIN:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

This statement returns all rows from table1, as well as any matching rows from table2. If there is no match in table2, NULL values will be returned for the right table’s columns.

CROSS JOIN: A CROSS JOIN returns the Cartesian product of the two tables. In other words, it returns all possible combinations of rows from both tables. Here is an example:

SELECT *
FROM table1
CROSS JOIN table2;

This statement returns all possible combinations of rows from both table1 and table2.

Aggregate functions

Aggregate functions in SQL are used to perform calculations on a set of values. Let’s take a look at SUM and AVG.

SUM: The SUM function calculates the total of a set of values. It takes a column name as an argument and returns the sum of all the values in that column.

SELECT SUM(column_name)
FROM table_name;

This statement returns the sum of all the values in the column_name column of the table_name table.

AVG: The AVG function calculates the average of a set of values. It also takes a column name as an argument and returns the average of all the values in that column.

SELECT AVG(column_name)
FROM table_name;

This statement returns the average of all the values in the column_name column of the table_name table.

Both SUM and AVG can be used with a WHERE clause to specify a condition for which rows should be included in the calculation.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

This statement returns the sum of all the values in the column_name column of the table_name table, but only for rows that meet the specified condition.

Conclusion

These are just a few basic examples of SQL statements, but there are many more features and capabilities of the language. SQL is an essential tool for working with data in relational databases, and is used by businesses, organizations, and individuals all over the world.