By Alvin Alexander. Last updated: December 15, 2021
My favorite book about SQL programming is the book, Understanding SQL, and I recently created this SQL code so I can experiment with the examples from the book:
create table salespeople ( snum int not null, sname varchar(10) not null, city varchar(10) not null, comm float not null, constraint unique index idx_salespeople (snum) ) engine = InnoDB; create table customers ( cnum int not null, cname varchar(10) not null, city varchar(10) not null, rating int not null, snum int not null, constraint unique index idx_cust (cnum), foreign key (snum) references salespeople(snum) ON DELETE CASCADE ) engine = InnoDB; create table orders ( onum int not null, amt float not null, odate date not null, cnum int not null, snum int not null, constraint unique index idx_orders (onum), foreign key (cnum) references customers(cnum) ON DELETE CASCADE, foreign key (snum) references salespeople(snum) ON DELETE CASCADE ) engine = InnoDB; LOAD DATA LOCAL INFILE 'salespeople.csv' INTO TABLE salespeople FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS; LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS; LOAD DATA LOCAL INFILE 'orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
At the moment I haven’t included the CSV file data here, but I may d that in the future.