International Taxation – I

Digital Technology

11/2/2020 K SAILAJA KUMAR 1

Topics to be Covered
1. Introduction to tools SQL DB
2. Single - Table Queries
3. Joins and Subqueries
4. Table Expression and Set Operators
5. SQL Commands
6. SQL Programmable Objects

11/2/2020 K SAILAJA KUMAR 2

1.Introduction to tools SQL DB
◦ SQL Overview and RDBMS Concepts
◦ History of SQL and Why we need SQL
◦ Definition - SQL, RDBMS, Table, Field, Row/Record, Column, Nulls.
◦ SQL Constraints
◦ Data Integrity
◦ Database Normalization

11/2/2020 K SAILAJA KUMAR 3

2.Single - Table Queries
◦ SQL Datatypes,
◦ Examples of Operators -Arithmetic, Comparison, Logical,
◦ SELECT Syntax Using FROM, WHERE, GROUP BY, HAVING, ORDER BY
Clauses,
◦ Working on Case Expression, Like Predicate, NULL Values,
◦ System Defined Function - Scalar, Aggregate, Date, String,
Ranking/Windows Function

11/2/2020 K SAILAJA KUMAR 4

3. Joins and Subqueries
◦ Basic Overview of different types of joins, Syntax and Examples –
 Inner,
 Outer,
 Full Outer,
 Self,
 Cartesian Join,
◦ Self-Contained Scalar and Multivalued Subqueries

11/2/2020 K SAILAJA KUMAR 5

4. Table Expression and Set Operators
Definition and Syntax
◦ - Table Expressions and Set Operators
◦ Derived Tables
◦ Common Table Expression (CTEs)
◦ Views,
◦ Table- Valued Functions, Definition and Syntax
◦ - Union, Union ALL, Intersect, Except Operators

11/2/2020 K SAILAJA KUMAR 6

5. SQL Commands
◦ Data Manipulation Language (DML) Syntax
◦ - SELECT, INSERT, UPDATE, DELETE, MERGE
◦ Data Definition Language (DDL) Syntax
◦ - CREATE, ALTER, DROP, TRUNCATE
◦ Data Control Language (DCL) Syntax
◦ -GRANT, REVOKE,
◦ Transactional Control Language Syntax
◦ - COMMIT, ROLLBACK, SAVE TRANSACTION,
◦ Other data Insertion Queries
◦ - INSERT VALUES, INSERT SELECT, INSERT EXEC, SELECT INTO, BULK INSERT

11/2/2020 K SAILAJA KUMAR 7

6. SQL Programmable Objects
◦ Definition
◦ - Variables, Batches, Transaction, Flow Element (IF. Else, While),
◦ Syntax and Examples
◦ - Cursors and Temporary Tables,
◦ Overview on Stored Procedure,
◦ User Defined Function (UDFs) and Indexes,
◦ Basics of Error Handling.

11/2/2020 K SAILAJA KUMAR 8

SQL Overview
Structured Query Language (SQL) –
◦ Programming language
◦ Designed for managing Relational Database Management System
(RDBMS)

Can also be used in

◦ Relational Data Stream Management Systems (RDSMS)
◦ "not-only SQL" (NoSQL) databases.

11/2/2020 K SAILAJA KUMAR 9

SQL implementation:
◦ MS SQL:

◦ MySQL:

◦ Oracle:

◦ PostgreSQL:

◦ SQLite:

11/2/2020 K SAILAJA KUMAR 10

History of SQL
◦ Dr. E. F. Codd published the paper, "A Relational Model of Data for Large Shared Data Banks",
in June 1970 in the Association of Computer Machinery (ACM) journal, Communications of
the ACM.
◦ Codd's model is now accepted as the definitive model for relational database management
systems (RDBMS).
◦ The language, Structured English Query Language (SEQUEL) was developed by IBM
Corporation, Inc., to use Codd's model.
◦ SEQUEL later became SQL (still pronounced "sequel").
◦ In 1979, Relational Software, Inc. (now Oracle) introduced the first commercially available
implementation of SQL.
◦ Today, SQL is accepted as the standard RDBMS language.

