N

Basic SQL

Chapter 6: Basic SQL

6.1 SQL Data Definition and Data Types

  • Basic SQL

    • SQL (Structured Query Language)

    • Considered a significant reason for the commercial success of relational databases.

    • Original appearance in 1974 and the last stable release was SQL:2011.

    • Core specifications established by standards organizations:

      • ANSI (since 1986)

      • ISO/IEC 9075 (since 1987).

  • Key Functions of SQL

    • SQL comprises statements for:

    • Data definition

    • Queries

    • Updates

    • It includes different categories such as:

    • DDL (Data Definition Language)

    • DML (Data Manipulation Language)

    • VDL/QDL (View Definition Language / Query Definition Language)

    • Plus specialized extensions that may vary depending on the specific implementation.

  • SQL Language Characteristics

    • SQL is case insensitive for keywords (e.g., SELECT, FROM) but case sensitive for data values (e.g., 'John' vs 'john').

    • Case style conventions may improve readability:

    • Each SQL statement typically ends with a semicolon (;), with a few exceptions (e.g., the USE statement).

Schema and Catalog Concepts in SQL

  • SQL Schema

    • Generally equivalent to a database in many systems.

    • Identified by a schema name and includes:

    • Authorization identifier

    • Descriptors for each element.

  • Schema Elements

    • Types of schema elements include:

    • Tables

    • Constraints

    • Views

    • Domains

    • Other constructs

  • SQL Terminology

    • Common terms:

    • Table, row, and column correlate to the relational model terms: relation, tuple, and attribute.

    • CREATE Statement

    • The main SQL command for data definition.

    • Commonly performed through user-friendly tools such as SQL Server Management Studio.

    • Other statements include:

    • MODIFY statement

    • DROP statement

Schema and Catalog Concepts in SQL (Continued)

  • CREATE SCHEMA Statement

    • Syntax: CREATE SCHEMA schema_name [auth];

    • Example: CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

  • Catalog

    • Defined as a named collection of schemas within an SQL environment (e.g., an installation of an SQL-compliant RDBMS on a computer system).

The CREATE TABLE Command in SQL

  • Purpose: To define a new table (relation).

    • Requires stating the table's name and specifying its attributes along with any initial constraints.

    • Optional specification of schema is available (e.g., CREATE TABLE COMPANY.EMPLOYEE ... or CREATE TABLE EMPLOYEE ...).

  • Base Tables vs. Virtual Relations

    • Base tables (base relations) are those where the relation and its tuples are physically created and stored as a file by the DBMS.

    • Virtual relations are created through statements such as CREATE VIEW.

CREATE TABLE Examples for Company Data

Example: EMPLOYEE Table Creation
CREATE TABLE EMPLOYEE
(Fname VARCHAR(15),
 Minit CHAR NOT NULL,
 Lname VARCHAR(15) NOT NULL,
 Ssn CHAR(9) NOT NULL,
 Bdate DATE,
 Address VARCHAR(30),
 Sex CHAR,
 Salary DECIMAL(10,2),
 Super_ssn CHAR(9),
 Dno INT NOT NULL,
 PRIMARY KEY (Ssn),
 FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn),
 FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber));
Example: DEPARTMENT Table Creation
CREATE TABLE DEPARTMENT
(Dname VARCHAR(15),
 Dnumber INT PRIMARY KEY,
 Mgr_ssn CHAR(9) NOT NULL,
 Mgr_start_date DATE NOT NULL,
 UNIQUE (Dname),
 FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn));
Example: DEPT_LOCATIONS Table Creation
CREATE TABLE DEPT_LOCATIONS
(Dnumber INT NOT NULL,
 Dlocation VARCHAR(15) NOT NULL,
 PRIMARY KEY (Dnumber, Dlocation),
 FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber));
Additional CREATE TABLE Examples

  • Project Table

CREATE TABLE PROJECT
(Pname VARCHAR(15) NOT NULL,
 Pnumber INT NOT NULL,
 Plocation VARCHAR(15),
 Dnum INT NOT NULL,
 PRIMARY KEY (Pnumber),
 UNIQUE (Pname),
 FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber));

  • Works On Table

CREATE TABLE WORKS_ON
(Essn CHAR(9) NOT NULL,
 Pno INT NOT NULL,
 Hours DECIMAL(3,1) NOT NULL,
 PRIMARY KEY (Essn, Pno),
 FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
 FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber));

  • Dependent Table

