Basic SQL Knowledge Test For Beginner

photo: pexel

Are you new to SQL and preparing for your first job that requires SQL knowledge? 

This blog post is designed specifically for beginners like you who are just starting out on their journey with SQL. Whether you're a student, a recent graduate, or someone looking to transition into a tech role, this guide will help you build a strong foundation in SQL concepts, understand common SQL queries, and prepare you for the types of SQL tasks you might encounter in your first job. We'll break down complex concepts into easy-to-understand steps, provide practical examples, and give you tips to succeed in interviews and on the job. Let's get you job-ready with SQL!

Below is the table structure for the `customer`, `user_account`, and `contact` tables in a more visual format:

Below are the SQL statements to create the `customer`, `user_account`, and `contact` tables according to the schema provided:

1. `customer` Table

```sql

CREATE TABLE customer (

    id INT PRIMARY KEY,

    customer_name VARCHAR(255) NOT NULL,

    city_id INT,

    customer_address VARCHAR(255),

    contact_person VARCHAR(255),

    email VARCHAR(128),

    phone VARCHAR(128),

    is_active INT

);

```

2. `user_account` Table

```sql

CREATE TABLE user_account (

    id INT PRIMARY KEY,

    first_name VARCHAR(64) NOT NULL,

    last_name VARCHAR(64) NOT NULL,

    user_name VARCHAR(128) NOT NULL,

    password VARCHAR(255) NOT NULL,

    email VARCHAR(128),

    phone VARCHAR(128),

    is_active INT

);

```

3. `contact` Table

```sql

CREATE TABLE contact (

    id INT PRIMARY KEY,

    user_account_id INT,

    customer_id INT,

    contact_type_id INT,

    contact_outcome_id INT,

    additional_comment VARCHAR(255),

    initiated_by_customer INT,

    initiated_by_user INT,

    FOREIGN KEY (user_account_id) REFERENCES user_account(id),

    FOREIGN KEY (customer_id) REFERENCES customer(id)

);

```

Here’s the SQL query to find all pairs of customers and agents (users) who have been in contact more than once. For each pair, the query displays the user ID, first name, last name, customer ID, customer name, and the number of their contacts. The result is ordered by the user ID in ascending order:

```sql

SELECT 

    ua.id AS user_id,

    ua.first_name,

    ua.last_name,

    c.id AS customer_id,

    c.customer_name,

    COUNT(ct.id) AS contact_count

FROM 

    contact ct

JOIN 

    user_account ua ON ct.user_account_id = ua.id

JOIN 

    customer c ON ct.customer_id = c.id

GROUP BY 

    ua.id, ua.first_name, ua.last_name, c.id, c.customer_name

HAVING 

    COUNT(ct.id) > 1

ORDER BY 

    ua.id ASC;

```

Explanation:

- JOIN: The `contact` table is joined with `user_account` and `customer` tables based on the `user_account_id` and `customer_id` respectively.

- GROUP BY: Groups the results by `user_id`, `first_name`, `last_name`, `customer_id`, and `customer_name` to aggregate the contacts.

- HAVING COUNT(ct.id) > 1: Filters out the pairs that have been in contact more than once.

- ORDER BY ua.id ASC: Orders the result by `user_id` in ascending order.

Here are two slightly more complex SQL query questions along with their detailed explanations:

Question 1: Find the Top 3 Customers by Total Purchase Amount

You have a `customer` table and an `invoice` table. The `invoice` table contains information about customer purchases, including the `customer_id`, `total_amount`, and `invoice_date`. Your task is to find the top 3 customers who have spent the most, along with their total amount spent and the number of purchases they made. Order the result by total amount spent in descending order.

Tables:

- customer: Contains customer information.

  - `id`: INT (Primary Key)

  - `customer_name`: VARCHAR(255)

- invoice: Contains invoice information.

  - `customer_id`: INT (Foreign Key referencing `customer.id`)

  - `total_amount`: DECIMAL(10,2)

  - `invoice_date`: DATE

Query:

```sql

SELECT 

    c.id AS customer_id,

    c.customer_name,

    SUM(i.total_amount) AS total_spent,

    COUNT(i.id) AS total_purchases

FROM 

    customer c

JOIN 

    invoice i ON c.id = i.customer_id

GROUP BY 

    c.id, c.customer_name

ORDER BY 

    total_spent DESC

LIMIT 3;

```

Explanation:

- JOIN: Combines the `customer` and `invoice` tables based on the `customer_id`.

- SUM(i.total_amount): Calculates the total amount each customer has spent.

- COUNT(i.id): Counts the number of invoices (purchases) for each customer.

- GROUP BY c.id, c.customer_name: Groups the results by customer.

- ORDER BY total_spent DESC: Orders the results by the total amount spent in descending order.

- LIMIT 3: Limits the result to the top 3 customers.

---

Question 2: Find All Users Who Have Never Made a Purchase

You have a `user_account` table and an `order` table. Some users might have registered but never made a purchase. Your task is to find all users who have never placed an order, displaying their user ID, first name, last name, and email.

Tables:

- user_account: Contains user information.

  - `id`: INT (Primary Key)

  - `first_name`: VARCHAR(64)

  - `last_name`: VARCHAR(64)

  - `email`: VARCHAR(128)

- order: Contains order information.

  - `user_id`: INT (Foreign Key referencing `user_account.id`)

  - `order_date`: DATE

Query:

```sql

SELECT 

    ua.id AS user_id,

    ua.first_name,

    ua.last_name,

    ua.email

FROM 

    user_account ua

LEFT JOIN 

    order o ON ua.id = o.user_id

WHERE 

    o.user_id IS NULL;

```

Explanation:

- LEFT JOIN: Joins the `user_account` table with the `order` table to include all users, even if they haven't placed an order.

- WHERE o.user_id IS NULL: Filters out only those users who have never made an order (i.e., no matching record in the `order` table).

- ua.id, ua.first_name, ua.last_name, ua.email: Selects the relevant user information for the result.

You can practice SQL without installing a database server by using online SQL platforms like:

1. SQLFiddle (www.sqlfiddle.com): Allows you to write and execute SQL queries in an interactive online environment.

2. DB Fiddle (www.db-fiddle.com): Similar to SQLFiddle, supporting multiple database systems for practice.

3. LeetCode (www.leetcode.com): Offers SQL challenges to solve directly in your browser.

4. HackerRank (www.hackerrank.com/domains/sql): Provides SQL problems with an in-browser SQL editor for practice.

5. Mode Analytics SQL Tutorial (www.mode.com/sql-tutorial/): Offers an interactive SQL tutorial where you can write and test queries.

These tools allow you to practice SQL queries without needing to install anything on your local machine.

Incremental Data Loading from Databases for ETL

 

pexel

Let first discuss what is incremental loading into the data warehouse by ETL from different data sources including databases.

Incremental Loading into Data Warehouses:

Incremental loading is crucial for efficiently updating data warehouses without reprocessing all data. It involves adding only new or modified data since the last update. Key aspects include:

1. Efficiency: Incremental loading reduces processing time and resource usage by only handling changes.

2. Change Detection: Techniques like timestamp comparison or change data capture (CDC) identify modified data.

3. Data Consistency: Ensure consistency by maintaining referential integrity during incremental updates.

4. Performance: Proper indexing, partitioning, and parallel processing enhance performance during incremental loads.

5. Logging and Auditing: Logging changes ensures traceability and facilitates error recovery in incremental loading processes.

Incremental Loading Explained

In contrast to a full load, which transfers the entire dataset every time, an incremental load focuses on only the new or modified data since the last successful load. This optimized approach offers several benefits:

• Reduced Processing Time: Less data translates to faster load times, improving overall efficiency.
• Lower Resource Consumption: Smaller data transfers mean less strain on system resources like network bandwidth and storage.
• More Frequent Updates: With quicker loads, you can update your target database more frequently, keeping data fresher for analytics and reporting.

Identifying Changes

To isolate changes, various techniques are employed depending on the database type:

• Timestamps: Many databases offer built-in timestamp columns that automatically track record creation or modification times. Incremental loads can filter based on these timestamps to identify new or updated data.
• Log Capture: Some databases maintain change logs that record insert, update, and delete operations. Incremental loads can process these logs to determine changes.
• Sequence Numbers: Certain databases assign unique sequence numbers to each record. By tracking the highest sequence number processed in the previous load, you can identify newly added data.
• Triggers: Triggers are stored procedures that execute automatically in response to specific database events like insertions or updates. These triggers can be used to capture changes and prepare them for incremental loading.

Example: E-commerce Data Warehouse

Imagine an e-commerce business with a data warehouse storing customer orders. A full load would transfer all order data every night, even if only a few new orders were placed.

An incremental approach would:

1. Track the timestamp of the last successful load.
2. On subsequent loads, query for orders with timestamps after the recorded mark.
3. Only these new orders would be transferred and loaded into the data warehouse.

Database-Specific Techniques

Here's a glimpse into how different database types might handle incremental loads:

• MySQL: Utilizes timestamps or binary logs for change data capture.
• PostgreSQL: Leverages triggers or logical decoding for capturing changes.
• SQL Server: Change Tracking or CDC (Change Data Capture) features can be used.
• Oracle: Change Data Capture features can be used.

By implementing incremental loading, you can streamline data movement between databases, ensure timely updates, and optimize resource utilization.

Let's discuss each of them now.

Streamlined Data Updates: Incremental Loading in SQL Server

When automating data movement with ETL or ELT processes, focusing solely on changed data since the last run significantly improves efficiency. This approach, known as incremental loading, stands in contrast to full loads that transfer the entire dataset each time. To implement incremental loading, we need a reliable method to pinpoint the modified data.

Traditionally, "high water mark" values are used. This involves tracking a specific column in the source table, such as a datetime field or a unique integer column, to identify the latest processed value.

Introducing Temporal Tables (SQL Server 2016 onwards):

