SQL101: Introduction to SQL

Structured Query Language(SQL) is a database language that is used to manage and retrieve data stored in a database, such that it can be easily accessed and manipulated. It is a common language recognized by Database Management Systems(DBMS) like MYSQL. We are going to make reference to relational databases since they are mostly used.

• Relational database:Store data in form of tables. As the name suggests, a relation ties two records or tables together.

• Table: made up of rows and columns.

• Rows: A single record in a table.eg one person's details in a User's table.

• Column: value of a particular type eg First_Name column in table person.

• Statement: A text that is uniformly recognized as a valid command by the database.They usually end with a semicolon.
  

CREATE TABLE users(
id INTEGER,
first_name VARCHAR(60),
last_name VARCHAR(60)
);

Note:SQL clauses like CREATE and TABLE are usually written in capital letters.
SQL syntax are divided into data definition language and data manipulation language.

Data Definition Language

They are used to build and modify the structure of your tables,views, indexes and other objects in the database. Examples include:

• CREATE TABLE statement: creates a new table with the specified columns and data types.
• CREATE DATABASE statement: creates a new database.
• ALTER TABLE statement: used to add, modify or delete columns in an existing table.
• ALTER DATABASE statement: modifies an existing database.
• DROP TABLE statement: used to delete an existing table and all of its data.
• CREATE INDEX statement: creates an index (search key)

Data Manipulation Language

They include statements used to work with data in the database.Some examples include:

• INSERT Statement:used to insert new data into a table.
• UPDATE Statement:used to modify the existing data in a table.
• DELETE Statement:used to delete data from a table.
• SELECT Statement:used to retrieve data from one or more tables.

We shall look at examples of these statements in details.

Creating a database

Lets now create a database named customers that we shall be working from.The syntax is as shown:

CREATE DATABASE database_name;

Now to our database
CREATE DATABASE customers;

Some databases might require you to use (``) when defining database schemas eg creating tables and databases.As shown below:

`
CREATE DATABASE `customers`;
`

We can also be able to view all the databases we have created using the following command:

SHOW DATABASES;

Assign a user to our database

Once you are able to access your database either through MySQL prompt or any other way, it is advisable to create a new user in order to avoid using the root user and causing damage to the whole database in general.
Syntax:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Lets create a user named marrie, at localhost, with a password pass_123.

CREATE USER 'marrie'@'localhost' IDENTIFIED BY 'Pass_123';

We can now grant our new user some privileges. The syntax is as shown:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

Lets grant our user access to the customers database.

GRANT PRIVILEGE ON customers.* TO 'marrie'@'localhost';

It’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:

FLUSH PRIVILEGES;

Working in our current database

Now that we have our database assigned to a user, we can start issuing commands to create tables and insert our data in the tables. First we move to our database.
Syntax:
USE database;

Example:
USE customers;

Creating a table

Once inside our customers database , we can now be able to create various tables as discussed above.

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

Lets create a table users as an example.

`
CREATE TABLE users (
id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
birth_day INT,
born_day DATE()
);
`
You can also confirm if the table is created using the following command

SHOW TABELS;

Note that the exact syntax and behavior of the SHOW TABLES statement may vary slightly depending on the specific SQL database management system being used.

Alter table

We can also change the outlook of the table in case we want to add, delete, or modify columns, constraints, indexes, and other attributes of a table.
Syntax:

ALTER TABLE table_name action;

Where table_name is the name of the table you want to modify, and action is the specific alteration you want to make to the table.
Example:

• Add a new column to an existing table:

  `
ALTER TABLE users
ADD COLUMN age INT;
`

• Modify an existing column in a table:

`
ALTER TABLE users
MODIFY birth_day DATE();
`

• Delete a column from an existing table:

`
ALTER TABLE user
DROP COLUMN age;
`

• Add a primary key to a table:

`
ALTER TABLE user
ADD CONSTRAINT PRIMARY KEY (id)
`

Note that the exact syntax and behavior of the ALTER TABLE statement may vary slightly depending on the specific SQL database management system being used. Also, it's important to be careful when using ALTER TABLE, as modifying the structure of a table can affect the data that is stored in the table.

Inserting data in a table

We have our table, we can now feed it with some data.
Syntax:

INSERT INTO table_name (column_name_1, column_name_2, column_name_3)VALUES (value_1, value_2, value_3);

Note that the order of the values in the "VALUES" clause must match the order of the columns in the "INSERT INTO" statement.
Example:

INSERT INTO users (id, first_name, last_name, email, birth_day, born_day)
VALUES (1, 'John', 'Doe', 'johndoe@example.com', '1980-01-01', '2000-01-01');

