Free SQL Tutorials: Basic SQL Tutorial for Beginners

Preface

SQL a query language used throughout the world for creating, modifying and accessing database. Though it ensures intense level of ease and flexibility of implementation and accessibility, it renders this as a splendid platform to utilize flawlessly in the purpose of running databases. However, myriad of posts in air are floating that cover distinct aspects accordingly. But here, I am writing this beginners SQL tutorial only in the vision to make students and fresher acquainted of how to learn SQL. Also, this SQL tutorial for beginners is about to make them educate of SQL commands as well as proficient with entire attributes of SQL basics. Now, before I take you with this free SQL training, let’s have a look over a brief introduction on SQL and its other aspects which is all about.

SQL Introduction

The SQL language which stands for “Structured Query Language” is all about a universal standard language engaged by myriad of RDBMS (Relational Database Management System) for accessing and amending database in a sequence. For the first time it was developed in 1970s by world renowned ANSI/ISO standard organization IBM. However, the implementation of SQL thus verily improvised its aspect and render as a dominant tool despite of other RDBMS systems. Therefore, in order to make learning SQL simpler, easy and convenient, I am presenting this effectual SQL basics tutorial or simply a concise SQL notes that embraced with SQL commands and basics of SQL tutorial that you must read especially if you are standing at learning level. So, devoid of consuming your extra more time, let’s open the first chapter and learn SQL more precisely. In the mean while, I would like to put here some radiance over My SQL Database to avoid confusion between SQL and MySQL Database.

What is My SQL Database

If we define this in simple manner then SQL is a non-procedural language that makes earnest contribution towards processing of massive data in groups of records despite of handling a single record in the time. Some of the imposing aspects that SQL can perform efficiently in lack of any hassles are creating tables and other database objects along with storing, modifying, retrieving, deleting data.

SQL Statements

SQL SELECT Statement

SELECT statement is most regularly used SQL command that is used to retrieve data or query from a specific table in a database. A query seems to be highly earnest toward fetching the data from specific columns of a table. In order to write a SQL SELECT statement, it highly obligatory to define column(s) and concerned table name. However this is termed as SQL SELECT Statement.

SQL SELECT Statement Syntax:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

Where,

Table-name denotes that table from where information is to be retrieved. Column_list are those columns range from where data will be fetched. The code in parenthesis is optional.

SQL INSERT Statement

This SQL INSERT statement is useful to add new data rows in a table. We can insert data in the table by adopting two distinct methods:

1. Direct inclusion of data in a table Syntax

INSERT INTO TABLE_NAME
[ (col1,  col2, col3,..colN) ]
VALUES (value1, value2, value3,....valueN);

Where, col1, col2,…colN are the name columns where you crave to add data. If you supposed to insert a row or adding values to the columns, you should ensure the order of values proportional to the columns in the table instead of specifying the column(s) in SQL query.

INSERT INTO TABLE_NAME
VALUES (value1, value2, value3,....valueN);

2. Engraving data by SELECT statement Syntax

