SQL Tutorial for Beginners [Coding Streams]

This repository contains a comprehensive SQL tutorial designed for beginners. Whether you're new to programming or just getting started with SQL, this tutorial will provide you with a strong foundation in the basics of SQL and relational database management.

The tutorial covers essential SQL concepts, including database creation, table creation, data insertion and retrieval, filtering, sorting, grouping, and more. It also covers advanced SQL topics such as joins, subqueries, and functions.

Each topic in the tutorial includes clear explanations, examples, and practice exercises to help you reinforce your understanding of the material. Additionally, the repository includes a collection of sample SQL queries that you can use as templates for your own projects.

Whether you're looking to build a career in data science, web development, or any other field that involves working with databases, this SQL tutorial will provide you with the foundational knowledge and skills you need to succeed.

Database

Databases are used to store and organize large amounts of data in a way that makes it easy to access, manage, and analyze. Here are a few examples of why we need databases:

Online shopping: When you buy something online, your order information is stored in a database. This makes it easy for the retailer to manage orders, inventory, and shipping information.
Social media: Social media platforms like Facebook and Twitter use databases to store user profiles, posts, and comments. This allows users to easily connect with each other and share information.
Banking: Banks use databases to store customer account information, transactions, and loan details. This helps them to manage customer accounts and provide financial services.

Why do we use Database?

Here are some reasons why we need databases:

Efficient data storage: Databases are designed to efficiently store and retrieve large amounts of data.
Easy data access: Databases allow users to easily access and search through large amounts of data, making it easy to find the information they need.
Data consistency: Databases ensure that data is consistent and accurate by enforcing rules and constraints that prevent duplicate or inconsistent data from being stored.
Improved data security: Databases provide various security features such as authentication, authorization, and encryption, to protect data from unauthorized access and ensure data privacy.
Data sharing: Databases allow multiple users or applications to access and share data, enabling collaboration and better decision-making.

Structure of Database

The structure of a database in MySQL consists of tables, columns, and rows.

Tables: A table is a collection of related data organized in rows and columns. Each table has a unique name and is used to store data on a specific topic, such as customers, orders, or products.
Columns: Columns are the vertical sections of a table that define the attributes or characteristics of the data being stored, such as name, age, or address. Each column has a unique name and data type, such as text, number, or date.
Rows: Rows are the horizontal sections of a table that represent a single record or instance of data, such as a specific customer or order. Each row contains data values for each column defined in the table.

In addition to tables, columns, and rows, a MySQL database can also include indexes, keys, and relationships.

Indexes are used to speed up database queries by creating a quick reference to specific columns in a table.
Keys are used to uniquely identify records in a table and to establish relationships between tables.
Relationships are established by creating links between tables based on common columns. This allows data to be shared and referenced across different tables in the database.

SQL

SQL (Structured Query Language) is a programming language used to manage and manipulate data in relational databases. In simple terms, SQL allows you to store, retrieve, modify, and delete data in a database.

Here are a few reasons why SQL is commonly used:

Easy to learn and use: SQL is a very straightforward language, and the basic syntax can be learned in a short amount of time.
Widely used: SQL is one of the most widely used programming languages in the world, and is supported by almost all major database systems.
Efficient: SQL is designed to work with large amounts of data, and is optimized for performance.
Flexible: SQL can be used to perform a wide variety of tasks, from simple data queries to complex data analysis.
Powerful: SQL provides a variety of functions and operators that allow you to manipulate and analyze data in many different ways.

Data Types

MySQL supports several data types and structures to define the attributes of data stored in a table. Here are the basic data types and structures in MySQL:

Numeric data types: Numeric data types are used to store numeric values, such as integers or decimal numbers. Examples include INT, BIGINT, FLOAT, and DECIMAL.
Character data types: Character data types are used to store text data, such as names, addresses, or descriptions. Examples include CHAR, VARCHAR, and TEXT.
Date and time data types: Date and time data types are used to store dates and times. Examples include DATE, TIME, and DATETIME.
Boolean data type: Boolean data type is used to store logical values, such as true or false. It is represented as TINYINT(1).
Binary data types: Binary data types are used to store binary data, such as images or audio files. Examples include BLOB and VARBINARY.

