dbms

SQLSQL(Structured Query (Structured Query Language)Language)

Presented by: Manohar Prasad Specialization: IB+IT Roll No.- 1999/01030003

Structured Query Language (SQL)

SQL allows user to access data in relational database management systems, such as Oracle, Access, SQL Server, FoxPro, Informix and others, by allowing users to describe the data the user wishes to see.

04/11/2304/11/23 22www.manoharprasad.wordpress.cowww.manoharprasad.wordpress.comm

SQL statements are issued for the SQL statements are issued for the purpose of:purpose of:

Data definitionData definition - Defining tables and - Defining tables and structures in the database . structures in the database .

Data manipulationData manipulation - Inserting new data, - Inserting new data, Updating existing data, Deleting existing Updating existing data, Deleting existing data, and Querying the Database ( Retrieving data, and Querying the Database ( Retrieving existing data from the database). existing data from the database).


DDL StatementsDDL Statements

CREATECREATE - Create new database objects such as tables or - Create new database objects such as tables or views views

DROPDROP - Drop a database object such as a table, view - Drop a database object such as a table, view or index or index

ALTERALTER - Change an existing table, view or index - Change an existing table, view or index definitiondefinition

GRANTGRANT - Allow another user to access database objects - Allow another user to access database objects such as tables or views (For such as tables or views (For Data

Control )

REVOKEREVOKE - Disallow a user access to database objects such - Disallow a user access to database objects such as as tables and views (For tables and views (For Data Control )

04/11/2304/11/23 44www.manoharprasad.wordpress.comwww.manoharprasad.wordpress.com

DML StatementsDML Statements

SELECTSELECT - Retrieve data from a database table - Retrieve data from a database table

INSERTINSERT - Insert new data into a database table - Insert new data into a database table

UPDATEUPDATE - Change the values of some data items in a - Change the values of some data items in a database table database table

DELETEDELETE - Delete rows from a database table- Delete rows from a database table

TRUNCATETRUNCATE - Delete all rows from a database table (can - Delete all rows from a database table (can not not be rolled back) be rolled back)

COMMITCOMMIT - Make all recent changes permanent (DML - - Make all recent changes permanent (DML - transactional) transactional)


Creating Tables

Tables are defined with the CREATE TABLE command. This command creates an empty table, a table with no rows.

Basically defines a table name as describing a

set of named columns.

Defines the data types and sizes of the

columns.

Each table must have at least one column.


Type description Oracle SQLvariable-length char. string

VARCHAR2(l)

fixed-length char. string CHAR(l)

number NUMBER

date DATE

The main SQL data types


Creating a table

E.g.:- 1) CREATE TABLE Employee (

Emp_NoChar(4),

Name Varchar2(15),

AddressVarchar2(15),

DOBDate,

SalaryNumber(9,2) );


Creating table with constrains

e.g.:- 2) CREATE TABLE Employee (

Emp_No Char(4) Primary Key,

Name Varchar2(15) Not Null,

Address Varchar2(15),

DOB Date,

Salary Number(9,2) Check (Salary > 3500) );

Primary KeyAlways Salary must be > 3500

Name field Cannot be null

04/11/2304/11/23 99www.manoharprasad.wordpress.cowww.manoharprasad.wordpress.co

mm

Altering a Table

Once created, tables can be altered to accommodate changing needs.


Eg:- 1) ALTER TABLE Employee

ADD Sex char(1);

+

Add new column to the table


2) ALTER TABLE Employee DROP COLUMN

Sex ;

Delete a existing column from the table


Displaying Table Structure

E.g.:- DESCRIBE Employee;

Name Null? Type

--------------- -------------- -------------

EMP_NO NOT NULL CHAR(4)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(15)

DOB DATE

SALARY NUMBER(9,2)

SEX CHAR(1)


Entering Values to a table

SQL provides the key word INSERT to add data to a table.

E.g.:- 1) INSERT INTO Employee

VALUES (‘E001’,’Ranjith’,’Matara’,’12-Feb-76’,7400);


Entering Values to a table

Naming columns for insert

2) INSERT INTO Employee (Emp_No, Name, Salary) VALUES (‘E001’,’Ranjith’,7400.00);


04/11/2304/11/23 www.manoharprasad.wordpress.comwww.manoharprasad.wordpress.com 1616

dbms

Education