SQL Summary Version 5
SQL Summary Version 5
SQL Summary Version 5
SELECT <field(s)>
FROM <table(s)>
WHERE condition(s)
GROUP BY expression
ORDER BY expression
==============================
ORDER BY – ASC or DESC. ASC is default – smallest first, largest last. A first, Z last.
5 SELECT * FROM tblName ORDER BY price DESC . . . the highest price is first
6 SELECT * FROM tblInventory ORDER BY price, make . . . cheapest to most expensive in alphabetical order by make
1
THE BEST . . . THE 5 BEST . . . THE WORST . . . THE HIGHEST . . . THE MOST (EXPENSIVE) (CHEAPEST) . . . EARLIEST . . . LATEST
7 SELECT TOP 1 * FROM tblName ORDER BY ASC / SELECT TOP 5 * FROM ORDER BY DESC.
10 SELECT TOP 1 lastName, firstName FROM tblPrintLogs WHERE email LIKE “*student*” ORDER BY cost DESC
12 SELECT DISTINCT fields FROM tblName ORDER BY . . . . eg SELECT DISTINCT LastName FROM tblStudents
PERFORM ARITHMETIC ON THE SELECTED FIELDS AND THEN NAME THIS “NEW” FIELD (called an Alias)
13 SELECT fields calculation FROM ORDER BY . . . . eg SELECT Name, (Math + Science + IT) AS Total FROM tblStudents
14 SELECT fields arithmetic function(calculation) . . . . . eg SELECT Name ROUND((Math + IT) /2, 1) AS Average FROM tblStudents
16 SELECT string manipulation (field) & field & field . . . . eg SELECT LEFT(firstName,1)
EXTRACT A PORTION OF A DATE - YEAR, MONTH, DAY FROM A FIELD THAT HAS THE DATA TYPE OF “Date”
2
PERFORMING AGGREGATE FUNCTIONS ON ALL THE VALUES IN A PARTICULAR FIELD. YEILDS A SINGLE VALUE.
19 SELECT fields WHERE condition = < > <= >= <> . . . . eg SELECT surname FROM PrintLogs WHERE TotalPages > 5 . . . .
20 SELECT Name FROM Table WHERE Science > (SELECT AVG (Science) FROM Table ) . . . .
22 SELECT * FROM tblName WHERE ModelNumber LIKE “XC-450??” – wildcard ? for a single character
24 SELECT fields FROM WHERE calculation condition . . . eg SELECT fields WHERE YEAR(NOW()) – YEAR(DOB) <= 30
COMPARING AGES TO MORE THAN ONE CONDITION eg larger than March but smaller than June
25 SELECT fields WHERE condition AND condition . . . eg SELECT fields WHERE MONTH(DOB) BETWEEN 3 AND 6.
3
USING GROUP BY and HAVING with aggregate functions (aggregate function only provide one result unless . . . )
GROUP BY – The result of an aggregate function can offer more information if grouped into categories satisfying a condition
28 SELECT continent, COUNT(countryName) FROM tblWorld GROUP BY continent . . . for number of countries on each continent
29 SELECT continent, COUNT(countryName) FROM tblWorld WHERE population > 20000000 GROUP BY continent
30 SELECT continent, SUM(population) FROM tblWorld GROUP BY continent . . . the total population on each continent
31 SELECT continent, SUM(population) FROM tblWorld GROUP BY continent HAVING SUM(population) > 50000000
Arithmetic function: INT( ), ROUND( ), TRUNCATE( ). Formats the single parameter within the brackets. RND - a single random number.
Comparison operators: < > >= <= <> (not equal to)
String manipulation: LEFT(x), RIGHT(x), MID(x, y), LEN, & - concatenation operator in Ms Access
Date and time: DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE. Used with a field of the data type “date”. NOW( ) yields today from the PC.
Aggregate functions: MAX(), MIN(), AVG(), SUM(), COUNT(). Returns a single result (value) eg it adds all the values in a field when you SUM
They do not return any details from a specific record. Example: If you need maximum and minimum values with details use ORDER BY.
COUNT does not count a record that has a NULL value in the specified field. The other aggregate functions ignore NULL values eg SUM
WHERE TotalPages > AVG(TotalPages) . . . does not work. You cannot compare a value to an aggregate.
WHERE TotalPages > (SELECT AVG(TotalPages) FROM PrintLogs) . . . this works because each statement presents a value.
SELECT make, model, price FROM tblInventory WHERE price < (SELECT AVG(price) FROM tblInventury.) See 14 for another example.
4
Each aggregate function must have its own SELECT statement so that it presents its own value for comparison
WHERE (SELECT AVG(field1) FROM Table1) > WHERE (SELECT AVG(field2) FROM Table1).
Quotes. Regular quotes for string data. Hash symbols for date/time . #2019/05/23 9:33:00#. (Boolean (True) – no quotes)
SQL Summary – Queries that alter data in a table (insert records, delete records or edit existing records) (table is not altered)
32 INSERT – adds a new record to a table and populates all the fields (not when autonumber is the primary key)
NOTE: The VALUES, the order of the values, and the datatypes match the table structure exactly
33 INSERT - adds a new record, specified fields . . . (useful when the first field is autonumber)
INSERT INTO tablename (fieldTitle1, fieldTitle2, fieldTitle3) VALUES (field1Data, field2Data, field2Data)
Eg INSERT INTO tblname (name, DOB, gender, grade, boarder) VALUES (‘Lynn’, #02 Feb 2000#, ‘F’, 10, false)
34 UPDATE – all . . . (the whole table, and all its records are given a new value e.g. the school gets a new name – everybody is affected.
5
35 UPDATE – updates a record that matches a condition
UPDATE tablename SET field1 = value1, field2 = value2, fieldN = valueN WHERE condition
E.g. UPDATE PrintLogs SET FirstName = “Henrietta” WHERE Surname = “Bates” AND FirstName = “Henry”.
36 DELETE – all . . . (Deletes all the records in the table and cannot be undone in Ms Access. The table structure is not affected)
37 DELETE – those that match a condition . . . (NOTE: This delete SQL command cannot be undone Ms Access)
E.g. DELETE FROM tblStudents WHERE studentID = 38 (best to delete using the primary key value, not the person’s name)
1. SELECT *
2. SELECT name, region
3. SELECT name, area/population . . . (area divided by population which gives the population density)
4. SELECT ROUND(area/population, 2) . . . as above rounded to 2 decimal places
5. SELECT LENGTH(name)
6. SELECT name, LEFT(name,1)
7. SELECT name FROM tblWorldStats WHERE population > (SELECT population FROM tblWorldStats WHERE name = “Russia”)
a. Note: A query within a query – the second SELECT must only return one value or the comparison operator cannot work.
8. SELECT INT(genderMale / totalEnrolement * 100) . . . Percentage of male students enrolled in a college rounded down.
9. SELECT ROUND(genderMale / totalEnrolement * 100, 2) . . . Percentage of male students enrolled in a college rounded to 2 decimal places
10. SELECT ROUND(RND(SizeKb) * 5,0) . . . whole random numbers from 1 to 5 inclusive.
6
11. SELECT LastName & “ “ & LEFT(firstName,1) AS LastNameInit . . . last name concatenated with initial.
RESOURCE:
www.sqlzoo.net - useful teach, example and quiz website. Note: Does not focus on Ms Access SQL implementation – small differences.