1 / 36

SQL: Overview and DDL Ch. 10.1 - 10.6

SQL: Overview and DDL Ch. 10.1 - 10.6. John Ortiz. Overview. 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

celeste-marty
Download Presentation

SQL: Overview and DDL Ch. 10.1 - 10.6

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL: Overview and DDLCh. 10.1 - 10.6 John Ortiz

  2. Overview • 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. SQL: Overview

  3. Components 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, … • … SQL: Overview

  4. SQL: 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); SQL: Overview

  5. SQL: 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? SQL: Overview

  6. Outline • 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). SQL: Overview

  7. Oracle 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. SQL: Overview

  8. Oracle 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). SQL: Overview

  9. Oracle 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> exit SQL: Overview

  10. Use 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) SQL: Overview

  11. Common 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 off • Customize environment: set option • Options: echo on, pause on, pagesize 30, … • View current settings: showall • Configuration file: login.sql SQL: Overview

  12. SQL*Plus Command Editing • Show command in buffer: list • A set of simple line editing commands. • Better use external editor, say, vi or emacs. SQL: Overview

  13. SQL 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. SQL: Overview

  14. A 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) SQL: Overview

  15. Create 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)); SQL: Overview

  16. Create Tables Syntax create table Table-Name ( Col-Name Type Deft-Val Col-Constraint, … Col-Name Type Deft-Val Col-Constraint, Table-Constraint, … Table-Constraint); SQL: Overview

  17. Oracle 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. SQL: Overview

  18. SQL Integrity Constraints • Rules or regulations imposed to ensure data integrity. • Column Constraints. • Table Constraints. • Assertions (Multiple-table Constraints). • Triggers. • Primary Key, Foreign Key, Check, Not Null, Unique, … SQL: Overview

  19. Column Definition • Syntax for column definition: col_name data_type [default value] [column constraints] • Syntax for column constraints: [constraint constraint_name] [not] null | check condition | unique | primary key | references table_name [(column)] [on delete cascade] SQL: Overview

  20. Column Constraints • not null. Can not take null value. • unique. Can not have identical non-null values • primary key. Both not null and unique • references T(A). All non-null values must be currently in T.A. • check (condition). Values must satisfy the check condition. • Can be expressed as table constraints, too. SQL: Overview

  21. Column Constraints Example SQL>create table Courses (CNo char(6) primary key, Title varchar2(50) not null, Hours integer default 3 check (Hours > 0 and hours < 6), Dept varchar2(20) references Departments(Name)); SQL: Overview

  22. Table Constraints • Syntax for table constraints: [constraint constraint_name] check condition | unique (column {, column}) | primary key (column {, column}) | foreign key (column {, column}) references table_name[(column {, column})] [on delete cascade] SQL: Overview

  23. Table Constraints Example SQL> create table Enrollment (SID char(9) not null references Students, CNo varchar2(7) not null, Year number(2) not null, Grade char(2), primary key (SID, CNo, Year), foreign key (CNo) references Courses); SQL: Overview

  24. Table Constraints Example (cont.) SQL> create table Students (SID char(9) primary key, Name varchar2(25), Age integer check(Age > 18 and Age < 100), Sex char check(Sex in {‘F’, ‘M’}), Major varchar2(4) GPA number (3,2) not null, constraint ic12 check (GPA >= 2.0 and (Major = ‘IS’ or GPA >= 3.0))); SQL: Overview

  25. Referential Integrity & Data Update • Assume that Courses.Dept references Departments.Name. What should the system do to students if we change a department’s name or delete a department? • SQL provides four options: • No action. Disallow such an update. • Cascade. Accept update and update all affected foreign key values. • Set default. Accept update & set default FK. • Set null. Accept update & set FK to null. SQL: Overview

  26. Referential Integrity Example SQL>create table Courses ( CNo char(6) not null primary key, Title varchar(35) not null, Hours int check (Hours between 1 and 5), Dept varchar(20), foreign key (Dept) references Departments(Name) on delete no action on update cascade); SQL: Overview

  27. Drop Table • Delete schema definition of a table. drop table Table-Name; • Problem: drop table Departments will fail if it is referenced by foreign keys. • Solution: drop table Departments cascade constraints; All referential constraints will be dropped before the table is dropped. SQL: Overview

  28. Alter Table • Change table schema (even after entering data) • Add a new column. alter table Students add (Address varchar2(40)); • Add a new constraint. alter table Students add (unique(Address)); • Modify a column definition. alter table Students modify (Name varchar2(30)); SQL: Overview

  29. Alter Table (cont.) • Remove a column. alter table Students drop (Address); • Enable and disable a constraint on a table alter table Students enable constraint ic12; alter table Students disable constraint ic12; • Newly added column can not be specified as not null. • Can not modify a column to a type of a smaller size. SQL: Overview

  30. Simple Update Statements • Insert Statement: insert into table_name [(column {, column})] [values (expression {, expression})] • Update Statement: update table_name [corr_name] set column = {expression | null} {, column = {expression | null}} [where search_condition] • Delete Statement: delete from table_name SQL: Overview

  31. Example of Update insert into Students values (`123456789', `Kathy', 26, ‘F’, ‘CS’, null) or insert into Students (Name, SID, Age, Major, Sex) values (`Kathy', `123456789', 26, ‘CS’, ‘F’) SQL: Overview

  32. Example of Update (cont.) • Increase the GPA of the student with SID = 123456789 by 0.5. update Students set GPA = GPA + 0.5 where SID = '123456789‘ • Delete all tuples from Students. delete from Students • The schema of Students remains. SQL: Overview

  33. Data Dictionary • Data dictionary (system catalog) contains information about all database objects (tables, views, indexes, sequences, etc). • Common Oracle Data Dictionary Tables • user_objects(object_name, object_id, object_type, created, last_ddl_time, timestamp, status) • Example database objects include tables, views, sequences, indexes, and packages. SQL: Overview

  34. Data Dictionary (cont.) • user_tables(table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_row_len) • user_tab_columns(name, table_name, column_name, data_type, data_length, nullable, column_id, default_length, data_default, num_distinct, low_value, high_value) • Use select * from dictionary to see all system tables and views. • Use describe table-name to view a schema. SQL: Overview

  35. DDL Summary • Specify appropriate data type for each column. You may also define your own domains. • Specify as many constraints as needed for applications. • Specify desirable actions for foreign key constraints. • Not all constraints can be specified at the same time. It is necessary to update schemas. • Major schema change after data is entered is very costly. SQL: Overview

  36. Look Ahead • Next topic: Interactive SQL • Read textbook: • Oracle 8 Programming: A primer by R. Sunderraman. Chapter 2. SQL: Overview

More Related