Lecture4

Lecture4 - INTRODUCTION TO SQL Winter 2010 Lecture 4...

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

View Full Document Right Arrow Icon
INTRODUCTION TO SQL Winter 2010 Lecture 4
Background image of page 1

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

View Full DocumentRight Arrow Icon
Administrivia First assignment is due on Friday at 11am! Don’t forget about the late penalties… Teaching Assistants: Patrick Liang (pliang AT caltech) Office hours: Wednesdays 4pm-5pm, 104 Annenberg Rishi Chandy (rchandy AT caltech) Office hours: Thursdays 4pm-5pm, 104 Annenberg Email questions to them! Show up for office hours! Make them work for their pay! Donnie’s office hours: Tuesdays 3pm-5pm, 111 Anb
Background image of page 2
Last Time Finished off the relational algebra Can express many different kinds of queries Can modify relations: insert, update, delete The relational algebra is procedural Many different ways to compute a particular result The relational algebra is verbose Even with additional operations, many queries are very complicated expressions
Background image of page 3

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

View Full DocumentRight Arrow Icon
SQL SQL = Structured Query Language Original language was “SEQUEL” IBM’s System R project (early 1970’s) “Structured English Query Language” Caught on very rapidly Simple, declarative language for writing queries Also includes many other features Standardized by ANSI/ISO SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003 Most implementations loosely follow the standards (plenty of portability issues)
Background image of page 4
SQL Features Data Definition Language (DDL) Specify relation schemas (attributes, domains) Specify a variety of integrity constraints Access constraints on data Indexes and other storage “hints” for performance Data Manipulation Language (DML) Generally based on relational algebra Supports querying, inserting, updating, deleting data Very sophisticated features for multi-table queries Other useful tools Defining views, transactions, etc.
Background image of page 5

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

View Full DocumentRight Arrow Icon
SQL Basics SQL language is case-insensitive both keywords and identifiers (for the most part) SQL statements end with a semicolon SQL comments have two forms: Single-line comments start with two dashes -- This is a SQL comment. Block comments follow C style /* * This is a block comment in SQL. */
Background image of page 6
SQL Databases SQL relations are contained within a database Each application usually works against one database Several applications may use the same database, too An example from MySQL: CREATE DATABASE bank; USE bank; Creates a new, empty database called bank USE statement makes bank the “default” database for the current connection DDL and DML operations will be evaluated in the context of the default database
Background image of page 7

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

View Full DocumentRight Arrow Icon
Creating a SQL Table In SQL, relations are called “tables” Not exactly like relational model “relations” anyway Syntax: CREATE TABLE t ( attr1 domain1, attr2 domain2, ... , attrN domainN ); t is name of relation (table) attr1 , … are names of attributes (columns) domain1 , … are domains (types) of attributes
Background image of page 8
SQL Names Tables, columns, etc. require names Rules on valid names can vary dramatically across
Background image of page 9

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

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

This note was uploaded on 02/01/2010 for the course CS 42 taught by Professor Donniepinkston during the Spring '10 term at Caltech.

Page1 / 46

Lecture4 - INTRODUCTION TO SQL Winter 2010 Lecture 4...

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

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