SQL Introduction

SQL Introduction

What is SQL? :

SQL is ‘structured Query Language’ which is a computer language for storing, manipulating and retrieving data stored in relational database. SQL is a language of database, it includes database creation, deletion, fetching rows and modifying rows etc.

SQL is an ANSI (American National Standards Institute) standard but there are many different versions of the SQL language. SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server uses SQL as standard database language.

Purpose of SQL:

SQL is the most widely used commercial relational database language. It has various purposes like

•    It allows users to access data in relational database management systems.

•    It allows users to describe the data.

•    It allows users to define the data in database and manipulate that data.

•    It allows embedding within other languages using SQL modules, libraries & pre-compilers.

•    It allows users to create and drop databases and tables.

•    It allows users to create view, stored procedure, functions in a database.

•    It allows users to set permissions on tables, procedures, and views.

History of SQL:

SQL was originally developed at IBM in the SEQUEL-XRM and System-R projects (1974-1977). Almost immediately, other vendors introduced DBMS products based on SQL, and it is now a de facto standard. SQL continues to evolve in response to changing needs in the database area.

In 1979 Oracle introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language. Here is the year wise development history:

•    1970 E.F. Codd publishes Definition of Relational Model

•    1975 Initial version of SQL Implemented (D. Chamberlin)

•    IBM experimental version: System R (1977) w/revised SQL

•    IBM commercial versions: SQL/DS and DB2 (early 1980s)

•    Oracle introduces commercial version before IBM's SQL/DS

•    INGRES 1981 & 85

•    Share Base 1982 & 86

•    Data General (1984)

•    Sybase (1986)

•    By 1992 over 100 SQL products

The standard revisions of SQL are

•    SEQUEL/Original SQL - 1974

•    SQL/86 - Ratification and acceptance of a formal SQL standard by ANSI (American National Standards Institute) and ISO (International Standards Organization).

•    SQL/92 - Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.

•    SQL/99 - Added regular expression matching, recursive queries (e.g. transitive closure), triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features (e.g. structured types).

•    SQL/2003 - Introduced XML-related features (SQL/XML), Window functions, Auto generation.

•    SQL/2006 - Lots of XML Support for XQuery, an XML-SQL interface standard.

•    SQL/2008 - Adds INSTEAD OF triggers, TRUNCATE statement.

Syntax of SQL:

SQL is followed by unique set of rules and guidelines called Syntax. All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and the entire statements end with a semicolon (;).
Important point to be noted is that SQL is case insensitive which means SELECT and select have same meaning in SQL statements but MySQL make difference in table names.
The basic form of an SQL query is as follows:
SELECT [DISTINCT] select-list
FROM from-list
WHERE qualification

Every query must have a SELECT clause, which specifies columns to be retained in the result, and a FROM clause, which specifies a cross-product of tables. The optional WHERE clause specifies selection conditions on the tables mentioned in the FROM clause.

•    The from-list in FROM clause is a list of table names. A table name can be followed by a range variable; a range variable is particularly useful when the same table name more than once in the from-list.

•    The select-list is a list of column names of tables named in the from-list. Column names can be prefixed by a range variable.

•    The qualification in the WHERE clause is a Boolean combination i.e., an expression using the logical connectives AND, OR, and NOT of condition of the form expression op expression, where op is one of the comparison operators. An expression is a column name, a constant or an arithmetic or string expression.

SQL SELECT Statement:

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name

