"A.B"

"A"."B"

-- the view name is stored in the
-- system catalogs as ANIDENTIFIER
CREATE VIEW AnIdentifier (RECEIVED) AS VALUES 1
-- the view name is stored in the system
-- catalogs with case intact
CREATE VIEW "ACaseSensitiveIdentifier" (RECEIVED) AS VALUES 1

[ { 

table-Name

 | 

correlation-Name

 } . ] 

SQL92Identifier

-- C.Country is a column-Name qualified with a
-- 
correlation-Name
.
SELECT C.Country
FROM APP.Countries C

SQL92Identifier

-- C is a correlation-Name
SELECT C.NAME
FROM SAMP.STAFF C

SQL92Identifier

-- FlightBooks is a new-table-Name that does not include a schema-Name
RENAME TABLE FLIGHTAVAILABILITY TO FLIGHTAVAILABLE

SQL92Identifier

-- SAMP.EMPLOYEE is a table-Name qualified by a schemaName
SELECT COUNT(*) FROM SAMP.EMPLOYEE
-- You must qualify system catalog names with their schema, SYS
SELECT COUNT(*) FROM SYS.SysColumns

SQL92Identifier

-- country is a Simple-column-Name
CREATE TABLE CONTINENT (COUNTRY VARCHAR(26) NOT NULL PRIMARY KEY,
COUNTRY_ISO_CODE CHAR(2), REGION VARCHAR(26))

[ 

schemaName
. ] 

SQL92Identifier

[ 

schemaName
. ] 

SQL92Identifier

-- SAMP.PROJECT is a table-Name that includes a schemaName
SELECT COUNT(*) FROM SAMP.PROJECT

[ 

schemaName
. ] 

SQL92Identifier

-- This is a View qualified by a schema-Name
SELECT COUNT(*) FROM SAMP.EMP_RESUME

[ 

schemaName

 . ] 

SQL92Identifier

DROP INDEX APP.ORIGINDEX;
-- OrigIndex is an index-Name without a schema-Name 
CREATE INDEX ORIGINDEX ON FLIGHTS (ORIG_AIRPORT)

SQL92Identifier

-- country_fk2 is a constraint name
CREATE TABLE DETAILED_MAPS (COUNTRY_ISO_CODE CHAR(2)
CONSTRAINT country_fk2 REFERENCES COUNTRIES)

SQL92Identifier

stmt.executeUpdate("UPDATE SAMP.STAFF SET COMM = " +
"COMM + 20 " + "WHERE CURRENT OF " + ResultSet.getCursorName());

[ 

schemaName

 . ] 

SQL92Identifier

DROP TRIGGER TRIG1

SQL92Identifier

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.fullAccessUsers', 'Amber,FRED')

ij> CREATE TABLE mytable (mycol INT);
0 rows inserted/updated/deleted
ij> INSERT INTO mytable VALUES (1), (2), (3);
3 rows inserted/updated/deleted
-- this example uses the ij command prepare,
-- which prepares a statement
ij> prepare p1 AS 'INSERT INTO MyTable VALUES (4)';
-- p1 depends on mytable;
ij> execute p1;
1 row inserted/updated/deleted
-- Derby  executes it without recompiling
ij> CREATE INDEX i1 ON mytable(mycol);
0 rows inserted/updated/deleted
-- p1 is temporarily invalidated because of new index
ij> execute p1;
1 row inserted/updated/deleted
-- Derby  automatically recompiles p1 and executes it
ij> DROP TABLE mytable;
0 rows inserted/updated/deleted
-- Derby  permits you to drop table
-- because result set of p1 is closed
-- however, the statement p1 is temporarily invalidated
ij> CREATE TABLE mytable (mycol INT);
0 rows inserted/updated/deleted
ij> INSERT INTO mytable VALUES (1), (2), (3);
3 rows inserted/updated/deleted
ij> execute p1;
1 row inserted/updated/deleted
-- Because p1 is invalid, Derby  tries to recompile it
-- before executing.
-- It is successful and executes.
ij> DROP TABLE mytable;
0 rows inserted/updated/deleted
-- statement p1 is now invalid,
-- and this time the attempt to recompile it
-- upon execution will fail
ij> execute p1;
ERROR 42X05: Table 'MYTABLE' does not exist.

ALTER TABLE 

table-Name


{
    ADD COLUMN 

column-definition

 |
    ADD 

CONSTRAINT clause

 |
    DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE 
	 constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
    ALTER 

column-alteration

 |
    LOCKSIZE { ROW | TABLE }
}

Simple-column-Name


DataType
[ 

Column-level-constraint

 ]*
[ [ WITH ] DEFAULT {ConstantExpression | NULL } ]

