RDBMS Basics Practice Test: Intermediate and Advanced SQL — Flashcards

Quiz on join expressions, views and transactions, integrity constraints, SQL data types, OLAP and relational algebra.

Intermediate-advanced SQL is a level of proficiency that goes beyond the basics of Structured Query Language (SQL). It involves a deeper understanding of the language and its functions, as well as the ability to write complex queries and manipulate large databases. 

Some intermediate SQL queries include:
Filters: WHERE and HAVING filters
If-then: CASE WHEN and COALESCE
Set operators: UNION, INTERSECT, and EXCEPT
Self joins: Joining a table with itself
Window functions: Applying a calculation to a grouped set of values
WITH: Stringing together subqueries 

Advanced SQL allows you to write more complex queries to derive insights for a business. Some advanced SQL topics include: Extracting information from multiple tables using Joins in SQL, Writing Subqueries, Using Common Table Expressions (CTE), Creating Stored Procedures (SPs), and Using Triggers in SQL.
OLAP cubes are data structures that provide rapid analysis of data. They are multi-dimensional arrays of data that are used in OLAP systems to represent and analyze data from multiple dimensions. For example, a company might use an OLAP cube to summarize financial data by product, by time-period, and by city to compare actual and budget expenses.