INSERT INTO table_name
[(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM table_name [WHERE condition];

Note:

• Prior to adding a new row, you must ensure the value datatype and column matches.
• You are required to follow integrity constraints, if any assigned to table.

SQL UPDATE Statement

The UPDATE statement contributes to alter an existing row in the table.

Syntax

UPDATE table_name
SET column_name1 = value1,
column_name2 = value2, ...
[WHERE condition]

Where table_name is the table to be updated. column_name1, column_name2… are the columns which are to be altered. Value1, value2… stands for new values. Note: As per above syntax, WHERE is recognized as the affected rows. In case if WHERE clause is overlooked the entire columns values for intact rows get affected.

SQL DELETE Statement

When you are supposed to delete rows from a table The DELETE Statement is obvious.

Syntax

DELETE FROM table_name [WHERE condition];

Where, table_name is the name of table to be modified. Note: Prior to use the Delete statement it is extremely necessary to identify the row to be deleted. In the case if it does not include WHERE clause, it may delete entire rows in the table.

SQL TRUNCATE Statement

This command is used to delete entire rows comprised by a table and obvious to free the space.

Syntax

TRUNCATE TABLE table_name;

SQL DROP Statement

The DROP statement is employed not only to remove an object from database but to delete the entire structure of a table from the database. A table removed by DROP statement neither retrieved back nor can the references be instituted. So, care must be taken prior to bring into employment.

Syntax

DROP TABLE table_name;

SQL CREATE TABLE Statement

As the name imprinted, the SQL CREATE TABLE statement is used to create a new table. It allows integrity constraints defining primary, unique and foreign key towards fabricating a table at both column level and table level. On distinct RDBMS the syntax and implementation of CREATE statements vary.

Syntax

CREATE TABLE table_name
(column_name1 datatype,
column_name2 datatype,
... column_nameN datatype
);

Where, table_name is the name of table. Column_name1, column_name2…… is the name of columns. datatype is the nature of data like char, number, date etc.

SQL ALTER TABLE Statement

The SQL ALTER TABLE Statement plays an earnest role, when it comes to make amendments to the table structure and defining its columns. The ALTER SQL command is extremely efficient to perform these functionalities.

• Add, Drop & Modifying the table columns
• Adding and dropping Constraints
• Disable and Enable Constraints

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

SQL RENAME Command

This SQL RENAME Statement performs to modify the name of a table or an object in the database. Note: When the name of an object will be changed the references made previously will be affected. Therefore, you are required to change the old name by new name to each and every reference.

Syntax

RENAME old_table_name To new_table_name;

SQL GRANT Command

SQL Grant command is used to endow with intact privileges and accessibilities on the objects of database to the users.

Syntax

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

Where, privilege_name is the privilege or right of access to the users. object_name denotes the name of objects in a database. user_name is the user who possesses the right of accessibility. PUBLIC is employed to grant permission of access to entire users. ROLES are the set of privileges clustered together. With Grant Option is used to grant access to a user despite of other users.

SQL REVOKE Command

When you are supposed to remove the privileges and right of access to a user from database object, you can use this SQL REVOKE Command.

Syntax of Revoke Cammand

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

SQL INDEX command

When a database comprises thousands of records in the table, it seems to consume tons of valuable time and very confounding. Therefore, in order to get rid of such complications you can use SQL INDEX Command to create indexes for columns ensuring instant accessibility. Even though, indexes can be fabricated on either a single or multiple column(s). While creating an index, it define a ROW ID for each and every row followed by sorting of data.

Syntax of creating Index in SQL

CREATE INDEX index_name
ON table_name (column_name1,column_name2...);

Syntax of fabricating unique SQL index

CREATE UNIQUE INDEX index_name
ON table_name (column_name1,column_name2...);

Where, index_name is name defined to INDEX table_name denote the table of indexed column column_name1, column_name2… are the column which created the INDEX. SQL Clauses

SQL WHERE Clause

The WHERE clause is useful when you seems to retrieve particular sets of data by filtering irrelevant information from a table. This clause used to refine the data as per the conditions specified by you. Also, the WHERE clause can be used with SELECT, UPDATE and DELETE statements.

Syntax

WHERE {column or expression} comparison-operator value

Syntax for a WHERE clause with Select statement is:

SELECT column_list FROM table-name

WHERE condition; Where, column or expression is the table’s column or an expression. comparison-operator are the operators like = < > etc. value refers to any name of column or a user value employed for comparison. Note: Aliases cannot be used to define a set of conditions for the columns in SELECT statement while in contrary it is created solely for table that is capable to reference the columns in the table. Also, it enables the expression to use in the WHERE Clause.

SQL ORDER BY

This SQL statement ORDER BY is used with combination of SELECT statement to sort the data either in ascending or descending order in the obtained result.

Syntax

SELECT column-list
FROM table_name [WHERE condition]
[ORDER BY column1 [, column2, .. columnN] [DESC]];

Note: The column defined in ORDER BY clause must be falls within selected columns in SELECT column list. This lets you to symbolize the columns in ORDER BY clause with reference to assigning the location of a column in SELECT list despite of inscribing column name.

The query as an illustration can be denoted as –

SELECT name, salary FROM employee ORDER BY 1, 2;

By default the ORDER BY Clause arrange data in ascending order. However, if you wish to retrieve in descending order, you are supposed to elucidate the query as stated below –

SELECT name, salary
FROM employee
ORDER BY name, salary DESC;

This query will sort the data as ‘salary’ column in descending order as well as column ‘name’ by ascending order. In order to fetch data or both column name and salary in descending order, it can be retrieved with aid of query provide below –

SELECT name, salary
FROM employee
ORDER BY name DESC, salary DESC;

SQL GROUP BY Clause

The SQL GROUP BY clause is to execute with group functions in context to fetch data that is grouped on the ground of one or multiple columns. For example: If you wish to get the exact value of total amount of salary for a specific department, then you can use the query as described below –

SELECT dept, SUM (salary)
FROM employee
GROUP BY dept;

SQL HAVING Clause

HAVING clause contribute to refine the data concerned with group functions. It is same as of WHERE condition except the rule of using with group function. Group function can be used with HAVING clause regardless of WHERE Clause.

SQL Operators

SQL Logical Operators

SQL Logical Operators are extremely earnest to establish a comparison among multiple conditions in order to conclude about selection of a row for the output. Basically, there are three Logical operators recognized as AND, OR and NOT. You can employ multiple conditions while retrieving data by using SELECT statement and logical operators in the WHERE clause.

‘OR’ Logical Operator

The ‘OR’ can be used to designate set of rows where as a minimum conditions specified must be true.

For example:

SELECT first_name, last_name, subject
FROM student_details
WHERE subject = 'Maths' OR subject = 'Science'

‘AND’ Logical Operator

‘AND’ operator is used where intact conditions being assigned are true. For example –

SELECT first_name, last_name, age
FROM student_details
WHERE age >= 10 AND age

‘NOT’ Logical Operator

In the case if you want to sort out the specific rows that are not malleable with a condition, then ‘NOT’ operator is used. It retrieves those data which are reverse of the condition. For example –

SELECT first_name, last_name, games
FROM student_details
WHERE NOT games = 'Football'

Comparison Operators

If you are looking for those SQL language statements which are obvious to define the conditions for comparing data of specific rows with particular values and relevant to the conditions specified, you can use comparison operations. They can be also employed with SELECT statement to refine the data on the ground of specific conditions. Following are the operators that define a condition.

S. No.	Sign	Meaning
1	=	equal to
2	< >, !=	not equal to
3	<	less than
4	>	greater than
5	>=	greater than or equal to
6	<=	less than or equal to

Despite of these, there are lots of other comparison operators that can be executed in SQL such as ‘IN’, ‘IS NULL’, ‘BETWEEN…AND’, ‘LIKE’, which contribute to boost the capabilities of search through entire table. Let’s have a momentary look over these operators. IN – column values are equal to any one of the data available with specific set of values IS NULL – column values or data does not exist. LIKE – data or values of a column identical to a specific set of character(s). BETWEEN…AND – column data falls under two values and equal to the end values defined in the column range.

SQL Integrity Constraints

These sections of this SQL beginner’s tutorial post deals with SQL Integrity Constraints, which are efficient to execute distinct business rules throughout intact table or database. Generally, it is defined in two broad methods –

• Column-level definition: This is defined as the constraints to be assigned simultaneously after the defining the column.
• Table-level definition: Here the constraints can be specified only when the intact columns will be defined.

SQL Primary Key Constraints This constraint aids you to recognize each and every row in a table exclusively with a column or blend of columns.

Syntax to assign a Primary Key at column level

column name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to assign Primary Key at table level

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)

Where, Column_name1, column_name2,… is the columns that define primary key for the columns in table. The syntax under parenthesis i.e. [CONSTRAINT constraint_name] is not obligatory.

SQL Foreign Key Constraints

This constraint designates a column which is linked with other PRIMARY KEY in another table. It is extremely efficient to institute a relation among two columns either in one or multiple tables. In order to define a column as a Foreign Key, it is obligatory to define it as a Primary Key in the table about which it submit to. You can define one or multiple columns in a table as Foreign Key.

Syntax for defining Foreign Key at column level

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax for defining Foreign Key at table level

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

SQL Not Null Constraint

The Not Null Constraint ensures table rows to inscribe a specific value in column of the table and that is considered as not null.

Syntax of assigning a Not Null constraint

[CONSTRAINT constraint name] NOT NULL

SQL UNIQUE KEY

This constraint stands for asunder value in a column or group of column in each and every row of a table. A column may restrain null value but strictly endorse unique values that mean it avoids duplicate values.

Syntax to use Unique Key constraint at column level

[CONSTRAINT constraint_name] UNIQUE

SQL Check Constraint

You can use Check Constraint to make a column inscribed with a business rule. The intact rows in a table must be familiar with the rule. It can be implemented either for a single column or a group of columns.

Syntax for inscribing a SQL Check Constraint

[CONSTRAINT constraint_name] CHECK (condition)

Conclusion

Hopefully, my efforts to endow with SQL Tutorial for beginners: A Comprehensive Guide to Learn SQL will be appreciated by all of you especially who crave to be in the pose of obsession for comprehending SQL tutorials for beginners. This is not only efficient to make educated with basics of SQL but also aid to save tons of efforts and valuable time for entire group of SQL students, fresher and experts. Also, it is a perfect congregation of Basic of SQL language and SQL command that provide instant reference.