In this project we will cover the basics of Structure Query Language (SQL), Relational Databases, and Knex.js Queries.

• Introduction to Relational Databases, SQL and Knex
  • Prerequisites
  • Project Setup
  • Handy Query Notes & examples

• SQLite Studio Installed.
• This Web SQL Tool Loaded in a chrome or chromium browser tab. It does not work well on Firefox.

• clone this repository.
• move into the project folder.
• type npm i to download dependencies.

W3Schools.com

• SELECT (lastname || ',' || firstname ) AS FullName from employees;

• SELECT (Address || ',' || ' ' || City || ',' || ' ' || PostalCode || ',' || ' ' || Country) AS FullAddress FROM Customers;

• SELECT * FROM Customers WHERE Country = 'Spain';

• SELECT * FROM Customers WHERE Country LIKE 'Spain';

• SELECT * FROM Customers WHERE Country LIKE '%land';

• SELECT * FROM Customers WHERE Country not LIKE '%land';

• SELECT * FROM Employees WHERE Notes not LIKE '%university%';

• SELECT * FROM Products WHERE CategoryID = 1;

• SELECT * FROM Orders WHERE OrderDate >'1997-01-01';

• SELECT * FROM Orders WHERE OrderDate >'1997-01-01' ORDER BY OrderDate DESC;

• SELECT * FROM Orders WHERE OrderDate >'1997-01-01' ORDER BY OrderDate ASC, customerID ASC;

• 7 SELECT * FROM Products ORDER BY CategoryID ASC, Price DESC;

• INSERT INTO Shippers (ShipperName, Phone) VALUES ('blah', '(123) 456-7890');

• 8 INSERT INTO Shippers (ShipperName) VALUES ('UPS'); -- You have made changes to the database. Rows affected: 1 was the response and the empty phone field has null in it now

• UPDATE Shippers SET ShipperName = 'United Parcel Service' WHERE ShipperID = 4;

• 9 UPDATE Shippers SET ShipperName = 'USPO', Phone = '(123) 456-7890' WHERE ShipperID = 4;

• 10 DELETE FROM Shippers WHERE ShipperID = 5; --note ID's are not recycled