11/2/2020 K SAILAJA KUMAR 11

What Can SQL do?
◦ SQL can
◦ execute queries against a database
◦ retrieve data from a database
◦ insert records in a database
◦ update records in a database
◦ delete records from a database
◦ create new databases
◦ create new tables in a database
◦ create stored procedures in a database
◦ create views in a database
◦ set permissions on tables, procedures, and views

11/2/2020 K SAILAJA KUMAR 12

SQL comprises of 3 major sub-languages:
1. Data Definition Language (DDL):
◦ to create and modify the structure of the database
2. Data Manipulation Language (DML):
◦ to perform Read, Insert, Update and Delete operations on the
data of the database
3. Data Control Language (DCL):
◦ to control the access of the data stored in the database.

11/2/2020 K SAILAJA KUMAR 13

Table, Field/Column, Row/Record
The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.
Every table is broken up into smaller entities called fields.
A column is a vertical entity in a table that contains all information associated with a specific
field in a table.
A field is a column in a table that is designed to maintain specific information about every record
in the table.
A record/row is a horizontal entity in a table.

11/2/2020 K SAILAJA KUMAR 14

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
Constraints can be column level or table level. Column level constraints apply to a column, and table level
constraints apply to the whole table.
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 - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly

11/2/2020 K SAILAJA KUMAR 15

Data Integrity
Ensures that all data in a database can be traced and connected to other data.
Having a single, well-defined and well-controlled data integrity system increases stability,
performance, reusability and maintainability.
The following three integrity constraints are used in a relational database structure to achieve
data integrity:
Entity Integrity: This is concerned with the concept of primary keys. The rule states that every
table must have its own primary key and that each has to be unique and not null.
Referential Integrity: This is the concept of foreign keys. The rule states that the foreign key
value can be in two states. The first state is that the foreign key value would refer to a primary
key value of another table, or it can be null. Being null could simply mean that there are no
relationships, or that the relationship is unknown.
Domain Integrity: This states that all columns in a relational database are in a defined domain.

11/2/2020 K SAILAJA KUMAR 16

Database Normalization
Normalization is the process of efficiently organizing data in a database.
There are two goals of the normalization process:
◦ eliminating redundant data (for example, storing the same data in more than one table) and
◦ ensuring data dependencies make sense (only storing related data in a table).
Both of these are worthy goals as they reduce the amount of space a database consumes and
ensure that data is logically stored.
During the process of normalization, dependencies can be identified, which can cause problems
when deleting or updating.
Normalization also helps to simplify the structure of the tables.

11/2/2020 K SAILAJA KUMAR 17

Database Normalization
Why it is required?
Normalization reduces redundancy. Redundancy is the unnecessary repetition of data.
It can cause problems with storage, retrieval and updation of data.
Redundancy can lead to:
Inconsistencies:-errors are more likely to occur when facts are repeated.
Update anomalies:-
inserting, modifying and deleting data may cause inconsistencies.
Inconsistency occurs during the updation or deletion of data in one relation, while forgetting to make
corresponding changes in other relations.

11/2/2020 K SAILAJA KUMAR 18

Data Manipulation Language (DML)
- SELECT, INSERT, UPDATE, DELETE, MERGE

11/2/2020 K SAILAJA KUMAR 19

