SQL

SQL Overview

​

• Structured Query Language

​

• The standard for relational database management systems (RDBMS)

​

• RDBMS: A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables

​

Purpose of SQL Standard

• Specify syntax/semantics for data definition and manipulation
• Define data structures and basic operations
• Enable portability of database definition and application modules
• Specify minimal (level 1) and complete (level 2) standards
• Allow for later growth/enhancement to standard (referential integrity, transaction management, user-defined functions, extended join operations, national character sets)

​

Benefits of a Standardized Relational Language

• Reduced training costs
• Productivity
• Application portability
• Application longevity
• Reduced dependence on a single vendor
• Cross-system communication

​

SQL Environment

• Catalog
• A set of schemas that constitute the description of a database
• Schema
• The structure that contains descriptions of objects created by a user (base tables, views, constraints)
• Data Definition Language (DDL)
• Commands that define a database, including creating, altering, and dropping tables and establishing constraints
• Data Manipulation Language (DML)
• Commands that maintain and query a database
• Data Control Language (DCL)
• Commands that control a database, including administering privileges and committing data

​

SQL Environment

• Catalog
• A set of schemas that constitute the description of a database
• Schema
• The structure that contains descriptions of objects created by a user (base tables, views, constraints)
• Data Definition Language (DDL)
• Commands that define a database, including creating, altering, and dropping tables and establishing constraints
• Data Manipulation Language (DML)
• Commands that maintain and query a database
• Data Control Language (DCL)
• Commands that control a database, including administering privileges and committing data

​

Figure 6-1

A simplified schematic of a typical SQL environment, as described by the SQL: 2008 standard

SQL Major Aspects

• A major strength of the relational model is that it supports simple and powerful querying of data

​

• Structured Query Language (SQL) is the most widely used commercial relational database language

​

• SQL has several aspects to it:
1. Data Manipulation Language (DML)
• It allows users to pose queries and insert, delete and modify rows

​

• Data Definition Language (DDL)
• It allows users to create, delete, and modify tables and views

SQL Major Aspects

• SQL has several aspects to it:
3. Triggers and Advanced Integrity Constraints
• It supports “triggers”, which are actions executed by the DBMS whenever changes to the database meet conditions specified in triggers

​

• Embedded and Dynamic Language
• Embedded SQL allows SQL code to be called from a host language (e.g., Java)
• Dynamic SQL allows SQL queries to be constructed and executed at run-time

SQL Major Aspects

• SQL has several aspects to it:
3. Triggers and Advanced Integrity Constraints
• It supports “triggers”, which are actions executed by the DBMS whenever changes to the database meet conditions specified in triggers

​

• Embedded and Dynamic Language
• Embedded SQL allows SQL code to be called from a host language (e.g., Java)
• Dynamic SQL allows SQL queries to be constructed and executed at run-time

SQL Major Aspects

• SQL has several aspects to it:
5. Remote Database Access
• It allows connecting client programs to remote �database servers

​

• Transaction Management
• It allows users to explicitly control aspects of how a transaction is to be executed (later in the semester)

​

• Security
• It provides mechanisms to control users’ accesses to data objects (e.g., tables and views)

And others…

​

​

Database lifecycle revisited

36

Requirements

​

Logical Design

​

Physical design

​

Implementation

Logical Design

Structured Query Language (SQL)

SQL

37

Modules in EasyShop

DBMS

Customer processing

Supplier

processing

Item

processing

SQL

Demo: EasyShop application

SQL statements

DDL

(Data Definition Language)

CREATE

ALTER

DROP

TRUNCATE

DML

(Data Manipulation Language)

INSERT

UPDATE

DELETE

SELECT

DCL

(Data Control Language)

GRANT

REVOKE

TCL

(Transaction Control Language)

COMMIT

ROLLBACK

38

DDL Commands�

​

CREATE :�This command is used to create a new table in SQL. The user has to give information like table name, column names, and their datatypes.

