1 / 18

INTRODUCTION TO SQL

INTRODUCTION TO SQL. DAT702. 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. BUILDS ON PREVIOUS COURSES. ULI101 HTML

hovan
Download Presentation

INTRODUCTION TO SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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';

More Related