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
the standard language for Relation Database System. All relational database
management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres
and SQL Server use SQL as standard database language.
Also,
they are using different dialects, such as:
·
MS SQL Server using T-SQL,
·
Oracle using PL/SQL,
·
MS Access version of SQL is called JET SQL (native format) etc.
Why SQL?
·
Allows users to access data in relational database management
systems.
·
Allows users to describe the data.
·
Allows users to define the data in database and manipulate that
data.
·
Allows to embed within other languages using SQL modules,
libraries & pre-compilers.
·
Allows users to create and drop databases and tables.
·
Allows users to create view, stored procedure, functions in a database.
·
Allows users to set permissions on tables, procedures, and views
Data Types
SQL data type is
an attribute that specifies type of data of any object. Each column, variable
and expression has related data type in SQL.
Exact Numeric Data Types
|
DATA
TYPE
|
FROM
|
TO
|
|
bigint
|
-9,223,372,036,854,775,808
|
9,223,372,036,854,775,807
|
|
int
|
-2,147,483,648
|
2,147,483,647
|
|
smallint
|
-32,768
|
32,767
|
|
tinyint
|
0
|
255
|
|
bit
|
0
|
1
|
|
decimal
|
-10^38
+1
|
10^38
-1
|
|
numeric
|
-10^38
+1
|
10^38
-1
|
|
money
|
-922,337,203,685,477.5808
|
+922,337,203,685,477.5807
|
|
smallmoney
|
-214,748.3648
|
+214,748.3647
|
Approximate Numeric Data Types
|
DATA
TYPE
|
FROM
|
TO
|
|
float
|
-1.79E
+ 308
|
1.79E
+ 308
|
|
real
|
-3.40E
+ 38
|
3.40E
+ 38
|
Date and Time Data Types
|
DATA
TYPE
|
FROM
|
TO
|
|
datetime
|
Jan
1, 1753
|
Dec
31, 9999
|
|
smalldatetime
|
Jan
1, 1900
|
Jun
6, 2079
|
|
date
|
Stores
a date like June 30, 1991
|
|
|
time
|
Stores
a time of day like 12:30 P.M.
|
|
Character Strings Data Types
|
DATA
TYPE
|
Description
|
|
char
|
Maximum
length of 8,000 characters.( Fixed length non-Unicode characters)
|
|
varchar
|
Maximum
of 8,000 characters.(Variable-length non-Unicode data).
|
|
varchar(max)
|
Maximum
length of 231characters, Variable-length non-Unicode data (SQL Server 2005
only).
|
|
text
|
Variable-length
non-Unicode data with a maximum length of 2,147,483,647 characters.
|
Unicode Character Strings Data Types
|
DATA
TYPE
|
Description
|
|
nchar
|
Maximum
length of 4,000 characters.( Fixed length Unicode)
|
|
nvarchar
|
Maximum
length of 4,000 characters.(Variable length Unicode)
|
|
nvarchar(max)
|
Maximum
length of 231characters (SQL Server 2005 only).( Variable length Unicode)
|
|
ntext
|
Maximum
length of 1,073,741,823 characters. ( Variable length Unicode )
|
Binary Data Types
|
DATA
TYPE
|
Description
|
|
binary
|
Maximum
length of 8,000 bytes(Fixed-length binary data )
|
|
varbinary
|
Maximum
length of 8,000 bytes.(Variable length binary data)
|
|
varbinary(max)
|
Maximum
length of 231 bytes (SQL Server 2005 only). ( Variable length Binary data)
|
|
image
|
Maximum
length of 2,147,483,647 bytes. ( Variable length Binary Data)
|
Misc Data Types
|
DATA
TYPE
|
Description
|
|
sql_variant
|
Stores
values of various SQL Server-supported data types, except text, ntext, and
timestamp.
|
|
timestamp
|
Stores
a database-wide unique number that gets updated every time a row gets updated
|
|
uniqueidentifier
|
Stores
a globally unique identifier (GUID)
|
|
xml
|
Stores
XML data. You can store xml instances in a column or a variable (SQL Server
2005 only).
|
|
cursor
|
Reference
to a cursor object
|
|
table
|
Stores
a result set for later processing
|
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
SQL Arithmetic
Operators:
|
Operator
|
Description
|
|
+
|
Addition
- Adds values on either side of the operator
|
|
-
|
Subtraction
- Subtracts right hand operand from left hand operand
|
|
*
|
Multiplication
- Multiplies values on either side of the operator
|
|
/
|
Division
- Divides left hand operand by right hand operand
|
|
%
|
Modulus
- Divides left hand operand by right hand operand and returns remainder
|
SQL Comparison
Operators:
|
Operator
|
Description
|
Example
|
|
=
|
Checks
if the values of two operands are equal or not, if yes then condition becomes
true.
|
(a =
b) is not true.
|
|
!=
|
Checks
if the values of two operands are equal or not, if values are not equal then
condition becomes true.
|
(a !=
b) is true.
|
|
<>
|
Checks
if the values of two operands are equal or not, if values are not equal then
condition becomes true.
|
(a
<> b) is true.
|
|
>
|
Checks
if the value of left operand is greater than the value of right operand, if
yes then condition becomes true.
|
(a
> b) is not true.
|
|
<
|
Checks
if the value of left operand is less than the value of right operand, if yes
then condition becomes true.
|
(a
< b) is true.
|
|
>=
|
Checks
if the value of left operand is greater than or equal to the value of right
operand, if yes then condition becomes true.
|
(a
>= b) is not true.
|
|
<=
|
Checks
if the value of left operand is less than or equal to the value of right
operand, if yes then condition becomes true.
|
(a
<= b) is true.
|
|
!<
|
Checks
if the value of left operand is not less than the value of right operand, if
yes then condition becomes true.
|
(a
!< b) is false.
|
|
!>
|
Checks
if the value of left operand is not greater than the value of right operand,
if yes then condition becomes true.
|
(a
!> b) is true.
|
SQL Logical Operators:
|
Operator
|
Description
|
|
ALL
|
The
ALL operator is used to compare a value to all values in another value set.
|
|
AND
|
The
AND operator allows the existence of multiple conditions in an SQL
statement's WHERE clause.
|
|
ANY
|
The ANY
operator is used to compare a value to any applicable value in the list
according to the condition.
|
|
BETWEEN
|
The
BETWEEN operator is used to search for values that are within a set of
values, given the minimum value and the maximum value.
|
|
EXISTS
|
The EXISTS
operator is used to search for the presence of a row in a specified table
that meets certain criteria.
|
|
IN
|
The
IN operator is used to compare a value to a list of literal values that have
been specified.
|
|
LIKE
|
The
LIKE operator is used to compare a value to similar values using wildcard
operators.
|
|
NOT
|
The
NOT operator reverses the meaning of the logical operator with which it is
used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This
is a negate operator.
|
|
OR
|
The
OR operator is used to combine multiple conditions in an SQL statement's
WHERE clause.
|
|
IS
NULL
|
The
NULL operator is used to compare a value with a NULL value.
|
|
UNIQUE
|
The
UNIQUE operator searches every row of a specified table for uniqueness (no
duplicates).
|
Sql Expressions
An
expression is a combination of one or more values, operators, and SQL functions
that evaluate to a value.
SQL
EXPRESSIONs are like formulas and they are written in query language. You can
also use them to query the database for specific set of data.
Syntax:
Consider
the basic syntax of the SELECT statement as follows:
SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];
There are
different types of SQL expressions, which are mentioned below:
SQL - Boolean Expressions:
SQL
Boolean Expressions fetch the data on the basis of matching single value.
Following is the syntax:
SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;
Consider
the CUSTOMERS table having the following records:
SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh
|
32 | Ahmedabad |
2000.00 |
| 2 | Khilan
|
25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali |
25 | Mumbai
|
6500.00 |
| 5 | Hardik
|
27 | Bhopal
|
8500.00 |
| 6 | Komal
|
22 | MP | 4500.00 |
| 7 | Muffy
|
24 | Indore
| 10000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)
Here is
simple example showing usage of SQL Boolean Expressions:
SQL> SELECT * FROM CUSTOMERS WHERE SALARY = 10000;
+----+-------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+-------+-----+---------+----------+
| 7 | Muffy |
24 | Indore
| 10000.00 |
+----+-------+-----+---------+----------+
1 row in set (0.00 sec)
SQL - Numeric Expression:
This
expression is used to perform any mathematical operation in any query.
Following is the syntax:
SELECT numerical_expression as
OPERATION_NAME
[FROM table_name
WHERE CONDITION] ;
Here
numerical_expression is used for mathematical expression or any formula.
Following is a simple examples showing usage of SQL Numeric Expressions:
SQL> SELECT (15 + 6) AS ADDITION
+----------+
| ADDITION |
+----------+
| 21 |
+----------+
1 row in set (0.00 sec)
There are
several built-in functions like avg(), sum(), count(), etc., to perform what is
known as aggregate data calculations against a table or a specific table
column.
SQL> SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;
+---------+
| RECORDS |
+---------+
| 7 |
+---------+
1 row in set (0.00 sec)
SQL - Date Expressions:
Date
Expressions return current system date and time values:
SQL> SELECT CURRENT_TIMESTAMP;
+---------------------+
| Current_Timestamp |
+---------------------+
| 2009-11-12 06:40:23 |
+---------------------+
1 row in set (0.00 sec)
Another
date expression is as follows:
SQL> SELECT GETDATE();;
+-------------------------+
| GETDATE |
+-------------------------+
| 2009-10-22 12:07:18.140 |
+-------------------------+
1 row in set (0.00 sec)
No comments:
Post a Comment