Content Entry

OReilly - SQL Cookbook - 数据库用例代码(PostgreSQL)

Published: 2008-11-12 Categories: Cookbook Tags: SQL Cookbook OReilly Structure Case Data

--
-- PostgreSQL 8.*
--
SET client_encoding = 'UTF8';

--
-- table emp
--
CREATE TABLE emp (
EMPNO bigserial,
ENAME varchar(32) default NULL,
JOB varchar(32) default NULL,
MGR integer default NULL,
HIREDATE date,
SAL integer default NULL,
COMM integer default NULL,
DEPTNO integer default NULL,
CONSTRAINT pri_emp__EMPNO PRIMARY KEY (EMPNO)
) ;
--
-- Dumping data for table emp
--
INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 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');

--
-- table dept
--
CREATE TABLE dept (
DEPTNO bigserial,
DNAME varchar(32) default NULL,
LOC varchar(32) default NULL,
CONSTRAINT pri_dept__DEPTNO PRIMARY KEY (DEPTNO)
) ;

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

--
-- table t1
--
CREATE TABLE t1 (
ID bigserial,
CONSTRAINT pri_t1__ID PRIMARY KEY (ID)
) ;

INSERT INTO t1 (ID)
VALUES
('1');

--
-- table t10
--
CREATE TABLE t10 (
ID bigserial,
CONSTRAINT pri_t10__ID PRIMARY KEY (ID)
) ;

INSERT INTO t10 (ID)
VALUES
('1'),
('2'),
('3'),
('4'),
('5'),
('6'),
('7'),
('8'),
('9'),
('10');

Learn:
SQL Cookbook, by Anthony Molinaro.
Copyright 2006 O'Reilly Media, Inc.

--EOF--

comments loading