CREATE TABLE Students - Virginia Tech
CS 5614: Basic Data Definition and Modification in SQL
67
Introduction to SQL
V Structured Query Language (`Sequel')
V Serves as DDL as well as DML
V Declarative
V Say what you want without specifying how to do it V One of the main reasons for commercial success of DBMSs
V Many standards and implementations
V ANSI SQL V SQL-92/SQL-2 (Null operations, Outerjoins etc.) V SQL3 (Recursion, Triggers, Objects) V Vendor specific implementations
V "Bag Semantics" instead of "Set Semantics"
V Used in commercial RDBMSs
CS 5614: Basic Data Definition and Modification in SQL
68
Example:
V Create a Relation/Table in SQL
CREATE TABLE Students (sid CHAR(9), name VARCHAR(20), login CHAR(8), age INTEGER, gpa REAL);
V Support for Basic Data Types
V CHAR(n) V VARCHAR(n) V BIT(n) V BIT VARYING(n) V INT/INTEGER V FLOAT V REAL, DOUBLE PRECISION V DECIMAL(p,d) V DATE, TIME etc.
CS 5614: Basic Data Definition and Modification in SQL
69
More Examples
V And one for Courses
CREATE TABLE Courses (courseid CHAR(6), department CHAR(20));
V And one for their relationship!
CREATE TABLE takes
(sid CHAR(9),
V Why?
courseid CHAR(6));
V Can also provide default values
CREATE TABLE Students (sid CHAR(9), .... age INTEGER DEFAULT 21, gpa REAL);
CS 5614: Basic Data Definition and Modification in SQL
70
Examples Contd.
V DATE and TIME
V Implementations vary widely V Typically treated as strings of a special form V Allows comparisons of an ordinal nature ( etc.)
V DATE Example
V `1999-03-03' (No Y2K problems)
V TIME Examples
V `15:30:29' V `15:30:29.3875'
V Deleting a Relation/Table in SQL
DROP TABLE Students;
CS 5614: Basic Data Definition and Modification in SQL
71
Modifying Relation Schemas
V `Drop' an attribute (column)
ALTER TABLE Students DROP login;
V `Add' an attribute (column)
ALTER TABLE Students ADD phone CHAR(7);
V What happens to the new entry for the old records? V Default is `NULL' or say
ALTER TABLE Students ADD phone CHAR(7) DEFAULT `unknown';
V Always begin with `ALTER TABLE ' V Can use DEFAULT even with regular definition (as in Slide 69)
CS 5614: Basic Data Definition and Modification in SQL
72
How do you enter/modify data?
V INSERT command
INSERT INTO Students VALUES (`53688','Mark','mark2345',23,3.9)
V Cumbersome (use bulk loading; described later)
V DELETE command
DELETE FROM Students S WHERE S.name = `Smith'
V UPDATE command
UPDATE Students S SET S.age=S.age+1, S.gpa=S.gpa-1 WHERE S.sid = `53688'
CS 5614: Basic Data Definition and Modification in SQL
73
Domains
V Domains: Similar to Structs and other user-defined types
CREATE DOMAIN Email AS CHAR(8) DEFAULT `unknown'; .... login Email // instead of login CHAR(8) DEFAULT `unknown'
V Advantages: can be reused
junkaddress Email, fromaddress Email, toaddress Email, ....
V Can DROP DOMAINS too!
DROP DOMAIN Email;
V Affects only future declarations
CS 5614: Basic Data Definition and Modification in SQL
74
Keys
V To Specify Keys
V Use PRIMARY KEY or UNIQUE V Declare alongside attribute V For multiattribute keys, declare as a separate line
CREATE TABLE takes ( sid CHAR(9),
courseid CHAR(6), PRIMARY KEY (sid,courseid) );
V Whats the difference between PRIMARY KEY and UNIQUE?
V Typically only one PRIMARY KEY but any number of UNIQUE keys V Implementor allowed to attach special significance
CS 5614: Basic Data Definition and Modification in SQL
75
Creating Indices/Indexes
V Why?
V Speeds up query processing time
V For Students
CREATE INDEX indexone ON Students(sid); CREATE INDEX indextwo ON Students(login);
V How to decide attributes to place indices on?
V One is (typically) created by default on PRIMARY KEY V Creation of indices on UNIQUE attributes is implementation-dependent V In general, physical database design/tuning is very difficult! V Use Tools: Microsoft SQLServer has an index selection Wizard
V Why not place indices on all attributes?
V Too cumbersome for insertions/deletions/updates
V Like all things in computer science, there is a tradeoff! :-)
CS 5614: Basic Data Definition and Modification in SQL
76
Other Properties
V `NOT NULL' instead of DEFAULT
CREATE TABLE Students (sid CHAR(9), name VARCHAR(20), login CHAR(8), age INTEGER, gpa REAL);
V Can insert a tuple without a value for gpa
V NULL will be inserted
V If we had specified
gpa REAL NOT NULL);
V insert cannot be made!
................
................
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
- sql cheat sheet websitesetup
- 242 31 sql set operators so handy venn you need them sas support
- lecture 02 modern sql carnegie mellon university
- practice exam databricks
- advanced sql subqueries and complex joins mit opencourseware
- sql overview and highlights princeton university
- csc 443 data base management systems adelphi university
- sql basic concepts the university of edinburgh
- get sas r sy with proc sql mwsug
- create table students virginia tech
Related searches
- virginia tech finance banking and financial institutions
- create table sql in python
- create table db2 example
- db2 sql create table example
- db2 create table statement
- db2 create table sample
- sql create table examples
- virginia tech decision notification
- virginia tech admissions decisions
- virginia tech student success center
- virginia tech early decision notification
- virginia tech admission decision notification