SQL Journey: Blog #1

Began coding using SQL today and found my self finishing the first lesson on Khan Academy.

The following are the instructions for the Project for Lesson 1 which is to design a store database.

Instructions:

Create your own store! Your store should sell one type of things, like clothing or bikes, whatever you want your store to specialize in. You should have a table for all the items in your store, and at least 5 columns for the kind of data you think you'd need to store. You should sell at least 15 items, and use select statements to order your items by price and show at least one statistic about the items.

Here is my code:

CREATE TABLE electronics_store (id INTEGER PRIMARY KEY, item TEXT, quantity INTEGER, price NUMERIC, section TEXT);

INSERT INTO electronics_store VALUES (1, "resistor", 1000, 0.05,"Small items");

INSERT INTO electronics_store VALUES (2, "capacitor", 577, 0.15,"Small items");

INSERT INTO electronics_store VALUES (3, "normally-open pushbuttons", 150, 0.50,"Medium items");

INSERT INTO electronics_store VALUES (4, "normally-closed pushbuttons", 150, 0.50,"Medium items");

INSERT INTO electronics_store VALUES (5, "40W soldering iron", 10, 6,"Large items");

INSERT INTO electronics_store VALUES (6, "multitester", 15, 30,"Large items");

INSERT INTO electronics_store VALUES (7, "Wire stripper", 30, 4,"Medium items");

INSERT INTO electronics_store VALUES (8, "tweezers", 100, 0.75,"Medium items");

INSERT INTO electronics_store VALUES (9, "breadboard", 200, 1.50,"Medium items");

INSERT INTO electronics_store VALUES (10, "555 IC", 150, 0.30,"Small items");

INSERT INTO electronics_store VALUES (11, "12V rechargeable battery", 150, 4,"Large items");

INSERT INTO electronics_store VALUES (12, "22AWG wire", 300, 0.10,"Small items");

INSERT INTO electronics_store VALUES (13, "Piezoelectric Speaker", 150, 0.40,"Small items");

INSERT INTO electronics_store VALUES (14, "red LED", 1000, 0.01,"Small items");

INSERT INTO electronics_store VALUES (15, "Solar charging module for Arduino", 150, 8,"Medium items");

SELECT item, price FROM electronics_store ORDER BY price;

SELECT section, SUM(quantity) FROM electronics_store GROUP BY section;

SELECT item, MAX(quantity) FROM electronics_store;

SELECT item, MIN(quantity) FROM electronics_store;

SELECT item, MAX(price) FROM electronics_store;

SELECT item, MIN(price) FROM electronics_store;

Here are the results: