Next: 3.2 Sub-Languages of SQL

Chapter 3 - Introduction to SQL (Single Table DML Statements)

Table of Contents

• 3.1 Welcome
•     3.1.1 SQL and SQL Standardization
• 3.2 Sub-Languages of SQL
• 3.3 The Basics of SELECT, Order of Execution, and the Dual Table
• 3.4 Aliases (Field and Table Aliases)
• 3.5 Calculated Values and Single-Line Functions
• 3.6. Filtering Data (WHERE)
•     3.6.1 Wildcards
•     3.6.2 Filtering with Aliases
• 3.7 Sorting Data (ORDER BY)
• 3.8 Limiting Results (FETCH)
• 3.9 CRUD
•     3.9.1 INSERT - Inserting New Data Rows
•     3.9.2 UPDATE - Updating Existing Data
•     3.9.3 DELETE - Deleting Existing Data
• 3.10 Practice Problems

Suggested Additional Reading Materials

• Text - Chapter 16 (SQL Data Manipulation Language)
  (Up to end of "Built-in Functions", ignoring grouping, count and having sections)
• W3Schools - SELECT
• W3Schools - SELECT DISTINCT
• W3Schools - SELECT WHERE
• W3Schools - SELECT AND, OR, NOT
• W3Schools - SELECT ORDER BY
• W3Schools - NULL Values
• W3Schools - INSERT
• W3Schools - UPDATE
• W3Schools - DELETE

3.1 Welcome to Week 3

Week 3 is where we move from from theory of design and database concepts to hand's on real programming work.  With the introduction of SQL and the coding environment.  This is a heavy week and takes a lot of time, so get started early. 

By the end of this week, you will be able to:

• define SQL and describe how it is used to communicate with various database management systems
• list and outline the application of the 4 primary sub-languages of SQL in real-world scenarios
• create basic data query statements requesting specific information from single tables while filtering and sorting the data
• demonstrate knowledge of the term CRUD
• be able to write and execute basic DML statements to insert, update and delete data within a relational database

3.1.1 SQL and SQL Standardization

What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases.

 SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?   SQL can:

• execute queries against a database
• retrieve data from a database
• insert records in a database
• update records in a database
• delete records from a database
• create new databases
• create new tables in a database
• create stored procedures in a database
• create views in a database
• set permissions on tables, procedures, and views

SQL is a Standard - BUT.... Although SQL is an ANSI/ISO standard, there are different versions of the SQL language. However, they all DBMSs support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, CREATE) in a similar manner.  It should also be noted that most of the DBMSs also have their own proprietary extensions in addition to the SQL standard!  This course uses Oracle as it's DBMS, but will try to remain agnostic with respect to the teaching and learning of the SQL language to maximize the learner's ability to apply their learned knowledge to a different DBMS in the future.