column-Name SET DATA TYPE VARCHAR(integer) |
column-name SET INCREMENT BY integer-constant

-- 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 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;

CALL 
procedure-Name
 ( [ ? [, ?]* ] )

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';
CALL SALES.TOTAL_REVENUE(?,?,?);

-- column-level primary key constraint named OUT_TRAY_PK:
CREATE TABLE SAMP.OUT_TRAY
	(
	SENT TIMESTAMP,
	DESTINATION CHAR(8),
	SUBJECT CHAR(64) NOT NULL CONSTRAINT OUT_TRAY_PK PRIMARY KEY,
	NOTE_TEXT VARCHAR(3000) 
   );

-- the table-level primary key definition allows you to
-- include two columns in the primary key definition:
CREATE TABLE SAMP.SCHED 
	(
	CLASS_CODE CHAR(7) NOT NULL, 
	DAY SMALLINT NOT NULL, 
	STARTING TIME, 
	ENDING TIME,
	PRIMARY KEY (CLASS_CODE, DAY)
	);

-- Use a column-level constraint for an arithmetic check
-- Use a table-level constraint
-- to make sure that a employee's taxes does not 
-- exceed the bonus
CREATE TABLE SAMP.EMP 
	(
	EMPNO CHAR(6) NOT NULL CONSTRAINT EMP_PK PRIMARY KEY,
	FIRSTNME CHAR(12) NOT NULL,
	MIDINIT vARCHAR(12) NOT NULL,
	LASTNAME VARCHAR(15) NOT NULL,
	SALARY DECIMAL(9,2) CONSTRAINT SAL_CK CHECK (SALARY >= 10000),
	BONUS DECIMAL(9,2), 
	TAX DECIMAL(9,2),
	CONSTRAINT BONUS_CK CHECK (BONUS > TAX)
	);

-- use a check constraint to allow only appropriate
-- abbreviations for the meals
CREATE TABLE FLIGHTS
	(
	FLIGHT_ID CHAR(6) NOT NULL ,
	SEGMENT_NUMBER INTEGER NOT NULL ,
	ORIG_AIRPORT CHAR(3),
	DEPART_TIME TIME,
	DEST_AIRPORT CHAR(3),
	ARRIVE_TIME TIME,
	MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT 
	CHECK (MEAL IN ('B', 'L', 'D', 'S')),
	PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
	);

CREATE TABLE METROPOLITAN
	(
	HOTEL_ID INT NOT NULL CONSTRAINT HOTELS_PK PRIMARY KEY,
	HOTEL_NAME VARCHAR(40) NOT NULL,
	CITY_ID INT CONSTRAINT METRO_FK REFERENCES CITIES
	);

-- create a table with a table-level primary key constraint
-- and a table-level foreign key constraint 
CREATE TABLE FLTAVAIL
	(
	FLIGHT_ID CHAR(6) NOT NULL, 
	SEGMENT_NUMBER INT NOT NULL, 
	FLIGHT_DATE DATE NOT NULL, 
	ECONOMY_SEATS_TAKEN INT,
	BUSINESS_SEATS_TAKEN INT,
	FIRSTCLASS_SEATS_TAKEN INT, 
	CONSTRAINT FLTAVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER), 
	CONSTRAINT FLTS_FK
	FOREIGN KEY (FLIGHT_ID, SEGMENT_NUMBER)
	REFERENCES Flights (FLIGHT_ID, SEGMENT_NUMBER)
	);
-- add a unique constraint to a column
ALTER TABLE SAMP.PROJECT 
ADD CONSTRAINT P_UC UNIQUE (PROJNAME);

-- create a table whose city_id column references the
-- primary key in the Cities table
-- using a column-level foreign key constraint  
CREATE TABLE CONDOS
	(
	CONDO_ID INT NOT NULL CONSTRAINT hotels_PK PRIMARY KEY,
	CONDO_NAME VARCHAR(40) NOT NULL,
	CITY_ID INT CONSTRAINT city_foreign_key
	REFERENCES Cities ON DELETE CASCADE ON UPDATE RESTRICT
	);

DECLARE GLOBAL TEMPORARY TABLE 

table-Name


    { column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ]  
NOT LOGGED [ON ROLLBACK DELETE ROWS]
 

set schema myapp;

create table t1(c11 int, c12 date);

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.

insert into SESSION.t1 values (1); 
-- SESSION qualification is mandatory here if you want to use 
-- the temporary table, because the current schema is "myapp."

select * from t1; 
-- This select statement is referencing the "myapp.t1" physical 
-- table since the table was not qualified by SESSION.

{
    DELETE FROM table-Name
        [

WHERE clause

] |
    DELETE FROM table-Name 
WHERE CURRENT OF

}

