######################################################################## # Every thing following # to the end of line is viewed as comments by SQL # # The SQL keywords in the following are capitalized here to make it easier # to recognize them and understand the SQL statements. # Note that SQL is case-insensitive so it is fine # if you use lower-case letters instead. ######################################################################## ######################################################################## # Create the database cs402 (if it is not there yet) and use it ######################################################################## CREATE DATABASE cs402; USE cs402; ######################################################################## # See what tables are currently in that database ######################################################################## SHOW TABLES; ######################################################################## # Create the salespeople table (if it is not there yet) in the current database ######################################################################## CREATE TABLE salespeople ( snum INTEGER NOT NULL, sname CHAR(15), city CHAR (15), comm NUMERIC(3,2), PRIMARY KEY (snum) ); ######################################################################## # Insert 5 records into the salespeople table ######################################################################## INSERT INTO salespeople VALUES (1001, 'Peel','London', 0.12); INSERT INTO salespeople VALUES (1002, 'Serres', 'San Jose', 0.13); INSERT INTO salespeople VALUES (1004, 'Motika', 'London', 0.11); INSERT INTO salespeople VALUES (1007, 'Rifkin', 'Barcelona', 0.15); INSERT INTO salespeople VALUES (1003, 'Axelrod', 'New York', 0.10); # Check what tables are currently in that database again SHOW TABLES; # Ask MySQL to describe the structure (i.e. schema) of the table DESCRIBE salespeople; # Show all current records in this table SELECT * FROM salespeople; ######################################################################## # Create the customers table (if it is not there yet) in the current database ######################################################################## CREATE TABLE customers (cnum INTEGER not null, cname CHAR(15), city CHAR(15), rating INTEGER, snum INTEGER, PRIMARY KEY (cnum)); ######################################################################## # Insert 7 records (if they are not there yet)into the customers table ######################################################################## INSERT INTO customers VALUES (2001,'Hoffman', 'London', 100, 1001); INSERT INTO customers VALUES (2002, 'Giovanni', 'Rome', 200, 1003); INSERT INTO customers VALUES (2003, 'Liu', 'San Jose', 200, 1002); INSERT INTO customers VALUES (2004, 'Grass', 'Berlin', 300, 1002); INSERT INTO customers VALUES (2006, 'Clemens', 'London', NULL, 1001); INSERT INTO customers VALUES (2008, 'Cisneros', 'San Jose', 300, 1007); INSERT INTO customers VALUES (2007, 'Pereira', 'Rome', 100, 1004); # Check what tables are currently in that database again SHOW TABLES; # Ask MySQL to describe the structure (i.e. schema) of the table DESCRIBE customers; # Show all current records in this table SELECT * FROM customers; ######################################################################## # Create the orders table (if it is not there yet) in the current database ######################################################################## CREATE TABLE orders (onum INTEGER not null, amot NUMERIC(8,2), odate DATE, cnum INTEGER, snum INTEGER, PRIMARY KEY (onum)); ######################################################################## # Insert 10 records into the orders table (if they are not there yet) ######################################################################## INSERT INTO orders VALUES (3001, 18.69, '2000-10-03', 2008, 1007); INSERT INTO orders VALUES (3003, 767.19, '2000-10-03', 2001, 1001); INSERT INTO orders VALUES (3002, 1900.10, '2000-10-03', 2007, 1004); INSERT INTO orders VALUES (3005, 5160.45, '2000-10-03', 2003, 1002); INSERT INTO orders VALUES (3006, 1098.16, '2000-10-03', 2008, 1007); INSERT INTO orders VALUES (3009, 1713.23, '2000-10-04', 2002, 1003); INSERT INTO orders VALUES (3007, 75.75, '2000-10-04', 2004, 1002); INSERT INTO orders VALUES (3008, 4723.00, '2000-10-05', 2006, 1001); INSERT INTO orders VALUES (3000, 1309.95, '2000-10-06', 2004, 1002); INSERT INTO orders VALUES (3011, 9891.88, '2000-10-06', 2006, 1001); # Check what tables are currently in that database again SHOW TABLES; # Ask MySQL to describe the structure (i.e. schema) of the table DESCRIBE orders; # Show all current records in this table SELECT * FROM orders;