Data Definition Language (DDL)-Create
◦ - CREATE, ALTER, DROP, TRUNCATE,
SQL CREATE TABLE Example
The following example creates a table called "Person" that contains five columns: PersonID,
LastName, FirstName, Address, and City:
CREATE TABLE Person (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

11/2/2020 K SAILAJA KUMAR 20

Data Definition Language (DDL)-DROP
The SQL DROP TABLE Statement
The DROP TABLE statement is used to drop an existing table in a database.
Syntax
DROP TABLE table_name;
SQL DROP TABLE Example
The following SQL statement drops the existing table “Person":
Drop Table Person;

11/2/2020 K SAILAJA KUMAR 21

Data Definition Language (DDL)-Truncate
SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
Syntax
TRUNCATE TABLE table_name;
Truncate Table Person;

11/2/2020 K SAILAJA KUMAR 22

Data Definition Language (DDL)-Alter
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 - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column to the “Person" table:
ALTER TABLE Person
ADD Email varchar(255);

11/2/2020 K SAILAJA KUMAR 23

Data Definition Language (DDL)-Alter
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax
ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL deletes the "Email" column from the “Person” table:
ALTER TABLE Person
DROP COLUMN Email;

11/2/2020 K SAILAJA KUMAR 24

Data Definition Language (DDL)-Alter
ALTER TABLE - ALTER/MODIFY COLUMN
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
MODIFY column_name datatype;
We use the following SQL statement:
ALTER TABLE Persons
MODIFY DateOfBirth date;

11/2/2020 K SAILAJA KUMAR 25

SQL CONSTRAINTS- NOT NULL
SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL
values when the "Persons" table is created:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
SQL NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created,
use the following SQL:
ALTER TABLE Persons
MODIFY Age int NOT NULL;

11/2/2020 K SAILAJA KUMAR 26

SQL CONSTRAINTS- UNIQUE
SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.

CREATE TABLE Persons ( CREATE TABLE Persons (

ID int NOT NULL, ID int NOT NULL,
LastName LastName varchar(255) NOT NULL,
varchar(255) NOT NULL, FirstName varchar(255),
FirstName varchar(255), Age int,
Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName)
UNIQUE (ID) );
);

Can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.

11/2/2020 K SAILAJA KUMAR 27

SQL CONSTRAINTS-PRIMARY KEY
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
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

11/2/2020 K SAILAJA KUMAR 28

SQL CONSTRAINTS- CHECK
The CHECK constraint is used to limit the value range that can be placed in a column.
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 you can not have any person below 18 years:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);

11/2/2020 K SAILAJA KUMAR 29

SQL CONSTRAINTS- DEFAULT
The DEFAULT constraint is used to provide a default value for a column.
The default value will be added to all new records IF no other value is specified.
SQL DEFAULT on CREATE TABLE
CREATE TABLE Persons ( CREATE TABLE Orders (
ID int NOT NULL, ID int NOT NULL,
LastName varchar(255) NOT NULL, OrderNumber int NOT NULL,
FirstName varchar(255), OrderDate date DEFAULT GETDATE()
Age int, );
City varchar(255) DEFAULT 'Sandnes'
);

The DEFAULT constraint can also be used to insert system values, by using functions like
GETDATE():

11/2/2020 K SAILAJA KUMAR 30

SQL CONSTRAINTS- FOREIGN KEY
A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in
another table.
The table containing the FOREIGN KEY is called the child table, and the table containing the
PRIMARY KEY is called the parent table.
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
Person_ID int FOREIGN KEY REFERENCES Persons(PersonID)
);

11/2/2020 K SAILAJA KUMAR 31

 SQL CONSTRAINTS- FOREIGN KEY
"Persons" table:
PersonID LastName FirstName Age
• "PersonID" column in the "Orders" table
1 Hansen Ola 30
points to the "PersonID" column in the
2 Svendson Tove 23
"Persons" table.
3 Pettersen Kari 20
• The "PersonID" column in the "Persons"
"Orders" table: table is the PRIMARY KEY in the
PersonID OrderNumber OrderID Items
"Persons" table.
3 77895 1 Book, Pen, USB
• The "PersonID" column in the "Orders"
3 44678 2 Laptop, Mouse
table is a FOREIGN KEY in the "Orders"
2 22456 3 Book, USB, Mouse
table.
1 24562 4 USB, Keyboard, Mouse

11/2/2020 K SAILAJA KUMAR 32

Database Normalization
Normalization is a process of organizing the data in database to avoid data redundancy,
insertion anomaly, update anomaly & deletion anomaly.
Normalization
The most commonly used normal forms:
First normal form(1NF)
Second normal form(2NF)
Third normal form(3NF)
BCNF
Fourth Normal Form(4NF)

11/2/2020 K SAILAJA KUMAR 33

