SQL Expressions

4 Key excerpts on "SQL Expressions"

• 

  eBook - ePub

  Computer Programming for Beginners

  A Step-By-Step Guide

  • Murali Chemuturi(Author)
  • 2018(Publication Date)
  • Chapman and Hall/CRC

    (Publisher)

  7 Arithmetic, Relational, and Logical Expressions Introduction to Expressions

  As we have noted in Chapter 6 , expressions are used in statements. Almost every statement contains at least one expression. Merriam Webster’s dictionary defines an expression generally as “an act of expressing” and “an act of making your thoughts, feelings etc. known by speech, writing or some other method,” and in the context of computer programming, thus, “a mathematical or logical symbol or a meaningful combination of symbols”. Wikipedia defines an expression thus: “an expression in computer programming is a combination of explicit values, constants, variables, operators and functions, that are interpreted according to the particular rules of precedence and of association for the specific programming language.”

  Let us enumerate the attributes of expressions in the context of computer programming to understand them better: 1.  An expression may consist of at least two variables, a constant, or a combination of variables and constants. 2.  The variables and constants in the expression are combined using mathematical, relational, or logical symbols, generally referred to as “operators.” 3.  The expression is amenable to evaluation using arithmetic, relational, or logical rules.

  4.  The evaluation of an expression in a statement yields only one value that can be used for assignment to a variable or in programmed decision making. That is, expressions can be used in assignment statements and control statements. Expressions are not used in a stand-alone mode.

  5.  Expressions, when used in assignment statements, must be on the right-hand side of the assignment symbol. An expression can never be on the left-hand side of an assignment symbol.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn SQL Database Programming

  Query and manipulate databases from popular relational database servers using SQL

  • Josephine Bush(Author)
  • 2020(Publication Date)
  • Packt Publishing

    (Publisher)

  Working with Expressions

  In this chapter, you will learn how to use expressions, including using literals, operators, columns, and built-in functions to create expressions. You will learn about the different types of built-in functions, including string, numeric, datetime, and advanced functions, which include casting and converting to other data types. You will learn how to use statistical functions, including how to get and use variance and standard deviation. Finally, you will learn how to create a generated column based on an expression.

  In this chapter, we will cover the following topics:

  • Using expressions
  • Working with dates and times
  • Using statistical functions
  • Using generated columns

  Passage contains an image

  Technical requirements

  You can refer to the code files of this chapter at the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-9 .

  Passage contains an image

  Using expressions

  An expression is a combination of values that are interpreted by MySQL to produce another value. Expressions can be used in SELECT statement clauses, the WHERE clause, the ORDER BY clause, the HAVING clause (covered in Chapter 10 , Grouping and Summarizing Data ), or in a SET statement (covered in Chapter 12 , Programmable Objects ).

  Expressions include column values, operators, literal values, built-in functions, NULL values, user-defined functions, and stored procedures. User-defined functions and stored procedures are

  covered in Chapter 12 ,

  Programmable Objects.

  You can combine literals, operators, and built-in functions in countless ways to produce expressions. Your imagination may be the only limit on the ways you can combine these into expressions.

  Passage contains an image

  Literal values

  A literal value is a constant value such as a string, a number, or a NULL value. The following query shows an example of different literal values in a SELECT statement: SELECT 'string', 1, 1.23, NULL; The previous query has four literals: a string, a number, a floating-point or decimal number, and a NULL. The following screenshot shows the results:

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • Auerbach Publications

    (Publisher)

  This chapter shows how the relational database model is used from an application perspective. There is little point in understanding something such as relational database modeling without seeing it applied in some way, and so this chapter looks at how a database model is accessed by applications. A relational database model contains tables, where rows in tables are accessed using a computer programming language called Structured Query Language (SQL). SQL is a declarative programming language that is used to read data from tables and update data in those tables.

  A declarative programming language describes what you are coding without describing how the problem is solved, which can be difficult to understand, because declarative code requires all instructions in a single complex instruction. Other computer programming languages such as C and FORTRAN are procedural or modular and break software down into separate parts called modules; and then there is Java, which is object-oriented.

  When a database model is well designed, the creation of SQL code can be a simpler process, which also can imply that difficulties encountered in coding of SQL queries can often indicate database model design flaws, or just too much granularity in a data model.

  This chapter covers:

  •  What SQL is •  The origins of SQL •  Different types of queries •  Changing data in tables •  Changing table structure

  4.1    What is SQL?

  SQL is a declarative computer programming language used for accessing row and column values in relational database tables:

  •  SQL is structured in the sense that it is used to access structured data from tables in a relational database. •  Use of the word “language” implies a computer programming language that has specific syntax for performing specific tasks.

  •  SQL is a declarative language consisting of single commands where the database itself does a lot of the work in deciding how to get that information. In other words, SQL tells the database what it wants, and the database does some of the logical assessment, because logic is built into a relational database using keys and relationships between tables. A procedural language, on the other hand, contains blocks of commands, where those blocks of commands are sequences of distinct steps, typically where each successive step is dependent on the result of the previous step (command) in the sequence.

  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