Teradata基础教程中的数据库表：
| Customer: | 客户信息表 |
| Location: | 位置信息表 |
| Employee: | 雇员信息表 |
| Job: | 工作信息表 |
| Department: | 部门表 |
| Employee_Phone: | 员工电话表 |
| Location_Employee: | 员工与位置的对照表 |
| Location_Phone: | 位置与电话的对照表 |
| Contact: | 联系表 |
整个试验数据库的名称是TERADATA_EDUCATION，其下分成以下三个数据库：
CUSTOMER_SERVICE数据库：包含所有的物理表
CS_VIEWS数据库：包含所有的视图
SQL用户：下面进一步分成SQL00至SQL20共21个数据库用户，供学员登录系统进行测试使用。每个用户登录的密码与其用户名相同，如用SQL01登录，其密码也为SQL01。
脚本文件有如下：
INTED00.sql
INTED01.sql
INTED02.sql
INTED03.sql
然后利用BTEQ或Queryman等工具来按秩序运行上面的脚本文件。以Windows BTEQ为例，假设上述脚本文件存放在\TNT_Sql目录下，则执行方法如下：
.run file=\TNT_Sql\INTED00.sql
.run file=\TNT_Sql\INTED01.sql
.run file=\TNT_Sql\INTED02.sql
.run file=\TNT_Sql\INTED03.sql
这样就创建了测试用的数据库，可以利用BTEQ或Queryman等前端工具来学习Teradata SQL，完成本书中所介绍的例子和练习。
INTED00.sql
.LOGON DBC,DBC
/*************************************************/
/* Create a "super user" for Teradata Education */
/* Classroom Lab database/userid installation */
/*************************************************/
/* Note: This script must be run by a user who */
/* has Create Database/User authority and */
/* the right to grant or re-grant select */
/* on the dictionary tables.
/*************************************************/
/* Space limits and account codes may be */
/* modified by the user (RPK 3/97) */
/*************************************************/
CREATE USER Teradata_Education AS PASSWORD = educate
PERM = 20000000 SPOOL = 5000000
ACCOUNT = ('$M_D2102');GRANT ALL ON Teradata_Education TO Teradata_Education
WITH GRANT OPTION;GRANT SELECT ON DBC TO Teradata_Education
WITH GRANT OPTION;
.LOGOFF
INSQL01.TXT
.SET SESSIONS 8
.SET QUIET
ON
.LOGON Teradata_Education,educate
/********************************************************/
/* Creates Database Customer_Service, defines and */
/* populates nine (9) sample tables (RPK 3/97) */
/********************************************************/
SELECT *
FROM DBC.Databases
WHERE DatabaseName = 'Customer_Service';
.IF ACTIVITYCOUNT = 0 THEN .GOTO CreateCS
GRANT DROP DATABASE
ON Customer_Service TO Teradata_Education;
DELETE DATABASE Customer_Service;
DROP DATABASE Customer_Service;
.LABEL CreateCS
CREATE DATABASE Customer_Service
FROM Teradata_Education AS
PERM=500000 ACCOUNT = ('$M_P0623');DATABASE Customer_Service;
CREATE TABLE contact, FALLBACK
(contact_number INTEGER
,contact_name CHAR(30) NOT NULL
,area_code SMALLINT NOT NULL
,phone INTEGER NOT NULL
,extension INTEGER
,last_call_date DATE NOT NULL)
UNIQUE PRIMARY INDEX (contact_number);
CREATE TABLE customer, FALLBACK
(customer_number INTEGER
,customer_name CHAR(30) NOT NULL
,parent_customer_number INTEGER
,sales_employee_number INTEGER
)
UNIQUE PRIMARY INDEX (customer_number);
CREATE TABLE department, FALLBACK
(department_number SMALLINT
,department_name CHAR(30) NOT NULL
,budget_amount DECIMAL(10,2)
,manager_employee_number INTEGER
)
UNIQUE PRIMARY INDEX (department_number)
,UNIQUE INDEX (department_name);
CREATE TABLE employee, FALLBACK
(employee_number INTEGER
,manager_employee_number INTEGER
,department_number INTEGER
,job_code INTEGER
,last_name CHAR(20) NOT NULL
,first_name VARCHAR(30) NOT NULL
,hire_date DATE NOT NULL
,birthdate DATE NOT NULL
,salary_amount DECIMAL(10,2) NOT NULL
)
UNIQUE PRIMARY INDEX (employee_number);
CREATE TABLE employee_phone, FALLBACK
(employee_number INTEGER NOT NULL
,area_code SMALLINT NOT NULL
,phone INTEGER NOT NULL
,extension INTEGER
,comment_line CHAR(72)
)
PRIMARY INDEX (employee_number);
CREATE TABLE job, FALLBACK
(job_code INTEGER
,description VARCHAR(40) NOT NULL
,hourly_billing_rate DECIMAL(6,2)
,hourly_cost_rate DECIMAL(6,2)
)
UNIQUE PRIMARY INDEX (job_code)
,UNIQUE INDEX (description);
CREATE TABLE location, FALLBACK
(location_number INTEGER
,customer_number INTEGER NOT NULL
,first_address_line CHAR(30) NOT NULL
,city VARCHAR(30) NOT NULL
,state CHAR(15) NOT NULL
,zip_code INTEGER NOT NULL
,second_address_line CHAR(30)
,third_address_line CHAR(30)
)
PRIMARY INDEX (customer_number);
CREATE TABLE location_employee, FALLBACK
(location_number INTEGER NOT NULL
,employee_number INTEGER NOT NULL
)
PRIMARY INDEX (employee_number);
CREATE TABLE location_phone, FALLBACK
(location_number INTEGER
,area_code SMALLINT NOT NULL
,phone INTEGER NOT NULL
,extension INTEGER
,description VARCHAR(40) NOT NULL
,comment_line LONG VARCHAR
)
PRIMARY INDEX (location_number);
INSERT INTO contact
VALUES
(8010,'Brayman, Connie',408,1112345,112,870721);
INSERT INTO contact
VALUES
(8001,'Leblanc, James',805,2213456,221,870801);
INSERT INTO contact
VALUES
(8005,'Hughes, Jack',212,5432126,710,870805);
INSERT INTO contact
VALUES
(8007,'Smith, Ginny',408,3792152,333,870801);
INSERT INTO contact
VALUES
(8008,'Torres, Alison',802,5487890,444,880814);
INSERT INTO contact
VALUES
(8009,'Dibble, Nancy',602,2713387,652,880809);
INSERT INTO customer
VALUES
(00,'Corporate Headquarters',NULL,NULL);
INSERT INTO customer
VALUES
(01,'A to Z Communications, Inc.',NULL,1015);
INSERT INTO customer
VALUES
(02,'Simple Instruments Co.',1,1015);
INSERT INTO customer
VALUES
(03,'First American Bank',NULL,1023);
INSERT INTO customer
VALUES
(04,'Sum Bank',3,1023);
INSERT INTO customer
VALUES
(05,'Federal Bureau of Rules',NULL,1018);
INSERT INTO customer
VALUES
(06,'Liberty Tours',NULL,1023);
INSERT INTO customer
VALUES
(07,'Cream of the Crop',NULL,1018);
INSERT INTO customer
VALUES
(08,'Colby Co.',NULL,1018);
INSERT INTO customer
VALUES
(09,'More Data Enterprise',NULL,1023);
INSERT INTO customer
VALUES
(10,'Graduates Job Service',NULL,1015);
INSERT INTO customer
VALUES
(11,'Hotel California',NULL,1015);
INSERT INTO customer
VALUES
(12,'Cheap Rentals',NULL,1018);
INSERT INTO customer
VALUES
(13,'First American Bank',3,1023);
INSERT INTO customer
VALUES
(14,'Metro Savings',NULL,1018);
INSERT INTO customer
VALUES
(15,'Cates Modeling',NULL,1015);
INSERT INTO customer
VALUES
(16,'VIP Investments',3,1023);
INSERT INTO customer
VALUES
(17,'East Coast Dating Service',NULL,1023);
INSERT INTO customer
VALUES
(18,'Wall Street Connection',NULL,1023);
INSERT INTO customer
VALUES
(19,'More Data Enterprise',9,1015);
INSERT INTO customer
VALUES
(20,'Metro Savings',14,1018);
INSERT INTO department
VALUES
(401,'customer support',982300,1003);
INSERT INTO department
VALUES
(201,'technical operations',293800,1025);
INSERT INTO department
VALUES
(301,'research and development',465600,1019);
INSERT INTO department
VALUES
(302,'product planning',226000,1016);
INSERT INTO department
VALUES
(403,'education',932000,1005);
INSERT INTO department
VALUES
(402,'software support',308000,1011);
INSERT INTO department
VALUES
(501,'marketing sales',308000,1017);
INSERT INTO department
VALUES
(100,'president',400000,0801);
INSERT INTO department
VALUES
(600,'None',,1099);
INSERT INTO employee
VALUES
(0801,0801,100,111100,'Trainer','I.B.',730301,450811,100000);
INSERT INTO employee
VALUES
(1001,1003,401,412101,'Hoover','William',760618,500114,25525);
INSERT INTO employee
VALUES
(1002,1003,401,413201,'Brown','Alan',760731,440809,43100);
INSERT INTO employee
VALUES
(1003,0801,401,411100,'Trader','James',760731,470619,37850);
INSERT INTO employee
VALUES
(1004,1003,401,412101,'Johnson','Darlene',761015,460423,36300);
INSERT INTO employee
VALUES
(1005,0801,403,431100,'Ryan','Loretta',761015,550910,31200);
INSERT INTO employee
VALUES
(1006,1019,301,312101,'Stein','John',761015,531015,29450);
INSERT INTO employee
VALUES
(1007,1005,403,432101,'Villegas','Arnando',770102,370131,49700);
INSERT INTO employee
VALUES
(1008,1019,301,312102,'Kanieski','Carol',770201,580517,29250);
INSERT INTO employee
VALUES
(1009,1005,403,432101,'Lombardo','Domingus',770201,451115,31000);
INSERT INTO employee
VALUES
(1010,1003,401,412101,'Rogers','Frank',770301,350423,46000);
INSERT INTO employee
VALUES
(1011,0801,402,421100,'Daly','James',770315,491211,52500);
INSERT INTO employee
VALUES
(1012,1005,403,432101,'Hopkins','Paulene',770315,420218,37900);
INSERT INTO employee
VALUES
(1013,1003,401,412102,'Phillips','Charles',770401,630810,24500);
INSERT INTO employee
VALUES
(1014,1011,402,422101,'Crane','Robert',780115,600704,24500);
INSERT INTO employee
VALUES
(1015,1017,501,512101,'Wilson','Edward',780301,570304,53625);
INSERT INTO employee
VALUES
(1016,0801,302,321100,'Rogers','Nora',780301,590904,56500);
INSERT INTO employee
VALUES
(1017,0801,501,511100,'Runyon','Irene',780501,511110,66000);
INSERT INTO employee
VALUES
(1018,1017,501,512101,'Ratzlaff','Larry',780715,540531,54000);
INSERT INTO employee
VALUES
(1019,0801,301,311100,'Kubic','Ron',780801,421211,57700);
INSERT INTO employee
VALUES
(1020,1005,403,432101,'Charles','John',781001,490621,39500);
INSERT INTO employee
VALUES
(1021,1025,201,222101,'Morrissey','Jim',781001,430429,38750);
INSERT INTO employee
VALUES
(1022,1003,401,412102,'Machado','Albert',790301,570714,32300);
INSERT INTO employee
VALUES
(1023,1017,501,512101,'Rabbit','Peter',790301,621029,26500);
INSERT INTO employee
VALUES
(1024,1005,403,432101,'Brown','Allen',790501,540116,43700);
INSERT INTO employee
VALUES
(1025,0801,201,211100,'Short','Michael',790501,470707,34700);
INSERT INTO employee_phone
VALUES
(0801,213,8278777,101,'Corporate President');
INSERT INTO employee_phone
VALUES
(1001,415,2412021,NULL,'Graduates Job Service');
INSERT INTO employee_phone
VALUES
(1001,415,3563560,NULL,'Hotel California');
INSERT INTO employee_phone
VALUES
(1001,213,2872019,NULL,'Cates Modeling');
INSERT INTO employee_phone
VALUES
(1001,415,6567000,NULL,'More Data');
INSERT INTO employee_phone
VALUES
(1001,415,4491221,NULL,'A TO Z office');
INSERT INTO employee_phone
VALUES
(1001,415,4491225,NULL,'A TO Z System Manager');
INSERT INTO employee_phone
VALUES
(1001,415,4491244,NULL,'A TO Z Secretary');
INSERT INTO employee_phone
VALUES
(1001,415,9234864,NULL,'residence/office');
INSERT INTO employee_phone
VALUES
(1001,415,9237892,NULL,'Simple Instruments');
INSERT INTO employee_phone
VALUES
(1002,213,2721606,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1002,213,8278777,439,'office');
INSERT INTO employee_phone
VALUES
(1003,213,3774534,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1003,213,8278777,401,'office');
INSERT INTO employee_phone
VALUES
(1004,212,7230101,NULL,'First Am. Bank computer room');
INSERT INTO employee_phone
VALUES
(1004,212,7232121,NULL,'First Am. Bank system manager');
INSERT INTO employee_phone
VALUES
(1004,609,5591011,213,'Sum Bank system manager');
INSERT INTO employee_phone
VALUES
(1004,609,5591011,224,'Sum Bank computer room');
INSERT INTO employee_phone
VALUES
(1004,609,5591011,225,'Sum Bank secretary');
INSERT INTO employee_phone
VALUES
(1004,609,5785781,NULL,'residence/office');
INSERT INTO employee_phone
VALUES
(1004,212,5786099,NULL,'Liberty Tours main number');
INSERT INTO employee_phone
VALUES
(1004,919,9789000,NULL,'More Data System Manager');
INSERT INTO employee_phone
VALUES
(1004,617,7567676,NULL,'First Am. Bank Manager');
INSERT INTO employee_phone
VALUES
(1004,212,8282828,NULL,'VIP Investments');
INSERT INTO employee_phone
VALUES
(1004,718,2243283,NULL,'East Coast Dating');
INSERT INTO employee_phone
VALUES
(1004,212,4909190,NULL,'Wall Street Connection');
INSERT INTO employee_phone
VALUES
(1005,213,2514189,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1005,213,8278777,415,'office');
INSERT INTO employee_phone
VALUES
(1006,213,8278777,410,'office');
INSERT INTO employee_phone
VALUES
(1006,213,3716087,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1007,213,2274764,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1007,213,8278777,440,'office');
INSERT INTO employee_phone
VALUES
(1008,213,3788092,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1008,213,8278777,429,'office');
INSERT INTO employee_phone
VALUES
(1009,213,2482619,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1009,213,8278777,413,'office');
INSERT INTO employee_phone
VALUES
(1010,202,5456187,NULL,'Residence/office');
INSERT INTO employee_phone
VALUES
(1010,213,8278777,NULL,'office');
INSERT INTO employee_phone
VALUES
(1010,202,3239119,NULL,'Fed Bureau of Rules request name');
INSERT INTO employee_phone
VALUES
(1010,804,9230911,NULL,'Cream of the Crop');
INSERT INTO employee_phone
VALUES
(1010,313,4630300,NULL,'Colby Co');
INSERT INTO employee_phone
VALUES
(1010,312,0990988,NULL,'Cheap Rentals');
INSERT INTO employee_phone
VALUES
(1010,804,4563000,370,'Metro Savings');
INSERT INTO employee_phone
VALUES
(1010,804,4563000,375,'Metro Savings');
INSERT INTO employee_phone
VALUES
(1010,312,5692122,NULL,'Metro Savings');
INSERT INTO employee_phone
VALUES
(1011,213,8278777,422,'office');
INSERT INTO employee_phone
VALUES
(1011,213,3549138,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1012,213,8278777,418,'office');
INSERT INTO employee_phone
VALUES
(1012,213,9788422,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1013,213,8278777,411,'office');
INSERT INTO employee_phone
VALUES
(1013,213,9857506,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1014,213,8278777,442,'office');
INSERT INTO employee_phone
VALUES
(1014,213,2528809,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1015,213,8278777,436,'office');
INSERT INTO employee_phone
VALUES
(1015,213,3012906,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1015,415,4491225,NULL,'A to Z system manager');
INSERT INTO employee_phone
VALUES
(1015,415,9237892,NULL,'Simple Instruments receptionist');
INSERT INTO employee_phone
VALUES
(1016,213,8278777,412,'office');
INSERT INTO employee_phone
VALUES
(1016,213,2925224,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1017,213,8278777,425,'office');
INSERT INTO employee_phone
VALUES
(1017,213,9231070,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1018,202,3239119,NULL,'Fed Bureau of Rules');
INSERT INTO employee_phone
VALUES
(1018,804,2989791,NULL,'residence/office');
INSERT INTO employee_phone
VALUES
(1019,213,8278777,418,'office');
INSERT INTO employee_phone
VALUES
(1019,213,2640855,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1020,213,8278777,433,'office');
INSERT INTO employee_phone
VALUES
(1020,213,2248513,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1021,213,8278777,428,'office');
INSERT INTO employee_phone
VALUES
(1021,213,2659291,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1022,213,8278777,416,'office');
INSERT INTO employee_phone
VALUES
(1022,213,4982012,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1023,212,7232121,NULL,'First Am. Bank system manager');
INSERT INTO employee_phone
VALUES
(1023,212,8283747,NULL,'residence/office');
INSERT INTO employee_phone
VALUES
(1023,609,5591011,213,'Sum Bank receptionist');
INSERT INTO employee_phone
VALUES
(1024,213,8278777,417,'office');
INSERT INTO employee_phone
VALUES
(1024,213,2724743,NULL,'residence');
INSERT INTO employee_phone
VALUES
(1025,213,8278777,429,'office');
INSERT INTO employee_phone
VALUES
(1025,213,2964652,NULL,'residence');
INSERT INTO job
VALUES
(111100,'Corporate President',0,0);
INSERT INTO job
VALUES
(412102,'Product Specialist',0,0);
INSERT INTO job
VALUES
(412103,'System Support Analyst',0,0);
INSERT INTO job
VALUES
(413201,'Dispatcher',0,0);
INSERT INTO job
VALUES
(412101,'Field Engineer',0,0);
INSERT INTO job
VALUES
(512101,'Sales Rep',0,0);
INSERT INTO job
VALUES
(312101,'Software Engineer',0,0);
INSERT INTO job
VALUES
(312102,'Hardware Engineer',0,0);
INSERT INTO job
VALUES
(322101,'Planning Specialist',0,0);
INSERT INTO job
VALUES
(432101,'Instructor',0,0);
INSERT INTO job
VALUES
(222101,'System Analyst',0,0);
INSERT INTO job
VALUES
(422101,'Software Analyst',0,0);
INSERT INTO job
VALUES
(104201,'Electronic Assembler',0,0);
INSERT INTO job
VALUES
(104202,'Mechanical Assembler',0,0);
INSERT INTO job
VALUES
(411100,'Manager - Customer Support',0,0);
INSERT INTO job
VALUES
(421100,'Manager - Software Support',0,0);
INSERT INTO job
VALUES
(431100,'Manager - Education',0,0);
INSERT INTO job
VALUES
(321100,'Manager - Product Planning',0,0);
INSERT INTO job
VALUES
(311100,'Manager - Research and Development',0,0);
INSERT INTO job
VALUES
(511100,'Manager - Marketing Sales',0,0);
INSERT INTO location
VALUES
(05000000,0,'1294 Jefferson Blvd','Los Angeles','California',
951604032,NULL,NULL);
INSERT INTO location
VALUES
(05000001,1,'101 Middlefield Rd','Palo
Alto','California',951604032,NULL,NULL);
INSERT INTO location
VALUES
(05000002,2,'49 Fourth St','San Francisco','California',941031066,
NULL,NULL);
INSERT INTO location
VALUES
(33000003,3,'10366 25th St','New York City','New
York',105293045,NULL,NULL);
INSERT INTO location
VALUES
(31000004,4,'55 Madison Av','Trenton','New Jersey',123419199,NULL,
NULL);
INSERT INTO location
VALUES
(09000005,5,'1 Lincoln Square','Washington','DC',156075555,NULL,
NULL);
INSERT INTO location
VALUES
(33000006,6,'10 River Rd','Schenectady','New York',123016166,NULL,
NULL);
INSERT INTO location
VALUES
(47000007,7,'4035 South 35th Av','Arlington','Virginia',222061016,
NULL,NULL);
INSERT INTO location
VALUES
(23000008,8,'1100 State St','Detroit','MI',484107888,NULL,NULL);
INSERT INTO location
VALUES
(34000009,9,'4400 Greenwood Rd','Wilmington','NC',284031199,NULL,
NULL);
INSERT INTO location
VALUES
(05000010,10,'5171 El Camino Real','Palo Alto','California',94071,
NULL,NULL);
INSERT INTO location
VALUES
(05000011,11,'770 Hotel Dr','Menlo Park','California',940585151,
NULL,NULL);
INSERT INTO location
VALUES
(14000012,12,'510 Benton Av','Chicago','Illinois',606483930,NULL,
NULL);
INSERT INTO location
VALUES
(22000013,13,'1059 Kings Rd','Boston','Massachusetts',012104091,
NULL,NULL);
INSERT INTO location
VALUES
(47000014,14,'1690 Miller Av','Richmond','Virginia',223104121,
NULL,NULL);
INSERT INTO location
VALUES
(05000015,15,'687 Culver Blvd','Culver
City','California',900513965,NULL,NULL);
INSERT INTO location
VALUES
(33000016,16,'2255 16th Av','New York City','New
York',105293033,NULL,NULL);
INSERT INTO location
VALUES
(33000017,17,'4001 Harbor Blvd','Brooklyn','New
York',105431915,NULL,NULL);
INSERT INTO location
VALUES
(33000018,18,'105 Time Square','New York City','New
York',105082682,NULL,NULL);
INSERT INTO location
VALUES
(05000019,19,'567 El Camino Real','San
Mateo','California',942153219,NULL,NULL)
;
INSERT INTO location
VALUES
(14000020,20,'876 Winston St','Chicago','Illinois',606316166,NULL,
NULL);
INSERT INTO location_employee
VALUES
(05000001,1001);
INSERT INTO location_employee
VALUES
(05000002,1001);
INSERT INTO location_employee
VALUES
(33000003,1004);
INSERT INTO location_employee
VALUES
(31000004,1004);
INSERT INTO location_employee
VALUES
(09000005,1010);
INSERT INTO location_employee
VALUES
(33000006,1004);
INSERT INTO location_employee
VALUES
(47000007,1010);
INSERT INTO location_employee
VALUES
(23000008,1010);
INSERT INTO location_employee
VALUES
(34000009,1004);
INSERT INTO location_employee
VALUES
(05000010,1001);
INSERT INTO location_employee
VALUES
(05000011,1001);
INSERT INTO location_employee
VALUES
(14000012,1010);
INSERT INTO location_employee
VALUES
(22000013,1004);
INSERT INTO location_employee
VALUES
(47000014,1010);
INSERT INTO location_employee
VALUES
(05000015,1001);
INSERT INTO location_employee
VALUES
(33000016,1004);
INSERT INTO location_employee
VALUES
(33000017,1004);
INSERT INTO location_employee
VALUES
(33000018,1004);
INSERT INTO location_employee
VALUES
(33000019,1001);
INSERT INTO location_employee
VALUES
(14000020,1010);
INSERT INTO location_phone
VALUES
(05000000,213,8278777,101,'Corporate Presidents office',NULL);
INSERT INTO location_phone
VALUES
(05000001,415,4491221,NULL,'FEs office',NULL);
INSERT INTO location_phone
VALUES
(05000001,415,4491225,NULL,'System Manager',NULL);
INSERT INTO location_phone
VALUES
(05000001,415,4491244,NULL,'Secretary','available 9:00 to 5:00');
INSERT INTO location_phone
VALUES
(05000002,415,9237892,NULL,'Receptionist','ask for page');
INSERT INTO location_phone
VALUES
(33000003,212,7230101,NULL,'Computer Room',NULL);
INSERT INTO location_phone
VALUES
(33000003,212,7232121,NULL,'System Manager',NULL);
INSERT INTO location_phone
VALUES
(31000004,609,5591011,213,'Receptionist','leave message');
INSERT INTO location_phone
VALUES
(31000004,609,5591011,224,'System Manager',NULL);
INSERT INTO location_phone
VALUES
(31000004,609,5591011,225,'Computer Room',NULL);
INSERT INTO location_phone
VALUES
(09000005,202,3239119,NULL,'Switchboard',NULL);
INSERT INTO location_phone
VALUES
(33000006,212,5786099,NULL,'Small office',NULL);
INSERT INTO location_phone
VALUES
(47000007,804,9230911,NULL,'Switchboard',NULL);
INSERT INTO location_phone
VALUES
(23000008,313,4630300,NULL,'Receptionist',NULL);
INSERT INTO location_phone
VALUES
(34000009,919,9789000,NULL,'Receptionist',NULL);
INSERT INTO location_phone
VALUES
(34000009,919,9789000,601,'John Moore','Vice President');
INSERT INTO location_phone
VALUES
(05000010,415,2412021,NULL,'Alice Hamm','President');
INSERT INTO location_phone
VALUES
(05000011,415,3563560,NULL,'J.R. Stern','Owner');
INSERT INTO location_phone
VALUES
(14000012,312,9880988,NULL,'Tom Thumb','Owner');
INSERT INTO location_phone
VALUES
(22000013,617,7567676,NULL,'Computer Room',NULL);
INSERT INTO location_phone
VALUES
(22000013,617,7562918,NULL,'System Manager',NULL);
INSERT INTO location_phone
VALUES
(47000014,804,4563000,370,'Alan Monday','System Manager');
INSERT INTO location_phone
VALUES
(47000014,804,4563000,375,'Receptionist',NULL);
INSERT INTO location_phone
VALUES
(05000015,213,2872019,NULL,'Charles Cates','Owner');
INSERT INTO location_phone
VALUES
(33000016,212,8282828,NULL,'Andy Moore',NULL);
INSERT INTO location_phone
VALUES
(33000017,718,2243283,NULL,'various contacts',NULL);
INSERT INTO location_phone
VALUES
(33000018,212,4909190,NULL,'Tom Sellers',NULL);
INSERT INTO location_phone
VALUES
(05000019,415,6567000,NULL,'Receptionist',NULL);
INSERT INTO location_phone
VALUES
(14000020,312,5692122,NULL,'Receptionist',NULL);
INSERT INTO location_phone
VALUES
(14000020,312,5692136,NULL,'System Manager',NULL);
.LOGOFF
INSQL02.sql
.LOGON Teradata_Education,educate
/********************************************************/
/* Creates Database CS_VIEWS and populates it with ten */
/* views of the Customer_Service tables. (RPK 3/97) */
/* */
/* Update by P. Derouin 8/19/97 */
/********************************************************/
SELECT DatabaseName
FROM DBC.Databases
WHERE DatabaseName = 'CS_VIEWS';
.IF ACTIVITYCOUNT = 0 THEN .GOTO CreateVMGRANT DROP DATABASE
ON CS_VIEWS TO Teradata_Education;
DELETE DATABASE CS_VIEWS;
DROP DATABASE CS_VIEWS;.LABEL CreateVM
CREATE DATABASE CS_VIEWS
FROM Teradata_Education AS PERM = 0
ACCOUNT = ('$M_P0623');
GRANT SELECT
ON Customer_Service TO CS_VIEWS WITH
GRANT OPTION;
DATABASE CS_VIEWS;
CREATE VIEW contact
(contact_number
,contact_name
,area_code
,phone
,extension
,last_call_date)
AS
SELECT
contact_number
,contact_name
,area_code
,phone
,extension
,last_call_date
FROM CUSTOMER_SERVICE.contact;
CREATE VIEW customer
(customer_number
,customer_name
,parent_customer_number
,sales_employee_number)
AS
SELECT
customer_number
,customer_name
,parent_customer_number
,sales_employee_number
FROM CUSTOMER_SERVICE.customer;
CREATE VIEW department
(department_number
,department_name
,budget_amount
,manager_employee_number)
AS
SELECT
department_number
,department_name
,budget_amount
,manager_employee_number
FROM CUSTOMER_SERVICE.department;
CREATE VIEW employee
(employee_number
,manager_employee_number
,department_number
,job_code
,last_name
,first_name
,hire_date
,birthdate
,salary_amount)
AS
SELECT
employee_number
,manager_employee_number
,department_number
,job_code
,last_name
,first_name
,hire_date
,birthdate
,salary_amount
FROM CUSTOMER_SERVICE.employee;
CREATE VIEW employee_phone
(employee_number
,area_code
,phone
,extension
,comment_line)
AS
SELECT
employee_number
,area_code
,phone
,extension
,comment_line
FROM CUSTOMER_SERVICE.employee_phone;
CREATE VIEW job
(job_code
,description
,hourly_billing_rate
,hourly_cost_rate)
AS
SELECT
job_code
,description
,hourly_billing_rate
,hourly_cost_rate
FROM CUSTOMER_SERVICE.job;
CREATE VIEW location
(location_number
,customer_number
,first_address_line
,city
,state
,zip_code
,second_address_line
,third_address_line)
AS
SELECT
location_number
,customer_number
,first_address_line
,city
,state
,zip_code
,second_address_line
,third_address_line
FROM CUSTOMER_SERVICE.location;
CREATE VIEW location_employee
(location_number
,employee_number)
AS
SELECT
location_number
,employee_number
FROM CUSTOMER_SERVICE.location_employee;
CREATE VIEW location_phone
(location_number
,area_code
,phone
,extension
,description
,comment_line)
AS
SELECT
location_number
,area_code
,phone
,extension
,description
,comment_line
FROM CUSTOMER_SERVICE.location_phone;CREATE VIEW emp
(emp
,mgr
,dept
,job
,last
,first1
,hire
,birth
,sal)
AS
SELECT
employee_number
,manager_employee_number
,department_number
,job_code
,last_name
,first_name
,hire_date
,birthdate
,salary_amount
FROM CUSTOMER_SERVICE.employee;
.LOGOFF
INSQL03.TXT
.LOGON Teradata_Education,educate
/*************************************************/
/* Create SQL Class Userids: SQL00 TO SQL20 */
/* from newly created userid SQL */
/*************************************************/
/* Note: This script will fail if these userids */
/* are already defined. To cleanup old SQL */
/* userids, run the script: rmsql01.txt */
/*************************************************/
/* Spool limit, account code values, and also */
/* access rights to the sample views and tables */
/* will be inherited from user SQL. (RPK 3/97) */
/* */
/* Updated by P.Derouin (8/19/97) */
/*************************************************/
CREATE USER SQL99
FROM Teradata_Education AS
PASSWORD = SQL99 PERM = 12000000
SPOOL = 500000 ACCOUNT = ('$M_P0623');
GRANT SELECT
ON Customer_Service TO ALL SQL99;
GRANT SELECT
ON CS_VIEWS TO ALL SQL99;
CREATE USER SQL00
FROM SQL99 AS PASSWORD = SQL00
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL01
FROM SQL99 AS PASSWORD = SQL01
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL02
FROM SQL99 AS PASSWORD = SQL02
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL03
FROM SQL99 AS PASSWORD = SQL03
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL04
FROM SQL99 AS PASSWORD = SQL04
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL05
FROM SQL99 AS PASSWORD = SQL05
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL06
FROM SQL99 AS PASSWORD = SQL06
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL07
FROM SQL99 AS PASSWORD = SQL07
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL08
FROM SQL99 AS PASSWORD = SQL08
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL09
FROM SQL99 AS PASSWORD = SQL09
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL10
FROM SQL99 AS PASSWORD = SQL10
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL11
FROM SQL99 AS PASSWORD = SQL11
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL12
FROM SQL99 AS PASSWORD = SQL12
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL13
FROM SQL99 AS PASSWORD = SQL13
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL14
FROM SQL99 AS PASSWORD = SQL14
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL15
FROM SQL99 AS PASSWORD = SQL15
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL16
FROM SQL99 AS PASSWORD = SQL16
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL17
FROM SQL99 AS PASSWORD = SQL17
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL18
FROM SQL99 AS PASSWORD = SQL18
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL19
FROM SQL99 AS PASSWORD = SQL19
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
CREATE USER SQL20
FROM SQL99 AS PASSWORD = SQL20
PERM = 250000 DEFAULT DATABASE = CS_VIEWS;
.LOGOFF
