SQL

• SQL is a database computer language designed for the retrieval and

management of data in a relational database. SQL stands for
Structured Query Language.
• SQL is a language to operate databases; it includes database creation,
deletion, fetching rows, modifying rows, etc. SQL is an ANSI (American
National Standards Institute) standard language, but there are many
different versions of the SQL language.
What is SQL?

• SQL is Structured Query Language, which is a computer language for

storing, manipulating and retrieving data stored in a relational database.
• SQL is the standard language for Relational Database System. All the
Relational Database Management Systems (RDMS) like MySQL, MS Access,
Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their
standard database language.
• Also, they are using different dialects, such as −
• MS SQL Server using T-SQL,
• Oracle using PL/SQL,
• MS Access version of SQL is called JET SQL (native format) etc.
Why SQL?

• SQL is widely popular because it offers the following advantages −

• Allows users to access data in the relational database management systems.
• Allows users to describe the data.
• Allows users to define the data in a database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-
compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures and views.
A Brief History of SQL

• 1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of

relational databases. He described a relational model for databases.
• 1974 − Structured Query Language appeared.
• 1978 − IBM worked to develop Codd's ideas and released a product
named System/R.
• 1986 − IBM developed the first prototype of relational database and
standardized by ANSI. The first relational database was released by
Relational Software which later came to be known as Oracle.
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.
• There are various components included in this process.
• These components are −
• Query Dispatcher
• Optimization Engines
• Classic Query Engine
• SQL Query Engine, etc.
• A classic query engine handles all the non-SQL queries, but a SQL query engine won't
handle logical files.
SQL Commands

• The standard SQL commands to interact with relational databases are

CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands
can be classified into the following groups based on their nature
• DDL - Data Definition Language
• DML - Data Manipulation Language
• DCL - Data Control Language
DDL - Data Definition Language

• CREATE
Creates a new table, a view of a table, or other object in the database.
• ALTER
Modifies an existing database object, such as a table.
• DROP
Deletes an entire table, a view of a table or other objects in the
database.
DML - Data Manipulation Language

• SELECT
Retrieves certain records from one or more tables
• INSERT
Creates a record.
• UPDATE
Modifies records.
• DELETE
Deletes records.
DCL - Data Control Language

• GRANT
Gives a privilege to user.

• REVOKE
• Takes back privileges granted from user.
What is RDBMS?

• RDBMS stands for Relational Database Management System. RDBMS

is the basis for SQL, and for all modern database systems like MS SQL
Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
• A Relational database management system (RDBMS) is a database
management system (DBMS) that is based on the relational model as
introduced by E. F. Codd.
What is a table?

• The data in an RDBMS is stored in database objects which are called

as tables. This table is basically a collection of related data entries and
it consists of numerous columns and rows.
• Remember, a table is the most common and simplest form of data
storage in a relational database
Table name Attribute names

Tables in SQL
Product

PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks

Powergizmo $29.99 Gadgets GizmoWorks

SingleTouch $149.99 Photography Canon

MultiTouch $203.99 Household Hitachi

Tuples or rows
SQL Process

• Following is a simple diagram showing the SQL Architecture

DDL - Data Definition Language

• SQL CREATE TABLE Statement

• CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
DDL - Data Definition Language
• SQL ALTER TABLE Statement
• The ALTER TABLE statement is used to add, delete, or modify columns
in an existing table.
• The ALTER TABLE statement is also used to add and drop various
constraints on an existing table.
• ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
SQL DROP TABLE Statement

• The DROP TABLE statement is used to drop an existing table in a

database.
• Syntax
DROP TABLE table_name;
DML - Data Manipulation Language

• SELECT
Retrieves certain records from one or more tables
• INSERT
Creates a record.
• UPDATE
Modifies records.
• DELETE
Deletes records.
SELECT COMMAND
• The SELECT statement is used to select data from a database.
• The data returned is stored in a result table, called the result-set.
SELECT Syntax
SELECT column1, column2, ...
FROM table_name;
SELECT COMMAND

SELECT CustomerName, City FROM Customers;

SQL> SELECT * FROM CUSTOMERS;

SQL Query

Basic form: (plus many many more bells and whistles)

