Transcript Introduction to SQL - Seneca

DBS201: Introduction to
Structure Query Language (SQL)
Lecture 1
1
Agenda


The basic commands and functions of SQL
How to use SQL to query a database to extract
useful information
2
Introduction to SQL

SQL: Structured Query Language
 Designed specifically for communicating with
databases
 SQL functions fit into two broad categories:


Data definition language
Data manipulation language
3
Introduction to SQL (continued)

Data definition language

SQL includes commands to create



Database objects such as tables, indexes, and views
Commands to define access rights to those database
objects
Data manipulation language

Includes commands to insert, update, delete, and
retrieve data within the database tables
4
Introduction to SQL (continued)

SQL is relatively easy to learn

Basic command set has a vocabulary of less
than 100 words

Sample vocabulary:



CREATE COLLECTION
CREATE TABLE
CREATE VIEW
5
Introduction to SQL (continued)

Sample vocabulary (continued):







DROP COLLECTION
DROP TABLE
DROP VIEW
ALTER
INSERT
SELECT
DELETE
6
Introduction to SQL (continued)

American National Standards Institute (ANSI)
prescribes a standard SQL

Several SQL dialects exist

Oracle, MySQL, Access etc
7
Data Manipulation Commands
*
* Ignore Insert command for now
8
Sample Table: PART
PART
NUMBER
PART
DESC
AX12
Iron
AZ52
ON
HAND
CLASS
WAREHOUSE
PRICE
104
HW
3
23.95
Dartboard
20
SG
2
12.95
BA74
Basketball
40
SG
1
29.95
BH22
Cornpopper
95
HW
3
24.95
BT04
GasGrill
11
AP
2
149.99
BZ66
Washwer
52
AP
3
399.99
CA14
Gridle
78
HW
3
39.99
CB03
Bike
44
SG
1
299.99
CX11
Blender
112
HW
3
22.95
CZ81
Treadmill
68
SG
2
349.99
9
Listing Table Rows

SELECT


Used to list contents of table
Syntax

SELECT Field1, Field 2,…
FROM tablename
WHERE Condition 1 AND/OR Condition 2
ORDER BY Field1, Field 2,…
10
Listing Table Rows

At a minimum, must specify what you want to
select and where you want to select it from

SELECT PART_NUMBER FROM PART
11
Listing Table Rows, specifying a
specific field name
12
Listing All Table Rows

Asterisk can be used as wildcard character to
list all attributes

SELECT * FROM PART
13
Listing Table Rows with * to
represent all field names
14
Selecting Rows with Comparison
Operators

Select partial table contents by placing
restrictions on rows to be included in output


Add conditional restrictions to the SELECT
statement, using WHERE clause
SELECT * FROM PART WHERE ON_HAND >
30
15
Listing Table Rows with * to
represent all field names
16
Comparison Operators
17
Selecting Rows with Comparison
Operators
Note criteria is in
Parenthesis –
PART_NUMBER is a
character field
SELECT * FROM PART WHERE PART_NUMBER = ‘AX12’
18
Sorting Output

Data is displayed in the order which it was
added to the tables initially

To change the order the data is displayed in,
use the ORDER BY clause in the SELECT
statement

SELECT * FROM PART ORDER BY
ON_HAND
19
Sorting Output – Single Column
SELECT * FROM PART ORDER BY ON_HAND
20
Sorting Output – Multiple
Columns
Note how boat name is
sorted within owner num
SELECT * FROM PART ORDER BY PRICE, PART_NUMBER
21
Sorting Output

Data is displayed in the order which it was
added to the tables initially

To sort data in descending order, use the
DESC keyword after each field specified in the
ORDER BY clause that is to be displayed in
descending order
22
In Summary

SELECT statement


Used to list contents of table
Syntax

SELECT Field1, Field 2,…
FROM tablename
WHERE Condition 1 AND/OR Condition 2
ORDER BY Field1, Field 2,…
23
Comparison Operators
24