Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….

Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

UNIT-3

SQL
 Introduction to SQL
 History of SQL
 Benefits of SQL
 Features of SQL
 DBMS v/s RDBMS
 Introduction to SQL: Data types, Constraints
 Commands in SQL: Create table, Drop command, Alter Queries in SQL
 Statements in SQL (Insert, delete and update)
 Manipulation of data
 Conclusion of Unit

# Introduction to SQL
SQL, stands for Structured Query Language, is a domain-specific programming language used
for managing and manipulating relational databases. SQL is essential for both database
administrators and developers who work with relational databases. The main purpose of SQL is
to provide a structured and standardized way to communicate with databases. It allows users to
define, manipulate, and query data, as well as manage the structure of the database itself. SQL is
essential for tasks such as creating and modifying tables, inserting, updating, and deleting
records, and retrieving data based on specific criteria.

SQL is not tied to any specific database management system but rather serves as a standardized
way to interact with various DBMS, such as MySQL, PostgreSQL, Oracle, Microsoft SQL Server,
and many others. While the core concepts of SQL remain relatively consistent across systems,
there might be some variations in syntax and features between different DBMS implementations.

SQL is not a single, monolithic language, but rather a collection of sub-languages that cater to
different aspects of database management. Some of the most commonly used sub-languages
within SQL include:
1. Data Definition Language (DDL): DDL is used for defining and managing the structure
of the database. Statements like CREATE, ALTER, and DROP are part of DDL and are
used to create and modify tables, indexes, and other database objects.
2. Data Manipulation Language (DML): DML allows you to manipulate the data within the
database. The main statements in DML are INSERT, UPDATE, and DELETE, which
respectively add new records, modify existing records, and remove records from tables.
3. Data Control Language (DCL): DCL is concerned with managing user access and
permissions within the database. Statements like GRANT and REVOKE are used to grant
or revoke privileges to users and roles.
4. Transaction Control Language (TCL): TCL statements are used to manage transactions,
which are sequences of one or more database operations that are executed as a single
unit. The main TCL statements are COMMIT, ROLLBACK, and SAVEPOINT.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

#History of SQL

The chronological overview of SQL development:

1. 1960s: Early concepts of using a query language to interact with databases were developed
by IBM's Information Management System (IMS) project.
2. Early 1970s: Donald D. Chamberlin and Raymond F. Boyce develop a prototype query
language called SEQUEL (Structured English Query Language) while working on the IBM
System R project.
3. 1974: "A Relational Model of Data for Large Shared Data Banks," a research paper by Edgar
F. Codd, outlines the foundational principles of the relational database model, which SQL is
designed to work with.
4. Late 1970s: SEQUEL is renamed SQL due to trademark issues.
5. 1981: The first commercial relational database system to support SQL, Oracle Database, is
released by Oracle Corporation.
6. 1986: The American National Standards Institute (ANSI) publishes the first official SQL
standard, SQL-86 (SQL1).
7. 1987: IBM releases IBM Db2, which supports SQL as its query language.
8. 1992: The SQL-92 (SQL2) standard is published, introducing important features like outer
joins and subqueries.
9. 1993: Microsoft releases Microsoft SQL Server, a relational database system supporting SQL.
10. 1995: MySQL, an open-source relational database management system, is released. It
becomes widely known for its use of SQL.
11. 2003: The SQL:2003 standard is released, bringing improvements like support for recursive
queries and improved window functions.
12. 2005: PostgreSQL 8.0 introduces enhanced support for SQL features and standards
compliance.
13. 2007: SQLite becomes more prominent as an embedded SQL database engine, used in
various applications and devices.
14. 2011: The SQL:2011 standard is released, introducing further enhancements to the
language, including temporal data support and enhancements to the MERGE statement.
15. 2015: MySQL is forked into MariaDB due to concerns about Oracle's acquisition of MySQL.
16. 2016: The SQL:2016 standard is released, focusing on additional features for JSON support,
improved query performance, and more.
17. 2017: Microsoft releases SQL Server 2017, which includes support for running on Linux.
18. 2019: PostgreSQL continues to gain popularity for its extensibility and support for advanced
SQL features.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# Benefits of SQL
Some of the key benefits of SQL are:
1. Ease of Use: SQL uses a simple and intuitive syntax that resembles natural language,
making it relatively easy to learn and use, especially for querying and managing data.
2. Data Retrieval and Manipulation: SQL provides powerful and flexible tools for retrieving,
filtering, sorting, and aggregating data from databases. This makes it easy to extract specific
information from large datasets.
3. Data Integrity and Consistency: SQL enforces data integrity by allowing the definition of
constraints, such as primary keys, foreign keys, and unique constraints. This ensures that
data remains accurate and consistent over time.
4. Data Definition and Modeling: SQL supports the creation, modification, and deletion of
database objects such as tables, indexes, and views. It also enables the definition of
relationships between tables, facilitating data modeling.
5. Database Normalization: SQL supports the principles of database normalization, which
helps organize data efficiently, reduces data duplication, and improves overall data integrity.
6. Transaction Management: SQL supports transactions, allowing users to group multiple SQL
statements into a single unit of work. This ensures that changes to the database are either
fully completed or fully rolled back in case of errors or failures.
7. Concurrency Control: SQL provides mechanisms for managing concurrent access to data by
multiple users or applications, ensuring that transactions are isolated and consistent.
8. Security and Access Control: SQL enables the definition of access permissions, roles, and
privileges at both the database and object levels. This helps protect sensitive data and
restricts unauthorized access.
9. Scalability: SQL databases can handle large amounts of data and scale horizontally (adding
more servers) or vertically (upgrading server hardware) to accommodate increasing workloads.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# Features of SQL
1. High Performance
SQL includes features like high-performance programming skills for highly transactional
database systems. SQL has several ways to represent the data more analytically.