DELETE FROM SAMP.IN_TRAY;

stmt.executeUpdate("DELETE FROM SAMP.IN_TRAY WHERE CURRENT OF " +
	resultSet.getCursorName())

FOR
{
    READ ONLY | FETCH ONLY |
    UPDATE [ OF 

Simple-column-Name

 [ , 

Simple-column-Name

]* ]
}

SELECT RECEIVED, SOURCE, SUBJECT, NOTE_TEXT FROM SAMP.IN_TRAY FOR UPDATE;

FROM TableExpression [ , TableExpression ] *

SELECT Cities.city_id
FROM Cities
WHERE city_id < 5
-- other types of TableExpressions
SELECT TABLENAME, ISINDEX 
FROM SYS.SYSTABLES T, SYS.SYSCONGLOMERATES C
WHERE T.TABLEID = C.TABLEID
ORDER BY TABLENAME, ISINDEX;
-- force the join order
SELECT *
FROM Flights, FlightAvailability
WHERE FlightAvailability.flight_id = Flights.flight_id
AND FlightAvailability.segment_number = Flights.segment_number
AND Flights.flight_id < 'AA1115'
-- a TableExpression can be a joinOperation. Therefore
-- you can have multiple join operations in a FROM clause
SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME, FLIGHTS.DEST_AIRPORT
FROM COUNTRIES LEFT OUTER JOIN CITIES
ON COUNTRIES.COUNTRY_ISO_CODE = CITIES.COUNTRY_ISO_CODE
LEFT OUTER JOIN FLIGHTS
ON Cities.AIRPORT = FLIGHTS.DEST_AIRPORT;

GROUP BY 

column-Name

 [ , 

column-Name

 ] *

-- find the average flying_times of flights grouped by
-- airport
SELECT AVG (flying_time), orig_airport
FROM Flights
GROUP BY orig_airport

SELECT MAX(city), region
FROM Cities, Countries
WHERE Cities.country_ISO_code = Countries.country_ISO_code
GROUP BY region
-- group by an a smallint
SELECT ID, AVG(SALARY)
FROM SAMP.STAFF
GROUP BY ID
-- Get the AVGSALARY and EMPCOUNT columns, and the DEPTNO column using the AS clause
-- And group by the WORKDEPT column using the correlation name OTHERS
SELECT OTHERS.WORKDEPT AS DEPTNO,
AVG(OTHERS.SALARY) AS AVGSALARY,
COUNT(*) AS EMPCOUNT
FROM SAMP.EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT;

HAVING searchCondition

-- SELECT COUNT(*)
-- FROM SAMP.STAFF
-- GROUP BY ID
-- HAVING SALARY > 15000;

-- Find the total number of economy seats taken on a flight,
-- grouped by airline,
-- only when the group has at least 2 records. 
SELECT SUM(ECONOMY_SEATS_TAKEN), AIRLINE_FULL
FROM FLIGHTAVAILABILITY, AIRLINES
WHERE SUBSTR(FLIGHTAVAILABILITY.FLIGHT_ID, 1, 2) = AIRLINE
GROUP BY AIRLINE_FULL
HAVING COUNT(*) > 1

TableExpression

 [ INNER ] JOIN 

TableExpression

 { ON booleanExpression }

SELECT *
FROM SAMP.EMPLOYEE INNER JOIN SAMP.STAFF
ON EMPLOYEE.SALARY < STAFF.SALARY;

-- Join the EMP_ACT and EMPLOYEE tables
-- select all the columns from the EMP_ACT table and 
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result
SELECT SAMP.EMP_ACT.*, LASTNAME
     FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
     ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO;
-- Join the EMPLOYEE and DEPARTMENT tables, 
-- select the employee number (EMPNO), 
-- employee surname (LASTNAME), 
-- department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the
-- DEPARTMENT table) 
-- and department name (DEPTNAME) 
-- of all employees who were born (BIRTHDATE) earlier than 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 
     FROM SAMP.EMPLOYEE JOIN SAMP.DEPARTMENT 
     ON WORKDEPT = DEPTNO 
     AND YEAR(BIRTHDATE) < 1930;

-- Another example of "generating" new data values, 
-- using a query which selects from a VALUES clause (which is an 
-- alternate form of a fullselect). 
-- This query shows how a table can be derived called "X"
-- having 2 columns "R1" and "R2" and 1 row of data
SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS VALUESTABLE1(C1, C2)
JOIN (VALUES (3, 2), (1, 2),
(0, 3)) AS VALUESTABLE2(c1, c2)
ON VALUESTABLE1.c1 = VALUESTABLE2.c1;

This results in:
  C1         |C2         |C1         |2
