SQL Tutorial

Introduction

Structured Query Language (SQL) is the standard language used to communicate with databases. It allows you to create, manipulate, and retrieve data efficiently. In this tutorial, we’ll explore essential SQL commands and concepts, helping you build a solid foundation for working with databases.

Types of Queries in SQL

DDL (Data Definition Language):

Queries that Create, drop, and modify database structures are DDL Queries

Create
Drop
Alter
Truncate
Rename

DML (Data Manipulation Language):

Queries used to manipulate the data are known as DML Queries.

Insert
Delete
Update

DQL (Data Query Language):

Queries used to Retrieve data

Select

TCL (Transaction Control Language):

Only DML statements such as INSERT, DELETE, and UPDATE can be used with TCL statements.

These functions cannot be used when creating or deleting tables because they are committed to the database.

Commit
Rollback
Savepoint

DCL (Data Control Language):

DCL commands are used to grant and take back authority from any database user.

Grant
Revoke

Creating Databases and Tables

Create a Database:

				
					CREATE DATABASE database_name;

Use a Database:

				
					USE database_name;

Create a Table:

				
					CREATE TABLE table_name (
    col1_name datatype,
    col2_name datatype,
    col3_name datatype,
    ...
);

Viewing Databases and Tables

View All Databases:

				
					SHOW DATABASES;

View All Tables in a Database:

				
					SHOW TABLES;

View Table Description:

				
					DESC table_name;

View Table Content:

				
					SELECT * FROM table_name;

Inserting and Querying Data

Insert Data into a Table:

				
					INSERT INTO table_name (col1_name, col2_name, col3_name, ...) 
VALUES (val1, val2, val3, ...);

Insert Multiple rows in a table:

				
					INSERT INTO table_name (col1_name, col2_name, col3_name, ...) 
VALUES 
(val1, val2, val3, ...),
(val1, val2, val3, ...),
(val1, val2, val3, ...)
......................;

Query Specific Columns:

				
					SELECT col1_name, col2_name, ... FROM table_name;

Query with Conditions:

				
					SELECT col1_name, col2_name, ... FROM table_name WHERE condition;

SQL DDL Commands

1) CREATE Command :

The CREATE command is used to create a new database, table, view, or other database objects.

Example : Creating a New table :

				
					CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

2) DROP Command :

The DROP command is used to delete an existing database object, such as a table, database, or view. Be cautious when using DROP, as it permanently removes the object and all its data.

Drop a table :

				
					DROP TABLE table_name;

Drop a database :

				
					DROP DATABASE database_name;

3) ALTER Command:

The ALTER command is used to modify the structure of an existing database object, such as adding, deleting, or modifying columns in a table.

ADD a column :

				
					ALTER TABLE table_name ADD column_name datatype;

Drop a column:

				
					ALTER TABLE table_name DROP COLUMN column_name;

Modify a column(Changing datatype of a column):

				
					 ALTER TABLE table_name MODIFY COLUMN column_name datatype ;

4) TRUNCATE Command:

The TRUNCATE command is used to remove all records from a table, but unlike DROP, it does not delete the table structure. It’s a fast way to delete all data from a table without removing the table itself.

Truncating a table :

				
					TRUNCATE TABLE table_name;

5) RENAME Command:

The RENAME command is used to rename an existing database object, such as a table. This command is useful when you want to change the name of an object without altering its structure or data.

Rename a column :

				
					ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

--OR 

ALTER TABLE table_name CHANGE old_name new_name datatype ;

Rename a table :

				
					RENAME TABLE old_name TO new_name;

SQL DML Commands

1) Insert Command :

The INSERT command is used to add new rows (records) to a table. You can insert a single row or multiple rows at once.

Inserting a single row :

				
					INSERT INTO table_name (col1_name , col2_name, col3_name,....) VALUES (val1 , val2, val3 , …….) ;

Inserting a multiple rows:

				
					INSERT INTO table_name (col1_name , col2_name, col3_name,....)
