11  DQL

Data Query Language (DQL) is used for performing queries on the data within schema objects to retrieve data from the database

Data Query Language (DQL)

11.1 DQL

The syntax of SQL is designed to resemble English, making it more accessible to users. It allows the manipulation of a set of data records with a single statement.

Core SQL consists of statements that are made up of keywords, operators, values, and the names of system or user objects and functions. Statements are often concluded by a semicolon, but it is optional.

SELECT SName, ClassID, Attendance FROM Student WHERE Attendance < 90;

Here, the tokens SELECT, FROM and WHERE are keywords.

The names SName, ClassID, and Attendance are object names.

The sign < is an operator.

The number 90 is a value.

In the following sections, we will focus on the DQL aspect to demonstrate how to perform data wrangling in various environments (R, Python, Excel, Access, SAS).

11.2 Important Notes

  • A semicolon (;) at the end of an SQL statement is not always strictly necessary, especially in single-statement scenarios in most databases.

  • It is generally considered good practice to use a semicolon (;). This ensures that the SQL statements are clearly terminated, avoids ambiguity, and maintains compatibility across different DBMSs and environments.

  • The order of SQL clauses (see next section) is crucial for the correct execution and interpretation of an SQL query.

  • SQL keywords such as SELECT, FROM, WHERE, JOIN and others are case-insensitive across most SQL implementations.

  • The case sensitivity of identifiers such as table names, column names, and aliases depends on the database system and its configuration.

  • String literals are always case-sensitive. The contents of a string must match exactly, including the case, when used in conditions or comparisons.

  • Single quotes are used for string literals in SQL. They denote a specific string of text that is being used in a query.

  • Double quotes are used for delimited identifiers, such as table names, column names, and other object names. This allows you to include special characters, spaces, or case sensitivity within identifiers.

  • Single line comments start with --. Anything after this will be ignored.

  • Multiline comments start with /* and end with */. Anything between these two symbols will be ignored.

11.3 DQL for different environments: Outline

  • R: Read or create a dataframe > Load the specific library > Run DQL

  • Python: Read or create a pandas Data.Frame > Load the specific library > Run DQL

  • Microsoft Excel: Use Excel with Visual Basic Macros OR create the ODBC connection

  • Microsoft Access: Load the Microsoft Access DB > Run DQL

  • SAS: Read or create a SAS dataset > Run DQL using PROC SQL