1. Creating a table

Creating a table

CREATE TABLE customer_return (

Product_ID INTEGER,

Product_Description TEXT

);

INSERT INTO customer_return(Product_ID, Product_Description)

VALUES ("0001", "Nike Shoes");

INSERT INTO customer_return(Product_ID, Product_Description)

VALUES ("0002", "Adidas Shoes");

SELECT * FROM customer_return;

Creating a table with NOT NULL Part 1

CREATE table inventory (

Inventory_ID NOT NULL,

Inventory_Name NOT NULL,

Inventory_Desc TEXT

);

INSERT INTO inventory (Inventory_Name, Inventory_Desc) VALUES ("MacBook", "Apple Laptop");

Creating a table with NOT NULL Part 2

CREATE table inventory (

Inventory_ID NOT NULL,

Inventory_Name NOT NULL,

Inventory_Desc TEXT

);

INSERT INTO inventory (Inventory_ID, Inventory_Name, Inventory_Desc) VALUES ("001", "MacBook", "Apple Laptop");

SELECT * FROM inventory;

Creating a table with a "Default" constraint

DROP TABLE IF EXISTS inventory;

CREATE table inventory (

Inventory_ID NOT NULL,

Inventory_Item_Name NOT NULL,

Inventory_Desc TEXT DEFAULT "Refers to Inventory_Item_Name"

);

INSERT INTO inventory (Inventory_ID, Inventory_Item_Name) VALUES ("001", "MacBook");

SELECT * FROM inventory;

Creating a table with a "Unique" constraint Part 1

DROP TABLE IF EXISTS inventory;

CREATE table inventory (

Inventory_ID INTEGER UNIQUE,

Inventory_Item_Name NOT NULL,

Inventory_Desc TEXT DEFAULT "Refers to Inventory_Item_Name"

);

INSERT INTO inventory (Inventory_ID, Inventory_Item_Name) VALUES ("001", "MacBook");

INSERT INTO inventory (Inventory_ID, Inventory_Item_Name) VALUES ("001", "MacBook Pro");

SELECT * FROM inventory;

Creating a table with a "Unique" constraint Part 2

DROP TABLE IF EXISTS inventory;

CREATE table inventory (

Inventory_ID INTEGER UNIQUE,

Inventory_Item_Name NOT NULL,

Inventory_Desc TEXT DEFAULT "Refers to Inventory_Item_Name"

);

INSERT INTO inventory (Inventory_ID, Inventory_Item_Name) VALUES ("001", "MacBook");

INSERT INTO inventory (Inventory_ID, Inventory_Item_Name) VALUES ("002", "MacBook Pro");

SELECT * FROM inventory;

Creating a table with "Unique" and "NOT NULL" constraints

DROP TABLE IF EXISTS inventory;

CREATE table inventory (

Inventory_ID INTEGER UNIQUE NOT NULL,

Inventory_Item_Name NOT NULL,

Inventory_Desc TEXT DEFAULT "Refers to Inventory_Item_Name"

);

INSERT INTO inventory (Inventory_ID, Inventory_Item_Name) VALUES (NULL, "MacBook");

INSERT INTO inventory (Inventory_ID, Inventory_Item_Name) VALUES ("002", "MacBook Pro");

SELECT * FROM inventory;

Creating a table with "Primary" Key

DROP TABLE IF EXISTS inventory;

CREATE table inventory (

Inventory_ID INTEGER PRIMARY KEY,

Inventory_Item_Name NOT NULL,

Inventory_Desc NAME

);

INSERT INTO inventory (Inventory_Item_Name, Inventory_Desc) VALUES ("MacBook", "Apple laptop");

INSERT INTO inventory (Inventory_Item_Name, Inventory_Desc) VALUES ("MacBook Pro", "Apple higher spec laptop");

SELECT * FROM inventory;