Week 2 Topic 3: Create tables with SQL and insert data

• SQL (Structured Query Language) is a standard programming language used to create tables in relational databases.
• Many relational databases support basic SQL operations, but some software may have extra features or slight command differences.

CREATE TABLE Syntax

• The CREATE TABLE syntax creates a new table in a database.
• It defines the table's structure, including column names, data types, constraints, and indexes.
• Syntax:

CREATE TABLE table_name (
    column1 datatype [ constraint ],
    column2 datatype [ constraint ],...
    columnN datatype [ constraint ]
);

• Example: Creating a Customer table:

CREATE TABLE Customer (
   CustomerID INTEGER ,
   FirstName TEXT ,
   LastName TEXT ,
   Address TEXT ,
   Phone TEXT , PRIMARY KEY (CustomerID)
);

• When creating a table with a foreign key, ensure the data type matches the primary key in the related table.
• If a table name is a reserved keyword (e.g., ORDER), use an alternative name (e.g., Orders).
• For tables with multiple primary keys, list all primary keys separated by a comma e.g. PRIMARY KEY (OrderID, ProductID)
• To delete a table, use the command: DROP TABLE MyCoolTable;

INSERT INTO Syntax

• The INSERT INTO command inserts data into a table.
• Syntax: INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (value1, value2);
• Example: Inserting data into the Customer table:

INSERT INTO Customer VALUES ( 1 , 'Fred' , 'Banks' , '3134 Hudson Lane' , '405195595' );
INSERT INTO Customer VALUES ( 2 , 'Nicholas' , 'Ramos' , '9 Washington Center' , '946151556' );
INSERT INTO Customer VALUES ( 3 , 'Anne' , 'Hansen' , '8604 Burrows Pass' , '129895116' );
INSERT INTO Customer VALUES ( 4 , 'Douglas' , 'Mendoza' , '9 Monument Avenue' , '898719584' );
INSERT INTO Customer VALUES ( 5 , 'Bonnie' , 'Lawson' , '9 Oneill Avenue' , '887514739' );
INSERT INTO Customer VALUES ( 6 , 'Elizabeth' , 'Ryan' , '1289 Del Mar Street' , '887514733' );

SELECT Command

• SELECT retrieves data from the database.
• SELECT * FROM TableName; selects all data from a table.
• To select specific columns, list the column names after the SELECT command such as SELECT FirstName, Address FROM Customer;

WHERE Keyword

• Filters rows based on a condition.
• Only shows rows where the expression is true.
• SELECT * FROM Customer WHERE LastName = 'Wood'; only selects customers with the last name 'Wood'.
• Mathematical operations can be used in the WHERE clause such as SELECT * FROM Customer WHERE CustomerID > 2;

LIKE Operator

• Checks if text matches a pattern.
• % matches zero or more characters.
• _ matches exactly one character.

IN Operator

• Checks if a value is in a list of possible values.
• SELECT * FROM Customer WHERE CustomerID IN (2,3,4); selects customers with CustomerID 2, 3, or 4.

ORDER BY Keyword

• Displays results in a specific order.
• ASC: ascending order (default).
• DESC: descending order.

AS (Alias) Keyword

• Renames a column.
• SELECT FirstName AS FN, LastName AS LN FROM Customer; renames FirstName to FN and LastName to LN in the output.

LIMIT and OFFSET Clauses

• Restrict the number of rows returned by a SELECT statement.
• LIMIT determines the number of rows returned.
• OFFSET skips a specified number of rows before returning data.
• Often used with ORDER BY.

Aggregate Functions

• Aggregate functions work over multiple rows and combine them.
• COUNT(): Counts all the values.
• SUM(): Adds all the values together.
• AVG(): Computes the average.
• MIN(): Gets the lowest value.
• MAX(): Gets the highest value.

Aggregate - COUNT()

• COUNT() returns the number of rows in the table.
• It always returns a single row unless GROUP BY is used.
• SELECT COUNT (ProductID) FROM Product; returns the number of products.
• COUNT(1) or COUNT(*) counts the number of rows.
• COUNT(ProductID) counts rows where ProductID is not null.

Aggregate - AVG(), MIN(), MAX()

• Example:

SELECT AVG(Price), MIN(Price), Max(Price)
FROM Product;

GROUP BY Keyword

• Used to determine the aggregate price per brand.
• SELECT AVG(Price), BrandID FROM Product GROUP BY BrandID;

WHERE Clause (with Aggregate Functions)

• The WHERE clause is processed before aggregate functions.
• Rows are filtered based on the WHERE clause condition before aggregates are applied.
• Aggregates cannot be used in a WHERE clause.

HAVING Clause

• Similar to WHERE, but it occurs after the aggregates have finished.
• SELECT ProductID,AVG(Price) FROM Product WHERE BrandID < 3 GROUP BY ProductID;
• Only shows brands that have an average price of over $20.

VIEW

• A saved query that works like a regular table, but the rows cannot be edited.
• Useful for large and complex queries.
• CREATE VIEW MyCoolView AS SELECT*FROM Mycool table; creates a view named MyCoolView.
• Example: Creating a view to show the number of orders per CustomerID:

CREATE VIEW OrdersPerCustomer AS
 SELECT CustomerID,COUNT(OrderID)AS OrderCount 
FROM Orders GROUP BY CustomerID;

• After creating a VIEW, it can be used as a standard table, but the rows cannot be changed.
• SELECT * FROM OrdersPerCustomer;