Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

SQL Tutorial i SQL TUTORIAL Simply Easy Learning by tutorialspoint
...
com
TUTORIALS POINT Simply Easy Learning ABOUT THE TUTORIAL SQLTutorial SQL is a database
computer language designed for the retrieval and management of data in relational database
...
This tutorial will give you quick start with SQL
...
Prerequisites Before you start doing practice with various types
of examples given in this reference, I'm making an assumption that you are already aware about
what is database, especially RDBMS and what is a computer programming language
...
com
...
com or this tutorial may not be redistributed or
reproduced in any way, shape, or form without the written permission of tutorialspoint
...
Failure
to do so is a violation of copyright laws
...
If you discover that the tutorialspoint
...
com TUTORIALS POINT Simply Easy Learning Table
of Content SQL Tutorial
...
2
Prerequisites
...
2 SQL Overview
...
15 Why
SQL?
...
16 SQL
Process:
...
17 DDL - Data Definition Language:

...
17 DCL - Data Control
Language:
...
17 SQL RDBMS
Concepts
...
19 What is record, or row?

...
19 What is NULL
value?
...
20 NOT NULL Constraint:

...
21 Example:

...
21 UNIQUE
Constraint:
...
21 DROP a UNIQUE
Constraint:
...
22 Create Primary
Key:
...
23 FOREIGN
Key:
...
24 DROP a FOREIGN KEY Constraint:

...
25 Example:


...
25 INDEX:

...
26 DROP a INDEX
Constraint:
...
26 Database
Normalization
...
29 SQL RDBMS
Databases
...
32
History:
...
33 MS SQL
Server
...
33
Features:
...
34
History:
...
35 MSACCESS
...
36 SQL
Syntax
...
37 SQL DISTINCT Clause:

...
37 SQL AND/OR
Clause:
...
38 SQL BETWEEN Clause:

...
38 SQL ORDER BY Clause:

...
38 SQL COUNT Clause:

...
38 SQL CREATE TABLE
Statement:
...
39 SQL CREATE INDEX Statement:

...
39 SQL DESC
Statement:
...
39 SQL ALTER TABLE
Statement:
...
39 SQL INSERT INTO Statement:

...
39 TUTORIALS POINT Simply Easy Learning SQL DELETE
Statement:
...
40 SQL DROP DATABASE Statement:

...
40 SQL COMMIT Statement:

...
40 SQL Data Types


...
41 Approximate Numeric Data Types:

...
42 Character Strings Data Types:

...
42 Binary Data
Types:
...
43 SQL
Operators
...
44 SQL Arithmetic Operators:

...
45 SQL Logical
Operators:
...
50 Syntax:

...
50 SQL - Numeric
Expression:
...
51 SQL CREATE
Database
...
53 Example:

...
54 Syntax:

...
54 SQL SELECT
Database
...
55 Example:

...
56 Syntax:

...
56 Example:

...
57 SQL DROP or DELETE
Table
...
59 Example:

...
60 Syntax:

...
60 Populate one table using another
table:
...
62
Syntax:
...
62 SQL WHERE Clause

...
64 Example:

...
66 The AND Operator:

...
66 Example:

...
67 Syntax:

...
67 SQL UPDATE
Query
...
69 Example:

...
71 Syntax:

...
71 SQL LIKE
Clause
...
73 Example:

...
75 Syntax:

...
75 SQL ORDER BY
Clause
...
77 Example:

...
79 Syntax:

...
79 SQL Distinct Keyword

...
81 TUTORIALS POINT Simply Easy
Learning Example:
...
83 Syntax:

...
83 SQL
Constraints
...
85 DEFAULT
Constraint:
...
86 Drop Default
Constraint:
...
87 Example:

...
87 PRIMARY
Key:
...
88 Delete Primary Key:

...
89 Example:

...
90 CHECK Constraint:

...
90 DROP a CHECK Constraint:

...
91 Example:

...
91 Dropping
Constraints:
...
92 SQL Joins

...
94 INNER
JOIN
...
94 Example:

...
95 Syntax:

...
95 RIGHT
JOIN
...
96 Example:

...
97 Syntax:

...
98 SELF
JOIN
...
99 Example:

...
100 Syntax:

...
100 SQL Unions Clause

...
102 Example:

...
103 Syntax:

...
104 Example:

...
109 Syntax:

...
109 SQL Alias Syntax

...
111 Example:

...
113 The CREATE INDEX Command:

...
113 Unique Indexes:

...
114 Implicit Indexes:

...
114 When should indexes be avoided?

...
115
Syntax:
...
116 SQL TRUNCATE
TABLE
...
118 Example:

...
119 Creating Views:


...
119 The WITH CHECK
OPTION:
...
120 TUTORIALS POINT Simply Easy Learning
Inserting Rows into a View:
...
122 Dropping Views:

...
123 Syntax:

...
123 SQL Transactions

...
125 Transaction
Control:
...
125 Example:

...
126 Example:

...
127 Example:

...
128 The SET TRANSACTION
Command:
...
130 Syntax:

...
131 SQL Date
Functions
...
134
ADDTIME(expr1,expr2)
...
135 CURDATE()

...
136 CURTIME()

...
136 CURRENT_TIMESTAMP and
CURRENT_TIMESTAMP()
...
136 DATEDIFF(expr1,expr2)

...
137 DATE_SUB(date,INTERVAL expr
unit)
...
138 DATE_SUB(date,INTERVAL
expr unit)
...
140
DAYNAME(date)
...
140
DAYOFWEEK(date)
...
140 EXTRACT(unit
FROM date)
...
141
FROM_UNIXTIME(unix_timestamp)
...
141 HOUR(time)


...
142 LOCALTIME and LOCALTIME()

...
142 MAKEDATE(year,dayofyear)

...
143
MICROSECOND(expr)
...
143
MONTH(date)
...
143
NOW()
...
144 PERIOD_DIFF(P1,P2)

...
144
SECOND(time)
...
145
STR_TO_DATE(str,format)
...
145
SUBTIME(expr1,expr2)
...
146 TIME(expr)

...
146 TIMESTAMP(expr),
TIMESTAMP(expr1,expr2)
...
147
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
...
148
TIME_TO_SEC(time)
...
148 UNIX_TIMESTAMP(),
UNIX_TIMESTAMP(date)
...
149 UTC_TIME,
UTC_TIME()
...
149 WEEK(date[,mode])

...
150 TUTORIALS POINT Simply
Easy Learning WEEKOFYEAR(date)
...
150 YEARWEEK(date),
YEARWEEK(date,mode)
...
152 Example

...
153 SQL Clone
Tables
...
154 Step
1:
...
154 Step
3:
...
156 Subqueries with the SELECT
Statement:
...
157 Subqueries with the INSERT

Statement:
...
157 Subqueries with the UPDATE
Statement:
...
158 Subqueries with the DELETE
Statement:
...
159 SQL – Using
Sequences
...
160 Example:

...
161 PERL
Example:
...
161 Renumbering an Existing
Sequence:
...
161 SQL – Handling Duplicates
...
163 Example:

...
165 Preventing SQL
Injection:
...
166 SQL Useful Functions

...
176 ACOS(X)

...
176
ATAN(X)
...
177
BIT_AND(expression)
...
177
BIT_OR(expression)
...
178
CEILING(X)
...
178
COS(X)
...
179
DEGREES(X)
...
179
FLOOR(X)
...
180
GREATEST(n1,n2,n3,
...
180
INTERVAL(N,N1,N2,N3,
...
)

...
181 LOG(B,X)

...
181
MOD(N,M)
...
182 PI()

...
182 POWER(X,Y)


...
182 ROUND(X)

...
183
SIGN(X)
...
183 SQRT(X)

...
184
STDDEV(expression)
...
184 TRUNCATE(X,D)

...
186 BIN(N)

...
187 CHAR(N,
...
187 TUTORIALS POINT Simply Easy Learning
CHAR_LENGTH(str)
...
188
CONCAT(str1,str2,
...
188
CONV(N,from_base,to_base)
...
189
EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
...
189
FIND_IN_SET(str,strlist)
...
189 HEX(N_or_S)

...
190 INSTR(str,substr)

...
191 LEFT(str,len)

...
191
LOAD_FILE(file_name)
...
191
LOWER(str)
...
192
LTRIM(str)
...
)

...
193
OCT(N)
...
193
ORD(str)
...
193
QUOTE(str)
...
194
REPEAT(str,count)
...
194
REVERSE(str)
...
195

RPAD(str,len,padstr)
...
195
SOUNDEX(str)
...
196
SPACE(N)
...
196 TUTORIALS POINT Simply Easy Learning
SUBSTRING(str,pos)
...
197 SUBSTRING(str,pos,len)

...
197
SUBSTRING_INDEX(str,delim,count)
...
198 TRIM([remstr FROM] str)

...
198 UNHEX(str)

...
199 TUTORIALS POINT Simply Easy Learning
SQL Overview SQL tutorial gives unique learning on Structured Query Language and it helps to make
practice on SQL commands which provides immediate results
...
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
...
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
...
 Allows users to describe the data
...
 Allows to embed within other languages
using SQL modules, libraries & pre-compilers
...
CHAPTER 1 TUTORIALS POINT Simply Easy Learning  Allows users to create view, stored
procedure, functions in a database
...
E
...
"Ted" of IBM is known as the father of relational databases
...
 1974 -- Structured Query Language appeared
...
 1986 -- IBM
developed the first prototype of relational database and standardized by ANSI
...
SQL Process: When you
are executing an SQL command for any RDBMS, the system determines the best way to carry out
your request and SQL engine figures out how to interpret the task
...
These components are Query Dispatcher, Optimization Engines, Classic
Query Engine and SQL Query Engine, etc
...
Following is a simple diagram showing SQL Architecture:
TUTORIALS POINT Simply Easy Learning SQL Commands: The standard SQL commands to interact
with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP
...
DROP Deletes an entire table, a view of a table
or other object in the database
...
RDBMS is the basis for SQL and for all
modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access
...
F
...
What is tabl e? The data0 in RDBMS is
stored in database objects called tables
...
Remember, a table is the most common and simplest form of data
storage in a relational database
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 |
Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+---------+ CHAPTER 2 TUTORIALS POINT Simply Easy Learning What is field? Every table is broken up into
smaller entities called fields
...
A field is a column in a table that is designed to maintain specific information about
every record in the table
...
For example, there are 7 records in the above CUSTOMERS
table
...
00 | +----+----------+-----+-----------+----------+ A
record is a horizontal entity in a table
...
For example, a column in the
CUSTOMERS table is ADDRESS, which represents location description and would consist of the
following: +-----------+ | ADDRESS | +-----------+ | Ahmedabad | | Delhi | | Kota | | Mumbai | | Bhopal
| | MP | | Indore | +----+------+ What is NULL value? A NULL value in a table is a value in a field that
appears to be blank, which means a field with a NULL value is a field with no value
...
A field with a NULL value is one that has been left blank during record creation
...
These are used to limit the type of data that can go into a table
...
Constraints could be column level or table level
...
Following are commonly used constraints available in SQL:  NOT NULL Constraint: Ensures
that a column cannot have NULL value
...
 UNIQUE Constraint: Ensures that all values in a column are different
...
 FOREIGN Key: Uniquely
identified a rows/records in any another database table
...
 INDEX: Use to create and retrieve
data from the database very quickly
...
If you do not want a column to have a NULL value, then you need to define such constraint
on this column specifying that NULL is now not allowed for that column
...
Example: For example, the following SQL creates a new
table called CUSTOMERS and adds five columns, three of which, ID and NAME and AGE, specify not
to accept NULLs: CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); If

CUSTOMERS table has already been created, then to add a NOT NULL constraint to SALARY column
in Oracle and MySQL, you would write a statement similar to the following: ALTER TABLE
CUSTOMERS MODIFY SALARY DECIMAL (18, 2) NOT NULL; TUTORIALS POINT Simply Easy Learning
DEFAULT Constraint: The DEFAULT constraint provides a default value to a column when the INSERT
INTO statement does not provide a specific value
...
Here, SALARY column is set to 5000
...
then by default
this column would be set to 5000
...
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME
VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2)
DEFAULT 5000
...
00; Drop Default Constraint: To
drop a DEFAULT constraint, use the following SQL: ALTER TABLE CUSTOMERS ALTER COLUMN
SALARY DROP DEFAULT; UNIQUE Constraint: The UNIQUE Constraint prevents two records from
having identical values in a particular column
...
Example: For example, the following SQL
creates a new table called CUSTOMERS and adds five columns
...
Primary keys must contain
unique values
...
A table can have only one primary
key, which may consist of single or multiple fields
...
If a table has a primary key defined on any field(s), then you can not
have two records having the same value of that field(s)
...
Create Primary Key: Here is the syntax to define ID attribute as a primary
key in a CUSTOMERS table
...
For defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME) ); To create a PRIMARY KEY
constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the
following SQL syntax: ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID,
NAME); Delete Primary Key: You can clear the primary key constraints from the table, Use Syntax:
ALTER TABLE CUSTOMERS DROP PRIMARY KEY ; TUTORIALS POINT Simply Easy Learning FOREIGN
Key: A foreign key is a key used to link two tables together
...
Foreign Key is a column or a combination of columns whose values match a Primary Key in a

different table
...
If a table has a primary key defined on any field(s), then you can
not have two records having the same value of that field(s)
...
ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID)
REFERENCES CUSTOMERS (ID); TUTORIALS POINT Simply Easy Learning DROP a FOREIGN KEY
Constraint: To drop a FOREIGN KEY constraint, use the following SQL: ALTER TABLE ORDERS DROP
FOREIGN KEY; CHECK Constraint: The CHECK Constraint enables a condition to check the value being
entered into a record
...
Example: For example, the following SQL creates a new table called
CUSTOMERS and adds five columns
...
This syntax does not work with MySQL: ALTER TABLE CUSTOMERS
DROP CONSTRAINT myCheckConstraint; TUTORIALS POINT Simply Easy Learning INDEX: The INDEX is
used to create and retrieve data from the database very quickly
...
When index is created, it is assigned a ROWID for each row before it
sorts out the data
...
Selection of fields depends on what you are using in your SQL queries
...
); To create an INDEX on AGE column, to optimize the search on
customers for a particular age, following is the SQL syntax: CREATE INDEX idx_age ON CUSTOMERS (
AGE ); DROP an INDEX Constraint: To drop an INDEX constraint, use the following SQL: ALTER TABLE
CUSTOMERS DROP INDEX idx_age; Data Integrity: The following categories of the data integrity exist
with each RDBMS:  Entity Integrity : There are no duplicate rows in a table
...
 Referential Integrity : Rows cannot be deleted which are
used by other records
...
Database Normalization Database normalization is
the process of efficiently organizing data in a database
...

Ensuring data dependencies make sense
...
Normalization consists of a
series of guidelines that help guide you in creating a good database structure
...
The aim of normal forms is to organize the database structure so that it

complies with the rules of first normal form, then second normal form, and finally third normal form
...
 First Normal Form (1NF)  Second Normal Form
(2NF)  Third Normal Form (3NF) First Normal Form First normal form (1NF) sets the very basic rules
for an organized database:  Define the data items required, because they become the columns in a
table
...
 Ensure that there are no repeating groups of data
