SQL Summary MS Access 2023

SQL STATEMENTS CHEATSHEET Order of the Select Statement SELECT TOP < value> DISTINCT < field> FROM < table> WHERE < condition> GROUP BY < field> HAVING < condition> ORDER BY < field> Format of Insert Statement: INSERT INTO < table> (< field>) VALUES < value1>, < value2>, … , < valuen> //Insert a record with the values to the fields listed Format of an Insert with a Select INSERT INTO tblVisits (PrisonerID, VisitorName, VisitLength,VisitDate) SELECT PrisonerID, ‘Joan’, VisitLength, NOW() FROM tblVisits WHERE PrisonerID = 3 AND VisitorName = ‘Susan’ //Use a SELECT statement in place of VALUES. Make sure the fields match the fields in the first line. 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 (Sorting) 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 Use a comma to separate multiple sorting fields NOT AND. 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> 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 < field> IN (< value1>, < value2>, …. ,< value2>) //The records where the field has values in the square brackets. This is the same as WHERE < field> = < value1> OR < field> = < value2> OR … OR < field> = < value2> SELECT < field(s)> FROM < table> WHERE < field> NOT IN (< value1>, < value2>, …. ,< value2>) //The records where the field has NONE of the values in the square brackets SELECT < field(s)> FROM < table> WHERE < field> IS NULL //The records where the field has a NULL value. Do not use =. SELECT TOP < value> < field(s)> FROM < table> WHERE < condition> ORDER BY < fields(s)> //Only the records before and including the limit value are shown. E.g. if LIMIT is 5 you get 5 records. If ORDER BY is not used you get the first records of the table in the order the table was created. To get the top 5 rows, add DESC to ORDER BY < field(s)> SELECT < fields> FROM < table> WHERE < condition> GROUP BY < field> HAVING (< condition1>, < condition2>…) //Having is for the conditions for the new result set – i.e. use WHERE when the field is in the existing table, use HAVING when you have GROUP BY and have created a new field. NB* use only when using GROUP BY SELECT DISTINCT < fields> FROM < table> //Removes all duplicates Embedded Queries SELECT Name, Salary FROM tblTeachers WHERE Salary = (SELECT MAX(Salary) FROM tblTeachers) //Criteria for the WHERE statement is another SQL statement Finding the person with the above average salary SELECT Name, Salary FROM tblTeachers WHERE Salary > (SELECT AVG(Salary) FROM tblTeachers) Finding the person with the highest salary //If you just want the the highest SALARY SELECT MAX( Salary ) FROM tblTeachers //If you want the person who got the highest salary - assuming only ONE person got it SELECT TOP 1 name, salary FROM tblTeachers ORDER BY salary DESC //If you want the person who got the highest salary. You may have more than one person who earns the highest salary SELECT Name, Salary FROM tblTeachers WHERE Salary = (SELECT MAX(Salary) FROM tblTeachers) Format for Word Manipulation Note - all strings numbered from 1 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 3rd and 4th values SELECT LENGTH(< field>) AS < field> FROM < table> //give the length of the field SELECT < value1> & < value2> & < value3> AS < field> FROM < table> //gives the values combined together as one string value SELECT INSTR(< string>,< substring>) AS < field> FROM < table> //gives the position of the first letter of the substring in the string Example: SELECT INSTR(surname,'van') AS PositionVan FROM tblUsers //gives the position of 'van' in a surname. If 'van' is not in the surname 0 will be returned. SELECT INSTR (< number>,< substring>,< string>) AS < field> FROM < table> //gives the position of the first letter of the substring in the string starting the search from position < number> 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 To calculate age accurately use: (NOW() – < DOB>)/365.25 where < DOB> is a field that stores the birth date in your table. 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 < 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 RAND( ) AS < field> FROM < table> //Gives a random number from 0 to 0.9999… SELECT MOD(< value>,< value>) AS < field> FROM < table> //Gives the remainder of the first value after dividing by the second value, same as the % for java SELECT INT(< value>,< value>) AS < field> FROM < table> //Gives the integer value of a number after truncating the decimals. Also FLOOR and CEILING SELECT TRUNCATE(< field manipulated>) FLOOR (7.9 to 7) and CEILING (7.1 to 8) FROM < table> //Gives the value in the same format as the field SELECT FLOOR(RND(< field>) * (NoofOptions)) + StartNum //The field must be a numeric field to seed the random function. SELECT < field> FROM < table> WHERE < field> IN (< value>, < value> …) //Same as saying < field> = < value> OR < field> = < value> … Format for Table Joins 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 SELECT < table>.< field> FROM < table1> LEFT OUTER JOIN < table2> ON < table1>.< field> = < table2>.< field> //Outer join, table 1 contains info and the second has null value To find the fields that are only in table1 but NOT table 2: SELECT < table>.< field> FROM < table1> LEFT OUTER JOIN < table2> ON < table1>.< field> = < table2>.< field> WHERE < table1.field> IS NULL OR SELECT < table1> . < field> FROM < table1> WHERE < table1.field> NOT IN (SELECT < table2>.< field> FROM < table2>) SELECT < table>.< field> FROM < table1> RIGHT OUTER JOIN < table2> ON < table1>.< field> = < table2>.< field> //Outer join, table 2 contains info and the first has null value SELECT < table>.< field> FROM < table1> FULL JOIN < table2> ON < table1>.< field> = < table2>.< field> //Outer join, either table has info and either can have null value