For SQL Server 2016 and later versions, a powerful feature called temporal tables offers a more comprehensive solution. These tables are system-versioned, meaning they automatically maintain a complete history of data modifications. The database engine seamlessly stores this historical data in a separate table, accessible through queries with the FOR SYSTEM_TIME clause. This functionality allows applications to interact with historical data without requiring manual intervention.

Earlier Versions and Alternatives:

For pre-2016 SQL Server instances, Change Data Capture (CDC) provides an alternative, albeit less user-friendly approach. CDC necessitates querying a separate change table, and tracks modifications using log sequence numbers instead of timestamps.

Choosing the Right Technique:

The optimal method hinges on the data type. Temporal tables excel at handling dimension data, which can evolve over time. Fact tables, typically representing immutable transactions like sales, don't benefit from system version history. In these cases, a transaction date column serves effectively as the watermark value. For instance, the Sales.Invoices and Sales.InvoiceLines tables in the Wide World Importers OLTP database leverage the LastEditedWhen field (defaulting to sysdatetime()) for this purpose.

Incremental Loading in Oracle Databases

Oracle offers several methods for implementing incremental loads, allowing you to efficiently update your target tables:

1. Change Data Capture (CDC) Tools:

• Oracle GoldenGate: This powerful tool captures changes in real-time from source databases (including Oracle and non-Oracle) and replicates them to target databases. GoldenGate can be configured to identify only new or modified data for efficient incremental loads.

2. Time-Based Filtering:

• Leverage built-in Oracle data types like TIMESTAMP or LAST_MODIFIED to track record creation or update timestamps. Incremental load queries can filter the source data based on timestamps greater than the one captured during the last successful load.

3. High Water Marks (HWMs):

• Implement a separate table or mechanism to store a "high-water mark" (HWM), which represents the identifier (like a sequence number or maximum value) of the last record processed in the previous load. Subsequent loads can query for data with identifiers exceeding the stored HWM.

4. Triggers:

• Create database triggers that fire upon data modifications (insert, update, delete) in the source table. These triggers can be designed to capture changes and prepare them for incremental loads by inserting them into a temporary staging table. The incremental load process can then focus on this staging table.

5. Oracle Data Integrator (ODI):

• Utilize ODI, a data integration tool from Oracle, to build data flows that can handle incremental loads. ODI provides pre-built components and functionalities for identifying changes, transforming data, and performing incremental updates.

Choosing the Right Method

The optimal approach depends on various factors like:

• Source and Target Database Types: Compatibility between source and target systems influences the available techniques.
• Data Volume and Change Frequency: High-volume or frequently changing data might benefit from CDC tools for real-time updates.
• Performance Requirements: Techniques like triggers can introduce overhead, so consider the impact on overall performance.
• Technical Expertise: Some methods require advanced knowledge of Oracle features or specialized tools like GoldenGate.

By understanding these methods and carefully considering your specific scenario, you can establish an efficient incremental loading strategy for your Oracle databases.

Incremental Loading Strategies in PostgreSQL and MySQL

Optimizing data pipelines often involves focusing on changes since the last update. This approach, known as incremental loading, significantly improves efficiency compared to full loads that transfer the entire dataset repeatedly. Here's how PostgreSQL and MySQL tackle incremental loading:

PostgreSQL:

• Timestamps: Leverage built-in timestamp data types like TIMESTAMP or LAST_UPDATED to track record creation or modification times. Incremental loads can filter the source data based on timestamps exceeding the one captured during the last successful load. This is a simple and widely used approach.
• Logical Decoding: PostgreSQL offers a powerful feature called logical decoding. It allows you to capture changes (inserts, updates, deletes) happening in real-time and replicate them to other databases. This provides a robust mechanism for identifying and processing only the modified data.
• Triggers: You can create database triggers that fire upon data modifications in the source table. These triggers can be designed to capture changes and prepare them for incremental loads by inserting them into a temporary staging table. The incremental load process can then target this staging table for efficient updates.

Choosing the Right Method in PostgreSQL:

The optimal approach depends on your specific needs. Timestamps offer a straightforward solution for basic scenarios. Logical decoding excels at real-time change capture for complex data pipelines. Triggers provide greater flexibility but might introduce additional processing overhead.

MySQL:

• Timestamps: Similar to PostgreSQL, you can utilize timestamp data types like TIMESTAMP or LAST_MODIFIED for tracking data changes. Incremental loads can then filter the source data based on timestamps greater than the one captured during the last successful load.
• Binary Logs: MySQL maintains binary logs that record all database statements (including data manipulation). You can utilize tools or libraries to parse these logs and extract information about changes for incremental loading purposes. This approach offers a comprehensive view of data modifications but may require additional setup and processing overhead.

Choosing the Right Method in MySQL:

Timestamps provide a familiar and efficient solution for many use cases. Binary logs offer a more granular view of changes but require additional configuration and processing. Consider the complexity of your data pipelines and the need for real-time updates when selecting the most suitable method.

By understanding these techniques in PostgreSQL and MySQL, you can effectively implement incremental loading strategies to streamline your data pipelines and optimize resource utilization.