Transcript Chapter 7

BTM 382 Database Management
Chapter 7
Introduction to Structured Query Language
(SQL)
Chitu Okoli
Associate Professor in Business Technology Management
John Molson School of Business, Concordia University, Montréal
1
Data Definition Language (DDL) and
Data 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
2
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
3
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)
• SELECT column-list
• FROM tables
• WHERE conditions
– G-H (GROUP BY goes with HAVING)
4
Set date and number formats in Oracle
• Some data (e.g. dates and numbers) assume that certain formats are
being used; using different formats can give errors
• ALTER SESSION is used to set environment variables, including date
and number formats
• Date formats: If you have problems entering dates, execute this SQL
command before all your other commands:
– alter session set nls_date_format = 'dd-mm-yyyy';
• You need to customize the date format to exactly what you want
– You can also use the function: to_date( '20150301', 'YYYYMMDD' )
• Language and number formats: These commands could help:
– alter session set nls_language = English;
– alter session set nls_territory = Canada;
– alter session set nls_date_format = 'dd-mm-yyyy';
5
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
6