Skip to main content

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.


Labels:

Comments

Post a Comment

Popular posts from this blog

Financial Engineering

Financial Engineering: Key Concepts Financial engineering is a multidisciplinary field that combines financial theory, mathematics, and computer science to design and develop innovative financial products and solutions. Here's an in-depth look at the key concepts you mentioned: 1. Statistical Analysis Statistical analysis is a crucial component of financial engineering. It involves using statistical techniques to analyze and interpret financial data, such as: Hypothesis testing : to validate assumptions about financial data Regression analysis : to model relationships between variables Time series analysis : to forecast future values based on historical data Probability distributions : to model and analyze risk Statistical analysis helps financial engineers to identify trends, patterns, and correlations in financial data, which informs decision-making and risk management. 2. Machine Learning Machine learning is a subset of artificial intelligence that involves training algorithms t...
Post a Comment
Read more

Wholesale Customer Solution with Magento Commerce

The client want to have a shop where regular customers to be able to see products with their retail price, while Wholesale partners to see the prices with ? discount. The extra condition: retail and wholesale prices hasn’t mathematical dependency. So, a product could be $100 for retail and $50 for whole sale and another one could be $60 retail and $50 wholesale. And of course retail users should not be able to see wholesale prices at all. Basically, I will explain what I did step-by-step, but in order to understand what I mean, you should be familiar with the basics of Magento. 1. Creating two magento websites, stores and views (Magento meaning of website of course) It’s done from from System->Manage Stores. The result is: Website | Store | View ———————————————— Retail->Retail->Default Wholesale->Wholesale->Default Both sites using the same category/product tree 2. Setting the price scope in System->Configuration->Catalog->Catalog->Price set drop-down to...
Post a Comment
Read more

How to Prepare for AI Driven Career

  Introduction We are all living in our "ChatGPT moment" now. It happened when I asked ChatGPT to plan a 10-day holiday in rural India. Within seconds, I had a detailed list of activities and places to explore. The speed and usefulness of the response left me stunned, and I realized instantly that life would never be the same again. ChatGPT felt like a bombshell—years of hype about Artificial Intelligence had finally materialized into something tangible and accessible. Suddenly, AI wasn’t just theoretical; it was writing limericks, crafting decent marketing content, and even generating code. The world is still adjusting to this rapid shift. We’re in the middle of a technological revolution—one so fast and transformative that it’s hard to fully comprehend. This revolution brings both exciting opportunities and inevitable challenges. On the one hand, AI is enabling remarkable breakthroughs. It can detect anomalies in MRI scans that even seasoned doctors might miss. It can trans...
Post a Comment
Read more