Transcript SQL(Structured Query Language)

Writing SQL Statements
SQL statements are not case sensitive.
SQL statements can be on one or
more lines.
Keywords cannot be abbreviated or split
across lines.
Clauses are usually placed on separate
lines.
Tabs and indents are used to enhance
readability.
SQL(Structured Query
Language)
SQL classify the commands in four
categories
1- Data Retrival Command.
2- Data Manipulation Language (DML)
3- Data Definition Language (DDL)
4- Data Control Language (DCL)
Data Retrival Command
This category contains only one command:
SELECT. This command is a cornerstone of
SQL and allows users to query necessary
data from the database. Becouse of its
importance and widespread usage, if is
often considerd in a separate category of its
own.
Data Manipulation Language
(DML)
Commands in this category allow you to
manipulate data in existing database
objects. The most popular commands in
this category are INSERT, UPDATE, and
DELETE. Often it is necessary to use the
SELECT command to specify the set of
data that should be updated or deleted.
This is the reson why SELECT sometimes
is included in the DML category.
‫لغة معالجة البيانات‪DML :‬‬
‫تتيح لك لغة معالجة البيانات ‪ DML‬القيام بالمهمات التالية‪:‬‬
‫‪ -1‬إدراج‪.‬‬
‫‪ -2‬تحديث ‪.UPDATE‬‬
‫‪ -3‬حذف ‪.DELETE‬‬
‫‪ -4‬تحديد ‪ SELECT‬البيانات في قاعدة البيانات‪.‬‬
‫تتيح لك لغة ‪ DML‬العمل مع محتويات قاعدة بياناتك ‪.‬‬
‫أمر ‪SQL‬‬
‫‪Insert‬‬
‫‪Delete‬‬
‫‪Update‬‬
‫‪Select‬‬
‫‪Commit work‬‬
‫‪Rollback‬‬
‫الغرض منه‬
‫إضافة صفوف من البيانات إلى الجداول‪.‬‬
‫حذف صفوف من البيانات من الجداول‪.‬‬
‫تغيير البيانات الموجودة بالجدول‪.‬‬
‫استعادة صفوف من البيانات من جدول‪/‬طريقة عرض‪.‬‬
‫جعل التغييرات الخاصة بالعملية الحالية دائمة (كتابتها‬
‫على القرص)‪.‬‬
‫التراجع عن كافة التغييرات التي أجريت منذ آخر عملية‬
‫إيداع‪.‬‬
Data Definition Language (DDL)
Commands in this category modify the
structure of the database by creating,
replacing, altering, or droping objects such
as tables, indexes, and views.
‫لغة تعريف البيانات‪DDL :‬‬
‫تتيح لك لغة تعريف البيانات ‪ DDL‬القيام بالمهمات التالية‪:‬‬
‫‪ -1‬إنشاء كائن قاعدة البيانات‪.‬‬
‫‪ -2‬إسقاط كائن قاعدة البيانات‪.‬‬
‫‪ -3‬تغيير كائن قاعدة البيانات‪.‬‬
‫‪ -4‬منح امتيازات استخدام كائن قاعدة البيانات‪.‬‬
‫‪ -5‬سحب امتيازات استخدام قاعدة البيانات‪.‬‬
Data Control Language (DCL)
This category includes commands that protect the
integrity of the database and the consistency of
data by controlling and managing the acess to the
database structures. These commands are often
divided into transaction control commands
session control commands, and system control
commands.
e.g. create, grand, trancate.
SQL and SQL*Plus Interaction
SQL Statements
Buffer
SQL Statements
Server
SQL*Plus
SQL*Plus
Commands
Formatted Report
Query Results
SQL and SQL*Plus
SQL is a command language for communication with the
Oracle Server from any tool or application. Oracle SQL
contains many extensions. When you enter a SQL statement, it
is stored in a part of memory called the SQL buffer and remains
there until you enter a new statement.
SQL*Plus is an Oracle tool that recognizes and submits SQL
statements to the Oracle Server for execution and contains its
own command language.
Features of SQL
•Can be used by a range of users, including those with little
or no programming experience
•Is a nonprocedural language
•Reduces the amount of time required for creating and
maintaining systems
•Is an English-like language
Features of SQL*Plus
•Accepts ad hoc entry of statements
•Accepts SQL input from files
•Provides a line editor for modifying SQL
statements
•Controls environmental settings
•Formats query results into a basic report
•Accesses local and remote databases
SQL Statements Versus
SQL*Plus Commands
SQL
• A language
• ANSI standard
• Keyword cannot be
abbreviated
• Statements manipulate
data and table
definitions in the
database
SQL
statements
SQL
buffer
SQL*Plus
• An environment
• Oracle proprietary
• Keywords can be
abbreviated
• Commands do not
allow manipulation of
values in the database
SQL*Plus
commands
SQL*Plus
buffer
Capabilities of SQL SELECT
Statements
Selection
Projection
Table 1
Table 1
Table 1
Join
Table 2
Basic SELECT Statement
SELECT
FROM
[DISTINCT] {*, column [alias],...}
table;

SELECT identifies what columns.

FROM identifies which table.
Selecting All Columns
SQL> SELECT *
2 FROM
dept;
DEPTNO
--------10
20
30
40
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
Selecting Specific Columns
SQL> SELECT deptno, loc
2 FROM
dept;
DEPTNO
--------10
20
30
40
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON