Introduction to SQL

SQL (Structured Query Language) is a computer-based structured, formatted database language designed for managing data in Relational Database Management Systems (RDBMS). SQL is a standardized computer language that was initially developed by IBM for querying, altering, and defining relational databases using declarative statements.

  • IBM initially developed SQL in the 1970s
  • SQL is pronounced as "sequel".
  • SQL is a computer language for storing, manipulating, and retrieving data in a relational database.
  • SQL allows you to create, modify and query databases.
  • SQL is a standard language that is used by most relational databases.
  • SQL is used to access and manipulate data stored in tables.
  • SQL is used to join multiple tables, create views, stored procedures, and triggers.
  • SQL is used to create and manage the schema of the database.
  • SQL can be used to control access to the data and manage the transaction.
  • SQL is used to create reports and data visualizations.
  • SQL is used for data validation and data integrity.
  • SQL is used for data security and backup, and recovery.
  • SQL is used for performance tuning and optimization.
  • SQL is used for troubleshooting and monitoring the database performance.

what can sql do

SQL (Structured Query Language) can perform a wide range of operations on a relational database. Some of the main things that SQL can do include:

  • Retrieve specific data from one or more tables.
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

Note : These are just a few examples of the many operations that can be performed using SQL, and the specific set of commands and functionality may vary depending on the specific relational database management system (RDBMS) being used.

SQL Syntax rules

SQL syntax has a set of rules that must be followed for a SQL statement to be valid. Here are some of the most important rules to keep in mind when writing SQL statements:

  • SQL statements always start with the keywords
  • SQL is a case insensitve language. For example, SELECT, select both are same.
  • SQL statements must end with a semicolon (;).
  • Table and column names should be one word; if multiple words are used, they should be separated by an underscore (_) or camelCase. String values must be enclosed in single quotes (' ').
  • Date values must be enclosed in single quotes (' ') and should be in the format 'YYYY-MM-DD'.
  • SQL Numeric values should not be enclosed in quotes.
  • Each clause in a SQL statement should be written on a separate line for better readability.
  • The SELECT clause must come first in a SELECT statement, followed by the FROM clause and other clauses such as WHERE, JOIN, GROUP BY, and ORDER BY.
  • When using the WHERE clause to filter results, the comparison operator must be placed between the column name and the value being compared.
  • When joining tables, the ON clause should specify the relationship between the columns of the two tables.

Keep in mind that SQL dialects may vary slightly depending on your specific database management system.

create database

It is used to create database

Syntax:

create database <databaseName>;

Example:

create database practice;

use statement

USE Statement is used to select a database and perform SQL operations into that database

Syntax:

use <databaseName>;

Example:

use practice;

create table statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax:

create table <tableName> (
columnName1 data type ,
columnName2 data type ,
columnName3 data type , .... );

Example

create table employee(
id int ,
name text ,
age int );

INSERT INTO

The INSERT INTO statement is used to insert new records in a table.

Syntax :

insert into <tableName> values
(column1Value,column2Value,column3Value);

Example :

insert into student values
(1,"Rohit",19),
(2,"Jimmy",20),
(3,"Arjun",20)
(4,"Hardik",20)
(5,"Willimason",22)

select statement

The SELECT statement is used to select data from a database

Syntax :

select column1 , column2 , column3 , ... from <tableName> ;

Example :

select * from employee;

id name age
1 Rohit 19
2 Jimmy 20
3 Arjun 20
4 Hardik 20
5 Williamson 22

update statement

The UPDATE statement is used to modify the existing records in a table

Syntax :

update <tableName> set columnName = value , columnName = value where clause;

Example :

update employee set name = "David" , age = 21 where id = 4 ;

id name age
1 Rohit 19
2 Jimmy 20
3 Arjun 20
4 David 21
5 Williamson 22

DELETE statement

The DELETE statement is used to delete existing records in a table.

Syntax :

delete from <tableName> where clause ;

Example :

delete from employee where id=5 ;

Alter table statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

To add column in existing table use the following syntax :

alter table <tableName> add columnName datatype;

To rename a column in a table, use the following syntax:

alter table <tableName> rename column oldName to newName;

