DOC PREVIEW
UTSA CS 3743 - SQL - Overview and DDL

This preview shows page 1-2-17-18-19-35-36 out of 36 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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 2OverviewSQL: 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.orgImplemented 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 forSchema 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 ExampleCreate 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 6OutlineOracle SQL interface: sqlplusSQL DDL Interactive SQL queries, update, viewPL/SQL for procedures, constraints, triggersEmbedded SQL (Oracle Pro*C/C++, JDBC)Oracle SQL  Standard SQL Oracle manual: Oracle SQL Reference Manual (on-line).Lecture 10 SQL: Overview 7Oracle DBMSA 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 AccountNeeded 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*PlusInteractive user environment.Runs SQL statements, PL/SQL statements, and other SQL*Plus commandsAllow simple command editing Documentation:On-line “help” command within sqlplusOn-line manual.User login: Unix$sqlplus userid/pwd@cs SQL> exitLecture 10 SQL: Overview 10Use SQL*Plus to Run SQLSQL*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_nameRun Unix commands: !commandRun editor on .sql file: edit filenameSet host editor: define_editor=viSave a journal file: spool filename spool ofCustomize environment: set optionOptions: echo on, pause on, pagesize 30, …View current settings: show allConfiguration file: login.sqlLecture 10 SQL: Overview 12SQL*Plus Command EditingShow command in buffer: listA set of simple line editing commands.Better use external editor, say, vi or emacs.Lecture 10 SQL: Overview 13SQL Data Definition LanguageUsed by DBA or Designer to specify schemaA 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 SchemaStudents(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 TableIn 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 Typeschar(n). String of n < 2000 charvarchar2(n). String up to n <= 4000 charlong. Char string of length up to 2GBnumber(n,m). n digits, m after decimal point.number. Integer or real up to 40 digitsinteger. Integer up to 40 digitsblob. Binary data up to 4 GB date. DD-MMM-YYtime. HH:MM:SSThese may differ from SQL2 & SQL-1999.Lecture 10 SQL: Overview 18SQL Integrity ConstraintsRules or regulations imposed to ensure data integrity.Column Constraints.Table Constraints.Assertions


View Full Document

UTSA CS 3743 - SQL - Overview and DDL

Download SQL - Overview and DDL
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view SQL - Overview and DDL and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view SQL - Overview and DDL 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?