Silver Oak Group of Institutes

Silver Oak College of Computer Application

UNIT 4: Data Manipulation Commands

Topics:
➢ Adding Table Rows Saving Table Rows Listing Table
➢ Rows Updating Table Rows Restoring Table Rows
➢ Deleting Table Rows
➢ Select Queries: With Conditional Restrictions Arithmetic Operators  Logical Operators: AND, OR &
NOT Special Operators: Between IS NULL Like IN
Advanced Data Definition Commands:
➢ Changing a Column's Data Types
➢ Changing a Column's Data
➢ Characteristics
➢ Adding a Column
➢ Dropping a Column
➢ Column Advanced
➢ Data Updates
➢ Adding Primary and Foreign Key Deleting a Table from the Database
➢ Aggregate Function
SQL View

What is SQL?

● SQL stands for Structured Query Language

● SQL lets you access and manipulate databases
● SQL is an ANSI (American National Standards Institute) standard
● Structured Query Language (SQL) is the language used to manipulate relational databases. SQL is tied very closely with the
relational model.
● In the relational model, data is stored in structures called relations or tables.
● Each table has one or more attributes or columns that describe the table.
● In relational databases, the table is the fundamental building block of a database application. Tables are used to store data on
Employees, Equipment, Materials, Warehouses, Purchase Orders, Customer Orders, etc. Columns in the Employee table, for
example, might be Last Name, First Name, Salary, Hire Date, Social Security Number, etc.

What Can SQL do?

● 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

DDL, DML, DCL, TCL

DDL - Data Definition Language: statements used to define the database structure or schema.

CREATE - to create objects in the database

ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML - Data Manipulation Language: statements used for managing data within schema objects.

SELECT - retrieve data from the a database

INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remains

DCL - Data Control Language.

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows
statements to be grouped together into logical transactions.

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit

Basic Statements sequence of oracle for writing SQL.

SELECT (COLUMN LIST)

FROM (TABLE NAME)
[WHERE (CONDITION )]
[GROUP BY (COLUMN NAME)]
[HAVING (CONDTION)]
[ORDER BY (EXPRESSION)]
Select

The SELECT statement allows you to retrieve records from one or more tables in your database.

This operation is also called projection.

Syntax
SELECT columns
FROM tables
WHERE predicates;

Examples

Select Queries

All columns

select * from dept;

Selected columns

select LOC, DEPTNO from DEPT;

Selected columns with Alias

select Sname,Sno as Rollno,Maths as Vigaan from Student

Add Selected columns and also perform Mathematical operations on column

Select Sno,Sname,Eng+Maths as Total , Fees , Fees*0.10 as , Fees + Fees*0.10 after increment from Student

Where Conditions

Selected columns with > condition

SELECT name, city, state FROM suppliers WHERE supplier_id > 1000

All columns with > and < condition

SELECT * FROM emp where sal>2000 and sal<5000

Display All columns whose name is Ram

SELECT * FROM emp where name=’Ram’

Display All columns whose name is not Raj

SELECT * FROM emp where name !=’Raj’

Display All columns who belongs to city Abad , Surat

SELECT * FROM emp where city=’surat’ or city=’Abad’

Display Names who are studying in std 5

Select name from student where std=5

Like Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

● The percent sign (%) represents zero, one, or multiple characters

● The underscore sign (_) represents one, single character

SELECT * FROM emp where ename like ‘A%’

SELECT * FROM emp where ename like ‘%a’

SELECT * FROM emp where ename like ‘%a%’

SELECT * FROM emp where ename like ‘_a%’

SELECT * FROM emp where ename like ‘%a_’

SELECT * FROM emp where ename like ‘__a%’

SELECT * FROM emp where ename like ‘%a__’

SELECT * FROM emp where ename like ‘a%’ or ename like ‘k%’

SELECT * FROM emp where ename not like ‘a%’ and ename not like ‘k%’

SELECT * FROM emp where ename like ‘_____’

Between Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

select * from emp

where salary is between 10000 and 20000

select * from emp

where DateofBirth is between '1-jan-2000' and '1-jan-2015'

SELECT * FROM Products WHERE OfferPrice NOT BETWEEN 100 AND 200

What is a NULL Value?

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.

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

SELECT column_names FROM table_name WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

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.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

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:

Syntax:

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE Customers ADD Email varchar(255);

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting
a column):

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

The following SQL deletes the "Email" column from the "Customers" table:

Example
ALTER TABLE Customers DROP COLUMN Email;

ALTER TABLE - RENAME COLUMN

To rename a column in a table, use the following syntax:

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

SQL ALTER TABLE Example

Look at the "Persons" table:

ID LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:
ALTER TABLE PersonsADD DateOfBirth date;

The "Persons" table will now look like this:

ID LastName FirstName Address City DateOfBirth

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Change Data Type Example

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons ALTER COLUMN DateOfBirth year;

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-digit
format.

DROP COLUMN Example

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons DROP COLUMN DateOfBirth;

The "Persons" table will now look like this:

ID LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

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.

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.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

ALTER TABLE Persons ALTER COLUMN Age int NOT NULL;

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.

CREATE TABLE Persons (

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

ADD a UNIQUE Constraint

ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

ALTER TABLE Persons DROP CONSTRAINT UC_Person;

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).

CREATE TABLE Persons (

ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple
columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

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.
Look at the following two tables:

Persons Table
PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20

Orders Table
OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2
4 24562 1

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.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because
it has to be one of the values contained in the parent table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons (PersonID)
);
DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;

SQL Aggregate Functions

o SQL aggregation function is used to perform the calculations on multiple rows of a single column of a
table. It returns a single value.
o It is also used to summarize the data.

Types of SQL Aggregation Function

1. COUNT FUNCTION
o COUNT function is used to Count the number of rows in a database table. It can work on both numeric
and non-numeric data types.
o COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table.
COUNT(*) considers duplicate and Null.
Syntax
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )

Sample table:

PRODUCT_MAST
PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Query:
SELECT COUNT (*) FROM PRODUCT_MAST;
COUNT with WHERE
SELECT COUNT (*) FROM PRODUCT_MAST WHERE RATE>=20;

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax
SUM()
or
SUM( [ALL|DISTINCT] expression )

Example: SUM()
SELECT SUM(COST) FROM PRODUCT_MAST;

Example: SUM() with WHERE

SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3;

3. AVG function
The AVG function is used to calculate the average value of the numeric type. AVG function returns the average
of all non-Null values.

Syntax
AVG()
or
AVG( [ALL|DISTINCT] expression )

Example:
SELECT AVG(COST)
FROM PRODUCT_MAST;

4. MAX Function
MAX function is used to find the maximum value of a certain column. This function determines the largest
value of all selected values of a column.

Syntax
MAX()
or
MAX( [ALL|DISTINCT] expression )

Example:
SELECT MAX(RATE) FROM PRODUCT_MAST;

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest
value of all selected values of a column.

Syntax
MIN()
or
MIN( [ALL|DISTINCT] expression )

Example:
SELECT MIN(RATE)
FROM PRODUCT_MAST;

SQL VIEW
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.
You can add SQL statements and functions to a view and present the data as if the data were coming from one
single table.
A view is created with the CREATE VIEW statement.

CREATE VIEW Syntax

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example
The following SQL creates a view that shows all customers from Brazil:
Example
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';