-----------------------------------------------
3          |4          |3          |2
1          |5          |1          |2

-- List every department with the employee number and 
-- last name of the manager

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
	FROM DEPARTMENT INNER JOIN EMPLOYEE
	ON MGRNO = EMPNO;

-- List every employee number and last name 
-- with the employee number and last name of their manager
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME	
	FROM EMPLOYEE E INNER JOIN	
	DEPARTMENT INNER JOIN EMPLOYEE M 
        ON MGRNO = M.EMPNO
        ON E.WORKDEPT = DEPTNO;

INSERT INTO table-Name
    [ (

Simple-column-Name

 [ , 

Simple-column-Name

]* ) ]
	  Query

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

-- Insert a new department into the DEPARTMENT table,
-- but do not assign a manager to the new department
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
	VALUES ('E31', 'ARCHITECTURE', 'E01');
-- Insert two new departments using one statement 
-- into the DEPARTMENT table as in the previous example, 
-- but do not assign a manager to the new department.
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
	VALUES ('B11', 'PURCHASING', 'B01'),
	('E41', 'DATABASE ADMINISTRATION', 'E01');
--  Create a temporary table MA_EMP_ACT with the 
-- same columns as the EMP_ACT table. 
-- Load MA_EMP_ACT with the rows from the EMP_ACT
-- table with a project number (PROJNO) 
-- starting with the letters 'MA'.
CREATE TABLE MA_EMP_ACT 
    (
	EMPNO CHAR(6)  NOT NULL, 
	PROJNO CHAR(6)  NOT NULL, 
	ACTNO SMALLINT  NOT NULL, 
	EMPTIME DEC(5,2), 
	EMSTDATE DATE, 
	EMENDATE  DATE
    );

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);

JOIN Operation

TableExpression

 LEFT [ OUTER ] JOIN 

TableExpression


{
    ON booleanExpression 
    }

--match cities to countries in Asia

SELECT CITIES.COUNTRY, CITIES.CITY_NAME, REGION 
FROM Countries 
LEFT OUTER JOIN Cities
ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
WHERE REGION = 'Asia';

-- use the synonymous syntax, LEFT JOIN, to achieve exactly 
-- the same results as in the example above

SELECT  COUNTRIES.COUNTRY, CITIES.CITY_NAME,REGION 
FROM COUNTRIES 
LEFT JOIN CITIES 
ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
WHERE REGION = 'Asia';

-- Join the EMPLOYEE and DEPARTMENT tables, 
-- select the employee number (EMPNO), 
-- employee surname (LASTNAME), 
-- department number (WORKDEPT in the EMPLOYEE table
-- and DEPTNO in the DEPARTMENT table) 
-- and department name (DEPTNAME) 
-- of all employees who were born (BIRTHDATE) earlier than 1930

SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 
   FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT 
   ON WORKDEPT = DEPTNO 
   AND YEAR(BIRTHDATE) < 1930;

-- List every department with the employee number and 
-- last name of the manager,
-- including departments without a manager

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
      FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
	    ON MGRNO = EMPNO;

LOCK TABLE 

table-Name

 IN { SHARE | EXCLUSIVE } MODE

-- lock the entire table in share mode to avoid
-- a large number of row locks
LOCK TABLE Flights IN SHARE MODE;
SELECT *
FROM Flights
WHERE orig_airport > 'OOO';
-- lock the entire table in exclusive mode
-- for a transaction that will update many rows,
-- but where no single statement will update enough rows
-- acquire an exclusive table lock on the table.
-- In a row-level locking system, that transaction would
-- require a large number of locks or might deadlock.
LOCK TABLE HotelAvailability IN EXCLUSIVE MODE;
UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-10');

UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-11');

UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-12');

UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-12');
-- if a transaction needs to look at a table before
-- updating it, acquire an exclusive lock before
-- selecting to avoid deadlocks
LOCK TABLE People IN EXCLUSIVE MODE;
SELECT MAX(person_id) + 1 FROM PEOPLE;
-- INSERT INTO PEOPLE . . .

ORDER BY { 

column-Name

 | ColumnPosition }
    [ ASC | DESC ]
    [ , 

column-Name

 | ColumnPosition
        [ ASC | DESC ] ] * 

-- order by the correlation name NATION
-- order by the correlation name NATION
SELECT CITY_NAME, COUNTRY AS NATION 
FROM CITIES 
ORDER BY NATION;

{
    ( Query ) |
    Query INTERSECT [ ALL | DISTINCT ] Query |
    Query EXCEPT [ ALL | DISTINCT ] Query |
    Query UNION [ ALL | DISTINCT ] Query |


SelectExpression

 | 

VALUES Expression


}

-- a Select expression 
SELECT *
FROM ORG;