SELECT <attributes>
FROM <one or more relations>
WHERE <conditions>
Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT *
FROM Product
WHERE category=‘Gadgets’

PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
“selection”
Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100

PName Price Manufacturer

“selection” and SingleTouch $149.99 Canon
“projection” MultiTouch $203.99 Hitachi
Notation
Input Schema

Product(PName, Price, Category, Manfacturer)

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100

Answer(PName, Price, Manfacturer)

Output Schema
Details
• Case insensitive:
• Same: SELECT Select select
• Same: Product product
• Different: ‘Seattle’ ‘seattle’

• Constants:
• ‘abc’ - yes
• “abc” - no
Eliminating Duplicates
Category
SELECT DISTINCT category Gadgets
FROM Product
Photography
Household

Compare to:

Category
Gadgets
SELECT category Gadgets
FROM Product
Photography
Household
Ordering the Results

SELECT pname, price, manufacturer

FROM Product
WHERE category=‘gizmo’ AND price > 50
ORDER BY price, pname

Ties are broken by the second attribute on the ORDER BY list, etc.

Ordering is ascending, unless you specify the DESC keyword.

SQL - INSERT Query

• The INSERT INTO statement is used to insert new records in a table.

• INSERT INTO Syntax
• It is possible to write the INSERT INTO statement in two ways.
• The first way specifies both the column names and the values to be
inserted:
• INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...); \or
• INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Example

INSERT INTO Customers (CustomerName, ContactName,

Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21',
'Stavanger', '4006', 'Norway');
SQL SELECT DISTINCT Statement

• The SELECT DISTINCT statement is used to return only distinct

(different) values.
• Inside a table, a column often contains many duplicate values; and
sometimes you only want to list the different (distinct) values.
• The SELECT DISTINCT statement is used to return only distinct
(different) values.
• SELECT DISTINCT Syntax
• SELECT DISTINCT column1, column2, ...
FROM table_name;
SQL SELECT DISTINCT Statement

SELECT Country FROM Customers;

SELECT DISTINCT Country FROM Customers;

SQL AND, OR and NOT Operators

• The WHERE clause can be combined with AND, OR, and NOT
operators.
• The AND and OR operators are used to filter records based on more
than one condition:
• The AND operator displays a record if all the conditions separated by
AND is TRUE.
• The OR operator displays a record if any of the conditions separated
by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.
SQL AND, OR and NOT Operators

SELECT * FROM Customers

WHERE Country='Germany' AND City='Berlin'

SELECT * FROM Customers

WHERE City='Berlin' OR City='München';
SQL ORDER BY Keyword

• The ORDER BY keyword is used to sort the result-set in ascending or

descending order.
• The ORDER BY keyword sorts the records in ascending order by
default. To sort the records in descending order, use the DESC
keyword.
SELECT * FROM Customers
ORDER BY Customerid

SELECT * FROM Customers

ORDER BY Country DESC;
SQL NULL Values

• A field with a NULL value is a field with no value.

• If a field in a table is optional, it is possible to insert a new record or
update a record without adding a value to this field. Then, the field
will be saved with a NULL value
• IS NULL Syntax
• SELECT column_names
FROM table_name
WHERE column_name IS NULL;
SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NULL;
SQL UPDATE Statement

• The UPDATE statement is used to modify the existing records in a

table.

UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE Table

• UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
The SQL DELETE Statement

• The DELETE statement is used to delete existing records in a table.

• DELETE Syntax
• DELETE FROM table_name
WHERE condition;
SQL DELETE Example

DELETE FROM Customers

WHERE CustomerName='Alfreds Futterkiste';

DELETE FROM table_name;

DELETE * FROM table_name;

SQL MIN() and MAX() Functions
• The MIN() function returns the smallest value of the selected column.
• The MAX() function returns the largest value of the selected column.
• MIN() Syntax
• SELECT MIN(column_name)
FROM table_name
WHERE condition;
• MAX() Syntax
• SELECT MAX(column_name)
FROM table_name
WHERE condition;
SQL MIN() and MAX() Functions

SELECT MIN(Price) AS SmallestPrice

FROM Products;

