Skip to content

Instantly share code, notes, and snippets.

@showa-yojyo
Created November 27, 2023 07:14
Show Gist options
  • Save showa-yojyo/bb45bd501761eb243b7674999b4143d5 to your computer and use it in GitHub Desktop.
Save showa-yojyo/bb45bd501761eb243b7674999b4143d5 to your computer and use it in GitHub Desktop.
Commands and SQL statements executable in Firebird isql WIP
SHOW TABLE EMPLOYEE; -- isql-specific command
SELECT EMP_NO, FIRST_NAME FROM EMPLOYEE;
SELECT EMP_NO, FULL_NAME FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;
SELECT EMP_NO, LAST_NAME, SALARY, SALARY + 500 FROM EMPLOYEE;
SELECT DISTINCT DEPT_NO FROM EMPLOYEE;
SELECT LAST_NAME, DEPT_NO FROM EMPLOYEE WHERE DEPT_NO = 600;
SELECT EMP_NO, LAST_NAME, DEPT_NO FROM EMPLOYEE WHERE LAST_NAME = '';
SELECT LAST_NAME, DEPT_NO FROM EMPLOYEE
WHERE LAST_NAME = 'O''Brien'; -- how to escape quotes
SELECT EMP_NO, LAST_NAME, HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE = '1990-05-01';
SELECT EMP_NO, LAST_NAME, HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE != '1990-05-01';
SELECT EMP_NO, LAST_NAME, HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE > '1990-05-01';
SELECT EMP_NO, LAST_NAME, DEPT_NO FROM EMPLOYEE WHERE DEPT_NO BETWEEN 600 AND 700;
SELECT EMP_NO, LAST_NAME, DEPT_NO FROM EMPLOYEE
WHERE DEPT_NO IN (000, 100, 200, 300, 400, 500, 600);
SELECT EMP_NO, LAST_NAME FROM EMPLOYEE
WHERE LAST_NAME LIKE 'B%';
SELECT EMP_NO, LAST_NAME, PHONE_EXT FROM EMPLOYEE
WHERE PHONE_EXT IS NULL;
SELECT EMP_NO, LAST_NAME, SALARY, DEPT_NO FROM EMPLOYEE
WHERE DEPT_NO = 671 AND SALARY > 50000;
SELECT EMP_NO, LAST_NAME, SALARY, DEPT_NO FROM EMPLOYEE
WHERE DEPT_NO = 671 OR SALARY > 50000;
SELECT EMP_NO, LAST_NAME, DEPT_NO FROM EMPLOYEE
ORDER BY DEPT_NO;
SELECT EMP_NO, LAST_NAME, DEPT_NO FROM EMPLOYEE
ORDER BY DEPT_NO ASC, LAST_NAME DESC;
SELECT EMP_NO, LAST_NAME, PHONE_EXT FROM EMPLOYEE
ORDER BY PHONE_EXT DESC; -- null の位置に注意
SELECT EMP_NO, LAST_NAME, PHONE_EXT FROM EMPLOYEE
ORDER BY PHONE_EXT DESC NULLS FIRST;
SELECT FULL_NAME FROM EMPLOYEE WHERE LOWER(LAST_NAME) = 'green';
SELECT 'a' || 'b' FROM RDB$DATABASE; -- equivalent to Oracle's DUAL
SELECT SUBSTRING('Firebird' from 4 for 3) from RDB$DATABASE; -- 'ebi'
SELECT CHAR_LENGTH('Firebird') from RDB$DATABASE;
SELECT POSITION('A' in 'Firebird') from RDB$DATABASE; -- 0 if not found
SELECT LPAD(PHONE_EXT, 3, ' ') FROM EMPLOYEE;
SELECT TRIM(both 'O' from 'OEDO') FROM RDB$DATABASE;
SELECT TRIM(leading 'O' from 'OEDO') FROM RDB$DATABASE;
SELECT REPLACE('ORACLE', 'O', 'MI') FROM RDB$DATABASE;
SELECT REPLACE('ORACLE', 'O', '') FROM RDB$DATABASE;
SELECT LAST_NAME, ROUND(SALARY, -2) FROM EMPLOYEE;
SELECT LAST_NAME, TRUNC(SALARY) FROM EMPLOYEE; -- 0
SELECT MOD(50, 6) FROM RDB$DATABASE;
SELECT HIRE_DATE FROM EMPLOYEE;
SELECT CURRENT_DATE FROM RDB$DATABASE; -- equivalent to Oracle's SYSDATE
SELECT DATEDIFF(month, date '2009-08-15', date '2009-03-15') FROM RDB$DATABASE;
SELECT DATEADD(5 day to date '2009-08-15') FROM RDB$DATABASE;
SELECT LAST_DAY(of month from date '2009-08-15') FROM RDB$DATABASE;
SELECT CAST('today' as date) FROM RDB$DATABASE;
SELECT CAST(CURRENT_DATE as varchar(10)) FROM RDB$DATABASE;
SELECT CAST(CURRENT_TIMESTAMP as date) FROM RDB$DATABASE;
SELECT LAST_NAME, COALESCE(PHONE_EXT, 'N/A') FROM EMPLOYEE;
SELECT LAST_NAME, SALARY,
CASE
WHEN (SALARY BETWEEN 0.0 AND 50000.0) THEN 'C'
WHEN (SALARY BETWEEN 50000.0 AND 100000.0) THEN 'B'
ELSE 'A'
END GRADE
FROM EMPLOYEE;
SELECT LAST_NAME, DECODE(JOB_GRADE,
0, 'Novice',
1, 'Aspirant',
2, 'Battler',
3, 'Fighter',
4, 'Adept',
5, 'Chevalier',
6, 'Veteran',
'Warrior')
FROM EMPLOYEE;
SELECT AVG(SALARY), SUM(SALARY), MIN(SALARY), MAX(SALARY), COUNT(SALARY)
FROM EMPLOYEE;
SELECT MAX(FIRST_NAME), MIN(FIRST_NAME), COUNT(FIRST_NAME) FROM EMPLOYEE;
SELECT DEPT_NO, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPT_NO;
SELECT JOB_CODE, AVG(SALARY) FROM EMPLOYEE GROUP BY JOB_CODE;
SELECT DEPT_NO, JOB_CODE, AVG(SALARY) FROM EMPLOYEE
WHERE JOB_CODE = 'SRep'
GROUP BY DEPT_NO, JOB_CODE
HAVING AVG(SALARY) >= 50000;
SELECT DEPT_NO, AVG(SALARY) FROM EMPLOYEE
GROUP BY DEPT_NO
HAVING COUNT(1) > 2;
SELECT MIN(AVG(SALARY)) FROM EMPLOYEE
GROUP BY DEPT_NO; -- ERROR -104
----------------------------------------------------------------------
SELECT EMP_NO, LAST_NAME, DEPARTMENT
FROM EMPLOYEE NATURAL JOIN DEPARTMENT;
SELECT EMP_NO, FULL_NAME, JOB_TITLE
FROM EMPLOYEE NATURAL JOIN JOB;
SELECT EMP_NO, LAST_NAME, DEPARTMENT
FROM EMPLOYEE
JOIN DEPARTMENT USING (DEPT_NO);
SELECT PO_NUMBER, SALES_REP, CUSTOMER, CURRENCY
FROM SALES
JOIN CUSTOMER USING(CUST_NO)
JOIN COUNTRY USING(COUNTRY);
SELECT EMP_NO, LAST_NAME, DEPARTMENT
FROM EMPLOYEE
JOIN DEPARTMENT USING (DEPT_NO)
WHERE DEPT_NO = 600;
SELECT PO_NUMBER, CUST_NO, CUSTOMER
FROM SALES
JOIN CUSTOMER USING (CUST_NO);
SELECT PO_NUMBER, SALES_REP, FULL_NAME
FROM SALES
JOIN EMPLOYEE ON SALES.SALES_REP = EMPLOYEE.EMP_NO;
SELECT PO_NUMBER, SALES_REP, CUSTOMER, CURRENCY
FROM SALES
JOIN CUSTOMER ON SALES.CUST_NO = CUSTOMER.CUST_NO
JOIN COUNTRY ON CUSTOMER.COUNTRY = COUNTRY.COUNTRY;
SELECT d.DEPT_NO, d.DEPARTMENT, m.DEPT_NO, m.DEPARTMENT
FROM DEPARTMENT d
JOIN DEPARTMENT m ON d.HEAD_DEPT = m.DEPT_NO;
SELECT e.EMP_NO, e.LAST_NAME, e.SALARY, h.EMP_NO
FROM EMPLOYEE e JOIN SALARY_HISTORY h
ON e.SALARY BETWEEN h.OLD_SALARY AND h.NEW_SALARY; -- 意味はない
SELECT e.EMP_NO, e.LAST_NAME, d.DEPARTMENT
FROM EMPLOYEE e RIGHT OUTER JOIN DEPARTMENT d
USING (DEPT_NO); -- WHERE e.EMP_NO IS NULL;
SELECT e.EMP_NO, e.LAST_NAME, d.DEPARTMENT
FROM DEPARTMENT d LEFT OUTER JOIN EMPLOYEE e
USING (DEPT_NO); -- WHERE e.EMP_NO IS NULL;
SELECT e.LAST_NAME, d.DEPARTMENT
FROM EMPLOYEE e CROSS JOIN DEPARTMENT d; -- 意味は当然ない
----------------------------------------------------------------------
SELECT LAST_NAME, SALARY FROM EMPLOYEE WHERE SALARY >=
(SELECT SALARY FROM EMPLOYEE WHERE LAST_NAME = 'Green');
SELECT * FROM
(SELECT AVG(SALARY) SALARY_MEAN, DEPT_NO
FROM EMPLOYEE
GROUP BY DEPT_NO);
SELECT LAST_NAME FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE); -- why only Japanese?
----------------------------------------------------------------------
SELECT LAST_NAME, JOB_CODE, JOB_COUNTRY FROM EMPLOYEE
WHERE JOB_CODE IN
(SELECT JOB_CODE FROM EMPLOYEE
WHERE JOB_COUNTRY = 'USA');
SELECT LAST_NAME, SALARY, DEPT_NO FROM EMPLOYEE
WHERE SALARY < ANY -- ALL も試そう
(SELECT SALARY FROM EMPLOYEE WHERE DEPT_NO = 623);
AND DEPT_NO != 623
ORDER BY DEPT_NO;
SELECT EMP_NO, LAST_NAME FROM EMPLOYEE
WHERE DEPT_NO IN (
SELECT DEPT_NO FROM DEPARTMENT
WHERE DEPARTMENT IN ('Customer Support', 'Customer Service'));
----------------------------------------------------------------------
SELECT DEPT_NO, MIN(SALARY) FROM EMPLOYEE
GROUP BY DEPT_NO
HAVING MIN(SALARY) > (
SELECT MIN(SALARY) FROM EMPLOYEE WHERE DEPT_NO = 623);
----------------------------------------------------------------------
SELECT DEPT_NO, DEPARTMENT FROM DEPARTMENT
WHERE DEPT_NO IN (
SELECT HEAD_DEPT FROM DEPARTMENT);
SELECT DEPT_NO, DEPARTMENT FROM DEPARTMENT
WHERE DEPT_NO NOT IN (
SELECT HEAD_DEPT FROM DEPARTMENT
WHERE HEAD_DEPT IS NOT NULL);
----------------------------------------------------------------------
SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY FROM EMPLOYEE
UNION -- ALL も試そう
SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY FROM JOB;
-- Use below instead of INTERSECT:
SELECT DISTINCT JOB_CODE, JOB_GRADE, JOB_COUNTRY
FROM EMPLOYEE JOIN JOB
USING (JOB_CODE, JOB_GRADE, JOB_COUNTRY);
-- Use below instead of MINUS or EXCEPT:
SELECT DISTINCT e.JOB_CODE, e.JOB_GRADE, e.JOB_COUNTRY
FROM EMPLOYEE e
LEFT OUTER JOIN JOB j
USING (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
WHERE j.JOB_CODE IS NULL;
----------------------------------------------------------------------
INSERT INTO COUNTRY (COUNTRY, CURRENCY) VALUES ('Korea', 'Won');
INSERT INTO COUNTRY VALUES ('Korea', 'Won');
INSERT INTO SALARY_HISTORY
(EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY)
VALUES
(138, CURRENT_TIMESTAMP - 1, 'updater', 20.0);
INSERT INTO SALARY_HISTORY
(EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY)
VALUES
(138, '2023-11-25', LOWER(USER), 30.0);
-- INSERT INTO SELECT のための一時テーブルを作成
CREATE TABLE ENGINEER_EMPLOYEE (
EMP_NO SMALLINT NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL,
DEPT_NO CHAR(3) NOT NULL,
CONSTRAINT PK_ENGINEER PRIMARY KEY (EMP_NO)
);
-- DROP TABLE ENGINEER_EMPLOYEE;
INSERT INTO ENGINEER_EMPLOYEE
SELECT EMP_NO, FIRST_NAME, LAST_NAME, DEPT_NO FROM EMPLOYEE
WHERE JOB_CODE = 'Eng';
UPDATE SALARY_HISTORY
SET UPDATER_ID = LOWER(UPDATER_ID)
WHERE UPDATER_ID = USER;
UPDATE EMPLOYEE
SET LAST_NAME = 'Scott';
UPDATE EMPLOYEE SET
DEPT_NO = (
SELECT DEPT_NO FROM EMPLOYEE WHERE EMP_NO = 138),
SALARY = (
SELECT MAX(SALARY) FROM EMPLOYEE WHERE JOB_CODE = 'Eng'); -- ある理由で失敗するが後学のために実行するといい
DELETE FROM SALARY_HISTORY;
DELETE FROM EMPLOYEE
WHERE DEPT_NO = (
SELECT DEPT_NO FROM EMPLOYEE WHERE EMP_NO = 110)
AND SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE WHERE JOB_CODE = 'Eng'); -- これも
----------------------------------------------------------------------
-- COMMIT/ROLLBACK
INSERT INTO COUNTRY VALUES ('Korea', 'Won');
INSERT INTO COUNTRY VALUES ('Macau', 'Pataca');
COMMIT; -- or ROLLBACK;
-- SAVEPOINT then ROLLBACK TO SAVEPOINT
INSERT INTO COUNTRY VALUES ('Korea', 'Won');
INSERT INTO COUNTRY VALUES ('Macau', 'Pataca');
SAVEPOINT A;
INSERT INTO COUNTRY VALUES('Israel', 'Shekel');
INSERT INTO COUNTRY VALUES('Nigeria', 'Naira');
ROLLBACK TO A;
----------------------------------------------------------------------
-- Lock
-- 試すためにはユーザーアカウントを追加する必要がある。
-- SYSDBA で localhost:employee に接続して次を実行:
-- CREATE USER username PASSWORD '<password>';
-- User A:
SELECT EMP_NO, LAST_NAME FROM EMPLOYEE FOR UPDATE;
UPDATE EMPLOYEE SET LAST_NAME = 'XXXX' WHERE EMP_NO = 138;
-- User B:
UPDATE EMPLOYEE SET LAST_NAME = 'YYYY' WHERE EMP_NO = 138;
-- Waiting...
-- User A:
COMMIT; -- or ROLLBACK
-- User B:
-- Execution...
----------------------------------------------------------------------
-- TRUNCATE TABLE: Firebird にはこの構文がない。
-- CREATE TABLE PEOPLE (
-- id int not null,
-- nickname varchar(12) not null,
-- country char(4),
-- constraint pk_people primary key (id),
-- constraint uk_nickname unique (nickname) using index ix_nick
-- );
----------------------------------------------------------------------
-- TABLE
-- 表コマンド実行には権限が必要だ。SYSDBA でログインしたセッションにて
-- GRANT CREATE TABLE TO USER <username>; しておくこと。
CREATE TABLE EMP(
EMP_NO SMALLINT,
LAST_NAME VARCHAR(20),
EMAIL VARCHAR(30),
DEPT_NO CHAR(3)
);
-- show table emp;
--CREATE TABLE COPY_EMP(EMP_NO, LAST_NAME, EMAIL, DEPT_NO)
-- AS SELECT EMP_NO, LAST_NAME, NULL, DEPT_NO FROM EMPLOYEE;
-- SELECT * FROM COPY_EMP;
CREATE TABLE EMP_DEF(
EMP_NO SMALLINT,
LAST_NAME VARCHAR(20),
HIRE_DATE TIMESTAMP DEFAULT 'NOW',
DEPT_NO CHAR(3)
);
-- 不明
-- ALTER TABLE DEPARTMENT READ ONLY;
DROP TABLE EMP;
DROP TABLE COPY_EMP;
DROP TABLE EMP_DEF;
----------------------------------------------------------------------
-- NOT NULL, UNIQUE, PK, FK, CHECK
CREATE TABLE EMP(
EMP_NO SMALLINT NOT NULL,
LAST_NAME VARCHAR(20),
EMAIL VARCHAR(30),
DEPT_NO CHAR(3)
);
DROP TABLE EMP;
CREATE TABLE DEPT(
DEPT_NO CHAR(3) UNIQUE,
DEPARTMENT VARCHAR(25)
);
DROP TABLE DEPT;
CREATE TABLE DEPT(
DEPT_NO CHAR(3) PRIMARY KEY,
DEPARTMENT VARCHAR(25)
);
DROP TABLE DEPT;
-- 制約を一列に二つ付ける場合
CREATE TABLE DEPT(
DEPT_NO CHAR(3) NOT NULL,
DEPARTMENT VARCHAR(25) UNIQUE,
PRIMARY KEY (DEPT_NO)
);
DROP TABLE DEPT;
CREATE TABLE DEPT(
DEPT_NO CHAR(3),
DEPARTMENT VARCHAR(25),
MNGR_NO SMALLINT REFERENCES EMPLOYEE(EMP_NO)
);
DROP TABLE DEPT;
CREATE TABLE DEPT(
DEPT_NO CHAR(3) CHECK (
DEPT_NO = '000' OR (DEPT_NO > '0' AND DEPT_NO <= '999') OR DEPT_NO IS NULL),
DEPARTMENT VARCHAR(25)
);
DROP TABLE DEPT;
----------------------------------------------------------------------
-- View の作成にも GRANT が必要であることに注意。
CREATE VIEW EMP_V AS
SELECT EMP_NO, LAST_NAME, HIRE_DATE, DEPT_NO FROM EMPLOYEE
WHERE JOB_CODE = 'Eng';
-- show table EMP_V;
CREATE OR ALTER VIEW EMP_V (EID, LAST_NAME, HIRE_DATE, DID) AS
SELECT EMP_NO, LAST_NAME, HIRE_DATE, DEPT_NO FROM EMPLOYEE
WHERE JOB_CODE = 'Eng';
-- show table EMP_V;
CREATE VIEW DEPT_V (NAME, MAX_SALARY) AS
SELECT d.DEPARTMENT, MAX(e.SALARY)
FROM EMPLOYEE e JOIN DEPARTMENT d
USING (DEPT_NO)
GROUP BY d.DEPARTMENT;
-- SELECT * FROM DEPT_V;
CREATE OR ALTER VIEW COUNTRY_V AS
SELECT * FROM COUNTRY
WHERE CURRENCY NOT LIKE 'E%'
WITH CHECK OPTION; -- CONSTRAINT は指定不能
UPDATE COUNTRY_V SET CURRENCY = 'En' WHERE CURRENCY = 'Yen'; -- expectedly fail
-- Oracle の WITH READ ONLY はない。
DROP VIEW EMP_V;
DROP VIEW DEPT_V;
DROP VIEW COUNTRY_V;
----------------------------------------------------------------------
-- SEQUENCE の作成(どころか閲覧)にも GRANT を要する。
CREATE SEQUENCE EMP_SEQ
START WITH 0 INCREMENT BY 1; -- EXT VALUE FOR seq_name expression.
-- show sequence EMP_SEQ;
SELECT NEXT VALUE FOR EMP_SEQ FROM RDB$DATABASE;
ALTER SEQUENCE EMP_SEQ
RESTART WITH 100;
SELECT NEXT VALUE FOR EMP_SEQ FROM RDB$DATABASE;
DROP SEQUENCE EMP_SEQ;
----------------------------------------------------------------------
-- INDEX 作成
-- CREATE TABLE EMP(
-- EMP_NO SMALLINT,
-- LAST_NAME VARCHAR(20),
-- EMAIL VARCHAR(30),
-- DEPT_NO CHAR(3)
-- );
CREATE INDEX ix_emp_no ON EMP (EMP_NO);
-- show index ix_emp_no;
DROP INDEX ix_emp_no;
----------------------------------------------------------------------
-- SYNONYM はおそらくない
@showa-yojyo
Copy link
Author

参考:『Oracle Master Bronze 11g SQL 基礎 I 必修教本』

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment