SQL: Overview and DDL Ch. 10.1 - 10.6OverviewComponents of SQLSQL: A Simple ExampleSQL: A Simple Example (cont.)OutlineOracle DBMSOracle DB User AccountOracle SQL*PlusUse SQL*Plus to Run SQLCommon SQL*Plus CommandsSQL*Plus Command EditingSQL Data Definition LanguageA Sample University SchemaCreate Students TableCreate Tables SyntaxOracle SQL Built-in Data TypesSQL Integrity ConstraintsColumn DefinitionColumn ConstraintsColumn Constraints ExampleTable ConstraintsTable Constraints ExampleTable Constraints Example (cont.)Referential Integrity & Data UpdateReferential Integrity ExampleDrop TableAlter TableAlter Table (cont.)Simple Update StatementsExample of UpdateExample of Update (cont.)Data DictionaryData Dictionary (cont.)DDL SummaryLook AheadSQL: Overview and DDLCh. 10.1 - 10.6John OrtizLecture 10 SQL: Overview 2OverviewSQL: Structured Query Language, pronounced S. Q. L. or sequel.A standard language for relational, object-relational databases: SQL-86 by ANSI, SQL1 by ISO (87), SQL-89, SQL2 (92), SQL-1999. Web site: www.ansi.orgImplemented by all commercial R/OR DBMSs: Oracle, Sybase, Ingres, Informix, DB2, SQL Server, … (with various degree of conformity)We focus on SQL2 & SQL-1999 features and also Oracle SQL.Lecture 10 SQL: Overview 3Components of SQL Have statements forSchema definition & update tables, indexes, views, …Data Manipulation query, insertion, deletion, update, …Data integrity constraints, … System administration users, data security, performance tuning, … Application development procedures, transaction, embedded SQL, ……Lecture 10 SQL: Overview 4SQL: A Simple ExampleCreate a Students table create table Students ( SSN char(9) not null, Name varchar2(20), GPA number(3,2), Age number(2), primary key (SSN));Insert a Student tuple insert into Students values (312334455, ‘John Smith’, 3.25, 29);Lecture 10 SQL: Overview 5SQL: A Simple Example (cont.)Retrieve names of students whose GPA is higher than 3.0 and Age is less than 30. select Name from Students where GPA > 3.0 and Age < 30;What data types are supported?What constraints can be expressed? How?What if I want to change the schema? How to change data?How to express more complex queries?Lecture 10 SQL: Overview 6OutlineOracle SQL interface: sqlplusSQL DDL Interactive SQL queries, update, viewPL/SQL for procedures, constraints, triggersEmbedded SQL (Oracle Pro*C/C++, JDBC)Oracle SQL Standard SQL Oracle manual: Oracle SQL Reference Manual (on-line).Lecture 10 SQL: Overview 7Oracle DBMSA major commercial DBMS from Oracle (Object-Relational), supporting SQL.Components: Oracle server, SQL*Plus, Precompilers (Pro*C/C++, …), JDBC driver, SQLJ, WebDB, Forms, Report, …Platforms: Client-server, PC, Unix or Windows, Distributed, Parallel, …Multiuser, Security, Transactions, Recovery, We use it for course project and homework assignments.Lecture 10 SQL: Overview 8Oracle DB User AccountNeeded for every DB user. Has separate user id & password. Access through CSLan UNIX accounts. Environment variables for Oracle access: Oracle_BASE, Oracle_HOME, PATH, LD_PATH, CLASS_PATH, …On-line Instructions for using Oracle. “Using Oracle in CSLab” on course web page. On-line Oracle manuals (URL is given in the previous article).Lecture 10 SQL: Overview 9Oracle SQL*PlusInteractive user environment.Runs SQL statements, PL/SQL statements, and other SQL*Plus commandsAllow simple command editing Documentation:On-line “help” command within sqlplusOn-line manual.User login: Unix$sqlplus userid/pwd@cs SQL> exitLecture 10 SQL: Overview 10Use SQL*Plus to Run SQLSQL*plus understands only Oracle SQL.An SQL statement must end with semicolon ; a slash / on a line by itself, or a blank line.An SQL statement can be typed in, or loaded from a .sql file (get filename).An SQL can be executed from the command buffer (run), or from a .sql file (@filename)SQL statement in buffer can be written to a .sql file (save filename)Lecture 10 SQL: Overview 11Common SQL*Plus Commands Display schema: describe table_nameRun Unix commands: !commandRun editor on .sql file: edit filenameSet host editor: define_editor=viSave a journal file: spool filename spool ofCustomize environment: set optionOptions: echo on, pause on, pagesize 30, …View current settings: show allConfiguration file: login.sqlLecture 10 SQL: Overview 12SQL*Plus Command EditingShow command in buffer: listA set of simple line editing commands.Better use external editor, say, vi or emacs.Lecture 10 SQL: Overview 13SQL Data Definition LanguageUsed by DBA or Designer to specify schemaA set of statements used to define and to change the definition of tables, columns, data types, constraints, views, indexes, …SQL DDL & DML are integrated. A DDL statement often needs to contain some DML statements.Lecture 10 SQL: Overview 14A Sample University SchemaStudents(SID:string, Name:string, Age:integer, Sex:char, Major:string, GPA:real)Courses(Cno:string, Title:string, Hours:integer, Dept:string)Enrollment(SID:string, Cno:string, Year:string, Grade:string)Offers(Cno:string, Year:integer, FID:string)Faculty(FID:string, Name:string, Rank:string, Dept:string, Salary:real)Departments(Name:string, Location:string, ChairID:string)Lecture 10 SQL: Overview 15Create Students TableIn SQL*Plus: SQL> create table Students 2 (SID char(9) not null, 3 Name varchar2(25), 4 Age integer, 5 Sex char(1), 6 Major char(4), 7 GPA number(3,2), 8 primary key (SID));Lecture 10 SQL: Overview 16Create Tables Syntaxcreate table Table-Name ( Col-Name Type Deft-Val Col-Constraint, … Col-Name Type Deft-Val Col-Constraint, Table-Constraint, … Table-Constraint);Lecture 10 SQL: Overview 17Oracle SQL Built-in Data Typeschar(n). String of n < 2000 charvarchar2(n). String up to n <= 4000 charlong. Char string of length up to 2GBnumber(n,m). n digits, m after decimal point.number. Integer or real up to 40 digitsinteger. Integer up to 40 digitsblob. Binary data up to 4 GB date. DD-MMM-YYtime. HH:MM:SSThese may differ from SQL2 & SQL-1999.Lecture 10 SQL: Overview 18SQL Integrity ConstraintsRules or regulations imposed to ensure data integrity.Column Constraints.Table Constraints.Assertions
View Full Document