CREATE TABLE DEPENDENT
(Essn CHAR(9) NOT NULL,
 Dependent_name VARCHAR(15) NOT NULL,
 Sex CHAR,
 Bdate DATE,
 Relationship VARCHAR(8),
 PRIMARY KEY (Essn, Dependent_name),
 FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn));

CREATE TABLE Command Considerations

  • Order of Table Creation Matters

    • Foreign key references may result in errors if the referenced table does not exist.

    • Circular references must also be considered.

Attribute Data Types and Domains in SQL

  • Variability of Data Types Across Dialects

    • Different database systems (e.g., Microsoft SQL Server, Oracle, MySQL, PostgreSQL) may present variations in data type definitions.

  • Basic Data Types (Examples)

    • Numeric Data Types:

    • Integer numbers: INTEGER, INT, SMALLINT.

    • Floating-point: FLOAT, REAL, and DOUBLE PRECISION.

    • Character-string Data Types:

    • Fixed length: CHAR(n), CHARACTER(n).

    • Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n).

Additional Data Types

  • Bit-string Data Types:

    • Fixed length: BIT(n)

    • Varying length: BIT VARYING(n)

  • Boolean Data Type:

    • Can hold values: TRUE, FALSE, or NULL.

  • Date Data Type:

    • Should use DateTime. Structured as YYYY-MM-DD. Consists of 10 positions:

    • YEAR, MONTH, DAY.

Advanced Data Types in SQL

  • Timestamp Data Type:

    • Incorporating both DATE and TIME fields, with a minimum of six decimal places for fractions of seconds.

    • Optional WITH TIME ZONE qualifier.

  • Interval Data Type:

    • Represents a relative value for incrementing or decrementing an absolute value of date, time, or timestamp.

Creating Custom Domains

  • A custom domain simplifies changing the underlying data types for multiple attributes, enhancing schema readability:

    • Example: CREATE DOMAIN SSN_TYPE AS CHAR(9);

6.2 Specifying Constraints in SQL

  • Basic Constraint Types (refer to Chapter 5):

    • Key constraints

    • Referential integrity constraints

    • Restrictions on attribute domains and NULL values

    • Constraints on individual tuples within a relation.

Naming Constraints

  • The CONSTRAINT keyword is used to explicitly name a constraint, which aids in later alterations and understanding of the database schema.

Specifying Attribute Constraints

  • NOT NULL: Ensures that NULL is not allowed for a particular attribute.

  • Default Value: Using DEFAULT <value> to assign a default if none is provided.

  • CHECK Clause: Examples of using checks:

    • Example of defining a CHECK constraint:

    • Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

Specifying Restraints on Tuples Using CHECK

  • CHECK clauses can be appended at the end of a CREATE TABLE statement to enforce restrictions on tuples.

    • Example: CHECK (Dept_create_date <= Mgr_start_date);

Specifying Key and Referential Integrity Constraints

  • PRIMARY KEY Clause: Identifies one or more attributes that define the primary key of the table, e.g., Dnumber INT PRIMARY KEY;

  • UNIQUE Clause: Used to specify alternate/secondary keys, e.g., Dname VARCHAR(15) UNIQUE;

  • FOREIGN KEY Clause:

    • Default operation on violation is to reject the update.

    • Options available include SET NULL, CASCADE, and SET DEFAULT.

    • The DBMS treats the operations for SET NULL or SET DEFAULT similarly for both ON DELETE and ON UPDATE scenarios.

6.3 Basic Retrieval Queries in SQL

  • The SELECT Statement: The primary statement for querying information from a database.

    • SQL allows tables to hold duplicate tuples, known as multi-set or bag behavior, differing from strict relational models.

Structure of Basic SQL Queries

  • The basic form of the SELECT statement includes:

  SELECT <attribute list> 
  FROM <table list> 
  WHERE <condition>;

  • Explanation of Components:

    • <attribute list>: Names of the attributes whose values will be retrieved by the query.

    • <table list>: Names of the relations used in the query.

    • <condition>: A Boolean expression dictating which tuples are retrieved by the query.

SELECT Expression Components

  • Projection Attributes: Defined by the SELECT keyword, which indicates which data to retrieve.

  • Selection Condition: Specified via the WHERE clause, applying logical comparisons using operators (e.g., =, <>, <, <=, >, >=).

