Experiment 1

Aim: Introduction to DDL and DML

THEORY:
DBMS
A DBMS (Database Management System) is a software Experiment used to
manage a database. These Experiments enable users to access and modify
database
A DBMS is a complex set of software Experiments that controls the
organization, storage, management, and retrieval of data in a database.
DDL:
A data definition language or data description language (DDL) is a syntax
similar to a computer Experimenting language for defining data structures,
especially database schemas.
Many data description languages use a declarative syntax to define fields and data
types. SQL, however, uses a collection of imperative verbs whose effect is to
modify the schema of the database by adding, changing, or deleting definitions of
tables or other objects. These statements can be freely mixed with other SQL
statements, so the DDL is not truly a separate language. Following are the various
DDL commands.

1. Create:
- To make a new database, table, index, or stored procedure
CREATE TABLE statement
A commonly used CREATE command is the CREATE TABLE command. The typical
usage is:
CREATE TABLE [table name] ( [column definitions] ) [table parameters].
column definitions: A comma-separated list consisting of any of the following

Column definition: [column name] [data type] {NULL | NOT NULL} {column
options}
Primary key definition: PRIMARY KEY ( [comma separated column list] )
Constraints: {CONSTRAINT} [constraint definition]
RDBMS specific functionality

For example, the command to create a table named employees with a few sample
columns would be:
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

2. Alter
Alter - To modify an existing database object.
An ALTER statement in SQL changes the properties of an object inside of a
relational database management system (RDBMS). The types of objects that can
be altered depends on which RDBMS is being used. The typical usage is:
ALTER object type object name parameters.

For example, the command to add (then remove) a column named bubbles for an
existing table named sink would be:
P_Id

LastName

FirstName

Address

City

Hansen

Ola

Timoteivn 10

Sandnes

Svendson

Tove

Borgvn 23

Sandnes

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 Persons
ADD DateOfBirth date;

3. Drop
Drop - To destroy an existing database, table, index, or view.
For example, the command to drop a table named employees
would be: DROP TABLE employees;
The DROP statement is distinct from the DELETE and TRUNCATE
statements, in that DELETE and TRUNCATE do not remove the table
itself. For example, A DELETE statement might delete some (or all)
data from a table while leaving the table itself in database, whereas
DROP statement would remove entire table from database.

DML:
A data manipulation language (DML) is a family of syntax elements similar to a
computer Experimenting language used for inserting, deleting and updating data in
a database. Performing read-only queries of data is sometimes also considered a
component of DML.

A popular data manipulation language is that of Structured Query Language

(SQL), which is used to retrieve and manipulate data in a relational database.
Data manipulation language is used to append, change or remove data in a table.
In the case of SQL, the verbs are:

SELECT ... FROM ... WHERE...

INSERT INTO ... VALUES...

UPDATE ... SET ... WHERE...

DELETE FROM ... WHERE...

1. Select:
Select clause is used to list the attributes desired in the result of a query. It
corresponds to the projection operation of the relational algebra.
Example: SELECT CustomerName,City FROM Customers;

2. Insert:
A newly created relation is empty initially. We can use the insert
command to load data into the relation.
insert into <table name> values(A1,A2,An)
The values are specified in the order in which the corresponding
attributes are listed in the relation schema.

3. Update:
In certain situations we may wish to change a value in a tuple without changing all
the values in the tuple. For this purpose, the update statement can be used.
Example: update
EMPLOYEE set
age=20
Where SSN=514065 ;

SQL provides a case construct which we can use to perform both the update with
a single update statement avoiding the problem with the order of updates.
Example : Update
account set
balance =case
when
balance<=1000
then balance*1.05
else balance*1.06
end ;

4. Delete:
To delete a tuple from relation r, we use the following command delete from r
where r is the name of the relation
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria
Anders';

Experiment 2
Aim: To create Table queries using the following constraints
Primary Key constraint
Foreign Key constraint
Check Constraint
Unique Constraint
Not null constraint
Theory:
1. Primary key
The PRIMARY KEY constraint uniquely identifies each record in a
database table. Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary
key.

2. Foreign Key
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

3. Check:
The check clause in SQL can be applied to relation declarations as well to domain
declarations when applied to a relation declaration, the clause check (p) specified a
predicate p that must be specified by every tuple in a relation. A common use of
the check clause is to ensure that the attribute value satisfy specified condition.

4. Unique
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for
uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.

5) Not Null:
The not null specification prohibits the insertion of a null value.
For a attribute any database modification that would cause null to be inserted in an
attribute declared to be not null generates an error diagnostic. If an attribute is
declared as the primary key then it cannot take a null value.

