Computer Science

SQL SELECT

SQL SELECT is a statement used to retrieve data from a database. It allows users to specify the columns they want to retrieve and apply filtering conditions to narrow down the results. The SELECT statement is fundamental to querying and extracting specific information from databases in SQL.

Written by Perlego with AI-assistance
Related key terms

11 Key excerpts on "SQL SELECT"

  • SQL Server on Linux
    eBook - ePub

    SQL Server on Linux

    A Crash Course in Querying

    Databases are one of the cornerstones of modern businesses. Data retrieval is usually made with a SELECT statement and it's therefore very important that you are familiar with this part of your journey. Retrieved data is often not organized in the way you want it to be, so it requires additional formatting. Besides formatting, accessing very large amounts of data requires you to take into account the speed and manner of query execution, which can have a major impact on system performance.
    This chapter will be your guide through the basic elements of the SELECT statement, the last of the DML commands that we haven't yet covered in detail. To cover all the elements and scenarios we would need at least another book.
    We will cover the following topics:
    • Retrieving and filtering data
    • Summarizing data
    • Querying multiple tables
    Passage contains an image

    Retrieving and filtering data

    As you will have noticed, databases usually consist of many tables where all the data is stored. The AdventureWorks database contains 71 tables, including tables for Customers, Products, Orders, and so on. The table names clearly describe the data that is stored in the table. If you need to create a list of new products, or a list of customers who ordered the most products, you would retrieve this information by creating a query. A query is an enquiry into the database made by using the SELECT statement. The SELECT statement is the first and most fundamental SQL statement that we are going to introduce in this chapter.
    The SELECT statement consists of a set of clauses that specifies which data will be included into a query result set. All the clauses of SQL statements are the keywords and, because of that, are written in capital letters. A syntactically correct SELECT statement requires a mandatory FROM clause which specifies the source of the data you want to retrieve. Besides mandatory clauses, there are a few optional clauses that can be used to filter and organize data:
    Sign up to read
    Learn more about book
  • SQL in 7 Days
    eBook - ePub

    SQL in 7 Days

    A Quick Crash Course in Manipulating Data, Databases Operations, Writing Analytical Queries, and Server-Side Programming (English Edition)

    the " database. You can have many of them.
    There are tools that help define the database schema in the source control repository, track its changes and deploy these changes into the databases. We will discuss these tools in the section Code-First Approach and Migrations .
    Now, let us look closer into this most simple query. What does it have?
    SELECT is the basic command for retrieving the data from the database.
    It is so ubiquitous that SQL developers usually talk about "selecting data " from the database, rather than "getting " or "retrieving " or "querying " the data.
    The SELECT keyword is followed by the comma-separated list of fields Elsie wants to retrieve. If she wants to retrieve all fields from the table in the order they are defined, she can use the asterisk code instead of the list of the fields.
    After the list of the fields, she has the FROM clause. It defines the table to take the data from.
    As I have mentioned earlier, it is not the most natural order of clauses. Elsie must list the fields first, and the table name, which defines the fields, later.
    This query returns a resultset of all tuples from the source table. Note that the order of the records is defined, but the order of the tuples is not. If Elsie runs this query twice, the database can return the tuples ordered differently. But she can be sure that all the tuples will be returned one way or another.
    Sign up to read
    Learn more about book
  • Introductory Relational Database Design for Business, with Microsoft Access
    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
  • Beginning Visual Basic 2015
    eBook - ePub

    Beginning Visual Basic 2015

    • Bryan Newsome(Author)
    • 2015(Publication Date)
    • Wrox
      (Publisher)
    In SQL Server, you can save queries as a stored procedure. A stored procedure is much like a function you use in Visual Studio. You call it, pass in parameters and get a return value from the database. Using stored procedures can make your Visual Basic 2015 code easier because you have fewer complex SQL queries included in your code. They can also make your programs faster because database engines can precompile execution plans for queries when you create them, whereas the SQL code in a Visual Basic 2015 program needs to be reinterpreted every time it’s used. You will learn more about stored procedures in Chapter 13.
    To really understand the implications of queries, you need to learn some SQL. Fortunately, compared with other programming languages, basic SQL is really simple.

    UNDERSTANDING BASIC SQL SYNTAX

    The
    American National Standards Institute
    (
    ANSI
    ) defines the standards for ANSI SQL. Most database engines implement ANSI SQL to some extent and often add some features specific to the given database engine.
    The benefit of ANSI SQL is that once you learn the basic syntax for SQL, you have a solid grounding from which you can code the SQL language in almost any database. All you need to learn is a new interface for the database that you are working in. Many database vendors extended SQL to use advanced features or optimizations for their particular database. It is best to stick with ANSI-standard SQL in your coding whenever possible, in case you want to change databases at some point.

    Using SELECT Statement

    The SQL SELECT statement selects data from one or more fields in one or more records, and from one or more tables in your database. Note that the SELECT statement only selects data—it does not modify the data in any way.
    The simplest allowable SELECT statement is like this:
    SELECT * FROM Employees
    The preceding statement means “retrieve every field for every record in the Employees table.” The * indicates “every field.” Employees indicates the table name. If you want to retrieve only first names and last names, you can provide a list of field names rather than an asterisk (*) :
    SELECT [First Name], [Last Name] FROM Employees
    You need to enclose these field names in square brackets because they contain spaces. The square brackets indicate to the SQL interpreter that even though there is a space in the name, it should treat First Name as one object name and Last Name
    Sign up to read
    Learn more about book
  • Beginning Visual Basic 2010
    eBook - ePub

    Beginning Visual Basic 2010

    • Thearon Willis, Bryan Newsome(Authors)
    • 2011(Publication Date)
    • Wrox
      (Publisher)
    Query objects in Microsoft Access are a hybrid of two types of objects in SQL Server: views and stored procedures. Using database query objects can make your Visual Basic 2010 code simpler, because you have fewer complex SQL queries included in your code. They can also make your programs faster, because database engines can precompile execution plans for queries when you create them, whereas the SQL code in a Visual Basic 2010 program needs to be reinterpreted every time it's used.
    To really understand the implications of queries, you need to learn some SQL. Fortunately, compared to other programming languages, basic SQL is really simple.

    The SQL SELECT Statement

    The American National Standards Institute (ANSI) defines the standards for ANSI SQL. Most database engines implement ANSI SQL to some extent and often add some features specific to the given database engine.
    The benefit of ANSI SQL is that once you learn the basic syntax for SQL, you have a solid grounding from which you can code the SQL language in almost any database. All you need to learn is a new interface for the database that you are working in. Many database vendors extended SQL to use advanced features or optimizations for their particular database. It is best to stick with ANSI standard SQL in your coding whenever possible, in case you want to change databases at some point.
    The SQL SELECT statement selects data from one or more fields in one or more records and from one or more tables in your database. Note that the SELECT statement only selects data—it does not modify the data in any way.
    The simplest allowable SELECT statement is like this:
    SELECT * FROM Employees;
    The preceding statement means “retrieve every field for every record in the Employees table.” The *
    Sign up to read
    Learn more about book
  • Database Modeling Step by Step
    eBook - ePub

    Database Modeling Step by Step

    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
  • Beginning Visual Basic 2012
    eBook - ePub

    Beginning Visual Basic 2012

    • Bryan Newsome(Author)
    • 2012(Publication Date)
    • Wrox
      (Publisher)
    In SQL Server, you can save queries as a stored procedure. A stored procedure is much like a function you use in Visual Studio. You call it, pass in parameters and get a return value from the database. Using stored procedures can make your Visual Basic 2012 code easier because you have fewer complex SQL queries included in your code. They can also make your programs faster because database engines can precompile execution plans for queries when you create them, whereas the SQL code in a Visual Basic 2012 program needs to be reinterpreted every time it's used. You will learn more about stored procedures in Chapter 16.
    To really understand the implications of queries, you need to learn some SQL. Fortunately, compared with other programming languages, basic SQL is really simple.

    Understanding Basic SQL Syntax

    The American National Standards Institute (ANSI) defines the standards for ANSI SQL. Most database engines implement ANSI SQL to some extent and often add some features specific to the given database engine.
    The benefit of ANSI SQL is that once you learn the basic syntax for SQL, you have a solid grounding from which you can code the SQL language in almost any database. All you need to learn is a new interface for the database that you are working in. Many database vendors extended SQL to use advanced features or optimizations for their particular database. It is best to stick with ANSI-standard SQL in your coding whenever possible, in case you want to change databases at some point.

    Using SELECT Statement

    The SQL SELECT statement selects data from one or more fields in one or more records, and from one or more tables in your database. Note that the SELECT statement only selects data—it does not modify the data in any way.
    The simplest allowable SELECT statement is like this:
      SELECT * FROM Employees
    The preceding statement means “retrieve every field for every record in the Employees table.” The * indicates “every field.” Employees indicates the table name. If you want to retrieve only first names and last names, you can provide a list of field names instead of an asterisk (*) :
      SELECT [First Name], [Last Name] FROM Employees
    You need to enclose these field names in square brackets because they contain spaces. The square brackets indicate to the SQL interpreter that even though there is a space in the name, it should treat First Name as one object name and Last Name
    Sign up to read
    Learn more about book
  • Learning PostgreSQL 11
    eBook - ePub

    Learning PostgreSQL 11

    A beginner's guide to building high-performance PostgreSQL database solutions, 3rd Edition

    • Salahaldin Juba, Andrey Volkov(Authors)
    • 2019(Publication Date)
    • Packt Publishing
      (Publisher)

    SQL Language

    Structured Query Language (SQL ) is used to set up the structure of the database, to manipulate the data in a database, and to query the database. This chapter is dedicated to the Data Manipulation Language (DML ) that is used to create data in a database, change or delete it, and retrieve it from the database.
    After reading this chapter, you will understand the concept of SQL and the logic of SQL statements. You will be able to write your own SQL queries and manipulate data using this language. A complete reference for SQL can be found in the official PostgreSQL documentation at http://www.postgresql.org/docs/current/static/sql.html .
    The topics that we will cover in this chapter are as follows:
    • SQL fundamentals
    • Lexical structure
    • Querying data with SELECT statements
    • Changing data in a database
    The code examples in this chapter are based on the prototype of a car portal database described in the previous chapters. The scripts to create the database and fill it with sample data can be found in the attached media. They are called schema.sql and data.sql. All of the code examples from this chapter can be found in the examples.sql file.
    Refer to Chapter 2 , PostgreSQL in Action , for details on how to use the psql utility.
    Passage contains an image

    SQL fundamentals

    SQL is used to manipulate the data in a database and to query the database. It's also used to define and change the structure of the data—in other words, to implement the data model. You already know this from the previous chapters.
    In general, SQL has three parts:
    • Data Definition Language (DDL )
    • DML
    • Data Control Language (DCL )
    The first part is used to create and manage the structure of the data, the second part is used to manage the data itself, and the third part controls access to the data. Usually, the data structure is defined only once, and then it's rarely changed. However, data is constantly inserted into the database, changed, or retrieved. For this reason, the DML is used more often than the DDL.
    Sign up to read
    Learn more about book
  • Database Management Systems
    eBook - ePub

    Database Management Systems

    what is the minimum value?
    What is the minimum price between the products?
    • SELECT MIN(PROD_PRICE) AS MIN_PRICE FROM PRODUCT;
    MIN_PRICE
    125
    What are the maximum price, minimum price, and the range between prices for products?
    • SELECT MAX(PROD_PRICE) AS MAX_PRICE, MIN(PROD_PRICE) AS MIN_PRICE,
    • (MAX(PROD_PRICE) - MIN(PROD_PRICE)) AS PRICE_RANGE FROM PRODUCT;
    MAX_PRICE
    MIN_PRICE
    PRICE_RANGE
    19,000 125 18875
    What is the date when the first salesperson was hired?
    • SELECT MIN(SLPRS_HIRE_DATE) AS MIN_DATE FROM SLPRS;
    MIN_DATE
    2013-11-22

    Summary

    A query is an inquiry to find extra data from the database. Unlike INSERT, DELETE, CREATE, and UPDATE commands that change the database structure or the database data set, queries are just accessing the data without making any changes to the database. An SQL query selects and extracts data from a table or tables. The selection process always applies to the two-dimensional structure of tables. To select data from a database, the SELECT SQL command is used.
    Every SELECT SQL command selects table data and extracts the data into a new table. The previous query selects the SLPRS_ID and SLPRS_SALARY attributes. The new table is not part of the stored database. It is created ad hoc every time the particular action is executed and the extracted table always returns the current available data of/to the querying table(s).
    In forming the criteria for the row selector, the WHERE clause with all conditional and logical operators is used. The logical and conditional operators in the WHERE clause return the value TRUE for each row that satisfies the criteria set. The OR operator must select rows from a predefined set of values and not from a range of possible values. In this case, the IN operator should be used instead of many repeating OR operators.
    Searching text with wildcards requires the use of the LIKE operator, rather than the (=) equal operator. This usually occurs when some repeating patterns of characters exist inside text data. Using arithmetic operations in numeric data types is a frequent operation. Any arithmetic operation (+−/*) may be applied to an arithmetic data type. An ORDER BY clause is always placed at the end of the query and rearranges the rows of a table accordingly in ascending or descending order.
    Sign up to read
    Learn more about book
  • Beginning Microsoft SQL Server 2012 Programming
    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
  • Learn SQL Database Programming
    eBook - ePub

    Learn SQL Database Programming

    Query and manipulate databases from popular relational database servers using SQL

    Querying a Single Table

    In this chapter, you will learn how to query a single table. This includes learning how to use the SQL SELECT statement and the FROM, WHERE, and ORDER BY clauses. You will also learn how to tell which index your query is using and if you may need additional indexes. By the end of this chapter, you will be able to understand how to query data using the SELECT statement and the FROM clause. You will also learn how to limit the results with a WHERE clause, how to use an ORDER BY clause to return results in a specified order, and how to see information about what indexes are being used or may be needed.
    In this chapter, we will cover the following topics:
    • Using the SELECT statement and FROM clause
    • Using the WHERE clause
    • Using the ORDER BY clause
    • Using indexes with your queries
    Let's get started!
    Passage contains an image

    Technical requirements

    The code files for this chapter can be found at the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-6 .
    Passage contains an image

    Using the SELECT statement and FROM clause

    To extract data from a table, you need to use a SQL SELECT query. The SELECT query allows you to specify what data you want from a table using a simple query structure.
    Passage contains an image

    Understanding the SELECT statement and the FROM clause

    At a minimum, every SELECT statement on a table needs the SELECT and FROM keywords. If you want to select all the rows and columns from the appearances table in lahmansbaseballdb, you should execute the following query:
    SELECT * FROM lahmansbaseballdb.appearances;
    Make sure to add a semicolon (;) to the end of your SQL statements. The semicolon marks the end of a query. The query may execute without it, but it is good practice to ensure that the SQL code will execute properly.
    Sign up to read
    Learn more about book
Index pages curate the most relevant extracts from our library of academic textbooks. They’ve been created using an in-house natural language model (NLM), each adding context and meaning to key research topics.
Explore more topic indexes
View all