Create Database

To create a database in MySQL, you can use the following steps:

Open the MySQL command-line interface: You can do this by opening the terminal or command prompt and entering the command mysql -u [username] -p.
Enter your MySQL user password when prompted.

Once you're in the MySQL shell, type the following command to create a new database:

CREATE DATABASE database_name;
-- Replace `database_name` with the name you want to give your database.

To check if your database was created successfully, you can type the following command:

SHOW DATABASES;
-- This will show you a list of all the databases in your MySQL server, including the one you just created.

You can also use the following command to switch to your new database:

USE database_name;
-- This will allow you to start working with the tables and data in your new database.

Create Tables

Before creating tables in MySQL, it's important to keep the following things in mind:

Plan your database schema: This involves designing the structure of your database, including the tables, columns, data types, and relationships between tables. Take the time to carefully plan your schema before creating any tables, as it will save you time and effort later on.
Choose meaningful table and column names: Use descriptive names for your tables and columns that accurately reflect their contents. This will make it easier for you and other developers to understand the purpose of each table and column.
Use appropriate data types: Choose the appropriate data type for each column based on the type of data it will store. For example, use INTEGER for whole numbers and VARCHAR for text.
Define primary and foreign keys: Define primary keys for each table to uniquely identify each record, and define foreign keys to establish relationships between tables. This will help ensure data integrity and prevent data inconsistencies.
Define constraints: Use constraints to enforce rules and restrictions on the data stored in your tables. For example, use NOT NULL to require a value for a column, and use UNIQUE to prevent duplicate values.

To create a table in MySQL, you can use the following steps:

Open the MySQL command-line interface: You can do this by opening the terminal or command prompt and entering the command mysql -u [username] -p.
Enter your MySQL user password when prompted.

Once you're in the MySQL shell, select the database in which you want to create the table by typing:

USE database_name;
-- Replace "database_name" with the name of the database where you want to create the table.

To create a new table, use the CREATE TABLE statement followed by the table name, and a list of column definitions inside parentheses. For example:

CREATE TABLE table_name (
column1_name datatype constraint,
column2_name datatype constraint,
...
);
-- Replace "table_name" with the name you want to give your table, and add column definitions as needed.

Define the data types and constraints for each column. For example, to create a table with columns for name, age, and email, you can use the following statement:

CREATE TABLE users (
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(50) UNIQUE
);
-- This creates a table called "users" with columns for name, age, and email. The "name" column is defined as a VARCHAR with a maximum length of 50 characters and is required (NOT NULL). The "age" column is defined as an integer, and the "email" column is defined as a VARCHAR with a maximum length of 50 characters and must be unique.

After defining the columns, you can add any additional constraints, such as PRIMARY KEY or FOREIGN KEY. For example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- This creates a table called "orders" with columns for order_id, user_id, and order_date. The "order_id" column is defined as a primary key, and the "user_id" column is defined as a foreign key that references the "user_id" column in the "users" table.

Constraints in SQL

In SQL, there are several types of constraints that can be used to enforce rules and restrictions on the data stored in a table. Some of the most common types of constraints are:

NOT NULL: This constraint requires that a column must have a value, and cannot be left blank (NULL).
UNIQUE: This constraint requires that the values in a column must be unique, and cannot be repeated.
PRIMARY KEY: This constraint is used to uniquely identify each record in a table. It requires that a column or set of columns must have a unique value for each record, and cannot be left blank (NULL).
FOREIGN KEY: This constraint is used to establish a relationship between two tables. It requires that a column or set of columns in one table must match the primary key of another table.
CHECK: This constraint is used to ensure that the values in a column meet certain conditions. For example, it can be used to ensure that a numeric column is within a certain range of values, or that a text column contains a specific pattern of characters.
DEFAULT: This constraint specifies a default value for a column if no value is provided. For example, you can use this to set a default value of 0 for a numeric column, or a default value of "Unknown" for a text column.

Insert Data Into Tables

To insert data into a table in MySQL, you can use the INSERT INTO statement. Here are the basic steps:

Make sure you have selected the database where the table is located by using the USE statement:
```
USE database_name;
-- Replace "database_name" with the name of your database.
```

Use the INSERT INTO statement to specify the name of the table and the values you want to insert:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
-- Replace "table_name" with the name of your table, and "column1", "column2", "column3", etc. with the names of the columns you want to insert data into. Then replace "value1", "value2", "value3", etc. with the values you want to insert into those columns.

For example, to insert a new record into a table called "employees" with columns for "name", "age", and "salary", you can use the following statement:

INSERT INTO employees (name, age, salary)
VALUES ('John Doe', 25, 50000);
-- This will insert a new record into the "employees" table with the name "John Doe", age 25, and a salary of 50000.

If you want to insert multiple records at once, you can use a single INSERT INTO statement with multiple sets of values:

INSERT INTO employees (name, age, salary)
VALUES ('John Doe', 25, 50000),
    ('Jane Smith', 30, 60000),
    ('Bob Johnson', 35, 70000);
-- This will insert three new records into the "employees" table with the specified names, ages, and salaries.

Query Data Using SELECT Clause

To query data from a MySQL database, you can use the SELECT statement. Here are the basic steps:

Make sure you have selected the database where the table is located by using the USE statement:
```
USE database_name;
-- Replace "database_name" with the name of your database.
```

Use the SELECT statement to specify the columns you want to retrieve and the table you want to query:

SELECT column1, column2, ...
FROM table_name;
-- Replace "column1", "column2", etc. with the names of the columns you want to retrieve, and "table_name" with the name of the table you want to query.

For example, to retrieve all the records from a table called "employees" and display their names and salaries, you can use the following statement:
```
SELECT name, salary
FROM employees;
```

Filter Data Using WHERE Clause

To filter data using the WHERE clause in MySQL, you can use the following steps:

Add a WHERE clause to the SELECT statement to filter the data based on certain conditions:

SELECT column1, column2, ...
FROM table_name
WHERE condition;
-- Replace "condition" with the condition you want to filter by.

For example, to retrieve only the records from the "employees" table where the salary is greater than 50000, you can use the following statement:
```
SELECT name, salary
FROM employees
WHERE salary > 50000;
```
You can use various operators in the WHERE clause, such as "=", "<>", "<", ">", "<=", ">=", "LIKE", and "IN", to compare values and filter the data.

You can also combine multiple conditions using logical operators such as "AND" and "OR".

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;
-- Replace "condition1" and "condition2" with the conditions you want to combine.

For example, to retrieve only the records from the "employees" table where the salary is greater than 50000 and the age is less than 30, you can use the following statement:
```
SELECT name, salary
FROM employees
WHERE salary > 50000 AND age < 30;
```

Wildcard in WHERE caluse

In SQL, a wildcard character is a special symbol used to represent one or more characters in a string. The two most commonly used wildcard characters are the percent symbol % and the underscore symbol _.

The percent symbol % represents zero or more characters, while the underscore symbol _ represents a single character.

When used in a WHERE clause, these wildcard characters can be used to search for patterns in a column value or string.

For example, to select all rows from a table where the value in the name column starts with the letter 'J', you can use the % symbol as follows:

SELECT * FROM my_table WHERE name LIKE 'J%';
-- This query will return all rows where the value in the name column starts with the letter 'J'.

Similarly, if you want to select all rows from a table where the value in the phone_number column ends with the digits '1234', you can use the % symbol as follows:

SELECT * FROM my_table WHERE pho
-- This query will return all rows where the value in the phone_number column ends with the digits '1234'.ne_number LIKE '%1234';

The underscore symbol _ can be used to match a single character, so for example, to find all names in a table that have six letters and end with the letter 's', you could use:

SELECT name FROM my_table WHERE name LIKE '____s';
-- This query will return all names in the name column of my_table that are exactly six characters long and end with the letter 's'.

Sort Data Using ORDER BY

To sort data in MySQL, you can use the ORDER BY clause in the SELECT statement. Here are the basic steps:

Use the SELECT statement to retrieve the data from the table:

