Lecture_4-_SQL_and_Advanced_SQL

Lecture_4-_SQL_and_Advanced_SQL - CSCI-507 Database Theory...

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

View Full Document Right Arrow Icon
1 CSCI-507 Database Theory Lecture 3 Structured Query Language (SQL) & Advanced SQL 2 Objectives In this lecture, you will learn: • The basic commands and functions of SQL • How to use SQL for data administration (to create tables, indexes, and views) • How to use SQL for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database for useful information
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 3 Objectives (con’d.) • About the relational set operators UNION, UNION ALL, INTERSECT, and MINUS • How to use the advanced SQL JOIN operator syntax • About the different types of subqueries and correlated queries • How to use SQL functions to manipulate dates, strings, and other data 4 Objectives (cont’d.) • How to create and use updatable views • How to create and use triggers and stored procedures • How to create embedded SQL
Background image of page 2
3 5 Introduction to SQL • SQL functions fit into two broad categories: – Data definition language – Data manipulation language • Basic command set has vocabulary of fewer than 100 words • American National Standards Institute (ANSI) prescribes a standard SQL • Several SQL dialects exist 6 Data Definition Commands • The database model – In this chapter, a simple database with these tables is used to illustrate commands: • CUSTOMER • INVOICE • LINE • PRODUCT • VENDOR – Focus on PRODUCT and VENDOR tables
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 7 8 Creating the Database • Two tasks must be completed: – Create database structure – Create tables that will hold end-user data • First task: – RDBMS creates physical files that will hold database – Differs substantially from one RDBMS to another
Background image of page 4
5 9 The Database Schema • Authentication – DBMS verifies that only registered users are able to access database – Log on to RDBMS using user ID and password created by database administrator Schema – Group of database objects that are related to each other 10 Data Types • Data type selection is usually dictated by nature of data and by intended use • Supported data types: – Number(L,D), Integer, Smallint, Decimal(L,D) – Char(L), Varchar(L), Varchar2(L) – Date, Time, Timestamp – Real, Double, Float – Interval day to hour – Many other types
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 11 Creating Table Structures • Use one line per column (attribute) definition • Use spaces to line up attribute characteristics and constraints • Table and attribute names are capitalized • NOT NULL specification • UNIQUE specification 12 Creating Table Structures (cont’d.) • Primary key attributes contain both a NOT NULL and a UNIQUE specification • RDBMS will automatically enforce referential integrity for foreign keys • Command sequence ends with semicolon
Background image of page 6
7 13 SQL Constraints • NOT NULL constraint – Ensures that column does not accept nulls • UNIQUE constraint – Ensures that all values in column are unique • DEFAULT constraint – Assigns value to attribute when a new row is added to table • CHECK constraint – Validates data when attribute value is entered
Background image of page 7

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

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

This note was uploaded on 04/15/2011 for the course CSCI 507 taught by Professor Rashid during the Spring '11 term at George Mason.

Page1 / 50

Lecture_4-_SQL_and_Advanced_SQL - CSCI-507 Database Theory...

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

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