SQL Commands and Functions
Summary
SQL (Structured Query Language) is a standardized language used to create, maintain, modify, and query relational databases. When a user wants to get some information from a database file, he can issue a SQL query.
Types of SQL commands OR Processing capabilities of SQL:
① Data Definition Language Commands (DDL Commands)
② Data Manipulation Language Command (DML Commands)
③ Transaction Control Language Command (TCL commands)
④ Data Control Language (DCL Commands)
DDL Commands
All the commands used to create, modify or delete physical structure of an object like a table. e.g., Create, Alter, Drop.
DML Commands
DML contains commands that can be used to manipulate the database objects and to query the databases for information retrieval.
e.g Select, Insert, Delete, Update etc.
TCL commands
TCL include commands to control the transactions in a database system. The commonly used commands in TCL are COMMIT, ROLLBACK etc.
Data Control Language (DCL)
This language is used for controlling the access to the data. Various commands like GRANT, REVOKE etc are available in DCL.
Data Types in SQL
Following are the most common data types of SQL.
1) Number
2) Char
3) Varchar
4) Date
5) Long
6) Raw/ Long Raw
Constraints in SQL
Constraints are the conditions that can be enforced on the attributes of a relation. Constraints are applicable to one field or group of fields. The constraints come in play whenever we try to insert, delete or update a record in a relation. They are used to ensure the integrity of a relation, hence named as integrity constraints.
Constraints can be:
① Column Constraint: Apply only to individual column e.g. NOT NULL constraint
② Table Constraint: Apply to a group of columns.
③ Unique Constraint: Primary Key Constraint, Foreign Key Constraint
④ Default Constraint
⑤ Check Constraint
Operators in SQL:
The following are the commonly used operators in SQL
1. Arithmetic Operators: +, -, *, /
Arithmetic operators are used to perform simple arithmetic operations.
2. Relational Operators: =, <, >, <=, >=, <>
Relational Operators are used when two values are to be compared and
3. Logical Operators: OR, AND, NOT
Logical operators are used to connect search conditions in the WHERE Clause in SQL.
4. Range check – between low and high
5. List check-in
6. Pattern check – like, not like ( % and _ ‘underscore’ is used)
DML Commands
DML Commands are as under:
SELECT - Used for making queries
INSERT - Used for adding new row or record into a table
UPDATE- used for modification in existing data in a table
DELETE – used for deletion of records.
INSERT Statement
To insert a new tuple into a table is to use the insert statement
insert into <table> [(<column i, . . . , column j>)] values (<value i, . . . , value j>);
SELECT Command
This command is used to give certain specified information from the database table. It can perform selection as well as projection.
Syntax: SELECT <column_name1>, [<comun_name2>, ...] FROM <table_name>
Selection:
This capability of SQL can return you the tuples form a relation with all the attributes.
e.g. SELECT name, class FROM student;
The above command displays only name and class attributes from student table.
Projection:
This is the capability of SQL to return only specific attributes in the relation. Use of where clause is required when specific tuples are to be fetched or manipulated.
e.g SELECT * FROM student;
The command will display all the tuples in the relation student
ORDER BY Clause
ORDER BY clause is used to display the result of a query in a specific order(sorted order).
The sorting can be done in ascending or in descending order. It should be kept in mind that the actual data in the database is not sorted but only the results of the query are displayed in sorted order.
e.g. SELECT name, city FROM student ORDER BY name;
The above query returns name and city columns of table student sorted by name in increasing/ascending order.
GROUP BY Clause
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY column1, column2, ... column_n;
aggregate_function can be a function such as SUM, COUNT, MAX, MIN, AVG etc.
e.g SELECT name, COUNT(*) as "Number of employees"
FROM student
WHERE marks>350
GROUP BY city;
HAVING Clause
The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
The syntax for the HAVING clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;
e.g SELECT SUM(marks) as "Total marks"
FROM student
GROUP BY department
HAVING SUM(sales) > 1000;
DELETE Command
To delete the record fro a table SQL provides a delete statement. The general syntax is:-
DELETE FROM <table_name> [WHERE <condition>];
e.g. DELETE FROM student WHERE city = ‘Delhi’;
This command deletes all those records whose city is Delhi.
e.g. DELETE FROM student;
It deletes all records from the student table.
UPDATE Command
To update the data stored in the database, UPDATE command is used.
e. g. UPDATE student SET marks = marks + 100;
Increase marks of all the students by 100.
ALTER TABLE Command
In SQL if we ever need to change the structure of the database then ALTER TABLE command is used. By using this command we can add a column in the existing table, delete a column from a table or modify columns in a table.
ALTER TABLE table_name
ADD column_name datatype;
e.g ALTER TABLE student ADD(Address varchar(30));
The above command adds a column Address to the table student.
ALTER TABLE table_name
DROP COLUMN column_name;
e.g ALTER TABLE Student
DROP COLUMN Address;
The column Address will be removed from the table student.
DROP TABLE Command
Sometimes you may need to drop a table which is not in use. DROP TABLE command is used to
Delete/drop a table permanently. It should be kept in mind that we can not drop a table if it contains
records. That is first all the rows of the table have to be deleted and only then the table can be
dropped. The general syntax of this command is:-
DROP TABLE <table_name>;
e.g DROP TABLE student;
This command will remove the table student
SQL Functions
SQL provides a large collection of inbuilt functions also called library functions that can be used directly in SQL statements.
1. Mathematical functions
2. String functions
3. Date & Time functions
1. Mathematical functions
Some of the commonly used mathematical functions are sum() avg(), count(), min(), max() etc.
e.g. SELECT sum(marks) FROM student;
displays the sum of all the marks in the table student.
e.g. SELECT min(Roll_no), max(marks) FROM student;
displays smallest Roll_no and highest marks in the table student.
2. String functions
These functions are used to deal with the string type values like
ASCII, LOWER, UPPER, LEN, LEFT, RIGHT, TRIM, LTRIM, RTRIM etc.
ASCII: Returns the ASCII code value of a character(the leftmost character of string).
Syntax: ASCII(character)
SELECT ASCII('a') returns 97
SELECT ASCII('A') returns 65
SELECT ASCII('1') returns 49
SELECT ASCII('ABC') returns 65
For Upper character 'A' to 'Z' ASCII value 65 to 90
For Lower character 'A' to 'Z' ASCII value 97 to 122
For digit '0' to '9' ASCII value 48 to 57
LOWER: Convert character strings data into lowercase.
Syntax: LOWER(string)
SELECT LOWER('STRING FUNCTION') returns string function
UPPER: Convert character strings data into Uppercase.
Syntax: UPPER(string)
SELECT UPPER('string function') returns STRING FUNCTION
LEN: Returns the length of the character string.
Syntax: LEN(string)
SELECT LEN('STRING FUNCTION') returns 15
REPLACE: Replaces all occurrences of the second string(string2) in the first string(string1) with a third string(string3).
Syntax: REPLACE('string1','string2','string3')
SELECT REPLACE('STRING FUNCTION','STRING','SQL') returns SQL Function
Returns NULL if any one of the arguments is NULL.
LEFT: Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.
Syntax: LEFT(string,integer)
SELECT LEFT('STRING FUNCTION', 6) returns STRING
RIGHT: Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.
Syntax: RIGHT(string,integer)
SELECT RIGHT('STRING FUNCTION', 8) returns FUNCTION
LTRIM: Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)
Syntax: LTRIM(string)
SELECT LTRIM(' STRING FUNCTION') returns STRING FUNCTION
RTRIM: Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)
Syntax: RTRIM( string )
SELECT RTRIM('STRING FUNCTION ') returns STRING FUNCTION
REVERSE: Returns reverse of an input string.
Syntax: REVERSE(string)
SELECT REVERSE('STRING FUNCTION') returns NOITCNUF GNIRTS
REPLICATE: Repeats an input string for a specified number of times.
Syntax: REPLICATE (string, integer)
SELECT REPLICATE('FUNCTION', 3) returns FUNCTIONFUNCTIONFUNCTION
SPACE: Returns a string of repeated spaces. The SPACE function is an equivalent of using
REPLICATE function to repeat spaces.
Syntax: SPACE ( integer) (If integer is negative, a null string is returned.)
SELECT ('STRING') + SPACE(1) + ('FUNCTION') returns STRING FUNCTION
SUBSTRING: Returns part of a given string.
SUBSTRING function retrieves a portion of the given string starting at the specified character(startindex) to the number of characters specified(length).
Syntax: SUBSTRING (string,startindex,length)
SELECT SUBSTRING('STRING FUNCTION', 1, 6) returns STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8) returns FUNCTION
