SQL Tutorial

Introduction

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

Basics of MY SQL Interview Questions

Understanding Select, Insert, Update, Delete in MySQL

Types of Queries in SQL

DDL (Data Definition Language):

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

Create
Drop
Alter
Truncate
Rename

DML (Data Manipulation Language):

Some queries manipulate the data in SQL. Such queries 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 work with TCL statements.

However, you cannot use these for creating or deleting tables because SQL automatically commits those changes.

Commit
Rollback
Savepoint

DCL (Data Control Language):

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

Grant
Revoke

Creating Databases and Tables in SQL

Create a Database:

				
					CREATE DATABASE database_name;

Use a Database:

				
					USE database_name;

Creating a Table:

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

Viewing Databases and Tables in SQL

View All Databases:

				
					SHOW DATABASES;

To view All Tables in a Database:

				
					SHOW TABLES;

Viewing 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, ...);

Inserting 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;

A 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 :

Use DROP command to delete an existing database object, such as a table, database, or view. However you should be cautious when using DROP, as it permanently removes the object and all its data.

To Drop a table :

				
					DROP TABLE table_name;

Dropping a database :

				
					DROP DATABASE database_name;

3) ALTER Command:

You can use ALTER command 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:

TRUNCATE command is used to remove all records from a table, but unlike DROP, it does not delete the table structure. Moreover, 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:

To rename an existing database object such as table, we use RENAME command. Additionally, it 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 ;

Renaming 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.

Insert 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 , …….),
.......................... ;

2) UPDATE Command:

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;

3) DELETE Command:

To delete and existing record in a table DELETE command is used delete. You can delete one or more rows based on a condition.

				
					DELETE FROM table_name WHERE condition ;

Operators and Functions- MySQL Interview Preparation

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, ...);

5) 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;

6) 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;

SQL Sorting queries

Sort in Ascending Order:

				
					SELECT * FROM table_name WHERE condition ORDER BY col_name;

To 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 :

This statement 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- MySQL Interview Concepts

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 in SQL

Union Vs Union ALL- MySQL Interview Guide

				
					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 MY SQL Interview Questions & Answers

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;

Except :

				
					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:

CASE clause is more flexible and can handle multiple conditions.

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

Views of SQL tables

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;

The 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;

Select all views within a database:

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

View with check option

The WITH CHECK OPTION clause is an optional clause and can be added to a CREATE VIEW statement. It 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 improves database performance by faster data retrieval.

It stores the index on the hard disk.
The 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 are used for joins to improve joins performance.
Avoid columns that contain too many null values.
Small tables do not require indexes.

Advance Topics in MySQL Interview

Triggers:

Triggers 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;

Stored Procedures Vs Functions in MySQL

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 ;

SQL 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: in SQL

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. To summarize, this SQL guide has covered fundamental queries, operations, and advanced concepts to help you efficiently manage and manipulate data. With these skills, covered in this SQL tutorial, you’re well on your way to becoming proficient in database management.

Furthermore, if you’re looking to further enhance your SQL and gain hands-on experience, consider joining Netmax Technologies. Additionally, 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. So, enroll today and take the first step towards becoming a proficient data scientist with Netmax Technologies!