BoDyGuArD

CREATE TABLE DEPARTMENT
(DEPT_NO DEPTNO NOT aaaa
DEPARTMENT VARCHAR(25) NOT aaaa UNIQUE
HEAD_DEPT DEPTNO
MNGR_NO EMPNO
BUDGET BUDGET
:-):-):-):-):-):-):-):-) VARCHAR(15)
PHONE_NO PHONENUMBER DEFAULT "555-1234"
PRIMARY aaa (DEPT_NO)
FOREIGN aaa (HEAD_DEPT) REFERENCES DEPARTMENT (DEPT_NO));

CREATE TABLE EMPLOYEE
(EMP_NO EMPNO NOT aaaa
FIRST_NAME FIRSTNAME NOT aaaa
LAST_NAME LASTNAME NOT aaaa
PHONE_EXT VARCHAR(4)
HIRE_DATE DATE DEFAULT "NOW" NOT aaaa
DEPT_NO DEPTNO NOT aaaa
JOB_CODE JOBCODE NOT aaaa
JOB_GRADE JOBGRADE NOT aaaa
JOB_COUNTRY COUNTRYNAME NOT aaaa
SALARY SALARY NOT aaaa
aaaa_NAME COMPUTED BY (LAST_NAME || "" || FIRST_NAME))
PRIMARY aaa (EMP_NO)
FOREIGN aaa (DEPT_NO) REFERENCES
DEPARTMENT (DEPT_NO)
FOREIGN aaa (JOB_CODE JOB_GRADE JOB_COUNTRY) REFERENCES
JOB (JOB_CODE JOB_GRADE JOB_COUNTRY)
CHECK (SALARY >= (SELECT MIN_SALARY FROM JOB WHERE
JOB.JOB_CODE = EMPLOYEE.JOB_CODE AND
JOB.JOB_GRADE = EMPLOYEE.JOB_GRADE AND
JOB.JOB_COUNTRY = EMPLOYEE.JOB_COUNTRY) AND
SALARY <= (SELECT MAX_SALARY FROM JOB WHERE
JOB.JOB_CODE = EMPLOYEE.JOB_CODE AND
JOB.JOB_GRADE = EMPLOYEE.JOB_GRADE AND
JOB.JOB_COUNTRY = EMPLOYEE.JOB_COUNTRY));

CREATE INDEX name ON table (columns)

CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME FIRST_NAME);

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT PROJ_NAME);

NAMEX INDEX ON EMPLOYEE (LAST_NAME FIRST_NAME)

ALTER INDEX NAMEX INACTIVE;
DROP INDEX NAMEX;

CREATE UNIQUE INDEX NAMEX ON EMPLOYEE (LAST_NAME FIRST_NAME);

CREATE VIEW PHONE_LIST AS
SELECT EMP_NO FIRST_NAME LAST_NAME PHONE_EXT :-):-):-):-):-):-):-):-) PHONE_NO
FROM EMPLOYEE DEPARTMENT
WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO;

INSERT INTO table_name (col1 col2 ...) VALUES (val1 val2 ...);

INSERT INTO COUNTRY (COUNTRY CURRENCY) VALUES ("USA" "DOLLAR");
The next group inserts values into the DEPARTMENT table. For example:
INSERT INTO DEPARTMENT
(DEPT_NO DEPARTMENT HEAD_DEPT BUDGET :-):-):-):-):-):-):-):-) PHONE_NO)
VALUES
("000" "CORPORATE HEADQUARTERS" aaaa 1000000 "MONTEREY"
"(408) 555-1234");

SELECT * FROM DEPARTMENT;

CREATE TABLE SALES_EXT EXTERNAL "/PATH/SALES.DAT"
(PO_NUMBER CHAR(10)
CUST_NO CHAR(12)
SALES_REP CHAR(10)
ORDER_STATUS CHAR(13)
ORDER_DATE CHAR(12)
SHIP_DATE CHAR(12)
DATE_NEEDED CHAR(12)
PAID CHAR(7)
QTY_ORDERED CHAR(12)
TOTAL_VALUE CHAR(12)
DISCOUNT CHAR(16)
ITEM_TYPE CHAR(8)
EOL CHAR(1));

SELECT * FROM SALES_EXT;