You can also insert multiple rows at once by separating each set of values with a comma, like this:

`
INSERT INTO users (id, first_name, last_name, email, birth_day, born_day)
VALUES
(1, 'John', 'Doe', 'johndoe@example.com', '1980-01-01', '2000-01-01'),
(2, 'Jane', 'Kim', 'janekim@example.com', '1985-02-02', '2005-02-02'),
(3, 'Bob', 'Smith', 'bobsmith@example.com', '1990-03-03', '2010-03-03');
`
This would insert three new rows into the "users" table, one for each set of values.

Viewing the data in the table.

Now lets verify that we added something to our table.

SELECT * FROM users;

You will notice that the above displays all the data in the users table.
We can also select a specific column from the table.

SELECT first_name, last_name FROM users;

We can also place a condition within which the data we want can be collected.

SELECT first_name, last_name FROM users WHERE birth_day='1980-01-01';

Updating the data in the database

It is also possible to modify the data we inserted in the table in case you need to change the data already existing in the database table.
Syntax:

`
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
`
Lets change the name of John Doe to Joe Doo

`
UPDATE users
SET first_name = 'Joe', last_name = 'Doo'
WHERE email = 'johndoe@example.com';
`

Deleting a record from the table

We can also remove records from a table.
Syntax:

`
DELETE FROM table_name
WHERE condition;
`
Example

`
DELETE FROM users
WHERE born_day < 2010-03-03;
`

SQL Constraints

Constraints ensure that the data in a table is consistent and accurate by limiting the type of data that can be inserted into a table, or by defining relationships between tables. Here are some examples of constraints in SQL:

• NOT NULL This constraint ensures that a column cannot have a NULL value. Example:

ALTER TABLE users MODIFY last_name VARCHAR(60) NOT NULL

• PRIMARY KEY This constraint uniquely identifies each record in a table. A primary key column cannot have NULL values, and the values in the column must be unique. Example:

ALTER TABLE users ADD PRIMARY KEY (id)

• FOREIGN KEY
  This constraint establishes a relationship between two tables by defining a column in one table as a foreign key that refers to the primary key of another table.
  `
ALTER TABLE users
ADD CONSTRAINT fk_customer FOREIGN KEY(age) REFERENCES customers(id);
`

• UNIQUE
  This constraint ensures that the values in a column are unique.
  ALTER TABLE users MODIFY email VARCHAR(100) UNIQUE

• CHECK
  This constraint allows you to specify a condition that must be true for a record to be inserted or updated.
  ALTER TABLE customers ADD CONSTRAINT ck_age CHECK (age >= 18);

• ENUM
  This constraint contains a string object with a value chosen from a list of permitted values.

`
-- Creating the table
CREATE TABLE shops (
id INTEGER PRIMARY KEY,
name VARCHAR (55),
quality ENUM ('High','Average','Low')
);
-- Insert into the table
INSERT INTO shops VALUES(1 'Johny' 'Low');
INSERT INTO shops VALUES(1 'Doe' 'Average');
INSERT INTO shops VALUES(1 'Marrie' 'High');
`

• SET A set can have zero or more values chosen from a list of permitted values.

`
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name VARCHAR(60),
cert SET('A1','A2','B1','B2')
);
`
Each student can have O,1 or more of these certificates. In ENUM however, you can have only one distinct value.
INSERT INTO students VALUES(1, 'Paul', A1,B1,B2);

Aggregate Functions in SQL

They are used to perform various operations on the data stored , hence enabling us to compute data not found in the database.
Some of the most commonly used functions include:

• AVG():It returns the average value of a numeric column.

`
Syntax: SELECT AVG(column_name) FROM table_name;
Example: SELECT AVG(age) FROM users;
`

• COUNT():It returns the number of rows in a table or the number of non-null values in a column.

`
Syntax: SELECT COUNT(column_name) FROM table_name;
Example: SELECT COUNT(*) FROM users;
`

• MAX():It returns the maximum value in a column.

`
Syntax: SELECT MAX(column_name) FROM table_name;
Example: SELECT MAX(age) FROM users;
`

• MIN():It returns the minimum value in a column.

`
Syntax: SELECT MIN(column_name) FROM table_name;
Example: SELECT MIN(age) FROM users;
`

• SUM():It returns the sum of values in a numeric column.

`
Syntax: SELECT SUM(column_name) FROM table_name;
Example: SELECT SUM(age) FROM users;
`

• UPPER(): It converts a string to uppercase.

`
Syntax: SELECT UPPER(column_name) FROM table_name;
Example: SELECT UPPER(first_name) FROM user;
`

• LOWER(): It converts a string to lowercase.

