SQL Keywords

SQL keywords are case insensitive. So the keyword SELECT and select will behave in the same way. Let’s look at some of the commonly used SQL keywords.

• AND : AND is an sql operator which is used for adding multiple condition. If the conditions are satisfied by a row. It will be part of the result set.

Syntax	SELECT column_name FROM table_name WHERE column1 = value1 AND column2 = value2;
Output	On execution of this command, if column1 is equal to value1 and column2 is equal to value2 then only the row will be qualified for result set

• AS : AS is used for aliasing a column or table.

Syntax	SELECT AVG(column_name) FROM table_name;
Output	Execution of this command will provide the average of column_name.

• BETWEEN : BETWEEN is an operator which is used for defining a set. The value for range can be numeric, text and date.

Syntax	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Output	Execution of this command will give a result set bounded by value1 and value2.

• COUNT : COUNT is a function which provides the count of the row for a column. The column should be a not null column.

Syntax	SELECT COUNT(column_name) FROM table_name;
Output	On execution of this command, the result will contain the count of the rows for column_name.

• CREATE TABLE : This command is used to create table in the database. It allows to specify the name of the table along with the name a datatype of the column.

Syntax	CREATE TABLE table_name (column1 datatype, column2 datatype);
Output	Execution of this command will result in creation of table with name as table_name along with column1 and column2 as the columns of the table.

• DELETE : DELETE is the command which is used for removing rows from the table.

Syntax	DELETE FROM table_name WHERE column_name = value;
Output	On execution of this command the rows that will be selected based on the condition in the WHERE clause will be deleted.

• GROUP BY : GROUP BY is a clause in SQL that is only used with aggregate functions. It is used along with the SELECT statement to arrange identical data into groups.

Syntax	SELECT COUNT(*) FROM table_name GROUP BY column_name;
Output	Execution of this order will result in grouping the result set based on the column_name.

• INNER JOIN : INNER JOIN will select records with matching values in two tables.

Syntax	SELECT column_name FROM table_1 INNER JOIN table_2 ON table_1.column_name = table_2.column_name;
Output	Above command will result in rows where both tables have matching values for column_name.

• INSERT : INSERT is used for adding a new row in the table.

Syntax	INSERT INTO table_name (column1, column2, column3) VALUES (value1, 'value2', value3);
Output	Execution of this command will result in addition of a new row with values corresponding to the columns.

• LIKE : LIKE is an operator which is used for specifying a pattern. This operator is used along with WHERE clause.

Syntax	SELECT column_name FROM table_name WHERE column_name LIKE pattern;
Output	Output will the rows that will satisfy the like pattern.

• LIMIT : LIMIT is a clause which allows the restricting the result set rows to maximum number specified by the limit clause.

Syntax	SELECT column_name FROM table_name LIMIT number;
Output	The resultset will be limited by the number that is provided as part of the limit clause.

• MAX : MAX is a function that will return the max value from the column specified.

Syntax	SELECT MAX(column_name) FROM table_name;
Output	The output will the maximum value specified in the column column_name.

• MIN : MIN is a function that will return the min value from the column specified.

Syntax	SELECT MIN(column_name) FROM table_name;
Output	The output will the minimum value specified in the column column_name.

• OR : OR is the operator that is used for selecting the rows based on satisfaction of either condition in WHERE clause.

Syntax	SELECT column_name FROM table_name WHERE column_name = value1 OR column_name = value2;
Output	Result set will contain rows where column_name value will be either value1 or value2.

• ORDER BY : ORDER BY is used for sorting of columns in ascending or descending order numerically or in alphabetical order.

Syntax	SELECT column_name FROM table_name ORDER BY column_name ASC;
Output	On execution of the command above we will get the result set in ascending order.

• LEFT JOIN, RIGHT JOIN : These will combine rows from different tables even if the join condition is not met. Every row in the left/right table is returned in the result set, and if the join condition is not met, then NULL values are filled in the columns from the left/right table.

Syntax	SELECT column_name(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;
Output	Execution of the command above will result in rows from the table_1 along with rows which satisfies the condition from table_2
Syntax	SELECT column_name(s) FROM table_1 RIGHT JOIN table_2 ON table_1.column_name = table_2.column_name;
Output	Execution of the command above will result in rows from the table_2 along with rows which satisfies the condition from table_1.

• ROUND : ROUND is a function that rounds of the number specified in the column based on the integer that is specified as part of the function.

Syntax	SELECT ROUND(column_name, integer) FROM table_name;
Output	The output of the command will result in rounding up the number based on the integer that is provided as part of the function.

• SELECT : SELECT is used to fetch the data from the data base.

Syntax	SELECT column_name FROM table_name
Output	On execution of this command the result set will contain rows for column column_name.

• SELECT DISTINCT : SELECT DISTINCT is used for retrieving distinct values from the column that is specified.

Syntax	SELECT DISTINCT column_name FROM table_name;
Output	On execution of the command above the result set will only contain the unique values from the column column_name.

• SUM : SUM is a function will provides the total value of a column which is numeric.

Syntax	SELECT SUM(column_name) FROM table_name;
Output	Execution of this command will result in the total of all the row that are part of the column column_name

• UPDATE : UPDATE is used for updating values of a row of a table.

Syntax	UPDATE table_name SET some_column = some_value WHERE some_column = some_value;
Output	Execution of this command will result in updating the row that will satisfy the condition in the where clause.

• WHERE : WHERE is used for specifying the condition that should be satisfied for selecting the row to be part of the result set.

Syntax	SELECT column_name FROM table_name WHERE column_name operator value;
Output	The output of this command will result in the rows that are satisfying the where clause.

SQL Datatypes & SQL Operator

SQL Datatypes

SQL Data Type is an attribute that specifies the type of data of any object. Every column is required to have a name and data type in the database table. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement. SQL offers six categories of data types for your use which are listed below

1.   Binary Datatypes

There are Three types of binary Datatypes which are given below:

binary	It has a maximum length of 8000 bytes. It contains fixed-length binary data.
varbinary	It has a maximum length of 8000 bytes. It contains variable-length binary data..
image	It has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.

2.   Approximate Numeric Datatype

The subtypes are given below:

float	Range from -1.79E + 308 to 1.79E + 308. It is used to specify a floating-point value e.g. 6.2, 2.9 etc.
real	Range from -3.40e + 38 to 3.40e + 38. It specifies a single precision floating point number.

3.   Exact Numeric Datatype

The subtypes are given below:

int	It is used to specify an integer value.
smallint	It is used to specify small integer value.
bit	It has the number of bits to store.
decimal	It specifies a numeric value that can have a decimal number.
numeric	It is used to specify a numeric value.

4.   Character String Datatype

The subtypes are given below:

char	It has a maximum length of 8000 characters. It contains Fixed-length non-unicode characters.
varchar	It has a maximum length of 8000 characters. It contains variable-length non-unicode characters.
text	It has a maximum length of 2,147,483,647 characters. It contains variable-length non-unicode characters.

5.   Date and time Datatypes

The subtypes are given below:

date	It is used to store the year, month, and days value.
time	It is used to store the hour, minute, and second values.
timestamp	It stores the year, month, day, hour, minute, and the second value.

SQL Operators

SQL Data Type is an attribute that specifies the type of data of any object. Every column is required to have a name and data type in the database table. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement. SQL offers six categories of data types for your use which are listed below

1.   SQL Arithmetic Operators

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

+	It adds the value of both operands. i.e. a+b will give 30
-	It is used to subtract the right-hand operand from the left-hand operand. i.e. a-b will give 10
*	It is used to multiply the value of both operands. i.e. a*b will give 200
/	It is used to divide the left-hand operand by the right-hand operand. i.e. a/b will give 2
%	It is used to divide the left-hand operand by the right-hand operand and returns reminder. i.e a%b will give 0

2.   SQL Comparison Operators

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

=	It checks if two operands values are equal or not, if the values are queal then condition becomes true. Example: (a=b) is not true
!=	It checks if two operands values are equal or not, if values are not equal, then condition becomes true. Example: (a!=b) is true
<>	It checks if two operands values are equal or not, if values are not equal then condition becomes true. Example: (a<>b) is true
>	It checks if the left operand value is greater than right operand value, if yes then condition becomes true. Example: (a>b) is not true
<	It checks if the left operand value is less than right operand value, if yes then condition becomes true. Example: (a
>=	It checks if the left operand value is greater than or equal to the right operand value, if yes then condition becomes true. Example: (a>=b) is not true
<=	It checks if the left operand value is less than or equal to the right operand value, if yes then condition becomes true. Example: (a<=b) is true
!<	It checks if the left operand value is not less than the right operand value, if yes then condition becomes true. Example: (a!=b) is not true
!><	It checks if the left operand value is not greater than the right operand value, if yes then condition becomes true Example: (a!>b) is true

3.   SQL Logical Operators

There is the list of logical operator used in SQL:

ALL	It compares a value to all values in another value set.
AND	It allows the existence of multiple conditions in an SQL statement.
ANY	It compares the values in the list according to the condition.
BETWEEN	It is used to search for values that are within a set of values.
IN	It compares a value to that specified list value.
NOT	It reverses the meaning of any logical operator.
OR	It combines multiple conditions in SQL statements.
EXISTS	It is used to search for the presence of a row in a specified table.
LIKE	It compares a value to similar values using wildcard operator.

SQL Statements / Commands:

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL

1.   DDL - Data Definition Language.

This includes changes to the structure of the table like creation of table, altering table, deleting a table etc. All DDL commands are auto-committed that means it saves all the changes permanently in the database. Here are some commands that come under DDL:

• CREATE : It is used to create objects (tables, views) in the database

Syntax	CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example	CREATE TABLE EMPLOYEE ( ID NUMBER, Name VARCHAR2(20), Email VARCHAR2(100),DOB DATE);



• ALTER : It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

Syntax	"To add a new column in the table use" ALTER TABLE table_name ADD column_name COLUMN-definition; "To modify existing column in the table use" ALTER TABLE MODIFY(COLUMN DEFINITION....);
Example	ALTER TABLE EMPLOYEE ADD(ADDRESS VARCHAR2(20)); ALTER TABLE EMPLOYEE MODIFY (NAME VARCHAR2(100)):



• DROP : It is used to delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints).

Syntax	DROP TABLE TABLE_NAME;
Example	DROP TABLE EMPLOYEE;



• TRUNCATE : Remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark) .

