Free Online Courses Hub

SQL Statements | SQL Queries

Following is the complete list of SQL Statements.

There is a list of SQL Queries with examples like create a statement, update statement, delete statement, etc.

Create Statement in SQL:

Create statement is used to create a table, index, or View

Statement to Create Table:

Create tabel TabelName(AttributeName dataType(size) PRIMARY KEY, AttributeName DataType(Size)…);

Alter Statement:

Alter statement in SQL is used to change, modify or delete a column of an existing table.

For Example:

Adding Column Through Alter Statement:

alter table TableName add columnName datatype(size);
alter table student add rollNo int(255);

Modify Column DataType through Alter Statement:

Alter Table TableName modify Column ColumnName datatype(size);

For Example:

To change kk Column datatype from int to varchar in Student table then we can write

alter table Student modify kk varchar(255);

Drop Column through Alter Statement:

Syntax:

alter table TableName drop column columnName;

Drop Statement:

Drop statement in SQL is used to Delete the complete Table including data.

Syntax:

Drop table TableName;

For Example:
To Drop the student table we can write a query

Drop table Student;

Rename Statement:

Rename statement is used to rename a table in SQL

Syntax:

Rename table OldTableName to new TableName;

rename table students to faculty;

Table Name can also be changed through alter the query

Syntax:

alter table OldTableName rename NewTableName;

For Example:

Alter table Students rename Faculty;

The above student table name will be changed to faculty.

Truncate Statement in SQL:

A truncate statement in SQL is used to Delete all the data from a table but the table is not deleted.

Syntax:

Truncate table TableName;

For Example:

To Delete all the data from a student table then we can write statements like:

truncate table student

The above query will delete all the data from the student table.

Insert Statement:

Through insert statement we can insert data into the table

Syntax:

insert into tableName(ColumnName1,ColumnName2,…) values (value1, value2,..);

or

insert into TableName() values (value1, value2, …)

Inserting multiple values:

insert into TableName() values (value1, valu2,…) , (value3,value4,…) , (value4, value5,…)

Update Query in SQL:

Update Query in SQL is used to change the existing data in SQL.

Syntax:

update tableName set ColumnName1 = Value1, ColumnName2 = value2 where Condition;

For example:

To change the father’s name Aslam from Javad to Asad on the student table then we can write

update student set FatherName = "Asad" where fatherName = "Javad"

Delete Statement in SQL:

A delete statement in SQL is used to delete existing records or present data in a table.

Syntax:

Delete from tableName where Condition;

For Example:

To Delete the name of customers from customers table where location = Mexico

delete * from customers where location = "Maxico"

The above query will delete all the records from a customers table having the location of Mexico

Through the Where condition, we can delete data based on a given condition.

Delete All Data from a table Through delete Statement:

Syntax:

Delete from TableName;

For Example:

To delete all the data from the customer’s table we can write.

delete *  from customer

The above query will delete all the data from the customer table

Order by Clause:

The order by clause in SQL is used to sort the result in ascending order.
in order to order the result in ascending order, we use the word asc
while to sort the order in descending order we use desc at the end of order by.

Syntax:

To sort data in ascending order:

select columnName1, columnName2,.. from tableName orderBy asc;

To sort data in ascending order:

select columnName1, columnName2,.. from tableName orderBy desc;

For Example:

To Sort data present in an employee table based on their salary

select salary from employee order by asc;

The above SQL statement will sort data in ascending order.

order by is by default sort the data in ascending order

select * from customer order by country;

The above query will sort the data in ascending order based on the country names.

or

select * from customer order by id

The above query will sort the data in ascending order based on the id

SELECT * FROM student ORDER BY Country ASC, studentName DESC;

The above query will select all the data from the student table and sort the data in ascending order based on country
and sort the student’s name in descending order based on student Name.

Group by Statement in SQL:

The GROUP BY statement groups rows that have the same values into summary rows,
like “find the number of students in each school”.

The GROUP BY statement in SQL is often used with these functions:

COUNT(), MAX(), MIN(), SUM(), AVG() to group the results by one or more columns.

Syntax:

Select ColumnName from tableName where Condition Group by ColumnName;

SELECT count(studentId), address
FROM students
GROUP BY address
select count(studentID) from students;

Will count the number of students id

MAX()

select MAX(salary) from employee;

This will print the maximum salary;

MIN():

select MIN(salary) from employee;

This will print the minimum Salary from the employee table.

avg()

select avg(salary) from employee;

This will print the average salary from the employee table

Sum()

Select sum(salary) from employee;

This will print the sum of employee salary;

Having Clause:

Having clause is used with the aggregate functions

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
SELECT COUNT(CustomerID)
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

Upper()

Select upper(“how are you”)

Syntax:

upper(str)

The above function will convert the string to upper case

SELECT UPPER(studentName) AS UppercaseStudentName
FROM student;

The above statement will select student name from student Table and shows in upper case and
will show the title as uppercaseStudentName

lower(str)

select lower("HOW ARE YOU")

The above function will convert the string to lower case

Inticap Function

Inticap()

This function will convert the first letter of the string into Capital

For example

select inticap("how are you")

will convert the string into

How Are You

concate()

The concat function will concatenate two strings together

select concat("How are","You")

The Concat function will combine two strings together

substr()

The SUBSTR() function extracts a substring from an existing String (starting at any position).

Note: The SUBSTR() and MID() functions equals to the SUBSTRING() function.

Syntax:

SUBSTR(string, start, length)

select substr("How are you",4,9)

will extract are you from the how are you string

Instr()

The instr() function will search the string in a given string

Syntax:

SELECT INSTR("Courseshub.org", "org") AS MatchPosition;

The above statement will search org in the Courseshub.org string and return the position of that string

lpad()

This will add the given string to the left side of the string

select lpad("I am learning sql", 30, "How I am")

rpad()

This will add the given string to the right side of the given string

select rpad("I am learning sql",30,"How I am")

Syntax:

LPAD(string, length, lpad_string)

LTRIM()

This will remove spaces from the left of the string

SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString;

RTRIM()

This will remove spaces from the right of the string
SELECT LTRIM(‘SQL Tutorial ‘) AS LeftTrimmedString;

Truncate()

Truncate the number to the specified number of decimal places

SELECT TRUNCATE(135.375, 2);

select truncate(333.233,2)

This will truncate the number to 2 decimal places

truncate(number, decimal)

mod()

MOD( dividend, divider )

SELECT MOD(25,7)
FROM dual;

Will print the remainder of a division

coalesce()

This function returns the first non-null value in a list

SELECT COALESCE(NULL, NULL, NULL, 'courseshub.org', NULL, 'google.com');

We will add a complete list of SQL statements and will update this blog every day you will find a complete list of SQL statements in this blog. So do check out our blog more SQL Statements will be added soon.

Exit mobile version