Syntax:

CREATE TABLE table_name

( column_1 datatype,

column_2 datatype,

column_3 datatype, .... );

​

Example

�We need to create a table for storing Student information of a particular College. Create syntax would be as below.

CREATE TABLE Student_info

( College_Id number(2),

College_name varchar(30),

Branch varchar(10) );

ALTER Command

• ALTER: :�This command is used to add, delete or change columns in the existing table. The user needs to know the existing table name and can do add, delete or modify tasks easily.
• Syntax :

Syntax to add a column to an existing table.

​

• ALTER TABLE table_name ADD column_name datatype;

Example:

�In our Student_info table, we want to add a new column for CGPA.

​

The syntax:

​

• ALTER TABLE Student_info ADD CGPA number;

TRUNCATE:

This command is used to remove all rows from the table, but the structure of the table still exists.

Syntax:

Syntax to remove an existing table.

• TRUNCATE TABLE table_name;

​

Example :

The College Authority wants to remove the details of all students for new batches but wants to keep the table structure.

• TRUNCATE TABLE Student_info;�

• DROP :�This command is used to remove an existing table along with its structure from the Database.

Syntax:� Syntax to drop an existing table.

DROP TABLE table_name;

Example:�If the College Authority wants to change their Database by deleting the Student_info Table. 

• DROP TABLE Student_info;

Data Manipulation Language (DML)

• Data Manipulation Language (DML) commands in SQL deals with manipulation of data records stored within the database tables. It does not deal with changes to database objects and its structure. The commonly known DML commands are INSERT, UPDATE and DELETE.

DML Commands

• SELECT : Used to query or fetch selected fields or columns from a database table
• INSERT : Used to insert new data records or rows in the database table
• UPDATE : Used to set the value of a field or column for a particular record to a new value
• DELETE : Used to remove one or more rows from the database table

�SELECT�

• SELECT command or statement in SQL is used to fetch data records from the database table and present it in the form of a result set.

The basic syntax for writing a SELECT query in SQL is as follows :

SELECT column_name1, column_name2, …� FROM table_name� WHERE condition_ expression;

​

The parameters used in the above syntax are as follows :

• column_name1, column_name2, … : Specify the column_names which have to be fetched or selected for the final result set.
• table_name: Specify the name of the database table from which these results have to be fetched.
• condition_expression: Specify the condition expression for filtering records for the final result set.

Example :

SELECT customer_id,� sale_date,� order_id,� store_state� FROM customers;

​

​

INSERT�

• INSERT commands in SQL are used to insert data records or rows in a database table. In an INSERT statement, we specify both the column_names for which the entry has to be made along with the data value that has to be inserted.

The basic syntax for writing INSERT statements in SQL is as follows :

• INSERT INTO table_name (column_name_1, column_name_2, column_name_3, ...)�VALUES (value1, value2, value3, ...)

​

UPDATE

• UPDATE : UPDATE command or statement is used to modify the value of an existing column in a database table.

The syntax for writing an UPDATE statement is as follows :

UPDATE table_name� SET column_name_1 = value1, column_name_2 = value2, ...� WHERE condition;

Example :

UPDATE customers� SET store_state = 'DL'� WHERE store_state = 'NY';

DELETE�

• DELETE statement in SQL is used to remove one or more rows from the database table. It does not delete the data records permanently. We can always perform a rollback operation to undo a DELETE command.

​

• The syntax for writing an DELETE statement is as follows :

DELETE FROM table_name WHERE condition;

​

​

• DELETE FROM customers�WHERE store_state = 'MH'�AND customer_id = '1001';

DCL (Data Control Language)

• DCL (Data Control Language) includes commands like GRANT and REVOKE, which are useful to give “rights & permissions.” Other permission controls parameters of the database system.

Examples of DCL commands:

• Grant
• Revoke

​

• Grant:

This command is use to give user access privileges to a database.

Syntax:

• GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
• For example:

GRANT SELECT ON Users TO'Tom'@'localhost;

Revoke:

• It is useful to back permissions from the user.

Syntax:

REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}

For example:

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;

Some of The Most Important SQL Commands�

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

​

Data types and operators in SQL

39

Data types

NUMBER

CHAR

VARCHAR2

DATE

Relational operators

=, !=,

<, >,

<=, >=

Logical operators

AND

OR

NOT

• CHAR – Fixed length with a maximum length of 8,000 characters
• Varchar - Variable-length storage with a maximum length of 8,000 characters
• Varchar2- Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character.
• Maximum size is: 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED
• 4000 bytes or characters if MAX_STRING_SIZE = STANDARD
• Date- Stores a date like June 30, 1991

Logical operators:�

The Logical operators are those that are true or false. They return true or false values to combine one or more true or false values.�

Logical operators:

• The AND and OR operators are used with the WHERE clause.
• These two operators are called conjunctive operators.

AND Operator: 

• This operator displays only those records where both the conditions condition1 and condition2 evaluates to True. 

Syntax:

• SELECT * FROM table_name WHERE condition1 AND condition2 and ...conditionN; table_name: name of the table

condition1,2,..N : first condition, second condition and so on

OR Operator �

​

• This operator displays the records where either one of the conditions condition1 and condition2 evaluates to True. That is, either condition1 is True or condition2 is True. 

�Syntax: 

• SELECT * FROM table_name

WHERE condition1 OR condition2 OR... conditionN; table_name:

name of the table condition1,2,..N : first condition, second condition

and so on

OR Operator �

• Now, we consider a table database to demonstrate AND & OR operators with multiple cases:

​

AND Operator �

• If suppose we want to fetch all the records from the Student table where Age is 18 and ADDRESS is Delhi. then the query will be:

Query:

SELECT * FROM Student WHERE Age = 18 AND ADDRESS = 'Delhi';

​

Output: � 

OR Operator

• To fetch all the records from the Student table where NAME is Ram or NAME is SUJIT. � Query:
• SELECT * FROM Student WHERE NAME = 'Ram' OR NAME = 'SUJIT';

​

​

​

Combining AND and OR:�

• We can combine AND and OR operators in the below manner to write complex queries. 

Syntax: 

SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);

• Take an example to fetch all the records from the Student table where Age is 18 NAME is Ram or RAMESH.

Query:

SELECT * FROM Student WHERE Age = 18 AND (NAME = 'Ram' OR NAME = 'RAMESH');

NOT Operator�

• NOT Syntax�SELECT column1, colomn2, … FROM table_name WHERE NOT condition;
• Demo Database�Below is a selection from the “Customers” table in the Northwind sample database:

​

NOT Operator�

• NOT Example�The following SQL statement selects all fields from “Customers” where country is not “UK”�SELECT * FROM Customers WHERE NOT Country=’UK’;

​

Combining AND and NOT�

You can also combine the AND, OR and NOT operators.�Example:�SELECT * FROM Customers WHERE NOT Country=’USA’ AND NOT Country=’UK’;

DDL and DML

40

Constraints - Types:

• UNIQUE
• NOT NULL
• PRIMARY KEY
• CHECK
• FOREIGN KEY

Constraints - Levels:

• Column level
• Table level

SQL Constraints

• SQL constraints are used to specify rules for the data in a table.
• Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table.
• If there is any violation between the constraint and the data action, the action is aborted.
• Constraints can be column level or table level.
• Column level constraints apply to a column, and table level constraints apply to the whole table.

​

SQL Constraints

• The following constraints are commonly used in SQL:
• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

​

SQL NOT NULL Constraint�

• By default, a column can hold NULL values.
• The NOT NULL constraint enforces a column to NOT accept NULL values.
• This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Example

• CREATE TABLE Persons (�    ID int NOT NULL,�    LastName varchar(255) NOT NULL,�    FirstName varchar(255) NOT NULL,�    Age int�);