Query:

Output
Table created.

CREATE TABLE Persons

(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Experiment 3
Aim: To perform the function of Insert statement in table.
Theory: The INSERT INTO statement is used to insert new
records in a table.
Query:
1) A new table is create to further insert records in the table.
CREATE TABLE CUSTOMERS(
ID INT

NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT

NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),
PRIMARY KEY (ID) ) ;

2) A new record is inserted using the Insert Command.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

3) Similarly, the other records are inserted. The statements are :

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

Experiment 4
Aim: To perform the function of update statement with and
without WHERE clause.
THEORY: The UPDATE statement is used to update existing records in
a table. Notice the WHERE clause in the SQL UPDATE statement!
The WHERE clause specifies which record or records that should be
updated. If you omit the WHERE clause, all records will be updated!
Update Statement:
In certain situations we may wish to change a value in a tuple without
changing all the values in the tuple. For this purpose, the update
statement can be used.
Example : UPDATE Customers

SET ContactName='Alfred Schmidt', City='Hamburg'

WHERE CustomerName='Alfreds Futterkiste';

Query:
1) SELECT * FROM LOT ;

2) UPDATING TABLE LOT WITHOUT WHERE CLAUSE :

UPDATE LOT
SET DEPT= IT;

3) SELECT * FROM VEHICLE ;

4) UPDATING TABLE LOT WITH WHERE CLAUSE :

UPDATE VEHICLE
SET COLOR='PINK' WHERE MODEL= 'SPARK';

Experiment 5

Aim: To perform the function of select statement using

Boolean operators, logical operators, IN, BETWEEN, LIKE
etc.
THEORY: 1) The BETWEEN operator selects values within a range.
The values can be numbers, text, or dates.
2) The IN operator allows you to specify multiple values in a WHERE
clause.
3) The LIKE operator is used to search for a specified pattern in a
column.
STATEMENTS:
1) LIKE:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern ;

2) BETWEEN:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2 ;

3) IN:

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

Query:
1)

BETWEEN:

SELECT * FROM P_SPACE

SELECT * FROM P_SPACE

WHERE SPACE_NO BETWEEN 30 AND 65

2)

IN:

SELECT * FROM P_SPACE

WHERE DEPT IN ('COE')

3)

LIKE:

SELECT * FROM P_SPACE

WHERE VEHICLE_NO LIKE '%5%'

Experiment 6

Aim: To perform the function of GROUP BY clause and ORDER

BY clause.
THEORY: 1) The GROUP BY statement is used in conjunction with the
aggregate functions to group the result-set by one or more columns.
2) The ORDER BY keyword is used to sort the result-set by one or
more columns. The ORDER BY keyword sorts the records in ascending
order by default. To sort the records in a descending order, you can
use the DESC keyword.

STATEMENTS:
1) GROUP BY:

SELECT column_name, aggregate_function(column_name)

FROM table_name
WHERE column_name operator value
GROUP BY column_name ;

2) ORDER BY:

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC ;

Query:
1) GROUP BY:
SELECT * FROM VEHICLE

SELECT COLOR, COUNT (ID)

FROM VEHICLE
GROUP BY COLOR

2) ORDER BY:
SELECT * FROM VEHICLE
ORDER BY ID ASC

SELECT * FROM VEHICLE

ORDER BY ID DESC

Experiment 7
Aim: To perform the function of DELETE statement with and
without WHERE clause.

THEORY: The DELETE statement is used to delete rows in a table.

Notice the WHERE clause in the SQL DELETE statement! The WHERE
clause specifies which record or records that should be deleted. If you
omit the WHERE clause, all records will be deleted!
Delete Statement:
In certain situations we may wish to erase a value in a tuple without
changing all the values in the tuple. For this purpose, the delete
statement can be used.
Example: DELETE FROM table_name
WHERE some_column=some_value ;

Query:
1) SELECT * FROM CUSTOMERS
Note: Using table not from the Database.

1) DELETE *WITH WHERE CLAUSE:

DELETE FROM CUSTOMERS
WHERE ID = 1

2) DELETE *WITHOUT WHERE CLAUSE:

DELETE FROM CUSTOMERS
Note: All the record gets Deleted without using WHERE

Experiment 8

clause.

Aim: To perform the function of ALTER statement for adding

new columns and constraints, dropping columns and
constraints, modifying column type etc.
THEORY: The ALTER TABLE statement is used to add, delete, or
modify columns in an existing table.
SQL ALTER TABLE Syntax
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype
To delete a column in a table, use the following syntax (notice that some database
systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name

Query:
1) Adding a new Column:
ALTER TABLE P_SPACE
ADD VEHICLE_TYPE VARCHAR (10)

