Format of insert statement:
INSERT INTO <table> (<field>)
VALUES( <value1>, <value2>, … , <valuen> )
//Insert a record with the values to the fields listed
Format of delete statement: (use with caution)
DELETE FROM <table>
WHERE <condition> AND/OR <condition>
//Delete the record with the field that fits the condition
Updating Data in a Table:
UPDATE <table>
SET <field> = <new value>
WHERE <field> = <existing value>
//Change the value in the fields of the record that fits the condition to the new value
Format of order statement
SELECT <field(s)>
FROM <table>
ORDER BY <field1>, … , <fieldn>
//Order the records alphabetically by the first field then the next field
SELECT <field(s)>
FROM <table>
ORDER BY <field1> DESC, … , <fieldn> DESC
//Order the records alphabetically in descending order by the first field then the next field
Selecting Data in a Table
SELECT <field(s)>
FROM <table>
//Limit the amount of fields on the table
SELECT *
FROM <table>
//Show all fields of the table
Format to see specific records
SELECT <field(s)>
FROM <table>
WHERE <condition>// Show the records that fit the condition
SELECT <field(s)>
FROM <table>
WHERE <condition> LIKE <%pattern>
//The records where the pattern exists at the end of the value
LIKE <pattern%>
//The records where the pattern exists at the beginning of the value
LIKE <%pattern%>
//The records where the pattern exists anywhere in the value
SELECT <field(s)>
FROM <table>
WHERE <condition> AND <condition>
//The records where both the conditions are true
SELECT <field(s)>
FROM <table>
WHERE <condition> OR <condition>
//The records where either of the conditions are true
SELECT <field(s)>
FROM <table>
WHERE <field> BETWEEN <value1> AND <value2>
//The records where the field has values between the two values specified
SELECT <field(s)>
FROM <table>
WHERE <condition>
LIMIT <value>
//Only the records before the limit value are shown
SELECT <fields>
FROM <table>
WHERE <condition>
GROUP BY <field>
HAVING (<condition1>, <condition2>…)
//Having is for the conditions for the new result set
NB* use only when using GROUP BY or aggregate funtion
SELECT DISTINCT <fields>
FROM <table>
//Removes all duplicates
Format for word manipulation
SELECT LEFT(<field>,<value>) AS <field>
FROM <table>
//characters from left until the value
SELECT RIGHT(<field> , <value>) AS <field>
FROM <table>
//all characters from right until the value from right
SELECT MID(<field>, <value1>, <value2>) AS <field>
FROM <table>
//all characters from value 1 until the amount of value 2 e.g. (3,2) will give you the 3 rd and 4th values
SELECT CONCAT(<value1>,<value2>,<value3>) AS <field>
FROM <table>
//gives the values combined together as one value
Format for date manipulation
SELECT MONTH(<field>) AS <field>
FROM <table>
//gives the month of the date
SELECT DAY(<field>) AS <field>
FROM <table>
//gives the day of the date
SELECT YEAR(<field>) AS <field>
FROM <table> //gives the year of the date
SELECT NOW() AS <field>
FROM <table>
//gives the current date from the computer clock
Finding the difference between 2 dates
DateDiff ("yyyy", #15/10/1998#, #22/11/2003#)
Result: 5 - result in years
DateDiff ("m", #15/10/2003#, #22/11/2003#)
Result: 1 – result in months
DateDiff ("d", #15/10/2003#, #22/11/2003#)
Result: 38 – result in days
CDate
The function called "CDate" will convert any value to a date as long as the expression is a valid
date
Example finding the ave(time field) will give a decimal answer
Cdate(ave(time field) will give you a result in time format.
This can be done instead of the format function
This can be used on dates as well. It does the formatting for you.
Format for number manipulation
SELECT AVG(<field>) AS <field>
FROM <table>
//gives the average of the records for that field
SELECT COUNT(*)
FROM <table>
WHERE <condition>
//Counts the amount of records in the table with the condition
SELECT MAX(<field>) AS <field>
FROM <table>
//Show the record with the biggest value in the field
SELECT MIN(<fields>) AS <field>
FROM <table>
//Show the record with the smallest value in the field
SELECT SUM(<field>) AS <field>
FROM <table>
//Show total sum of the records in the field
SELECT [Price] Mod 3 ASRemainder
FROM Guitars;
Shows the remainder of the price field divided by 3
SELECT <field>, COUNT(*) AS <field>
FROM <table>
WHERE <condition>
GROUP BY <field>
//Show the amount of records in every field in the group by condition
SELECT ROUND(<field>,<value>) AS <field>
FROM <table>
//Rounds the field off to the “valueth” decimal place e.g. (3.456,1) => 3.5
SELECT RND( ) AS <field>
FROM <table>
//Gives a random number from 0 to 0.9999…
Select a random record from a table
SELECT Top 1 <fields>
FROM <tableName>
Order by rnd <field> - field must be of type number
SELECT top 1 Passengers.Fullname, Passengers.Flight, Passengers.Destination,
Passengers.PassengerID
FROM Passengers
order by rnd(passengerId)
Add a random number after a field
SELECT [Brand] & int(Rnd()*10) AS BrandWithNumber
FROM Guitars;
SELECT MOD(<value>,<value>) AS <field>
FROM <table>
It however gives the same random number to all the records. To get a different number add any field of
datatype number to the statement.
SELECT [Brand] & int(Rnd(itemcode)*10) AS BrandWithRandom
FROM Guitars;
Select Int ((6 - 1 + 1) * Rnd + 1) would return a random number between 1 and 6
Select Int ((200 - 150 + 1) * Rnd + would return a random number between 150 and 200
150)
Pseudo random dates
SELECT FORMAT( 0 , "Short Date") = 1899/12/30
So just adding a random amount to the first parameter gets you a pseudo random date
From the Jeppe database
SELECT format(rnd(raceDate),"short date") AS Expr1
FROM tblRace;
Random character in a String
Random Characters/Words?
SELECT MID(“ABCDE”, INT(RND() * 5) + 1 , 1)
From the Jeppe database
SELECT Mid(name,Int(Rnd()*5)+1,1) AS Expr1 from tblrunner
Formatting the result of a Query
SELECT FORMAT(column_name,format) FROM table_name;
Eg.
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products;
To format time
Select format(date(),”HH:MM:SS”))
Format to 2 decimal
SELECT Guitars.Brand, Guitars.Series, Format(Guitars.Price,'0000.00') AS formatedPrice
FROM Guitars;
Format to currency with 2 decimals
SELECT Guitars.Brand, Guitars.Series, Format(Guitars.Price,'\R0000.00') AS formatedPrice
FROM Guitars;
Format average of time
SELECT format(Avg(tblResult.Time1),"short time") AS AvgOfTime1
FROM tblResult;
OR
SELECT format(Avg(tblResult.Time1),"Long Date") AS AvgOfTime1
FROM tblResult;
OR
SELECT format(Avg(tblResult.Time1),"hh:mm:ss") AS AvgOfTime1
FROM tblResult;
//Gives the remainder of the first value after dividing by the second value, same as the % for java
SELECT TRUNCATE(<field manipulated>)
FROM <table>
//Gives the value in the same format as the field
SELECT <field>
FROM <table>
WHERE <field> IN (<value>, <value> …)
SELECT *
FROM products
WHERE product_name NOT IN ('Pear', 'Banana', 'Bread');
//Same as saying <field> = <value> OR <field> = <value> …
SELECT column_name(s)
FROM table_name
WHERE column_name IN (select …);
SELECT Id, FirstName, LastName, Country
FROM Customer
WHERE Country IN
(SELECT Country
FROM Supplier)
Format for table joins
Types of Joins
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns
in both tables.
SELECT <table>.<field>
FROM <table1>, <table2>
WHERE <table1>.<field> = <table2>.<field>
//Inner join by just using where conditions
SELECT <table>.<field>
FROM <table1>
INNER JOIN <table2> ON <table1>.<field> = <table2>.<field>
//Inner join with full format and the order of the tables do not matter
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right
table (table2). The result is NULL in the right side when there is no match.
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left
table (table1). The result is NULL in the left side when there is no match.
Order of the select
SELECT DISTINCT <field>
FROM <table>
WHERE <condition>
GROUP BY <field>
HAVING <condition>
ORDER BY <field>
LIMIT <value>
Selecting using the result of another select
SELECT itemDescription, itemCategory, itemCostPrice
FROM tblItems
WHERE (((itemCostPrice)<(SELECT Avg(itemCostPrice) FROM tblItems)));
From the whos got Talent database
SELECT tblActs.ActName, tblActs.Category, tblActs.SponsorshipAmount
FROM tblActs
WHERE (((tblActs.SponsorshipAmount)>(SELECT Avg(tblActs.SponsorshipAmount) AS
AvgOfSponsorshipAmount FROM tblActs)));
SELECT tblActs.ActName, tblActs.Category, ([judge1]+[judge2]+[judge3])/3 AS FinalScore
FROM tblActs INNER JOIN tblPoints ON tblActs.ActNum = tblPoints.ActNum
WHERE (((([judge1]+[judge2]+[judge3])/3)>(SELECT Avg(([judge1]+[judge2]+[judge3])/3) FROM tblPoints)));
INSERT using SELECT
Copy only a few columns from "Suppliers" into "Customers":
Example
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;
Example
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';
IF field not in the table that you are selecting from you need to specify the value
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, ‘new York’ FROM Suppliers
WHERE Country='Germany';
update tblPoints
set judge3 = 6 where actnum=(select actnum from tblActs where actname="wey-wey")
insert into tblPoints (actnum, judge1, judge2, judge3)
select actnum, 5,5,5 from tblActs where actname="Delly-H"
Updating a table using results of another table
In the jeppe database if Bev Browne changes schools.
UPDATE tblRunner INNER JOIN tblSchool ON tblRunner.SchoolID = tblSchool.SchoolID SET
tblRunner.SchoolID = [tblSchool].[SchoolID]
WHERE (((tblRunner.Name)="Bev Browne") AND ((tblSchool.Name)="B"));
Altering a table
• ALTER TABLE <table>
ADD COLUMN <newfield> <type>;
• ALTER TABLE <table>
ALTER COLUMN <oldfield> <type>;
• ALTER TABLE <table>
DROP COLUMN <oldfield>;
Example
ALTER TABLE pets
ADD COLUMN favouriteToy TEXT(10);
ALTER TABLE pets
ALTER COLUMN favouriteToy INTEGER;
ALTER TABLE pets
DROP COLUMN favouriteToy;
Getting rid of a table
DROP TABLE <table>;
DROP TABLE petToys;
Creating table with a primary key
CREATE TABLE tblStudents(
SCode VARCHAR (9) NOT NULL,
SSName VARCHAR (40) NOT NULL,
SFName VARCHAR (40) NOT NULL,
BDate DATE,
AcYear INTEGER NOT NULL,
DP YESNO not null,
PRIMARY KEY (Scode)
);
Table created but not linked to tblStudents
CREATE TABLE tblMarks(
id INTEGER NOT NULL,
SCode VARCHAR (9) NOT NULL,
SSCode VARCHAR (9) NOT NULL,
E1 INTEGER NOT NULL,
E2 INTEGER NOT NULL,
PRIMARY KEY (id)
);
Create table with foreign Key
CREATE TABLE tblMarks(
id INTEGER NOT NULL,
SCode VARCHAR (9) NOT NULL,
SSCode VARCHAR (9) NOT NULL,
E1 INTEGER NOT NULL,
E2 INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (SCode) REFERENCES tblStudents(SCode)
);
Reg_No AUTOINCREMENT – for an auto number field
x double not null – for a double field