INSERT INTO SALES
(PO_NUMBER CUST_NO SALES_REP ORDER_STATUS ORDER_DATE SHIP_DATE
DATE_NEEDED PAID QTY_ORDERED TOTAL_VALUE DISCOUNT ITEM_TYPE)
SELECT
PO_NUMBER CUST_NO SALES_REP ORDER_STATUS ORDER_DATE SHIP_DATE
DATE_NEEDED PAID QTY_ORDERED TOTAL_VALUE DISCOUNT ITEM_TYPE
FROM SALES_EXT;

SELECT * FROM SALES;

UPDATE table
SET column = value
WHERE condition

UPDATE EMPLOYEE
SET SALARY = SALARY + 2000
WHERE JOB_CODE = "SALES";

UPDATE EMPLOYEE
SET SALARY = SALARY + 2000
WHERE JOB_CODE = "SALES" AND HIRE_DATE < "01-Jan-1992";

SELECT DEPT_NO FROM EMPLOYEE
WHERE aaaa_NAME = "Young Katherine";

UPDATE EMPLOYEE
SET MNGR_NO = 107
WHERE DEPT_NO = "623";

UPDATE EMPLOYEE
SET MNGR_NO = 107
WHERE DEPT_NO = (SELECT DEPT_NO FROM EMPLOYEE
WHERE aaaa_NAME = "Young Katherine");

DELETE FROM table
WHERE condition

DELETE FROM EMPLOYEE
WHERE JOB_CODE = "Mngr";

DELETE FROM EMPLOYEE
WHERE JOB_CODE = "Srep" AND HIRE_DATE < "10-Jul-1993";

SELECT EMP_NO JOB_CODE HIRE_DATE FROM EMPLOYEE;

SELECT DEPT_NO FROM EMPLOYEE
WHERE aaaa_NAME = "Young Katherine";

DELETE FROM EMPLOYEE
WHERE DEPT_NO = "623";

DELETE FROM EMPLOYEE
WHERE DEPT_NO = (SELECT DEPT_NO FROM EMPLOYEE
WHERE aaaa_NAME = "Young Katherine");

SELECT [DISTINCT] columns
FROM tables
WHERE <search_conditions>
[GROUP BY column HAVING <search_condition>]
ORDER BY <sort_order>;

SELECT DEPARTMENT DEPT_NO aaaa_NAME EMP_NO
FROM DEPARTMENT EMPLOYEE
WHERE DEPARTMENT = "Engineering" AND MNGR_NO = EMP_NO;

SELECT * FROM PHONE_LIST;

SELECT JOB_CODE FROM JOB;

SELECT DISTINCT JOB_CODE FROM JOB;

SELECT DISTINCT JOB_CODE JOB_GRADE FROM JOB;

SELECT LAST_NAME FIRST_NAME PHONE_EXT
FROM EMPLOYEE
WHERE LAST_NAME = "Green";

LAST_NAME = "Green"

WHERE condition;

condition = column operator value [log_operator condition]
value = value arith_operator value

SELECT LAST_NAME FIRST_NAME PHONE_EXT
FROM EMPLOYEE
WHERE LAST_NAME = "Green";

