Section 24 – Lessons 333 to 335 – Complete 2020 Web Development Bootcamp

Lesson 333 – SQL Commands: CREATE Table and INSERT Data

The main things you will be doing with a database are:

  • Create
  • Read
  • Update
  • Destroy

CRUD is the acronym for Create, Read, Update, Destroy.

Squlite.com creates an online playground to create and test SQL databases.

To create a new products table, you can use this SQL code –

CREATE TABLE products (
  id INT NOT NULL,
  name STRING,
  price MONEY,
  PRIMARY KEY (id)
)

To insert data into the products table, you can use this SQL code –

INSERT INTO	products
VALUES (1, "Pen", 1.20)

In order to add values to only the id and name columns of the products table, you can use this code which identifies which columns you will be inserting data into –

INSERT INTO products (id, name)
VALUES (2, "Pencil")

Lesson 334 -SQL Commands READ, SELECT and WHERE

To read everything from a table like the products table, you can use this code –

SELECT * FROM products 

If you only want to read some of the values in the table then instead of using “*”, you can insert the name of each value you want to read followed by a comma. So if you only want to read the name and price from the products table you would use this code –

SELECT name, price FROM products 

If you want to read all of the data relating to just one id, you can use this code –

SELECT * FROM products WHERE id=1

Lesson 335 – Updating Single Values and Adding Columns in SQL

To update a value in the SQL database, you use UPDATE, SET and WHERE statements. The code to update the price of the pencil item in the products table, you can use this code –

UPDATE products
SET price = 0.80
WHERE id=2

In order to update the products table by adding a new column, you have to ALTER the table. The follow SQL code will add a new column called Stock which takes an integer as a value –

ALTER TABLE products
ADD stock INT