SQL - Structured Query Language

Syllabus

SQL: SQL Data Definition and Data Types; Specifying constraints in SQL, Schema changing statements in SQL. (6.1, 6.2, 7.4)

SQL uses the terms table, row and column for the formal relational model terms relation, tuple, and attribute, respectively. The main SQL command for data definition is the CREATE statement, which can be used to create schemas, tables(relations), and domains (as well as other constructs such as views, assertions, and triggers).

SQL Data Definition and Data Types

An SQL Schema is identified by a schema name, and includes an authorization identifier to indicate the user or account who owns the schema, as well as descriptors for each element in the schema. Schema elements include tables, constraints, views, domains and other constructs (such as authorization grants) that determine the schema.

A Schema is created via the CREATE SCHEMA statement, which can include all the schema elements definition.

Example: To create a schema for airline-related tables and give the authorization ID anita access to all of the objects that use the schema, use the following syntax:

CREATE SCHEMA FLIGHTS AUTHORIZATION anita;

Parts of SQL Language

• Data-Definition Language: The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.

• Data-Manipulation Language: The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.

• Integrity: The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.

• View Definition: The SQL DDL includes commands for defining views.

• Transaction Control: SQL includes commands for specifying the beginning and end of transactions.

• Embedded SQL and Dynamic SQL: Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++ and Java.

• Authorization: The SQL DDL includes commands for specifying access rights to relations and views.

SQL Data Definition

The SQL standard supports a variety of built-in types, including:

• char(n): A fixed length character string with user-specified length n.

• varchar(n): A variable length character string with user specified maximum length n.

• binary(n): Equal to CHAR (), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1.

• int: An integer (a finite subset of the integers that is machine dependent).

• smallint: A small integer (a machine-dependent subset of the integer type).

• numeric(p,d): A fixed point number with user specified precision.

• real, double precision: Floating point and double precision floating point numbers with machine dependent precision.

• float(n): A floating point number, with precision of at least n digits.

• bool: Zero is considered as false, nonzero values are considered as true.

• date: A date. Format: YYYY-MM-DD.

• timestamp(fsp): A timestamp. Format: YYYY-MM-DD hh:mm:ss.

Specifying Constraints in SQL

Create Command

The CREATE TABLE statement is used to create a new table in a database.

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ...
);

Example:

CREATE TABLE Persons (
  ID int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Age int,
  PRIMARY KEY (ID)
);

CREATE TABLE Orders (
  OrderID int NOT NULL,
  OrderNumber int NOT NULL,
  PersonID int,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (PersonID)
    REFERENCES Persons(ID)
);

OR

ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(ID);

Deleting the Structure of a Table

DROP TABLE table_name;

Table Structure Display

DESCRIBE table_name;
DESC table_name;

Examples:

DESCRIBE Orders;
DESC Persons;

Insert into

INSERT INTO Persons (ID, Lastname, Firstname, Age)
VALUES (1001, 'Bobby', 'Brown', 29);

INSERT INTO Persons VALUES (1002, 'Stella', 'Green', 36);
INSERT INTO Orders VALUES (101, 1011, NULL);

Alter Statement to change the constraints on the table after creation

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Update

Setting or changing the value of a particular row based on the primary key of that table.

UPDATE Orders SET Personid = 1001 WHERE OrderId = 101;

Displaying the contents of a table

SELECT

SELECT * FROM Persons;
SELECT * FROM Orders;
SELECT orderid, ordernumber FROM Orders;

Schema Modification

The DROP Command

The DROP command can be used to drop named schema elements, such as tables, domains or constraints. One can also drop a schema. For example:

DROP SCHEMA Company CASCADE;
DROP SCHEMA Company RESTRICT;

If the RESTRICT option is chosen instead of CASCADE, a table is dropped only if it is not referred in any constraint.

The ALTER Command

The definition of a base table or of other named schema elements can be changed by using the ALTER command.

ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);
ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE;
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT;
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT '333445555';
ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;