Basic SQL PDF
Document Details
Uploaded by ClearAmbiguity2695
2011
Ramez Elmasri and Shamkant Navathe
Tags
Summary
This textbook introduces basic SQL concepts, commands and their applications in database systems including data types, constraints, and query operations. It provides insights into practical database design scenarios through examples and practical exercises. It's useful for readers learning SQL programming languages.
Full Transcript
Chapter 4 Basic SQL Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Basic SQL SQL language Considered one of the major reasons for the commercial success of relational databases SQL Structured Query...
Chapter 4 Basic SQL Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Basic SQL SQL language Considered one of the major reasons for the commercial success of relational databases SQL Structured Query Language Statements for data definitions, queries, and updates (both DDL and DML) Core specification Plus specialized extensions Copyright © 2011 Ramez Elmasri and Shamkant Navathe SQL Data Definition and Data Types Terminology: Table, row, and column used for relational model terms relation, tuple, and attribute CREATE statement Main SQL command for data definition Copyright © 2011 Ramez Elmasri and Shamkant Navathe Schema and Catalog Concepts in SQL SQL schema Identified by a schema name Includes an authorization identifier and descriptors for each element Schema elements include Tables, constraints, views, domains, and other constructs Each statement in SQL ends with a semicolon Copyright © 2011 Ramez Elmasri and Shamkant Navathe Schema and Catalog Concepts in SQL (cont’d.) CREATE SCHEMA statement CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’; Catalog Named collection of schemas in an SQL environment SQL environment Installation of an SQL-compliant RDBMS on a computer system Copyright © 2011 Ramez Elmasri and Shamkant Navathe The CREATE TABLE Command in SQL Specify a new relation Provide name Specify attributes and initial constraints Can optionally specify schema: CREATE TABLE COMPANY.EMPLOYEE... or CREATE TABLE EMPLOYEE... Copyright © 2011 Ramez Elmasri and Shamkant Navathe The CREATE TABLE Command in SQL (cont’d.) Base tables (base relations) Relation and its tuples are actually created and stored as a file by the DBMS Virtual relations Created through the CREATE VIEW statement Copyright © 2011 Ramez Elmasri and Shamkant Navathe Data Definition, Constraints, and Schema Changes Used to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database Slide 8- 8 Copyright © 2011 Ramez Elmasri and Shamkant Navathe CREATE TABLE Specifies a new base relation by giving it a name, and specifying each of its attributes and their data types (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n)) A constraint NOT NULL may be specified on an attribute CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) ); Slide 8- 9 Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe CREATE TABLE In SQL2, can use the CREATE TABLE command for specifying the primary key attributes, secondary keys, and referential integrity constraints (foreign keys). Key attributes can be specified via the PRIMARY KEY and UNIQUE phrases CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ); Slide 8- 11 Copyright © 2011 Ramez Elmasri and Shamkant Navathe DROP TABLE Used to remove a relation (base table) and its definition The relation can no longer be used in queries, updates, or any other commands since its description no longer exists Example: DROP TABLE DEPENDENT; Slide 8- 12 Copyright © 2011 Ramez Elmasri and Shamkant Navathe ALTER TABLE Used to add an attribute to one of the base relations The new attribute will have NULLs in all the tuples of the relation right after the command is executed; hence, the NOT NULL constraint is not allowed for such an attribute Example: ALTER TABLE DEPT ADD JOB VARCHAR(12); The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple. This can be done using the UPDATE command. Slide 8- 13 Copyright © 2011 Ramez Elmasri and Shamkant Navathe REFERENTIAL INTEGRITY OPTIONS We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys) CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ON DELETE SET DEFAULT ON UPDATE CASCADE); Slide 8- 14 Copyright © 2011 Ramez Elmasri and Shamkant Navathe REFERENTIAL INTEGRITY OPTIONS (continued) CREATE TABLE EMP( ENAME VARCHAR(30) NOT NULL, ESSN CHAR(9), BDATE DATE, DNO INTEGER DEFAULT 1, SUPERSSNCHAR(9), PRIMARY KEY (ESSN), FOREIGN KEY (DNO) REFERENCES DEPT ON DELETE SET DEFAULT ON UPDATE CASCADE, FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE); Slide 8- 15 Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe The CREATE TABLE Command in SQL (cont’d.) Some foreign keys may cause errors Specified either via: Circular references Or because they refer to a table that has not yet been created Copyright © 2011 Ramez Elmasri and Shamkant Navathe Attribute Data Types and Domains in SQL Basic data types Numeric data types Integer numbers: INTEGER, INT, and SMALLINT Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION Character-string data types Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Specifying Constraints in SQL Basic constraints: Key and referential integrity constraints Restrictions on attribute domains and NULLs Constraints on individual tuples within a relation Copyright © 2011 Ramez Elmasri and Shamkant Navathe Specifying Attribute Constraints and Attribute Defaults NOT NULL NULL is not permitted for a particular attribute Default value DEFAULT CHECK clause Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21); Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Specifying Key and Referential Integrity Constraints PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY; UNIQUE clause Specifies alternate (secondary) keys Dname VARCHAR(15) UNIQUE; Copyright © 2011 Ramez Elmasri and Shamkant Navathe Specifying Key and Referential Integrity Constraints (cont’d.) FOREIGN KEY clause Default operation: reject update on violation Attach referential triggered action clause Options include SET NULL, CASCADE, and SET DEFAULT Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE CASCADE option suitable for “relationship” relations Copyright © 2011 Ramez Elmasri and Shamkant Navathe Giving Names to Constraints Keyword CONSTRAINT Name a constraint Useful for later altering Copyright © 2011 Ramez Elmasri and Shamkant Navathe Specifying Constraints on Tuples Using CHECK CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually CHECK (Dept_create_date