From Beginner to Pro: Essential SQL Query Examples

Managing data is a crucial aspect of running any website or application, including blogs. And when it comes to managing data, SQL (Structured Query Language) is one of the most popular and powerful tools available.

SQL allows you to create, manipulate, and query data stored in databases, and it is used by countless websites and applications around the world.

In this article, we’ll explore some SQL query examples with the Postgresql database.

We’ll use a sample dataset consisting of three tables:

• posts (id, title, content, author_id, created_at, updated_at)
• authors (id, name, email)
• comments (id, post_id, author_id, content, created_at)

These tables represent some of the core entities in a blogging platform, and we’ll use them to demonstrate various SQL queries that you might use to manage your blog’s data.

We’ll start by looking at some basic queries for creating tables, inserting data, and querying data.

Then we’ll dive deeper into more advanced SQL features like JOINs, filtering, and sorting, showing how they can be used to extract insights from your blog’s data. By the end of t

The query for Create Tables:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- The author_id column is an integer that is required and is intended to reference the id column of the authors table.


CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL,
  author_id INTEGER NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- The post_id column is an integer that is required and is intended to reference the id column of the posts table.
-- The author_id column is an integer that is required and is intended to reference the id column of the authors table.

-- Mapp those FOREIGN keys
ALTER TABLE posts
ADD CONSTRAINT posts_author_id_fkey
FOREIGN KEY (author_id) REFERENCES authors(id);

ALTER TABLE comments
ADD CONSTRAINT comments_author_id_fkey
FOREIGN KEY (author_id) REFERENCES authors(id);

ALTER TABLE comments
ADD CONSTRAINT comments_post_id_fkey
FOREIGN KEY (post_id) REFERENCES posts(id);

Insert some example data into the tables:

Insert authors details:

INSERT INTO authors (name, email) VALUES 
  ('John Smith', 'john.smith@example.com'),
  ('Jane Doe', 'jane.doe@example.com'),
  ('Bob Johnson', 'bob.johnson@example.com'),
  ('Sarah Lee', 'sarah.lee@example.com');

Insert post details

INSERT INTO posts (title, content, author_id, created_at, updated_at) VALUES 
  ('My First Post', 'This is my first post. Hello world!', 1, NOW(), NOW()),
  ('A Review of My Favorite Book', 'I recently read my favorite book and I want to share my thoughts on it.', 2, NOW(), NOW()),
  ('10 Tips for Beginner Programmers', 'As a seasoned programmer, I want to share some tips for those just starting out.', 1, NOW(), NOW()),
  ('The Importance of Sleep for Productivity', 'Sleep is essential for productivity. Here''s why you should prioritize it.', 4, NOW(), NOW());

Insert comments details:

INSERT INTO comments (post_id, author_id, content, created_at) VALUES 
  (1, 3, 'Great first post!', NOW()),
  (1, 2, 'Welcome to the blogging world!', NOW()),
  (2, 1, 'I love that book too!', NOW()),
  (3, 4, 'Thanks for the tips!', NOW()),
  (4, 2, 'I completely agree. Sleep is so important.', NOW());

1. Get all the posts with their author names:

SELECT posts.*, authors.name AS author_name
FROM posts
JOIN authors ON posts.author_id = authors.id;

Output:

2. Get the post with the most comments:

SELECT posts.*, COUNT(comments.id) AS comment_count
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id
ORDER BY comment_count DESC
LIMIT 1;

Output:

3. Get all the comments made by a specific author:

SELECT comments.*, authors.name AS author_name
FROM comments
JOIN authors ON comments.author_id = authors.id
WHERE authors.name = 'Jane Doe';

Output:

4. Get the authors who have made more than 1 comment:

SELECT authors.*, COUNT(comments.id) AS comment_count
FROM authors
JOIN comments ON authors.id = comments.author_id
GROUP BY authors.id
HAVING COUNT(comments.id) > 1;

5. Get the posts that were created in the last 7 days:

SELECT *
FROM posts
WHERE created_at >=created_at - INTERVAL '7 DAYS'

Output:

6. Get the number of blog posts published each month:

SELECT 
TO_CHAR(created_at,'month') as month,
COUNT(id) AS post_count
FROM posts
GROUP BY month;

Output:

7. Get the total number of comments made on each post:

SELECT posts.id, posts.title, COUNT(comments.id) AS comment_count
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id;

8. Let's play with joining in the above query:

In our above example data, we have all posts with at least one comment.

Now, create one post without comment details

INSERT INTO posts (title, content, author_id, created_at, updated_at) VALUES 
('Post without comment', 'This Post doesnt has any comments', 1, NOW(), NOW());

Run the “Get the total number of comments made on each post” query again:

1. With LEFT JOIN: Get all posts with total comments and sort by post

SELECT posts.id, posts.title, COUNT(comments.id) AS comment_count
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id
ORDER BY  posts.id

2. With INNER JOIN: Get all posts with total comments which have at least one comment

SELECT posts.id, posts.title, COUNT(comments.id) AS comment_count
FROM posts
INNER JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id
ORDER BY  posts.id

If you notice the above two queries, join the same tables and give different result sets.

Left Join → Retrieve all posts and then calculate the count of comments.

Inner Join → Retrieve posts that have comments and calculate the count of comments.

Based on the requirement we can choose the Left or Inner Join.

we’ve only scratched the surface of what’s possible with SQL, I hope that this post has given you a solid foundation for working with SQL.

As you continue to explore this powerful language, you’ll find countless ways to apply it to your own unique needs and goals.

Follow me for more updates!