Table creation: Difference between revisions

Content added Content deleted
m (→‎{{header|REXX}}: re-instated original whitespace.)
(→‎{{header|SQL PL}}: Corrected for Db2 with output)
Line 702: Line 702:
<lang sql pl>
<lang sql pl>
CREATE TABLE dept (
CREATE TABLE dept (
deptno NUMBER(2)
deptno NUMERIC(2)
NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR2(14)
dname VARCHAR(14)
NOT NULL CONSTRAINT dept_dname_uq UNIQUE,
NOT NULL CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR2(13)
loc VARCHAR(13)
);
);
CREATE TABLE emp (
CREATE TABLE emp (
empno NUMBER(4)
empno NUMERIC(4)
NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2(10),
ename VARCHAR(10),
job VARCHAR2(9),
job VARCHAR(9),
mgr NUMBER(4),
mgr NUMERIC(4),
hiredate DATE,
hiredate DATE,
sal NUMBER(7,2)
sal DECIMAL(7,2)
CONSTRAINT emp_sal_ck CHECK (sal > 0),
CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBER(7,2),
comm DECIMAL(7,2),
deptno NUMBER(2)
deptno NUMERIC(2)
CONSTRAINT emp_ref_dept_fk
CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
REFERENCES dept(deptno)
);
);


INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');


INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
</lang>
</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}}==
=={{header|Tcl}}==
Retrieved from "https://rosettacode.org/wiki/Table_creation"