SQL SELECT

11 Key excerpts on "SQL SELECT"

• 

  eBook - PDF

  Wiley Pathways Introduction to Database Management

  • Mark L. Gillenson, Paulraj Ponniah, Alex Kriegel, Boris M. Trukhnov, Allen G. Taylor, Gavin Powell, Frank Miller(Authors)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  As you have already learned, you use SELECT to retrieve data. You can also use SELECT to perform calculations and to retrieve the results from functions, which are special commands designed to operate on data and return a result. 6.2.1 Working with SELECT You have already been introduced to the SELECT statement and its basic syn- tax. We’re going to talk a bit more about SELECT because you will likely use it more than any other command. The complete SELECT command syntax is beyond the scope of this chapter, but we’ll take a closer look at a few additional syntax options. SELECT statement command examples used in this chapter are all based on Microsoft SQL Server’s Transact-SQL variation of the SQL standard language. Command syntax for the SELECT command, as well as for other com- mands discussed later in the chapter, varies for other DBMS products. SELECT commands are considered declarative statements rather than being procedural in nature. This means that you specify what data you are looking for rather than provide a logical sequence of steps that guide the system in how to find the data. The relational DBMS analyzes the declarative SQL SELECT state- ment and creates an access path—a plan for what steps to take to respond to the query. The SELECT command also allows the user, in some circumstances, to exert a certain amount of logical control over the data retrieval process. 6.2.2 USING SIMPLE DATA RETRIEVAL 193 Another point is that SQL SELECT commands can be run in either an inter- active query or an embedded mode. In the query mode, the user types the com- mand at a workstation and presses the Enter key. In the embedded mode, the SELECT command is embedded within the lines of a higher-level language pro- gram and functions as an input or “read” statement for the program. When the program is run and the program logic reaches the SELECT command, the pro- gram executes the SELECT.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Fundamentals of Database Management Systems

  • Mark L. Gillenson(Author)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  It’s a bit unfortunate that the same word is used to mean two different things, but that’s the way it is. The fact is that the SQL SELECT command is capable of performing relational Select, Project, and Join operations singly or in combination, and much more SQL SELECT commands are considered, for the most part, to be ‘‘declarative’’ rather than ‘‘procedural’’ in nature. This means that you specify what data you are looking for rather than provide a logical sequence of steps that guide the system in how to find the data. Indeed, as we will see later in this chapter, the relational DBMS analyzes the declarative SQL SELECT statement and creates an access path, a plan for what steps to take to respond to the query. The exception to this, and the reason for the qualifier ‘‘for the most part’’ at the beginning of this paragraph, is that a feature of the SELECT command known as ‘‘subqueries’’ permits the user to specify a certain amount of logical control over the data retrieval process. Another point is that SQL SELECT commands can be run in either a ‘‘query’’ or an ‘‘embedded’’ mode. In the query mode, the user types the command at a workstation and presses the Enter key. The command goes directly to the relational DBMS, which evaluates the query and processes it against the database. The result is then returned to the user at the workstation. Commands entered this way can normally also be stored and retrieved at a later time for repetitive use. In the embedded mode, the SELECT command is embedded within the lines of a higher-level language program and functions as an input or ‘‘read’’ statement for the program. When the program is run and the program logic reaches the SELECT command, the program executes the SELECT. The SELECT command is sent to the DBMS which, as in the query-mode case, processes it against the database and returns the results, this time to the program that issued it. The program can then use and further process the returned data.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Programming with Microsoft Visual Basic 2017

  • Diane Zak(Author)
  • 2017(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  You use the SELECT statement to create database queries. As you learned in Chapter 11, a database query , often referred to more simply as a query , is a statement that allows you to retrieve specific information from a database. For example, you can use a query to specify the fields and records you want either to display or to use in a calculation. The basic syntax of the SELECT statement is shown in Figure 12-1 along with some of the operators that can be included in the WHERE clause’s condition. Capitalizing the boldfaced keywords in a SELECT statement is optional; however, many programmers do so for clarity. You can pronounce SQL either as ess-cue-el or as sequel . Figure 12-1 SELECT statement’s basic syntax and operators SELECT Statement Basic syntax SELECT FROM table [ WHERE condition ] [ ORDER BY [ DESC ]] Operators for the WHERE clause’s condition = equal to <> not equal to > greater than >= greater than or equal to < less than <= less than or equal to AND all subconditions must be true for the compound condition to evaluate to True OR only one of the subconditions needs to be true for the compound condition to evaluate to True NOT reverses the truth-value of the condition LIKE uses a wildcard character to compare text values; the % wildcard represents zero or more characters and the _ (underscore) wildcard represents one character IS NULL compares a value with a NULL value Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. 531 F-1 SELECT Statement In the syntax, fieldList is one or more field names separated by commas, and table is the name of the table containing the fields. The WHERE and ORDER BY clauses are optional parts of the syntax. You use the WHERE clause , which contains a condition , to limit the records you want to retrieve.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Database

  Principles Programming Performance

  • Patrick O'Neil(Author)
  • 2014(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  The SQL SELECT statement is more complicated and difficult to master than the relatively simple relational algebra, but you should never feel lost or uncer-tain as long as you have access to computer facilities where experimenta-tion can clear up uncertainties about SQL use. The interactive SQL environment discussed in the current chapter allows you to type a query on a terminal screen and get an immediate answer. Such interactive queries are sometimes called ad hoc queries. (Ad hoc is a Latin phrase meaning for this specific purpose.) This refers to the fact that an SQL SELECT statement is meant to be composed all at once on a few typewritten lines and not be dependent on any prior interaction in a user session. The feature of not being dependent on prior interaction is also known as non-procedurality. SQL differs in this way even from relational algebra, where a prior alias statement might be needed in order to represent a product of a table with itself. The difference between SQL and procedural languages such as Pascal or C is profound: you don't need to write a program to try out an SQL query, you just have to type the relatively short, self-contained text of the query and submit it. Of course, an SQL query can be rather complex. In Section 3.8, after a good deal of introduction, we consider the full form of an SQL SELECT state-ment for the first time. A limited subset of this full form, known as a Subse-lect, is listed in Figure 3.1. You shouldn't feel too intimidated by the complexity of the Select statement, however. The fact that a Select state-ment is non-procedural means that it has a lot in common with a menu-driven application, where a user is expected to fill in some set of choices from a menu and then to press the Enter key to execute the menu choices all at once. The various clauses of the Select statement correspond to menu choices: you will occasionally need all these clauses, but you don't expect to use all of them every time you pose a query.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  SQL Clearly Explained

  • Jan L. Harrington(Author)
  • 2003(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Purt Two Performing Interactive Data Manipulation This Page Intentionally Left Blank Simple SQL Retrieval It may seem a bit backwards to talk about retrieval before creating a database or entering data, but much of SQUs data modification syntax relies on finding data to be changed. You will therefore find it easier to work with modification statements if you are first famil- iar with retrieving data. We are therefore going to assume that someone else has created a database and loaded it with data for our use. SQL has one command for retrieving data: SELECT. This is nowhere as restrictive as it might seem. SELECT contains syntax for choosing columns, choosing rows, combining tables, grouping data, and per- forming some simple calculations. In fact, a single SELECTs t a t e m e n t can result in a DBMS performing any or all of the relational algebra operations. 35 36 SIMPLESQL RETRIEVAL The basic syntax of the SELECT statement has the following general structure: SELECT column1, column2, ... FROM table1, table2, ... WHERE selection criteria m The SELECT clause specifies the columns you want to see. You spec- ify the tables used in the query in the FROM clause. The optional WHERE clause can contain a wide variety of criteria that identify which rows you want to retrieve. Note: Most SQL command processors are not case sensitive when it comes to parts of a SQL statement. SQL keywords, table names, column names, and so on can be in any case you choose. However, most DBMSs are case sensitive when it comes to matching data values. Therefore whenever you place a value in quotes for SQL to match, you must match the case of the stored data. In this book, SQL keywords will appear in uppercase let- ters; database components such as columns and table names will appear in lowercase letters. In addition to these basic clauses, SELECT has many other syntax op- tions.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • CRC Press

    (Publisher)

  What does all this mean without using a plethora of nasty long words? In short, SQL was developed as a shorthand method of retrieving information from relational databases, and SQL has become the industry standard over the last 20 years. Here is an example of a query (a question posed to the database that asks for specific information), which is written in SQL and retrieves all the rows in a table called AUTHOR:

  SELECT AUTHOR_ID,  NAME FROM AUTHOR;

  4.1.2    SQL for Different Databases

  SQL as implemented in different database vendor products is usually standardized using American National Standards Institute (ANSI) standardized SQL, but individual database vendors will have different naming conventions, different syntax, and even extra add-on bells and whistles. In reality, different database vendors develop a unique relational database and Relational Database Management Systems (RDBMS) containing a proprietary form of SQL.

  Relational Database Management System (RDBMS) is a term used to describe both a database and the associated tools (database management toolkit) used to work with and manage database software.

  So what are the basics of SQL?

  4.1.3    Introducing SQL

  This section summarizes the different types of SQL as described in the following list:

  •  Query Command. Querying or reading a database is performed with a single command called the SELECT command, which is used to retrieve data from tables in a database. There are various ways in which data can be retrieved from tables:

  ○  Basic Query. Retrieve all rows from a single table.

  ○  Filtered Query. A filtered query uses a WHERE clause to include or exclude specific rows.

  ○  Sorted Query. Sorting uses the ORDER BY clause to retrieve rows in a specific sorted order.

  ○  Aggregated Query

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introductory Relational Database Design for Business, with Microsoft Access

  • Jonathan Eckstein, Bonnie R. Schultz(Authors)
  • 2017(Publication Date)
  • Wiley

    (Publisher)

  10 Basic Structured Query Language (SQL)

  Structured Query Language (SQL) allows you to retrieve, manipulate, and display information from a database. MS Access lets you perform many of the same tasks using Query Design View, but other database software either lacks this capability or may implement it differently. If you understand the SQL language underlying Access queries, you will be able to formulate queries for essentially any relational database, including ones too large to store in Access. For most of this chapter, we will draw examples from the plumbing store database in Chapter 7 , whose design is shown in Figure 10.1 .

  Figure 10.1

  Relationships Window for plumbing supply store database.

  Using SQL in Access

  You can easily display the SQL form of any query in Access. After pressing the “Query Design” button, you simply select “SQL View” from the “View” button at the left of the “Home” ribbon at the top of the window. When viewing the results of a query, you can also display its SQL form by selecting “SQL View” from the “Home” ribbon. When viewing the SQL form of any query, you may run it by selecting “Datasheet View” on the same “View” button, or by pressing the “!” (Run) button next to it.

  The SELECT … FROM Statement

  The SELECT … FROM statement is the core of SQL. It specifies which information you want to display. Although SQL has other statements, this book focuses on the SELECT statement. The most basic form for the SELECT statement is:

  SELECT expressions FROM data_source ;

  In the simplest case, expressions is a single field name and data_source is a single table. For example, for the plumbing store database, the statement

  SELECT OrderDate FROM ORDERS;

  shows the OrderDate field for each row of the ORDERS table. The expressions specifier may also be a list of attribute names separated by commas. An example of such a query from the same database is:

  SELECT City, State FROM CUSTOMER;

  This query shows the City and State

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Wiley Pathways Introduction to Database Management, Project Manual

  • Mark L. Gillenson, Paulraj Ponniah, Alex Kriegel, Boris M. Trukhnov, Allen G. Taylor, Gavin Powell, Frank Miller(Authors)
  • 2009(Publication Date)
  • Wiley

    (Publisher)

  Project 6.4 Using the SELECT Command Overview The SELECT command is used to retrieve data and to evaluate expressions. In many applications, it is the most commonly used SQL command. Because of this, it’s important that you be familiar and comfortable with the basic use and syntax of the SELECT command. Outcomes After completing this project, you will know how to: use SELECT to retrieve data from a table use SELECT to evaluate an expression What you’ll need To complete this project, you will need: a computer with SQL Server 2005 Evaluation Edition installed the sample database Chapter6, which you will attach during this project Completion time 30 minutes Precautions Do not make changes to any database other than Chapter6. Do not make any changes other than those specified in this project. Part A: If necessary, launch SQL Server Management Studio and connect to your local server Understanding the SQL Language 117 Part B: Attach the sample database 1. Open the My Computer window and create a folder named CH6Data at the root of the C: drive. 2. Copy CH6DB.zip to CH6Data and extract the file to the default location. 3. In SQL Server Management Studio, right-click Databases and select Attach. 4. Click Add. Locate and select C:\CH6Data\CH6DB\Chap6.mdf, and then click OK. 5. In the Attach Databases dialog box, click OK. 6. Verify that the database Chapter6 is listed. Expand Chapter6, and then expand Tables. Part C: Open a new query window and test 1. Right-click Chapter6 and select New Query. 2. Execute the following: SELECT * FROM DBO.SALESPERSON 3. Verify that three rows are returned. Part D: Execute each of the queries below and describe the result Note: Clear the query window after executing each query. 1. SELECT * FROM DBO.SALESPERSON ___________________________________________________________________________ ___________________________________________________________________________ 2.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Developing Windows-Based and Web-Enabled Information Systems

  • Nong Ye, Teresa Wu(Authors)
  • 2014(Publication Date)
  • CRC Press

    (Publisher)

  151 8 Structured Query Language Structured query language (SQL) is the most influential query language of relational data-bases. This chapter first reviews the background of SQL followed by a brief discussion on Backus–Naur form (BNF) and extended BNF (EBNF), a main notation technique used in illustrating SQL syntax. The basic SQL statements, including CREATE, UPDATE, DELETE, and QUERY data, are then explained with examples. 8.1 Introduction to SQL SQL was initially developed by IBM as a part of the System R project in the early 1970s and was named structured English query language (SEQUEL). Later, it evolved to today’s acronym: SQL. In 1986, the American National Standards Institute first pub-lished the SQL standard, called “SQL-86,” followed by the International Organization for Standardization (ISO) in 1987. Since then, SQL has been enhanced several times. The first major revision was released in 1992, known as “SQL2” or “SQL-92,” which greatly enhanced the standard specification. The SQL syntax discussed in this chapter follows the SQL2 standard. SQL3 was released with support for object-oriented data manage-ment in 1999. In 2003, extensible markup language (XML)-related features were first introduced into SQL, resulted into version SQL-2003. As of today, there are seven revi-sions, and the most recent one is SQL-2011 (ISO 9075:2011), which was formally adopted in December 2011. According to the SQL standard, SQL is composed of five main languages: • Data definition language (DDL) . DDL is used to create and modify the relation sche-mas within a database. Example commands are the CREATE, ALTER, and DROP statements. • Data manipulation language (DML) . DML is used to retrieve, store, modify, delete, insert, and update data in the database. Examples of this are the SELECT, UPDATE, and INSERT statements. • Data control language (DCL) . DCL is used to create roles, permissions, and referen-tial integrity constraints for a database.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  Wow — that’s a lot to decipher. Let’s look at the parts.

  NOTE Note that the parentheses around the TOP expression are, technically speaking, optional. Microsoft refers to them as “required,” and then points out that a lack of parentheses is actually supported, but for backward compatibility only. This means that Microsoft may pull support for that in a later release, so if you do not need to support older versions of SQL Server, I strongly recommend using parentheses to delimit a TOP expression in your queries.

  The SELECT Statement and FROM Clause

  The verb — in this case a SELECT — is the part of the overall statement that tells SQL Server what you are doing. A SELECT indicates that you are merely reading information, as opposed to modifying it. What you are selecting is identified by an expression or column list immediately following the SELECT . You’ll see what I mean by this in a moment.

  Next, you add in more specifics, such as where SQL Server can find this data. The FROM statement specifies the name of the table or tables from which you want to get your data. With these, you have enough to create a basic SELECT statement. Fire up the SQL Server Management Studio and take a look at a simple SELECT statement:

  SELECT * FROM INFORMATION_SCHEMA.TABLES;

  Code snippet Chap03.sql

  Let’s look at what you’ve asked for here. You’ve asked to SELECT information; when you’re working in SQL Server Management Studio, you can also think of this as requesting to display information. The * may seem odd, but it actually works pretty much as * does everywhere: it’s a wildcard. When you write SELECT * , you’re telling T-SQL that you want to select every column from the table. Next, the FROM indicates that you’ve finished writing which items to output and that you’re about to indicate the source of the information — in this case, INFORMATION_SCHEMA.TABLES

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  6 SQL: Beyond the Query Language LEARNING OBJECTIVES • To define a relational database schema in SQL satisfying the constraints of the design • To learn how to manipulate data using insert, update, and delete • To understand fundamental data security using privileges Although SQL is short for Structured Query Language, the SQL standard has evolved to include much more than just the query language. There is a data definition language for defining tables and associated constraints on the table and its attributes, including referential integrity. There is also a data manipulation language for inserting, updating, and deleting tuples from tables. In addi- tion, there are language components for defining data access privileges. This chapter highlights these features of a relational database. It is important to note that every database product is an approximation to the SQL standard. When you use a particular database product, it is important to look up the specific syntax it supports. 6.1 Data Definition Chapter 1 introduced the relational data model as a collection of relations. A relation’s schema refers to its description, indicating the attributes in the relation. A visual schema of the database includes the tables with their attributes along with yellow gold keys to indicate primary keys and orange keys to denote foreign keys linked to their associated primary keys, illustrating the referential integrity relationship between the tables. Textually, a summary syntax for a relational schema provides the table name with a list of its attributes enclosed in parentheses, underlining the attributes that form the primary key of the table. The SQL DDL specifies the syntax to create, drop, and alter tables. The SQL standard provides a sublanguage for defining a database, called a data definition lan- guage (DDL). There is a create table statement to define the table attributes with the associated types and constraints, along with any constraints that apply at the table level.

  Sign up to read

  Learn more about book