I keep on getting 1146 Error Code: 1146. Table 'world.itemsintransactions' doesn't exist, and before I kept on getting many other diffferent error codes that I eventually fix only to move onto another error code. Please bear in mind this is my first MySQL assignment, never used MySQL before this assignment, so apologies if I have missed out on something very obvious!
Here is my code...
Create DATABASE CS_Store;
USE CS_Store;
CREATE TABLE customers (
birth_day date,
first_name VARCHAR(20),
last_name VARCHAR(20),
c_id VARCHAR(20),
CONSTRAINT PK_Customers PRIMARY KEY (c_id));
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1993-07-11','Victor','Davis',1);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('2001-03-28','Katarina','Williams',2);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1965-12-11','David','Jones',3);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1980-10-10','Evelyn','Lee',4);
CREATE TABLE employees (
birth_day date,
first_name VARCHAR(20),
last_name VARCHAR(20),
e_id int,
CONSTRAINT PK_Employees PRIMARY KEY (e_id)
);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1983-09-02','David','Smith',1);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1990-07-23','Olivia','Brown',2);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1973-05-11','David','Johnson',3);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1999-11-21','Mia','Taylor',4);
CREATE TABLE transactions (
e_id int,
c_id int,
date date,
t_id int,
CONSTRAINT PK_transactions PRIMARY KEY (t_id),
FOREIGN KEY (e_id) REFERENCES employees(e_id),
FOREIGN KEY (c_id) REFERENCES customers(c_id)
);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,1,'2020-8-11',1);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (3,1,'2020-8-15',2);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,4,'2020-9-01',3);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (2,2,'2020-9-07',4);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (4,3,'2020-9-07',5);
CREATE TABLE items (
amount int,
price_for_each int,
name VARCHAR(20),
CONSTRAINT PK_items PRIMARY KEY (name)
);
INSERT INTO items (price_for_each, amount, name) VALUES (110,22,'2lof milk');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'6 cans of lemonade');
INSERT INTO items (price_for_each, amount, name) VALUES (150,20,'Pack of butter');
INSERT INTO items (price_for_each, amount, name) VALUES (450,13,'Roast chicken');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'Pack of rice');
INSERT INTO items (price_for_each, amount, name) VALUES (20,50,'Banana');
INSERT INTO items (price_for_each, amount, name) VALUES (200,30,'3kg sugar');
INSERT INTO items (price_for_each, amount, name) VALUES (150,15,'Toast bread');
INSERT INTO items (price_for_each, amount, name) VALUES (150,18,'Earl Grey tea');
CREATE TABLE promotions (
discount int,
p_id int,
CONSTRAINT PK_promotions PRIMARY KEY (p_id)
);
INSERT INTO promotions (discount, p_id) VALUES (99,1);
INSERT INTO promotions (discount, p_id) VALUES (200,2);
INSERT INTO promotions (discount, p_id) VALUES (150,3);
INSERT INTO promotions (discount, p_id) VALUES (150,4);
CREATE TABLE itemsinpromotions (
name VARCHAR(20),
p_id int,
amount int,
FOREIGN KEY (name) REFERENCES items(name),
FOREIGN KEY (p_id) REFERENCES promotions(p_id)
);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('6 cans of lemonade',1,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Roast chicken',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Toast bread',3,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('2lof milk',4,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Banana',4,3);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('3kg sugar',4,2);
CREATE TABLE itemsintransactions (
name VARCHAR(20),
t_id int,
amount int,
FOREIGN KEY (name) REFERENCES items(name),
FOREIGN KEY (t_id) REFERENCES transactions(t_id)
);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES('6 cans of lemonade',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',1,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',1,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',2,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',3,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',3,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',4,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',4,20);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',4,8);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Earl Grey tea',5,10);
CREATE VIEW DavidSoldTo AS
SELECT birth_day, first_name, last_name
FROM customers
WHERE EXISTS (SELECT 1 FROM Transactions WHERE e_id=1)
ORDER BY birth_day
;
CREATE VIEW PeopleInShop AS
SELECT DISTINCT birth_day, first_name, last_name
FROM Employees
UNION
SELECT DISTINCT birth_day, first_name, last_name
FROM Customers
WHERE EXISTS (SELECT 1 FROM Transactions WHERE date=2020-9-07)
ORDER BY birth_day
;
CREATE VIEW ItemsLeft AS
SELECT amount AS amount_left, name
FROM Items
WHERE amount_left NOT IN (SELECT COUNT(t_id) FROM Transactions)
GROUP BY name
;
-- Not yet completed
CREATE VIEW PromotionItemsSatisfiedByTransactions AS
SELECT name, p_id, COUNT(t_id) AS number_of_times
FROM Promotions NATURAL JOIN Transactions
SELECT number_of_times
WHERE number_of_times >= 1;c_id
Apart from the error related to the schema name, that was explained by @ysth, there is another issue with your code. Consider these table declarations:
create table customers (
birth_day date,
first_name varchar(20),
last_name varchar(20),
c_id varchar(20),
constraint pk_customers primary key (c_id)
);
create table transactions (
e_id int,
c_id int,
date date,
t_id int,
constraint pk_transactions primary key (t_id),
foreign key (e_id) references employees(e_id),
foreign key (c_id) references customers(c_id)
);
The problem is that customers(c_id) is of datatype varchar(20), while referencing column transactions(c_id) is int. Because of that, the foreign key constraint creation fails, as does the creation of table transactions. The missing table propagates error through out the rest of the script.
Fix one of the datatype so they are identical. I would expect something called ..._id to be int. Then, the whole script should just work.