Rosetta Code

Table creation: Difference between revisions

Page Discussion

Table creation (view source)

Revision as of 13:22, 15 December 2018

5,009 bytes added ,  5 years ago
→‎{{header|SQL PL}}: Corrected for Db2 with output
m (→‎{{header|REXX}}: re-instated original whitespace.)
(→‎{{header|SQL PL}}: Corrected for Db2 with output)
Line 702:
<lang sql pl>
CREATE TABLE dept (
deptno NUMBERNUMERIC(2)
NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR2VARCHAR(14)
NOT NULL CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR2VARCHAR(13)
);
CREATE TABLE emp (
empno NUMBERNUMERIC(4)
NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2VARCHAR(10),
job VARCHAR2VARCHAR(9),
mgr NUMBERNUMERIC(4),
hiredate DATE,
sal NUMBERDECIMAL(7,2)
CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBERDECIMAL(7,2),
deptno NUMBERNUMERIC(2)
CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
 
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
 
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '171980-DEC12-8017', 800, NULL, 20);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '021981-APR04-8102', 2975, NULL, 20);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '091981-JUN06-8109', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '191987-APR04-8719', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '171981-NOV11-8117', 5000, NULL, 10);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '231987-MAY05-8723', 1100, NULL, 20);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '031981-DEC12-8103', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '231982-JAN01-8223', 1300, NULL, 10);
</lang>
<output>
db2 => CREATE TABLE dept (
db2 (cont.) => deptno NUMERIC(2)
db2 (cont.) => NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
db2 (cont.) => dname VARCHAR(14)
db2 (cont.) => NOT NULL CONSTRAINT dept_dname_uq UNIQUE,
db2 (cont.) => loc VARCHAR(13)
db2 (cont.) =>);
DB20000I The SQL command completed successfully.
db2 => CREATE TABLE emp (
db2 (cont.) => empno NUMERIC(4)
db2 (cont.) => NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
db2 (cont.) => ename VARCHAR(10),
db2 (cont.) => job VARCHAR(9),
db2 (cont.) => mgr NUMERIC(4),
db2 (cont.) => hiredate DATE,
db2 (cont.) => sal DECIMAL(7,2)
db2 (cont.) => CONSTRAINT emp_sal_ck CHECK (sal > 0),
db2 (cont.) => comm DECIMAL(7,2),
db2 (cont.) => deptno NUMERIC(2)
db2 (cont.) => CONSTRAINT emp_ref_dept_fk
db2 (cont.) => REFERENCES dept(deptno)
db2 (cont.) =>);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
DB20000I The SQL command completed successfully.
db2 => INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
DB20000I The SQL command completed successfully.
db2 => INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
DB20000I The SQL command completed successfully.
db2 => describe table dept;
 
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DEPTNO SYSIBM DECIMAL 2 0 No
DNAME SYSIBM VARCHAR 14 0 No
LOC SYSIBM VARCHAR 13 0 Yes
 
3 record(s) selected.
 
db2 => describe table emp;
 
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EMPNO SYSIBM DECIMAL 4 0 No
ENAME SYSIBM VARCHAR 10 0 Yes
JOB SYSIBM VARCHAR 9 0 Yes
MGR SYSIBM DECIMAL 4 0 Yes
HIREDATE SYSIBM DATE 4 0 Yes
SAL SYSIBM DECIMAL 7 2 Yes
COMM SYSIBM DECIMAL 7 2 Yes
DEPTNO SYSIBM DECIMAL 2 0 Yes
 
8 record(s) selected.
 
db2 => select * from dept;
 
DEPTNO DNAME LOC
------ -------------- -------------
10. ACCOUNTING NEW YORK
20. RESEARCH DALLAS
 
2 record(s) selected.
 
db2 => select * from emp;
 
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ------ ---------- --------- --------- ------
7369. SMITH CLERK 7902. 12/17/1980 800.00 - 20.
7566. JONES MANAGER 7839. 04/02/1981 2975.00 - 20.
7782. CLARK MANAGER 7839. 06/09/1981 2450.00 - 10.
7788. SCOTT ANALYST 7566. 04/19/1987 3000.00 - 20.
7839. KING PRESIDENT - 11/17/1981 5000.00 - 10.
7876. ADAMS CLERK 7788. 05/23/1987 1100.00 - 20.
7902. FORD ANALYST 7566. 12/03/1981 3000.00 - 20.
7934. MILLER CLERK 7782. 01/23/1982 1300.00 - 10.
 
8 record(s) selected.
</output>
 
=={{header|Tcl}}==
Anonymous user
Retrieved from "https://rosettacode.org/wiki/Special:MobileDiff/3277"