SQL Cookbook, 2nd Edition

• DB2 11.5
• Oracle Database 19c
• PostgreSQL 12
• SQL Server 2017
• MySQL 8.0

mysql 8.0:

CREATE TABLE EMP (
    EMPNO INTEGER NOT NULL,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INTEGER,
    HIREDATE DATE,
    SAL INTEGER,
    COMM INTEGER,
    DEPTNO INTEGER
);

INSERT INTO EMP VALUES
    (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20),
    (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

CREATE TABLE DEPT (
    DEPTNO INTEGER,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);

INSERT INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE T1 (ID INTEGER);
INSERT INTO T1 VALUES (1);

CREATE TABLE T10 (ID INTEGER);
INSERT INTO T10 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

CREATE TABLE T100 (ID INTEGER);
CREATE TABLE T500 (ID INTEGER);

DELIMITER //

CREATE PROCEDURE InsertData()
BEGIN
    DECLARE counter INT DEFAULT 1;

    -- Insert into T100
    SET counter = 1;
    WHILE (counter <= 100) DO
        INSERT INTO T100 (ID) VALUES (counter);
        SET counter = counter + 1;
    END WHILE;

    -- Insert into T500
    SET counter = 1;
    WHILE (counter <= 500) DO
        INSERT INTO T500 (ID) VALUES (counter);
        SET counter = counter + 1;
    END WHILE;
END//

DELIMITER ;

CALL InsertData();

postgretsql 12:

CREATE TABLE EMP (
    EMPNO SERIAL PRIMARY KEY,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INTEGER,
    HIREDATE DATE,
    SAL INTEGER,
    COMM INTEGER,
    DEPTNO INTEGER
);

INSERT INTO EMP VALUES
    (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, DEFAULT, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, DEFAULT, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, DEFAULT, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, DEFAULT, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, DEFAULT, 20),
    (7839, 'KING', 'PRESIDENT', DEFAULT, '1981-11-17', 5000, DEFAULT, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, DEFAULT, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, DEFAULT, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, DEFAULT, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, DEFAULT, 10);

CREATE TABLE DEPT (
    DEPTNO INTEGER,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);

INSERT INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE T1 (ID INTEGER);
INSERT INTO T1 VALUES (1);

CREATE TABLE T10 (ID INTEGER);
INSERT INTO T10 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

CREATE TABLE T100 (ID INTEGER);
CREATE TABLE T500 (ID INTEGER);

CREATE OR REPLACE FUNCTION InsertData()
RETURNS VOID AS $$
DECLARE
    counter INT := 1;
BEGIN
    -- Insert into T100
    WHILE (counter <= 100) LOOP
        INSERT INTO T100 (ID) VALUES (counter);
        counter := counter + 1;
    END LOOP;

    -- Insert into T500
    counter := 1;
    WHILE (counter <= 500) LOOP
        INSERT INTO T500 (ID) VALUES (counter);
        counter := counter + 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT InsertData();

sql server 2019:

CREATE TABLE EMP (
    EMPNO int PRIMARY KEY,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR int,
    HIREDATE DATE,
    SAL int,
    COMM int,
    DEPTNO int
);
 
INSERT INTO EMP VALUES
    (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, DEFAULT, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, DEFAULT, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, DEFAULT, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, DEFAULT, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, DEFAULT, 20),
    (7839, 'KING', 'PRESIDENT', DEFAULT, '1981-11-17', 5000, DEFAULT, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, DEFAULT, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, DEFAULT, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, DEFAULT, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, DEFAULT, 10);
 
CREATE TABLE DEPT (
    DEPTNO int,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);
 
INSERT INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');

select * from EMP;

oracle 21c;

-- 1. 创建 EMP 表：给 COMM 加 DEFAULT 0，日期字段用 DATE 类型（Oracle 原生支持）
CREATE TABLE EMP (
    EMPNO INT PRIMARY KEY,          -- INT 在 Oracle 中是 NUMBER(38) 的别名，没问题
    ENAME VARCHAR2(10),             -- 推荐用 VARCHAR2（Oracle 官方推荐，兼容性更好）
    JOB VARCHAR2(9),
    MGR INT,
    HIREDATE DATE,                  -- Oracle 日期类型为 DATE，无需额外定义
    SAL INT,
    COMM INT DEFAULT 0,             -- 关键修正：给 COMM 加默认值 0，支持插入时用 DEFAULT
    DEPTNO INT
);
 
-- 2. 插入 EMP 数据：日期用 TO_DATE 显式转换格式，COMM 可写 DEFAULT 或直接用 0
INSERT ALL
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, DEFAULT, 20)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975, DEFAULT, 20)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981-09-28', 'YYYY-MM-DD'), 1250, 1400, 30)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850, DEFAULT, 30)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'YYYY-MM-DD'), 2450, DEFAULT, 10)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1982-12-09', 'YYYY-MM-DD'), 3000, DEFAULT, 20)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7839, 'KING', 'PRESIDENT', DEFAULT, TO_DATE('1981-11-17', 'YYYY-MM-DD'), 5000, DEFAULT, 10)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'YYYY-MM-DD'), 1500, 0, 30)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('1983-01-12', 'YYYY-MM-DD'), 1100, DEFAULT, 20)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 950, DEFAULT, 30)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 3000, DEFAULT, 20)
    
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('1982-01-23', 'YYYY-MM-DD'), 1300, DEFAULT, 10)
SELECT 1 FROM DUAL; -- 必须添加此句，Oracle 要求 INSERT ALL 以子查询结束


 
-- 3. 创建 DEPT 表：给 DEPTNO 加 PRIMARY KEY，保证唯一性和关联能力
CREATE TABLE DEPT (
    DEPTNO INT PRIMARY KEY,         -- 关键修正：DEPTNO 设为主键
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);
 
-- 4. 插入 DEPT 数据（无语法问题，直接使用）
INSERT ALL
INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK')
INTO DEPT VALUES    (20, 'RESEARCH', 'DALLAS')
INTO DEPT VALUES    (30, 'SALES', 'CHICAGO')
 INTO DEPT VALUES   (40, 'OPERATIONS', 'BOSTON')    
SELECT 1 FROM DUAL; 
-- 最后提交事务（Oracle 默认手动提交，需显式执行）


select * from EMP;

select * from DEPT;

SQL Cookbook
https://resources.oreilly.com/examples/9780596009762/
https://github.com/shahbaz-ali/SQL-Cook-Book-Sample-Data/
MySQL Cookbook 2nd Edition
https://resources.oreilly.com/examples/9780596527082/

posted @ 2025-08-28 22:18 ®Geovin Du Dream Park™ 阅读(17) 评论(0) 收藏举报

®Geovin Du Dream Park™

why we only heard about haves and have-nots, but we did'nt heard about doers and doer-nots. 人生是一种心境,生活是一种艺术,成功是一种心态,幸福是一种感觉,竞争是一种建构,情感是一种容合.学习是一种成长.

SQL Cookbook, 2nd Edition

公告