String quoting and concatenation differences in SQL - Mini Project: Build & Apply

String Quoting and Concatenation Differences in SQL

📖 Scenario: You are working with a small database of book titles and authors. You want to create a new column that combines the author's name and the book title into a single string for display purposes.

🎯 Goal: Build a SQL query that correctly uses string quoting and concatenation to combine author names and book titles into one string with a separator.

📋 What You'll Learn

Create a table called books with columns id (integer), author (text), and title (text).

Insert three rows with exact data: (1, 'Jane Austen', 'Pride and Prejudice'), (2, 'Mark Twain', 'Adventures of Huckleberry Finn'), (3, 'George Orwell', '1984').

Create a variable or alias called separator with the value ', by '.

Write a SELECT query that concatenates title, separator, and author into a new column called full_title.

Use single quotes for string literals and the correct concatenation operator for standard SQL.

💡 Why This Matters

🌍 Real World

Combining text fields like author names and titles is common in reporting and user interfaces to display meaningful information.

💼 Career

Understanding string quoting and concatenation in SQL is essential for database querying, report generation, and data transformation tasks.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), author (text), and title (text). Then insert these exact rows: (1, 'Jane Austen', 'Pride and Prejudice'), (2, 'Mark Twain', 'Adventures of Huckleberry Finn'), and (3, 'George Orwell', '1984').

SQL

-- Create the books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows. Use single quotes for text values.

2

Define a separator string

Create a variable or alias called separator with the string value ', by ' using single quotes.

SQL

CREATE TABLE books (
  id INTEGER,
  author TEXT,
  title TEXT
);

INSERT INTO books (id, author, title) VALUES
  (1, 'Jane Austen', 'Pride and Prejudice'),
  (2, 'Mark Twain', 'Adventures of Huckleberry Finn'),
  (3, 'George Orwell', '1984');

-- Define separator variable
-- Your code here

Need a hint?

Use a WITH clause to define a temporary named value called separator with the string ', by '.

3

Write the SELECT query with concatenation

Write a SELECT query that uses the books table and the separator alias to create a new column called full_title. Concatenate title, separator.sep, and author using the standard SQL concatenation operator.

SQL

CREATE TABLE books (
  id INTEGER,
  author TEXT,
  title TEXT
);

INSERT INTO books (id, author, title) VALUES
  (1, 'Jane Austen', 'Pride and Prejudice'),
  (2, 'Mark Twain', 'Adventures of Huckleberry Finn'),
  (3, 'George Orwell', '1984');

WITH separator AS (SELECT ', by ' AS sep)
-- Write SELECT query to concatenate title, separator.sep, and author
-- Your code here

Need a hint?

Use the || operator to join strings in standard SQL. Use a comma to join books and separator in the FROM clause.

4

Complete the query with ordering

Add an ORDER BY clause to the SELECT query to sort the results by id in ascending order.

SQL

CREATE TABLE books (
  id INTEGER,
  author TEXT,
  title TEXT
);

INSERT INTO books (id, author, title) VALUES
  (1, 'Jane Austen', 'Pride and Prejudice'),
  (2, 'Mark Twain', 'Adventures of Huckleberry Finn'),
  (3, 'George Orwell', '1984');

WITH separator AS (SELECT ', by ' AS sep)
SELECT title || separator.sep || author AS full_title
FROM books, separator
-- Add ORDER BY id ascending
-- Your code here

Need a hint?

Use ORDER BY id ASC to sort results by the id column in ascending order.