...
First Rule of 1NF: You must define the data items
...
For example, you put all
the columns relating to locations of meetings in the Location table, those relating to members in the
MemberDetails table, and so on
...
Consider we have the following table: TUTORIALS POINT Simpl y Easy
Learning CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT
NOT NULL, ADDRESS CHAR (25), ORDERS VARCHAR(155) ); So if we populate this table for a single
customer having multiple orders, then it would be something as follows: ID NAME AGE ADDRESS
ORDERS 100 Sachin 36 Lower West Side Cannon XL-200 100 Sachin 36 Lower West Side Battery XL200 100 Sachin 36 Lower West Side Tripod Large But as per 1NF, we need to ensure that there are
no repeating groups of data
...
Second Normal Form Second normal form states that it should meet
all the rules for 1NF and there must be no partial dependences of any of the columns on the primary
key: Consider a customer-order relation and you want to store customer ID, customer name, order
ID and order detail, and date of purchase: CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL,
CUST_NAME VARCHAR (20) NOT NULL, ORDER_ID INT NOT NULL, ORDER_DETAIL VARCHAR (20) NOT
NULL, SALE_DATE DATETIME, PRIMARY KEY (CUST_ID, ORDER_ID) ); This table is in first normal form,
in that it obeys all the rules of first normal form
...
Combined, they are unique assuming same customer would hardly order same thing
...
CUST_NAME is dependent on CUST_ID, and there's no real link between a
customer's name and what he purchased
...
To make this table comply with second normal form, you
need to separate the columns into three tables
...
 All nonprimary fields are dependent on the primary key
...
For example, in the below table, street name, city, and state
are unbreakably bound to the zip code
...
To comply with third normal form, all you need to do is move the Street, City,
and State fields into their own table, which you can call the Zip Code table: CREATE TABLE ADDRESS(
ZIP VARCHAR(12), STREET VARCHAR(200), CITY VARCHAR(100), STATE VARCHAR(100), PRIMARY KEY
(ZIP) ); Next, alter the CUSTOMERS table as follows: CREATE TABLE CUSTOMERS( CUST_ID INT NOT
NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, ZIP VARCHAR(12), EMAIL_ID
VARCHAR(256), PRIMARY KEY (CUST_ID) ); The advantages of removing transitive dependencies are
mainly twofold
...
The second advantage is data integrity
...
For example, if address and zip code data were stored in three or four different
tables, then any changes in zip codes would need to ripple out to every record in those three or four
tables
...
This tutorial gives a brief overview of few most popular RDBMS
...
MySQL MySQL is an open source SQL database,
which is developed by Swedish company MySQL AB
...
" MySQL is supporting many different platforms including
Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X
...
MySQL comes with a
very fast, multi-threaded, multi-user, and robust SQL database server
...
 First internal release on 23 May
1995
...
 Version 3
...
 Version 4
...
 Version 4
...
 Version 4
...

Version 5
...
 Sun Microsystems acquired
MySQL AB on 26 February 2008
...
1: production release 27 November 2008
...
 High Availability
...
 Robust Transactional Support
...
 Strong Data Protection
...
 Management
Ease
...
 Lowest Total Cost of Ownership
...
Its primary
query languages are:  T-SQL
...
History:  1987 - Sybase releases SQL Server for UNIX
...
 1989 - Microsoft, Sybase, and
Aston-Tate release SQL Server 1
...
 1990 - SQL Server 1
...
0 clients
...
 2000 - Microsoft releases
SQL Server 2000
...
 2002 Microsoft releases SQLXML 2
...
 2002 - Microsoft releases
SQLXML 3
...
TUTORIALS POINT Simply Easy Learning  2005 - Microsoft releases SQL Server 2005 on
November 7th, 2005
...
 High Availability
...

Database snapshots
...
 Service Broker
...
 Ranking functions
...
 XML integration
...
CATCH
...
ORACLE It is a very
large and multi-user database management system
...
Oracle works to efficiently manage its resource, a
database of information, among the multiple clients requesting and sending data in the network
...
Oracle supports all major operating
systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX
flavors
...
 1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development
Laboratories to undertake development work
...
0 of Oracle was released and it
became first commercial relational database and first SQL database
...
(RSI)
...
 1982 - RSI was
renamed to Oracle Corporation
...
0, rewritten in C language and ran on multiple platforms
...
0 was
released
...
 1985
- Oracle version 4
...
It contained features like concurrency control - multi-version read
consistency, etc
...
The new version focused on better
partitioning, easy migration, etc
...
Microsoft Access is an entry -level
database management software
...
MS Access uses the Jet database engine, which utilizes a specific
SQL language dialect (sometimes referred to as Jet SQL)
...
MS Access has easy-to-use intuitive graphical interface
...
0 was released
...
1 released to improve compatibility with inclusion of the
Access Basic programming language
...
 2007 - Access 2007, a new database format was introduced ACCDB which supports complex
data types such as multi valued and attachment fields
...
 The import and export of data to many
formats including Excel, Outlook, ASCII, dBase, Paradox, FoxPro, SQL Server, Oracle, ODBC, etc
...
This makes it very convenient to distribute the entire application to
another user, who can run it in disconnected environments
...
These queries and Access tables can be referenced from other programs like VB6 and
...
 The desktop editions of Microsoft SQL Server can be used with Access as an
alternative to the Jet Database Engine
...
Unlike
client-server relational database management systems (RDBMS), Microsoft Access does not
implement database triggers, stored procedures, or transaction logging
...
This
tutorial gives you a quick start with SQL by listing all the basic SQL Syntax: All the SQL statemen ts
start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE,
SHOW and all the statements end with a semicolon (;)
...
So if you are working with MySQL, then you need to give table
names as they exist in the database
...
columnN
FROM table_name; SQL DISTINCT Clause: SELECT DISTINCT column1, column2
...
columnN FROM table_name WHERE
CONDITION; SQL AND/OR Clause: SELECT column1, column2
...
columnN FROM table_name WHERE column_name IN (val -1,
val-2,
...
columnN FROM table_name
WHERE column_name BETWEEN val-1 AND val-2; SQL LIKE Clause: SELECT column1,
column2
...
columnN FROM table_name WHERE CONDITION ORDER BY
column_name {ASC|DESC}; SQL GROUP BY Clause: SELECT SUM(column_name) FROM table_name
WHERE CONDITION GROUP BY column_name; SQL COUNT Clause: SELECT COUNT(column_name)
FROM table_name WHERE CONDITION; SQL HAVING Clause: SELECT SUM(column_name) FROM
table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);
SQL CREATE TABLE Statement: CREATE TABLE table_name( TUTORIALS POINT Simply Easy Learning
column1 datatype, column2 datatype, column3 datatype,
...
columnN);
SQL DROP INDEX Statement: ALTER TABLE table_name DROP INDEX index_name; SQL DESC
Statement: DESC table_name; SQL TRUNCATE TABLE Statement: TRUNCATE TABLE table_name; SQL
ALTER TABLE Statement: ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
SQL ALTER TABLE Statement (Rename): ALTER TABLE table_name RENAME TO new_table_name;
SQL INSERT INTO Statement: INSERT INTO table_name( column1, column2
...
valueN); SQL UPDATE Statement: UPDATE table_name TUTORIALS POINT Simply
Easy Learning SET column1 = value1, column2 = value2
...
Each column, variable
and expression has related data type in SQL
...
You would choose a particular data type for a table column based on your requirement
...
5808 +922,337,203,685,477
...
3648
+214,748
...
79E + 308 1
...
40E + 38 3
...
M
...
33 milliseconds accuracy where as smalldatetime has 1 minute accuracy
...
( Fixed
length non-Unicode characters) Varchar Varchar Maximum of 8,000 characters
...
varchar(max) varchar(max) Maximum length of 231characters, Variable -length nonUnicode data (SQL Server 2005 only)
...
Unicode Character Strings Data Types: DATA TYPE Description
Nchar Maximum length of 4,000 characters
...
(Variable length Unicode) nvarchar(max) Maximum length of 231characters (SQL
Server 2005 only)
...
(
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) Image Maximum length of 2,147,483,647
bytes
...
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
...
cursor Reference to a cursor object table
Stores a result set for later processing TUTORIALS POINT Simply Easy Learning SQL Operators What is
an Operator in SQL? 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
...
 Arithmetic operators  Comparison operators  Logical
operators  Operators used to negate conditions SQL Arithmetic Operators: Assume variable a holds
10 and variable b holds 20, then: Operator Description Example + Addition - Adds values on either
side of the operator a + b will give 30 - Subtraction - Subtracts right hand operand from left hand
operand a - b will give -10 * Multiplication - Multiplies values on either side of the operator a * b will
give 200 / Division - Divides left hand operand by right hand operand b / a will give 2 % Modulus Divides left hand operand by right hand operand and returns remainder b % a will give 0 CHAPTER 6
TUTORIALS POINT Simply Easy Learning Here are simple examples showing usage of SQL Arithmetic
Operators: SQL> select 10+ 20; +--------+ | 10+ 20 | +--------+ | 30 | +--------+ 1 row in set (0
...
00 sec) SQL>
select 10 / 5; +--------+ | 10 / 5 | +--------+ | 2
...
03 sec) SQL> select 12 %
5; +---------+ | 12 % 5 | +---------+ | 2 | +---------+ 1 row in set (0
...
(a = b) is not true
...
(a != b) is true
...
(a <> b) is true
...
(a > b) is not true
...
(a < b) is true
...
(a >= b) is not true
...
(a <= b) is true
...
(a !< b) is false
...
(a !> b) is true
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25
| Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+ 7 rows in set (0
...
00 | |
5 | Hardik | 27 | Bhopal | 8500
...
00 | +----+----------+-----+--------+----------+ 3 rows in set (0
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 sec) SQL> SELECT * FROM CUSTOMERS WHERE SALARY
!= 2000; +----+----------+-----+---------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+---------+----------+ | 2 | Khilan | 25 | Delhi | 1500
...
00 |
| 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore |
10000
...
00 sec) SQL> SELECT * FROM
CUSTOMERS WHERE SALARY <> 2000; +----+----------+-----+---------+----------+ TUTORIALS POINT Simply
Easy Learning | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+---------+----------+ | 2 |
Khilan | 25 | Delhi | 1500
...
00 | | 5 | Hardik | 27 | Bhopal |
8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 sec) SQL> SELECT * FROM CUSTOMERS WHERE SALARY >=
6500; +----+----------+-----+---------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+----+---------+----------+ | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | |
7 | Muffy | 24 | Indore | 10000
...
00 sec) SQL
Logical Operators: Here is a list of all the logical operators available in SQL
...
AND The AND
operator allows the existence of multiple conditions in an SQL statement's WHERE clause
...
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
...
IN The IN operator is
used to compare a value to a list of literal values that have been specified
...
NOT The NOT operator reverses
the meaning of the logical operator with which it is used
...
This is a negate operator
...
IS NULL The NULL operator is used to compare a value with a NULL
value
...
Consider the CUSTOMERS table having the following records: SQL> SELECT * FROM
CUSTOMERS; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+----------+ TUTORIALS POINT Simply Easy Learning | 1 | Ramesh | 32 |
Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 sec) Here are simple examples showing usage of SQL Comparison Operators: SQL> SELECT *
FROM CUSTOMERS WHERE AGE >= 25 AND SALARY >= 6500; +----+----------+-----+---------+---------+ | ID
| NAME | AGE | ADDRESS | SALARY | +----+----------+-----+---------+---------+ | 4 | Chaitali | 25 |
Mumbai | 6500
...
00 | +----+----------+-----+---------+---------+ 2
rows in set (0
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | +----+----------+-----+-----------+----------+ 5 rows in set (0
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25
| Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+ 7 rows in set (0
...
00 | +----+-------+-----+---------+---------+ 1 row in set (0
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 |
+----+----------+-----+---------+---------+ 3 rows in set (0
...
00 | | 4 |
Chaitali | 25 | Mumbai | 6500
...
00 | +----+----------+-----+--------+---------+ 3 rows in set (0
...
02 sec) SQL> SELECT * FROM CUSTOMERS WHERE AGE > ALL
(SELECT AGE FROM CUSTOMERS WHERE SALARY > 6500); +----+--------+-----+-----------+---------+ | ID |
NAME | AGE | ADDRESS | SALARY | +----+--------+-----+-----------+---------+ | 1 | Ramesh | 32 |
Ahmedabad | 2000
...
02 sec) SQL> SELECT *
FROM CUSTOMERS WHERE AGE > ANY (SELECT AGE FROM CUSTOMERS WHERE SALARY > 6500); +---+----------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 4 |
Chaitali | 25 | Mumbai | 6500
...
00 | +----+----------+-----+----------+---------+ 4 rows in set (0
...
SQL EXPRESSIONs are like formulas and they are written in query language
...
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 matchin g
single value
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25
| Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+ CHAPTER 7 TUTORIALS
POINT Simply Easy Learning 7 rows in set (0
...
00 | +----+-------+-----+---------+----------+ 1 row in set (0
...

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
...
00 sec)
There are several built-in functions like avg(), sum(), count(), etc
...
SQL> SELECT COUNT(*) AS
"RECORDS" FROM CUSTOMERS; +---------+ | RECORDS | +---------+ | 7 | +---------+ 1 row in set (0
...
00 sec) Another date expression is as
follows: TUTORIALS POINT Simply Easy Learning SQL> SELECT GETDATE();; +-------------------------+ |

GETDATE | +-------------------------+ | 2009-10-22 12:07:18
...
00 sec) TUTORIALS POINT Simply Easy Learning SQL CREATE Database The SQL CREATE DATABASE
statement is used to create new SQL database
...
Example: If you want to create new database , then CREATE DATABASE statement would be
as follows: SQL> CREATE DATABASE testDB; Make sure you have admin privilege before creating any
database
...
00 sec) CHAPTER 8 TUTORIALS POINT Simply Easy Learning DROP or DELETE Database The SQL
DROP DATABASE statement is used to drop an existing database in SQL schema
...
Example: If you want to delete an existing database ,
then DROP DATABASE statement would be as follows: SQL> DROP DATABASE testDB; NOTE: Be
careful before using this operation because by deleting an existing database would result in loss of
complete information stored in the database
...
Once a database is dropped, you can check it
...
00
sec) CHAPTER 9 TUTORIALS POINT Simply Easy Learning SQL SELECT Database When you have
multiple databases in your SQL Schema, then before starting your operation, you would need to
select a database where all the operations would be performed
...
Syntax: Basic syntax of USE statement is as follows: USE
DatabaseName; Always database name should be unique within the RDBMS
...
00 sec) Now, if you want to work with AMROOD database, then you
can execute the following SQL command and start working with AMROOD database: SQL> USE
AMROOD; CHAPTER 10 TUTORIALS POINT Simply Easy Learning SQL CREATE Table Creating a basic
table involves naming the table and defining its columns and each column's data type
...
Syntax: Basic syntax of CREATE TABLE
statement is as follows: CREATE TABLE table_name( column1 datatype, column2 datatype, column3
datatype,
...
In this case, you want to create a new
table
...
Then in
brackets comes the list defining each column in the table and what sort of data type it is
...
A copy of an existing table can be created using a
combination of the CREATE TABLE statement and the SELECT statement
...
Create Table Using another Table A copy of an existing
table can be created using a combination of the CREATE TABLE statement and the SELECT statement
...
All columns or specific columns can be selected
...
Syntax: The basic syntax for creating a table from another table is as follows:
CHAPTER 11 TUTORIALS POINT Simply Easy Learning CREATE TABLE NEW_TABLE_NAME AS SELECT [
column1, column2
...
are the fields of existing table and same would be used to create fields of new table
...
00 | | 2 | 1500
...
00 | | 4 |
6500
...
00 | | 6 | 4500
...
00 | +----+----------+ Example: Following is an
example, which creates a CUSTOMERS table with ID as primary key and NOT NULL are the
constraints showing that these fileds can not be NULL while creating records in this table: SQL>
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); You can verify if your table has
been created successfully by looking at the message displayed by the SQL server, otherwise you can
use DESC command as follows: SQL> DESC CUSTOMERS; +---------+---------------+------+-----+---------+------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+------+-----+---------+-------+ | ID
| int(11) | NO | PRI | | | | NAME | varchar(20) | NO | | | | | AGE | int(11) | NO | | | | | ADDRESS |
char(25) | YES | | NULL | | | SALARY | decimal(18,2) | YES | | NULL | | +---------+---------------+------+----+---------+-------+ TUTORIALS POINT Simply Easy Learning 5 rows in set (0
...
TUTORIALS POINT Simply Easy Learning SQL DROP or DELETE Table The SQL
DROP TABLE statement is used to remove a table definition and all data, indexes, triggers,
constraints, and permission specifications for that table
...
Syntax: Basic syntax of DROP TABLE statement is as follows: DROP TABLE
table_name; Example: Let us first verify CUSTOMERS table and then we would delete it from the
database: SQL> DESC CUSTOMERS; +---------+---------------+------+-----+---------+-------+ | Field | Type |
Null | Key | Default | Extra | +---------+---------------+------+-----+---------+-------+ | ID | int(11) | NO | PRI
| | | | NAME | varchar(20) | NO | | | | | AGE | int(11) | NO | | | | | ADDRESS | char(25) | YES | |
NULL | | | SALARY | decimal(18,2) | YES | | NULL | | +---------+---------------+------+-----+---------+-------+
5 rows in set (0
...
01 sec) Now, if you would try
DESC command, then you would get error as follows: SQL> DESC CUSTOMERS; ERROR 1146 (42S02):
Table 'TEST
...
CHAPTER 12 TUTORIALS POINT Simply Easy Learning SQL INSERT Query The SQL INSERT
INTO Statement is used to add new rows of data to a table in the database
...
columnN)] VALUES (value1, value2, value3,
...
columnN are the names of the columns in the table into which you want to insert data
...
But make sure the order of the values is in the same order as the columns in
the table
...
valueN); Example: Following statements would create six records in
CUSTOMERS table: INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1,
'Ramesh', 32, 'Ahmedabad', 2000
...
00 ); INSERT INTO CUSTOMERS
(ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000
...
00 ); INSERT
INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500
...
00 );
You can create a record in CUSTOMERS table using second syntax as follows: CHAPTER 13
TUTORIALS POINT Simply Easy Learning INSERT INTO CUSTOMERS VALUES (7, 'Muffy', 24, 'Indore',
10000
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+----+-----------+----------+ Populate one table using another table: You can populate data into a table
through select statement over another table provided another table has a set of fields, which are
required to populate first table
...
columnN)] SELECT column1, column2,
...
These result
tables are called result-sets
...
are the fields of a table
whose values you want to fetch
...
00 | | 2 |
Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai |
6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+ Following is an example, which would
fetch ID, Name and Salary fields of the customers available in CUSTOMERS table: SQL> SELECT ID,
NAME, SALARY FROM CUSTOMERS; This would produce the following result: +----+----------+----------+
| ID | NAME | SALARY | CHAPTER 14 TUTORIALS POINT Simply Easy Learning +----+----------+----------+
| 1 | Ramesh | 2000
...
00 | | 3 | kaushik | 2000
...
00 |
| 5 | Hardik | 8500
...
00 | | 7 | Muffy | 10000
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4
| Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP |
4500
...
00 | +----+----------+-----+-----------+----------+ TUTORIALS
POINT Simply Easy Learning SQL WHERE Clause The SQL WHERE clause is used to specify a condition
while fetching the data from single table or joining with multiple tables
...
You would use WHERE clause to filter the
records and fetching only necessary records
...
, which we would examine in
subsequent chapters
...
00 | | 5 | Hardik | 8500
...
00 | | 7 | Muffy | 10000
...
Here,
it is important to note that all the strings should be given inside single quotes ('') where as numeric
values should be given without any quote as in above example: SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS WHERE NAME = 'Hardik'; This would produce the following result: +----+---------+----------+ | ID | NAME | SALARY | +----+----------+----------+ | 5 | Hardik | 8500
...
These two
operators are called conjunctive operators
...
The AND Operator: The AND
operator allows the existence of multiple conditions in an SQL statement's WHERE clause
...
AND [conditionN]; You can
combine N number of conditions using AND operator
...

