Lecture 07 - Lecture 07 Comp355 Introduction to SQL Part I...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
Lecture 07 Comp355
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Introduction to SQL Part I
Background image of page 2
Objectives of SQL SQL is a transform-oriented language with 2 major components: A DDL for defining database structure. A DML for retrieving and updating data. SQL is relatively easy to learn: it is non-procedural - you specify what information you require, rather than how to get it;
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Objectives of SQL Consists of standard English words: 1) CREATE TABLE Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2)); 2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300); 3) SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000;
Background image of page 4
History of SQL In late 70s, ORACLE appeared and was probably first commercial RDBMS based on SQL. In 1987, ANSI and ISO published an initial standard for SQL. In 1989, ISO published an addendum that defined an ‘Integrity Enhancement Feature’. In 1992, first major revision to ISO standard occurred, referred to as SQL2 or SQL/92. In 1999, SQL3 was released with support for object- oriented data management.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Writing SQL Commands Most components of an SQL statement are case insensitive , except for literal character data. More readable with indentation and lineation: Each clause should begin on a new line. Start of a clause should line up with start of other clauses. If clause has several parts, should each appear on a separate line and be indented under start of clause.
Background image of page 6
DDL Data types supported by SQL standard. Main SQL DDL statements are: CREATE/ALTER DOMAIN DROP DOMAIN CREATE/ALTER TABLE DROP TABLE CREATE VIEW DROP VIEW
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ISO SQL Data Types
Background image of page 8
CREATE TABLE CREATE TABLE TableName ({colName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK searchCondition] [,. ..]} [PRIMARY KEY (listOfColumns),] {[FOREIGN KEY (listOfFKColumns) REFERENCES ParentTableName [(listOfCKColumns)], [ON UPDATE referentialAction] [ON DELETE referentialAction ]] [,…]} {[CHECK (searchCondition)] [,…] })
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Creates a table with one or more columns of the specified dataType . With NOT NULL, system rejects any attempt to insert a null in the column. Can specify a DEFAULT value for the column. Primary
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 48

Lecture 07 - Lecture 07 Comp355 Introduction to SQL Part I...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online