Understanding SQL

SQL: The Complete Reference provides an in-depth discussion of SQL fundamentals, modern SQL products, and SQLs role in trends such as data warehousing, "thin-client" architectures, and Internet-based e-commerce. This book is your one-stop...
Nội dung trích xuất từ tài liệu:
Understanding SQLUnderstanding SQL Part 9Articles A Understanding SQL A1 B Exporting Data A45 C Visual Basic Function Reference A49 D Internet Explorer Web Page Color Names A59 A1 Part 9: Articles Article AUnderstanding SQL SQL Select Queries . . . . . . . . . . . . . . . . A4 SQL Action Queries . . . . . . . . . . . . . . . . A38 Underlying every query in Microsoft Access is the SQL database command language. Although you can design most queries using the simple Access design grid (or the view, function, or stored procedure designer in an Access project file), Access stores every query you design as an SQL command. When you use one of the designers, Access creates the SQL for you. However, for advanced types of queries that use the results of a second query as a comparison condition, you need to know SQL in order to define the second query (called a subquery). Also, you cannot use the design grid to construct all the types of queries Access is capable of handling; you must use SQL for some of them. As you learned in Chapter 18, “Building Queries in an Access Project,” understanding SQL is essential to building queries in SQL Server. Note This article does not document all the syntax variants accepted by Access, but it does cover all the features of the SELECT statement and of action queries. Wherever possible, ANSI-standard syntax is shown to provide portability across other databases that also support some form of SQL. You might notice that Access modifies the ANSI-standard syntax to a syntax that it prefers after you define and save a query. You can find some of the examples shown in the following pages in the ContactsDataCopy.mdb sample database. When an example is in the sample database, you’ll find the name of the sample query in italics immediately preceding the query in the text. For a discussion of the syntax conventions used in this article, see the Conventions and Features Used In This Book section in the book’s front matter. How to Use This Article This article contains two major sections: SQL select queries and SQL action queries. Within the first section, you can find keywords used in the SQL language in alphabetical order. You can also find entries for the basic building blocks you need to understand and use in various clauses: Column-Name, Expression, Search-Condition, and Subquery. If you’re new to SQL, you might want to study these building block topics first. You can then study the major clauses of a SELECT statement in the order in which they appear in a SELECT statement: PARAMETERS, SELECT, FROM, WHERE, GROUP BY, HAVING, UNION, and ORDER BY. In the second section, you can find a discussion of the syntax for the four types of queries that you can use to update your database, also in alphabetical order: DELETE, INSERT, SELECT INTO, and UPDATE. As you study these topics you’ll find references to some of the major clauses that you’ll also use in a SELECT statement. You can find the details about those clauses in the first section. A3 Part 9: ArticlesPart 9: Articles Microsoft Office Access 2003 Inside Out SQL Select QueriesArticle 1 The SELECT statement forms the core of the SQL database language. You use the SELECT state- ment to select or retrieve rows and columns from database tables. The SELECT statement syntax contains six major clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. In an Access desktop database (.mdb), Microsoft Access implements four significant exten- sions to the standard language: TRANSFORM, to allow you to build crosstab queries; IN, to allow you to specify a remote database connection or to specify column names in a crosstab query; DISTINCTROW in a SELECT statement, to limit the rows returned from the to rows that have different primary key values in the tables that supply columns in the ; and WITH OWNERACCESS OPTION in a SELECT statement, to let you design queries that can be run by users who are authorized to use the query, including those who have insufficient access rights to the tables referenced in the query. Note When you save a query you have written in SQL in your database, Access often examines your SQL command and adds brackets or extra parentheses to make the com- mand easier to parse and compile. In some cases, Access restates complex predicates orChapter A1 changes the ANSI-standard syntax to one it prefers. For this reason, the examples shown in the book might not exactly match what you see in the sample queries when you open them in SQL view. If you enter the SQL exactly as shown in the book, it will return the same result as the sample query you find in the database. Aggregate Functions: AVG, CHECKSUM_AGG, COUNT, MAX, MIN,Chapter A1 STDEV, STDEVP, SUM, VAR, VARP See Table 8-1 (on page 297 of the printed book) in Chapter 8, “ ...