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

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

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

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

Hint

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

Practice

1. In SQL, which of the following is the correct way to write the text string Hello?

easy

A. Use double quotes: "Hello"

B. Use single quotes: 'Hello'

C. Use backticks: `Hello`

D. No quotes needed: Hello

Solution

1. Step 1: Understand SQL string quoting rules

   In SQL, text strings must be enclosed in single quotes to be recognized as string literals.

2. Step 2: Identify correct quoting for text

   Double quotes are for identifiers like column names, backticks are MySQL-specific for identifiers, and no quotes means a column or keyword.

3. Final Answer:

   Use single quotes: 'Hello' -> Option B

4. Quick Check:

   Text strings = single quotes [OK]

Hint: Text strings always use single quotes in SQL [OK]

Common Mistakes:

• Using double quotes for text strings
• Using backticks for text strings
• Not using any quotes for text

2. Which of the following SQL statements correctly concatenates the strings 'Hello' and 'World'?

easy

A. SELECT CONCAT('Hello', 'World');

B. SELECT 'Hello' + 'World';

C. SELECT 'Hello' || 'World';

D. SELECT 'Hello' & 'World';

Solution

1. Step 1: Know SQL string concatenation operators

   Standard SQL uses || to join strings. Some databases use CONCAT() function.

2. Step 2: Check each option

   SELECT 'Hello' + 'World'; uses + which is not standard SQL for strings. SELECT 'Hello' || 'World'; uses || which is correct in standard SQL but not supported in all databases. SELECT CONCAT('Hello', 'World'); uses CONCAT() which is valid and widely supported in many databases. SELECT 'Hello' & 'World'; uses & which is invalid.

3. Final Answer:

   SELECT CONCAT('Hello', 'World'); -> Option A

4. Quick Check:

   CONCAT() is widely supported for string concatenation [OK]

Hint: Use CONCAT() function for portable string concatenation [OK]

Common Mistakes:

• Using + for string concatenation
• Using & for string concatenation
• Confusing CONCAT() with operator

3. What is the output of this SQL query?

SELECT 'SQL' || ' ' || 'Rocks' AS message;

medium

A. SQL|| ||Rocks

B. 'SQL Rocks'

C. 'SQL' ' ' 'Rocks'

D. SQL Rocks

Solution

1. Step 1: Understand string concatenation with ||

   The operator || joins strings without adding extra quotes in the output.

2. Step 2: Evaluate the concatenation

   'SQL' + ' ' + 'Rocks' becomes the string SQL Rocks without quotes in the result display.

3. Final Answer:

   SQL Rocks -> Option D

4. Quick Check:

   Concatenated string shows without quotes in output [OK]

Hint: Concatenation joins strings; output shows text without quotes [OK]

Common Mistakes:

• Expecting quotes around output string
• Confusing concatenation operator with plus sign
• Misreading output as literal code

4. Identify the error in this SQL statement:

SELECT "Hello" || 'World';

medium

A. Double quotes around Hello cause a syntax error

B. Using || operator is invalid

C. Single quotes around World cause error

D. No error, statement is correct

Solution

1. Step 1: Understand quoting rules for strings and identifiers

   Double quotes are for identifiers (like column names), not string literals. Using them around text causes error.

2. Step 2: Check the statement

   "Hello" is treated as an identifier, which likely does not exist, causing an error. Single quotes around 'World' are correct. || operator is valid.

3. Final Answer:

   Double quotes around Hello cause a syntax error -> Option A

4. Quick Check:

   Double quotes = identifiers, not strings [OK]

Hint: Use single quotes for text, double quotes for names [OK]

Common Mistakes:

• Using double quotes for string literals
• Thinking || operator is invalid
• Confusing single and double quotes

5. You want to create a SQL query that returns the full name by joining first name and last name columns with a space in between. Which of the following is the best and most portable way to do this?

hard

A. SELECT first_name + ' ' + last_name FROM users;

B. SELECT first_name || ' ' || last_name FROM users;

C. SELECT CONCAT(first_name, ' ', last_name) FROM users;

D. SELECT first_name & ' ' & last_name FROM users;

Solution

1. Step 1: Understand string concatenation portability

   Different databases support different concatenation methods: || is standard but not supported everywhere; CONCAT() is widely supported.

2. Step 2: Evaluate options for portability

   SELECT first_name + ' ' + last_name FROM users; uses + which works in some but not all databases. SELECT first_name || ' ' || last_name FROM users; uses || which is standard but not supported in all (e.g., SQL Server). SELECT CONCAT(first_name, ' ', last_name) FROM users; uses CONCAT() which is supported in most modern databases. SELECT first_name & ' ' & last_name FROM users; uses & which is invalid.

3. Final Answer:

   SELECT CONCAT(first_name, ' ', last_name) FROM users; -> Option C

4. Quick Check:

   CONCAT() is most portable for string join [OK]

Hint: Use CONCAT() for portable string joining across databases [OK]

Common Mistakes:

• Using + operator assuming it works everywhere
• Using || operator without checking database support
• Using & operator which is invalid