Example: Consider the CUSTOMERS table having the following records: +----+----------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 |
Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota
| 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 | | 7 | Muffy | 10000
...
Syntax: The basic syntax of OR operator with WHERE clause is as follows: SELECT column1,
column2, columnN FROM table_name WHERE [condition1] OR [condition2]
...
For an action to be taken by the SQL
statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR
must be TRUE
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 |
| 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+-----+----------+----------+ Following is an example, which would fetch ID, Name and Salary fields from the
CUSTOMERS table where salary is greater than 2000 OR age is less tan 25 years: SQL> SELECT ID,
NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 OR age < 25; This would produce the
following result: +----+----------+----------+ | ID | NAME | SALARY | +----+----------+----------+ | 3 |
kaushik | 2000
...
00 | TUTORIALS POINT Simply Easy Learning | 5 | Hardik |
8500
...
00 | | 7 | Muffy | 10000
...
You can use WHERE clause with UPDATE query to update sel ected rows,
otherwise all the rows would be affected
...
, columnN =
valueN WHERE [condition]; You can combine N number of conditions using AND or OR operators
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 |
Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+---------+ Following is an example, which would update ADDRESS for a customer whose ID is 6: SQL>
UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6; Now, CUSTOMERS table would have the
following records: CHAPTER 17 TUTORIALS POINT Simply Easy Learning +----+----------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 |
Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota
| 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00; Now, CUSTOMERS table would have the following records:
+----+----------+-----+---------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+--------+---------+ | 1 | Ramesh | 32 | Pune | 1000
...
00 | | 3 | kaushik
| 23 | Pune | 1000
...
00 | | 5 | Hardik | 27 | Pune | 1000
...
00 | | 7 | Muffy | 24 | Pune | 1000
...
You can use WHERE clause with DELETE query to delete
selected rows, otherwise all the records would be deleted
...
Example: Consider the CUSTOMERS table having
the following records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY
| +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5
| Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore |
10000
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
TUTORIALS POINT Simply Easy Learning SQL LIKE Clause The SQL LIKE clause is used
to compare a value to similar values using wildcard operators
...
The underscore represents a single number o r
character
...
Syntax: The basic syntax of % and _ is as
follows: SELECT FROM table_name WHERE column LIKE 'XXXX%' or SELECT FROM table_name
WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE column LIKE 'XXXX_' or SELECT
FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM table_name WHERE column LIKE
'_XXXX_' You can combine N number of conditions using AND or OR operators
...
CHAPTER 19 TUTORIALS POINT Simply Easy Learning Example: Here are
number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:
Statement Description WHERE SALARY LIKE '200%' Finds any values that start with 200 WHERE
SALARY LIKE '%200%' Finds any values that have 200 in any position WHERE SALARY LIKE '_00%'
Finds any values that have 00 in the second and third positions WHERE SALARY LIKE '2_%_%' Finds
any values that start with 2 and are at least 3 characters in length WHERE SALARY LIKE '%2' Finds any
values that end with 2 WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second
position and end with a 3 WHERE SALARY LIKE '2___3' Finds any values in a five -digit number that
start with 2 and end with 3 Let us take a real example, consider the CUSTOMERS table having the
following records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +---+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 |
Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore |

10000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
Note: All the databases do not
support TOP clause
...
Syntax: The basic syntax of TOP
clause with SELECT statement would be as follows: SELECT TOP number|percent column_name(s)
FROM table_name WHERE [condition] Example: Consider the CUSTOMERS table having the following
records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik
| 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 | CHAPTER 20
TUTORIALS POINT Simply Easy Learning | 2 | Khilan | 25 | Delhi | 1500
...
00 | +----+---------+-----+-----------+---------+ If you are using MySQL server, then here is an
equivalent example: SQL> SELECT * FROM CUSTOMERS LIMIT 3; This would produce the following
result: +----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00
| | 3 | kaushik | 23 | Kota | 2000
...
00
| | 2 | Khilan | 25 | Delhi | 1500
...
00 | +----+---------+-----+----------+---------+ TUTORIALS POINT Simply Easy Learning SQL ORDER BY Clause The SQL ORDER BY clause is
used to sort the data in ascending or descending order, based on one or more columns
...
Syntax: The basic syntax of ORDER BY
clause is as follows: SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1,
column2,
...

Make sure whatever column you are using to sort, that column should be in column-list
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4
| Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP |
4500
...
00 | +----+----------+-----+-----------+----------+ Following is
an example, which would sort the result in ascending order by NAME and SALARY: SQL> SELECT *
FROM CUSTOMERS ORDER BY NAME, SALARY; This would produce the following result: +----+---------+-----+-----------+----------+ CHAPTER 21 TUTORIALS POINT Simply Easy Learning | ID | NAME | AGE |
ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | | 1 | Ramesh |
32 | Ahmedabad | 2000
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 | | 2
| Khilan | 25 | Delhi | 1500
...
00 | | 5 | Hardik | 27 | Bhopal |
8500
...
00 | +----+----------+-----+-----------+----------+ TUTORIALS
POINT Simply Easy Learning SQL Group By The SQL GROUP BY clause is used in collaboration with the
SELECT statement to arrange identical data into groups
...
Syntax: The basic syntax of GROUP
BY clause is given below
...
SELECT column1, column2 FROM table_name
WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2 Example: Consider
the CUSTOMERS table having the following records: +----+----------+-----+-----------+----------+ | ID |
NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 |
Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 | | Hardik | 8500
...
00
| | Khilan | 1500
...
00 | | Muffy | 10000
...
00 | +----------+------------+ Now, let us have following table where CUSTOMERS table has the following records with
duplicate names: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +---+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 |
Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore |
10000
...
00 | | kaushik |
8500
...
00 | | Muffy | 10000
...
00 | +---------+-------------+
TUTORIALS POINT Simply Easy Learning SQL Distinct Keyword The SQL DISTINCT keyword is used in
conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique
records
...
While
fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate
records
...
columnN FROM table_name WHERE [condition] Example:
Consider the CUSTOMERS table having the following records: +----+----------+-----+-----------+----------+ |
ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 |
Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
CHAPTER 23 TUTORIALS POINT
Simply Easy Learning +----------+ | SALARY | +----------+ | 1500
...
00 | | 2000
...
00
| | 6500
...
00 | | 10000
...
00 | | 2000
...
00 | | 6500
...
00 | | 10000
...
Some
databases sort query results in ascending order by default
...
columnN] [ASC |
DESC]; You can use more than one column in the ORDER BY clause
...
Example: Consider the CUSTOMERS table
having the following records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS |
SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 |
Indore | 10000
...
00 | | 5 | Hardik | 27 | Bhopal |
8500
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 |
+----+----------+-----+-----------+----------+ Following is an example, which would sort the result in
descending order by NAME: SQL> SELECT * FROM CUSTOMERS ORDER BY NAME DESC; This would
produce the following result: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS |
SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 6 | Komal | 22 | MP | 4500
...
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 | +----+----------+-----+-----------+----------+ This will sort customers by ADDRESS in your
ownoOrder of preference first and in a natural order for the remaining addresses
...
TUTORIALS POINT Simply Easy Learning SQL
Constraints Constraints are the rules enforced on data columns on table
...
This ensures the accuracy and reliability of the data in the
database
...
Column level constraints are applied only
to one column where as table level constraints are applied to the whole table
...
These constraints have already been di scussed in SQL RDBMS Concepts chapter but its worth to revise them at this point
...

 DEFAULT Constraint: Provides a default value for a column when none is specified
...
 PRIMARY Key: Uniquely identified
each rows/records in a database table
...
 CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy

certain conditions
...
NOT
NULL Constraint: By default, a column can hold NULL values
...
A NULL is not the same as no data, rather, it represents unknown data
...
Example: For example, the following SQL creates a new table called CUSTOMERS and
adds five columns
...
00 by default, so in case INSERT INTO
statement does not provide a value for this column, then by default this column would be set to
5000
...
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT
NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2) DEFAULT 5000
...
00; Drop Default Constraint: To drop a DEFAULT constraint, use the
following SQL: ALTER TABLE CUSTOMERS ALTER COLUMN SALARY DROP DEFAULT; TUTORIALS POINT
Simply Easy Learning UNIQUE Constraint: The UNIQUE Constraint prevents two records from having
identical values in a particular column
...
Example: For example, the following SQL
creates a new table called CUSTOMERS and adds five columns
...

Primary keys must contain unique values
...
A table
can have only one primary key, which may consist of single or multiple fields
...
If a table has a primary key defined on
any field(s), then you can not have two records having the same value of that field(s)
...
Create Primary Key: Here is the syntax to
define ID attribute as a primary key in a CUSTOMERS table
...
For defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME) ); TUTORIALS POINT Simply
Easy Learning To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when
CUSTOMERS table already exists, use the following SQL syntax: ALTER TABLE CUSTOMERS ADD
CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME); Delete Primary Key: You can clear the primary
key constraints from the table, Use Syntax: ALTER TABLE CUSTOMERS DROP PRIMARY KEY ; FOREIGN
Key: A foreign key is a key used to link two tables together
...
Primary key field from one table and insert it into the other table where it becomes a foreign
key i
...
, Foreign Key is a column or a combination of columns, whose values match a P rimary Key in a
different table
...
If a table has a primary key defined on any field(s), then you can
not have two records having the same value of that field(s)
...
ALTER TABLE ORDERS ADD
FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID); DROP a FOREIGN KEY Constraint: To
drop a FOREIGN KEY constraint, use the following SQL: ALTER TABLE ORDERS DROP FOREIGN KEY;
CHECK Constraint: The CHECK Constraint enables a condition to check the value being entered into a
record
...
Example: For example, the following SQL creates a new table called CUSTOMERS and adds
five columns
...
This syntax does not work with MySQL: ALTER TABLE
CUSTOMERS DROP CONSTRAINT myCheckConstraint; INDEX: The INDEX is used to create and
retrieve data from the database very quickly
...
When index is created, it is assigned a ROWID for each row before it sorts out the
data
...
Selection of fields depends on what you are using in your SQL queries
...
); To create an INDEX on AGE column, to optimize the search on customers for
a particular age, following is the SQL syntax: CREATE INDEX idx_age ON CUSTOMERS ( AGE ); DROP
an INDEX Constraint: To drop an INDEX constraint, use the following SQL: TUTORIALS POINT Simply
Easy Learning ALTER TABLE CUSTOMERS DROP INDEX idx_age; Constraints can be specified when a
table is created with the CREATE TABLE statement or you can use ALTER TABLE statment to create
constraints even after the table is created
...
For

example, to drop the primary key constraint in the EMPLOYEES table, you can use the following
command: ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK; Some implementations
may provide shortcuts for dropping certain constraints
...
Instead of permanently
dropping a constraint from the database, you may want to temporarily disable the constraint, and
then enable it later
...
Data integrity is handled in a relational database
through the concept of referential integrity
...
These constraints include Primary Key, Foreign Key, Unique
Constraints and other constraints mentioned above
...
A JOIN
is a means for combining fields from two tables by using values common to each
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4
| Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP |
4500
...
00 | +----+----------+-----+-----------+----------+ (b) Another
table is ORDERS as follows: +-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID |
AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100
| 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20 00:00:00 | 2 | 1560 | | 103 | 2008-05-20
00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+ Now, let us join these two tables in
our SELECT statement as follows: SQL> SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS,
ORDERS WHERE CUSTOMERS
...
CUSTOMER_ID; This would produce the following result:
+----+----------+-----+--------+ | ID | NAME | AGE | AMOUNT | +----+----------+-----+--------+ | 3 | kaushik
| 23 | 3000 | | 3 | kaushik | 23 | 1500 | | 2 | Khilan | 25 | 1560 | CHAPTER 26 TUTORIALS POINT
Simply Easy Learning | 4 | Chaitali | 25 | 2060 | +----+----------+-----+--------+ Here, it is noticeable that
the join is performed in the WHERE clause
...
However, the most
common operator is the equal symbol
...
 LEFT JOIN: returns all rows
from the left table, even if there are no matches in the right table
...
 FULL JOIN: returns rows when
there is a match in one of the tables
...
 CARTESIAN JOIN: returns
the Cartesian product of the sets of records from the two or more joined tables
...
They are also referred to as an
EQUIJOIN
...
The query compares each row of table1 with each
row of table2 to find all pairs of rows which satisfy the join-predicate
...

Syntax: The basic syntax of INNER JOIN is as follows: SELECT table1
...
column2
...
common_filed = table2
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal
| 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows: +-----+---------------------+-------------+--------+ TUTORIALS POINT
Simply Easy Learning | OID | DATE | ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102
| 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20
00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+-------+ Now, let us join these two tables using INNER JOIN as follows: SQL> SELECT ID, NAME,
AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS
...
CUSTOMER_ID; This would produce the following result: +----+----------+--------+--------------------+ | ID | NAME | AMOUNT | DATE | +----+----------+--------+---------------------+ | 3 | kaushik | 3000 |
2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 2 | Khilan | 1560 | 2009-11-20
00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | +----+----------+--------+---------------------+ LEFT
JOIN The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right
table
...
This means that a left join returns
all the values from the left table, plus matched values from the right table or NULL in case of no
matching join predicate
...
column1,
table2
...
FROM table1 LEFT JOIN table2 ON table1
...
common_field;
Here given condition could be any given expression based on your requirement
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4
| Chaitali | 25 | Mumbai | 6500
...
00 | TUTORIALS POINT Simply
Easy Learning | 6 | Komal | 22 | MP | 4500
...
00 | +----+---------+-----+-----------+----------+ (b) Another table is ORDERS as follows: +-----+---------------------+-------------+-------+ | OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 |
2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20
00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+-------+ Now, let us join these two tables using LEFT JOIN as follows: SQL> SELECT ID, NAME, AMOUNT,
DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS
...
CUSTOMER_ID; This
would produce the following result: +----+----------+--------+---------------------+ | ID | NAME | AMOUNT
| DATE | +----+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 1560 |
2009-11-20 00:00:00 | | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-1008 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | | 5 | Hardik | NULL | NULL | | 6 | Komal
| NULL | NULL | | 7 | Muffy | NULL | NULL | +----+----------+--------+---------------------+ RIGHT JOIN The
SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table
...
This means that a right join returns all
the values from the right table, plus matched values from the left table or NULL in case of no
matching join predicate
...
column1, table2
...
FROM table1 RIGHT JOIN table2 ON table1
...
common_field; Example: Consider the following two tables, (a) CUSTOMERS table is as
follows: TUTORIALS POINT Simply Easy Learning +----+----------+-----+-----------+----------+ | ID | NAME |
AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad |
2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 |
Muffy | 24 | Indore | 10000
...
ID = ORDERS
...

The joined table will contain all records from both tables, and fill in NULLs f or missing matches on
either side
...
column1,
table2
...
FROM table1 FULL JOIN table2 ON table1
...
common_field;
Here given condition could be any given expression based on your requirement
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+---------+-----+-----------+----------+ (b) Another table is ORDERS as follows: +-----+---------------------+------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ |
102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20
00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+-------+ Now, let us join these two tables using FULL JOIN as follows: SQL> SELECT ID, NAME, AMOUNT,
DATE FROM CUSTOMERS FULL JOIN ORDERS ON CUSTOMERS
...
CUSTOMER_ID; This
would produce the following result: +------+----------+--------+---------------------+ | ID | NAME |
AMOUNT | DATE | +------+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 |
Khilan | 1560 | 2009-11-20 00:00:00 | | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik |
1500 | 2009-10-08 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | | 5 | Hardik | NULL |
NULL | | 6 | Komal | NULL | NULL | | 7 | Muffy | NULL | NULL | | 3 | kaushik | 3000 | 2009-10-08
00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | +------+----------+--------+---------------------+ If your
Database does not support FULL JOIN like MySQL does not support FULL JOIN, then you can use
UNION ALL clause to combine two JOINS as follows: SQL> SELECT ID, NAME, AMOUNT, DATE FROM
CUSTOMERS LEFT JOIN ORDERS TUTORIALS POINT Simply Easy Learning ON CUSTOMERS
...
CUSTOMER_ID UNION ALL SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT
JOIN ORDERS ON CUSTOMERS
...
CUSTOMER_ID SELF JOIN The SQL SELF JOIN is used to
join a table to itself as if the table were two tables, temporarily renaming at least one table in the
SQL statement
...
column_name,
b
...
FROM table1 a, table1 b WHERE a
...
common_field; Here,
WHERE clause could be any given expression based on your requirement
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4
| Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP |
4500
...
00 | +----+----------+-----+-----------+----------+ Now, let us
join this table using SELF JOIN as follows: SQL> SELECT a
...
NAME, a
...
SALARY < b
...
00 | | 2 | kaushik |
1500
...
00 | | 2 | Chaitali | 1500
...
00 | | 6 | Chaitali |
4500
...
00 | | 2 | Hardik | 1500
...
00 | | 4 | Hardik |

6500
...
00 | | 1 | Komal | 2000
...
00 | | 3 | Komal | 2000
...
00 | | 2 | Muffy | 1500
...
00 | | 4 | Muffy | 6500
...
00 | | 6 | Muffy | 4500
...
Thus, it equates to an inner join where the
join-condition always evaluates to True or where the joincondition is absent from the statement
...
column1, table2
...

FROM table1, table2 [, table3 ] Example: Consider the following two tables, (a) CUSTOMERS table is
as follows: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik
| 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
To use UNION, each SELECT must have the same
number of columns selected, the same number of column expressions, the same data type, and have
them in the same order, but they do not have to be the same length
...
Example: Consider the following two tables, (a)
CUSTOMERS table is as follows: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS
| SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 |
Indore | 10000
...
ID = ORDERS
...
ID =
ORDERS
...
The same rules that apply to UNION apply to the
UNION ALL operator
...
TUTORIALS POINT Simply Easy Learning Example: Consider the following
two tables, (a) CUSTOMERS table is as follows: +----+----------+-----+-----------+----------+ | ID | NAME |
AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad |
2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 |
Muffy | 24 | Indore | 10000
...
ID = ORDERS
...
ID = ORDERS
...
e
...
 SQL EXCEPT
Clause : combines two SELECT statements and returns rows from the first SELECT statement that are
not returned by the second SELECT statement
...
This means INTERSECT
returns only common rows returned by the two SELECT statements
...
MySQL does not support
INTERSECT operator Syntax: The basic syntax of INTERSECT is as follows: SELECT column1 [, column2
] FROM table1 [, table2 ] [WHERE condition] INTERSECT SELECT column1 [, column2 ] FROM table1 [,
table2 ] [WHERE condition] Here given condition could be any given expression based on your
requirement
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+----+-----------+----------+ (b) Another table is ORDERS as follows: +-----+---------------------+-------------+-------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 200910-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20 00:00:00 |
2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+ Now,
let us join these two tables in our SELECT statement as follows: TUTORIALS POINT Simply Easy
Learning SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON
CUSTOMERS
...
CUSTOMER_ID INTERSECT SELECT ID, NAME, AMOUNT, DATE FROM
CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS
...
CUSTOMER_ID; This would produce
the following result: +------+---------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +-----+---------+--------+---------------------+ | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500
| 2009-10-08 00:00:00 | | 2 | Ramesh | 1560 | 2009-11-20 00:00:00 | | 4 | kaushik | 2060 | 200805-20 00:00:00 | +------+---------+--------+---------------------+ EXCEPT Clause The SQL EXCEPT
clause/operator is used to combine two SELECT statements and returns rows from the first SELECT
statement that are not returned by the second SELECT statement
...
Just as with the UNION operator, the
same rules apply when using the EXCEPT operator
...

Syntax: The basic syntax of EXCEPT is as follows: SELECT column1 [, column2 ] FROM table1 [, table2
] [WHERE condition] EXCEPT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE
condition] Here given condition could be any given expression based on your requirement
...
00 | | 2 | Khilan | 25 | Delhi |
1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 |
+----+----------+-----+-----------+----------+ (b) Another table is ORDERS as follows: +-----+--------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+-------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 |
2009-11-20 00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+------------+--------+ Now, let us join these two tables in our SELECT statement as follows: SQL> SELECT
ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS
...
CUSTOMER_ID EXCEPT SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN
ORDERS ON CUSTOMERS
...
CUSTOMER_ID; This would produce the following result:
TUTORIALS POINT Simply Easy Learning +----+---------+--------+---------------------+ | ID | NAME |
AMOUNT | DATE | +----+---------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 5 | Hardik
| NULL | NULL | | 6 | Komal | NULL | NULL | | 7 | Muffy | NULL | NULL | +----+---------+--------+--------------------+ TUTORIALS POINT Simply Easy Learning SQL NULL Values The SQL NULL is the term used
to represent a missing value
...
A
field with a NULL value is a field with no value
...
Syntax: The basic syntax of NULL while
creating a table: SQL> CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT
NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here,
NOT NULL signifies that column should always accept an explicit value of the given data type
...
A
field with a NULL value is one that has been left blank during record creation
...
You must
use the IS NULL or IS NOT NULL operators in order to check for a NULL value
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali |
25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | | | 7 | Muffy | 24 | Indore | | +----+----------+-----+----------+----------+ Now, following is the usage of IS NOT NULL operator: SQL> SELECT ID, NAME, AGE,
ADDRESS, SALARY FROM CUSTOMERS WHERE SALARY IS NOT NULL; This would produce the
following result: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +---+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 |
Hardik | 27 | Bhopal | 8500
...
The use of table aliases means to rename a table in a particular SQL statement
...
The
column aliases are used to rename a table's columns for the purpose of a particular SQL query
...
FROM table_name
AS alias_name WHERE [condition]; The basic syntax of column alias is as follows: SELECT
column_name AS alias_name FROM table_name WHERE [condition]; Example: Consider the
following two tables, (a) CUSTOMERS table is as follows: +----+----------+-----+-----------+----------+ | ID |
NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 |
Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
ID, C
...
AGE,
O
...
ID = O
...
Simply put, an index is a pointer to data in a table
...
For example, if you want to reference all
pages in a book that discuss a certain topic, you first refer to the index, which lists all topics
alphabetically and are then referred to one or more specific page numbers
...
Indexes can be created or dropped with no effect on the data
...
Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents
duplicate entries in the column or combination of columns on which there's an index
...
The basic syntax is as follows: CREATE INDEX index_name ON table_name
(column_name); Unique Indexes: Unique indexes are used not only for performance, but also for
data integrity
...
The
basic syntax is as follows: CREATE INDEX index_name on table_name (column_name); CHAPTER 30
TUTORIALS POINT Simply Easy Learning Composite Indexes: A composite index is an index on two or
more columns of a table
...
Should there be only one column used, a single-column index should be the choice
...
Implicit Indexes: Implicit indexes are indexes that are
automatically created by the database server when an object is created
...
The DROP INDEX Command: An index
can be dropped using SQL DROP command
...
The basic syntax is as follows: DROP INDEX index_name;
You can check INDEX Constraint chapter to see actual examples on Indexes
...
The following guidelines indicate when the use of an index should be
reconsidered:  Indexes should not be used on small tables
...
 Indexes should not be used on columns that contain a high number of
NULL values
...
TUTORIALS POINT
Simply Easy Learning SQL ALTER TABLE Command The SQL ALTER TABLE command is used to add,
delete or modify columns in an existing table
...
Syntax: The basic syntax of ALTER TABLE to add a new
column in an existing table is as follows: ALTER TABLE table_name ADD column_name datatype; The
basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows: ALTER TABLE
table_name DROP COLUMN column_name; The basic syntax of ALTER TABLE to change the DATA
TYPE of a column in a table is as follows: ALTER TABLE table_name MODIFY COLUMN column_name
datatype; The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as
follows: ALTER TABLE table_name MODIFY column_name datatype NOT NULL; The basic syntax of
ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows: ALTER TABLE table_name ADD
CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2
...
); The basic syntax of ALTER TABLE to DROP CONSTRAINT from a
table is as follows: CHAPTER 31 TUTORIALS POINT Simply Easy Learning ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint; If you're using MySQL, the code is as f ollows: ALTER TABLE
table_name DROP INDEX MyUniqueConstraint; The basic syntax of ALTER TABLE to DROP PRIMARY
KEY constraint from a table is as follows: ALTER TABLE table_name DROP CONSTRAINT
MyPrimaryKey; If you're using MySQL, the code is as follows: ALTER TABLE table_name DROP

