SQL, short for Structured Query Language and pronounced Sequel (or Squeal by a strange few) is a standardized programming language used for managing and manipulating relational databases.

Relational databases are a tabular database management system or DBMS, where the data within the tables are related to each other by common attributes and even maintain relationships with data in different tables.

This means that they are robust while being flexible and easily scalable.

The relationship between the different tables in a relational database are managed by 'keys', of which there are two:

• Primary Key
  This is a column (usually the first) which has all it's values to be unique with no null or repeating characters.
  Each table has only one primary key.

• Foreign Keys
  These are columns or group of columns that refer to the primary keys of other tables.
  These keys can have null and repeating characters and are used to establish a connection between different tables.

COMMON DATA TYPES

The following are the three (3) basic data types that can be stored in and requested via SQL.

• String
  This is data consisting of an ordered sequence of characters which could be numbers, letters, symbols or any combination thereof.

• Numeric
  This data type, as the name implies, consists of numbers. This data type, along with date and time can have arithmetic operations performed on them.

• Date & Time
  This data type represents date and time and is usually used for logging purposes. It is usually stored in the YYYY-MM-DD format.

OPERATORS

SQL recognizes arithmetic operators such as those below which can be used to manipulate data.

\= equal to

\> greater than

< less than

<> not equal to

\>= greater than or equal to

<= less than or equal to

BASIC SQL COMMANDS

Before speaking on the basic commands that can be used to easily access and manipulate data via SQL, it should be noted one of the advantages of SQL over other DBMS is it's readability.

As such, it is common practice to write SQL syntax in CAPS, although it isn't case sensitive.

With that said, here are some commands you can use to get started on your SQL journey.

QUERYING DATA

• SELECT
  This statement is used to retrieve data from one or more tables in a database.
  You could also use SELECT * to SELECT ALL

    SELECT column1, column2
    FROM table_name;
  

• FROM
  This clause specifies the table or tables from which to retrieve data.

    SELECT column1, column2
    FROM table1, table2;
  

• WHERE
  This command is used to filter rows based on a specified condition.

    SELECT column1, column2
    FROM table_name
    WHERE condition;
  

• %
  The % symbol is a wildcard character used in conjunction with the LIKE operator to match zero or more characters in a string, ie to match a broad range of characters.
  As an example, WHERE country LIKE 'US%'; would return all records with both 'US' and 'USA' whereas WHERE country LIKE 'USA'; would only return records containing 'USA' specifically.

    SELECT column1, column2
    FROM table_name
    WHERE column1 LIKE 'prefix%';
  

• LIKE
  This operator is used to search for a specified pattern in a column.

    SELECT column1, column2
    FROM table_name
    WHERE column1 LIKE 'pattern';
  

• BETWEEN
  This command is used to select values within a specified range.

    SELECT column1, column2
    FROM table_name
    WHERE column1 BETWEEN value1 AND value2;
  

• INNER JOIN
  This command is used to combine rows from two or more tables based on a related column between them.

    SELECT table1.column1, table2.column2
    FROM table1
    INNER JOIN table2 ON table1.column = table2.column;
  

• LEFT JOIN
  This command returns all rows from the left table (table1) and the matching rows from the right table (table2).

    SELECT table1.column1, table2.column2
    FROM table1
    LEFT JOIN table2 ON table1.column = table2.column;
  

• RIGHT JOIN
  This command returns all rows from the right table (table2) and the matching rows from the left table (table1).

    SELECT table1.column1, table2.column2
    FROM table1
    RIGHT JOIN table2 ON table1.column = table2.column;
  

• OUTER JOIN
  This command returns all rows from both tables, including rows that do not have a match in the other table.

    SELECT table1.column1, table2.column2
    FROM table1
    FULL OUTER JOIN table2 ON table1.column = table2.column;
  

MANAGING DATA

• Creating a Database: To create a new database, you use the CREATE DATABASE statement followed by the name of the database.

    CREATE DATABASE MyDatabase;
  

• Creating Tables: Tables are used to store data in a database. To create a table, you use the CREATE TABLE statement followed by the table name and column definitions. For example:

     CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        age INT,
        email VARCHAR(100)
    );
  

  In this example, we're creating a table named users with columns for id, name, age, and email.

• Inserting Data: You can insert data into a table using the INSERT INTO statement. For example:

    INSERT INTO users (name, age, email) 
    VALUES ('John Doe', 30, 'john@example.com');
  

• Querying Data: To retrieve data from a table, you use the SELECT statement. For example:

    SELECT * 
    FROM users;
  

  This query will return all rows and columns from the users table.

• Filtering Data: You can filter the results using the WHERE clause. For example:

    SELECT * 
    FROM users 
    WHERE age > 25;
  

  This query will return users whose age is greater than 25.

• Updating Data: To modify existing data, you use the UPDATE statement. For example:

    UPDATE users 
    SET age = 35 
    WHERE name = 'John Doe';
  

  This query will update the age of the user with the name 'John Doe' to 35.

• Deleting Data: To remove data from a table, you use the DELETE FROM statement. For example:

    DELETE 
    FROM users WHERE age > 40;
  

  This query will delete users whose age is greater than 40.

• Sorting Data: You can sort the results using the ORDER BY clause. For example:

    SELECT * 
    FROM users ORDER BY age DESC;
  

  This query will return users sorted by age in descending order.

• Grouping Data: You can group rows using the GROUP BY clause. For example:

    SELECT age, COUNT(*) 
    FROM users GROUP BY age;
  

  This query will count the number of users for each age.

• Joining Tables: You can combine data from multiple tables using JOIN statements. For example:

    SELECT users.name, orders.order_id 
    FROM users JOIN orders ON users.id = orders.user_id;
  

  This query will return the names of users along with their order IDs from a hypothetical orders table, assuming there's a relationship between the users and orders tables through the user_id field.

---

It is important to note that while these commands might seem numerous, there is no need to memorize any of these as they can be easily searched up at any time.

If you liked this article, check out some Linux commands everyone should know here.