To delete a column in a table, use the following syntax:

alter table <tableName> drop column columnName;

Example to add column in existing table

alter table employee add country datatype;

ORDER BY CLAUSE

The SQL ORDER BY clause is used to sort the result set in either ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword

Syntax :

select columnName1 , columnName2 from <tableName> order by columnName asc|desc;

Example :

select id , name from employee order by name asc ;

The above statement select id and name from the employee table and then sort them in ascending order by name.

GROUP BY CLAUSE

The GROUP BY clause is used to group rows by one or more columns.

The GROUP BY clause is used in conjunction with aggregate functions such as MIN(), MAX(), SUM(), AVG() and COUNT().

Syntax :

select columnName1 , columnName2 , aggregateFuntion from <tableName> group by columnName ;

Example :

select age , count(*) from employee group by age ;

The above statement groups the rows by age column and counts the number of each age.

LIKE OPERATOR

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:

  • The percent sign (%) represents zero, one, or multiple characters
  • The underscore sign (_) represents one, single character

Syntax :

select columnName1 , columnName2 from <tableName> where columnName like pattern

Example 1:

select id , name from employee where name like 'r%' ;

The above statement select id and name from the employee table whose name start with r.

Example 2:

select id , name from employee where name like '[!RD]%' ;

The above statement select id and name from the employee table whose name not start with R and D.

union operator

The UNION operator selects rows from two or more tables. If rows of tables are the same, those rows are only included once in the result set

Syntax :

select columnName1 from <tableName1>
UNION
select columnName1 from <tableName2>

Employee table:

id name age
1 Rohit 19
2 Jimmy 20
3 Arjun 20
4 Hardik 20
5 Williamson 22

Student table :

id name age
10 Elvish 19
11 Nancy 20
12 Karan 21

Example :

select age from employee
UNION
select age from student ;

OUPUT:

age
19
20
21
22

The above SQL command returns the age column from the employee table and the student table, ignoring the duplicate rows.

SUBQUERY

It's possible to place a SQL query inside another query known as subquery.
In a subquery, the outer query's result is dependent on the result-set of the inner subquery. That's why subqueries are also called nested queries

Syntax :

select columnName1 , columnName2 from <tableName> where columnName expression operator
(select columnName from <tableName> where ...);

Example :

select * from employee
where age =(select min(age) from employee);

OUTPUT:

id name age
1 Rohit 19

The above statement :

  1. executes the subquery first; selects minimum age from the employee table.
  2. executes the outer query; selects rows where age is equal to the result of subquery.

HAVING CLAUSE

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
The HAVING clause in SQL is used if we need to filter the result set based on aggregate functions such as MIN() and MAX(), SUM() and AVG() and COUNT().

Syntax :

select columnName from <tableName>
where condition
group by columnName
having condition;

Example :

select count(id) , age from employee
group by age
having count(id>1);

OUTPUT:

count(id) age
1 19
2 20
1 21
1 22

The above statement :

  1. counts the number of rows by grouping them by country.
  2. returns the result set if their count is greater than 1.

EXISTS Statement

The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
EXISTS operator executes the outer SQL query if the subquery is not NULL
.

Syntax :

select columnName from <tableName>
where exists
( select columnName from <tableName> where condition );

Here I am creating a child experience table

id T_companySwitch Experience
1 2 1
2 4 1
3 1 1
4 3 2
5 1 2

Example :

select id , name from employee
where exists ( select id from experience where experience.id=employee.id );

OUTPUT:

id name
1 Rohit
2 Jimmy
3 Arjun
4 David
5 Williamson

JOINS

JOIN joins two tables based on a common column, and selects records that have matching values in these columns.

DIFFERENT TYPES OF JOINS :

  1. (INNER) JOIN : Returns records that have matching values in both tables.
  2. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table.
  3. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
  4. FULL (OUTER) JOIN : Returns all records when there is a match in either left or right table.

INNER JOIN Syntax :

select columnName from <table1>
inner join <table2>
on table1.columnName=table2.columnName;

Example :

select employee.id , employee.name , experience.experience from employee
inner join experience
on employee.id=experience.id;

The above command returns those rows where there is a match between column values in both join conditions.

OUTPUT:

