BTM 382 Database Management Chapter 7 Introduction to

BTM 382 Database Management Chapter 7 Introduction to SQL (Structured Query Language) Chitu Okoli Associate Professor in Business Technology Management John Molson School of Business, Concordia University, Montréal

Structure of BTM 382 Database Management § § § Week 1: Introduction and overview § ch 1: Introduction Weeks 2 -6: Database design § ch 3: Relational model § ch 4: ER modeling § ch 6: Normalization § ERD modeling exercise § ch 5: Advanced data modeling Week 7: Midterm exam Weeks 8 -10: Database programming § ch 7: Intro to SQL § ch 8: Advanced SQL § SQL exercises Weeks 11 -13: Database management § ch 2, 14: Data models § ch 13: Business intelligence and data warehousing § ch 9, 15, 16: Selected managerial topics

Review of Chapter 7: Introduction to SQL § What are some key features of SQL as a programming language? § What are some important things to note about Oracle’s version of SQL? § How do you go about constructing an SQL query?

Overview of SQL

Structured Query Language (SQL) § Standard programming language for relational databases § Originated in 1970 s by IBM; latest version 2011 § … But nobody follows the exact standard § Every DBMS has its own flavour of SQL § Fourth-generation programming language 1. Machine language: speak to computers in their language • 1011 0111 0011 2. Assembly language: human codes for machine language • ADD 7 3 3. Tell computer step-by-step what to do (e. g. C#, Java, Python) • x = y + 3 4. Tell computer what it should give you (e. g. SQL, HTML) • select sum(Quantity. In. Stock) from Product;

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

Four major kinds of database operations: Create, Read, Edit/Update, Delete (CRED/CRUD) Data Definition Language (DDL): Change database structure Data Manipulation Language (DML): Change data contents inside tables Create CREATE tables and other INSERT data into tables structures (also ADD) Read SELECT (system catalogue queries) SELECT data from tables Edit/ ALTER table and other Update structures (also MODIFY) UPDATE table Delete DELETE from table DROP tables and other structures

Miscellaneous notes about Oracle SQL

COMMIT and ROLLBACK in Oracle (DML commands) § COMMIT § Saves all DML changes since the last COMMIT or DDL command § Whenever you issue any DDL command, COMMIT is automatically triggered § In other words, whenever you issue a command that changes the structure of the database, all data contents are automatically saved right away § If you do not COMMIT before closing the connection, all uncommitted DML commands (changes to data contents) will be lost § ROLLBACK § Cancel all DML changes since the last COMMIT or DDL command § Microsoft Access automatically commits every command (you cannot undo any changes)

Oracle character types: CHAR and VARCHAR 2 § CHAR(n) § Always a string of exact length of n (any unused characters are filled with blanks) § E. g. Hello in CHAR(10) is stored as ‘Hello ’ (10 characters) § VARCHAR 2(n) § A string of maximum length of n (any unused characters are empty) § E. g. Hello in VARCHAR 2(10) is stored as ‘Hello’ (5 characters) § Converts empty strings (‘’) to null § VARCHAR(n) § Same as VARCHAR 2, except that it distinguishes between null and empty string (‘’) § Not used in Oracle; automatically converted to VARCHAR 2 for now § Might be used in the future, so you should never use it § Best practice (recommendation, not requirement): Only use VARCHAR 2 all the time; there is no good reason to ever use anything else in Oracle

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 § Changes will last until you end the session (e. g. when you close Oracle SQL Developer); then they will revert to the system default § 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 this function for a single date occurence: to_date('20150301', 'YYYYMMDD') § Language and number formats: These commands might help: § alter session set nls_language = English; § alter session set nls_territory = Canada; § alter session set nls_date_format = 'dd-mm-yyyy';

General strategy for tackling SELECT queries

SELECT FROM… and maybe WHERE § Every SELECT query has three general sections, but they have two different orders of creation or presentation that you must understand 1. The creation order: the order in which you actually create the code: § SELECT column list (required) § Optional clauses: § WHERE conditions § GROUP BY aggregation § ORDER BY sort order § FROM tables (required) 2. The final code order: the order in which the final code must appear: § SELECT column list (required) § FROM tables (required) § Optional clauses (WHERE, GROUP BY, ORDER BY) § If there is any clause that cannot be easily obtained, then perhaps you need a subquery

1. SELECT column list (required) § Which columns do you want to see in the output? § Every item will be one column of data in your resulting output

1. SELECT column list (required)

2. Optional elements: WHERE, GROUP BY, ORDER BY § WHERE § What conditions or restrictions do you want in the output ROWS of the select clause? § Although optional, most SELECT queries have a WHERE clause § With no WHERE clause, all the rows in the table are displayed § GROUP BY § What criteria will you use for grouping rows? § HAVING: what conditions or restrictions do you want in the GROUP BY clause? § ORDER BY § What is the sort order of the final result?

2. Optional elements: WHERE

2. Optional elements: GROUP BY and HAVING § GROUP BY: What criteria will you use for grouping rows? § HAVING: what conditions or restrictions do you want in the GROUP BY clause?

2. Optional elements: WHERE versus HAVING clauses § WHERE is used to restrict a subset of original table rows from a query result § § HAVING is used to restrict a subset of aggregation result rows when using a GROUP BY aggregation § § specified as a GROUP BY item or is an aggregate function You can use WHERE with GROUP BY and HAVING all together, but then the conditions in the WHERE clause are not aggregates (that is, not specified in GROUP BY or aggregate functions) § § § HAVING only works with GROUP BY If you use GROUP BY, then every item in the SELECT and HAVING clauses is either: § § § Usually a SELECT query, but also UPDATE and DELETE WHERE: filter original table rows before the aggregation HAVING: filter result rows after the aggregation Mnemonics to help you 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)

2. Optional elements: WHERE versus HAVING clauses

2. Optional elements: ORDER BY

3. FROM tables and joins (required) § Which tables will give you the columns that all the other clauses need? § Tables needed for SELECT, WHERE, GROUP BY, HAVING and ORDER BY § Therefore, the FROM clause is the last part of the query that you should compose! § Do NOT include anything needed by any subqueries; each subquery has its own FROM statement § Two simple steps to compose FROM clause: 1. Identify the minimum list of necessary tables (from the SELECT, WHERE, GROUP BY, HAVING and ORDER BY clauses), using the ERD to trace the path 2. Join them together using appropriate JOIN clauses

3. FROM tables and joins (required)

Summary of Chapter 7: Introduction to SQL § SQL is the standard programming language for relational databases § Each DBMS has its own flavour of SQL; Oracle’s version has some noteworthy features. § To construct an SQL query, you need to understand the key components: § § SELECT (required) FROM (required) WHERE (optional) Other optional elements