INTRODUCTION TO SQL

1. INTRODUCTION TO SQL DAT702

2. WHAT IS SQL • Standard Query Language • Ability to access and manipulate databases • Retrieve data • Insert, delete, update records • Create and set permissions • databases, tables, procedures and views • Query databases

3. BUILDS ON PREVIOUS COURSES • ULI101 • HTML • Create a webpage • INT213 • ASP • Connect to database to make webpage functional • DAT702 • SQL • Manipulate data in database connected to webpage

4. RDBMS • Relational Database Management System • SQL, MS Access, Oracle, IBM DB2, SQL Server • Data is stored in database objects called tables • A table is a collection of related data organized into columns (fields) and rows (records)

5. KEYWORDS • Defined by SQL • Many are mandatory, but most are optional SELECT name FROM teams WHERE id = 9 • SELECT and FROM are mandatory • WHERE is optional

6. IDENTIFIERS • Names that are given to database objects such as tables and columns • “teams” is the table name • “name” and “id” are column names SELECT name FROM teams WHERE id = 9

7. CONSTANTS • Literals that represent fixed values • “9” is a numeric constant SELECT name FROM teams WHERE id = 9

8. CLAUSES • A portion of an SQL statement • The SELECT clause is “SELECT from” • The FROM clause is “FROM teams” • The WHERE clause is “WHERE id = 9”

9. SYNTAX • How the clause is put together • What keywords, identifiers and constants does it consist of • MOST IMPORTANTLY – are they in the correct order according to SQL

10. DDL COMMANDS • Used to manage database objects such as tables and columns • CREATE, ALTER and DROP mysql> create table teams (id int(5) not null primary key, name varchar(37) not null, divisionvarchar(2)); Query OK, 0 rows affected (0.05 sec)

11. CREATE mysql> create table teams (id int(5) not null primary key, name varchar(37) not null, division varchar(2)); Query OK, 0 rows affected (0.05 sec) +------------+-------------+-----+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+-----+------+---------+-------+ | id | int(5) | NO | PRI | NULL | | | name | varchar(37) | NO | | NULL | | | division | varchar(3) | YES | | NULL | | +------------+-------------+-----+------+---------+-------+ 3 rows in set (0.00 sec)

12. ALTER mysql> ALTER TABLE teams DROP COLUMN division; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 Check to make sure the column no longer exists

13. DROP mysql> DROP TABLE teams; Query OK, 0 rows affected (0.03 sec) Check to make sure the table no longer exists

14. DML COMMANDS • Used to manage data within tables and columns • INSERT, UPDATE and DELETE • (RE-CREATE THE TEAMS TABLE)

15. INSERT • mysql> insert into teams values (‘12345', ‘Toronto', ‘NE'); • Query OK, 1 row affected (0.02 sec)

16. UPDATE • UPDATE teams SET name = ‘TO‘ WHERE name = ‘Toronto'; • Query OK, 1 row affected (0.02 sec)

17. DELETE • DELETE FROM teams WHERE name = ‘TO'; • Query OK, 1 row affected (0.02 sec)

18. INSERT DATA FROM TEXT FILE • load data local infile'test.txt' into table TEAMS lines terminated by '\r\n';