id name experience
1 Rohit 1
2 Jimmy 1
3 Arjun 1
4 David 2
5 Williamson 2

Left join

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

Syntax :

select columnName from <table1>
left join <table2>
on table1.columnName=table2.columnName;

Example :

select employee.id , employee.name , experience.experience from employee
left join experience
on employee.id=experience.id;

right join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

Syntax :

select columnName from <table1>
right join <table2>
on table1.columnName=table2.columnName;

Example :

select employee.id , employee.name , experience.experience from employee
right join experience
on employee.id=experience.id;

FULL outer join

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
Tip: FULL OUTER JOIN and FULL JOIN are the same.

Syntax :

select columnName from <table1>
right join <table2>
on table1.columnName=table2.columnName
where condition;

Example :

select employee.id , employee.name , experience.experience from employee
right join experience
on employee.id=experience.id
order by employee.name;

Constraints

In a database table, we can add rules to a column known as constraints. These rules control the data that can be stored in a column.

For example, if a column has NOT NULL constraint, it means the column cannot store NULL values.

Constraint Description
NOT NULL values cannot be null
UNIQUE values cannot match any older value
PRIMARY KEY used to uniquely identify a row
FOREIGN KEY references a row in another table
CHECK validates condition for new value
DEFAULT set default value if not passed
CREATE INDEX used to speedup the read process

NOT NULL

The NOT NULL constraint in a column means that the column cannot store NULL values.

Example

create table employee(
id int not null ,
name text not null ,
age int );

Here, the id and the name columns of the employee table won't allow NULL values.

UNIQUE

The UNIQUE constraint in a column means that the column must have unique value.

Example

create table employee(
id int not null unique ,
name text not null ,
age int );

Here, the value of the id column must be unique, as well as it cannot store NULL values.

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

Example

create table employee(
id int primary key ,
name text not null ,
age int );

Here, the value of the id column is a unique identifier for a row. Similarly, it cannot store NULL value and must be UNIQUE.

FOREIGN KEY

The FOREIGN KEY (REFERENCES in some databases) constraint in a column is used to reference a record that exists in another table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

Example

create table experience(
id int primary key ,
T_companySwitch int ,
experience int
foreign key (id) references employee (id));

Here the value of id in the experience table must be a value from the id column of the employee table.

CHECK

The CHECK constraint checks the condition before allowing values in a table.

Example

