SQL: DDL, ICs, Updates and Views - University of Wisconsin ...
SQL: DDL, ICs, Updates and Views
Module 3, Lecture 5
Database Management Systems, R. Ramakrishnan
1
SQL is More Than Just a Query Language
y Data-definition language (DDL):
? Create / destroy / alter relations and views. ? Define integrity constraints (IC's).
y Update language:
? Insert /delete / modify (update) tuples. ? Interact closely with ICs.
y Access Control:
? Can grant / revoke the right to access and manipulate tables (relations / views).
Database Management Systems, R. Ramakrishnan
2
Creating Relations
CREATE TABLE Boats (bid: INTEGER, bname: CHAR(10), color: CHAR(10))
y Creates the Boats relation that we know and love. Three fields, names and types as shown.
CREATE TABLE Reserves (sname: CHAR(10), bid: INTEGER, day: DATE)
y A small change: Reserves uses sname instead of sid. y No ICs have been specified. (We'll discuss this later.)
Database Management Systems, R. Ramakrishnan
3
Destroying and Altering Relations
DROP TABLE Boats
y Destroys the relation Boats. The schema information and the tuples are deleted.
ALTER TABLE Boats ADD COLUMN boatkind: CHAR(10)
y The schema of Boats is altered by adding a new field; every tuple in the current instance is extended with a null value in the new field.
Database Management Systems, R. Ramakrishnan
4
Creating Indexes
CREATE INDEX NameColorInd ON Boats (bname, color)
y Creates a B+-tree index on Boats, with (bname, color) as the search key.
? Question: What is order at bottom of tree?
y This statement is NOT included in the SQL/92 standard!
? Syntax usually differs slightly between systems.
? e.g., CREATE INDEX NameColorInd ON Boats
WITH STRUCTURE = BTREE, KEY = (bname,color)
y To drop an index (Sybase):
DROP INDEX Boats.NameColorInd
Database Management Systems, R. Ramakrishnan
5
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- constraints types of sql constraints duke university
- cs1307 database management systems lab manual
- create table students virginia tech
- sumita arora xii structured query language
- oracle sql tutorial emory university
- sql to hive cheat sheet cloudera
- defining relational schema cs 2451 database systems
- the relational model carnegie mellon school of computer
- sql data types and schemas integrity constraints
- chapter 4 advanced sql date and time data types in sql
Related searches
- university of scranton tuition and fees
- university of wisconsin school rankings
- state of wisconsin rights of cosigner
- university of illinois track and field
- state of wisconsin regulation and licensing
- university of wisconsin madison graduation
- university of wisconsin academic calendar
- university of wisconsin 2020 2021 calendar
- university of michigan sat scores and gpa
- state of wisconsin department of financial in
- kwame nkrumah university of science and tech
- employment law updates and news