dbms, oracle database
TRANSCRIPT
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 1/16
SQL (Structured Query Language)
Presented by: Manohar PrasadSpecialization: IB+ITRoll No.- 1999/01030003
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 2/16
Structured Query Language (SQL)
SQL allows user to access data in relationaldatabase management systems, such as Oracle,
Access, SQL Server, FoxPro, Informix and others,by allowing users to describe the data the userwishes to see.
5/4/2012 2www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 3/16
SQL statements are issued for thepurpose of:
Data definition - Defining tables and structures
in the database .
Data manipulation - Inserting new data,
Updating existing data, Deleting existing data, and
Querying the Database ( Retrieving existing datafrom the database).
5/4/2012 3www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 4/16
DDL Statements
CREATE - Create new database objects such as tables orviews
DROP - Drop a database object such as a table, viewor index
ALTER - Change an existing table, view or index definition
GRANT - Allow another user to access database objects
such as tables or views (For Data Control )
REVOKE - Disallow a user access to database objects such astables and views (For Data Control )
5/4/2012 4www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 5/16
DML Statements
SELECT - Retrieve data from a database table
INSERT - Insert new data into a database table
UPDATE - Change the values of some data items in adatabase table
DELETE - Delete rows from a database table
TRUNCATE - Delete all rows from a database table (can notbe rolled back)
COMMIT - Make all recent changes permanent (DML -transactional)
5/4/2012 5www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 6/16
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 thecolumns.
Each table must have at least one column.
5/4/2012 6www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 7/16
Type description Oracle SQL
variable-length char. string VARCHAR2(l)
fixed-length char. string CHAR(l)number NUMBER
date DATE
The main SQL data types
5/4/2012 7www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 8/16
Creating a table
E.g.:- 1) CREATE TABLE Employee (Emp_No Char(4),
Name Varchar2(15),
Address Varchar2(15),
DOB Date,
Salary Number(9,2) );
5/4/2012 8www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 9/16
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 Key
Always Salarymust be > 3500Name field
Cannot be null
5/4/2012 9www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 10/16
Altering a Table
Once created, tables can be altered to accommodatechanging needs.
5/4/2012 10www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 11/16
Eg:- 1) ALTER TABLE Employee
ADD Sex char(1);
+
Add new column to the table
5/4/2012 11www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 12/16
2) ALTER TABLE Employee
DROP COLUMN Sex ;
Delete a existing column from the table
5/4/2012 12www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 13/16
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)
5/4/2012 13www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 14/16
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);
5/4/2012 14www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 15/16
Entering Values to a table
Naming columns for insert
2) INSERT INTO Employee (Emp_No, Name, Salary)VALUES (‘E001’,’Ranjith’,7400.00);
5/4/2012 15www.manoharprasad.wordpress.com
8/3/2019 DBMS, Oracle Database
http://slidepdf.com/reader/full/dbms-oracle-database 16/16
5/4/2012 www.manoharprasad.wordpress.com 16