WHERE NOT (LAST_NAME = “Green”

SELECT LAST_NAME FIRST_NAME EMP_NO FROM EMPLOYEE
WHERE LAST_NAME LIKE "%an";

SELECT LAST_NAME FIRST_NAME EMP_NO FROM EMPLOYEE
WHERE LAST_NAME LIKE "_e%";

SELECT LAST_NAME FIRST_NAME
FROM EMPLOYEE
WHERE FIRST_NAME STARTING WITH "Ke";

SELECT LAST_NAME FIRST_NAME
FROM EMPLOYEE
WHERE PHONE_EXT IS aaaa;

SELECT LAST_NAME FIRST_NAME PHONE_EXT
FROM EMPLOYEE
WHERE PHONE_EXT IS NOT aaaa;

SELECT LAST_NAME FIRST_NAME
FROM EMPLOYEE
WHERE SALARY BETWEEN 100000 AND 250000;

SELECT DEPT_NO LAST_NAME FIRST_NAME FROM EMPLOYEE
WHERE DEPT_NO IN (120 600 623);

SELECT DEPT_NO LAST_NAME FIRST_NAME HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_NO = 623 AND HIRE_DATE > "01-Jan-1992";

SELECT CUSTOMER CUST_NO
FROM CUSTOMER
WHERE COUNTRY = "USA" OR COUNTRY = "Canada";

SELECT LAST_NAME FIRST_NAME HIRE_DATE DEPT_NO
FROM EMPLOYEE
WHERE DEPT_NO = 623 OR DEPT_NO = 600
AND HIRE_DATE > "01-JAN-1992";

(WHERE DEPT_NO = 623)
OR
(WHERE DEPT_NO = 600 AND HIRE_DATE > "01-JAN-1992")

SELECT LAST_NAME FIRST_NAME HIRE_DATE DEPT_NO
FROM EMPLOYEE
WHERE (DEPT_NO = 623 OR DEPT_NO = 600)
AND HIRE_DATE > "01-JAN-1992";

SELECT JOB_COUNTRY FROM EMPLOYEE
WHERE EMP_NO = 144;

SELECT EMP_NO LAST_NAME FROM EMPLOYEE
WHERE JOB_COUNTRY = "USA";

SELECT EMP_NO LAST_NAME FROM EMPLOYEE
WHERE JOB_COUNTRY =
(SELECT JOB_COUNTRY FROM EMPLOYEE
WHERE EMP_NO = 144);

SELECT FIRST_NAME LAST_NAME
FROM EMPLOYEE
WHERE JOB_GRADE IN
(SELECT JOB_GRADE FROM JOB WHERE JOB_GRADE <= 2);

SELECT SALARY FROM EMPLOYEE
WHERE DEPT_NO = 623;

SELECT LAST_NAME DEPT_NO FROM EMPLOYEE
WHERE SALARY IN
(SELECT SALARY FROM EMPLOYEE WHERE DEPT_NO = 623);

SELECT LAST_NAME SALARY FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY FROM EMPLOYEE WHERE DEPT_NO = 623);

SELECT LAST_NAME SALARY FROM EMPLOYEE
WHERE SALARY > ANY
(SELECT SALARY FROM EMPLOYEE WHERE DEPT_NO = 623);

SELECT COUNT(DISTINCT JOB_CODE) FROM JOB;

SELECT COUNT(EMP_NO) MIN(HIRE_DATE) SUM(SALARY)
FROM EMPLOYEE;

SELECT JOB_CODE JOB_GRADE MAX_SALARY
FROM JOB WHERE JOB_COUNTRY = "USA";

SELECT JOB_CODE SUM(MAX_SALARY)
FROM JOB WHERE JOB_COUNTRY = "USA"
GROUP BY JOB_CODE;

SELECT HEAD_DEPT SUM(BUDGET)
FROM DEPARTMENT
GROUP BY HEAD_DEPT;

SELECT HEAD_DEPT SUM(BUDGET)
FROM DEPARTMENT
GROUP BY HEAD_DEPT
HAVING SUM(BUDGET) > 2000000;

SELECT LAST_NAME FIRST_NAME PHONE_EXT
FROM EMPLOYEE
ORDER BY LAST_NAME;

SELECT LAST_NAME FIRST_NAME PHONE_EXT
FROM EMPLOYEE
ORDER BY LAST_NAME DESC;

SELECT LAST_NAME FIRST_NAME PHONE_EXT
FROM EMPLOYEE
ORDER BY LAST_NAME DESC FIRST_NAME;

SELECT <columns>
FROM <left_table> [INNER] JOIN <right_table>
[ON <searchcondition>]
[WHERE <searchcondition>];

SELECT D.DEPARTMENT D.MNGR_NO E.SALARY
FROM DEPARTMENT D JOIN EMPLOYEE E
ON D.MNGR_NO = E.EMP_NO
AND E.SALARY*2 > (SELECT SUM(S.SALARY) FROM EMPLOYEE S
WHERE D.DEPT_NO = S.DEPT_NO)
ORDER BY D.DEPARTMENT;

SELECT col [ col ...] | *
FROM <left_table> {LEFT | RIGHT | aaaa} [OUTER] JOIN
<right_table> [ON <searchcondition>]
[WHERE <searchcondition>];

SELECT PROJ_ID aaaa_NAME
FROM EMPLOYEE LEFT OUTER JOIN EMPLOYEE_PROJECT
ON EMPLOYEE.EMP_NO = EMPLOYEE_PROJECT.EMP_NO;

FROM EMPLOYEE_PROJECT LEFT OUTER JOIN EMPLOYEE

CAST (<value> | aaaa AS datatype)

SELECT DEPARTMENT LAST_NAME || " is the manager"
FROM DEPARTMENT EMPLOYEE
WHERE MNGR_NO = EMP_NO;

CREATE DOMAIN PROJNO
AS CHAR(5)
CHECK (VALUE = UPPER (VALUE));

