DATABASE NOTES

DOWNLOAD

In a database you can define the structure of the data and manipulate the data using some commands. There are two types of languages for this task. These are:

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

Data Definition Language (DDL)

A data definition language or data description language (DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users.

Common DDL statements are CREATE, ALTER, and DROP.

Data Manipulation Language (DML)

A data manipulation language (DML) is a language that enables users to access and manipulate data in a database. The goal is to provide efficient human interaction with the system. Data manipulation involves:

• Retrieval of information from the database- SELECT statement

• Insertion of new information into the database - INSERT statement

• Deletion of information in the database - DELETE statement

• Modification of information in the database - UPDATE statement

A query language is a part of DML involving information retrieval only. The terms DML and query language are often used synonymously. A popular data manipulation language is Structured Query Language (SQL). This is used to retrieve and manipulate data in a relational database. Other forms of DML are those used by IMS/DLI, CODASYL databases, such as IDMS and others. Data manipulation language comprises the SQL data change statements, which modify stored data but not the schema or database objects.

There are two types of DML:

• Procedural: The user specifies what data is needed and how to get it

• Nonprocedural: The user only specifies what data is needed. This is easier for the user but may not generate code as efficient as that produced by procedural languages.

A form provides the user a systematic way of storing information into the database. It is an interface in a user specified layout that lets users to view, enter, and change data directly in database objects such as tables.

A report is used to generate the overall work outcome in a clear format. You can also create reports in database.

To retrieve all the columns in a table the syntax is:	SELECT * FROM <TABLENAME>;
To retrieve specific columns in a table then the syntax is:	SELECT COLUMN_NAME1, COLUMN_NAME2, … FROM <TABLENAME>;
To retrieve all the columns in a table with a condition then the syntax is:	SELECT * FROM <TABLENAME> where condition; Eg: select * from student where admission_no = 1234;

INSERT statement is used to add one or more records to a database. The general syntax of the

insert statement is shown below.

INSERT INTO <table_name> (column1, column2, column3,...)

VALUES (value1, value2,value3,...);

Note:

1. Numerical values can be written in normal, whereas text literals has to enclose in single quotes.

2. Values order / sequence has to follow the same order / sequence as column names specified.

3. We can insert values into all the columns or specific columns.

Update statement is used for modifying records in a database. The general syntax of the update statement is as follows:

UPDATE <table_name>

SET <column_name> =  NEW_VALUE

WHERE <condition>;

Note:

1.  If we forget to write the where condition, all the values in the specified column of table will updates with the NEW_VALUE.

2. We can update more the one column values at a time.

Delete Statement is used to remove one or more records in a database. The general syntax of the delete statement is as follows:

DELETE FROM <table_name> WHERE <condition>;

If we forget to write where condition, this query will deletes all the rows of the table.

Create statement is used for creating a database or a table in any RDBMS Software. A commonly used CREATE command is the CREATE TABLE command. The general syntax of the create statement is shown below.

CREATE TABLE <TABLENAME> ([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])

For example, if you would like to create a table using the Create statement, type the following and click Execute.

CREATE TABLE Employee

(

ID INTEGER, Name VARCHAR (50),

Department VARCHAR (50),

Address VARCHAR (120),

Contact_Number INTEGER

);