2. High Availability
Structured Query Language is cooperative with databases like Microsoft SQL Server, MS Access,
SAP Adaptive Server, MySQL, Oracle Database, SAP HANA, etc. All of these relational database
management systems support SQL. It is easy to create an application extension for procedural
programming and various other functions, which are additional features, thus converting SQL
into a powerful tool.

3. Scalability and Flexibility

SQL includes features like Flexibility and scalability. In this programming language creation of
tables and earlier created or not utilized tables can be dropped or deleted very quickly in a
database.

4. Robust Transactional Support

It also includes the feature that can handle massive records and manage numerous transactions.

5. High Security
It is straightforward to grant permissions on tables, procedures, and views; hence SQL gives
security to your data.

6. Comprehensive Application Development

SQL is utilized by various programmers to program applications to access a database. Every
small and large organization is using SQL this time for their database.

7. Management Ease
SQL programming language is utilized in each and every relational database management
system. Standard commands like Drop, Insert, Delete, Select, Create, Update is easily accessible
by a developer to manage large amounts of data from the database.

8. Open Source
Structures Query Language is an open-source programming language feature for developing
relational database management systems.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# Key Features of SQL

Some key features of SQL are:
1. Data Querying and Retrieval:
a. SELECT Statement: The core SQL command for querying and retrieving data from one or
more tables.
b. WHERE Clause: Filters data based on specified conditions.
c. ORDER BY Clause: Sorts query results in ascending or descending order.
d. GROUP BY Clause: Groups data for aggregate functions like SUM, AVG, COUNT, etc.
e. HAVING Clause: Filters grouped data based on specified conditions.
2. Data Manipulation:
a. INSERT Statement: Adds new records into a table.
b. UPDATE Statement: Modifies existing records in a table.
c. DELETE Statement: Removes records from a table.
d. MERGE Statement: Combines INSERT, UPDATE, and DELETE operations based on a
specified condition.
3. Data Definition:
a. CREATE Statement: Creates new database objects such as tables, views, indexes, and
more.
b. ALTER Statement: Modifies existing database objects, including tables and columns.
c. DROP Statement: Removes database objects.
4. Data Integrity and Constraints:
a. Primary Key Constraint: Enforces uniqueness and identifies the primary key of a table.
b. Foreign Key Constraint: Establishes relationships between tables and enforces
referential integrity.
c. Unique Constraint: Ensures the uniqueness of values in a column.
d. Check Constraint: Enforces specific conditions on column values.
e. Not Null Constraint: Ensures that a column cannot contain null values.
5. Data Relationships and Joins:
a. JOIN Operations: Combines data from multiple tables based on specified relationships
(INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).
b. Subqueries: Nested queries used within another query to retrieve specific data.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

6. Data Aggregation and Functions:

a. Aggregate Functions: Performs calculations on a set of values (SUM, AVG, COUNT, MAX,
MIN).
b. Scalar Functions: Performs operations on individual values (UPPER, LOWER, CONCAT,
LENGTH, etc.).
7. Transactions and Concurrency Control:
a. BEGIN TRANSACTION: Initiates a transaction block.
b. COMMIT: Finalizes a transaction, saving changes.
c. ROLLBACK: Reverts a transaction, undoing changes.
d. SAVEPOINT: Creates a point within a transaction to which you can later roll back.
e. Isolation Levels: Specify how transactions interact and handle concurrent access to
data.
8. Data Security and Access Control:
a. GRANT: Assigns specific privileges (SELECT, INSERT, UPDATE, DELETE) to users or
roles.
b. REVOKE: Removes previously granted privileges.
9. Views:
a. CREATE VIEW: Creates a virtual table based on the result of a query.
b. ALTER VIEW: Modifies the definition of an existing view.
c. DROP VIEW: Removes a view.
10. Indexes:
a. CREATE INDEX: Creates an index on one or more columns to improve query
performance.
b. DROP INDEX: Removes an index.
11. Stored Procedures, Functions, and Triggers:
a. Stored Procedures: Precompiled sets of one or more SQL statements, stored for later
execution.
b. Functions: Return a single value based on input parameters.
c. Triggers: Automatically executed in response to certain events (INSERT, UPDATE,
DELETE).
12. Standardization and Portability:
a. SQL is an ANSI/ISO standard language, ensuring portability of code across different
database systems.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# DBMS v/s RDBMS

Here's a comparison between DBMS (Database Management System) and RDBMS (Relational
Database Management System) presented in a table format:
S.No Feature DBMS RDBMS
Can store any type of data, Stores data in structured tables
1 Data Storage
structured or unstructured. with rows and columns.
Supports relationships between Follows the relational model
Data
2 data but not necessarily based with well-defined relationships
Relationships
on the relational model. between tables.
Provides basic data integrity Enforces data integrity through
3 Data Integrity features but may not enforce constraints like primary keys,
strict constraints. foreign keys, etc.
Follows a rigid schema where
Schema Offers flexibility in defining data
4 tables and relationships need to
Flexibility structures and relationships.
be predefined.
Typically follows normalization
May or may not support
5 Normalization principles to eliminate data
normalization principles.
redundancy.
Provides powerful data
Data Offers basic data manipulation manipulation using SQL,
6
Manipulation capabilities. including SELECT, INSERT,
UPDATE, DELETE.
Optimized for complex query
Query Generally optimized for read-
7 operations through indexing and
Performance heavy operations.
query optimization techniques.
May or may not support Supports indexing to enhance
8 Indexing
indexing for faster data retrieval. query performance.
MySQL, PostgreSQL, Oracle
9 Examples File systems, NoSQL databases.
Database, Microsoft SQL Server.
May or may not provide full
ACID ACID (Atomicity, Consistency, Generally provides full ACID
10
Compliance Isolation, Durability) compliance for transactions.
compliance.
Supports various data models Primarily follows the relational
11 Data Modeling
like hierarchical, network, etc. data model.
Aims to minimize data
Data May lead to data redundancy
12 redundancy through
Redundancy due to the lack of normalization.
normalization.
Provides standardized scalability
Offers less standardized
13 Scalability options like horizontal and
scalability options.
vertical scaling.
Suitable for smaller projects Ideal for projects where
Examples of Use
14 with less complex data structured data and complex
Cases
relationships. relationships are prevalent.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

#SQL Data Types

Data types are used to represent the nature of the data that can be stored in the database table.
Data types determine the kind of data that a column can hold. For example, in a particular column
of a table, if we want to store a string type of data then we will have to declare a string data type of
this column.
Data types mainly classified into three categories for every database.
1. String Data types
2. Numeric Data types
3. Date and time Data types

1. String Data types:

It is used to specify a fixed length string that can contain
CHAR(Size) numbers, letters, and special characters. Its size can be 0 to 255
characters. Default is 1.

It is used to specify a variable length string that can contain

VARCHAR(Size) numbers, letters, and special characters. Its size can be from 0
to 65535 characters.

It holds a string that can contain a maximum length of 255

TEXT(Size)
characters.

2. Numeric Data types

It is used for the integer value. Its signed range varies from -2147483648
INT(size) to 2147483647 and unsigned range varies from 0 to 4294967295. The
size parameter specifies the max display width that is 255.

INTEGER(size) It is equal to INT(size).

It is used to specify a floating point number. Its size parameter specifies

FLOAT(size, d) the total number of digits. The number of digits after the decimal point is
specified by d parameter.

It is used to specify a floating point number. MySQL used p parameter to

determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the
FLOAT(p)
data type becomes FLOAT (). If p is from 25 to 53, the data type becomes
DOUBLE().

It is a normal size floating point number. Its size parameter specifies the
DOUBLE(size, d) total number of digits. The number of digits after the decimal is specified
by d parameter.

It is used to specify a fixed point number. Its size parameter specifies the
total number of digits. The number of digits after the decimal parameter
DECIMAL(size, d) is specified by d parameter. The maximum value for the size is 65, and
the default value is 10. The maximum value for d is 30, and the default
value is 0.

