SQLSlide

 Collection of interrelated data
 Set of programs to access the data
 DBMS contains information about a particular enterprise
 DBMS provides an environment that is both convenient
and efficient to use.
 Database Applications:
 Banking: all transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
 Databases touch all aspects of our lives

What is SQL?
– When a user wants to get some
information from a database file, he
can issue a query.
1
– A query is a user–request to retrieve
data or information with a certain
condition.
– SQL is a query language that allows user
to specify the conditions. (instead of
algorithms)

Concept of SQL
– The user specifies a certain condition.
1
– The result of the query will then be
stored in form of a table.
– Statistical information of the data.
– The program will go through all the
records in the database file and select
those records that satisfy the
condition.(searching).

Both an ANSI and ISO standard
Types of commands:
1. Data Definition Language (DDL) : Create, Alter, Drop,
Rename, Truncate
2. Data Manipulation Language (DML): Insert, Delete,
Update
1. Data Retrieval: Select
2. Transaction Control: Commit, Rollback, Savepoint
3. Data Control Language (DCL): Grant, Revoke

5
A simplified schematic of a typical SQL
environment, as described by the SQL-2003
standard

6
 Catalog
 A set of schemas that constitute the description of a database
 Schema (or Database)
 The structure that contains descriptions of objects created by a user (base tables,
views, constraints)
 Data Definition Language (DDL)
 Commands that define a database, including creating, altering, and dropping tables and
establishing constraints
 Data Manipulation Language (DML)
 Commands that maintain and query a database
 Data Control Language (DCL)
 Commands that control a database, including administering privileges and committing data

CREATE TABLE {table}
( {column datatype [DEFAULT expr]
[column_constraint] ... | table_constraint}
[, { column datatype [DEFAULT expr]
[column_constraint] ...
)
ALTER TABLE {table}
[ADD|MODIFY {column datatype [DEFAULT expr] [column_constraint]}
[DROP drop_clause]
DROP TABLE {table} [cascade constraints]
DESC {table}

INSERT INTO {table | view} [ (column [, column] ...) ]
VALUES (expr,expr ...)
UPDATE {table | view }
SET { (column [, column] = { expr | }
[WHERE condition]
DELETE [FROM] {table | view} [WHERE condition]

Data Retrieval:
SELECT [DISTINCT | ALL] {table|view}
FROM {table | view}
[WHERE condition ]
[GROUP BY expr [, expr]]
[ORDER BY {expr} [ASC | DESC]]
select * from dept;
select deptname from dept where deptid='10';
select lname,fname from emp order by lname desc;
select max(salary) from emp group by positionid;
select deptname from dept,emp where
dept.deptid=emp.deptid and emp.empid='111';

Transaction Control:
COMMIT
ROLLBACK [ to {savepoint}]
SAVEPOINT {name}
commit;
savepoint point1;
rollback to point1;

Data Control Language:
GRANT [privileges]
ON object TO user|public
[WITH GRANT OPTION]
REVOKE [privileges]
ON object TO user|public
[CASCADE CONSTRAINTS]
grant select,update on emp to XYZ ;
revoke update on emp to XYZ;

SelectionProjection
Table 1 Table 2
Table 1Table 1
Join

 SELECT identifies the columns to be displayed.
 FROM identifies the table containing those columns.
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;

SELECT department_id, location_id
FROM departments;

 SQL statements are not case-sensitive.
 SQL statements can be entered on one or more lines.
 Keywords cannot be abbreviated or split across lines.
 Clauses are usually placed on separate lines.
 Indents are used to enhance readability.
 In SQL Developer, SQL statements can optionally be
terminated by a semicolon (;). Semicolons are
required when you execute multiple SQL statements.
 In SQL*Plus, you are required to end each SQL
statement with a semicolon (;).

SQLSlide

Recommended

More Related Content

What's hot (20)

Viewers also liked (6)

Similar to SQLSlide (20)

Recently uploaded (20)

SQLSlide