Class02.2_ch02_SQL_Spring_2010

Class02.2_ch02_SQL_Spring_2010 - Class 2 Introduction to...

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

View Full Document Right Arrow Icon
Class 2 Introduction to Structured Query Language (SQL) Spring 2010 BCIS 4620 Jack D. Becker
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 Class 2 Overview Announcements CISO – Most Thursdays – Please Join! Lab Times (see next slide) Chapter 2 (Kroenke) Introduction to SQL Homework #1: NX Tutorial Homework #2 [Preview]: Building a DB2/SQL Database
Background image of page 2
3 Lab Tutor Hours/ DRAFT [See My Website] All 4620 Tutors in Room 333) Sunday, 12P-4P, 4 hrs Monday, 8A-12P, 4 hrs Tuesday, 6P-10P, 4 hrs Wednesday, 8P-11P, 3 hrs Friday, 9A-11A, 2 hrs Saturday, 5P-8P, 3 hrs Surya Venkat
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Introduction Structured Query Language (SQL) is a data sublanguage that has constructs for defining and processing a database It can be Used stand-alone within a DBMS command Embedded in triggers and stored procedures Used in scripting or programming languages
Background image of page 4
5 SQL-92 SQL was developed by IBM in late 1970s SQL-92 was endorsed as a national standard by ANSI in 1992 SQL3 incorporates some object-oriented concepts but has not gained acceptance in industry Data Definition Language (DDL) is used to define database structures Data Manipulation Language (DML) is used to query and update data SQL statements are terminated with a semicolon (;)
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 SQL-92 3 Types of Statements Data Definition Language (DDL) CREATE TABLE, VIEW, SNYNONYM DROP, ALTER Data Manipulation Language (DML) SELECT, INSERT, UPDATE, DELETE Data Control Language (DCL) GRANT, REVOKE COMMIT, ROLLBACK
Background image of page 6
7 Sample Database IDEF1X “I-DE-FIX” Design D = Delete; I = Insert; U=Update; C=Cascade; R=Restrict P=Strong Identifier; FK=Foreign Key
Background image of page 7

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

View Full DocumentRight Arrow Icon
8 Sample Data -- Tables
Background image of page 8
9 Sample Data: EMPLOYEE
Background image of page 9

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

View Full DocumentRight Arrow Icon
10 CREATE TABLE CREATE TABLE statement is used for creating relations Each column is described with three parts: column name, data type, and optional constraints Example CREATE TABLE PROJECT ( ProjectID Integer Primary Key, Name Char(25) Unique Not Null, Department VarChar(100) Null, MaxHours Numeric(6,1) Default 100);
Background image of page 10
11 Data Types Standard data types Char for fixed-length character VarChar for variable-length character It requires additional processing than Char data types Integer for whole number Numeric There are many more data types in the SQL-92 standard Decimal, INT2, Binary, …
Background image of page 11

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

View Full DocumentRight Arrow Icon
12 Constraints Constraints can be defined within the CREATE TABLE statement, or they can be added to the table after it is created using the ALTER table statement Five types of constraints: PRIMARY KEY may not have null values UNIQUE may have null values NULL/NOT NULL may or may not have NULL value FOREIGN KEY constraint “enforced” CHECK for valid data value or range
Background image of page 12
13 ALTER Statement ALTER statement changes table structure, properties, or constraints after it has been created Example (See Fig. 6.2 next slide) ALTER TABLE ASSIGNMENT ADD CONSTRAINT EmployeeFK FOREIGN KEY (EmployeeNum) REFERENCES EMPLOYEE (EmployeeNumber) ON UPDATE CASCADE ON DELETE NO ACTION;
Background image of page 13

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

View Full DocumentRight Arrow Icon
Image of page 14
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 03/09/2011 for the course BCIS 4620 taught by Professor Staff during the Spring '08 term at North Texas.

Page1 / 52

Class02.2_ch02_SQL_Spring_2010 - Class 2 Introduction to...

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

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