1 / 12

Quick review of SQL

Quick review of SQL. And conversion to Oracle SQL. Assuming…. You know how to model data using An ER diagram A class diagram You know how to translate entity-relationship models into: Tables With constraints. SQL. Structured Query Language Made up of the following components:

rajah-davenport
Download Presentation

Quick review of SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Quick review of SQL And conversion to Oracle SQL

  2. Assuming… • You know how to model data using • An ER diagram • A class diagram • You know how to translate entity-relationship models into: • Tables • With constraints

  3. SQL • Structured Query Language • Made up of the following components: • Data Manipulation Language (DML) • Data Definition Language (DDL) • Transaction control • Data Control Language (DCL) • Oracle SQL complies with the core ANSI/ISO standard for SQL:2003, but is not completely compliant. • It also has extensions, that are not compliant, so are not portable to non-Oracle databases.

  4. DML • SELECT • INSERT • UPDATE • DELETE

  5. DDL • CREATE • ALTER • DROP • Any object in the database. • TRUNCATE • Deletes all rows in a table.

  6. Transaction control • A database transaction is a unit of work that leaves the database in a consistent state. • To ensure consistency, ongoing work can be committed, rolled back to a checkpoint, or rolled back completely. • COMMIT • ROLLBACK

  7. Data Control Language • These control user access to an Oracle database. • These instructions include: • GRANT • REVOKE • SET ROLE…

  8. Exercises Lab 1, Week 1 • Learn the datatypes that are used in Oracle 10g. • Retrieve all columns and rows • Retrieve specific columns • Use SQL*Plus DESCRIBE command • Specify an alias for a column • Learn the SQL built-in functions provided by Oracle. • Learn about the DUAL table. • Build SELECT clause expressions • with the Concatenation String operator • with arithmetic operators • with SQL built-in functions. • Work with NULLs in a SELECT clause expression • Implement conditional log in SELECT clause expressions. • Retrieve specific rows from tables.

  9. Exercises Lab 2, Week 1 • Building WHERE clause conditions • with relational operators • With subqueries • Building composite WHERE Clause conditions with logical operators • Grouping records in a query’s result set. • Rolling up groups into supergroups. • Ordering records in a query’s result set • Building an inner join of two tables • Building an outer join of two tables. • Inserting new rows in a table • Deleting rows from a table.

  10. Lab 1 Week 2 • Committing and Rolling back transactions • Writing and storing scripts.

  11. Lab 2 Week 2 • Data Definition Language • DCL

  12. References ‘Hands-on Oracle Database 10g Express Edition for Windows’, Bobrowski, S., Oracle Press, McGraw Hill / Osborne, 2006. ‘Oracle Database 10g A Beginner’s guide’, Abramson, I., Abbey, M., Corey, M., Oracle Press, McGraw Hill / Osborne, 2004.

More Related