PRIMARY KEY; Example: Consider the CUSTOMERS table having the following records: +----+---------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik |
23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
00 | NULL | | 2 | Ramesh | 25 | Delhi | 1500
...
00 |
NULL | | 4 | kaushik | 25 | Mumbai | 6500
...
00 | NULL |
| 6 | Komal | 22 | MP | 4500
...
00 | NULL | +----+--------+-----+-----------+----------+------+ Following is the example to DROP sex column from existing table:
ALTER TABLE CUSTOMERS DROP SEX; Now, CUSTOMERS table is changed and following would be
output from SELECT statement: TUTORIALS POINT Simply Easy Learning +----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+----------+ | 1 |
Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 |
Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
You can also use DROP TABLE
command to delete complete table but it would remove complete table structure form the database
and you would need to re-create this table once again if you wish you store some data
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25
| Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+ Following is the example to
truncate: SQL > TRUNCATE TABLE CUSTOMERS; Now, CUSTOMERS table is truncated and following
would be the output from SELECT statement: SQL> SELECT * FROM CUSTOMERS; Empty set (0
...
A view is actually a
composition of a table in the form of a predefined SQL query
...
A view can be created from one or many tables which depends on the
written SQL query to create a view
...
 Restrict
access to the data such that a user can see and (sometimes) modify exactly what they need and no
more
...
Creating Views:
Database views are created using the CREATE VIEW statement
...
To create a view, a user must have the appropriate system
privilege according to the specific implementation
...
FROM table_name WHERE [condition];
You can include multiple tables in your SELECT statement in very similar way as you use them in
normal SQL SELECT query
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4

| Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP |
4500
...
00 | +----+----------+-----+-----------+----------+ Now,
following is the example to create a view from CUSTOMERS table
...
Following is the example: SQL > SELECT * FROM CUSTOMERS_VIEW; This would
produce the following result: +----------+-----+ | name | age | +----------+-----+ | Ramesh | 32 | | Khilan
| 25 | | kaushik | 23 | | Chaitali | 25 | | Hardik | 27 | | Komal | 22 | | Muffy | 24 | +----------+-----+
The WITH CHECK OPTION: The WITH CHECK OPTION is a CREATE VIEW statement option
...
If they do not satisfy the condition(s), the UPDATE or INSERT
returns an error
...
Updating a View: A view can be updated under
certain conditions: TUTORIALS POINT Simply Easy Learning  The SELECT clause may not contain the
keyword DISTINCT
...
 The SELECT clause
may not contain set functions
...
 The SELECT
clause may not contain an ORDER BY clause
...

The WHERE clause may not contain subqueries
...

 Calculated columns may not be updated
...
So if a view satisfies all the
abovementioned rules then you can update a view
...
Now, try to
query base table, and SELECT statement would produce the following result: +----+----------+-----+----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 |
Ramesh | 35 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota
| 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
The same rules that apply
to the UPDATE command also apply to the INSERT command
...
TUTORIALS POINT Simply
Easy Learning Deleting Rows into a View: Rows of data can be deleted from a view
...
Following is an
example to delete a record having AGE= 22
...
Now, try to query base table, and SELECT statement would produce the following
result: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+----------+ | 1 | Ramesh | 35 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik
| 27 | Bhopal | 8500
...
00 | +----+----------+-----+-----------+---------+ Dropping Views: Obviously, where you have a view, you need a way to drop the view if it is no
longer needed
...
The WHERE clause places
conditions on the selected columns, whereas the HAVING clause places conditions on groups
created by the GROUP BY clause
...
The following is the
syntax of the SELECT statement, including the HAVING clause: SELECT column1, column2 FROM
table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY
column1, column2 Example: Consider the CUSTOMERS table having the following records: +----+---------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 |
kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal |
8500
...
00 | | 7
| Muffy | 24 | Indore | 10000
...
00 | +----+--------+-----+---------+---------+
TUTORIALS POINT Simply Easy Learning SQL Transactions Atransaction is a unit of work that is
performed against a database
...

A transaction is the propagation of one or more changes to the database
...
It is important to control transactions to ensure data integrity and to
handle database errors
...
Properties of Transactions: Transactions have the
following four standard properties, usually referred to by the acronym ACID:  Atomicity: ensures
that all operations within the work unit are completed successfully; otherwise, the transaction is
aborted at the point of failure, and previous operations are rolled back to their former state
...
 Isolation: enables transactions to operate independently of and transparent to each
other
...
Transaction Control: There are following commands used to control transactions: 
COMMIT: to save the changes
...
 SAVEPOINT: creates points
within groups of transactions in which to ROLLBACK  SET TRANSACTION: Places a name on a
transaction
...
They can not be used while creating tables or dropping them because
these operations are automatically committed in the database
...
CHAPTER 35 TUTORIALS POINT Simply Easy Learning The COMMIT command saves all
transactions to the database since the last COMMIT or ROLLBACK command
...
00 | | 2 | Khilan | 25 | Delhi |
1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 |
+----+----------+-----+-----------+----------+ Following is the example, which would delete records from the

table having age = 25 and then COMMIT the changes in the database
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+----+-----------+----------+ The ROLLBACK Command: The ROLLBACK command is the transactional
command used to undo transactions that have not already been saved to the database
...
The syntax for ROLLBACK command is as follows: ROLLBACK; Example:
Consider the CUSTOMERS table having the following records: TUTORIALS POINT Simply Easy
Learning +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik
| 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
SQL> DELETE FROM
CUSTOMERS WHERE AGE = 25; SQL> ROLLBACK; As a result, delete operation would not impact the
table and SELECT statement would produce the following result: +----+----------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32
| Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
The syntax for SAVEPOINT
command is as follows: SAVEPOINT SAVEPOINT_NAME; This command serves only in the creation of
a SAVEPOINT among transactional statements
...
The syntax for rolling back to a SAVEPOINT is as follows: ROLLBACK TO
SAVEPOINT_NAME; Following is an example where you plan to delete the three different records
from the CUSTOMERS table
...
00 | | 2 | Khilan | 25 | Delhi
| 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+ Now, here is the series of operations: SQL>
SAVEPOINT SP1; Savepoint created
...

SQL> SAVEPOINT SP2; Savepoint created
...
SQL> SAVEPOINT SP3; Savepoint created
...
Now that the three deletions have taken place, say you have changed your mind and
decided to ROLLBACK to the SAVEPOINT that you identified as SP2
...

Notice that only the first deletion took place since you rolled back to SP2: SQL> SELECT * FROM
CUSTOMERS; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+----------+ | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal
| 22 | MP | 4500
...
00 | +----+----------+-----+-----------+----------+ 6

rows selected
...
The syntax for RELEASE SAVEPOINT is as follows:
RELEASE SAVEPOINT SAVEPOINT_NAME; TUTORIALS POINT Simply Easy Learning Once a SAVEPOINT
has been released, you can no longer use the ROLLBACK command to undo transactions performed
since the SAVEPOINT
...
This command is used to specify characteristics for the
transaction that follows
...

The syntax for SET TRANSACTION is as follows: SET TRANSACTION [ READ WRITE | READ ON LY ];
TUTORIALS POINT Simply Easy Learning SQL Wildcard Operators We already have discussed SQL LIKE
operator, which is used to compare a value to similar values using wildcard operators
...
Note that MS Access uses the asterisk (*) wildcard
character instead of the percent sign (%) wildcard character
...
Note that MS Access uses a question mark (?) instead of the underscore (_) to match any
one character
...
The underscore
represents a single number or character
...
Sy ntax: The
basic syntax of ‘%’ and ‘_’ is as follows: SELECT FROM table_name WHERE column LIKE 'XXXX%' or
SELECT FROM table_name WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE
column LIKE 'XXXX_' or SELECT FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM
table_name CHAPTER 36 TUTORIALS POINT Simply Easy Learning WHERE column LIKE '_XXXX_' You
can combine N number of conditions using AND or OR operators
...
Example: Here are number of examples showing WHERE part having different LIKE
clause with '%' and '_' operators: Statement Description WHERE SALARY LIKE '200%' Finds any values
that start with 200 WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position
WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions WHERE
SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length WHERE
SALARY LIKE '%2' Finds any values that end with 2 WHERE SALARY LIKE '_2%3' Finds any values that
have a 2 in the second position and end with a 3 WHERE SALARY LIKE '2___3' Finds any values in a
five-digit number that start with 2 and end with 3 Let us take a real example, consider the
CUSTOMERS table having the following records: +----+----------+-----+-----------+----------+ | ID | NAME |
AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad |
2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 |
Muffy | 24 | Indore | 10000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
There are
various other functions supported by your RDBMS
...
Name
Description ADDDATE() Adds dates ADDTIME() Adds time CONVERT_TZ() Converts from one
timezone to another CURDATE() Returns the current date CURRENT_DATE(), CURRENT_DATE
Synonyms for CURDATE() CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW() CURTIME() Returns the
current time DATE_ADD() Adds two dates DATE_FORMAT() Formats date as specified DATE_SUB()
Subtracts two dates DATE() Extracts the date part of a date or datetime expression DATEDIFF()
Subtracts two dates DAY() Synonym for DAYOFMONTH() DAYNAME() Returns the name of the

weekday DAYOFMONTH() Returns the day of the month (1-31) DAYOFWEEK() Returns the weekday
index of the argument CHAPTER 37 TUTORIALS POINT Simply Easy Learning DAYOFYEAR() Returns
the day of the year (1-366) EXTRACT Extracts part of a date FROM_DAYS() Converts a day number to
a date FROM_UNIXTIME() Formats date as a UNIX timestamp HOUR() Extracts the hour LAST_DAY
Returns the last day of the month for the argument LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW() MAKEDATE() Creates a date from the
year and day of year MAKETIME MAKETIME() MICROSECOND() Returns the microseconds from
argument MINUTE() Returns the minute from the argument MONTH() Returns the month from the
date passed MONTHNAME() Returns the name of the month NOW() Returns the current date and
time PERIOD_ADD() Adds a period to a year-month PERIOD_DIFF() Returns the number of months
between periods QUARTER() Returns the quarter from a date argument SEC_TO_TIME() Converts
seconds to 'HH:MM:SS' format SECOND() Returns the second (0-59) STR_TO_DATE() Converts a
string to a date SUBDATE() When invoked with three arguments a synonym for DATE_SUB()
SUBTIME() Subtracts times SYSDATE() Returns the time at which the function executes
TIME_FORMAT() Formats as time TIME_TO_SEC() Returns the argument converte d to seconds
TIME() Extracts the time portion of the expression passed TIMEDIFF() Subtracts time TIMESTAMP()
With a single argument, this function returns the date or datetime expression
...
The related function SUBDATE() is a synonym for
DATE_SUB()
...

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY); +--------------------------------------------------------+ | DATE_ADD('1998-01-02', INTERVAL 31 DAY) | +---------------------------------------------------------+ |
1998-02-02 | +---------------------------------------------------------+ 1 row in set (0
...
00 sec) When invoked with the
days form of the second argument, MySQL treats it as an integer number of days to be added to
expr
...
00 sec) TUTORIALS POINT
Simply Easy Learning ADDTIME(expr1,expr2) ADDTIME() adds expr2 to expr1 and returns the result
...
mysql> SELECT
ADDTIME('1997-12-31 23:59:59
...
000002'); +--------------------------------------------------------+ | DATE_ADD('1997-12-31 23:59:59
...
000002') | +--------------------------------------------------------+ | 1998-01-02 01:01:01
...
00 sec) CONVERT_TZ(dt,from_tz,to_tz) This converts a datetime value dt from the time zone
given by from_tz to the time zone given by to_tz and returns the resulting value
...
mysql> SELECT CONVERT_TZ('2004-01-01
12:00:00','GMT','MET'); +---------------------------------------------------------+ | CONVERT_TZ('2004-01-01
12:00:00','GMT','MET') | +---------------------------------------------------------+ | 2004-01-01 13:00:00 | +--------------------------------------------------------+ 1 row in set (0
...
00 sec)
CURDATE() Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending
on whether the function is used in a string or numeric context
...
00 sec) mysql> SELECT
CURDATE() + 0; +---------------------------------------------------------+ | CURDATE() + 0 | +--------------------------------------------------------+ | 19971215 | +---------------------------------------------------------+ 1 row in set
(0
...
The value is expressed in the current time zone
...
00 sec)
mysql> SELECT CURTIME() + 0; +---------------------------------------------------------+ | CURTIME() + 0 | +--------------------------------------------------------+ | 235026 | +---------------------------------------------------------+ 1
row in set (0
...
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW()
...
mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+ | DATE('2003-12-31 01:02:03') | +--------------------------------------------------------+ | 2003-12-31 | +---------------------------------------------------------+ 1 row in set
(0
...
expr2 expressed as a value in days from
one date to the other
...
Only the date parts of
the values are used in the calculation
...
00 sec) DATE_ADD(date,INTERVAL expr unit),
DATE_SUB(date,INTERVAL expr unit) These functions perform date arithmetic
...
expr is an expression specifying the interval value to be
added or subtracted from the starting date
...
unit is a keyword indicating the units in which the expression should be interpreted
...
The following table shows the
expected form of the expr argument for each unit value; unit Value ExpectedexprFormat
MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS
WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND
'SECONDS
...
MICROSECONDS' MINUTE_SECOND
'MINUTES:SECONDS' HOUR_MICROSECOND 'HOURS
...
MICROSECONDS' DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS
HOURS:MINUTES' DAY_HOUR 'DAYS HOURS' TUTORIALS POINT Simply Easy Learning YEAR_MONTH
'YEARS-MONTHS' The values QUARTER and WEEK are available beginning with MySQL 5
...
0
...
| +--------------------------------------------------------+ | 1998-01-01 00:01:00 | +---------------------------------------------------------+ 1 row in
set (0
...
00 sec) DATE_FORMAT(date,format) Formats the date value according to the format string
...
The ‘%’ character is required before format
specifier characters
...
Sat) %b Abbreviated
month name (Jan
...
12) %D Day of the month with English suffix (0th, 1st,
2nd, 3rd,
...
31) %e Day of the month, numeric (0
...
999999) %H Hour (00
...
12) %I Hour (01
...
59) %j Day of year (001
...
23) %l Hour (1
...
December) %m Month, numeric (00
...
59) %s Seconds (00
...
53), where Sunday is the first day of the week %u Week (00
...
53), where Sunday is the first day of the
week; used with %X %v Week (01
...
Saturday) %w Day of the week (0=Sunday
...
character %x x, for any
...
not listed
above mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); +--------------------------------------------------------+ | DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') | +--------------------------------------------------------+ | Saturday October 1997 | +---------------------------------------------------------+ 1
row in set (0
...
| +--------------------------------------------------------+ | 22 22 10 10:23:00 PM 22:23:00 00 6 | +--------------------------------------------------------+ TUTORIALS POINT Simply Easy Learning 1 row in set (0
...
DAY(date) DAY() is a
synonym for DAYOFMONTH()
...
mysql>
SELECT DAYNAME('1998-02-05'); +---------------------------------------------------------+ | DAYNAME('199802-05') | +---------------------------------------------------------+ | Thursday | +--------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT DAYOFMONTH('1998-02-03'); +--------------------------------------------------------+ | DAYOFMONTH('1998-02-03') | +---------------------------------------------------------+ | 3 | +--------------------------------------------------------+ 1 row in set (0
...
, 7 = Saturday)
...
mysql> SELECT DAYOFWEEK('1998-02-03'); +--------------------------------------------------------+ |DAYOFWEEK('1998-02-03') | +---------------------------------------------------------+ | 3 | +--------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT DAYOFYEAR('1998-02-03'); +--------------------------------------------------------+ TUTORIALS POINT Simply Easy Learning | DAYOFYEAR('1998-02-03') | +--------------------------------------------------------+ | 34 | +---------------------------------------------------------+ 1 row in
set (0
...
mysql> SELECT EXTRACT(YEAR FROM '1999-07-02'); +--------------------------------------------------------+ | EXTRACT(YEAR FROM '1999-07-02') | +---------------------------------------------------------+ |
1999 | +---------------------------------------------------------+ 1 row in set (0
...
00 sec) FROM_DAYS(N)
Given a day number N, returns a DATE value
...
00 sec) Use
FROM_DAYS() with caution on old dates
...
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format) Returns a representation of the unix_timestamp
argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on
whether the function is used in a string or numeric context
...
unix_timestamp is an internal timestamp value such as is produced by the
UNIX_TIMESTAMP() function
...
mysql>
SELECT FROM_UNIXTIME(875996580); TUTORIALS POINT Simply Easy Learning +--------------------------------------------------------+ | FROM_UNIXTIME(875996580) | +--------------------------------------------------------+ | 1997-10-04 22:23:00 | +---------------------------------------------------------+ 1 row in set (0
...
The range of the return value is 0 to 23 for time-of-day
values
...
mysql> SELECT HOUR('10:05:03'); +---------------------------------------------------------+ |
HOUR('10:05:03') | +---------------------------------------------------------+ | 10 | +--------------------------------------------------------+ 1 row in set (0
...
Returns NULL if the argument is invalid
...
00 sec) LOCALTIME and LOCALTIME() LOCALTIME
and LOCALTIME() are synonyms for NOW()
...
MAKEDATE(year,dayofyear)
Returns a date, given year and day-of-year values
...
mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32); +--------------------------------------------------------+ | MAKEDATE(2001,31), MAKEDATE(2001,32) | +--------------------------------------------------------+ | '2001-01-31', '2001-02-01' | +---------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT MAKETIME(12,15,30); +--------------------------------------------------------+ | MAKETIME(12,15,30) | +--------------------------------------------------------+ | '12:15:30' | +---------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT MICROSECOND('12:00:00
...
123456') | +--------------------------------------------------------+ | 123456 | +---------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT MINUTE('98-02-03
10:05:03'); +---------------------------------------------------------+ | MINUTE('98-02-03 10:05:03') | +--------------------------------------------------------+ | 5 | +---------------------------------------------------------+ 1 row in set
(0
...
mysql> SELECT
MONTH('1998-02-03') +---------------------------------------------------------+ | MONTH('1998-02-03') | +--------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in
set (0
...
mysql> SELECT
MONTHNAME('1998-02-05'); +---------------------------------------------------------+ TUTORIALS POINT Simply
Easy Learning | MONTHNAME('1998-02-05') | +---------------------------------------------------------+ |
February | +---------------------------------------------------------+ 1 row in set (0
...
The value is expressed in

the current time zone
...
00 sec) PERIOD_ADD(P,N) Adds N months to period P (in the format
YYMM or YYYYMM)
...
Note that the period argument P is not
a date value
...
00 sec) PERIOD_DIFF(P1,P2) Returns the numbe r of months
between periods P1 and P2
...
Note that the
period arguments P1 and P2 are not date values
...
00 sec)
QUARTER(date) Returns the quarter of the year for date, in the range 1 to 4
...
00 sec) SECOND(time) Returns the second for time, in
the range 0 to 59
...
00 sec) SEC_TO_TIME(seconds) Returns the seconds argument,
converted to hours, minutes and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending
on whether the function is used in a string or numeric context
...
00 sec)
STR_TO_DATE(str,format) This is the inverse of the DATE_FORMAT() function
...
STR_TO_DATE() returns a DATETIME value if the format string contains
both date and time parts or a DATE or TIME value if the string contains only date or time parts
...
00 sec)
SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days) When invoked with the INTERVAL form
of the second argument, SUBDATE() is a synonym for DATE_SUB()
...
mysql> SELECT DATE_SUB('1998-01-02',
INTERVAL 31 DAY); +---------------------------------------------------------+ | DATE_SUB('1998-01-02',
INTERVAL 31 DAY) | +---------------------------------------------------------+ | 1997-12-02 | +--------------------------------------------------------+ TUTORIALS POINT Simply Easy Learning 1 row in set (0
...
00 sec) SUBTIME(expr1,expr2)
SUBTIME() returns expr1
...
expr1 is a time or
datetime expression, and expr2 is a time
...
999999', ->
'1 1:1:1
...
999999'
...
999997 |
+---------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT SYSDATE(); +--------------------------------------------------------+ | SYSDATE() | +---------------------------------------------------------+ | 200604-12 13:47:44 | +---------------------------------------------------------+ 1 row in set (0
...
mysql>
SELECT TIME('2003-12-31 01:02:03'); +---------------------------------------------------------+ | TIME('2003-12-

31 01:02:03') | +---------------------------------------------------------+ | 01:02:03 | +--------------------------------------------------------+ 1 row in set (0
...
expr2
expressed as a time value
...
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59
...
000002'); TUTORIALS POINT Simply Easy Learning +--------------------------------------------------------+ | TIMEDIFF('1997-12-31 23:59:59
...
| +---------------------------------------------------------+ |
46:58:57
...
00 sec)
TIMESTAMP(expr), TIMESTAMP(expr1,expr2) With a single argument, this function returns the date
or datetime expression expr as a datetime value
...
mysql>
SELECT TIMESTAMP('2003-12-31'); +---------------------------------------------------------+ |
TIMESTAMP('2003-12-31') | +---------------------------------------------------------+ | 2003-12-31 00:00:00 |
+---------------------------------------------------------+ 1 row in set (0
...
The unit for interval is given by the unit argument, which should
be one of the following values: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH,
QUARTER or YEAR
...
For example, DAY and SQL_TSI_DAY both are legal
...
00 sec)
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) Returns the integer difference between the
date or datetime expressions datetime_expr1 and datetime_expr2
...
The legal values for unit are the same as those listed in the description of the
TIMESTAMPADD() function
...
00 sec) TUTORIALS POINT Simply Easy Learning TIME_FORMAT(time,format) This is used like
the DATE_FORMAT() function, but the format string may contain format specifiers only for hours,
minutes and seconds
...
23
...
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h
%I %l'); +---------------------------------------------------------+ | TIME_FORMAT('100:00:00', '%H %k %h %I
%l') | +---------------------------------------------------------+ | 100 100 04 04 4 | +--------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT TIME_TO_SEC('22:23:00'); +---------------------------------------------------------+ |
TIME_TO_SEC('22:23:00') | +---------------------------------------------------------+ | 80580 | +--------------------------------------------------------+ 1 row in set (0
...
mysql> SELECT TO_DAYS(950501); +--------------------------------------------------------+ | TO_DAYS(950501) | +---------------------------------------------------------+ |
728779 | +---------------------------------------------------------+ 1 row in set (0
...
If UNIX_TIMESTAMP() is called with a date argument, it
returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC
...

mysql> SELECT UNIX_TIMESTAMP(); +---------------------------------------------------------+ |
UNIX_TIMESTAMP() | +---------------------------------------------------------+ | 882226357 | +--------------------------------------------------------+ 1 row in set (0
...
00 sec) UTC_DATE,
UTC_DATE() Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format,
depending on whether the function is used in a string or numeric context
...
00 sec) UTC_TIME, UTC_TIME() Returns the
current UTC time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function
is used in a string or numeric context
...
00 sec)
UTC_TIMESTAMP, UTC_TIMESTAMP() Returns the current UTC date and time as a value in 'YYYYMM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a
string or numeric context
...
00 sec) WEEK(date[,mode]) This function returns the week number for
date
...
If the
mode argument is omitted, the value of the default_week_format system variable is used Mode
First Day of week Range Week 1 is the first week
...
00 sec) WEEKDAY(date)
Returns the weekday index for date (0 = Monday, 1 = Tuesday,
...
mysql> SELECT
WEEKDAY('1998-02-03 22:23:00'); +---------------------------------------------------------+ | WEEKDAY('199802-03 22:23:00') | +---------------------------------------------------------+ | 1 | +--------------------------------------------------------+ 1 row in set (0
...
WEEKOFYEAR() is a compatibility functi on that is equivalent to
WEEK(date,3)
...
00 sec) YEAR(date) Returns the year for date, in the
range 1000 to 9999, or 0 for the
...
date
...
00 sec) YEARWEEK(date), YEARWEEK(date,mode) Returns year and week for a date
...
The year in the result may be different
from the year in the date argument for the first and the last week of the year
...
00 sec) Note that the week number is different
from what the WEEK() function would return (0) for optional arguments 0 or 1, as WEEK() then
returns the week in the context of the given year
...
Temporary Tables are a great feature that lets
you store and process intermediate results by using the same selection, update, and join capabilities
that you can use with typical SQL Server tables
...
The most important thing that should be known for temporary tables
is that they will be deleted when the current client session terminates
...
23 onwards
...
23, you can't
use temporary tables, but you can use heap tables
...
If you run the code in a PHP script, the temporary table will be
destroyed automatically when the script finishes executing
...
Example: Here is an example showing you usage of
temporary table: mysql> CREATE TEMPORARY TABLE SALESSUMMARY ( -> product_name
VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0
...
00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 );
Query OK, 0 rows affected (0
...
25, 90, 2); mysql>
SELECT * FROM SALESSUMMARY; +--------------+-------------+----------------+------------------+ |
product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+---------------+------------------+ | cucumber | 100
...
00 | 2 | +--------------+-------------+----------------+-----------------+ 1 row in set (0
...
Now if you
will log out of the MySQL session and then you will issue a SELECT command, then you will find no
data available in the database
...
Dropping Temporary
Tables: By default, all the temporary tables are deleted by MySQL when your database connection
gets terminated
...
Following is the example on dropping a temproary table
...
00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0
...
00 se c) mysql>
INSERT INTO SALESSUMMARY -> (product_name, total_sales, avg_unit_price, total_units_sold) ->
VALUES -> ('cucumber', 100
...
25 | 90
...
00 sec) mysql> DROP TABLE SALESSUMMARY;
mysql> SELECT * FROM SALESSUMMARY; ERROR 1146: Table 'TUTORIALS
...
SELECT
...
If you are using MySQL RDBMS,
you can handle this situation by the following steps:  Use SHOW CREATE TABLE command to get a
CREATE TABLE statement that specifies the source table's structure, indexes and all
...
This way
you will have exact clone table
...
SELECT statement, too
...

SQL> SHOW CREATE TABLE TUTORIALS_TBL \G; *************************** 1
...
00 sec) Step 2: Rename this table and create another table
...
80 sec) Step 3: After executing step 2, you will clone a table in your database
...
SELECT statement
...
07 sec) Records: 3 Duplicates: 0 Warnings: 0 Finally, you will have exact clone table
as you wanted to have
...

A subquery is used to return data that will be used in the main query as a condition to further
restrict the data to be retrieved
...
There are a few rules
that subqueries must follow:  Subqueries must be enclosed within parentheses
...
 An ORDER BY cannot be used in a subquery, although
the main query can use an ORDER BY
...
 Subqueries that return more than one row can only be used with
multiple value operators, such as the IN operator
...
 A subquery cannot be immediately
enclosed in a set function
...
Subqueries with the SELECT Statement:
Subqueries are most frequently used with the SELECT statement
...
00 | | 2 | Khilan | 25 | Delhi | 1500
...
00 | | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 6 | Komal | 22 | MP | 4500
...
00 | +----+----------+----+-----------+----------+ Now, let us check the following subquery with SELECT statement: SQL> SELECT
* FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500) ; This
would produce the following result: +----+----------+-----+---------+----------+ | ID | NAME | AGE |
ADDRESS | SALARY | +----+----------+-----+---------+----------+ | 4 | Chaitali | 25 | Mumbai | 6500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
The INSERT statement uses the data returned from the subquery to insert into another
table
...
The basic syntax is as follows: INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [
*|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ] Example: Consider a
table CUSTOMERS_BKP with similar structure as CUSTOMERS table
...
Either single or multiple columns in a table can be
updated when using a subquery with the UPDATE statement
...
Following example updates SALARY by 0
...
25 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );
This would impact two rows and finally CUSTOMERS table would have the following records: +----+---------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+----------+----------+ | 1 | Ramesh | 35 | Ahmedabad | 125
...
00 | | 3 |
kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal |
2125
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
The basic
syntax is as follows: DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT
COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] TUTORIALS POINT Simply Easy Learning Example:
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table
...
00 | | 3 |
kaushik | 23 | Kota | 2000
...
00 | | 6 | Komal | 22 | MP |
4500
...
00 | +----+----------+-----+---------+----------+ TUTORIALS
POINT Simply Easy Learning SQL – Using Sequences Asequence is a set of integers 1, 2, 3,
...
Sequences are frequently used in databases because many
applications require each row in a table to contain a unique value, and sequences provide an easy
way to generate them
...
Using
AUTO_INCREMENT column: The simplest way in MySQL to use sequences is to define a column as
AUTO_INCREMENT and leave rest of the things to MySQL to take care
...
This will create table and after that it will insert few rows in this table where it is
not required to give record ID because its auto-incremented by MySQL
...
02 sec) mysql> INSERT
INTO INSECT (id,name,date,origin) VALUES -> (NULL,'housefly','2001-09-10','kitchen'), ->
(NULL,'millipede','2001-09-10','driveway'), -> (NULL,'grasshopper','2001-09-10','front yard'); Query
OK, 3 rows affected (0
...
00
sec) CHAPTER 41 TUTORIALS POINT Simply Easy Learning Obtain AUTO_INCREMENT Values:
LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how
to issue SQL statements
...
PERL Example: Use the mysql_insertid attribute to obtain the
AUTO_INCREMENT value generated by a query
...
The following example
references it through the database handle: $dbh->do ("INSERT INTO INSECT (name,date,origin)

VALUES('moth','2001-09-14','windowsill')"); my $seq = $dbh->{mysql_insertid}; PHP Example: After
issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling
mysql_insert_id( ): mysql_query ("INSERT INTO INSECT (name,date,origin) VALUES('moth','2001-0914','windowsill')", $conn_id); $seq = mysql_insert_id ($conn_id); Renumbering an Existing Sequence:
There may be a case when you have deleted many records from a table and you want to resequence
all the records
...
If you determine that resequencing an AUTO_INCREMENT
column is unavoidable, the way to do it is to drop the column from the table, then add it again
...
Following is the example where MySQL will start sequence from 100
...

mysql> ALTER TABLE t AUTO_INCREMENT = 100; TUTORIALS POINT Simply Easy Learning SQL –
Handling Duplicates There may be a situation when you have multiple duplicate records in a table
...
The SQL DISTINCT keyword, which we already have discussed, is used in
conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique
records
...
columnN FROM table_name WHERE [condition] Example:
Consider the CUSTOMERS table having the following records: +----+----------+-----+-----------+----------+ |
ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 |
Ahmedabad | 2000
...
00 | | 3 | kaushik | 23 | Kota | 2000
...
00 | | 5 | Hardik | 27 | Bhopal | 8500
...
00 | | 7 | Muffy | 24 | Indore | 10000
...
CHAPTER 42 TUTORIALS POINT
Simply Easy Learning +----------+ | SALARY | +----------+ | 1500
...
00 | | 2000
...
00
| | 6500
...
00 | | 10000
...
00 | | 2000
...
00 | | 6500
...
00 | | 10000
...
This lesson will teach you how to help prevent this from
happening and help you secure your scripts and SQL statements in your server side scripts such as
PERL Script
...
Never trust
user provided data, process this data only after validation; as a rule, this is done by pattern
matching
...
if (preg_match("/^\w{8,20}$/",
$_GET['username'], $matches)) { $result = mysql_query("SELECT * FROM CUSTOMERS WHERE
name=$matches[0]"); } else { echo "user name not accepted"; } To demonstrate the problem,

consider this excerpt: // supposed input $name = "Qadir'; DELETE FROM CUSTOMERS;";
mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'"); The function call is supposed
to retrieve a record from the CUSTOMERS table where the name column matches the name
specified by the user
...
But here, by appending an entirely new query
to $name, the call to the database turns into disaster: the injected DELETE query removes all records
from CUSTOMERS
...
If you try to stack queries, the call
fails
...
Preventing SQL Injection: You can
handle all escape characters smartly in scripting languages like PERL and PHP
...
if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name =
mysql_real_escape_string($name); mysql_query("SELECT * FROM CUSTOMERS WHERE
name='{$name}'"); The LIKE Quandary: To address the LIKE quandary, a custom escaping mechanism
must convert user-supplied ‘%’ and ‘_’ characters to literals
...
$sub = addcslashes(mysql_real_escape_string("%str"),
"%_"); // $sub == \%str\_ mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");
TUTORIALS POINT Simply Easy Learning SQL Useful Functions SQL has many built-in functions for
performing processing on string or numeric data
...
 SQL MAX Function - The SQL MAX aggregate function allows us to
select the highest (maximum) value for a certain column
...
 SQL AVG
Function - The SQL AVG aggregate function selects the average value for certain table column
...

SQL SQRT Functions - This is used to generate a square root of a given number
...
 SQL CONCAT Function - This is
used to concatenate any string inside any SQL command
...
 SQL String Functions - Complete list of SQL
functions required to manipulate strings in SQL
...
To understand COUNT function, consider an employee_tbl table,
which is having the following records: SQL> SELECT * FROM employee_tbl; +------+------+------------+-------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+-------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack | 2007-05-06 | 170 | | 3
| Jack | 2007-04-06 | 100 | | 4 | Jill | 2007-04-06 | 220 | | 5 | Zara | 2007-06-06 | 300 | | 5 | Zara |
2007-02-06 | 350 | +------+------+------------+--------------------+ 7 rows in set (0
...
01 sec) Similarly, if you
want to count the number of records for Zara, then it can be done as follows: SQL>SELECT COUNT(*)
FROM employee_tbl -> WHERE name="Zara"; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1
row in set (0
...
SQL MAX Function SQL MAX function is
used to find out the record with maximum value among a record set
...
00 sec) Now suppose based on the above table you want to fetch maximum value of
daily_typing_pages, then you can do so simply using the following command: SQL> SELECT
MAX(daily_typing_pages) -> FROM employee_tbl; +-------------------------+ | MAX(daily_typing_pages)
| +-------------------------+ | 350 | +-------------------------+ 1 row in set (0
...
00 sec) You can use MIN Function along
with MAX function to find out minimum value as well
...
01 sec) SQL MIN Function
SQL MIN function is used to find out the record with minimum value among a record set
...
00 sec) Now suppose based on the above table you
want to fetch minimum value of daily_typing_pages, then you can do so simply using the following
command: SQL> SELECT MIN(daily_typing_pages) -> FROM employee_tbl; +-------------------------+ |
MIN(daily_typing_pages) | +-------------------------+ | 100 | +-------------------------+ 1 row in set (0
...
00
sec) You can use MIN Function along with MAX function to find out minimum value as well
...
01 sec) SQL AVG Function SQL AVG function is used to find out the average of a field
in various records
...
00 sec) Now suppose based on the above
table you want to calculate average of all the dialy_typing_pages, then you can do so by using the
following command: SQL> SELECT AVG(daily_typing_pages) -> FROM employee_tbl; +------------------------+ | AVG(daily_typing_pages) | +-------------------------+ | 230
...
03 sec) You can take average of various records set using GROUP BY clause
...
SQL> SELECT name, AVG(daily_typing_pages) -> FROM employee_tbl GROUP BY name;

