--
-- SAMPLE DERBY STATEMENTS
--

-- Add a new column with a column-level constraint
-- to an existing table
-- An exception will be thrown if the table
-- contains any rows
-- since the newcol will be initialized to NULL
-- in all existing rows in the table
ALTER TABLE CITIES ADD COLUMN REGION VARCHAR(26)
    CONSTRAINT NEW_CONSTRAINT CHECK (REGION IS NOT NULL);

-- Add a new unique constraint to an existing table
-- An exception will be thrown if duplicate keys are found
ALTER TABLE SAMP.DEPARTMENT
    ADD CONSTRAINT NEW_UNIQUE UNIQUE (DEPTNO);

-- add a new foreign key constraint to the
-- Cities table. Each row in Cities is checked
-- to make sure it satisfied the constraints.
-- if any rows don't satisfy the constraint, the
-- constraint is not added
ALTER TABLE CITIES ADD CONSTRAINT COUNTRY_FK
    Foreign Key (COUNTRY) REFERENCES COUNTRIES (COUNTRY);

-- Add a primary key constraint to a table
-- First, create a new table
CREATE TABLE ACTIVITIES (CITY_ID INT NOT NULL,
    SEASON CHAR(2), ACTIVITY VARCHAR(32) NOT NULL);
-- You will not be able to add this constraint if the
-- columns you are including in the primary key have
-- null data or duplicate values.
ALTER TABLE Activities ADD PRIMARY KEY (city_id, activity);

-- Drop the city_id column if there are no dependent objects:
ALTER TABLE Cities DROP COLUMN city_id RESTRICT;
-- Drop the city_id column, also dropping all dependent objects:
ALTER TABLE Cities DROP COLUMN city_id CASCADE;

-- Drop a primary key constraint from the CITIES table

ALTER TABLE Cities DROP CONSTRAINT Cities_PK;
-- Drop a foreign key constraint from the CITIES table
ALTER TABLE Cities DROP CONSTRAINT COUNTRIES_FK;
-- add a DEPTNO column with a default value of 1
ALTER TABLE SAMP.EMP_ACT ADD COLUMN DEPTNO INT DEFAULT 1;
-- increase the width of a VARCHAR column
ALTER TABLE SAMP.EMP_PHOTO ALTER PHOTO_FORMAT SET DATA TYPE VARCHAR(30);
-- change the lock granularity of a table
ALTER TABLE SAMP.SALES LOCKSIZE TABLE;

-- Remove the NOT NULL constraint from the MANAGER column
ALTER TABLE Employees ALTER COLUMN Manager NULL;
-- Add the NOT NULL constraint to the SSN column
ALTER TABLE Employees ALTER COLUMN ssn NOT NULL;

-- Change the default value for the SALARY column
ALTER TABLE Employees ALTER COLUMN Salary DEFAULT 1000.0;
ALTER TABLE Employees ALTER COLUMN Salary DROP DEFAULT;


CREATE FUNCTION TO_DEGREES
    ( RADIANS DOUBLE )
    RETURNS DOUBLE
    PARAMETER STYLE JAVA
    NO SQL LANGUAGE JAVA
    EXTERNAL NAME 'java.lang.Math.toDegrees';


CREATE FUNCTION PROPERTY_FILE_READER
    ( FILENAME VARCHAR( 32672 ) )
    RETURNS TABLE
      (
         KEY_COL     VARCHAR( 10 ),
         VALUE_COL VARCHAR( 1000 )
      )
    LANGUAGE JAVA
    PARAMETER STYLE DERBY_JDBC_RESULT_SET
    NO SQL
    EXTERNAL NAME 'vtis.example.PropertyFileVTI.propertyFileVTI';

CREATE INDEX OrigIndex ON Flights(orig_airport);

CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY);

CREATE INDEX IXSALE ON SAMP.SALES (SALES);

CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
    IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2))
    PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 
    'com.acme.sales.calculateRevenueByMonth';

CREATE ROLE purchases_reader;

CREATE ROLE purchases_reader_role;

