DBS 201 Introduction to Structure Query Language SQL

DBS 201: Introduction to Structure Query Language (SQL) Lecture 1

Agenda l l The basic commands and functions of SQL How to use SQL to query a database to extract useful information 2

Introduction to SQL: Structured Query Language l Designed specifically for communicating with databases l SQL functions fit into two broad categories: l l l Data definition language Data manipulation language 3

Introduction to SQL (continued) l Data definition language l SQL includes commands to create l l l Database objects such as tables, indexes, and views Commands to define access rights to those database objects Data manipulation language l Includes commands to insert, update, delete, and retrieve data within the database tables 4

Introduction to SQL (continued) l SQL is relatively easy to learn l Basic command set has a vocabulary of less than 100 words l Sample vocabulary: l l l CREATE COLLECTION CREATE TABLE CREATE VIEW 5

Introduction to SQL (continued) l Sample vocabulary (continued): l l l l DROP COLLECTION DROP TABLE DROP VIEW ALTER INSERT SELECT DELETE 6

Introduction to SQL (continued) l American National Standards Institute (ANSI) prescribes a standard SQL l Several SQL dialects exist l Oracle, My. SQL, Access etc 7

Data Manipulation Commands * * Ignore Insert command for now 8

Sample Table: PART NUMBER PART DESC AX 12 Iron AZ 52 ON HAND CLASS WAREHOU SE PRICE 104 HW 3 23. 95 Dartboard 20 SG 2 12. 95 BA 74 Basketball 40 SG 1 29. 95 BH 22 Cornpopper 95 HW 3 24. 95 BT 04 Gas. Grill 11 AP 2 149. 99 BZ 66 Washwer 52 AP 3 399. 99 CA 14 Gridle 78 HW 3 39. 99 CB 03 Bike 44 SG 1 299. 99 CX 11 Blender 112 HW 3 22. 95 CZ 81 Treadmill 68 SG 2 349. 99 9

Listing Table Rows l SELECT l l Used to list contents of table Syntax l SELECT Field 1, Field 2, … FROM tablename WHERE Condition 1 AND/OR Condition 2 ORDER BY Field 1, Field 2, … 10

Listing Table Rows l At a minimum, must specify what you want to select and where you want to select it from l SELECT PART_NUMBER FROM PART 11

Listing Table Rows, specifying a specific field name 12

Listing All Table Rows l Asterisk can be used as wildcard character to list all attributes l SELECT * FROM PART 13

Listing Table Rows with * to represent all field names 14

Selecting Rows with Comparison Operators l Select partial table contents by placing restrictions on rows to be included in output l l 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 = ‘AX 12’ 18

Sorting Output l Data is displayed in the order which it was added to the tables initially l To change the order the data is displayed in, use the ORDER BY clause in the SELECT statement l 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 l Data is displayed in the order which it was added to the tables initially l 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 l SELECT statement l l Used to list contents of table Syntax l SELECT Field 1, Field 2, … FROM tablename WHERE Condition 1 AND/OR Condition 2 ORDER BY Field 1, Field 2, … 23

Comparison Operators 24