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.

Python Django with GCP

 

Building a Scalable Application with Python, Django, DRF, and Google Cloud Platform

Here's a breakdown of how you can leverage Python, Django, DRF (Django REST Framework), and Google Cloud Platform (GCP) to build robust and scalable applications:

1. Project Setup:

• Python and Django: Ensure you have Python and Django installed. You can use virtual environments to manage dependencies for your project.
• GCP Project: Create a project on GCP and enable billing if necessary.
• Cloud SDK: Install the Google Cloud SDK to interact with GCP services from your command line.

2. Building the Application:

• Django Project: Use django-admin startproject to create a new Django project.
• Django App: Create a Django app for your main functionality using python manage.py startapp <app_name>.
• Models: Define your data models in the app's models.py file.
• Serializers: Create serializers using DRF to represent your models as JSON for the API.
• Views: Implement views in your app's views.py file using Django and DRF to handle API requests and responses.
• URL Patterns: Define URL patterns in your project's urls.py file to map URLs to specific views.

3. Leveraging GCP Services:

• Cloud SQL: Use Cloud SQL for your application's database. It offers managed MySQL or PostgreSQL instances with automatic scaling.
• Cloud Storage: Utilize Cloud Storage for storing static files like images or media. Django integrates with Cloud Storage through libraries like django-storages.
• Cloud Functions: For serverless functions triggered by events, consider Cloud Functions. They're ideal for background tasks or API endpoints with minimal compute needs.
• Cloud Pub/Sub: For asynchronous communication between services, leverage Cloud Pub/Sub, a message queuing service.
• Cloud Monitoring: Monitor your application's health and performance using Cloud Monitoring. It provides detailed insights and alerts for potential issues.

4. Deployment:

• App Engine: Consider deploying your Django application on Google App Engine. It offers standard and flexible environments, allowing you to choose the level of control over your application's runtime.
• Cloud Run: Alternatively, use Cloud Run, a serverless platform for deploying containerized applications. It allows you to scale your application automatically based on traffic.
• Compute Engine: For full control over your deployment, utilize Compute Engine to create virtual machines for running your Django application.

5. Security:

• Cloud IAM: Implement Cloud IAM (Identity and Access Management) to control access to GCP resources.
• Cloud KMS: Use Cloud KMS (Key Management Service) to manage your application's secrets securely.

Additional Considerations:

• Authentication and Authorization: Implement mechanisms like Django REST Framework JWT or OAuth for user authentication and authorization within your API.
• Caching: Consider using a caching service like Memcache or Redis to improve application performance. Cloud Memorystore offers managed solutions for both.
• Load Balancing: Utilize Cloud Load Balancing to distribute traffic across multiple instances of your application for scalability and high availability.

Resources:

• Getting started with Django on GCP: https://cloud.google.com/python/django/appengine
• Django REST Framework: https://www.django-rest-framework.org/tutorial/quickstart/
• Google Cloud Platform Documentation: https://cloud.google.com/
• Check my other tutorials related to this topic

By combining the power of Python, Django, DRF, and GCP, you can build robust, scalable, and secure web applications with a focus on efficient resource utilization.