2) Dropping a Column:

ALTER TABLE P_SPACE

DROP COLUMN VEHICLE_TYPE

3) Modify:
ALTER TABLE VEHICLE
ADD YEAR INT

ALTER TABLE VEHICLE

MODIFY YEAR VARCHAR (10)

4) Adding a Constraint:
ALTER TABLE VEHICLE
DROP PRIMARY KEY

ALTER TABLE VEHICLE

ADD CONSTRAINT PK PRIMARY KEY (ID)

Experiment 9
Aim: To perform the function of inbuilt SQL Functions like
SUM, MAX, MIN, AVG, COUNT, DISTINCT etc.

THEORY: SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:

AVG() - Returns the average value

COUNT() - Returns the number of rows

FIRST() - Returns the first value

LAST() - Returns the last value

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

Query:
1) ALTER TABLE P_SPACE
ADD PARK_HOURS INT
SELECT * FROM P_SPACE
UPDATE P_SPACE
SET PARK_HOURS = 5 WHERE SPACE_NO = 34
UPDATE P_SPACE
SET PARK_HOURS = 6 WHERE SPACE_NO = 44
UPDATE P_SPACE
SET PARK_HOURS = 8 WHERE SPACE_NO = 54
UPDATE P_SPACE
SET PARK_HOURS = 4 WHERE SPACE_NO = 64
UPDATE P_SPACE SET PARK_HOURS = 5 WHERE SPACE_NO = 74

2) SUM() Returns the Sum :

SELECT SUM (PARK_HOURS)

FROM P_SPACE

3) MAX() Returns the Largest Value :

SELECT MAX (PARK_HOURS)
AS MAX_PARKING_HOURS
FROM P_SPACE

4) MIN() Returns the Smallest Value :

SELECT MIN (PARK_HOURS)
AS MIN_PARKING_HOURS
FROM P_SPACE

5) AVG() Returns the Average Value :

SELECT AVG (PARK_HOURS)
AS AVG_PARKING_HOURS FROM

6) COUNT() Returns the Number

Of Rows :
SELECT COUNT (*) FROM P_SPACE
WHERE DEPT = 'IT'

Experiment 10

P_SPACE

Aim: To create and modify the Sequences.

THEORY: A sequence is a set of integers 1, 2, 3 that are generated in order on
demand. Sequences are frequently used in databases because many applications
require each row in a table to contain a unique value, and sequences provide an easy
way to generate them. Use the CREATE SEQUENCE statement to create
a sequence, which is a database object from which multiple users may generate
unique integers. You can use sequences to automatically generate primary key
values.
Sequence numbers are generated independently of tables, so the same sequence
can be used for one or for multiple tables. It is possible that individual sequence
numbers will appear to be skipped, because they were generated and used in a
transaction that ultimately rolled back. Additionally, a single user may not realize
that other users are drawing from the same sequence. After a sequence is
created, you can access its values in SQL statements with the CURRVAL pseudo
column, which returns the current value of the sequence, or the NEXTVAL pseudo
column, which increments the sequence and returns the new value.

Query:
1) CREATE SEQUENCE DIV1
START WITH 25 INCREMENT BY 4

2) At the time of Insertion:

INSERT INTO P_SPACE VALUES
(DIV1.NEXTVAL, 98747,'BT', 4)
INSERT INTO P_SPACE VALUES
(DIV1.NEXTVAL, 98747,'BT', 4)

Experiment 11

Aim: To create and modify the Indexes.

THEORY: An index can be created in a table to find data more quickly and
efficiently.
The users cannot see the indexes, they are just used to speed up
searches/queries.
Note: Updating a table with indexes takes more time than updating a table without
(because the indexes also need an update). So you should only create indexes on
columns (and tables) that will be frequently searched against.

Query:
1) SELECT * FROM P_SPACE

2) CREATE INDEX INDEX1

ON P_SPACE (SPACE_NO, DEPT)
3)

Dropping an Index :
DROP INDEX INDEX1

Experiment 12
Aim: To create and modify the View.
THEORY: 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 functions, WHERE, and JOIN statements to a view and present
the data as if the data were coming from one single table.

Query:
1) CREATE VIEW DIVY AS
SELECT SPACE_NO, DEPT
FROM P_SPACE
WHERE DEPT= 'IT'
2) SELECT * FROM DIVY

3) UPDATE DIVY SET DEPT='COE' WHERE SPACE_NO = 34