1. Database Systems: Design, Implementation, and Management Chapter 7 Introduction to Structured Query Language (SQL)

2. Data Definition Language (DDL) andData Manipulation Language (DML) • Data Definition Language (DDL)defines the structure of the database • E.g. tables, columns, keys, indexes, etc. • Data Manipulation Language (DML)manipulates the actual data contents • E.g. adding, modifying and deleting data • Understanding the difference between these two types of SQL commands will help you master SQL

3. Create, Read, Edit/Update, Delete (CRED/CRUD): Four major kinds of database operations • Create • DDL: CREATE tables and other structures • DML: INSERT data into tables • Read • DDL: SELECT (system catalogue queries) • DML: SELECT • Edit/Update • DDL: ALTER table and other structures • DML: UPDATE table • Delete • DDL: DROP tables and other structures • DML: DELETE from table

4. WHERE versus HAVING • WHERE is used to restrict a subset of rows from a regular query result • Usually a SELECT query, but also UPDATE and DELETE • HAVING is used to restrict a subset of rows when using a GROUP BY aggregation • HAVING only works with GROUP BY • Mnemonics to not confuse the two: • SELECT FROM WHERE? (WHERE is the normal clause for a SELECT statement) • G-H (GROUP BY goes with HAVING)

5. Set date and number formats in Oracle • Some data (e.g. dates and numbers) assume that certain formats are being used • ALTER SESSION is used to set environment variables, including date and number formats • If you have problems entering dates, execute this SQL command before your other commands: • alter session set nls_date_format = 'dd-mm-yyyy'; • With language and number format problems, try: • alter session set nls_language = English; • alter session set nls_territory = Canada;

6. Sources • Most of the slides are adapted from Database Systems: Design, Implementation and Management by Carlos Coronel and Steven Morris. 11th edition (2015) published by Cengage Learning. ISBN 13: 978-1-285-19614-5 • Other sources are noted on the slides themselves