INTRODUCTION
TO
ORACLE AND SQL
COMMANDS
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
BASIC DEFINITIONS
⮚ Database: A collection of related data.
⮚ Data: Known facts that can be recorded and have an implicit
meaning.
⮚ Database Management System (DBMS): A software
package/ system to facilitate the creation and maintenance of
a computerized database.
⮚ Database System: The DBMS software together with the data
itself. Sometimes, the applications are also included.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
RELATIONAL DATABASE MANAGEMENT SYSTEM
(RDBMS):
⮚ RDBMS data is structured in database tables, fields and records.
⮚ Each RDBMS table consists of database table rows.
⮚ Each database table row consists of one or more database table fields.
⮚ RDBMS store the data into collection of tables, which might be related by
common fields (database table columns).
⮚ RDBMS also provide relational operators to manipulate the data stored
into the database tables.
⮚ Most RDBMS use SQL as database query language.
⮚ Edgar Codd introduced the relational database model.
⮚ The most popular RDBMS are MS SQL Server, DB2, Oracle and MySQL.
Most RDBMS use SQL as database query language.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
ORACLE 11G
Oracle Database 11g Programming with SQL is focused on:
❑Querying a Database with SQL
❑Conversion Functions, Conditional Expressions, Group Functions, and Joins
❑ Manipulating Queries and Data
❑ Using DDL, Views, and Schema Objects
❑ Controlling User Access and Managing Objects
❑ Managing Data Dictionary Views and Large Data Sets
❑ Managing Time Zones and Date-time Functions
❑ Sub queries and Regular Expressions
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
INTRODUCTION TO LIVE SQL
⮚ Live SQL provides an introduction to the Structured Query Language
(SQL), learn how to create tables with primary keys, columns,
constraints, indexes, and foreign keys.
⮚ We can work on various platforms if we have the software but when
there is a issue of not having software we can use Live SQL.
⮚ To work onto this module, click on the link given below
https://livesql.oracle.com/apex/f?p=590:1000
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Step1: Click on Sign In
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Step2: Click on create account and fill the required details
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Step3: Click on SQL Worksheet
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
INTRODUCTION TO SQL
SQL stands for Structured Query Language.
⮚ SQL lets you access and manipulate databases.
⮚ SQL can execute queries against a database
⮚ SQL can retrieve data from a database
⮚ SQL can insert records in a database
⮚ SQL can update records in a database
⮚ SQL can delete records from a database
⮚ SQL can create new databases
⮚ SQL can create new tables in a database
⮚ SQL can create stored procedures in a database
⮚ SQL can create views in a database
⮚ SQL can set permissions on tables, procedures, and views and many more
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DATA TYPES COMMONLY USED IN SQL
⮚CHAR(size): This data type is used to store character strings values of fixed length.
The size in the brackets determines the number of characters it can hold.
The maximum number of characters (i.e. the size) this data type can hold is 255
characters.
The data held is right padded with spaces to whatever length specified.
⮚VARCHAR(size): This data type is used to store variable length alphanumeric data.
It is a flexible form of CHAR data type.
The maximum data this data type can hold is 4000 characters.
⮚DATE: This data type is used to represent date.
Date time stores date in the 24-hour format.
⮚NUMBER(P,S): The NUMBER data type is used to store numbers(fixed or floating point).
Numbers of virtually any magnitude maybe stored up to 38 digits of precision.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
SQL COMPONENTS
SQL consists of three components:
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DATA DEFINITION LANGUAGE (DDL)
This component of the SQL language is used to create and modify tables
and other objects in the database. For tables there are three main commands:
CREATE TABLE tablename 🡪 to create a table in the database
DROP TABLE tablename 🡪 to remove a table from the database
ALTER TABLE tablename 🡪 to add or remove columns from a table in the
database
RENAME <new table name> to <old table name>; 🡪 To change the name
of a table as per the user’s wish or requirement.
TRUNCATE TABLE<table name>; 🡪 Truncate Tables empties the table
completely. Once the data deleted cannot be retrieved.
DESC tablename 🡪 To view just the blank table
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
CREATE TABLE COMMAND
Purpose:- Tables are the basic unit of data storage in an Oracle
Database. Data is stored in rows and columns.
Syntax:- Create table <TableName> (<ColumnName1> <Data
Type>(<Size>), <ColumnName2> <Data Type>(<Size>),
………..<ColumnName n> <Data Type>(<Size>));
Example:-
Create table STUDENT (Roll_no number(5), Name varchar2(20), Branch
varchar2(10));
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
ALTER TABLE COMMAND
Purpose:- The structure of a table can be modified by using a alter table
command. Alter Table allows changing the structure of an existing table. With
Alter Table it is possible to add or delete columns, create or destroy indexes,
change the data type of existing columns, or rename columns or the table itself
Syntax:-
Adding New Columns
Alter table <table name> add(<new col name> <data type> (<Size>), <new
col name> <data type> (<Size>),…..);
Modifying Existing Column
Alter table <table name> modify(<col name> <new data type> (< new Size>),
<col name> <new data type> (<new Size>),…..);
Dropping A Column
Alter table <table name> drop column <col name>;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Example:-
Alter table STUDENT add( Telephone number(10));
Alter table STUDENT modify( Telephone number(20));
Alter table STUDENT drop column Telephone;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
RENAME TABLE COMMAND
Purpose:- To change the name of a table as per the user’s wish or
requirement.
Syntax:- Rename <new table name> to <old table name>;
Example:- Rename STUDENT to group_a101;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DROP TABLE COMMAND
Purpose:- Sometimes table in the database becomes obsolete and need to be
discarded, so we use drop table command.
Syntax:- Drop table <table name>;
Example:-
Drop table CSEA_Data101;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DATA MANIPULATION LANGUAGE (DML)
DML component of the SQL language is used to manipulate data within a
table. There are four main commands:
SELECT 🡪 to select rows of data from a table
INSERT 🡪 to insert rows of data into a table
UPDATE 🡪 to change rows of data in a table
DELETE 🡪 to remove rows of data from a table
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
INSERT COMMAND
Command:- Insert data into a table
Purpose:- The Insert Into Table command is used to load the created table
with data to be manipulated later.
Syntax:- Insert into <table name>(<col1>,<col2>,……..,<col n>) values
(<expression1>, <expression2> ,……,<expression3>);
Example:-
Insert into STUDENT values(101, ‘Aanchal’, ’CSE’);
Insert into STUDENT values(126, ‘Jotnain’, ’CSE’);
Insert into STUDENT values(149, ‘Sapanpreet’, ’ECE’);
Insert into STUDENT values(151, ‘Savita’, ’CSE’);
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
INSERT DATA INTO A TABLE FROM A TABLE
(Copying contents of a table to another table)
Command:- Insert into….Select
Purpose:- To filter the data that is not required
Syntax:- Insert into <table name>Select <col name1>, <col name2>….
from <table name>;
Example:-
Insert into CSEA_Data101 Select roll_no,name from STUDENT where
roll_no=149;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
SELECT COMMAND
Command:- (a) Selecting all rows and all columns
Purpose:- This command is used to view all the rows and columns of the
table created in the database.
Syntax:- Select * from <table name>;
Example:-
Select * from STUDENT;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
SELECT COMMAND
(b) Selected rows and all columns using where clause
Purpose:- If the information of a particular student is to be retrieved from a
table, its retrieval must be based on a specific condition
Syntax:- Select * from <table name> where <condition>;
Example:-
Select * from STUDENT where roll_no=151;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
ALIAS
Purpose:- SQL aliases are used to give a table, or a column in a table, a
temporary name.
ALIAS COLUMN SYNTAX
Syntax:- SELECT column_name AS alias_name
FROM table_name;
Example:-
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
ALIAS TABLE SYNTAX
Syntax:- SELECT column_name(s) FROM table_name AS alias_name;
Example:-
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the
Horn' AND c.CustomerID=o.CustomerID;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DELETE COMMAND
Purpose:- To delete the rows from the table that satisfies the condition
provided by its where clause and returns the number of records deleted.
Syntax:- Removal of all rows
Delete from <table name>;
Removal of Specific Row(s)
Delete from <table name> where <condition>;
Example:-
Delete from CSEA_Data101 where rollno=149;
Delete from CSEA_Data101;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DELETE & TRUNCATE
TRUNCATE
⮚TRUNCATE is a DDL command
⮚TRUNCATE is executed using a table lock and the whole table is locked to remove all
records.
⮚We cannot use the WHERE clause with TRUNCATE.
⮚TRUNCATE removes all rows from a table.
⮚Minimal logging in the transaction log, so it is faster performance-wise.
DELETE
⮚DELETE is a DML command.
⮚DELETE is executed using a row lock, each row in the table is locked for deletion.
⮚We can use where clause with DELETE to filter & delete specific records.
⮚The DELETE command is used to remove rows from a table based on WHERE
condition.
⮚It maintains the log, so it slower than TRUNCATE.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DROP COMMAND
1. The DROP command removes a table from the
database.
2. All the tables' rows, indexes, and privileges will also
be removed.
3. No DML triggers will be fired.
4. The operation cannot be rolled back.
5. DROP and TRUNCATE are DDL commands, whereas
DELETE is a DML command.
6. DELETE operations can be rolled back (undone),
while DROP and TRUNCATE operations cannot be
rolled back
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
Difference between DELETE, DROP and TRUNCATE
DELETE DROP TRUNCATE
1. It is a DML command. 1. It is a DDL command. 1. It is a DDL command.
2. It is used to delete rows or records 2. It is used to delete the entire table 2. It is used to delete the entire
based on conditions specified in the along with its schema and structure records of a table without affecting
WHERE clause. respectively. the schema of the table.
3. If the WHERE clause is not specified
with conditions it deletes all the 3. There is no WHERE clause. 3. There is no WHERE clause.
records of the table.
4. It is a DDL command. As a result, 4. It is a DDL command. As a result,
4. It is a DML command. As a result,
the changes cannot be rolled back or the changes cannot be rolled back or
the operation can be rolled back.
undone. undone.
5. It is faster than DELETE in execution
5. It scans every row before deleting because it does not scan every row
5. It is faster and time-saving.
making it slower and time-consuming. before deleting which makes it the
least time-consuming.
6. Syntax: DELETE FROM TABLE 6. Syntax: TRUNCATE TABLE
6. Syntax: DROP TABLE Table_Name;
Table_Name WHERE [CONDITIONS]; Table_Name;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
UPDATE COMMAND
Purpose:- The Update statement updates columns in the existing table’s
rows with new values. The Set clause indicates which column data should
be modified and the new valued they should hold.
Syntax:-
Updating Specific Records
Update <table name> set <col name1>=<expression1>, <col
name2>=<expression2> where <condition>;
Updating All The Records
Update <table name> set <col name1>=<expression1>, <col
name2>=<expression2>;
Example:-
Update STUDENT set Branch=’ECE’ where roll_no=101;
Update STUDENT set Branch=’CSE’;
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
DATA CONTROL LANGUAGE (DCL)
This component of the SQL language is used to create privileges to allow
users access to, and manipulation of, the database. There are two main
commands:
GRANT 🡪 to grant a privilege to a user
REVOKE 🡪 to revoke (remove) a privilege from a user.
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
FURTHER READINGS
1. EF Codd’s Rules -- https://www.tutorialcup.com/dbms/codds-rule.htm
2. Introduction to databases.pdf
3. https://nptel.ac.in/courses/106105175/
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)
THANK YOU
University Institute of Engineering (UIE)