Syntax	TRUNCATE TABLE table_name;
Example	TRUNCATE TABLE EMPLOYEE;

2.   DML - Data Manipulation Language.

DML commands are used for manipulating the data stored in the table and not the table itself. DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back:

• INSERT : The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax	INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .... valueN); INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
Example	INSERT INTO employee (id, name) VALUES (1, 'admin');



• UPDATE : This command is used to update or modify the value of a column in the table.

Syntax	UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]
Example	UPDATE employee SET name = 'admin' WHERE id = '1';



• DELETE : It is used to remove one or more row from a table.

Syntax	DELETE FROM table_name [WHERE condition];
Example	DELETE FROM employee WHERE id = 1;

3.   DCL - Data Control Language.

Data control language are the commands to grant and take back authority from any database user.

• GRANT : It is used to give user access privileges to a database.

Syntax	GRANT SELECT, UPDATE ON TABLE_NAME TO USER_NAME, ANOTHER_USER_NAME;
Example	GRANT SELECT, UPDATE ON EMPLOYEE TO HR, APPS;



• REVOKE : It is used to take back permissions from the user.

Syntax	REVOKE SELECT, UPDATE ON TABLE_NAME FROM USER1, USER2;
Example	REVOKE SELECT, UPDATE ON EMPLOYEE FROM HR, APPS;

4.   TCL - Transaction Control Language.

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.

• COMMIT : Commit command is used to save all the transactions to the database.

Syntax	COMMIT;
Example	DELETE FROM EMPLOYEE WHERE ID = 1; COMMIT;



• ROLLBACK : Rollback command is used to undo transactions that have not already been saved to the database.

Syntax	ROLLBACK;
Example	DELETE FROM EMPLOYEE WHERE ID = 1; ROLLBACK;



• SAVEPOINT : It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax

SAVEPOINT SAVEPOINT_NAME;

5.   DQL - Data Query Language.

Data query language is used to fetch data from tables based on conditions that we can easily apply.

• SELECT : This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

Syntax	SELECT expressions FROM TABLE(S)_NAME WHERE conditions;
Example	SELECT * FROM EMPLOYEE WHERE ID = 1;

SQL Introduction

Basics :

• SQL stands for Structured Query Language and it is generally referred to as SEQUEL.

• It is used for storing and managing data in relational database management system (RDMS).

• It enables a user to create, read, update and delete relational databases and tables.

• All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database language QL allows users to query the database in a number of ways, using English-like / human readable statements.

• SQL commands are instructions which are used to communicate with the database.

• It is also used to perform specific tasks, functions, and queries of data.

• SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.

History :

• SQL was developed by IBM in the 1970s.

• The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989.

• ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3.

• Each time, ANSI added new features and incorporated new commands and capabilities into the language.

Rules :

• Structure query language is not case sensitive.

• Generally, keywords of SQL are written in uppercase.

• Statements of SQL are dependent on text lines.

• We can use a single SQL statement on one or multiple text line.

• Using the SQL statements, you can perform most of the actions in a database.

• SQL depends on tuple relational calculus and relational algebra