Database Normalization-1NF
First normal form (1NF)
An attribute (column) of a table cannot hold multiple values. It should hold only atomic values.
A company wants to store the names and contact details of its employees. It creates a as follows

emp_id emp_name emp_address emp_mobile

101 Herschel New Delhi 8912312390
8812121212
102 Jon Kanpur
9900012222
103 Ron Chennai 7778881212
9990000123
104 Lester Bangalore
8123450987

11/2/2020 K SAILAJA KUMAR 34

Database Normalization-1NF
First normal form (1NF)
An attribute (column) of a table cannot hold multiple values. It should hold only atomic values.

emp_id emp_name emp_address emp_mobile

101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
102 Jon Kanpur 9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
104 Lester Bangalore 8123450987

11/2/2020 K SAILAJA KUMAR 35

Database Normalization-2NF
A table is said to be in 2NF if both the following conditions hold:
Table is in 1NF (First normal form)
It should not have Partial dependency
No non-prime attribute is dependent on the proper subset of any candidate key of table.
An attribute that is not part of any candidate key is known as non-prime attribute

A school wants to store the data of teachers and the subjects they teach.
A teacher can teach more than one subjects, the table can have multiple rows for a same
teacher.

11/2/2020 K SAILAJA KUMAR 36

Database Normalization-2NF
teacher_id subject teacher_age
111 Maths 38
Candidate Keys:
111 Physics 38 {teacher_id, subject}
222 Biology 38 Non prime attribute:
teacher_age
333 Physics 40
333 Chemistry 40

The table is in 1 NF because each attribute has atomic values.

It is not in 2NF because non prime attribute teacher_age is dependent on teacher_id
alone which is a proper subset of candidate key.

11/2/2020 K SAILAJA KUMAR 37

Database Normalization-2NF
To make the table complies with 2NF we can break it in two tables like this:

teacher_subject table: teacher_details table:

teacher_id subject teacher_id teacher_age

111 Maths 111 38
111 Physics 111 38
222 Biology 222 38
333 Physics 333 40
333 Chemistry 333 40

11/2/2020 K SAILAJA KUMAR 38

Database Normalization-3NF
Third Normal form (3NF)
A table design is said to be in 3NF if both the following conditions hold:
Table must be in 2NF
It should not have Transitive dependency (A->B, B->C the A->C)
Transitive functional dependency of non-prime attribute on any super key should be removed.
An attribute that is not part of any candidate key is known as non-prime attribute.
In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional
dependency X-> Y at least one of the following conditions hold:
X is a super key of table
Y is a prime attribute of table
An attribute that is a part of one of the candidate keys is known as prime attribute.

11/2/2020 K SAILAJA KUMAR 39

Database Normalization-3NF
Suppose a company wants to store the complete address of each employee as follows

emp_id emp_name emp_zip emp_state emp_city emp_district

1001 John 282005 UP Agra Dayal Bagh
1002 Ajeet 222008 TN Chennai M-City
1006 Lora 282007 TN Chennai Urrapakkam
1101 Lilly 292008 UK Pauri Bhagwan
1201 Steve 222999 MP Gwalior Ratan

Super keys: {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…so

on
Candidate Keys: {emp_id}
Non-prime attributes: all attributes except emp_id are non-prime as they are not
part of any candidate keys.

11/2/2020 K SAILAJA KUMAR 40

Database Normalization-3NF
Here, emp_state, emp_city & emp_district dependent on emp_zip.
And, emp_zip is dependent on emp_id
Hence non-prime attributes (emp_state, emp_city & emp_district) transitively dependent on
super key (emp_id). This violates the rule of 3NF.
To make this table complies with 3NF we have to break the table into two tables to remove the
transitive dependency:
A->B->C
A->C will be separated

11/2/2020 K SAILAJA KUMAR 41

Database Normalization-3NF
employee table: employee_zip table:

emp_n emp_zip emp_state emp_city emp_district

emp_id emp_zip
ame
282005 UP Agra Dayal Bagh
1001 John 282005
222008 TN Chennai M-City
1002 Ajeet 222008
282007 TN Chennai Urrapakkam
1006 Lora 282007
292008 UK Pauri Bhagwan
1101 Lilly 292008
222999 MP Gwalior Ratan
1201 Steve 222999

11/2/2020 K SAILAJA KUMAR 42

SQL Datatypes

11/2/2020 K SAILAJA KUMAR 43

SQL Numeric Data Types
Datatype From To

bit 0 1

tinyint 0 255

smallint -32,768 32,767

int -2,147,483,648 2,147,483,647

-9,223,372,036,
bigint 9,223,372,036, 854,775,807
854,775,808

decimal -10^38 +1 10^38 -1

numeric -10^38 +1 10^38 -1

float -1.79E + 308 1.79E + 308

real -3.40E + 38 3.40E + 38

11/2/2020 K SAILAJA KUMAR 44

SQL Date and Time Data Types
Datatype Description

DATE Stores date in the format YYYY-MM-DD

TIME Stores time in the format HH:MI:SS

DATETIME Stores date and time information in the format YYYY-MM-DD HH:MI:SS

Stores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’
TIMESTAMP
UTC)

Stores year in 2 digit or 4 digit format. Range 1901 to 2155 in 4-digit format. Range
YEAR
70 to 69, representing 1970 to 2069.

11/2/2020 K SAILAJA KUMAR 45

SQL Character and String Data Types
Datatype Description

CHAR Fixed length with maximum length of 8,000 characters

VARCHAR Variable length storage with maximum length of 8,000 characters

Variable length storage with provided max characters, not supported in

VARCHAR(max)
MySQL

TEXT Variable length storage with maximum size of 2GB data

11/2/2020 K SAILAJA KUMAR 46

SQL Unicode Character
and String Data Types
Datatype Description

NCHAR Fixed length with maximum length of 4,000 characters

NVARCHAR Variable length storage with maximum length of 4,000 characters

NVARCHAR(max) Variable length storage with provided max characters

NTEXT Variable length storage with maximum size of 1GB data

11/2/2020 K SAILAJA KUMAR 47

SQL Binary Data Types
Datatype Description

BINARY Fixed length with maximum length of 8,000 bytes

VARBINARY Variable length storage with maximum length of 8,000 bytes

VARBINARY(max) Variable length storage with provided max bytes

IMAGE Variable length storage with maximum size of 2GB binary data

11/2/2020 K SAILAJA KUMAR 48

SQL Miscellaneous Data Types

Datatype Description

CLOB Character large objets that can hold up to 2GB

BLOB For binary large objects

XML for storing xml data

JSON for storing JSON data

11/2/2020 K SAILAJA KUMAR 49

SQL Operators
There are three types of operators in SQL:
SQL Arithmetic Operators:(+,-,/,*)
SQL Comparison Operators: (<, > ,<=,>=,=,!=)
SQL Logical Operators: (&&,||,between, not, exists, like )

11/2/2020 K SAILAJA KUMAR 50

SQL Arithmetic Operators:
Operators Descriptions Examples

+ It is used to add containing values of both operands a+b will give

150

- It subtracts right hand operand from left hand operand a-b will give -
50

* It multiply both operand's values a*b will give

5000
/ It divides left hand operand by right hand operand b/a will give 2

% It divides left hand operand by right hand operand and returns b%a will give 0
reminder

11/2/2020 K SAILAJA KUMAR 51

SQL Comparison Operators:
Operator Description Example
= Examine both operands value that are equal or not,if yes condition become true. (a=b) is not true

!= This is used to check the value of both operands equal or not,if not condition become true. (a!=b) is true

<> Examines the operand's value equal or not, if values are not equal condition is true (a<>b) is true

> Examine the left operand value is greater than right Operand, if yes condition becomes true (a>b) is not true