+------+-------------------------+ | name | AVG(daily_typing_pages) | +------+-------------------------+ | Jack |
135
...
0000 | TUTORIALS POINT Simply Easy Learning | John | 250
...
0000 | | Zara | 325
...
20 sec) SQL SUM
Function SQL SUM function is used to find out the sum of a field in various records
...
00 sec) Now suppose based on the above table you want to
calculate total of all the dialy_typing_pages, then you can do so by using the following command:
SQL> SELECT SUM(daily_typing_pages) -> FROM employee_tbl; +-------------------------+ |
SUM(daily_typing_pages) | +-------------------------+ | 1610 | +-------------------------+ 1 row in set (0
...
Following example will sum up
all the records related to a single person and you will have total typed pages by every person
...
17 sec) SQL
SQRT Function SQL SQRT function is used to find out the square root of any number
...
000000 | +----------+ 1 row in
set (0
...
You can use SQRT function to find out square root of various records as well
...
00 sec) Now suppose based on the above
table you want to calculate square root of all the dialy_typing_pages, then you can do so by using
the following command: SQL> SELECT name, SQRT(daily_typing_pages) -> FROM employee_tbl; +-----+--------------------------+ | name | SQRT(daily_typing_pages) | +------+--------------------------+ | John |
15
...
832397 | | Jack | 13
...
000000 | | Jill | 14
...
320508 | | Zara | 18
...
00 sec) SQL
RAND Function SQL has a RAND function that can be invoked to produce random numbers between
0 and 1: SQL> SELECT RAND( ), RAND( ), RAND( ); +------------------+-----------------+------------------+ |
RAND( ) | RAND( ) | RAND( ) | +------------------+-----------------+------------------+ | 0
...
1824410643265 | 0
...
00 sec) When invoked with an integer argument, RAND( ) uses that value to seed the random
number generator
...
18109050223705 | 0
...
20788908117254
| +------------------+------------------+------------------+ 1 row in set (0
...
00 sec) Now, use the following commands: SQL> SELECT * FROM employee_tbl ORDER BY RAND();
+------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+-----+------------+--------------------+ | 5 | Zara | 2007-06-06 | 300 | | 3 | Jack | 2007-04-06 | 100 | | 3 | Jack
| 2007-05-06 | 170 | | 2 | Ram | 2007-05-27 | 220 | | 4 | Jill | 2007-04-06 | 220 | | 5 | Zara | 200702-06 | 350 | | 1 | John | 2007-01-24 | 250 | +------+------+------------+--------------------+ 7 rows in set
(0
...
00 sec) SQL CONCAT Function
TUTORIALS POINT Simply Easy Learning SQL CONCAT function is used to concatenate two strings to
form a single string
...
00 sec) To understand CONCAT function in more detail, consider an
employee_tbl table, which is having the following records: SQL> SELECT * FROM employee_tbl; +-----+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+-----------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack |
2007-05-06 | 170 | | 3 | Jack | 2007-04-06 | 100 | | 4 | Jill | 2007-04-06 | 220 | | 5 | Zara | 200706-06 | 300 | | 5 | Zara | 2007-02-06 | 350 | +------+------+------------+--------------------+ 7 rows in set
(0
...
00 sec) SQL Numeric Function SQL numeric functions are used primarily for numeric
manipulation and/or mathematical calculations
...
ACOS() Returns the
arccosine of numeric expression
...
ASIN() Returns
the arcsine of numeric expression
...
ATAN2() Returns the
arctangent of the two variables passed to it
...
BIT_COUNT() Returns the string representation of the binary value passed to it
...
CEIL() Returns the smallest integer
value that is not less than passed numeric expression CEILING() Returns the smallest integer value
that is not less than passed numeric expression CONV() Convert numeric expression from one base
to another
...
The numeric expression should
be expressed in radians
...
DEGREES()
Returns numeric expression converted from radians to degrees
...
FLOOR() Returns the largest
integer value that is not greater than passed numeric expression
...
GREATEST() Returns the largest value of the
input expressions
...
and returns 0
if exp1 is less than exp2, returns 1 if exp1 is less than exp3 and so on
...
LOG() Returns the natural logarithm of the passed numeric
expression
...
MOD()
Returns the remainder of one expression by diving by another expression
...
Returns NULL if passed value is
NULL
...

