Building Schema SQL---------------------------------

CREATE TABLE tablename (
field1name int,
field2name varchar(255),
field3name boolean
);

CREATE TABLE Person (
MemberNumber int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
Town varchar(255),
PostCode varchar(255)
);

INSERT INTO Person
VALUES
(0001,'Kenyon','Tom','Home','Stockport','SK5***'),
(0002,'Doe','John','Manchester road','Manchester','SM4***'),
(0003,'Doe','Jane','Willaby road','Worcestershire','MK2***');




SQL Code---------------------------------------------------

Select * from Person // selects all items from the table Person

DELETE FROM Person WHERE MemberNumber = 0002; // deletes the whole record where Member number is equal to 0002


UPDATE Person
SET FirstName = "Beans"
WHERE MemberNumber = 0002;




CREATE TABLE BoardGames (
Name VarChar(255),
MinimumPlayers int,
MaximumPlayers int,
AgeRating VarChar(255),
AverageDuration VarChar(255),
Style VarChar(255),
RetailCost VarChar(255),
YearReleased int,
Version VarChar(255),
Rating VarChar(255)
);

INSERT INTO BoardGames
VALUES
("Wingspan",1,5,"10+","1 hour","Competitive","£64.99",2019,"2nd Edition", "5 stars"),
("Carcassonne",2,5,"7+","40 minutes","Competitive","£24.99",2015,"3rd Edition","4 stars"),
("Horrified",1,5,"10+","1 hour","Cooperative","£40.00",2019,"1st Edition","3 stars");

UPDATE BoardGames
SET RetailCost="£54.99"
WHERE Name = "Wingspan";

UPDATE BoardGames
SET Rating = "4 stars"
WHERE Name = "Horrified";

% = swap out
LIKE % to %
will return anything that has to in the middle and text on either side of it where the percents are
LIKE C%
first letter is C


SELECT GameTitle,Price,Published FROM Games WHERE Notes IS NOT null AND Price > 50 AND Competetive = true AND MaxPlayers >= 4;

SELECT DISTINCT Country FROM Customers; will return every seperate country once

ORDER BY Price; orders it in ascending order 0-9 A-Z
ORDER BY Price DESC; orders in descending order

SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

SELECT TOP 3 * FROM Customers; returns the first 3 from the database

SELECT MIN(Price) returns the smallest
SELECT MAX(Price) returns the largest

SELECT COUNT(*) returns the number of records that match

SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;

SELECT SUM(Quantity) returns the sum of all records that match

SELECT AVG(Price) returns the average of all records that match

WHERE CustomerName LIKE 'a%'; like means similar
The percent sign % represents zero, one, or multiple characters
The underscore sign _ represents one, single character


% Represents zero or more characters
_ Represents a single character
[] Represents any single character within the brackets * SELECT * FROM Customers WHERE CustomerName LIKE '[a-f]%'; returns any that start with a,b,c,d,e,f
^ Represents any character not in the brackets *
- Represents any single character within the specified range *
{} Represents any escaped character **


WHERE Country IN ('Germany', 'France', 'UK'); works like multiple ORs so it will return any with Country = Germany OR France Or UK

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20; returns all where price is less than 20 and more than 10

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName; returns all that are alphabetically between C and M