< Examines the left operand value is less than right Operand, if yes condition becomes true (a<="" td="">

>= Examines that the value of left operand is greater than or equal to the value of right operand (a>=b) is not true
or not,if yes condition become true

<= Examines that the value of left operand is less than or equal to the value of right operand or (a<=b) is true
not, if yes condition becomes true

!< Examines that the left operand value is not less than the right operand value (a!<="" td="">

!> Examines that the value of left operand is not greater than the value of right operand (a!>b) is true

11/2/2020 K SAILAJA KUMAR 52

SQL Logical Operators:
Operator Description
ALL this is used to compare a value to all values in another value set.

AND this operator allows the existence of multiple conditions in an SQL statement.

ANY this operator is used to compare the value in list according to the condition.

BETWEEN this operator is used to search for values, that are within a set of values

IN this operator is used to compare a value to that specified list value

NOT the NOT operator reverse the meaning of any logical operator

OR this operator is used to combine multiple conditions in SQL statements

EXISTS the EXISTS operator is used to search for the presence of a row in a specified table

LIKE this operator is used to compare a value to similar values using wildcard operator

11/2/2020 K SAILAJA KUMAR 53

SELECT Syntax
Select statement used to query or retrieve data from a table in the database.
Syntax
SELECT expressions
FROM tables
WHERE conditions;

11/2/2020 K SAILAJA KUMAR 54

GROUP BY, HAVING, ORDER BY Clauses
Optional clauses in SELECT statement
There are some optional clauses in SELECT statement:
[WHERE Clause] : It specifies which rows to retrieve.
[GROUP BY Clause] : Groups rows that share a property so that the aggregate function can be
applied to each group.
[HAVING Clause] : It selects among the groups defined by the GROUP BY clause.
[ORDER BY Clause] : It specifies an order in which to return the rows.

11/2/2020 K SAILAJA KUMAR 55

Example:student_details;
ID First_name Last_name Age Subject Hobby

1 Amar Sharma 20 Maths Cricket

2 Akbar Khan 22 Biology Football
3 Anthony Milton 25 Commerce Gambling

select the first name of all the students.

SELECT first_name
FROM student_details; Amar
Akbar
Anthony

11/2/2020 K SAILAJA KUMAR 56

Example:student_details;
ID First_name Last_name Age Subject Hobby

1 Amar Sharma 20 Maths Cricket

2 Akbar Khan 22 Biology Football
3 Anthony Milton 25 Commerce Gambling

Retrieve data from more than one column.

select first name and last name of all the students

SELECT first_name, last_name

Amar Sharma
FROM student_details;
Akbar Khan
Anthony Milton

11/2/2020 K SAILAJA KUMAR 57

SQL SELECT DISTINCT / UNIQUE
Student_Name Gender Mobile_Number HOME_TOWN

Rahul Ojha Male 7503896532 Lucknow

Disha Rai Female 9270568893 Varanasi
Sonoo Jaiswal Male 9990449935 Lucknow

SELECT DISTINCT home_town

FROM students HOME_TOWN

Lucknow
Varanasi

11/2/2020 K SAILAJA KUMAR 58

SQL SELECT COUNT
COUNT() function is used with SQL SELECT statement and it is very useful to count the number
of rows in a table having enormous data.
Return the total number of names of employee_table
SELECT COUNT(name) FROM employee_table;
Return the number of records in table.
SELECT COUNT(*) FROM employee_table;
Return the total distinct names of employee_table.
SELECT COUNT(DISTINCT name) FROM employee_table;

11/2/2020 K SAILAJA KUMAR 59

SQL DELETE
SQL DELETE statement is used to delete rows from a table

ID EMP_NAME CITY SALARY

101 Adarsh Singh Obra 20000

102 Sanjay Singh Meerut 21000

103 Priyanka Raipur 25000

Sharma
104 Esha Singhal Delhi 26000

DELETE FROM EMPLOYEE WHERE ID=101;

DELETE FROM EMPLOYEE;

11/2/2020 K SAILAJA KUMAR 60

Sample Questions
Explain the various SQL constraints with suitable examples
Explain data integrity provided by SQL
Describe the normalization forms with suitable examples
Demonstrate the syntax of following Data Definition Language (DDL) commands:
CREATE, ALTER, DROP, TRUNCATE
Demonstrate the syntax of following Data Manipulation Language (DML) commands:
SELECT, INSERT and DELETE
List the various operators supported by SQL and give one example for each
List the various data types supported by SQL and give suitable example

11/2/2020 K SAILAJA KUMAR 61

SELECT Syntax Using Order By Clause
The ORDER BY statement in sql is used to sort the fetched data in either ascending or
descending according to one or more columns.
By default ORDER BY sorts the data in ascending order.
We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort
in ascending order.
SELECT * FROM table_name ORDER BY column_name ASC|DESC

SELECT * FROM Student ORDER BY NAME DESC;

11/2/2020 K SAILAJA KUMAR 62

SELECT Syntax Using Group By
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions like SUM()
, AVG().
Important Points:
GROUP BY clause is used with the SELECT statement.
GROUP BY clause is placed after the WHERE clause.
GROUP BY clause is placed before ORDER BY clause if used any.
Syntax:
SELECT column1, function_name (column2)
FROM table_name
WHERE condition
GROUP BY column1, column2

11/2/2020 K SAILAJA KUMAR 63

SELECT Syntax Using Group By
Group By single column:
SELECT NAME, SUM(SALARY)
FROM Employee
GROUP BY NAME;

11/2/2020 K SAILAJA KUMAR 64

SELECT Syntax Using Group By
Group By multiple columns:
SELECT SUBJECT, YEAR, Count(*)
FROM Student
GROUP BY SUBJECT, YEAR;

11/2/2020 K SAILAJA KUMAR 65

SELECT Syntax Using HAVING Clause
WHERE clause is used to place conditions on columns
Having clause is used to place conditions on groups
Also we can not use the aggregate functions like SUM(), COUNT() etc. with WHERE clause.
So we have to use HAVING clause if we want to use any of these functions in the condition
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition

11/2/2020 K SAILAJA KUMAR 66

SELECT Syntax Using HAVING Clause
SELECT NAME, SUM(SALARY) FROM Employee
GROUP BY NAME
HAVING SUM(SALARY)>3000;

11/2/2020 K SAILAJA KUMAR 67

SQL Functions
(Aggregate and Scalar Functions)
Aggregate functions:
These functions are used to do operations from the values of the column and a single value is returned.
◦ AVG()
◦ COUNT()
◦ FIRST()
◦ LAST()
◦ MAX()
◦ MIN()
◦ SUM()
Scalar functions:
These functions are based on user input and returns a single value.
◦ UCASE()
◦ LCASE()
◦ MID()
◦ LEN()
◦ ROUND()
◦ NOW()
◦ FORMAT()

11/2/2020 K SAILAJA KUMAR 68

 SQL Functions
(Aggregate Functions-AVG)
AVG(): It returns average value after calculating from values in a
numeric column.
Syntax:
SELECT AVG(column_name) FROM table_name;
SELECT AVG(MARKS) AS AvgMarks FROM Students;

Output:

AvgMarks
80

11/2/2020 K SAILAJA KUMAR 69

 SQL Functions
(Aggregate Functions-Count)
COUNT(): It is used to count the number of rows returned in a
SELECT statement.
Syntax:
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) AS NumStudents FROM Stuents;

Output:

NumStudents
5

11/2/2020 K SAILAJA KUMAR 70

 SQL Functions
(Aggregate Functions-Count)
Computing number of students with unique/distinct age.
SELECT COUNT(DISTINCT AGE) AS NumStudents FROM
Students;

Output:

NumStudents
4

11/2/2020 K SAILAJA KUMAR 71

 SQL Functions
(Aggregate Functions-FIRST)
FIRST(): The FIRST() function returns the last value of the
selected column.
Syntax:
SELECT FIRST(column_name) FROM table_name;
SELECT FIRST(MARKS) AS MarksFIRST FROM Students;

Output:

MarksFIRST
90

11/2/2020 K SAILAJA KUMAR 72

 SQL Functions
(Aggregate Functions-LAST)
LAST(): The LAST() function returns the last value of the
selected column.
Syntax:
SELECT LAST(column_name) FROM table_name;
SELECT LAST(MARKS) AS MarksLast FROM Students;

Output:

MarksLast
85

11/2/2020 K SAILAJA KUMAR 73

 SQL Functions
(Aggregate Functions-MAX)
MAX(): The MAX() function returns the maximum value of the
selected column.
Syntax:
SELECT MAX(column_name) FROM table_name;
SELECT MAX(MARKS) AS MaxMarks FROM Students;

Output:

MaxMarks
95

11/2/2020 K SAILAJA KUMAR 74

 SQL Functions
(Aggregate Functions-MIN)
MIN(): The MIN() function returns the maximum value of the
selected column.
Syntax:
SELECT MIN(column_name) FROM table_name;
SELECT MIN(MARKS) AS MinMarks FROM Students;

Output:

MinMarks
50

11/2/2020 K SAILAJA KUMAR 75

 SQL Functions
(Aggregate Functions-SUM)
SUM(): The SUM() function returns the sum of all the values of
the selected column.
Syntax:
SELECT SUM(column_name) FROM table_name;
SELECT SUM(MARKS) AS TotalMarks FROM Students;

Output:

TotalMarks
400

11/2/2020 K SAILAJA KUMAR 76

SQL Functions
(SCALAR Functions-UCASE)
UCASE(): It converts the value of a field to uppercase.
Syntax:
SELECT UCASE(column_name) FROM table_name;
SELECT UCASE(NAME) FROM Students;

Output:

NAME
HARSH
SURESH
PRATIK
DHANRAJ
RAM

11/2/2020 K SAILAJA KUMAR 77

SQL Functions
(SCALAR Functions-LCASE)
LCASE(): It converts the value of a field to lowercase.
Syntax:
SELECT LCASE(column_name) FROM table_name;
SELECT LCASE(NAME) FROM Students;

Output:

NAME
harsh
suresh
pratik
dhanraj
ram

11/2/2020 K SAILAJA KUMAR 78

 SQL Functions
(SCALAR Functions-MID)
MID(): The MID() function extracts texts from the text field.
Syntax:
SELECT MID(column_name,start,length) AS some_name FROM
table_name;

specifying length is optional here, and start signifies start

position (starting from 1 )
SELECT MID(NAME,1,4) FROM Students;
NAME
HARS
SURE
PRAT
DHAN
RAM

11/2/2020 K SAILAJA KUMAR 79

 SQL Functions
(SCALAR Functions-LEN)
LEN(): The LEN() function returns the length of the value in a
text field.
Syntax:
SELECT LENGTH(column_name) FROM table_name;
SELECT LENGTH(NAME) FROM Students;

Output:
NAME
5
6
6
7
3

11/2/2020 K SAILAJA KUMAR 80

SQL Functions
(SCALAR Functions-ROUND)
ROUND(): The ROUND() function is used to round a numeric
field to the number of decimals specified.
Syntax:

SELECT ROUND(column_name,decimals) FROM table_name;

decimals- number of decimals to be fetched.

SELECT ROUND(MARKS,0) FROM table_name;
Output:
MARKS
90
50
80
95
85

11/2/2020 K SAILAJA KUMAR 81

SQL Functions
(SCALAR Functions-NOW)
NOW(): The NOW() function returns the current system date
and time.
Syntax:
SELECT NOW() FROM table_name;
SELECT NAME, NOW() AS DateTime FROM Students;

Output:
NAME DateTime
HARSH 1/13/2017 1:30:11 PM
SURESH 1/13/2017 1:30:11 PM
PRATIK 1/13/2017 1:30:11 PM
DHANRAJ1/13/2017 1:30:11 PM
RAM 1/13/2017 1:30:11 PM

11/2/2020 K SAILAJA KUMAR 82

 SQL Functions
(SCALAR Functions-FORMAT)
FORMAT(): The FORMAT() function is used to format how a
field is to be displayed.
Syntax:
SELECT FORMAT(column_name,format) FROM table_name;
SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM
Students;
Output:
NAME Date
HARSH 2017-01-13
SURESH 2017-01-13
PRATIK 2017-01-13
DHANRAJ2017-01-13
RAM 2017-01-13

11/2/2020 K SAILAJA KUMAR 83