-- a subquery 
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS;

-- a subquery
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS (CLASS_CODE);

-- a UNION
-- returns all rows from columns DEPTNUMB and MANAGER 
-- in table ORG
-- and (1,2) and (3,4)
-- DEPTNUMB and MANAGER are smallint columns
SELECT DEPTNUMB, MANAGER 
FROM ORG
UNION ALL
VALUES (1,2), (3,4);

-- a values expression
VALUES (1,2,3);
-- List the employee numbers (EMPNO) of all employees in the EMPLOYEE table 
-- whose department number (WORKDEPT) either begins with 'E' or 
-- who are assigned to projects in the EMP_ACT table 
-- whose project number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'
SELECT EMPNO
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112');
-- Make the same query as in the previous example
-- and "tag" the rows from the EMPLOYEE table with 'emp' and 
-- the rows from the EMP_ACT table with 'emp_act'. 
-- Unlike the result from the previous example, 
-- this query may return the same EMPNO more than once, 
-- identifying which table it came from by the associated "tag"
SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act' FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112');
-- Make the same query as in the previous example, 
-- only use UNION ALL so that no duplicate rows are eliminated
SELECT EMPNO
      FROM EMPLOYEE
      WHERE WORKDEPT LIKE 'E%'
  UNION ALL
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112');
-- Make the same query as in the previous example, 
-- only include an additional two employees currently not in any table and 
-- tag these rows as "new"
 SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act'
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
  UNION
     VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new');

TableExpression

 RIGHT [ OUTER ] JOIN 

TableExpression


{
    ON booleanExpression 
    }

-- get all countries and corresponding cities, including
-- countries without any cities

SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME 
FROM CITIES 
RIGHT OUTER JOIN COUNTRIES 
ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;

-- get all countries in Africa and corresponding cities, including
-- countries without any cities

SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
FROM CITIES 
RIGHT OUTER JOIN COUNTRIES 
ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
WHERE Countries.region = 'Africa';

-- use the synonymous syntax, RIGHT JOIN, to achieve exactly
-- the same results as in the example above

SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
FROM CITIES 
RIGHT JOIN COUNTRIES 
ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
WHERE Countries.region = 'Africa';

-- a TableExpression can be a joinOperation. Therefore
-- you can have multiple join operations in a FROM clause
-- List every employee number and last name 
-- with the employee number and last name of their manager

SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME	
	FROM EMPLOYEE E RIGHT OUTER JOIN	
	DEPARTMENT RIGHT OUTER JOIN EMPLOYEE M 
        ON MGRNO = M.EMPNO
        ON E.WORKDEPT = DEPTNO;

(

Query

)

-- avg always returns a single value, so the subquery is
-- a ScalarSubquery
SELECT NAME, COMM
  FROM STAFF
  WHERE EXISTS
    (SELECT AVG(BONUS + 800)
       FROM EMPLOYEE
       WHERE COMM < 5000
       AND EMPLOYEE.LASTNAME = UPPER(STAFF.NAME)
);
-- Introduce a way of "generating" new data values,
-- using a query which selects from a VALUES clause (which is an 
alternate form of a fullselect). 
-- This query shows how a table can be derived called "X" having 
2 columns "R1" and "R2" and 1 row of data.
SELECT R1,R2 
	FROM (VALUES('GROUP 1','GROUP 2')) AS X(R1,R2);

SELECT [ DISTINCT | ALL ] SelectItem [ , SelectItem ]*


FROM clause


[ 

WHERE clause

]
[ 

GROUP BY clause

 ]
[ 

HAVING clause

 ]

{
    * |
    { 

table-Name

 | 

correlation-Name

 } .* |
    Expression [AS 

Simple-column-Name

]
}

-- List head count of each department, 
-- the department number (WORKDEPT), and the average departmental salary (SALARY) 
-- for all departments in the EMPLOYEE table. 
-- Arrange the result table in ascending order by average departmental salary.
SELECT WORKDEPT, AVG(SALARY)
     FROM EMPLOYEE
     GROUP BY WORKDEPT
     ORDER BY 1;

VALUES CURRENT_TIMESTAMP