SELECT column1, column2, ...
FROM table_name;
-- Replace "column1", "column2", etc. with the names of the columns you want to retrieve, and "table_name" with the name of the table you want to query.

Add the ORDER BY clause to the SELECT statement to sort the data based on a specific column:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name;
-- Replace "column_name" with the name of the column you want to sort by.

For example, to retrieve the records from the "employees" table and sort them by salary in descending order, you can use the following statement:
```
SELECT name, salary
FROM employees
ORDER BY salary DESC;
```
You can use the ASC keyword to sort the data in ascending order (which is the default), or the DESC keyword to sort the data in descending order.

You can also sort the data based on multiple columns by specifying multiple column names in the ORDER BY clause:
```
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name1, column_name2, ...;
```

For example, to retrieve the records from the "employees" table and sort them by department and then by salary, you can use the following statement:
```
SELECT name, department, salary
FROM employees
ORDER BY department, salary DESC;
```

Functions

In MySQL, a function is a pre-defined or user-defined routine that performs a specific task and returns a value. MySQL provides many built-in functions that can be used to perform a wide variety of tasks, such as manipulating data, performing calculations, formatting output, and more. Here are some examples of built-in functions in MySQL:

Mathematical functions: ABS, CEIL, FLOOR, ROUND, etc.
String functions: CONCAT, LENGTH, LOWER, UPPER, TRIM, SUBSTRING, etc.
Date and time functions: NOW, DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
Aggregate functions: COUNT, SUM, AVG, MIN, MAX, etc.

Aggregate Functions

Aggregate functions in MySQL are functions that operate on a set of values and return a single value as the result. These functions are often used to perform calculations on data in a table, such as calculating the average or sum of a column of values. Here are some commonly used aggregate functions in MySQL:

COUNT(): Returns the number of rows in a table or the number of non-null values in a column.
SUM(): Returns the sum of values in a column.
AVG(): Returns the average value of a column.
MIN(): Returns the minimum value in a column.
MAX(): Returns the maximum value in a column.

Here's an example of how to use an aggregate function in MySQL:

SELECT COUNT(*) FROM my_table;
-- This statement retrieves the number of rows in the "my_table" table using the COUNT() function.

Group Data Using GROUP BY

In MySQL, the GROUP BY clause is used to group the rows of a result set based on one or more columns. The GROUP BY clause is typically used in combination with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX to perform calculations on each group of rows.

Here's how to use the GROUP BY clause to group data in MySQL:

Add the GROUP BY clause to the SELECT statement to group the data based on one or more columns:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...;
-- Replace "aggregate_function" with the name of the aggregate function you want to use, such as COUNT, SUM, AVG, MIN, or MAX. Replace "column" with the name of the column you want to perform the calculation on, and "column1", "column2", etc. with the names of the columns you want to group by.

For example, to retrieve the number of employees in each department from the "employees" table, you can use the following statement:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;
-- This statement groups the records by department and calculates the number of employees in each department using the COUNT function.

You can also use the HAVING clause to filter the groups based on a condition:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;
-- Replace "condition" with the condition you want to filter by.

For example, to retrieve the departments with more than 5 employees from the "employees" table, you can use the following statement:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
-- This statement groups the records by department, calculates the number of employees in each department using the COUNT function, and then filters the groups that have more than 5 employees using the HAVING clause.

Relationships

In SQL, a relationship is a connection or association between two or more tables in a relational database. Relationships are created by defining keys in the tables that allow the tables to be linked together. There are three main types of relationships in SQL:

One-to-One Relationship: This is a relationship in which each row in one table is linked to exactly one row in another table. For example, a table of employee information might be linked to a table of employee addresses, with each employee having exactly one address.
One-to-Many Relationship: This is a relationship in which each row in one table can be linked to one or more rows in another table. For example, a table of customers might be linked to a table of orders, with each customer having multiple orders.
Many-to-Many Relationship: This is a relationship in which each row in one table can be linked to one or more rows in another table, and vice versa. For example, a table of students might be linked to a table of courses, with each student taking multiple courses and each course having multiple students.

