SQL(Structured Query Language) : SQL(Structured Query Language) Albert Albert
Database Migration Team 07-08
What is SQL? : What is SQL? SQL =
a standard interactive and programming language for getting information from and updating a database.
History of SQL : History of SQL First Version
Developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s (SEQUEL)
Standardized in 1986 by ANSI, 1987 by ISO
SQL Language Elements : SQL Language Elements SQL Language is sub-divided into several language elements, including:
Statements
Queries
Expressions
Predicates
Clauses
Whitespace (format for readability)
Semicolon(“;”) as a statement terminator
Operation:: Queries : Operation:: Queries Queries
SQL common operation is the Query, which performed with the declarative SELECT keyword.
SELECT retrieves data from specified table.
SELECT is also the most complex statement in SQL.
Data Manipulation Language (DML)
Data Definition Language (DDL)
Queries : Queries FROM
indicates the source table/tables from which the data is to be retrieved.
WHERE
includes a comparison predicate, which is used to restrict the number of rows returned by the query.
GROUP BY
used to combine, or group, rows with related values into elements of a smaller set of rows.
HAVING
includes a comparison predicate used to eliminate rows after the GROUP BY clause is applied to the result set.
ORDER BY
used to identify which columns are used to sort the resulting data, and in which order they should be sorted.
Queries:: Simple Example : Queries:: Simple Example Fruits Table SELECT *
FROM fruits
WHERE Price > 3
ORDER BY FruitName; Results: Apple
Orange
Operation:: Data Manipulation : Operation:: Data Manipulation Queries
Data Manipulation Language (DML)
Other than query, SQL also includes syntax to update, insert, and delete records.
There query and update commands together form the DML part of SQL
Data Definition Language (DDL)
Data Manipulation : Data Manipulation UPDATE
updates data in a database table
DELETE
deletes data from a database table
INSERT
inserts new data into a database table
Data Manipulation:: INSERT : Data Manipulation:: INSERT INSERT
INTO Fruits (FruitName, Price)
VALUES (‘Grape’,’6’);
Data Manipulation:: UPDATE : Data Manipulation:: UPDATE UPDATE Fruits
SET price = ‘10’
WHERE FruitName = ‘Mango’;
Data Manipulation:: DELETE : Data Manipulation:: DELETE DELETE
FROM Fruits
WHERE FruitName = ‘Watermelon’;
Operation:: Data Definition : Operation:: Data Definition Queries
Data Manipulation Language (DML)
Data Definition Language (DDL)
The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
Data Definition : Data Definition CREATE
causes an object (a table, for example) to be created within the database.
DROP
causes an existing object within the database to be deleted, usually irretrievably
TRUNCATE
deletes all data from a table (non-standard, but common SQL statement)
ALTER
permits the user to modify an existing object in various ways
Data Definition:: Example : Data Definition:: Example CREATE TABLE Fruits
(
FruitName VARCHAR(50),
Price INT,
PRIMARY KEY(FruitName, Price)
); Command Result
Project:: Why SQL? (vs Access) : Project:: Why SQL? (vs Access)
Project:: Database Conversion : Project:: Database Conversion Access 2000 to MS SQL
Access 2000 feature
Upsizing Wizard !
Limitation
Query not Converted !
Problem if the content don’t match with the data definition.
End : End Question?