-- this example shows SELECT-FROM-WHERE
-- with an ORDER BY clause
-- and correlation-Names for the tables
SELECT CONSTRAINTNAME, COLUMNNAME 
FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks 
WHERE t.TABLENAME = 'FLIGHTS' AND t.TABLEID = col.
REFERENCEID AND t.TABLEID = cons.TABLEID 
AND cons.CONSTRAINTID = checks.CONSTRAINTID 
ORDER BY CONSTRAINTNAME;
-- This example shows the use of the DISTINCT clause
SELECT DISTINCT ACTNO
FROM EMP_ACT;
-- This example shows how to rename an expression 
-- Using the EMPLOYEE table, list the department number (WORKDEPT) and 
-- maximum departmental salary (SALARY) renamed as BOSS 
-- for all departments whose maximum salary is less than the 
-- average salary in all other departments.
SELECT WORKDEPT AS DPT, MAX(SALARY) AS BOSS 
	FROM EMPLOYEE EMP_COR 
	GROUP BY WORKDEPT 
	HAVING MAX(SALARY) < (SELECT AVG(SALARY)
				FROM EMPLOYEE
				WHERE NOT WORKDEPT = EMP_COR.WORKDEPT) 
	ORDER BY BOSS;

Query


[

ORDER BY clause

]
[

FOR UPDATE clause

]
WITH {RR|RS|CS|UR}

-- lists the names of the expression SAL+BONUS+COMM as TOTAL_PAY and
-- orders by the new name TOTAL_PAY
 SELECT FIRSTNME, SALARY+BONUS+COMM AS TOTAL_PAY
     FROM EMPLOYEE
     ORDER BY TOTAL_PAY;
-- creating an updatable cursor with a FOR UPDATE clause 
-- to update the start date (PRSTDATE) and the end date (PRENDATE)
-- columns in the PROJECT table
SELECT PROJNO, PRSTDATE, PRENDATE
      FROM PROJECT
      FOR UPDATE OF PRSTDATE, PRENDATE;
-- set the isolation level to RR for this statement only 
SELECT * 
FROM Flights 
WHERE flight_id BETWEEN 'AA1111' AND 'AA1112' 
WITH RR;

{
TableOrViewExpression | JOIN operation 
}

-- SELECT from a Join expression 
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
 FROM EMPLOYEE E LEFT OUTER JOIN
   	DEPARTMENT INNER JOIN EMPLOYEE M 
  ON MGRNO = M.EMPNO
  ON E.WORKDEPT = DEPTNO

(

Query

)

-- a subquery used as a TableExpression in a FROM clause
SELECT VirtualFlightTable.flight_ID
FROM
    (SELECT flight_ID, orig_airport, dest_airport
    FROM Flights
    WHERE (orig_airport = 'SFO' OR dest_airport = 'SCL') )
AS VirtualFlightTable
-- a subquery (values expression) used as a TableExpression
-- in a FROM clause
SELECT mycol1
FROM
    (VALUES (1, 2), (3, 4))
AS mytable (mycol1, mycol2)
-- a subquery used with EXISTS
SELECT *
FROM Flights
WHERE EXISTS
    (SELECT * FROM Flights WHERE dest_airport = 'SFO'
    AND orig_airport = 'GRU')
-- a subquery used with IN
SELECT flight_id, segment_number
FROM Flights
WHERE flight_id IN
    (SELECT flight_ID
    FROM Flights WHERE orig_airport = 'SFO'
    OR dest_airport = 'SCL')
-- a subquery used with a quantified comparison
SELECT NAME, COMM 
FROM STAFF 
WHERE COMM > 
(SELECT AVG(BONUS + 800)
     FROM EMPLOYEE
     WHERE COMM < 5000);

{
    UPDATE 

table-Name


        SET 

column-Name

 = 

Value


        [ , 

column-Name

 = 

Value

} ]*
        [

WHERE clause

] |
    UPDATE 

table-Name


        SET 

column-Name

 = 

Value


        [ , 

column-Name

 = 

Value

 ]*
        

WHERE CURRENT OF


}

-- All the employees except the manager of department (WORKDEPT) 'E21' have 
been temporarily reassigned. 
-- Indicate this by changing their job (JOB) to NULL and their pay
-- (SALARY, BONUS, COMM) values to zero in the EMPLOYEE table. 
UPDATE EMPLOYEE
  SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
  WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

-- PROMOTE the job (JOB) of certain employees to MANAGER
UPDATE EMPLOYEE
	SET JOB = 'MANAGER'
	WHERE CURRENT OF CURS1;
-- Increase the project staffing (PRSTAFF) by 1.5 for all projects
stmt.executeUpdate("UPDATE PROJECT SET PRSTAFF = "
"PRSTAFF + 1.5" +
"WHERE CURRENT OF" + ResultSet.getCursorName());

-- Change the job (JOB) of employee number (EMPNO) '000290' in the EMPLOYEE table 
-- to its DEFAULT value which is NULL
UPDATE EMPLOYEE
  SET JOB = DEFAULT
  WHERE EMPNO = '000290';

{
    VALUES ( 

Value

 {, 

Value

 }* )
        [ , ( 

Value

 {, 

Value

 }* ) ]* |
    VALUES 

Value

 [ , 

Value

 ]* |
 }

