Database Design and Implementation
Database Design and Implementation ISM 4212
University of Central Florida
Popular in Course
Popular in Information Systems Management
This 6 page Class Notes was uploaded by Marco Moen on Thursday October 22, 2015. The Class Notes belongs to ISM 4212 at University of Central Florida taught by Staff in Fall. Since its upload, it has received 24 views. For similar materials see /class/227446/ism-4212-university-of-central-florida in Information Systems Management at University of Central Florida.
Reviews for Database Design and Implementation
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 10/22/15
ISM 4212 7142010 Handout 4 More MSACCESS Help Selections Page 1 of6 SELECT Statement Instructs the Microso Jet database engine to return information from the database as a set of records Syntax SELECT predicale table table eld1 AS aliaxI table eld2 AS a1ias2 FROM tableexpression IN externaldafabase WHERE GROUP BY HIAVING ORDER BY WITH OWNERACCESS OPTION The SELECT statement has these parts Part Description predicate One of the following predicates ALL DISTINCT DISTINCTROW or TOP You use the predicate to restrict the number of records returned If none is speci ed the default is ALL Specifies that all elds from the speci ed table or tables are selected table The name of the table containing the elds from which records are selected eld1 eld2 The names of the fields containing the data you want to retrieve If you include more than one field they are retrieved in the order listed aliaxI alias2 The names to use as column headers instead of the original column names in table tableexprem39on The name of the table or tables containing the data you want to retrieve extemaldarabase The name of the database containing the tables in tableexprem on if they are not in the current database Remarks To perform this operation the Microso Jet database engine searches the specified table or tables extracts the chosen columns selects rows that meet the criterion and sorts or groups the resulting rows into the order speci e SELECT statements do not change data in the database SELECT is usually the rst word in an SQL statement Most SQL statements are either SELECT or SELECTINTO statements The minimum syntax for a SELECT statement is SELECT elds FROM table You can use an asterisk to select all elds in a table The following example selects all of the fields in the Employees table SELECT FROM Employees If a eld name is included in more than one table in the FROM clause precede it with the table name and the dot operator In the following example the Department field is in both the Employees table and the Supervisors table The SQL statement selects departments from the r 39 name 11 um L r 39 SELECT EmployeesDepartment SupervisorsSupVName FROM Employees INNER JOIN Supervisors WHERE EmployeesDepartment SupervisorsDepartment When a Recordset object is created the Microso Jet database engine uses the table39s eld name as the Field object name in the Recordset object If you want a different field name or a name is not implied by the expression used to generate the field use the AS reserved word The following example uses the title Birth to name the returned Field object in the resulting Recordset object ISM 4212 7142010 Handout 4 More MSACCESS Help Selections Page 2 of6 SELECT BirthDate AS Birth FROM Employees Whenever you use aggregate mctions or queries that return ambiguous or duplicate Field object names you must use the AS clause to provide an alternate name for the Field object The following example uses the title HeadCount to name the returned Field object in the resulting Recordset object SELECT COUNTEmployeeID AS HeadCount FROM Employees You can use the other clauses in a SELECT statement to irther restrict and organize your returned data For more information see the Help topic for the clause you are using See Also ALL DISTINCT DISTINCTROW TOP Predicates ORDER BY Clause Iicrosoft Jet SQL icroso Jet S L DELETE Statement Iicrosoft Jet SQL SELECTINTO Statement Iicrosoft Jet SQL FROM Clause Iicrosoft Jet SQL SQL Aggregate Functions SQL GROUP BY Clause Microso Jet SQL UNION Operation Iicroso Jet SQL HAVING Clause Microso Jet SQL UPDA39IE Statement Iicroso Jet SQL IN Clause Iicroso Jet SQL WHERE Clause Microso Jet SQL INSERT INTO Statement Microso Jet SQL WITH OWNERACCESS OPTION Declaration Microso Jet SQL Example SELECT Statement FROM Clause Example WHERE Clause Specifies which records from the tables listed in the M clause are affected by a SELECT UPDATE or DELETE statement Syntax SELECT eldlm ROM tableexpression WHERE criteria A SELECT statement containing a WHERE clause has these parts Part Description eldlixt The name ofthe eld or fields to be retrieved along with any fieldname aliases selection predicates ALL DISTINCT DISTINCTROW or TOP or other SELECT statement options tableexprem39on The name of the table or tables from which data is retrieved criteria An expression that records must satisfy to be included in the query results Remarks The Microsoft Jet database engine selects the records that meet the conditions listed in the WHERE clause If you do not specify a WHERE clause your query returns all rows from the table Ifyou specify more than one table in your query and you have not included a WHERE clause or a JOIN clause your query generates a Cartesian product of the tables WHERE is optional but when included follows FROM For example you can select all employees in the sales department WHERE Dept 39 Sales 39 or all customers between the ages of18 and 30 WHERE Age Between 18 And 30 ISM 4212 7142010 Handout 4 More MSACCESS Help Selections Page 3 of6 If you do not use a JOIN clause to perform SQL join operations on multiple tables the resulting Recordset object will not be updatable WHERE is similar to HAVING WHERE determines which records are selected Similarly once records are grouped with GROUP HAVING determines which records are displayed Use the WHERE clause to eliminate records you do not want grouped by a GROUP BY clause Use various expressions to determine which records the SQL statement retums For example the following SQL statement selects all employees whose salaries are more than 21000 SELECT LastName Salary EROM Emp loye es WHERE Salary gt 21000 AWHERE clause can contain up to 40 expressions linked by logical operators such as And and Or When you enter a eld name that contains a space or punctuation surround the name with brackets D For example a customer information table might include information about speci c customers SELECT Customer s Favorite Restarant When you specify the criteria argument date literals must be in US format even if you are not using the US version ofthe Microsoft Jet database engine For example May 10 1996 is written 10596 in the United Kingdom and 51096 in the United States Be sure to enclose your date literals with the number sign as shown in the following examples To find records dated May 10 1996 in aUnited Kingdom database you must use the following SQL statement SE LEC T FROM 0 rde rs WHERE ShippedDate 510 9 61 You can also use the DateValue Jnction which is aware of the international settings established by Microso Windows For example use this code for the United States SELECT EROM Orders WHERE ShippedDate DateValue 395109639 y39 And use this code for the United Kingdom SELECT EROM Orders WHERE ShippedDate DateValue 391059639 Note If the column referenced in the criteria string is of type GUID the criteria expression uses a slightly different syntax ISM 4212 7142010 Handout 4 More MSACCESS Help Selections Page 4 of6 WHERE ReplicaID GUID 1234567890ABCDEF1234567890ABCDEF Be sure to include the nested braces and hyphens as shown See Also ALL DISTINCT DISTINCTROW TOP Predicates LEFT JOIN RIGHT JOIN C 39 quot Jet OIJ Iicroso Jet SQL DELETE Statement Iicrosof t Jet SQL ORDER BY Clause Microso Jet SQL FROM Clause Iicrosof t Jet SQL SELECT Statement Microsoft Jet SQL GROUP BY Clause Microso Jet SQL SELECT INTO Statement Microso Jet S L HAVING Clause Microso Jet SQL SQL Aggregate Functions SQL IN Clause icroso Jet S L UPDATE Statement Iicrosof t Jet SQL INNER JOIN Operation Iicrosof t Jet SQL Example WHERE Clause Example UPDATE Statement Creates an update gue that changes values in elds in a speci ed table based on specified criteria Syntax UPDATE table SET newvalue WHERE criteria The UPDATE statement has these parts Part Description table e name of the table containing the data you Want to modify newvalue An expression that determines the value to be inserted into a particular field in the updated records criteria An expression that determines Which records Will be updated Only records that satisfy the expression are updated Remarks UPDATE is especially useful when you Want to change many records or When the records that you Want to change are in multiple tables u can change several fields at the same time The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom UPDATE Orders SET OrderAmount OrderAmount 11 Freight Freight 103 ISM 4212 7142010 Handout 4 More MSACCESS Help Selections Page 5 of6 WHERE ShipCountry 39UK39 Important I UPDATE does not generate a result set Also after you update records using an update query you cannot undo the operation If you want to know which records were updated rst examine the results of a select gue that uses the same criteria and then run the update query I Maintain backup copies of your data at all times If you update the wrong records you can retrieve them from your backup 39 s See Also SELECT Statement Microso Jet SQL WHERE Clause Microso Jet SQL Example UPDATE Statement Example INSERT INTO Statement Adds a record or multiple records to a table This is referred to as an append gue Syntax Multiplerecord append query INSERT INTO target eld1 eld2 IN externaldafabase SELECT soume eld1 eld2 FROM tableexpresrion Singlerecord append query INSERT INTO target eld1 eld2 VALUES valueI valueZL The INSERT INTO statement has these parts Part Description target The name of the table or query to append records to eld1 eld2 Names ofthe elds to append data to if following a target argument or the names of elds to obtain data from if following a roume argument extemaldarabare The path to an external database For a description of the path see the m clause rource The name of the table or query to copy records rom tableexprem39on The name of the table or tables from which records are inserted This argument can be a single table name or a compound resulting from an INNER JOIN LEFT JOIN or RIGHT JOIN operation or a saved query valueva1u22 The values to insert into the speci c elds of the new record Each value is inserted into the eld that corresponds to the values position in the list value is inserted into eld of the new record valueZ into ele and so on You must separate values with a comma and enclose text elds in ISM 4212 7142010 Handout 4 More MSACCESS Help Selections Page 6 of6 quotation marks 39 39 Remarks You can use the INSERT INTO statement to add a single record to a table using the singlerecord append query syntax as shown above In this case your code speci es the name and value for each eld ofthe record You must specify each of the fields of the record that a value is to be assigned to and a value for that field When you do not specify each field the default value or m is inserted for missing columns Records are added to the end of the table You can also use INSERT INTO to append a set of records from another table or query by using the SELECT FROM clause as shown above in the multiplerecord append query syntax In this case the SELECT clause specifies the fields to append to the speci ed target table The rource or target table may specify a table or a query If a query is specified the Microso Jet database engine appends records to any and all tables specified by the query INSERT INTO is optional but when included precedes the SELECT statement If your destination table contains a primary key make sure you append unique nonNull values to the primary key field or elds if you do not theMirrn n Ietd k 39 yy lnnt H If you append records to a table with an AutoNumber field and you want to renumber the appended records do not include the AutoNumber eld in your query Do include the AutoNumber field in the query if you want to retain the original values from the eld Use the IN clause to append records to a table in another database To create anew table II P the FT FCT INTO statement 39 A t b To find out which records will be appended before you run the append query rst execute and view the results of a select query that uses the same selection criteria An append query copies records from one or more tables to another The tables that contain the records you append are not affected by the append query Instead of appending existing records from another table you can specify the value for each field in a single new record using the VALUES clause If you omit the eld list the VALUES clause must include a value for every eld in the table otherwise the INSERT operation will fail Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create See Also FROM Clause Iicrosoft Jet SQL SELECT Statement Iicrosoft Jet SQL IN Clause Iicroso Jet SQL SELECTINTO Statement Iicrosoft Jet SQL INNER JOIN Operation Iicrosoft Jet SQL WHERE Clause Microso Jet SQL LEFT JOIN RIGHT JOIN C Tet OJ Example INSERT INTO Statement Example
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'