Coding In SQL
Coding In SQL. 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. Common SQL Keywords. Select From Where Group By Having Order By. Select.
Coding In SQL
E N D
Presentation Transcript
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
Common SQL Keywords • Select • From • Where • Group By • Having • Order By
Select • Lists the fields that you want to display in the query • Fields must contain the table name and the field name (Employee.EmpLast)
From • Lists the table or tables involved in the query • Separate table names with commas
Where • Lists the criteria that apply • Where Employee.EmpLast=“Jones”
Group By • Groups records with identical values in the specified fields (usually to calculate a summary statistic) • Group by Employee.JobID
Having • List the conditions for selecting grouped records, connected by AND, OR, or NOT • Having (Employee.JobID) = 3 OR (Employee.JobID) = 4
Order By • Specify sorting specifications • Order By Employee.JobID DESC
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;
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
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;