​

SQL UNIQUE Constraint�

• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
• However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
• Example :

CREATE TABLE Persons (�    ID int NOT NULL UNIQUE,�    LastName varchar(255) NOT NULL,�    FirstName varchar(255),�    Age int�);

SQL PRIMARY KEY Constraint�

• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

SQL PRIMARY KEY on CREATE TABLE

Example:

CREATE TABLE Persons (�    ID int NOT NULL,�    LastName varchar(255) NOT NULL,�    FirstName varchar(255),�    Age int,�    PRIMARY KEY (ID)�);

SQL FOREIGN KEY Constraint

• The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

​

• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

​

• The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

​

SQL FOREIGN KEY Constraint

• CREATE TABLE Orders (�    OrderID int NOT NULL,�    OrderNumber int NOT NULL,�    PersonID int,�    PRIMARY KEY (OrderID),�    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)�);
• Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.
• The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
• The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

​

SQL CHECK Constraint�

• SQL CHECK on CREATE TABLE
• The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:
• CREATE TABLE Persons (�    ID int NOT NULL,�    LastName varchar(255) NOT NULL,�    FirstName varchar(255),�    Age int,�    CHECK (Age>=18)�);

ALTER TABLE

• ALTER TABLE statement is used to modify the structure of an existing table
• Modification of structure includes
• Adding/Dropping columns or constraints
• Modifying the data type or size of columns

46

SELECT

47

• IN
• LIKE

• IS NULL
• BETWEEN
• AND

Operators:

Select Statement with operators

SELECT * FROM Customers

WHERE City IN ('Paris','London');

Like

SELECT * FROM Customers

WHERE City LIKE 's%';

BETWEEN�

SELECT * FROM Products

WHERE Price BETWEEN 50 AND 60;

DISTINCT, ORDER BY

Confidential

Copyright © 2017, Infosys Limited

• DISTINCT is used to get the discrete values from column(s)
• ORDER BY is used to sort the records while retrieving
• The data stored in the table will not be sorted
• By default the order is ASCENDING

48

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.
• SELECT DISTINCT column1, column2, ...�FROM table_name;
• SELECT DISTINCT Country FROM Customers;

SQL ORDER BY Keyword�

The ORDER BY command is used to sort the result set in ascending or descending order.

• The ORDER BY command sorts the result set in ascending order by default. To sort the records in descending order, use the DESC keyword.
• SELECT * FROM Customers�ORDER BY CustomerName ASC;
• SELECT * FROM Customers�ORDER BY CustomerName DESC;

SQL CASE Statement�

• The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
• If there is no ELSE part and no conditions are true, it returns NULL.

​

SQL CASE Statement�

• CASE�    WHEN condition1 THEN result1�    WHEN condition2 THEN result2�    WHEN conditionN THEN resultN�    ELSE result�END;

SQL CASE Statement�

• SELECT OrderID, Quantity,�CASE�    WHEN Quantity > 30 THEN 'The quantity is greater than 30'�    WHEN Quantity = 30 THEN 'The quantity is 30'�    ELSE 'The quantity is under 30'�END AS QuantityText�FROM OrderDetails;

SQL CASE Statement

• The following SQL will order the customers by City. However, if City is NULL, then order by Country:
• SELECT CustomerName, City, Country�FROM Customers�ORDER BY�(CASE�    WHEN City IS NULL THEN Country�    ELSE City�END);

SQL functions

Single row functions

• Numeric
• ROUND
• CEIL
• FLOOR
• ABS
• Character
• LOWER
• UPPER
• SUBSTR
• LENGTH
• DATE
• ADD_MONTHS
• MONTHS_BETWEEN
• TO_CHAR
• Conversion
• TO_CHAR
• TO_NUMBER
• TO_DATE
• Others
• NVL

51

Multi row functions (Aggregate functions)

• MIN
• MAX
• SUM
• AVG
• COUNT