Coding In SQL

1. Coding In SQL

2. Structure Query Language • Common query language used in database management systems • In Access • can view SQL code by going into SQL view • can build queries in SQL View mode

3. Common SQL Keywords • Select • From • Where • Group By • Having • Order By

4. Select • Lists the fields that you want to display in the query • Fields must contain the table name and the field name (Employee.EmpLast)

5. From • Lists the table or tables involved in the query • Separate table names with commas

6. Where • Lists the criteria that apply • Where Employee.EmpLast=“Jones”

7. Group By • Groups records with identical values in the specified fields (usually to calculate a summary statistic) • Group by Employee.JobID

8. Having • List the conditions for selecting grouped records, connected by AND, OR, or NOT • Having (Employee.JobID) = 3 OR (Employee.JobID) = 4

9. Order By • Specify sorting specifications • Order By Employee.JobID DESC

10. Sample SQL Query • Give list of employees with a JobID of 3 Select Employee.EmpFirst, Employee.EmpLast From Employee Where Employee.JobID = 3 Order By Employee.EmpLast;

11. Results

12. SQL Queries with Several Tables • Use the words INNER JOIN, RIGHT JOIN, or LEFT JOIN • To show the link (inner join) between two tables, use this code: FROM Employee INNER JOIN EmployeeTraining ON Employee.EmpID = EmployeeTraining.EmpID

13. Sample Query • Give a list of employee training classes taken, the date, and the employee who took the class (sort in alphabetic order by last name) • SELECT Employee.EmpFirst, Employee.EmpLast, EmployeeTraining.ClassID, EmployeeTraining.Date • FROM Employee INNER JOIN EmployeeTraining ON Employee.EmpID = EmployeeTraining.EmpID • ORDER BY Employee.EmpLast;

14. Results