Advanced SQL Topics
Aggregate Functions in SQL
SQL provides several aggregate functions to assist with data summarization. In this article we explore the usage of SUM, AVG, COUNT, MIN and MAX.
Classifying Results with SQL CASE Statements
SQL includes a CASE statement that allows you to return varying results based upon the evaluation of expressions. You can use a CASE statement anywhere within a SQL statement that you would normally include an expression. CASE statements are often found in SELECT, UPDATE and DELETE statements as well as WHERE and IN clauses.
Combining Query Results with the UNION Command
SQL’s UNION command allows you to combine the results of two or more database queries that are not necessarily linked through a database relationship. For example, imagine that you have a school database and wish to use it to create a master contact list for all students, faculty and staff. Looking at your database, you discover that the records corresponding to each of these constituencies appears in separate database tables.
Interested in the definition of concatenation? Find out in the About Databases glossary!
Controlling Data Access with Views
Database views allow you to easily reduce the complexity of the end user experience and limit their ability to access data contained in database tables by limiting the data presented to the end user. Essentially, a view uses the results of a database query to dynamically populate the contents of an artificial database table.
Counting Values in a Database Table with the SQL COUNT Function
The COUNT() function in SQL allows you to count database records based upon a variety of criteria. You can use it to count all records in a table, count unique values in a column or count the number of times records occur that meet certain criteria. This tutorial takes a brief look at each of these scenarios.
Joining Multiple Tables with SQL Inner Join Statements
You can use SQL JOIN statements to combine data from three or more tables. In an earlier article, we took a look at using inner joins and outer joins to combine data from two different tables. In many cases, youÂ’ll want to take this a step further and combine data from three or more tables. Let's take a look at the SQL statements that allow you to accomplish this goal for an inner join.
Retrieving Data from Multiple Tables with SQL Joins
SQL join statements allow you to combine data from two or more tables in your query results. Learn how to leverage this powerful technology to supercharge your database queries.
Selecting Data Within Ranges in SQL
The Structured Query Language (SQL)provides database users with the ability to create customized queries to extract information from databases. In an earlier article, we explored extracting information from a database using SQL SELECT queries. Let's expand upon that discussion and explore how you can perform advanced queries to retrieve data...
Summarizing Data with CUBE and ROLLUP
SQL's CUBE and ROLLUP commands allow for the efficient summarization of data.
Using Self-Joins in SQL
Did you know that you can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table? Let's take a look at an example.
Using the GROUP BY Clause to Group SQL Query Results
You may use basic SQL queries to retrieve data from a database but this often doesn’t provide enough intelligence to meet business requirements. SQL also provides you with the ability to group query results based upon row-level attributes in order to apply aggregate functions using the GROUP BY clause.
Data Control Language (DCL)
The Data Control Language (DCL) is a subset of the Structured Query Lanaguge (SQL) that allows database administrators to configure security access to relational databases. It complements the Data Definition Language (DDL), which is used to add and delete database objects, and the Data Manipulation Language (DML), which is used to retrieve,...