advanced_sql - IT133 ADVANCED DATABASE MANAGEMENT Lecture...

Info iconThis preview shows pages 1–13. Sign up to view the full content.

View Full Document Right Arrow Icon
IT133 – ADVANCED DATABASE MANAGEMENT Lecture 01: Advanced SQL
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Another Introduction SQL is a common language for ALL Relational Databases, skills can easily be transferred from one database to another. And, programs written in SQL are portable , they can often be moved with very little modification. The most common task/use: Querying data. A query is an operation that retrieves data from one or more tables or views.
Background image of page 2
Types of SQL Statements Data Definition Language Data Manipulation Language Transaction Control Statements Session Control Statements Embedded SQL Statements
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Data Definition Language (DDL) DDL lets you perform the following tasks: Create, alter, and drop schema objects ( CREATE, ALTER, DROP ) Grant and revoke privileges and roles ( GRANT, REVOKE ) Analyze information on a table, index, or cluster ( ANALYZE ) Establish auditing options ( AUDIT ) Add comments to the data dictionary ( COMMENT ) Oracle implicitly commits the current transaction before and after every DDL.
Background image of page 4
Data Manipulation Language (DML) DML statements access and manipulate data in existing schema objects. CALL INSERT SELECT DELETE LOCK TABLE UPDATE EXPLAIN PLAN MERGE DML do not implicitly commit the current transaction.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Transaction Control Statements Transaction control statements manage changes made by DML statements. COMMIT ROLLBACK SAVEPOINT SET TRANSACTION
Background image of page 6
Session Control Statements The single system control statement, ALTER SYSTEM, dynamically manages the properties of an Oracle Database instance. This statement does not implicitly commit the current transaction.
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Embedded SQL Statements Embedded SQL statements place DDL, DML, and transaction control statements within a procedural language program.
Background image of page 8
Relational Set Operators Joins Sub-queries Functions Advance SQL
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Relational Set Operators Advanced SQL – Part I UNION UNION ALL INTERSECT MINUS
Background image of page 10
Relational Set Operators SQL commands operate over entire sets of rows and columns (tables) at once. Using sets, you can combine two or more sets to create new sets (or relations). NB: The SQL-99 standard defines the operations that all DBMSs must perform on data, but it leaves the implementation details to the DBMS vendors. Some advanced SQL functions may not work on all DBMS implementations and that some DBMS Vendors may implement additional features not found in the SQL standard. UNION , INTERSECT , and MINUS are Oracle implementations EXCEPT is the SQL standard for MINUS
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Cont… UNION , INTERSECT , and MINUS will work properly for union-compatible relations. Union-compatible means that the names of the
Background image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 46

advanced_sql - IT133 ADVANCED DATABASE MANAGEMENT Lecture...

This preview shows document pages 1 - 13. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online