ROUND() Returns numeric expression rounded to an integer
...
SQRT()
Returns the non-negative square root of numeric expression
...
STDDEV() Returns the standard deviation of the numeric expression
...
TRUNCATE() Returns numeric exp1 truncated to exp2 decimal places
...
ABS(X) The ABS() function returns the absolute value of X
...
00 sec) SQL> SELECT ABS(-2); +---------------------------------------------------------+ |
ABS(2) | +---------------------------------------------------------+ | 2 | +--------------------------------------------------------+ 1 row in set (0
...
The value of X must
range between -1 and 1 or NULL will be returned
...
000000 | +---------------------------------------------------------+ 1 row in set (0
...
The value of X must be in the range of -1 to 1 or
NULL is returned
...
5707963267949 | +--------------------------------------------------------+ 1 row in set (0
...
SQL> SELECT
ATAN(1); +---------------------------------------------------------+ TUTORIALS POINT Simply Easy Learning |
ATAN(1) | +---------------------------------------------------------+ | 0
...
00 sec) ATAN2(Y,X) This function returns the arctangent of
the two arguments: X and Y
...
SQL> SELECT ATAN2(3,6); +--------------------------------------------------------+ | ATAN2(3,6) | +---------------------------------------------------------+ | 0
...
00 sec) BIT_AND(expression) The BIT_AND
function returns the bitwise AND of all bits in expression
...
The function itself returns a 64-bit integer value
...
The following example performs
the BIT_AND function on the PRICE column grouped by the MAKER of the car: SQL> SELECT MAKER,
BIT_AND(PRICE) BITS FROM CARS GROUP BY MAKER +---------------------------------------------------------+
|MAKER BITS | +---------------------------------------------------------+ |CHRYSLER 512 | |FORD 12488 |
|HONDA 2144 | +---------------------------------------------------------+ 1 row in set (0
...
The following example demonstrates using the BIT_COUNT() function to return the
number of active bits for a range of numbers: SQL> SELECT BIT_COUNT(2) AS TWO, BIT_COUNT(4)
AS FOUR, BIT_COUNT(7) AS SEVEN +-----+------+-------+ | TWO | FOUR | SEVEN | +-----+------+-------+ |
1 | 1 | 3 | +-----+------+-------+ 1 row in set (0
...
The
basic premise of the bitwise OR function is that it returns 0 if the corresponding bits match and 1 if
they do not
...
The following example performs the BIT_OR() function on the PRICE column of the CARS table,
grouped by the MAKER: SQL> SELECT MAKER, BIT_OR(PRICE) BITS FROM CARS GROUP BY MAKER +--

-------------------------------------------------------+ |MAKER BITS | +--------------------------------------------------------+ |CHRYSLER 62293 | |FORD 16127 | |HONDA 32766 | +--------------------------------------------------------+ 1 row in set (0
...
Consider the following example: SQL> SELECT CEILING(3
...
46) | +---------------------------------------------------------+ | 4 | +--------------------------------------------------------+ 1 row in set (0
...
43); +--------------------------------------------------------+ | CEIL(-6
...
00 sec) CONV(N,from_base,to_base)
The purpose of the CONV() function is to convert numbers between different number bases
...
The minimum base
value is 2 and the maximum is 36
...

Consider the following example, which converts the number 5 from base 16 to base 2: SQL> SELECT
CONV(5,16,2); +---------------------------------------------------------+ | CONV(5,16,2) | +--------------------------------------------------------+ | 101 | +---------------------------------------------------------+ 1 row in set (0
...
The value of X is
given in radians
...
44807361612917 | +--------------------------------------------------------+ 1 row in set (0
...
Consider the
following example: SQL>SELECT COT(1); +---------------------------------------------------------+ | COT(1) | +--------------------------------------------------------+ | 0
...
00 sec) DEGREES(X) This function returns the value of X converted from
radians to degrees
...
000000 | +--------------------------------------------------------+ 1 row in set (0
...
SQL>SELECT EXP(3); +--------------------------------------------------------+ | EXP(3) | +---------------------------------------------------------+ | 20
...
00 sec) FLOOR(X) This function returns the largest
integer value that is not greater than X
...
55); +--------------------------------------------------------+ | FLOOR(7
...
00 sec)
FORMAT(X,D) The FORMAT() function is used to format the number X in the following format:
###,###,###
...
The following example demonstrates the use and
output of the FORMAT() function: SQL>SELECT FORMAT(423423234
...
65434453,2) | +--------------------------------------------------------+ | 423,423,234
...
00 sec)
GREATEST(n1,n2,n3,
...
The following example uses the GREATEST() function to return
the largest number from a set of numeric values: SQL>SELECT GREATEST(3,5,1,8,33,99,34,55,67,43);
+---------------------------------------------------------+ | GREATEST(3,5,1,8,33,99,34,55,67,43) | +--------------------------------------------------------+ | 99 | +---------------------------------------------------------+ 1 row in set
(0
...
) The INTERVAL() function compares the value of N to the
value list (N1, N2, N3, and so on )
...
The following code is a simple example of how the INTERVAL() fun ction
works: SQL>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10); +---------------------------------------------------------+ |
INTERVAL(6,1,2,3,4,5,6,7,8,9,10) | +---------------------------------------------------------+ | 6 | +--------------------------------------------------------+ 1 row in set (0
...
) The INTERVAL()
function compares the value of N to the value list (N1, N2, N3, and so on )
...
The following code is a simple

example of how the INTERVAL() function works: SQL>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10); +--------------------------------------------------------+ | INTERVAL(6,1,2,3,4,5,6,7,8,9,10) | +--------------------------------------------------------+ | 6 | +---------------------------------------------------------+ TUTORIALS POINT Simply
Easy Learning 1 row in set (0
...
In our case, 7 was the offending value and is located in the sixth
index slot
...
) The LEAST() function is the opposite of the GREATEST() function
...
The
following example shows the proper usage and output for the LEAST() function: SQL>SELECT
LEAST(3,5,1,8,33,99,34,55,67,43); +---------------------------------------------------------+ |
LEAST(3,5,1,8,33,99,34,55,67,43) | +---------------------------------------------------------+ | 1 | +--------------------------------------------------------+ 1 row in set (0
...
If it is called with two arguments, it returns the
logarithm of X for an arbitrary base B
...
806662 | +---------------------------------------------------------+ 1 row in set (0
...
000000 | +---------------------------------------------------------+ 1 row in set
(0
...
SQL>SELECT LOG10(100); +--------------------------------------------------------+ | LOG10(100) | +--------------------------------------------------------+ | 2
...
00 sec) TUTORIALS
POINT Simply Easy Learning MOD(N,M) This function returns the remainder of N divided by M
...
00 sec) OCT(N) The OCT() function returns the string representation of
the octal number N
...
SQL>SELECT OCT(12); +--------------------------------------------------------+ | OCT(12) | +---------------------------------------------------------+ | 14 | +--------------------------------------------------------+ 1 row in set (0
...
SQL internally stores the full double-precision value of pi
...
141593 | +--------------------------------------------------------+ 1 row in set (0
...
SQL> SELECT POWER(3,3); +--------------------------------------------------------+ | POWER(3,3) | +---------------------------------------------------------+ | 27 | +--------------------------------------------------------+ 1 row in set (0
...
TUTORIALS POINT Simply Easy Learning
SQL>SELECT RADIANS(90); +---------------------------------------------------------+ | RADIANS(90) | +--------------------------------------------------------+ |1
...
00 sec) ROUND(X) ROUND(X,D) This function returns X rounded to the nearest integer
...
D must be
positive or all digits to the right of the decimal point will be removed
...
693893); +---------------------------------------------------------+ |
ROUND(5
...
00 sec) SQL>SELECT ROUND(5
...
693893,2) | +---------------------------------------------------------+ | 5
...
00 sec) SIGN(X) This function returns the
sign of X (negative, zero, or positive) as -1, 0, or 1
...
65); +--------------------------------------------------------+ | SIGN(-4
...
00 sec) SQL>SELECT SIGN(0); +--------------------------------------------------------+ | SIGN(0) | +---------------------------------------------------------+ | 0 | +-----------------

----------------------------------------+ 1 row in set (0
...
65); +--------------------------------------------------------+ | SIGN(4
...
00 sec)
SIN(X) This function returns the sine of X
...
893997 | +---------------------------------------------------------+ 1 row in set (0
...
Consider the following example: SQL>SELECT
SQRT(49); +---------------------------------------------------------+ | SQRT(49) | +--------------------------------------------------------+ | 7 | +---------------------------------------------------------+ 1 row in set (0
...
This is equivalent to taking the square root of the VARIANCE() of expression
...
2146 | +--------------------------------------------------------+ 1 row in set (0
...
SQL>SELECT TAN(45); +--------------------------------------------------------+ | TAN(45) | +---------------------------------------------------------+ | 1
...
00 sec) TUTORIALS POINT Simply Easy Learning
TRUNCATE(X,D) This function is used to return the value of X truncated to D number of decimal
places
...
If D is negative, then D number of values in the
integer part of the value is truncated
...
536432,2); +---------------------------------------------------------+ | TRUNCATE(7
...
53 | +---------------------------------------------------------+ 1
row in set (0
...
The following table details the important string functions: Name Description ASCII()
Returns numeric value of left-most character BIN() Returns a string representation of the argument
BIT_LENGTH() Returns length of argument in bits CHAR_LENGTH() Returns number of characters in
argument CHAR() Returns the character for each integer passed CHARACTER_LENGTH() A synonym
for CHAR_LENGTH() CONCAT_WS() Returns concatenate with separator CONCAT() Returns
concatenated string CONV() Converts numbers between different number bases ELT() Returns string
at index number EXPORT_SET() Returns a string such that for every bit set in the value bits, you get
an on string and for every unset bit, you get an off string FIELD() Returns the index (position) of the
first argument in the subsequent arguments FIND_IN_SET() Returns the index position of the first
argument within the second argument FORMAT() Returns a number formatted to specified number
of decimal places HEX() Returns a string representation of a hex value INSERT() Inserts a subs tring at
the specified position up to the specified number of characters INSTR() Returns the index of the first
occurrence of substring LCASE() Synonym for LOWER() LEFT() Returns the leftmost number of
characters as specified TUTORIALS POINT Simply Easy Learning LENGTH() Returns the length of a
string in bytes LOAD_FILE() Loads the named file LOCATE() Returns the position of the first
occurrence of substring LOWER() Returns the argument in lowercase LPAD() Returns the string
argument, left-padded with the specified string LTRIM() Removes leading spaces MAKE_SET()
Returns a set of comma-separated strings that have the corresponding bit in bits set MID() Returns a
substring starting from the specified position OCT() Returns a string representation of the octal
argument OCTET_LENGTH() A synonym for LENGTH() ORD() If the leftmost character of the
argument is a multi-byte character, returns the code for that character POSITION() A synonym for
LOCATE() QUOTE() Escapes the argument for use in an SQL statement REGEXP Pattern matching
using regular expressions REPEAT() Repeat a string the specified number of times REPLACE()
Replaces occurrences of a specified string REVERSE() Reverses the characters in a string RIGHT()

Returns the specified rightmost number of characters RPAD() Appends string the specified number
of times RTRIM() Removes trailing spaces SOUNDEX() Returns a soundex string SOUNDS LIKE
Compares sounds SPACE() Returns a string of the specified number of spaces STRCMP() Compares
two strings SUBSTRING_INDEX() Returns a substring from a string before the specified number of
occurrences of the delimiter SUBSTRING(), SUBSTR() Returns the substring as specified TRIM()
Removes leading and trailing spaces UCASE() Synonym for UPPER() UNHEX() Converts each pair of
hexadecimal digits to a character UPPER() Converts to uppercase TUTORIALS POINT Simply Easy
Learning ASCII(str) Returns the numeric value of the leftmost character of the string str
...
Returns NULL if str is NULL
...
SQL> SELECT ASCII('2'); +---------------------------------------------------------+ | ASCII('2') | +--------------------------------------------------------+ | 50 | +---------------------------------------------------------+ 1 row
in set (0
...
00 sec) BIN(N) Returns a string representation of the binary value of N, where N is a
longlong (BIGINT) number
...
Returns NULL if N is NULL
...
00 sec)
BIT_LENGTH(str) Returns the length of the string str in bits
...
00 sec) CHAR(N,
...
NULL values are skipped
...
00 sec) CHAR_LENGTH(str) Returns the length
of the string str measured in characters
...
This
means that for a string containing five two-byte characters, LENGTH() returns 10, whereas
CHAR_LENGTH() returns 5
...
00 sec) CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH()
...
) Returns the string
that results from concatenating the arguments
...
If all arguments
are non-binary strings, the result is a non-binary string
...
A numeric argument is converted to its equivalent binary string form; if
you want to avoid that, you can use an explicit type cast, as in this example: SQL> SELECT
CONCAT('My', 'S', 'QL'); +---------------------------------------------------------+ | CONCAT('My', 'S', 'QL') | +--------------------------------------------------------+ | SQL | +---------------------------------------------------------+ 1
row in set (0
...
) CONCAT_WS() stands for Concatenate With
Separator and is a special form of CONCAT()
...
The separator is added between the strings to be concatenated
...
If the separator is NULL, the result is NULL
...
00 sec)
CONV(N,from_base,to_base) Converts numbers between different number bases
...
Returns NULL if any
argument is NULL
...
The minimum base is 2 and the maximum base is 36
...
Otherwise, N is treated as unsigned
...
TUTORIALS POINT Simply Easy Learning SQL> SELECT CONV('a',16,2); +--------------------------------------------------------+ | CONV('a',16,2) | +---------------------------------------------------------+ | 1010 | +--------------------------------------------------------+ 1 row in set (0
...
) Returns
str1 if N = 1, str2 if N = 2, and so on
...
ELT() is the complement of FIELD()
...
00 sec)
EXPORT_SET(bits,on,off[,separator[,number_of_bits]]) Returns a string such that for every bit set in
the value bits, you get an on string and for every bit not set in the value, you get an off string
...
Strings are added to the
result from left to right, separated by the separator string (the default being the comma character

...
SQL> SELECT
EXPORT_SET(5,'Y','N',',',4); +---------------------------------------------------------+ | EXPORT_SET(5,'Y','N',',',4)
| +---------------------------------------------------------+ | Y,N,Y,N | +--------------------------------------------------------+ 1 row in set (0
...
) Returns the index (position starting with 1) of
str in the str1, str2, str3,
...
Returns 0 if str is not found
...
00 sec) FIND_IN_SET(str,strlist) Returns a value in the range of 1 to N if the string str is in the
string list strlist consisting of N substrings
...
00 sec) FORMAT(X,D) Formats the number X to a format like '#,###,###
...
If D is 0, the result has no decimal point or
fractional part
...
123456, 4); +--------------------------------------------------------+ | FORMAT(12332
...
1235 | +--------------------------------------------------------+ 1 row in set (0
...
This is equivalent to CONV(N,10,16)
...

SQL> SELECT HEX(255); +---------------------------------------------------------+ | HEX(255) | +--------------------------------------------------------+ | FF | +---------------------------------------------------------+ 1 row in set (0
...
00 sec) INSERT(str,pos,len,newstr) Returns the string str, with the substring beginning at position
pos and len characters long replaced by the string newstr
...
Replaces the rest of the string from position pos if len is not within
the length of the rest of the string
...
SQL> SELECT
INSERT('Quadratic', 3, 4, 'What'); +---------------------------------------------------------+ | INSERT('Quadratic',
3, 4, 'What') | +---------------------------------------------------------+ | QuWhattic | +--------------------------------------------------------+ 1 row in set (0
...
This is the same as the two-argument form of LOCATE(),
except that the order of the arguments is reversed
...
00 sec) LCASE(str) LCASE() is a synonym for LOWER()
...
SQL> SELECT

LEFT('foobarbar', 5); +---------------------------------------------------------+ | LEFT('foobarbar', 5) | +--------------------------------------------------------+ | fooba | +---------------------------------------------------------+ 1 row in
set (0
...
A multi-byte
character counts as multiple bytes
...
SQL> SELECT LENGTH('text'); +--------------------------------------------------------+ | LENGTH('text') | +---------------------------------------------------------+ | 4
| +---------------------------------------------------------+ 1 row in set (0
...
To use this function, the file must be located on the
server host, you must specify the full pathname to the file, and you must have the FILE privilege
...
If the file does not exist
or cannot be read because one of the preceding conditions is not satisfied, the function returns
NULL
...
0
...
SQL> UPDATE table_test -> SET
blob_col=LOAD_FILE('/tmp/picture') -> WHERE id=1;
...
The second syntax
returns the position of the first occurrence of substring substr in string str, starting at position pos
...
SQL> SELECT LOCATE('bar', 'foobarbar'); +--------------------------------------------------------+ | LOCATE('bar', 'foobarbar') | +---------------------------------------------------------+ | 4 | +--------------------------------------------------------+ 1 row in set (0
...
SQL>
SELECT LOWER('QUADRATICALLY'); +---------------------------------------------------------+ |
LOWER('QUADRATICALLY') | +---------------------------------------------------------+ | quadratically | +--------------------------------------------------------+ 1 row in set (0
...
If str is longer than len, the
return value is shortened to len characters
...
00 sec) LTRIM(str) Returns the string str with
leading space characters removed
...
00 sec) MAKE_SET(bits,str1,str2,
...
characters) consisting of the strings that have
the corresponding bit in bits set
...
NULL values in
str1, str2,
...
TUTORIALS POINT Simply Easy Learning SQL> SELECT
MAKE_SET(1,'a','b','c'); +---------------------------------------------------------+ | MAKE_SET(1,'a','b','c') | +--------------------------------------------------------+ | a | +---------------------------------------------------------+ 1 row in
set (0
...
OCT(N)
Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number
...
Returns NULL if N is NULL
...
00 sec) OCTET_LENGTH(str) OCTET_LENGTH() is a
synonym for LENGTH()
...
256) + (3rd byte code
...
If the leftmost
character is not a multi-byte character, ORD() returns the same value as the ASCII() function
...
00 sec)
POSITION(substr IN str) POSITION(substr IN str) is a synonym for LOCATE(substr,str)
...
The string is returned enclosed by single quotes
and with each instance of single quote (’ ‘ ‘), backslash (‘\’), ASCII NUL, and Control-Z preceded by a
backslash
...
SQL> SELECT QUOTE('Don\'t!'); +---------------------------------------------------------+ |
QUOTE('Don\'t!') | +---------------------------------------------------------+ | 'Don\'t!' | +--------------------------------------------------------+ 1 row in set (0
...
expr REGEXP pattern This function performs a pattern
match of expr against pattern
...
If either expr or
pat is NULL, the result is NULL
...

SQL> SELECT 'ABCDEF' REGEXP 'A%C%%'; +---------------------------------------------------------+ | 'ABCDEF'
REGEXP 'A%C%%' | +---------------------------------------------------------+ | 0 | +--------------------------------------------------------+ 1 row in set (0
...
*'; +--------------------------------------------------------+ | 'ABCDE' REGEXP '
...
00 sec) Let's see one more
example: SQL> SELECT 'new*\n*line' REGEXP 'new\\*
...
\\*line' | +---------------------------------------------------------+ | 1 |
+---------------------------------------------------------+ 1 row in set (0
...
If count is less than 1, returns an empty
string
...
SQL> SELECT REPEAT('SQL', 3); +--------------------------------------------------------+ TUTORIALS POINT Simply Easy Learning | REPEAT('SQL', 3) | +--------------------------------------------------------+ | SQLSQLSQL | +---------------------------------------------------------+ 1 row in set
(0
...
REPLACE() performs a case -sensitive match when searching
for from_str
...
mysql
...
mysql
...
mysql
...
00 sec)
REVERSE(str) Returns the string str with the order of the characters reversed
...
00
sec) RIGHT(str,len) Returns the rightmost len characters from the string str, or NULL if any argument
is NULL
...
00 sec) RPAD(str,len,padstr) Returns the string str, rightpadded with the string padstr to a length of len characters
...
SQL> SELECT RPAD('hi',5,'?'); +--------------------------------------------------------+ | RPAD('hi',5,'?') | +---------------------------------------------------------+ | hi??? | +--------------------------------------------------------+ 1 row in set (0
...
SQL> SELECT RTRIM('barbar '); +--------------------------------------------------------+ | RTRIM('barbar ') | +--------------------------------------------------------+ | barbar | +---------------------------------------------------------+ 1 row in set (0
...
Two strings that sound almost the same should have identical
soundex strings
...
You can use SUBSTRING() on the result to get a standard soundex
string
...
All international alphabetic characters
outside the A-Z range are treated as vowels
...
00 sec) expr1 SOUNDS LIKE expr2 This is

the same as SOUNDEX(expr1) = SOUNDEX(expr2)
...
SQL> SELECT SPACE(6); +---------------------------------------------------------+ | SELECT SPACE(6)
| +---------------------------------------------------------+ | ' ' | +---------------------------------------------------------+ 1
row in set (0
...
SQL> SELECT STRCMP('MOHD', 'MOHD'); +--------------------------------------------------------+ | STRCMP('MOHD', 'MOHD') | +---------------------------------------------------------+ | 0 | +--------------------------------------------------------+ 1 row in set (0
...
00 sec) Let's see one more example: SQL>
SELECT STRCMP('MOHD', 'AMOHD'); +---------------------------------------------------------+ |
STRCMP('MOHD', 'AMOHD') | +---------------------------------------------------------+ | 1 | +--------------------------------------------------------+ 1 row in set (0
...
The forms with a len argument return a sub string
len characters long from string str, starting at position pos
...
It is also possible to use a negative value for pos
...
A negative value
may be used for pos in any of the forms of this function
...
00 sec) SQL> SELECT SUBSTRING('foobarbar' FROM 4); +--------------------------------------------------------+ | SUBSTRING('foobarbar' FROM 4) | +---------------------------------------------------------+ | barbar | +--------------------------------------------------------+ 1 row in set (0
...
00 sec) TUTORIALS POINT Simply Easy Learning
SUBSTRING_INDEX(str,delim,count) Returns the substring from string str before count occurrences
of the delimiter delim
...
If count is negative, everything to the right of the final delimiter (counting from
the right) is returned
...
SQL> SELECT SUBSTRING_INDEX('www
...
com', '
...
mysql
...
', 2) | +--------------------------------------------------------+ | www
...
00 sec) TRIM([{BOTH
| LEADING | TRAILING} [remstr] FROM] str) TRIM([remstr FROM] str) Returns the string str with all
remstr prefixes or suffixes removed
...
remstr is optional and, if not specified, spaces are removed
...
00 sec) SQL>
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); +---------------------------------------------------------+ |
TRIM(LEADING 'x' FROM 'xxxbarxxx') | +---------------------------------------------------------+ | barxxx | +--------------------------------------------------------+ 1 row in set (0
...
00 sec) SQL> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); +--------------------------------------------------------+ | TRIM(TRAILING 'xyz' FROM 'barxxyz') | +---------------------------------------------------------+
| barx | +---------------------------------------------------------+ 1 row in set (0
...
TUTORIALS POINT Simply Easy Learning UNHEX(str) Performs the inverse
operation of HEX(str)
...
The resulting characters are
returned as a binary string
...
00 sec) The characters in the argument string
must be legal hexadecimal digits: '0'
...
'F', 'a'
...
If UNHEX() encounters any nonhexadecimal digits in the argument, it returns NULL
...
SQL> SELECT
UPPER('Allah-hus-samad'); +---------------------------------------------------------+ | UPPER('Allah-hus-samad')
| +---------------------------------------------------------+ | ALLAH-HUS-SAMAD | +--------------------------------------------------------+ 1 row in set (0

---