`
Syntax: SELECT LOWER(column_name) FROM table_name;
Example: SELECT LOWER(last_name) FROM employees;
`

• SUBSTRING():It extracts a sub string from a string.

`
Syntax: SELECT SUBSTRING(column_name, start_position, length) FROM table_name;
Example: SELECT SUBSTRING(first_name, 1, 3) FROM user;
`

• CONCAT():It concatenates two or more strings.

`
Syntax: SELECT CONCAT(string1, string2, ...) FROM table_name;
Example: SELECT CONCAT(first_name, ' ', last_name) FROM users;
`

• DATE():It returns the current date.

`
Syntax: SELECT DATE();
Example: SELECT DATE();
`

• MONTH():It returns the month of a date.

`
Syntax: SELECT MONTH(date_column) FROM table_name;
Example: SELECT MONTH(birth_day) FROM users;
`

• YEAR():It returns the year of a date.

`
Syntax: SELECT YEAR(date_column) FROM table_name;
Example: SELECT YEAR(birth_day) FROM users;
`

• DATEDIFF():It calculates the difference between two dates.

`
Syntax: SELECT DATEDIFF(date1, date2) FROM table_name;
Example: SELECT DATEDIFF(born_date, birth_day) FROM employees;
`

• NOW():It returns the current date and time.

`
Syntax: SELECT NOW();
Example: SELECT NOW();
`

The where clause

This clause is used to filter sql statements base on a certain condition or set of conditions.It is used in conjunction with the SELECT, UPDATE, DELETE statements to filter data from tables. Forexample:

SELECT * FROM people WHERE age >= 18;

The above example will select all the data for people above 18 years from the database.

The AND clause

We can also combine our where clause with an AND clause in order to select data based on multiple conditions. Lets view data about all developers above 18 years.

`
SELECT *
FROM people
WHERE age >= 18
AND job = 'dev';
`
We can also improve our comparison and add the OR clause as shown.

`
SELECT *
FROM people
WHERE (age >= 18 OR age < 40)
AND (job = 'dev' OR job = 'IT');
`

The BETWEEN clause

Sometimes it might be necessary to return values within a specified range, inclusive of the start and end values.
We can modify the example above as shown.

`
SELECT *
FROM people
WHERE age
BETWEEN 18 AND 40;
`
We can also combine the BETWEEN clause with AND / OR. Now lets pick data for all products with prices between 10 and 20 0r 30 and 40.

`
SELECT *
FROM products
WHERE (price BETWEEN 10 AND 20)
OR (price BETWEEN 30 AND 40);
`

The IN and NOT IN clauses

The IN clause is used to specify multiple values in a WHERE clause.Lets select name and age of people with 18,19 and 20 years of age.

`
SELECT id, name, age
FROM people
WHERE age IN (18, 19, 20);
`
The NOT IN clause fulfills the opposite of an IN clause. Lets return data of people and their ages and name except those who are 18, 19 and 20.

`
SELECT id, name, age
FROM people
WHERE age NOT IN (18, 19, 20);
`

The LIKE clause

It can be used in the WHERE clause to search for a pattern in a column.
The '%' pattern matches 0, 1 or many characters in a text.
The '_' pattern matches a single character.
We can have a table called employee with the data shown bellow

We can use the '%' pattern to select employees whose name contain 'i' character.

`
SELECT *
FROM employees
WHERE name LIKE '%i%';
`
This prints

We can also select employees whose name begins with 'J' and contain 'ohn'.

`
SELECT *
FROM employees
WHERE name LIKE '_ohn';
`
This prints

NOT LIKE - is used to find patterns that do not match the records specified.

The GROUPBY clause

Used to put together rows with the same value in one column or set of columns and then perform aggregate functions (such as SUM, COUNT, AVG, MIN, MAX) on the grouped data.
Now lets group the people column according to their ages

`
SELECT age, COUNT(*) as total_age
FROM people
GROUP BY age;
`

All the aggregate functions discussed above can be used with the GROUPBY clause. The column to be grouped by MUST be selected as well otherwise you will get an error.

ORDER BY clause

It sorts the results of a query in ascending or descending order based on the selected columns.
We can select the name and age columns from people and order them by their age in descending order.

`
SELECT name, age
FROM people
ORDER BY age DESC;
`

Note that by default the columns are ordered in ascending order. So you can remove the 'DESC' to maintain the same order.

The GROUP BY and ORDER BY even the WHERE clause can be used in one SELECT query. When this happens, make sure the ORDER BY clause comes after the GROUP BY. For example:

`
SELECT name, age, count(age) as total_age
FROM people
WHERE (age = 18 OR age =19)
GROUP BY age, name
ORDER BY age, name DESC;
`
In the query above we have grouped the people's data by age and name and arranged them in descending order.