CREATE GENERATOR EMP_NO_GEN;
SET GENERATOR EMP_NO_GEN TO 145;

INSERT INTO EMPLOYEE (FIRST_NAME LAST_NAME DEPT_NO JOB_CODE
JOB_GRADE JOB_COUNTRY HIRE_DATE SALARY PHONE_EXT) VALUES
("Reed" "Richards" "671" "Eng" 5 "USA" "07/27/95" 34000 "444");

SHOW TRIGGER SAVE_SALARY_CHANGE
Triggers on Table EMPLOYEE:
SAVE_SALARY_CHANGE Sequence: 0 Type: AFTER UPDATE Active
AS
BEGIN
IF (OLD.SALARY <> NEW.SALARY) THEN
INSERT INTO SALARY_HISTORY
(EMP_NO CHANGE_DATE UPDATER_ID OLD_SALARY PERCENT_CHANGE)
VALUES (OLD.EMP_NO
"NOW"
USER
OLD.SALARY
(NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY);
END

CREATE TRIGGER POST_NEW_ORDER FOR SALES
AFTER INSERT AS
BEGIN
POST_EVENT "new_order";
END !!

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
FOR SELECT PROJ_ID
FROM EMPLOYEE_PROJECT
WHERE EMP_NO = :EMP_NO
INTO :PROJ_ID
DO
SUSPEND;
END ^

SELECT * FROM GET_EMP_PROJ(71);

CREATE EXCEPTION UNKNOWN_EMP_ID
"Invalid employee number or project id.";

CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT PROJ_ID CHAR(5))
AS
BEGIN
BEGIN
INSERT INTO EMPLOYEE_PROJECT (EMP_NO PROJ_ID)
VALUES (:emp_no roj_id);
WHEN SQLCODE -530 DO
EXCEPTION UNKNOWN_EMP_ID;
END
SUSPEND;
END ^

EXECUTE PROCEDURE ADD_EMP_PROJ(20 "DGPII");

EXECUTE PROCEDURE ADD_EMP_PROJ(999 "DGPII");

SHOW PROCEDURE DEPT_BUDGET;
Procedure text:
================================================== ====================
DECLARE VARIABLE sumb DECIMAL(12 2);
DECLARE VARIABLE rdno CHAR(3);
DECLARE VARIABLE cnt INTEGER;
BEGIN
tot = 0;
SELECT BUDGET FROM DEPARTMENT WHERE DEPT_NO = :dno INTO :tot;
SELECT COUNT(BUDGET) FROM DEPARTMENT WHERE HEAD_DEPT = :dno INTO :cnt;
IF (cnt = 0) THEN
SUSPEND;
FOR SELECT DEPT_NO
FROM DEPARTMENT
WHERE HEAD_DEPT = :dno
INTO :rdno
DO
BEGIN
EXECUTE PROCEDURE DEPT_BUDGET :rdno RETURNING_VALUES :sumb;
tot = tot + sumb;
END
SUSPEND;
END
================================================== ====================
Parameters:
DNO INPUT CHAR(3)
TOT OUTPUT NUMERIC(15 2)

CHECK (SALARY >= (SELECT MIN_SALARY FROM JOB WHERE
JOB.JOB_CODE = EMPLOYEE.JOB_CODE AND
JOB.JOB_GRADE = EMPLOYEE.JOB_GRADE AND
JOB.JOB_COUNTRY = EMPLOYEE.JOB_COUNTRY)
AND
SALARY <= (SELECT MAX_SALARY FROM JOB WHERE
JOB.JOB_CODE = EMPLOYEE.JOB_CODE AND
JOB.JOB_GRADE = EMPLOYEE.JOB_GRADE AND
JOB.JOB_COUNTRY = EMPLOYEE.JOB_COUNTRY))

CHECK (order_status in ("new" "open" "shipped" "waiting"))
CHECK (ship_date >= order_date OR ship_date IS aaaa)
CHECK (date_needed > order_date OR date_needed IS aaaa)
CHECK (paid in ("y" "n"))
CHECK (qty_ordered >= 1)
CHECK (total_value >= 0)
CHECK (discount >= 0 AND discount <= 1)
CHECK (NOT (order_status = "shipped" AND ship_date IS aaaa))
CHECK (NOT (order_status = "shipped" AND
EXISTS (SELECT on_hold FROM customer
WHERE customer.cust_no = sales.cust_no
AND customer.on_hold = "*")))