SQL Training

History of SQL

• SQL (Structured Query Language) was developed by IBM in the late 1970s for IBM's DB2 RDBMS
• SQL became the language for relational database development
• ANSI formalized SQL as a standard in 1986
• SQL is vital to developing databases in major platforms such as Oracle, MySQL and Microsoft SQL Server

SQL Is Three Languages

• SQL is actually three languages!
• Data Definition Language (DDL) for the creation, alteration and dropping of database objects (e.g., tables)
• Data Manipulation Language (DML) for querying and updating relational data
• Data Control Language (DCL) for securing data

Sample Uses of SQL

• Interactive through GUI interfaces such as Microsoft Access query grids
• Embedded in GUI and console business applications
• Embedded in Java EE and Microsoft .NET web applications

SQL Difficulty Level

• SQL is a high-level data access language
• SQL was designed to be written by end users and developers
• SQL is not a programming language
• Commonly used in programming courses

Learning SQL

• SQL is relatively straightforward to learn
• Design query grids such as offered by Microsoft Access expedite learning SQL
• Excellent documentation is available from all major vendors such as MySQL: https://dev.mysql.com/doc/refman/5.7/en/
• SQL lexicon consists of understandable commands such as SELECT, INSERT, UPDATE and DELETE

SQL: The Good

• Relatively straightforward to learn
• Cross-platform
• Excellent documentation from major vendors (e.g., Oracle, IBM, Microsoft)
• Well respected in the IT industry

SQL: The Bad

• SQL is a computer language and may present challenges to students with no computer language background.

SQL: The Future

• SQL has a bright future!
• Both ANSI and ISO publish standards for SQL usage
• Tremendous corporate investment in SQL-based applications
• Full support by major vendors such as Oracle, Microsoft and IBM

Syntax

• Syntax was originally established by IBM based on the company's PL/I programming language
• The language keywords are not case sensitive although capitalization is standard practice
• The syntax is based on easily-understandable verbs:
  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Creating a Table

CREATE TABLE GardenTools
(
id			INT NOT NULL,
description	VARCHAR(1000),
price			NUMERIC (9,2))

Comments

--Single-line comments follow 2 hyphens

/*
This is a 
	multi-line comment.
*/

Strings

• Fixed-length strings are defined as char:

  • Customer_name CHAR(25) DEFAULT 'BestCo Ltd.'

• Varying-length strings are defined as varchar:

  • Address_line VARCHAR(100) DEFAULT '2017 Anywhere Lane'

Integer, Decimal, and Float Column Types

• quantity_on_hand INT DEFAULT 0

• item_price DECIMAL(9,2)

• population_of_asia FLOAT

Date and Time Column Types

• date_of_order DATE

• time_of_order TIME

Selecting Data from a Table

• SELECT * FROM GardenTools

• SELECT id, description FROM GardenTools WHERE price > 9.99

Inserting a Row into a Table

• INSERT INTO GardenTools VALUES (202, 'Large rake', 24.49)

• INSERT INTO GardenTools (id, description) VALUES (219, 'Small rake')

Updating a Row in a Table

• UPDATE GardenTools

• SET price = 25.99

• WHERE id = 202

Deleting a Row in a Table

• DELETE FROM GardenTools WHERE id = 202

Keep Learning SQL

by taking a

Webucator SQL class

https://www.webucator.com/database-training/sql-training.cfm