DDL / DML

 

SQL: DDL/DML for Tutorial (SELECT Statement)

If you have a database and want to follow along with the examples in the SQL SELECT statement tutorial, we have included the DDL and DML that you will need below.

Just follow the instructions to populate your database. Then return to the tutorial so that you can try the examples for yourself.

Return to Tutorial

DDL for Tutorial Examples

DDL stands for Data Definition Language and are the statements required to create the tables used in the SELECT statement examples.

Execute the following DDL statements in your database:

CREATE TABLE categories
( cat_id int NOT NULL,
  cat_name char(50) NOT NULL,
  CONSTRAINT categories_pk PRIMARY KEY (cat_id)
);

CREATE TABLE customers
( cust_id int NOT NULL,
  last_name char(50) NOT NULL,
  first_name char(50) NOT NULL,
  favorite_website char(50),
  CONSTRAINT customers_pk PRIMARY KEY (cust_id)
);

CREATE TABLE dept
( deptno int NOT NULL,
  dname char(50) NOT NULL, loc varchar2(50)
  CONSTRAINT departments_pk PRIMARY KEY (dept_id)
);
CREATE TABLE emp
( 	empno int NOT NULL,
	ename varchar2(50) NOT NULL,
	job               VARCHAR2(9),  
	mgr               NUMBER(4),
	hiredate          DATE,
	sal               NUMBER(7,2),
	comm              NUMBER(7,2), 
	deptno            NUMBER(2)
  CONSTRAINT employees_pk PRIMARY KEY (empno)
);

  
CREATE TABLE orders
( order_id int NOT NULL,
  cust_id int,
  order_date date,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

CREATE TABLE products
( product_id int NOT NULL,
  product_name char(50) NOT NULL,
  cat_id int,
  CONSTRAINT products_pk PRIMARY KEY (product_id)
);

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  city char(50),
  state char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

DML for Tutorial Examples

DML stands for Data Manipulation Language. These are the INSERT statements that you will need to run in your database to populate the data:

Execute the following DML statements in your database:

INSERT INTO categories
(cat_id, cat_name)
VALUES
(25, 'Deli');

INSERT INTO categories
(cat_id, cat_name)
VALUES
(50, 'Produce');

INSERT INTO categories
(cat_id, cat_name)
VALUES
(75, 'Bakery');

INSERT INTO categories
(cat_id, cat_name)
VALUES
(100, 'General Merchandise');

INSERT INTO categories
(cat_id, cat_name)
VALUES
(125, 'Technology');

INSERT INTO customers
(cust_id, last_name, first_name, favorite_website)
VALUES
(4000, 'Jackson', 'Joe', 'google.com');

INSERT INTO customers
(cust_id, last_name, first_name, favorite_website)
VALUES
(5000, 'Smith', 'Jane', 'stackoverflow.com');

INSERT INTO customers
(cust_id, last_name, first_name, favorite_website)
VALUES
(6000, 'Ferguson', 'Samantha', 'bing.com');

INSERT INTO customers
(cust_id, last_name, first_name, favorite_website)
VALUES
(7000, 'Reynolds', 'Allen', 'google.com');

INSERT INTO customers
(cust_id, last_name, first_name, favorite_website)
VALUES
(8000, 'Anderson', 'Paige', NULL);

INSERT INTO customers
(cust_id, last_name, first_name, favorite_website)
VALUES
(9000, 'Johnson', 'Derek', 'microsoft.com');

INSERT INTO dept
(deptno, dname, loc)
VALUES
(10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept
(deptno, dname, loc)
VALUES
(20, 'RESEARCH', 'DALLAS');

INSERT INTO dept
(deptno, dname, loc)
VALUES
(30, 'SALES	CHICAGO', 'DALLAS');

INSERT INTO dept
(deptno, dname, loc)
VALUES
(40, 'OPERATIONS',	'BOSTON');

	
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7369,	'SMITH',	'CLERK',	7902,	'17-DEC-80',	800, NULL,		20);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7499,	'ALLEN','SALESMAN',	7698, '20-FEB-81',	1600,	300,	30);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7521,	'WARD','SALESMAN',	7698, '22-FEB-81',	1250,	500,	30);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7566,	'JONES','MANAGER',	7839, '02-APR-81',	2975, NULL,		20);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7654,	'MARTIN','SALESMAN',7698, '28-SEP-81',	1250,	1400,	30);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7698,	'BLAKE','MANAGER',	7839, '01-MAY-81',	2850,	NULL,	30);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7782,	'CLARK','MANAGER',	7839, '09-JUN-81',	2450,	NULL,	10);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7788,	'SCOTT','ANALYST',	7566, '09-DEC-82',	3000,	NULL,	20);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7839,	'KING','PRESIDENT',	NULL, '17-NOV-81',	5000,	NULL,	10);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7844,	'TURNER','SALESMAN',7698, '08-SEP-81',	1500,	0	30);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7876,	'ADAMS','CLERK',	7788, '12-JAN-83',	1100,	NULL,	20);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7900,	'JAMES','CLERK',	7698, '03-DEC-81',	950	,	NULL, 30);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7902,	'FORD','ANALYST',	7566, '03-DEC-81',	3000,	NULL,	20);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7934,	'MILLER','CLERK',	7782, '23-JAN-82',	1300,	NULL,	10);


INSERT INTO orders
(order_id, cust_id, order_date)
VALUES
(1,7000,'2016/04/18');

INSERT INTO orders
(order_id, cust_id, order_date)
VALUES
(2,5000,'2016/04/18');

INSERT INTO orders
(order_id, cust_id, order_date)
VALUES
(3,8000,'2016/04/19');

INSERT INTO orders
(order_id, cust_id, order_date)
VALUES
(4,4000,'2016/04/20');

INSERT INTO orders
(order_id, cust_id, order_date)
VALUES
(5,null,'2016/05/01');



INSERT INTO products
(product_id, product_name, cat_id)
VALUES
(1,'Pear',50);

INSERT INTO products
(product_id, product_name, cat_id)
VALUES
(2,'Banana',50);

INSERT INTO products
(product_id, product_name, cat_id)
VALUES
(3,'Orange',50);

INSERT INTO products
(product_id, product_name, cat_id)
VALUES
(4,'Apple',50);

INSERT INTO products
(product_id, product_name, cat_id)
VALUES
(5,'Bread',75);

INSERT INTO products
(product_id, product_name, cat_id)
VALUES
(6,'Sliced Ham',25);

INSERT INTO products
(product_id, product_name, cat_id)
VALUES
(7,'Kleenex',null);


INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(100, 'Microsoft', 'Redmond', 'Washington');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(200, 'Google', 'Mountain View', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(300, 'Oracle', 'Redwood City', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(400, 'Kimberly-Clark', 'Irving', 'Texas');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(500, 'Tyson Foods', 'Springdale', 'Arkansas');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(600, 'SC Johnson', 'Racine', 'Wisconsin');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(700, 'Dole Food Company', 'Westlake Village', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(800, 'Flowers Foods', 'Thomasville', 'Georgia');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(900, 'Electronic Arts', 'Redwood City', 'California');

Return to Tutorial