To create a relationship between two tables, you need to define a foreign key in one table that references the primary key in the other table. The foreign key is a column or set of columns that contain values that match the values in the primary key of the other table. This creates a link between the two tables and allows you to retrieve data from both tables using SQL queries.

Here's an example of how to create a relationship between two tables in SQL:

CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   customer_id INT,
   order_date DATE,
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE customers (
   customer_id INT PRIMARY KEY,
   customer_name VARCHAR(50),
   customer_email VARCHAR(50)
);

-- In this example, the "orders" table is linked to the "customers" table using the "customer_id" column as a foreign key. This allows you to retrieve data from both tables using SQL queries that join the two tables together.

Joins

In MySQL, a join is used to combine rows from two or more tables based on a related column between them. Joining tables is useful for retrieving data that is spread across multiple tables in a database. There are several types of joins in MySQL:

Cross Join: Returns the combination of each row from two or more tables.
Inner Join: Returns only the rows from both tables that have matching values in the specified columns.
Left Join: Returns all the rows from the left table and the matching rows from the right table. If there is no matching row in the right table, NULL values are returned.
Right Join: Returns all the rows from the right table and the matching rows from the left table. If there is no matching row in the left table, NULL values are returned.
Full Outer Join: Returns all the rows from both tables, including the unmatched rows.
Self Join: A self join is a join in which a table is joined with itself. This can be useful when working with hierarchical data, or when trying to compare rows within the same table.

Here's an example of how to use an inner join in MySQL:

SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;
-- This statement joins the "orders" table with the "customers" table based on the "customer_id" column. Only the rows with matching values in both tables are returned.

Subqueries

In SQL, a subquery is a query within another query, used to retrieve data that will be used in the main query. The result of a subquery can be used in various parts of a SQL statement, such as in the SELECT, FROM, or WHERE clauses.

There are two types of subqueries in SQL: correlated and non-correlated. A non-correlated subquery is a subquery that can be run independently of the main query, while a correlated subquery is a subquery that is executed for each row returned by the main query.

The syntax for a non-correlated subquery is as follows:

SELECT column1, column2, ...
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);
-- In this example, the subquery is enclosed in parentheses and is used in the WHERE clause to filter the results of the main query.

A correlated subquery, on the other hand, references a column from the outer query, and is executed for each row returned by the outer query. Here's an example:

SELECT column1, column2, ...
FROM table1 t1
WHERE column1 = (SELECT MAX(column1) FROM table2 t2 WHERE t2.column2 = t1.column2);
-- In this example, the subquery is executed for each row in table1, and references the value of column2 in the current row of table1.

Name		Name	Last commit message	Last commit date
Latest commit History 17 Commits
.gitignore		.gitignore
01_create_database.sql		01_create_database.sql
02_create_tables.sql		02_create_tables.sql
03_constraints.sql		03_constraints.sql
04_insert_data.sql		04_insert_data.sql
05_alter_table.sql		05_alter_table.sql
06_query_data.sql		06_query_data.sql
07_tables.sql		07_tables.sql
07_where_clause.sql		07_where_clause.sql
README.md		README.md

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

SQL Tutorial for Beginners [Coding Streams]

Database

Why do we use Database?

Structure of Database

SQL

Data Types

Create Database

Create Tables

Constraints in SQL

Insert Data Into Tables

Query Data Using SELECT Clause

Filter Data Using WHERE Clause

Wildcard in WHERE caluse

Sort Data Using ORDER BY

Functions

Aggregate Functions

Group Data Using GROUP BY

Relationships

Joins

Subqueries

About

Releases

Packages

codingstreams/sql-tutorial

Folders and files

Latest commit

History

Repository files navigation

SQL Tutorial for Beginners [Coding Streams]

Database

Why do we use Database?

Structure of Database

SQL

Data Types

Create Database

Create Tables

Constraints in SQL

Insert Data Into Tables

Query Data Using SELECT Clause

Filter Data Using WHERE Clause

Wildcard in WHERE caluse

Sort Data Using ORDER BY

Functions

Aggregate Functions

Group Data Using GROUP BY

Relationships

Joins

Subqueries

About

Resources

Stars

Watchers

Forks

Releases

Packages 0

Packages