Example Queries

  • Query 0: Retrieve birth date and address for employee named 'John B. Smith'.

  SELECT Bdate, Address
  FROM EMPLOYEE
  WHERE Fname = 'John' AND Minit = 'B' AND Lname = 'Smith';

  • Query 1: Retrieve names and addresses of employees in the 'Research' department.

  SELECT Fname, Lname, Address
  FROM EMPLOYEE, DEPARTMENT
  WHERE Dname = 'Research' AND Dnumber = Dno;

  • Note the join condition Dnumber = Dno combines tuples from EMPLOYEE with DEPARTMENT based on matching values.

Handling Ambiguity with Attribute Names

  • SQL allows attributes to have the same name across different relations. To prevent ambiguity, qualify the attribute names with the corresponding relation names.

Aliasing and Renaming in SQL

  • The AS keyword can create aliases for attributes or tables:

    • Attribute Aliasing: SELECT attribute AS attr_alias_name

    • Table Aliasing: FROM table_name AS table_alias_name

    • Subquery Aliasing: FROM (subquery) AS virtual_table_name

    • Note: SQL syntax allows the AS keyword to be optional.

Handling Missing WHERE Clauses

  • An unspecified WHERE clause indicates there are no conditions placed on tuple selection, resulting in a CROSS PRODUCT—yielding all possible combinations of tuples.

  • Using an asterisk (*) allows you to retrieve all attribute values of selected tuples.

SQL Tables as Sets

  • SQL does not automatically eliminate duplicate tuples from query results. The DISTINCT keyword can be used in the SELECT clause to ensure only unique tuples are returned.

Set Operations in SQL

  • Set operations include:

    • UNION, INTERSECT, EXCEPT(Set difference).

    • Corresponding multiset operations are UNION ALL, EXCEPT ALL, INTERSECT ALL.

    • Macros can be implemented around binary set operators for simplification.

Substring Pattern Matching and Arithmetic Operators

  • The LIKE operator is used for pattern matching in strings:

    • % matches any number of characters,

    • _ matches a single character.

  • Standard arithmetic operators are:

    • Addition (+), subtraction (-), multiplication (*), and division (/).

    • The BETWEEN operator allows checking ranges within the conditions.

Ordering SQL Query Results

  • By default, SQL results are unordered; to explicitly order results, use the ORDER BY clause:

    • Utilize DESC for descending order and ASC for ascending order.

    • Example: ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC;

Summary Structure for Basic Retrieval Queries

  • The format of a retrieval query can be summarized as:

SELECT
FROM
[ WHERE <condition> ]
[ ORDER BY <attribute list> ];

6.4 INSERT, DELETE, and UPDATE Statements in SQL

  • Modifying Database Data: There are three primary commands:

    • INSERT: Adds new tuples to the database.

    • DELETE: Removes existing tuples from the relation.

    • UPDATE: Modifies existing attributes of one or more tuples.

INSERT Command

  • To use the INSERT command, specify the relation name along with a list of values for the new tuple:

INSERT INTO EMPLOYEE VALUES ('Richard', 'K', 'Marini', '653298653', '1962-12-30', '98 Oak Forest, Katy, TX', 'M', 37000, '653298653', 4);

  • Another Example:

INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber=W.Pno AND W.Essn=E.Ssn;

DELETE Command

  • Effectively removes tuples from a relation based on a specified WHERE clause:

DELETE FROM EMPLOYEE WHERE condition;

UPDATE Command

  • The UPDATE command is used to modify the attribute values of selected tuples by including an additional SET clause to specify the new values:

UPDATE EMPLOYEE SET Address = 'New Address' WHERE condition;

Additional Features of SQL

  • SQL provides techniques for specifying complex retrieval queries and allows integration with various programming languages.

  • Includes commands for defining physical database parameters, file structures for relations, and access paths, as well as transaction control commands.

  • Allows for the specification of user privileges (granting and revoking), triggers, and enhancements for object-relational systems.

  • Includes support for new technologies like XML and OLAP.

Summary of SQL

  • SQL is a comprehensive language used for:

    • Data definition, queries, updates, constraint specification, and view definition.

    • Key highlights from Chapter 6 include:

    • Data definition commands for creating tables.

    • Commands for specifying constraints.

    • Simple retrieval queries.

    • Database modification commands.

SQL Data Dictionary Language (DDL)

  • Schema manipulation is an essential aspect of DDL, including commands such as:

    • CREATE TABLE

    • CREATE INDEX

    • CREATE VIEW

    • CREATE CONSTRAINT

    • CREATE SCHEMA

    • CREATE DATABASE

SQL ALTER Command

  • The ALTER command provides functionality to modify schema elements, e.g.,

    • Modify existing tables or indexes.