DEC(size, d) It is equal to DECIMAL(size, d).

Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

3. Date and time Data types

It is used to specify date format YYYY-MM-DD. Its supported range is from
DATE
'1000-01-01' to '9999-12-31'.

It is used to specify date and time combination. Its format is YYYY-MM-

DATETIME(fsp) DD hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-
12-31 23:59:59'.

It is used to specify the timestamp. Its value is stored as the number of

seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its format is
TIMESTAMP(fsp)
YYYY-MM-DD hh:mm:ss. Its supported range is from '1970-01-01
00:00:01' UTC to '2038-01-09 03:14:07' UTC.

It is used to specify the time format. Its format is hh:mm:ss. Its supported
TIME(fsp)
range is from '-838:59:59' to '838:59:59'

It is used to specify a year in four-digit format. Values allowed in four digit

YEAR
format from 1901 to 2155, and 0000.
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# SQL Constraints
Constraints are rules that define the properties and relationships of the data in a database.
Constraints are the rules applied to a data columns or the complete table to limit the type of data
that can go into a table. They help enforce data integrity and maintain consistency.
Here are some common constraints:
1. NOT NULL
2. CHECK
3. DEFAULT
4. PRIMARY KEY
5. AUTO_INCREMENT
6. UNIQUE
7. INDEX
8. ENUM
9. FOREIGN KEY

1. NOT NULL
This constraint specifies that the column cannot have NULL or empty values. The below
statement creates a table with NOT NULL constraint.
Ex- mysql> CREATE TABLE STUDENT(ID INTEGER, LASTNAME TEXT NOT NULL,
FIRSTNAME TEXT NOT NULL, CITY VARCHAR(35));

2. CHECK(expr):
It controls the value in a particular column. It ensures that the inserted value in a column
must be satisfied with the given condition. In other words, it determines whether the value
associated with the column is valid or not with the given condition.
Ex- mysql> CREATE TABLE Persons (ID int NOT NULL,Name varchar(45) NOT NULL,
Age int CHECK (Age>=18) );

3. DEFAULT
This constraint is used to set the default value for the particular column where we have not
specified any value. It means the column must contain a value, including NULL.
Ex- mysql> CREATE TABLE Persons ( ID int NOT NULL,Name varchar(45) NOT NULL,
Age int, City varchar(25) DEFAULT 'New York');

4. PRIMARY KEY
This constraint is used to identify each record in a table uniquely. If the column contains
primary key constraints, then it cannot be null or empty. A table may have duplicate
columns, but it can contain only one primary key. It always contains unique value into a
column.
Ex-mysql>CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, Name
varchar(45) NOT NULL, Age int, City varchar(25));
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

5. AUTO_INCREMENT
This constraint automatically generates a unique number whenever we insert a new record
into the table. Generally, we use this constraint for the primary key field in a table.
Ex- mysql> CREATE TABLE Animals( id int NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL, PRIMARY KEY (id));

6. UNIQUE
This constraint ensures that all values inserted into the column will be unique. It means a
column cannot stores duplicate values. MySQL allows us to use more than one column with
UNIQUE constraint in a table.
Ex- mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40)
UNIQUE, Size VARCHAR(30));

7. INDEX
This constraint allows us to create and retrieve values from the table very quickly and easily.
An index can be created using one or more than one column. It assigns a ROWID for each
row in that way they were inserted into the table.
Ex- mysql> CREATE TABLE Shirts (id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35), size ENUM('small', 'medium', 'large', 'x-large') );

8. ENUM
The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a
list of permitted values in the column specification at the time of table creation. It is short for
enumeration, which means that each column may have one of the specified possible values. It
uses numeric indexes (1, 2, 3…) to represent string values.
Ex- mysql> CREATE TABLE Shirts (id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35), size ENUM('small', 'medium', 'large', 'x-large') );

9. FOREIGN KEY
This constraint is used to link two tables together. It is also known as the referencing
key. A foreign key column matches the primary key field of another table. It means a
foreign key field in one table refers to the primary key field of another table.
Ex- Let us consider the structure of these tables: Persons and Orders.
Table: Persons
mysql>CREATE TABLE Persons ( Person_ID int NOT NULL PRIMARY
KEY,Name varchar(45) NOT NULL, Age int, City varchar(25) );
Table: Orders
Mysql>CREATE TABLE Orders ( Order_ID int NOT NULL PRIMARY KEY,
Order_Num int NOT NULL, Person_ID int,FOREIGN KEY (Person_ID)
REFERENCES Persons(Person_ID) );
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# Commands in SQL: Create table, Drop command, Alter Queries in SQL

