8 - 1 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] CIS 8040 - Structured Query Language (SQL) SQL Outline Data Definition in SQL Indexes in SQL Database Queries in SQL Aggregate Functions of SQL Special Features of SQL Database Update in SQL8 - 2 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] SQL Statements Relational DBMS must  Allow to define and manipulate relations  Manage DB in terms of relations  Accept normalized relations SQL ( structured query language )  ANSI and ISO standard for relational DB  Supported by other non-relational DB systems SQL statements  Data definition:  create table, alter table, drop table, create view, drop view, create index, drop index  Data manipulation:  select, insert, delete A Relational DB Example (print out and bring to class) Supplier SPJ Part Project8 - 3 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] DDL - Create Relations CREATE TABLE TableName (colName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK searchCondition] [,...]} ] [PRIMARY KEY (listOfColumns),] [UNIQUE (listOfColumns),] […,]] [FOREIGN KEY (listOfFKColumns) REFERENCES ParentTableName [(listOfCKColumns)], [ON UPDATE referentialAction] [ON DELETE referentialAction ]] [,…]] [CHECK (searchCondition)] [,…]] ) BNF Notation (revisited): •All caps or boldface indicate required reserved words •Mixed case or italics indicate user-supplied values •Square brackets indicates optional •Parentheses are significant. Must be included if shown •Bar character (“|”) indicates an or condition Create Relations (alternate BNF) Create Table TableName (colName dataType [Not Null] [Unique] [Default defaultOption] [Check searchCondition] [,...]} ] [Primary Key (listOfColumns),] [Unique (listOfColumns)] [,…,]] [Foreign Key (listOfFKColumns) References ParentTableName [(listOfCKColumns)], [On Update referentialAction] [On Delete referentialAction ]] [,…]] [Check (searchCondition)] [,…]] ) BNF Notation: •All caps or boldface indicate required reserved words •Mixed case or italics indicate user-supplied values •Square brackets indicates optional •Parentheses are significant. Must be included if shown •Bar character (“|”) indicates an or condition8 - 4 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Create Relations - Example Some attribute types in Oracle: integer, number( p, q ), char( n ), varchar2(n), nchar(n), nvarchar2(n), date( dd-monthabbrev-yyyy ), long, raw(p), long raw, rowid. DB2 types accepted by Oracle: smallint, decimal(p,q), float, varchar(n), long, varchar  Note: the actual entering the DDL statement is not normally case sensitive  BUT, the one shown above is unique to Oracle and can be case sensitive  The NULL value represents a unknown value or is not applicable. Create table DMCDONALD.AUTHORS (AUID NVARCHAR2(10) NOT NULL, BOOKNO NVARCHAR2(13) NOT NULL, AUNAME NVARCHAR2(25), PRIMARY KEY (AUID), FOREIGN KEY (BOOKNO) REFERENCES DMCDONALD."Books" ON DELETE CASCADE); Create Table - Notes Creates a table with one or more columns of the specified dataType. With NOT NULL, system rejects any attempt to insert a null in the column. Can specify a DEFAULT value for the column. Primary keys should always be specified as NOT NULL. FOREIGN KEY clause specifies FK along with the referential action8 - 5 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Create Relations (cont’d) CREATE DOMAIN OwnerNumber AS VARCHAR(5) CHECK (VALUE IN (SELECT ownerNo FROM PrivateOwner)); CREATE DOMAIN StaffNumber AS VARCHAR(5) CHECK (VALUE IN (SELECT staffNo FROM Staff)); CREATE DOMAIN PNumber AS VARCHAR(5); CREATE DOMAIN PRooms AS SMALLINT; CHECK(VALUE BETWEEN 1 AND 15); CREATE DOMAIN PRent AS DECIMAL(6,2) CHECK(VALUE BETWEEN 0 AND 9999.99); Create Relations (cont’d) CREATE TABLE PropertyForRent ( propertyNo PNumber NOT NULL, …. rooms PRooms NOT NULL DEFAULT 4, rent PRent NOT NULL DEFAULT 600, ownerNo OwnerNumber NOT NULL, staffNo StaffNumber, branchNo BranchNumber NOT NULL, PRIMARY KEY (propertyNo), FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL….);8 - 6 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Alter a Relation (a.k.a. Table)  New attribute values in existing tuples are initialized to null values  Values for the new attribute must set by the user  Only one attribute may be added at a time ALTER TABLE base-relation-name ADD attr-name data-type; DROP TABLE base-relation-name; alter table SUPPLIER add AptNum char( 5 ); drop table SUPPLIER; Indexes There is no order among tuples All tuples in a relation may be sorted by the primary key attribute values Indexes speed up data retrieval How many students’ tuples would we search for a query with and without indexes? Find all suppliers who are based in Paris? Supplier Index Table AddrIndex Ptr London Athens …..8 - 7 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Create Indexes CREATE [UNIQUE] INDEX index name ON base-relation-name ( attr-name [order], attr-name[order] ... ) [CLUSTER]; An index may consist of one or more attributes  Example: Address/Age index The Unique option forces unique index tuples  The primary key attribute of a relation can be simulated as an indexed attribute with Unique option created by the DBMS. The Cluster