[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

•    table-name is the name of the table from which the information is retrieved.
•    column_list includes one or more columns from which data is retrieved.
•    The code within the brackets is optional.

For example, consider the table student_details. To select the first name of all the students the query would be like:
SELECT first_name FROM student_details;
You can also retrieve data from more than one column. For example, to select first name and last name of all the students.
SELECT first_name, last_name FROM student_details;
You can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement. We will discuss these commands in coming chapters.
In a SQL SELECT statement only SELECT and FROM statements are mandatory. Other clauses like WHERE, ORDER BY, GROUP BY, HAVING are optional.

SQL WHERE Clause:
The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE UPDATE statements.
Syntax of SQL WHERE Clause:
WHERE {column or expression} comparison-operator value
Syntax for a WHERE clause with Select statement is:
SELECT column_list FROM table-name
WHERE condition;

•    column or expression - Is the column of a table or a expression
•    comparison-operator - operators like = < > etc.
•    value - Any user value or a column name for comparison.

Subsets of SQL:

SQL has three major components: the Data Manipulation Language (DML), the Data Definition Language (DDL), and the Data Control Language (DCL).

The Data Manipulation Language (DML): This subset of SQL allows users to pose queries and to insert, delete and modify rows. It contains the subset of SQL commands used most frequently – those that simply manipulate the contents of a database in some form. The four most common DML commands are used to retrieve information from a database (the SELECT) command, add new information to a database (the INSERT command), modify information currently stored in a database (the UPDATE command) and remove information from a database (the DELETE command).

The Data Definition Language (DDL): This subset of SQL supports the creation, deletion, and modification of definitions for tables and views. It contains commands that are less frequently used. DDL commands modify the actual structure of a database, rather than the database’s contents. Examples of commonly used DDL commands include those used to generate a new database table (CREATE TABLE), modify the structure of a database table (ALTER TABLE), and delete a database table (DROP TABLE).

The Data Control Language (DCL): The Data Control Language (DCL) is used to manage user access to databases. It consists of two commands the GRANT command, used to add database permissions for a user, and the REVOKE command, used to take away existing permissions. These two commands form the core of the relational database security model.

SQL Operators:

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

•    Arithmetic operators

•    Comparison operators

•    Logical operators

•    Operators used to negate conditions

Arithmetic operators:


Arithmetic operators can perform arithmetical operations on numeric operands involved. Arithmetic operators are addition (+), subtraction (-), multiplication (*) and division (/). The + and - operators can also be used in date arithmetic.

Syntax:

 SELECT <Expression> [arithmetic operator] <expression>...
 FROM [table_name]
 WHERE [expression];

Parameters:

Expression: Expression made up of a single constant, variable, scalar function or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

arithmetic operator: Plus (+), minus (-), multiply (*), and divide (/).
table_name: Name of the table.

Comparison operators:


A comparison (or relational) operator is a mathematical symbol which is used to compare between two values.

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).
The following table describes different types of comparison operators
                             Operator                                Description
                                 =                                 Equal to
                                 >                                 Greater than
                                 <                                 Less than
                                 >=                                 Greater than equal to
                                 <=                                 Less than equal to
                                 < >                                 Not equal to

Syntax:

SELECT [column_name | * | expression] <comparison operator>
[column_name | * | expression]
FROM <table_name>
WHERE <expression> [comparison operator] <expression>;
Parameters:
column_name: Name of the column of a table.
*: Indicates all the columns of a table.
expression: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name: Name of the table.
comparison operator: Equal to (=), not equal to (<>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=).

Logical operators:


There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.
                               Operator                                   Description
                                AND    Logical AND compares between two Booleans as expression and return true when both expressions are true
                                OR    Logical OR compares between two Booleans as expression and return true when one of the expression is true
                               NOT    Not takes a single Boolean as an argument and changes its value from false to true or from true to false.

Syntax:

SELECT [column_name | * | expression] [boolean operator]
[column_name | * | expression .....]
FROM <table_name>
WHERE <expressions> [boolean operator |
arithmetic operator | ...] <expressions>;
Parameters:
column_name: Name of the column of a table.
*: All the columns of a table.
expression: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name: Name of the table.
boolean operator: AND, OR, NOT.
arithmetic operator: Plus (+), minus (-), multiply (*) and divide (/).

Negate operators:

The Negate Operators reverses the meaning of the logical operators with which it is used. Below are the few Examples which tell you about the Negate Operators.
•    NOT LIKE
•    NOT IN
•    NOT BETWEEN
•    IS NOT NULL
•    NOT EQUAL (<>) or (! = ).

NOT EQUAL: Not equal works exactly opposite to the equal operator. Operator (<>) and (! =) works same.
Example:
SQL>select * from emp
where
emp_job <> 'ACCOUNTANT';
Here all records are selected excluding those whose status is accountant in company.
NOT BETWEEN: This operator is used negate the between operator.
Example:
SQL>select * from emp
where
emp_salary NOT BETWEEN 2000 and 3000.
It will select all records excluding those whose salary is between 2000 and 3000, including 2000 and 3000.
IS NOT NULL: It will check whether the selected field is not empty (null).
Example:
SQL>select * from emp
where
email_id IS NOT NULL;
Selects records of those employees who have their email-id.
NOT LIKE: The NOT LIKE operator used wildcard operators to compare a value that is not similar. It supports both wildcard characters that are mentioned in like operators.
Example:
SQL> select * from emp
where
emp_name NOT LIKE 'A%';
Finds any name that does not start with A and continues for any length.
NOT IN: The NOT IN operator search the value that is not mentioned in a predetermined list, if any of the value matches than row will be displayed.
Example:
SQL>select * from emp
where
emp_job
NOT IN ('PROGRAMMER','OPERATOR');
It will display all details of employee excluding those whose status in company is either programmer or operator.

2 comments:

I want to have complete guide and full tutorial in word or pdf, ASAP.

I also want complete guide , study material in any word or pdf format . ASAP
via mail on
sohanp17@gmail.com

Post a Comment