Date:

Ex No: 1 DDL and DML

Practice Exercise

Note: Students should append last 4 digit of their register number with table name

given.

For example student with register number 111717104001 should create table as

BRANCH4001, Student4001, markgrade4001, project4001.

1. Create a table named BRANCH with following Structure

Data Field Name Data Type Constraint

Branch Number branchno int(1) Primary Key

Branch branchname varchar(30) Not Null

2 Create a table named STUDENT with following Structure

Data Field Name Data Type Constraint

Student Name name varchar(30) Not Null

Student Number registerno int(12) Primary Key
Branch Number branchno int(1) Foreign Key
Section sec varchar(1) Not Null
Joined Date joindate date Not Null
Mark mark int(3) Not Null

3. Add the column emailid to table student with Constraint UNIQUE.

4. Create a table named MARKGRADE with following Structure

Data Field Name Data Type Constraint

Grade grade varchar(1) Not Null

Lowest Mark lowmark int(3) Not Null
Highest Mark highmark int(3) Not Null

5. Create a table named PROJECT with following Structure

Data Field Name Data Type Constraint

Project Number pno int(3) Primary Key

Project Name pname varchar(60)
Project Manager pmgr int(4) Not Null
Persons persons int(3)
Budjet budjet int(8)
Project Start date pstart date Not Null
Project End Date pend date
Insert, Select , Update and Delete Commands.
Theory

 Data Retrieval: Select

 Data Manipulation Language (DML): Insert, Delete, Update

Data Manipulation Language (Insert, Delete, Update)

Data Manipulation Language (DML) is used by computer programs or database users to

retrieve, insert, delete and update data in a database. Currently, the most popular data
manipulation language is that of SQL, which is used to retrieve and manipulate data in a
Relational database

Data Manipulation Languages have their functional capability organized by the initial
word in a statement, which is almost always a verb. In the case of SQL, these verbs are:

 SELECT

An SQL SELECT statement returns a result set of records from one or more tables. It
retrieves zero or more rows from one or more base tables or views in a database. In
most applications, SELECT is the most commonly used Data Manipulation Language
(DML) command.

Example:

SELECT * FROM student;

* means all columns in a table

The SELECT statement has many optional clauses:

o WHERE specifies which rows to retrieve.

A WHERE clause in SQL specifies that a SQL Data Manipulation

Language (DML) statement should only affect rows that meet a specified
criteria. WHERE clauses are not mandatory clauses of SQL DML statements, but
should be used to limit the number of rows affected by a SQL DML statement
or returned by a query. WHERE is an SQL reserved word.

EXAMPLE : SELECT * FROM student WHERE mark >

90

o GROUP BY groups rows sharing a property so that an aggregate function

can be applied to each group.
A GROUP BY statement in SQL specifies that a SQL SELECT statement
returns a list that is grouped by one or more columns, usually in order to
apply some sort of aggregate function to certain columns.

Common grouping (aggregation) functions include:

 Count(expression) - Quantity of matching records (per
group)
 Sum(expression) - Summation of given value (per group)
 Min(expression) - Minimum of given value (per group)
 Max(expression) - Maximum of given value (per group)
 Avg(expression) - Average of given value (per group)

SELECT branchno, AVG(mark) FROM student

GROUP BY branchno;

o HAVING selects among the groups defined by the GROUP BY clause.

A HAVING statement in SQL specifies that a SQL SELECT statement should

only return rows where aggregate values meet the specified conditions.

Example:

SELECT branchno, AVG(mark) FROM student

GROUP BY branchno
HAVING AVG(mark) > 80;

o ORDER BY specifies an order in which to return the rows.

An ORDER BY clause in SQL specifies that a SQL SELECT statement returns

a result set with the rows being sorted by the values of one or more columns.
ORDER BY is the only way to sort the rows in the result set. Without this clause, the
relational database system may return the rows in any order. If an ordering is
required, the ORDER BY must be provided in the SELECT statement sent by the
application.

Example:

SELECT * FROM student ORDER BY registerno;

SELECT * FROM student ORDER BY mark;

 INSERT

The number of columns and values must be the same. The values specified (or
implied) by the INSERT statement must satisfy all the applicable constraints (such as
primary keys, CHECK constraints, and NOT NULL constraints). If a syntax error occurs or if
any constraints are violated, the new row is not added to the table and an error returned
instead.
Syntax:

INSERT INTO table (column1, [column2, ... ]) VALUES (value1, [value2, ...])

INSERT INTO table VALUES (value1, [value2, ...])

Example:

 INSERT INTO
student(name,registerno,branchno,section,joindate,mark,email
id) VALUES ('Aarthi',11306205001,1,'A','01-apr-2006',99,
'[email protected]')

 INSERT INTO student VALUES ('Aarthi',11306205001,1,'A','01-

apr-2006',99, '[email protected]')

Note that there are six values specified for the record. These correspond to the
table attributes in the order they were defined: name, registerno, branchno, section,
joindate, mark

 UPDATE

A SQL UPDATE statement that changes the data of one or more records in a table.
Either all the rows can be updated, or a subset may be chosen using a condition.

Syntax:

UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE

condition]

For the UPDATE to be successful, the user must have data manipulation privileges (UPDATE
privilege) on the table or column, the updated value must not conflict with all the applicable
constraints (such as primary keys, unique indexes, CHECK constraints, and NOT NULL
constraints).

Example:

UPDATE student
SET mark = mark + 2
WHERE registerno = 11306205001;

 DELETE
An SQL DELETE statement removes one or more records from a table. A subset may be
defined for deletion using a condition, otherwise all records are removed.

Syntax:

DELETE FROM table_name [WHERE condition]

Any rows that match the WHERE condition will be removed from the table. If the WHERE clause
is omitted, all rows in the table are removed. The DELETE statement should thus be used with
caution!

The DELETE statement does not return any rows; that is, it will not generate a result set.

Example :
DELETE FROM student
WHERE registerno = 11306205001;

Practice Exercise

1. Insert following Values into the Table Branch.

2. Insert following Values into the Table Student.

Alter data type of register number column to bigint

3. Insert following Values into the Table Markgrade.

4. Display the contents of Branch Table.

5. Display the contents of Student Table.

6. Display the contents of Markgrade Table.

7. Make all the Informations Permanent to the Database.

8. Display the details of students who are in branchnumber 2.

9. Display the detail of grade for the mark between 81 and 90.

10. Display the name of the branch with branch number 5.

11. Display the Number of students in each branch.

12. Display the Average mark of each branch.

13. Display the Maximum mark of each branch.

14. Display the Maximum mark of branch where Maximum Mark greater than 90.
15. Display the Name, Register Number, E-Mail ID and Join Date of Students who have

joined later 25th July 2008.

16. Update the Section to C where section is B.

17. Update the marks of Student with Branch Number 3 to 100.

18. Delete the details of student with register number 11305104001.

19. Delete All records from Student Table.

20. Delete records from branch where branch Name is Electrical.

Result

Thus to write SQL using Data Definition Commands, Data Manipulation Commands

for inserting, deleting, updating and retrieving Tables and Transaction Control

statements is successfully Completed.