CREATE TABLE dept (
  deptno INT PRIMARY KEY,
  dname VARCHAR(14) NULL,
  loc VARCHAR(13) NULL
);

CREATE TABLE emp (
  empno INT PRIMARY KEY,
  ename VARCHAR(10) NULL,
  job VARCHAR(9) NULL,
  mgr SMALLINT NULL,
  hiredate DATETIME NULL,
  sal NUMERIC(7,2) NULL,
  comm NUMERIC(7,2) NULL,
  deptno INT NULL REFERENCES dept(deptno)
);

CREATE TABLE unidac_blob (
  id INT PRIMARY KEY,
  title VARCHAR(30) NULL,
  picture IMAGE NULL
);

CREATE TABLE unidac_text(
  id INT PRIMARY KEY,
  title VARCHAR(30) NULL,
  textfield TEXT NULL
);

CREATE TABLE unidac_loaded (
  intg INTEGER NULL,
  dbl  DOUBLE PRECISION NULL,
  str  VARCHAR(50) NULL,
  dat  DATETIME NULL
);

CREATE TABLE crgrid_test (
  id INT PRIMARY KEY,
  name VARCHAR(10) NULL,
  country VARCHAR(30) NULL,
  city VARCHAR(30) NULL,
  street VARCHAR(30) NULL,
  birthdate DATETIME NULL,
  job VARCHAR(9) NULL,
  hiredate DATETIME NULL,
  sal NUMERIC(7, 2) NULL,
  remarks TEXT NULL
);

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

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

INSERT INTO crgrid_test (id, name, country, city, street, birthdate, job, hiredate, sal) VALUES
  (5001, 'SMITH', 'ENGLAND', 'LONDON', 'BOND st.', CONVERT(DATETIME, '12-10-1963', 105), 'CLERK',
   CONVERT(DATETIME, '17-12-1980', 105), 800);
INSERT INTO crgrid_test (id, name, country, city, street, birthdate, job, hiredate, sal) VALUES
  (5002, 'ALLEN', 'ENGLAND', 'LONDON', 'BAKER st.', CONVERT(DATETIME, '04-03-1961', 105), 'SALESMAN',
   CONVERT(DATETIME, '20-02-1981', 105), 1600);
INSERT INTO crgrid_test (id, name, country, city, street, birthdate, job, hiredate, sal) VALUES
  (5003, 'MARTIN', 'FRANCE', 'LION', 'WEAVER st.', CONVERT(DATETIME, '23-01-1957', 105), 'MANAGER',
   CONVERT(DATETIME, '02-04-1981', 105), 2900);

CREATE PROCEDURE sel_from_emp
AS
BEGIN
  SELECT * FROM emp ORDER BY empno
END
GO