create table experience(
id int ,
T_companySwitch int check (T_companySwitch <=4);< /p>

Here, the value of the T_companySwitch column must be less than or equal to 4. If not, the SQL statement results in an error.

DEFAULT

The DEFAULT constraint is used to set the default value if we try to store NULL in a column.

Example

create table experience(
id int ,
T_companySwitch int ,
experience int default 1;

Here, the default value of the experience column is 1. If we try to store the NULL value in the experience column, its value will be 1.

CREATE INDEX

If a column has CREATE INDEX constraint, it's faster to retrieve data if we use that column for data retrieval.

Example

create table experience(
id int primary key ,
T_companySwitch int ,
experience int );

create index exIndex
on experience (id);

Here, the SQL command creates an index named exIndex on the experience table using id column.

Note: We cannot see the speed difference with less records in a table. However, we can easily notice the speed difference between using indexes and not using indexes

Views

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.

Creating views

We can create View using CREATE VIEW statement. A View can be created from a single table or multiple tables

Syntax

create view as <viewName>
select column1 , column2 , ... from <tableName>
where condition;

Example

create view as below4
select id , name from employee
where id<4;< /p>

In this example we will create a View named below4 from the table employee.
To see the data in the View, we can query the view in the same manner as we query a table eg. select * from below4

Deleting views

We have learned about creating a View, but what if a created View is not needed any more? Obviously we will want to delete it. SQL allows us to delete an existing View. We can delete or drop a View using the DROP statement.

Syntax

drop view <viewName>;

Example

drop view below4;

Updating views

There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met, then we will not be allowed to update the view.

  1. The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.
  2. The SELECT statement should not have the DISTINCT keyword
  3. The View should have all NOT NULL values.
  4. The view should not be created using nested queries or complex queries.
  5. The view should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view.

Grant and revoke

Data Controlling Language (DCL) helps users to retrieve and modify the data stored in the database with some specified queries. Grant and Revoke belong to these types of commands of the Data controlling Language

Grant

SQL Grant command is specifically used to provide privileges to database objects for a user. This command also allows users to grant permissions to other users too.

Syntax

grant <privilageName> on <objectName>
to {userName | public | roleName};

Parameters of Grant Command

  • privilegeName: It represents the permission that is to be granted.
  • objectName: It represents the name of the database object, i.e., view, table, index, etc.
  • userName: It represents the user to which permission is to be provided.
  • public: It represents that all database users are given permissions.
  • role_name: It represents that users with a particular role are given these permissions.

To grant SELECT permission to a single user

grant select on employee
to manager;

ExampleTo grant SELECT permission to public

grant select on employee
to public;

REVOKE Command

The REVOKE command in SQL is used to revoke or withdraw permissions that were previously granted to an account on a database object. Therefore, we can think of REVOKE as a tool to limit a role's or user's ability to perform SELECT, INSERT, DELETE, UPDATE and CREATE statements on database objects., as well as to set constraints like foreign keys and update data records, among other operations.

The REVOKE command is the opposite of the command GRANT, which allows a user account or role access to the database object.

Syntax

revoke <privilageName> on <objectName>
from Role {cascade | restrict};

PARAMETERS OF REVOKE COMMAND

  • PRIVILEGES: We provide the privilege(s) that need to be revoked for a user account in this parameter. The privileges include SELECT , INSERT, DELETE, UPDATE and CREATE commands.
  • DATABASE OBJECTS: Database objects are database entities that could range from specific tables to all the tables in a schema.
  • ROLE | USER: The USER or the ROLE can be used to specify the user account from which the permissions have to be revoked.
  • CASCADE | RESTRICT: We can show that the revocation of the aforementioned PRIVILEGES can also remove the other privileges which derive their powers from the revoked privilege using CASCADE. In the absence of it, we can limit/ RESTRICT revocation to just the designated privilege.

Example : REVOKE UPDATE Privilege

revoke update on employee
from manager;

The above statement revoke an updated privilege granted to a user manager on the data object employee.

Example : REVOKE SELECT Privilege

revoke select on employee
from manager;

The above statement revoke an select privilege granted to a user manager on the data object employee.

Example : REVOKE INSERT Privilege

revoke insert on employee
from manager;

The above statement revoke an insert privilege granted to a user manager on the data object employee.

Example : REVOKE DELETE Privilege

revoke delete on employee
from manager;

The above statement revoke an delete privilege granted to a user manager on the data object employee.

Example : REVOKE TRUNCATE Privilege

revoke truncate on employee
from manager;

The above statement revoke an truncate privilege granted to a user manager on the data object employee.

Character functions

Character functions accept character inputs and can return either characters or number values as output.SQL provides a rich set of character functions that allow you to get information about strings and modify the contents of those strings in multiple ways.

Character functions are of the following two types:

  1. Case-Manipulative Functions (LOWER, UPPER and INITCAP)
  2. Character-Manipulative Functions (CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE)

Case-Manipulative Functions

1. Lower

This function converts alpha character values to lowercase. LOWER will actually return a fixed-length string if the incoming string is fixed-length. LOWER will not change any characters in the string that are not letters, since case is irrelevant for numbers and special characters, such as the dollar sign ( $ ) or modulus ( % ).

Syntax

lower (column | expression) ;

Example

select lower("HELLO");

OUTPUT:

lower("HELLO")
lower

2. Upper

This function converts alpha character values to uppercase. .

Syntax

upper (column | expression) ;

Example

select upper("hello");

OUTPUT:

upper("hello")
HELLO

3. INITCAP

This function converts alpha character values to uppercase for the first letter of each word and all others in lowercase. The words in the string is must be separated by either # or _ or space. .

Syntax

initcap (column | expression) ;

Example

select initcap("hello developer");

OUTPUT:

initcap("hello dev")
Hello Dev

Character-Manipulative Functions

1. Concat

This function always appends ( concatenates ) string2 to the end of string1. If either of the string is NULL, CONCAT function returns the non-NULL argument. If both strings are NULL, CONCAT returns NULL.

Syntax

concat (string1 , string2) ;

Example

select concat("hello", " developer");

OUTPUT:

concat("hello", " developer")
hello developer

2. Length

This function returns the length of the input string. If the input string is NULL, then LENGTH function returns NULL and not Zero.

Syntax

length (column | expression) ;

Example

select length("hello developer");

OUTPUT:

length("hello developer")
15

3. Substr

This function returns a portion of a string from a given start point to an end point. If a substring length is not given, then SUBSTR returns all the characters till the end of string.

Syntax

substr (string , start-index , length_of_extracted_string) ;

Example

select substr("js developer",3,8);

OUTPUT:

substr("js developer",3,8)
develop

4. INSTR

This function returns numeric position of a character or a string in a given string. Optionally, you can provide a position m to start searching, and the occurrence n of string. Also, if the starting position is not given, then it starts search from index 1, by default. If after searching in the string, no match is found then, INSTR function returns 0.

Syntax

instr (column | expression , string , m , n) ;

Example

select instr('Google apps are great applications','app',1,2);

OUTPUT:

instr('Google apps are great applications','app',1,2)
23

5. Trim

This function trims the string input from the start or end (or both). If no string or char is specified to be trimmed from the string and there exists some extra space at start or end of the string, then those extra spaces are trimmed off.

Syntax

trim (trailing from trim_source) ;

Example

select trim('H' from 'Hello');

OUTPUT:

trim('H' from 'Hello')
ello

6. Replace

This function searches for a character string and, if found, replaces it with a given replacement string at all the occurrences of the string.

Syntax

replace (string , search_string , replacement_string) ;

Example

select replace("js developer", "js" , "JavaScript");

OUTPUT:

replace("js developer", "js" , "JavaScript")
JavaScript developer

date functions

In SQL, dates are complicated for newbies, since while working with a database, the format of the data in the table must be matched with the input data to insert. In various scenarios instead of date, datetime (time is also involved with date) is used.

For storing a date or a date and time value in a database,MySQL offers the following data types:

DATE format YYYY-MM-DD
DATETIME format: YYYY-MM-DD HH:MI: SS
TIMESTAMP format: YYYY-MM-DD HH:MI: SS
YEAR format YYYY or YY

Some popular date functions are :

NOW()

Returns the current date and time.

Example

select now();

CURDATE()

Returns the current date .

Example

select curdate();

CURTIME()

Returns the current time.

Example

select curtime();

DATE()

Extracts the date part of a date or date/time expression.

Example

select date('2022-04-26 16:44:15.581');

Output

date('2022-04-26 16:44:15.581')
2022-04-26

EXTRACT()

Returns a single part of a date/time.

Syntax

extract(unit , from date);

Example

select extract(day from '2022-04-26 16:44:15.581');

Output

extract(day from '2022-04-26 16:44:15.581')
26

DATE_ADD()

Adds a specified time interval to a date.

Syntax

date_add(date , interval expr type);

Example

select date_add('2020-04-20' , interval 1 year);

Output

date_add('2020-04-20' , interval 1 year)
2021

Group Functions

Group functions are mathematical functions to operate on sets of rows to give one result per set. The types of group functions (also called aggregate functions) are: AVG, that calculates the average of the specified columns in a set of rows, COUNT, calculating the number of rows in a set.

Function Description
sum Returns the total sum
min Returns the lowest value
max Returns the highest value
avg Returns the average value
count(*) Returns the number of records in a table
count(column) Returns the number of values (NULL values will not be counted) of the specified column
COUNT (DISTINCT column) Returns the number of distinct values

Syntax

select columnName1 , group_Funtion from <tableName>
where condition group by columnName ;

Bowler table

id name salary city
101 Bumrah 10000 Mumbai
102 Bhuvaneshwar 80000 Mumbai
103 Null 5000 Indore
104 Bolt 70000 Goa
105 Hazelwood 5000 Goa

Example

select city , sum(salary) from bowler group by city ;

Output

city sum(salary)
Mumbai 90000
Indore 5000
Goa 75000

The above statement calculate the sum of salary from the bowler table group by city.