# CONCEPTS & SOME OTHER SQL STATEMENTS

## 1. DDL and Constraints

### Creating CUSTOMER table

```sql
CREATE TABLE CUSTOMER (
    customerid INT,
    customername VARCHAR(20) NOT NULL,
    caddress VARCHAR(30),
    ccity CHAR(15),
    cstate CHAR(2),
    czip CHAR(6) NOT NULL,
    PRIMARY KEY (customerid),
    UNIQUE (customername)
);
```

### Creating ORDERS table with Primary Key and Foreign Key

```sql
CREATE TABLE ORDERS (
    orderno INT,
    orderdate DATE,
    customerid INT,
    CONSTRAINT PKORDERS
        PRIMARY KEY (orderno),
    CONSTRAINT FKORDERS
        FOREIGN KEY (customerid)
        REFERENCES CUSTOMER(customerid)
);
```

The `PRIMARY KEY` uniquely identifies each order, while the `FOREIGN KEY` establishes a relationship between `ORDERS` and `CUSTOMER`.

---

## 2. ON DELETE CASCADE

The foreign key can be defined with `ON DELETE CASCADE`.

```sql
CREATE TABLE ORDERS (
    orderno INT,
    orderdate DATE,
    customerid INT,
    CONSTRAINT PKORDERS
        PRIMARY KEY (orderno),
    CONSTRAINT FKORDERS
        FOREIGN KEY (customerid)
        REFERENCES CUSTOMER(customerid)
        ON DELETE CASCADE
);
```

When a customer row is deleted, the corresponding rows in `ORDERS` are also deleted.

---

## 3. ON DELETE SET NULL

The foreign key can also be defined using `ON DELETE SET NULL`.

```sql
CREATE TABLE ORDERS (
    orderno INT,
    orderdate DATE,
    customerid INT,
    CONSTRAINT PKORDERS
        PRIMARY KEY (orderno),
    CONSTRAINT FKORDERS
        FOREIGN KEY (customerid)
        REFERENCES CUSTOMER(customerid)
        ON DELETE SET NULL
);
```

When a referenced customer row is deleted, the corresponding `customerid` in `ORDERS` is set to `NULL`.

---

## 4. Views

A view is a virtual table derived from one or more base tables. The view definition is stored and the data is obtained when the view is used.

### Example 1: FINISH_QTY

```sql
CREATE VIEW FINISH_QTY(PRODFINISH, TOTAL_QTY)
AS
SELECT PRODUCTFINISH, SUM(QTYONHAND)
FROM PRODUCT
GROUP BY PRODUCTFINISH;
```

To display the view:

```sql
SELECT *
FROM FINISH_QTY;
```

### Example 2: ORDERING_CUSTOMER

```sql
CREATE VIEW ORDERING_CUSTOMER(CUSTOMERID, CUSTOMERNAME)
AS
SELECT CUSTOMERID, CUSTOMERNAME
FROM CUSTOMER
WHERE CUSTOMERID IN (
    SELECT CUSTOMERID
    FROM ORDERS
);
```

To retrieve data using the view:

```sql
SELECT CUSTOMERID, CUSTOMERNAME, CADDRESS
FROM CUSTOMER
WHERE CUSTOMERID IN (
    SELECT CUSTOMERID
    FROM ORDERING_CUSTOMER
);
```

### Example 3: CUST_PROD

```sql
CREATE VIEW CUST_PROD(CUSTNAME, PRODNAME, QTYORD)
AS
SELECT DISTINCT C.CUSTOMERNAME,
                P.PRODUCTDESC,
                R.QUANTITY
FROM PRODUCT P, REQUESTS R, ORDERS O, CUSTOMER C
WHERE P.PRODUCTNO = R.PRODUCTNO
  AND R.ORDERNO = O.ORDERNO
  AND O.CUSTOMERID = C.CUSTOMERID;
```

A view can subsequently be used in queries as if it were a regular table.

### Dropping a View

```sql
DROP VIEW ORDERING_CUSTOMER;
```

---

## 5. General Constraints — CREATE ASSERTION

An assertion can be used to specify a constraint involving multiple tables.

```sql
CREATE ASSERTION CHECK_QTY
CHECK (
    NOT EXISTS (
        SELECT *
        FROM PRODUCT P, REQUESTS R
        WHERE P.PRODUCTNO = R.PRODUCTNO
          AND QTYONHAND < QUANTITY
    )
);
```

The assertion ensures that the database state satisfies the specified condition.

---

## 6. Trigger

A trigger is an event-driven action that is automatically executed when a specified event occurs.

Example:

```sql
CREATE TRIGGER QTY_VIOLATION
BEFORE INSERT OR UPDATE OF QUANTITY
ON ORDERS
FOR EACH ROW
WHEN (
    NEW.QUANTITY > (
        SELECT QTYONHAND
        FROM PRODUCT
        WHERE PRODUCTNO = NEW.PRODUCTNO
    )
)
DO_PROCEDURE(NEW.PRODUCTNO);
```

The trigger checks for a quantity violation before an insertion or update takes place.