9 Embedded SQL / Online Analytical Processing

Question 1

What are the two main categories of SQL operations?

Answer 1

DDL and DML

Question 2

What does DDL stand for?

Answer 2

Data Definition Language

Question 3

What is an example of a DDL command to create a table?

Answer 3

CREATE TABLE students (id INT, name VARCHAR(50), age INT)

Question 4

What command would you use to add a column to a table in DDL?

Answer 4

ALTER TABLE students ADD COLUMN email VARCHAR(100)

Question 5

What does DML stand for?

Answer 5

Data Manipulation Language

Question 6

What is the purpose of DML?

Answer 6

To manage and manipulate data within database tables

Question 7

Provide an example of a DML command to insert data.

Answer 7

INSERT INTO students (id, name, age) VALUES (1, ‘John Doe’, 20)

Question 8

What is Standalone SQL?

Answer 8

SQL commands run directly on a database outside of any other programming language

Question 9

What is Static SQL?

Answer 9

Queries written in application code before it runs and do not change during execution

Question 10

What is an example of Static SQL in Python?

Answer 10

query = ‘SELECT * FROM employees WHERE department = ‘Sales’’

Question 11

Define Dynamic SQL.

Answer 11

SQL statements built or modified during the execution of the program

Question 12

What is Embedded SQL?

Answer 12

SQL statements incorporated in a high-level programming language

Question 13

List some languages that incorporate Embedded SQL.

Answer 13

• C
• C++
• Java
• Python
• Haskell

Question 14

What is one benefit of using Embedded SQL?

Answer 14

Efficiency due to precompiling static SQL queries

Question 15

How does Embedded SQL facilitate error handling?

Answer 15

Uses the error handling mechanism of the host language to manage SQL execution errors

Question 16

What is SQL injection?

Answer 16

A security vulnerability that allows an attacker to inject malicious SQL code into a query

Question 17

How can SQL injection be prevented?

Answer 17

Using parameterized queries or prepared statements

Question 18

What does a parameterized query do?

Answer 18

Prevents changes to the query’s structure and handles escaping and sanitizing parameters

Question 19

What is a disadvantage of Embedded SQL?

Answer 19

Increased complexity as developers need to know both SQL and the host language

Question 20

What is a limitation regarding portability in Embedded SQL?

Answer 20

SQL code embedded in one host language may not be portable to another host language

Question 21

What is required before the actual source code is compiled in languages like C and Java?

Answer 21

Precompilation of SQL statements embedded in the code

Question 22

What is OLAP used for?

Answer 22

To test ideas or hypotheses and find insights from data using long, complex queries.

Question 23

What type of system is OLAP part of?

Answer 23

Analytical information systems.

Question 24

Why is OLAP considered hypothesis-based?

Answer 24

Because users start with a question and explore data across dimensions to test it.

Question 25

What is OLTP used for?

Answer 25

Daily transactions like insert, update, delete (e.g., online orders).

Question 26

What are the differences between OLAP and OLTP?

Answer 26

OLAP: for analysis, big data, fewer users, read-only; OLTP: for operations, small data, many users, read/write.

Question 27

What does ROLAP use for storage?

Answer 27

Relational databases like SQL tables.

Question 28

What are ROLAP strengths and weaknesses?

Answer 28

Scales well, uses less space, but slow for complex queries.

Question 29

What does MOLAP use for storage?

Answer 29

Multidimensional cubes with pre-aggregated data.

Question 30

What are MOLAP strengths and weaknesses?

Answer 30

Fast queries, but uses more space and doesn’t scale well.

Question 31

What is HOLAP?

Answer 31

A hybrid of ROLAP and MOLAP—balances speed, scale, and space.

Question 32

What is a data warehouse?

Answer 32

A central storage for cleaned and consistent data used for analysis.

Question 33

What are data warehouse characteristics?

Answer 33

Read-heavy, slower due to large size, integrates data from multiple sources.

Question 34

What is the star schema?

Answer 34

A central fact table connected to dimension tables in a star shape.

Question 35

What is stored in the fact table?

Answer 35

Numerical data (measures) like sales, profits, and foreign keys.

Question 36

What do dimension tables contain?

Answer 36

Descriptive attributes like date, store, product.

Question 37

How is snowflake schema different from star?

Answer 37

Snowflake normalizes dimension tables into smaller ones—slower but uses less space.

Question 38

What is a data cube?

Answer 38

A multidimensional view of fact data across dimensions like time, product, location.

Question 39

What does slicing a cube do?

Answer 39

Picks one value from a dimension to reduce cube to a 2D view.

Question 40

What does dicing a cube do?

Answer 40

Selects a sub-cube by filtering multiple dimensions.

Question 41

What does pivoting do in a cube?

Answer 41

Changes which dimensions appear as rows, columns, or layers (reorients the view).

Question 42

What is drill-down in a data cube?

Answer 42

Zooms in to more detailed data (e.g., year → month → day).

Question 43

What is Approach 1 to building a data cube?

Answer 43

Collect data directly from external systems into the cube.

Question 44

What is Approach 2 to building a data cube?

Answer 44

Use ETL: extract raw data, transform it, and load it into the data warehouse.

Question 45

What is Approach 3 to building a data cube?

Answer 45

Same as Approach 2, but tables are organized as OLAP-friendly (star/snowflake schema).

Question 46

What is the core concept of OLAP per Codd’s rules?

Answer 46

Multidimensional view of data.

Question 47

What does transparency mean in Codd’s rules?

Answer 47

Users don’t need to know internal system workings.

Question 48

What is dynamic sparse matrix handling?

Answer 48

Efficient handling of missing or sparse data.

Question 49

What does FASMI stand for in OLAP?

Answer 49

Fast, Analysis, Shared, Multi-dimensional, Information.

Question 50

What does GROUPING SETS do?

Answer 50

Runs multiple groupings (e.g., by brand, by year, total) in one SQL query.

Question 51

What does GROUP BY CUBE do?

Answer 51

Computes all possible groupings (power set) of columns—like doing every group by combination.