5 Schema and Catalog Concepts SQL schema in SQL 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

6 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

8 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

9

10

11 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

14 Attribute Data Types and Domains in SQL (cont d.) Additional data types Timestamp data type (TIMESTAMP) Includes the DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds Optional WITH TIME ZONE qualifier INTERVAL data type Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp

15 Domain Attribute Data Types and Domains in SQL (cont d.) Name used with the attribute specification Makes it easier to change the data type for a domain that is used by numerous attributes Improves schema readability Example: CREATE DOMAIN SSN_TYPE AS CHAR(9);

20 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

21 Giving Names to Constraints Keyword CONSTRAINT Name a constraint Useful for later altering

22 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 <= Mgr_start_date);

23 Basic Retrieval Queries in SQL SELECT statement One basic statement for retrieving information from a database SQL allows a table to have two or more tuples that are identical in all their attribute values Unlike relational model Multiset or bag behavior

24 The SELECT-FROM-WHERE Structure of Basic SQL Queries Basic form of the SELECT statement:

28 Ambiguous Attribute Names Same name can be used for two (or more) attributes As long as the attributes are in different relations Must qualify the attribute name with the relation name to prevent ambiguity

35 Ordering of Query Results Use ORDER BY clause Keyword DESC to see result in a descending order of values Keyword ASC to specify ascending order explicitly ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC

36 Discussion and Summary of Basic SQL Retrieval Queries

37 INSERT, DELETE, and UPDATE Statements in SQL Three commands used to modify the database: INSERT, DELETE, and UPDATE

38 The INSERT Command Specify the relation name and a list of values for the tuple

39 The DELETE Command Removes tuples from a relation Includes a WHERE clause to select the tuples to be deleted

40 The UPDATE Command Modify attribute values of one or more selected tuples Additional SET clause in the UPDATE command Specifies attributes to be modified and new values

41 Additional Features of SQL Techniques for specifying complex retrieval queries Writing programs in various programming languages that include SQL statements Set of commands for specifying physical database design parameters, file structures for relations, and access paths Transaction control commands

42 Additional Features of SQL (cont d.) Specifying the granting and revoking of privileges to users Constructs for creating triggers Enhanced relational systems known as object-relational New technologies such as XML and OLAP

STRUCTURED QUERY LANGUAGE SQL Structured Query Language 4.1 Introduction Originally, SQL was called SEQUEL (for Structured English QUery Language) and implemented at IBM Research as the interface for an

SQL Copyright 2013 Ramez Elmasri and Shamkant B. Navathe Data Definition, Constraints, and Schema Changes Used to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database Copyright

CS 338 Basic SQL Part II Bojana Bislimovska Spring 2017 Major research Outline Basic Retrieval Queries Exercises Ambiguous Attribute Names Major research Same name can be used for two or more attributes

SQL: Concepts Todd Bacastow IST 210: Organization of Data 2/17/2004 1 Design questions How many entities are there? What are the major entities? What are the attributes of each entity? Is there a unique

SQL SQL Functionality stands for Structured Query Language sometimes pronounced sequel a very-high-level (declarative) language user specifies what is wanted, not how to find it number of standards original

Olaf Hartig olaf.hartig@liu.se Structured Query Language Declarative language (what data to get, not how) Considered one of the major reasons for the commercial success of relational databases Statements

Lecture 07 Spring 2018 Borough of Manhattan Community College 1 SQL Identifiers SQL identifiers are used to identify objects in the database, such as table names, view names, and columns. The ISO standard

SQL stands for Structured Query Language sometimes pronounced sequel a very-high-level (declarative) language user specifies what is wanted, not how to find it number of standards original ANSI SQL updated

CSC 261/461 Database Systems Lecture 6 Fall 2017 Use of WITH The WITH clause allows a user to define a table that will only be used in a particular query (not available in all SQL implementations) Used

SQL Structured Query Language Declarative Specify the properties that should hold in the result, not how to obtain the result Complex queries have procedural elements International Standard SQL1 (1986)