VALUES (val1 , val2, val3 , …….) , 
(val1 , val2, val3 , …….),
(val1 , val2, val3 , …….) , 
(val1 , val2, val3 , …….),
.......................... ;

UPDATE Command:

The UPDATE command is used to modify existing records in a table. You can update one or more columns for specific rows based on a condition.

				
					UPDATE table_name SET column1 = val1 , column2 = val2 ,.... Where condition;

DELETE Command:

The DELETE command is used delete existing records in a table. You can delete one or more rows based on a condition.

				
					DELETE FROM table_name WHERE condition ;

Operators and Functions

1) Comparison Operators:

'=' Equal
'>' Greater than
'<' Less than
'<=' Less than or equal to
'>=' Greater than or equal to
'<>' Not equal to

2) Logical operators

AND : Return True only when both the condition are True Else return False
OR : Return False only when both the condition are False Else return True
NOT : Return True when condition is False and vise-versa.

3) Aggregate functions

SUM
AVG
MIN
MAX
COUNT

Syntax to use function in SQL:

				
					SELECT function_name(col_name) FROM table_name WHERE condition ;

4) In Operator:

SQL’s IN operator allows you to specify several values in a WHERE clause. It’s used to compare one or more values from a list with one another.

				
					SELECT * FROM table_name WHERE col_name IN (val1, val2, val3, ...);

4) Between Operator:

Use the BETWEEN operator to get numbers that are inside a range.

				
					SELECT * FROM table_name WHERE col_name BETWEEN val1 AND val2;

4) Like Operators (using Wildcards):

Wildcard characters are used with Like operator. It is used to search for a specified pattern from a column.

“%” is used for zero or more characters
“_” is used for a single character

				
					SELECT * FROM table_name WHERE col_name BETWEEN val1 AND val2;

Sorting

Sort in Ascending Order:

				
					SELECT * FROM table_name WHERE condition ORDER BY col_name;

Sort in Descending Order:

				
					SELECT * FROM table_name WHERE condition ORDER BY col_name DESC;

Distinct and Group By statement

Distinct Statement : It is used to return only distinct (different) values.

				
					SELECT DISTINCT col_name FROM table_name WHERE condition;

Group by statement : It groups rows that have the same values into summary rows, like “find the number of employees in same department”. Group By is always used with aggregate functions by grouping the result-set by one or more columns.

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

Keys and Constraints

Unique Key : Allows only unique values in column
Not Null : Do not allow null value in column
Default : Sets default value for a column
Check : Check the condition and allow only those values that satisfy the given condition

				
					CREATE TABLE table_name (
    col_name datatype UNIQUE,
    another_col_name datatype NOT NULL DEFAULT default_value CHECK (condition)
);

Primary Key : Combines NOT NULL and UNIQUE. A table can have only one primary key.

				
					CREATE TABLE table_name (
    col_name datatype PRIMARY KEY
);

Foreign Key :References a column in another table, maintaining referential integrity.

				
					CREATE TABLE table_name (
    col_name datatype,
    FOREIGN KEY (col_name) REFERENCES another_table_name(another_col_name)
);

Set Operations

Union:

				
					SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2;

Intersection:

				
					SELECT col1, col2 FROM table1 INTERSECT SELECT col1, col2 FROM table2;

Union All :

				
					Select col1,col2 ,.... From table1 UNION ALL select col1,col2,.... From table2;

Except :

				
					Select col1,col2 ,.... From table1 EXCEPT select col1,col2,.... From table2;

Joins

Inner Join:

				
					SELECT col1, col2 FROM table1 INNER JOIN table2 ON table1.FK = table2.PK;

Left Join:

				
					SELECT col1, col2 FROM table1 LEFT JOIN table2 ON table1.FK = table2.PK;

Right Join:

				
					SELECT col1, col2 FROM table1 RIGHT JOIN table2 ON table1.FK = table2.PK;

Cross Join:

				
					SELECT col1, col2 FROM table1 CROSS JOIN table2;

IF and CASE Clauses

In MySQL, the IF and CASE clauses are used to implement conditional logic within queries.

