{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

advanced_sql - IT133 ADVANCED DATABASE MANAGEMENT Lecture...

Info icon This 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
Image of page 1

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

View Full Document Right 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.
Image of page 2
Types of SQL Statements Data Definition Language Data Manipulation Language Transaction Control Statements Session Control Statements Embedded SQL Statements
Image of page 3

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

View Full Document Right 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.
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.
Image of page 5

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

View Full Document Right Arrow Icon
Transaction Control Statements Transaction control statements manage changes made by DML statements. COMMIT ROLLBACK SAVEPOINT SET TRANSACTION
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.
Image of page 7

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

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

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

View Full Document Right Arrow Icon
Relational Set Operators Advanced SQL Part I UNION UNION ALL INTERSECT MINUS
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
Image of page 11

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

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

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern