Assignment 1 - Shift 3 Level 4 SOD
(Solved)
SQL script containing table creation, sample data, and answers to 20 queries.
1. Create Tables
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
names VARCHAR(100),
gender CHAR(1),
address VARCHAR(100),
age INT
);
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
exp_date DATE,
unit_price DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
customer_id INT,
order_date DATETIME,
quantity INT,
total_price DECIMAL(10,2),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
2. Insert Sample Data
INSERT INTO customers (names, gender, address, age) VALUES
('Cedrick','M','Kigali',29),
('Alice','F','Huye',22),
('Bob','M','Musanze',35),
('Diane','F','Rubavu',28),
('Eric','M','Kigali',40),
('Keza','F','Nyagatare',26),
('Patrick','M','Rusizi',31);
INSERT INTO products (name, exp_date, unit_price) VALUES
('Laptop','2027-01-01',800000),
('Phone','2026-12-01',300000),
('Headphones','2026-06-01',50000),
('Keyboard','2027-03-01',25000),
('Mouse','2026-08-01',15000);
INSERT INTO orders (product_id, customer_id, order_date,
quantity, total_price) VALUES
(1,1,'2026-04-10 [Link]',1,800000),
(2,2,'2026-04-10 [Link]',2,600000),
(3,3,'2026-04-10 [Link]',3,150000),
(4,1,'2026-04-11 [Link]',2,50000),
(5,4,'2026-04-11 [Link]',1,15000),
(2,5,'2026-04-12 [Link]',1,300000),
(3,6,'2026-04-12 [Link]',2,100000),
(1,7,'2026-04-13 [Link]',1,800000);
3. SQL Queries (Answers)
1. SELECT [Link], o.order_date FROM customers c JOIN orders o ON
c.customer_id=o.customer_id;
2. SELECT [Link], [Link], [Link] FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id;
3. SELECT [Link], [Link], o.total_price FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id;
4. SELECT [Link], [Link] FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
[Link]='Kigali';
5. SELECT [Link], [Link] FROM products p JOIN orders o ON
p.product_id=o.product_id;
6. SELECT [Link], [Link], o.order_date FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
o.order_date>'2026-04-11';
7. SELECT [Link], o.order_id FROM customers c JOIN orders o ON
c.customer_id=o.customer_id WHERE [Link]>1;
8. SELECT [Link], o.total_price FROM customers c JOIN orders o ON
c.customer_id=o.customer_id WHERE o.total_price>100000;
9. SELECT [Link], o.total_price FROM orders o JOIN products p ON
o.product_id=p.product_id WHERE o.customer_id=1;
10. SELECT [Link], [Link] FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
p.unit_price<100000;
11. SELECT [Link], SUM(o.total_price) AS total_orders FROM customers c JOIN orders o
ON c.customer_id=o.customer_id GROUP BY [Link];
12. SELECT [Link], [Link], [Link] FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
[Link] BETWEEN 1 AND 3;
13. SELECT [Link], [Link], o.order_date FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id ORDER BY
o.order_date;
14. SELECT [Link], [Link], o.total_price FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
o.total_price IS NOT NULL;
15. SELECT [Link], [Link] FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
[Link]='Phone';
16. SELECT [Link], [Link], o.total_price FROM customers c JOIN orders o ON
c.customer_id=o.customer_id WHERE [Link]>25;
17. SELECT [Link], [Link], o.total_price FROM products p JOIN orders o ON
p.product_id=o.product_id WHERE [Link] IS NOT NULL;
18. SELECT [Link], [Link] FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
DATE(o.order_date)=CURDATE();
19. SELECT [Link], [Link], o.total_price FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
[Link] LIKE 'K%';
20. SELECT [Link], [Link], o.order_date FROM customers c JOIN orders o ON
c.customer_id=o.customer_id JOIN products p ON o.product_id=p.product_id WHERE
o.total_price BETWEEN 50000 AND 300000;