If clause:

The IF clause in MySQL is used to perform conditional logic within a query.

				
					IF(condition, true_value, false_value)

Case clause:

The CASE clause is more flexible and can handle multiple conditions.

				
					CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Views

View is a virtual table i.e, it looks like a table but not an actual table as it does not acquire any physical space.
View is a result of a stored query.

Create a View:

				
					CREATE VIEW view_name AS SELECT * FROM table_name WHERE condition;

View is like a table, thus table queries can be implemented on views except DDL queries.

Select data from view:

				
					SELECT * FROM view_name;

Drop a view:

				
					DROP VIEW view_name;

Show All Views:

				
					SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

View with check option

The WITH CHECK OPTION clause is an optional clause that can be added to a CREATE VIEW statement to ensure that any updates or inserts to a view meet the view’s conditions. If the new row doesn’t meet the conditions, the database server will reject the operation with an error.

				
					CREATE VIEW view_name AS select * from table_name where condition WITH CHECK OPTION;

Indexes

Indexing in mysql is a tool that can improve the performance of databases by making it faster to retrieve data.

Index is also stored somewhere in the hard disk.
Index table consists of key value and pointer.

Create a Index:

				
					CREATE INDEX index_name ON table_name (column_name);

Drop a Index:

				
					DROP INDEX index_name ON table_name;

Show All Indexes on table:

				
					SHOW INDEX FROM table_name;

Guidelines to create index :

Automatically creates the indexes for primary key and unique columns.
Index columns that you frequently use to retrieve data.
Index columns that are used for joins to improve joins performance.
Avoid columns that contain too many null values.
Small tables do not require indexes.

Triggers:

It is a set of actions that run automatically when a specified change operation (Insert , Update or Delete) is performed on a specific table.

Create a Trigger:

				
					CREATE TRIGGER trigger_name BEFORE/AFTER 
trigger_event ON 
table_name FOR EACH ROW 
BEGIN 
... 
END;

Drop a Trigger:

				
					DROP TRIGGER trigger_name;

Procedures:

It is a named block of SQL statements that can be stored in the database and executed repeatedly whenever needed.

Create a Procedure:

				
					CREATE PROCEDURE procedure_name([parameter_list])
BEGIN
    -- SQL statements;
END;

Call a procedure:

				
					CALL procedure_name ;

Drop a Procedure:

				
					Drop procedure procedure_name ;

Built-in Functions:

MySQL’s robust functions make complex calculations and data manipulation simple for data managers.

Date and Time function :

Now()
Current_date()
current_time()

String Functions

Upper(str)
Lower(str)
Concat(str1, str2, ….)
Length(str)
Left(str,length)
Right(str, length)
Locate(“char”, text)
Trim(str)
replace(str, old_str ,new_str)
substring(str, start, length)

Mathematical Functions

ABS(num)
Round(decimal_num, digits)
Floor(num)
Rand()

User-Defined Functions:

It is a custom function created by the user to perform specific tasks.

Create the function : to create a function you need to specify function name , parameters and its logic.

Syntax to create a function:

				
					Create function function_name (parameter1_name datatype , parameter2_name datatype ,......)
Returns datatype
Begin
    -- Queries;
    Return result;
End;

Call a function : You can call a function in your queries to perform the desired operation.

				
					Select function_name(agruments);

Conclusion :

Mastering SQL is essential for anyone working with databases, from beginners to seasoned developers. This tutorial has covered fundamental queries, operations, and advanced concepts to help you efficiently manage and manipulate data. With these skills, you’re well on your way to becoming proficient in database management.

If you’re looking to further enhance your SQL and gain hands-on experience, consider joining Netmax Technologies. As a premier institution in IT training, we offer comprehensive courses ranging from 45 days to 4-6 months, designed to prepare you for real-world challenges and career success. Our experienced instructors and practical training approach ensure that you gain the necessary knowledge and skills to thrive in the competitive field of powerbi. Enroll today and take the first step towards becoming a proficient data scientist with Netmax Technologies!