The HAVING clause

It is used to specify the conditions that must be met with the group in order to be included in the query result. It also helps us to filter based on the result of an aggregate functions which cannot be used in a WHERE clause.

`
SELECT age, SUM(*)
FROM people
GROUP BY age
HAVING SUM(*) > 100;
`

The query above prints the sum of each age category in the database with a sum greater than 100.

SQL JOINS

Joins are used to combine two or more tables in order to easily see data from the two tables and their relationship.
The join is specified in the FROM clause of a SELECT query.
SELECT * FROM users NATURAL JOIN orders

The above query creates a joined table with matched values on both tables. This establishes a one to many relationship between the user and the customers table. Meaning one user can be a customer of various products that the company offers. Note that the users who are not customers are not going to be selected.

Join Types

A natural join however, does not take into consideration the primary key and the foreign key which are very vital in a table relationship. It only considers common columns in both tables.If there are any common columns that are not primary key and foreign key, they will also be matched and only one copy of the output produced.
Lets look at some useful join types that can be used in place of a natural join.

• INNER JOIN It returns common rows in both table, just like a natural join taking into consideration both the Primary Key and the Foreign Key.

`
SELECT *
FROM users
INNER JOIN customers
ON user.id = customers.user_id;
`
You can also specify an alias for the tables for easy identification as shown.
`
SELECT *
FROM users AS u
INNER JOIN customers AS c
ON u.id = c.user_id;
`

• OUTER JOIN

One effect of INNER JOIN and NATURAL JOIN is that unmatched primary key is dropped ie a user who is not a customer of the company is simply dropped.
We can use an OUTER JOIN to include such customers.
A FULL OUTER JOIN returns all the rows from both tables along with NULL values where there is no match in either table.

`
SELECT *
FROM users
FULL OUTER JOIN customers
ON users.id = customers.user_id;
`
We can also have a FULL OUTER JOIN without intersection. Meaning we shall return all the records matching the ON clause, excluding rows found in both tables.
`
SELECT *
FROM users
FULL OUTER JOIN customers
ON users.id = customers.user_id;
WHERE users.id IS NULL
OR customers.user_id IS NULL
`

To give users a higher priority we use LEFT OUTER JOIN

`
SELECT *
FROM users
LEFT OUTER JOIN customers
ON users.id = customers.user_id;

`
To give customers a higher priority we use a RIGHT OUTER JOIN ie all values in the right and matching values in the left and NULL where a value in the left is missing.

• Self Joins This is a selfish type of join where a table is joined to itself as if it were two different tables with different aliases for each instances of the table. Consider the student table below:

We can do a self join of the table above as shown below:
`
SELECT s.name AS student, l.name as leader
FROM student AS s
LEFT JOIN student AS l
ON s.student_id = l.id;
`

The output of the above code will be:

• Cross Joins They are used to create all the possible combinations of two tables.It is also known as a Cartesian product, since it produces a result set that contains all possible combinations of rows between the two tables. It is similar to the Inner Join, where the join condition is not available with this clause.

The syntax is as follows:

`
SELECT *
FROM table1
CROSS JOIN table2;
`
Consider the following department and students table:
departments

students

The query will be as shown:

`
SELECT *
FROM students
CROSS JOIN departments;
`
The resultant table will be as shown:

Note that in the result set, there are 9 rows, which is the product of the number of rows in students (3) and the number of rows in departments (3).

Set Operations

They are used to manipulate and combine data from multiple tables and queries.

• UNION

The UNION operator combines the result sets of two or more SELECT statements into a single result set. The columns in the SELECT statements must have the same data type.

`
SELECT first_name, last_name
FROM users
UNION
SELECT prod_1, prod_2
FROM customer;
`

• INTERSECT

The INTERSECT operator returns only the rows that appear in both result sets of two or more SELECT statements.
`
SELECT first_name, last_name
FROM users
INTERSECT
SELECT prod_1, prod_2
FROM customer;
`
-EXCEPT

The EXCEPT operator returns only the distinct rows that appear in the first result set of two SELECT statements but not in the second result set.

`
SELECT first_name, last_name
FROM users
EXCEPT
SELECT prod_1, prod_2
FROM customer;
`

Conclusion

SQL is a very powerful query language , with very many advantages when it comes to data analysis and manipulation. There is a lot that is not covered in this article, I just picked a few things I found worth recalling. For more information, visit the W3school SQL documentation and snowflake as well. Once you understand the SQL syntax you can referrer to this cheat-sheet for a quick reminder of the queries. Happy coding and as always stay golden dev!!