CREATE SCHEMA FLIGHTS AUTHORIZATION anita;

CREATE SCHEMA EMP;

CREATE SCHEMA AUTHORIZATION takumi; 

CREATE SYNONYM SAMP.T1 FOR SAMP.TABLEWITHLONGNAME;

CREATE TABLE HOTELAVAILABILITY
     (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL,
    ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE));

CREATE TABLE PEOPLE
    (PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
    CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26));
    
CREATE TABLE greetings
  (i int generated by default as identity (START WITH 2, INCREMENT BY 1), ch char(50));

CREATE TABLE GROUPS
    (GROUP_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY 
    (START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));

CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
  FOR EACH ROW MODE DB2SQL
  values app.notifyEmail('Jerry', 'Table x is about to be updated'); 


CREATE TRIGGER FLIGHTSDELETE
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD_TABLE AS DELETEDFLIGHTS
  FOR EACH STATEMENT 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
  (SELECT FLIGHT_ID FROM DELETEDFLIGHTS);

CREATE TRIGGER FLIGHTSDELETE3
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD AS OLD
  FOR EACH ROW 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;



CREATE VIEW SAMP.V1 (COL_SUM, COL_DIFF)
    AS SELECT COMM + BONUS, COMM - BONUS
    FROM SAMP.EMPLOYEE;

CREATE VIEW SAMP.VEMP_RES (RESUME)
    AS VALUES 'Delores M. Quintana', 'Heather A. Nicholls', 'Bruce Adamson';

CREATE VIEW SAMP.PROJ_COMBO 
    (PROJNO, PRENDATE, PRSTAFF, MAJPROJ) 
            AS SELECT PROJNO, PRENDATE, PRSTAFF, MAJPROJ
            FROM SAMP.PROJECT UNION ALL 
        SELECT PROJNO, EMSTDATE, EMPTIME, EMPNO 
            FROM SAMP.EMP_ACT 
            WHERE EMPNO IS NOT NULL;

CREATE VIEW V1 (C1) AS SELECT SIN(C1) FROM T1;

declare global temporary table SESSION.t1(c11 int) not logged;
-- The SESSION qualification is redundant here because temporary 
-- tables can only exist in the SESSION schema. 

declare global temporary table t2(c21 int) not logged; 
-- The temporary table is not qualified here with SESSION because temporary 
-- tables can only exist in the SESSION schema.

DROP FUNCTION some_function_name;

DROP INDEX OrigIndex;

DROP INDEX DestIndex;

DROP PROCEDURE some_procedure_name;

DROP ROLE reader;

-- The RESTRICT keyword is required
DROP SCHEMA SAMP RESTRICT;

DROP SYNONYM some_synonym_name;

DROP TABLE some_table_name;

DROP TRIGGER TRIG1;

DROP VIEW AnIdentifier;

GRANT SELECT ON TABLE t TO maria,harry; 

GRANT UPDATE, TRIGGER ON TABLE t TO anita,zhi;

GRANT SELECT ON TABLE s.v to PUBLIC;

GRANT EXECUTE ON PROCEDURE p TO george;

GRANT purchases_reader_role TO george,maria;

GRANT SELECT ON TABLE t TO purchases_reader_role;

INSERT INTO COUNTRIES
      VALUES ('Taiwan', 'TW', 'Asia');

INSERT INTO MA_EMP_ACT
    SELECT * FROM EMP_ACT
    WHERE SUBSTR(PROJNO, 1, 2) = 'MA';
    
-- Insert the DEFAULT value for the LOCATION column
INSERT INTO DEPARTMENT
      VALUES ('E31', 'ARCHITECTURE', '00390', 'E01', DEFAULT);
    
LOCK TABLE FlightAvailability IN EXCLUSIVE MODE;

LOCK TABLE Maps IN EXCLUSIVE MODE;

RENAME INDEX DESTINDEX TO ARRIVALINDEX;

RENAME TABLE SAMP.EMP_ACT TO EMPLOYEE_ACT;