-- 3 rows of 1 column
VALUES (1),(2),(3);
-- 3 rows of 1 column
VALUES 1, 2, 3; 
-- 1 row of 3 columns
VALUES (1, 2, 3);
-- 3 rows of 2 columns
VALUES (1,21),(2,22),(3,23);
-- constructing a derived table
VALUES ('orange', 'orange'), ('apple', 'red'),
('banana', 'yellow')
-- Insert two new departments using one statement into the DEPARTMENT table, 
-- but do not assign a manager to the new department.
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
  VALUES ('B11', 'PURCHASING', 'B01'),
    ('E41', 'DATABASE ADMINISTRATION', 'E01')
-- insert a row with a DEFAULT value for the MAJPROJ column
INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE, MAJPROJ) 
VALUES ('PL2101', 'ENSURE COMPAT PLAN', 'B01', '000020', CURRENT_DATE, DEFAULT);

-- using a built-in function
VALUES CURRENT_DATE
-- getting the value of an arbitrary expression
VALUES (3*29, 26.0E0/3)
-- getting a value returned by a built-in function
values char(1)

WHERE 

Boolean expression

-- find the flights where no business-class seats have
-- been booked
SELECT *
FROM FlightAvailability
WHERE business_seats_taken IS NULL
OR business_seats_taken = 0
-- Join the EMP_ACT and EMPLOYEE tables
-- select all the columns from the EMP_ACT table and 
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result.
SELECT SAMP.EMP_ACT.*, LASTNAME
  FROM SAMP.EMP_ACT, SAMP.EMPLOYEE
  WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO;
-- Determine the employee number and salary of sales representatives 
-- along with the average salary and head count of their departments.
-- This query must first create a new-column-name specified in the AS clause 
-- which is outside the fullselect (DINFO) 
-- in order to get the AVGSALARY and EMPCOUNT columns, 
-- as well as the DEPTNO column that is used in the WHERE clause
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT
 FROM EMPLOYEE THIS_EMP,
   (SELECT OTHERS.WORKDEPT AS DEPTNO,
           AVG(OTHERS.SALARY) AS AVGSALARY,
           COUNT(*) AS EMPCOUNT
    FROM EMPLOYEE OTHERS
    GROUP BY OTHERS.WORKDEPT
   )AS DINFO
 WHERE THIS_EMP.JOB = 'SALESREP'
	   AND THIS_EMP.WORKDEPT = DINFO.DEPTNO;

WHERE CURRENT OF 

cursor-Name

Statement s = conn.createStatement();
s.setCursorName("AirlinesResults");
ResultSet rs = conn.executeQuery(
    "SELECT Airline, basic_rate " +
    "FROM Airlines FOR UPDATE OF basic_rate");
Statement s2 = conn.createStatement();
s2.executeUpdate("UPDATE Airlines SET basic_rate = basic_rate " +
    "+ .25 WHERE CURRENT OF AirlinesResults");

ABS(NumericExpression)

-- returns 3
VALUES ABS(-3)

AVG ( [ DISTINCT | ALL ] Expression )

SELECT AVG (DISTINCT flying_time), SUM (DISTINCT miles)
FROM Flights

SELECT AVG(c1)
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)

SELECT AVG(CAST (c1 AS DOUBLE PRECISION))
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)

BIGINT (CharacterExpression | NumericExpression ) 

CAST ( [ Expression | NULL | ? ]
    AS Datatype)

SELECT CAST (miles AS INT)
FROM Flights
-- convert timestamps to text
INSERT INTO mytable (text_column)
VALUES (CAST (CURRENT_TIMESTAMP AS VARCHAR(100)))
-- you must cast NULL as a data type to use it
SELECT airline
FROM Airlines
UNION ALL
VALUES (CAST (NULL AS CHAR(2)))
-- cast a double as a decimal
SELECT CAST (FLYING_TIME AS DECIMAL(5,2))
FROM FLIGHTS
-- cast a SMALLINT to a BIGINT
VALUES CAST (CAST (12 as SMALLINT) as BIGINT)

CHAR (CharacterExpression [, integer] ) 

CHAR (IntegerExpression ) 

CHAR (DatetimeExpression ) 

CHAR (DecimalExpression ) 

CHAR (FloatingPointExpression ) 

LENGTH ( { CharacterExpression | BitExpression } )

-- returns 20
VALUES LENGTH('supercalifragilistic')
-- returns 1
VALUES LENGTH(X'FF')
-- returns 4
VALUES LENGTH(1234567890)

{
    { CharacterExpression || CharacterExpression } |
    { BitExpression || BitExpression }
}