SELECT MAX(Price) AS LargestPrice

FROM Products;
SQL COUNT(), AVG() and SUM()

• The COUNT() function returns the number of rows that matches a specified criteria.
• The AVG() function returns the average value of a numeric column.
• The SUM() function returns the total sum of a numeric column.
• COUNT() Syntax
• SELECT COUNT(column_name)
FROM table_name
WHERE condition;
• AVG() Syntax
• SELECT AVG(column_name)
FROM table_name
WHERE condition;
• SUM() Syntax
• SELECT SUM(column_name)
FROM table_name
WHERE condition;
SQL COUNT(), AVG() and SUM()

SELECT COUNT(ProductID)
FROM Products;
SELECT AVG(Price)
FROM Products;

SELECT SUM(Quantity)
FROM OrderDetails;
SQL LIKE Operator

• The LIKE operator is used in a WHERE clause to search for a specified

pattern in a column.
• There are two wildcards used in conjunction with the LIKE operator:
• % - The percent sign represents zero, one, or multiple characters
• _ - The underscore represents a single character
• LIKE Syntax
• SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
SQL LIKE Operator
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers

WHERE CustomerName LIKE '%a';
The SQL IN Operator

• The IN operator allows you to specify multiple values in a WHERE

clause.
• The IN operator is a shorthand for multiple OR conditions.
• IN Syntax
• SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
The SQL IN Operator

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers

WHERE Country NOT IN ('Germany', 'France', 'UK');
The SQL BETWEEN Operator

• The BETWEEN operator selects values within a given range. The

values can be numbers, text, or dates.
• The BETWEEN operator is inclusive: begin and end values are
included. 
• BETWEEN Syntax
• SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
The SQL BETWEEN Operator

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;
SQL Aliases

• SQL Aliases
• SQL aliases are used to give a table, or a column in a table, a
temporary name.
• Aliases are often used to make column names more readable.
• An alias only exists for the duration of the query.
• Alias Column Syntax
• SELECT column_name AS alias_name
FROM table_name;
SQL Aliases

SELECT CustomerID as ID, CustomerName AS Customer

FROM Customers;
SELECT CustomerName AS Customer, ContactName AS [Contact
Person]
FROM Customers;
SQL GROUP BY Statement

• The SQL GROUP BY Statement

• The GROUP BY statement is often used with aggregate functions
(COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or
more columns.
• GROUP BY Syntax
• SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
SQL GROUP BY Statement

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country;
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SQL HAVING Clause

• The SQL HAVING Clause

• The HAVING clause was added to SQL because the WHERE keyword
could not be used with aggregate functions.
HAVING Syntax
• SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SQL HAVING Clause
• SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT >5
• The following SQL statement lists the number of customers in each
country, sorted high to low (Only include countries with more than 5
customers):(CustomerID) > 5;
SQL CREATE VIEW Statement

• In SQL, a view is a virtual table based on the result-set of an SQL

statement.
• A view contains rows and columns, just like a real table. The fields in a
view are fields from one or more real tables in the database.
• CREATE VIEW Syntax
• CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SQL CREATE VIEW Examples

• CREATE VIEW [Current Product List] AS

SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;
DATA CONTROL LANGUAGE
• DCL Commands are used to restrict the operations on a database
object for a particular user.DBA GRANTS and REVOKE the user
privileges using grant and revoke commands.
• Privileges represents the rights to a particular user to
access ,manipulate,destroy various objects
• Note-A user can be granted all the privileges or some specific
privileges
Creating Data Base users
• Note-Before a privilege can be granted to user ,it is mandatory to create a
user first
How to Create Data Base user
Syntax- Create user username
Identified by password;
Example-Create user abc
Identified by hello;
Output – User created
Note-user abc does not have any privileges at this point,so he cannot do
anything with the database right now
• Privilages can be either system or object privilages
Object Privilages-

Object Privileges Description

INSERT allows users to insert rows into a table.

SELECT allows users to select data from a database object.

UPDATE allows user to update data in a table.

.Allows the user to change the table defination

ALTER

DELETE Allows the user to delete the record

Index Allows the user to create index

With Grant option Allows the user to grant privileges to some other users