3. Create Teradata Table
This is a create table Tutorial in Teradata using CREATE TABLE statement using BTEQ script
CREATE DATABASE
PRACTICE_DB FROM DBC
AS PERM= 2000000000;
Objects are always created under some database. For creating a table we must create a database first and while creating database parent database is also specified. In the following example practice_DB is created as a child of already existing database DBC
Following statement set a database name as default database. As tables are created in some database and if you don't mention a databasename.tablename then tables or views or any other objects are created in the default database.
CREATE MULTISET TABLE DEPARTMENT ( DEPT_ID INTEGER, DEPT_NAME VARCHAR(30) )UNIQUE PRIMARY INDEX (DEPT_ID);
Mutiset / SET tables.
There are 2 types of tables. SET and another is multiset as SET table can not hold duplicate values and such type of tables are always slower especially once it is matter of inserting data into these tables. Reason on insertion of each record it will check all the rows if there is any such recordset already existing or not and this is default type of table. That means if there are thousand rows in the table on the insertion of each row it will check all the thousand rows and this way, performance impacts seriously. Best practice is always define table as multiset and control the duplication using primary index.
CREATE MULTISET TABLE EMPLOYEE ( EMPLOYEE_ID INTEGER, EMPLOYEE_NAME VARCHAR(50), DOB DATE format 'YYYY-MM-DD', DEPT_ID INTEGER, FOREIGN KEY (DEPT_ID) REFERENCES WITH CHECK OPTION PRACTICE_DB.DEPARTMENT(DEPT_ID) )UNIQUE PRIMARY INDEX (EMPLOYEE_ID);
In Above table a table created by name of DEPARTMENT and defining primary index DEPT_ID means DEPT_ID will always be a unique value for the each row of this table.
In the following we are creating another table as Employee where forcing the integrity check of DEPT_ID as a foreign key and only valid existing values from DEPARTMENT TABLE's DEPT_ID column will be accepted and inserted in this column of EMPLOYEE table
After creation of these 2 tables now we will be inserting few records in each table and then finally, a SELECT statement to extract records from these tables
INSERT INTO DEPARTMENT(DEPT_ID,DEPT_NAME)
VALUES
(33,'IT');
INSERT INTO EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_NAME,DOB,DEPT_ID) VALUES
(333,'JOSUF','1992-04-11',22);
SELECT * FROM DEPARTMENT;
SELECT * FROM EMPLOYEE;