--returns 'supercalifragilisticexbealidocious(sp?)'
VALUES 'supercalifragilistic' || 'exbealidocious' || '(sp?)'
-- returns NULL
VALUES CAST (null AS VARCHAR(7))|| 'AString'
-- returns '130asdf'
VALUES '130' || 'asdf'

CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END

COUNT ( [ DISTINCT | ALL ] Expression ) 

-- query not allowed
SELECT COUNT (DISTINCT flying_time), SUM (DISTINCT miles)
FROM Flights

-- Count the number of countries in each region,
-- show only regions that have at least 2
SELECT COUNT (country), region
FROM Countries
GROUP BY region
HAVING COUNT (country) > 1

COUNT(*)

-- Count the number of rows in the Flights table
SELECT COUNT(*)
FROM Flights

CURRENT_DATE

CURRENT DATE

-- find available future flights:
SELECT * FROM Flightavailability where flight_date > CURRENT_DATE;

CURRENT ISOLATION

VALUES CURRENT ISOLATION

CURRENT SCHEMA

-- or, alternately:

CURRENT SQLID

-- Set the name column default to the current schema:
CREATE TABLE mytable (id int, name VARCHAR(128) DEFAULT CURRENT SQLID)
-- Inserts default value of current schema value into the table:
INSERT INTO mytable(id) VALUES (1)
-- Returns the rows with the same name as the current schema:
SELECT name FROM mytable WHERE name = CURRENT SCHEMA

CURRENT_TIME

CURRENT TIME

VALUES CURRENT_TIME
-- or, alternately:

VALUES CURRENT TIME

CURRENT_TIMESTAMP

CURRENT TIMESTAMP

VALUES CURRENT_TIMESTAMP
-- or, alternately:

VALUES CURRENT TIMESTAMP

CURRENT_USER

VALUES CURRENT_USER

DATE ( expression )

VALUES DATE('1988-12-25')

DAY ( expression )

values day('2005-08-02');

DOUBLE [PRECISION] (NumericExpression ) 

DOUBLE (StringExpression ) 

HOUR ( expression )

SELECT * FROM TABLE1
WHERE HOUR(STARTING) BETWEEN 12 AND 17

ij> create table t1(c1 int generated always as identity, c2 int);
0 rows inserted/updated/deleted
ij> insert into t1(c2) values (8);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1 
-------------------------------
1                              
1 row selected
ij> select IDENTITY_VAL_LOCAL()+1, IDENTITY_VAL_LOCAL()-1 from t1;
1                                |2                          
-------------------------------------------------------------------
2                                |0                                
1 row selected
ij> insert into t1(c2) values (IDENTITY_VAL_LOCAL());
1 row inserted/updated/deleted
ij> select * from t1;
C1             |C2             
-------------------------------
1              |8              
2              |1              
2 rows selected
ij> values IDENTITY_VAL_LOCAL();
1                        
-------------------------------
2                              
1 row selected
ij> insert into t1(c2) values (8), (9);
2 rows inserted/updated/deleted
ij> -- multi-values insert, return value of the function should not change
values IDENTITY_VAL_LOCAL();
1                        
-------------------------------
2                              
1 row selected
ij> select * from t1;
C1             |C2             
-------------------------------
1              |8              
2              |1              
3              |8              
4              |9              
4 rows selected
ij> insert into t1(c2) select c1 from t1;
4 rows inserted/updated/deleted
-- insert with sub-select, return value should not change
ij> values IDENTITY_VAL_LOCAL();
1                        
-------------------------------
2                              
1 row selected
ij> select * from t1;
C1             |C2             
-------------------------------
1              |8              
2              |1              
3              |8              
4              |9              
5              |1              
6              |2              
7              |3              
8              |4              
8 rows selected         

INT[EGER] (NumericExpression | CharacterExpression ) 

LOCATE(CharacterExpression, CharacterExpression [, StartPosition] ) 

-- returns 2
VALUES LOCATE('love', 'clover')

LCASE or LOWER ( CharacterExpression ) 

-- returns 'asd1#w'
VALUES LOWER('aSD1#w')

SELECT LOWER(flight_id) FROM Flights

LTRIM(CharacterExpression)

-- returns 'asdf  '
VALUES LTRIM(' asdf  ')

MAX ( [ DISTINCT | ALL ] Expression )

SELECT COUNT (DISTINCT flying_time), MAX (DISTINCT miles)
FROM Flights

-- find the latest date in the FlightAvailability table
SELECT MAX (flight_date) FROM FlightAvailability
-- find the longest flight originating from each airport,
-- but only when the longest flight is over 10 hours
SELECT MAX(flying_time), orig_airport
FROM Flights
GROUP BY orig_airport
HAVING MAX(flying_time) > 10