{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Week 1 - Enterprise Systems and SQL Action Queries

Week 1 - Enterprise Systems and SQL Action Queries - Action...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Action Queries Why Action Queries? | A Sample Database | DELETE Queries: An Example | Delete Query Pitfalls: An Example | UPDATE Queries: An Example | Update Query Pitfalls | Insert Queries: An Example | Summary Why Action Queries? In BIS-345 you created a lot of SQL SELECT queries. These were highly useful SQL statements that extracted data from a database. These statements organized data and formed the basis for reports. However, these statements had limitations. For example, they could only access existing data, and weren't capable of altering the data stored in the database. There are times when the database user needs to change data in the database by adding to it, deleting it, or making changes to existing data. In these situations, SELECT statements aren't enough -- you need SQL that has capabilities beyond those found in SELECT. These statements are called action queries. Action queries fall into three categories. The first is INSERT queries which add new rows of data to the database. The second is DELETE queries which are capable of deleting specific rows and even all data in a table. The third category is UPDATE queries which simply alter existing data in the database. Each has a specific syntax you'll need to follow to prevent the database management system from producing errors. You can create SQL SELECT and SQL action queries in the query window in Microsoft Access. The remainder of this lecture focuses on how to do this. A Sample Database The entity-relationship diagram below is a sample database we will use to illustrate Delete, Insert, and Update queries. Before reading on, look over the diagram, and get familiar with its tables and columns. Delete Queries: An Example Perhaps the simplest form of action query is the Delete query. The Delete query takes this general form: DELETE FROM [table name] WHERE [column name] = [value] This general form requires you to provide the table from which you're deleting the data and a WHERE clause to identify which rows qualify for deletion. Here is an example problem: Problem Assume you want to delete all individuals from a table called Customer. Customer contains a column called Balance which holds how much money the customer still owes the company. You want to delete only those customers who have a balance that is less than $2.00. In this situation, you would use the following statement: Solution DELETE FROM Customer WHERE Balance < 2 Delete Query Pitfalls The next point is very important to remember -- don't forget your WHERE clause. While DELETE queries are very
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}