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.
