"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/View 'MYTABLE' does not exist.

ALTER TABLE table-Name
{
    ADD COLUMN column-definition |
    ADD CONSTRAINT clause |
    DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
    DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE 
	 constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
    ALTER [ COLUMN ] column-alteration |
    LOCKSIZE { ROW | TABLE }
}

Simple-column-NameDataType
[ Column-level-constraint ]*
[ [ WITH ] DEFAULT DefaultConstantExpression ]

column-Name SET DATA TYPE VARCHAR(integer) |
column-name SET INCREMENT BY integer-constant |
column-name RESTART WITH integer-constant |
column-name [ NOT ] NULL |
column-name [ WITH ] DEFAULT default-value 

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

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

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(?,?,?);

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 [[AS] correlation-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());

GRANT privilege-type ON [TABLE] { table-Name | view-Name } TO grantees

GRANT EXECUTE ON { FUNCTION | PROCEDURE } routine-designator TO grantees

  ALL PRIVILEGES |
  privilege-list

 table-privilege {, table-privilege }*

  DELETE |
  INSERT |
  REFERENCES [column list] |
  SELECT [column list] |
  TRIGGER |
  UPDATE [column list]

  ( column-identifier {, column-identifier}* )

{	authorization ID | PUBLIC } [,{ authorization ID | PUBLIC } ] *

{
	function-name | procedure-name
}

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 

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)

LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE

REVOKE privilege-type ON [ TABLE ] { table-Name | view-Name } FROM grantees

REVOKE EXECUTE ON { FUNCTION | PROCEDURE } routine-designator FROM grantees RESTRICT

  ALL PRIVILEGES |
  privilege-list 

  table-privilege {, table-privilege }* 

  DELETE |
  INSERT |
  REFERENCES [column list] |
  SELECT [column list] |
  TRIGGER |
  UPDATE [column list] 

  ( column-identifier {, column-identifier}* ) 

  { authorization ID | PUBLIC } [,{ authorization ID | PUBLIC } ] *

  {
   qualified-name [ signature ]
  }

REVOKE SELECT ON TABLE t FROM maria,harry 

REVOKE UPDATE, TRIGGER ON TABLE t FROM anita,zhi 

REVOKE SELECT ON TABLE s.v FROM PUBLIC

REVOKE UPDATE (c1,c2) ON TABLE s.v FROM PUBLIC

REVOKE EXECUTE ON PROCEDURE p FROM george RESTRICT 

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

{
    UPDATE table-Name [[AS] correlation-Name]
        SET column-Name = Value
        [ , column-Name = Value} ]*
        [WHERE clause] |
    UPDATE table-Name
        SET column-Name = Value
        [ , column-Name = Value ]*
        WHERE CURRENT OF
}

Expression | DEFAULT

-- 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 employees without a specific job title to MANAGER
UPDATE EMPLOYEE
	SET JOB = 'MANAGER'
	WHERE JOB IS NULL;
// 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'

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

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_name), 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

ORDER BY { column-Name | ColumnPosition | Expression }
    [ ASC | DESC ]
    [ , column-Name | ColumnPosition | Expression 
        [ ASC | DESC ] ] * 

SELECT CITY_NAME, COUNTRY AS NATION 
    FROM CITIES 
    ORDER BY NATION

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

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 COUNT(*),WORK_DEPT,AVG(SALARY)
     FROM EMPLOYEE
     GROUP BY WORK_DEPT
     ORDER BY 3

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

{
TableViewOrFunctionExpression | 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

{
    VALUES ( Value {, Value }* )
        [ , ( Value {, Value }* ) ]* |
    VALUES Value [ , Value ]*
 }

Expression | DEFAULT

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

(3+4)*9
(age < 16 OR age > 65) AND employed = TRUE

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

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

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