SQL Essentials: Definition, Querying, and Functions

SQL is the standard language used to work with relational databases. According to Database Systems Design, Implementation and Management by Carlos Coronel, Steven Morris, Peter Rob (z-lib.org), a database language must let users define structures, manipulate data, and perform complex queries with minimal effort. SQL meets these needs and is portable across relational database systems.

1. SQL as a Database Language

SQL serves more than one purpose in a database environment. In this lesson, the focus is on two major uses described in Database Systems Design, Implementation and Management by Carlos Coronel, Steven Morris, Peter Rob (z-lib.org):

• Data Definition Language (DDL) for creating and defining database objects.
• Query and function support for retrieving data and producing useful derived values.

2. DDL Capabilities in SQL

The source explains that SQL includes commands to create database objects such as tables, indexes, and views, and also commands to define access rights to those objects. This is the role of SQL as a data definition language.

Key DDL Uses

• Create schemas to define a database schema.
• Create tables to store data in rows and columns.
• Create indexes to support data access and performance.
• Create views to present selected data in a virtual table form.
• Define access rights to control who can use database objects.

These capabilities are specifically identified in Database Systems Design, Implementation and Management by Carlos Coronel, Steven Morris, Peter Rob (z-lib.org) as part of SQL’s DDL function.

3. Querying with SQL

SQL is also used to retrieve and transform stored data into useful information. The source notes that users often need more than raw stored values. Queries can be designed not only to select data, but also to calculate or derive new values from existing attributes.

For example, a query may need information that is not stored exactly as entered in the database. In such cases, SQL functions help create the needed result from existing numeric, date, or string values.

4. SQL Functions

Database Systems Design, Implementation and Management by Carlos Coronel, Steven Morris, Peter Rob (z-lib.org) explains that SQL functions use a numerical, date, or string value. That value may be:

• A constant or literal written directly in the command
• An attribute stored in a table

The source also states that a function may appear anywhere in an SQL statement where a value or attribute can be used.

Major Function Categories Mentioned

• Arithmetic functions
• Trigonometric functions
• String functions
• Date functions
• Time functions

Although many function types exist, this lesson emphasizes date/time functions and derived-value use in queries.

5. Date and Time Functions

The source states that all SQL-standard DBMSs support date and time functions. It also explains that date functions take one parameter, usually of a date or character data type, and return a value that may be character, numeric, or date type.

Date and time functions are useful when queries must work with values such as dates, times, intervals, or formatted results. They help users answer questions involving time-based data without storing every possible variation as a separate attribute.

Why Date/Time Functions Matter

• They let queries work directly with calendar and time values.
• They help transform stored date data into meaningful output.
• They support reporting and analysis when time is an important part of the question.

6. Derived-Value Functions in Queries

A derived value is a value produced from existing data rather than stored directly. The source explains that sometimes the needed data element is not present exactly as such in the database, so an SQL function must derive it from an existing attribute.

This means SQL queries can do more than display stored fields. They can calculate, convert, or transform data into a form that better answers a user’s question.

Characteristics of Derived Values

• They come from existing attributes.
• They are produced during query execution.
• They may use numeric, date, or string inputs.

7. Connecting DDL and Query Functions

To use SQL effectively, it is important to distinguish between defining the database and querying the database:

• DDL creates and defines structures such as schemas, tables, indexes, views, and access rights.
• Queries and functions retrieve data and generate useful derived results from stored values.

Together, these uses make SQL both a structural language and an operational language. As described in Database Systems Design, Implementation and Management by Carlos Coronel, Steven Morris, Peter Rob (z-lib.org), SQL supports the full process of defining objects and turning raw data into useful information.

8. Lesson Summary

• SQL is a standard relational database language.
• Its DDL capabilities include creating schemas, tables, indexes, views, and defining access rights.
• SQL queries can use functions to transform stored data into meaningful results.
• Functions can use constants or table attributes and can appear wherever a value can be used in an SQL statement.
• Date and time functions are supported in SQL-standard DBMSs and are important for time-based queries.
• Derived-value functions allow users to produce needed information even when it is not stored directly.