CREATE TABLE Statement

SQL provides the CREATE TABLE statement to create a new table in a given database. An SQL query
to create a table must define the structure of a table. The structure consists of the name of a table
and names of columns in the table with each column's data type. Note that each table must be
uniquely named in a database.
Syntax: CREATE TABLE table_name(column1 datatype,column2 datatype,column3 datatype,
..... columnN datatype, PRIMARY KEY( one or more columns ));

DROP Table Statement

The SQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to
remove a table's definition, and its data.
Syntax: DROP TABLE table_name;

ALTER TABLE Statement

The SQL ALTER TABLE command is a part of Data Definition Language (DDL) and modifies the
structure of a table. The ALTER TABLE command can add or delete columns, create or destroy
indexes, change the type of existing columns, or rename columns or the table itself.
Operation in alter-
 add: to add a new column to a table
Ex- ALTER TABLE table_name ADD column_name datatype;

 drop: to drop an existing column from a table

Ex-ALTER TABLE table_name DROP COLUMN column_name;

 modify: to change the datatype of any column in an existing table

Ex-ALTER TABLE table_name MODIFY COLUMN column_name datatype;

 rename: to rename a column name of an existing table

Ex- ALTER TABLE table_name RENAME COLUMN old_column_name to new_column_name;
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# Statements in SQL (Insert, delete and update)

INSERT INTO STATEMENT

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database. The
INSERT statement will only accept the data that follows all the attributes of a column in a table. The
data inserted into a table must have same datatypes, satisfy the constraints (if any), etc.
There are two basic syntaxes of the INSERT INTO statement which are shown below.

Syntax-1: INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1,

value2, value3,...valueN);

Syntax-2: INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

DELETE STATEMENT
DELETE statement can be used to delete multiple rows of a single table and records across multiple
tables. In order to filter the records to be deleted, you can use the WHERE clause along with the
DELETE statement.

Syntax: DELETE FROM table_name WHERE [condition];

UPDATE STATEMENT
UPDATE statement is used to modify the existing records in a table.

Syntax: UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

# Manipulation of data

Mathematical Functions Arithmetic Operators

Name Description Name Description

ABS() Return the absolute value %, MOD Modulo operator
MOD() Return the remainder * Multiplication operator
PI() Return the value of pi + Addition operator
Return the argument raised to the specified - Minus operator
POW() power Change the sign of the
Return the argument raised to the specified - argument
POWER() power / Division operator
RAND() Return a random floating-point value DIV Integer division
ROUND() Round the argument
SQRT() Return the square root of the argument
Truncate to specified number of decimal
TRUNCATE() places

String Functions
Logical Operators
Name Description
LIKE Simple pattern matching Name Description
NOT LIKE Negation of simple pattern matching AND, && Logical AND

STRCMP() Compare two strings NOT, ! Negates value

TRIM() Remove leading and trailing spaces OR, || Logical OR

UCASE() Synonym for UPPER()

LIKE Simple pattern matching

Aggregate Functions

Name Description
AVG() Return the average value of the argument
COUNT() Return a count of the number of rows returned
COUNT(DISTINCT) Return the count of a number of different values
MAX() Return the maximum value
MIN() Return the minimum value
SUM() Return the sum
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

Date and Time Functions

Name Description
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURTIME() Return the current time
DATE() Extract the date part of a date or datetime expression
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
QUARTER() Return the quarter from a date argument
SECOND() Return the second (0-59)
SYSDATE() Return the time at which the function executes
TIME() Extract the time portion of the expression passed
TIMEDIFF() Subtract time
WEEK() Return the week number
YEAR() Return the year
Faculty of : FCE. Program: BTech Class/Section: 4 F/J Date: ……………………….
Name of Faculty: Dr. S.K.Kapoor Name of Course: RDBMS Code: BCECCE4103

Comparison Operators

Name Description
> Greater than operator
>= Greater than or equal operator
< Less than operator
<>, != Not equal operator
<= Less than or equal operator
<=> NULL-safe equal to operator
= Equal operator
BETWEEN ... AND ... Whether a value is within a range of values
COALESCE() Return the first non-NULL argument
GREATEST() Return the largest argument
IN() Whether a value is within a set of values
Return the index of the argument that is less than the first
INTERVAL() argument
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
LIKE Simple pattern matching
NOT BETWEEN ... AND ... Whether a value is not within a range of values
NOT IN() Whether a value is not within a set of values
NOT LIKE Negation of simple pattern matching
STRCMP() Compare two strings