Projection operation in DBMS Theory - Mini Project: Build & Apply

Projection Operation in Databases

📖 Scenario: You are working with a database of a small bookstore. The database has a table called Books that stores information about each book available in the store.The table Books has these columns: BookID, Title, Author, YearPublished, and Price.

🎯 Goal: You will write SQL queries to perform the projection operation, which means selecting specific columns from the Books table to see only the information you need.

📋 What You'll Learn

Create the Books table with the exact columns and data given

Insert the exact rows of data into the Books table

Write a SQL query to select only the Title and Author columns

Write a SQL query to select only the Title and Price columns

💡 Why This Matters

🌍 Real World

Projection is used in databases to get only the needed information, making data easier to read and faster to process.

💼 Career

Database professionals and developers use projection queries daily to retrieve relevant data efficiently for reports, applications, and analysis.

1

Create the Books table and insert data

Write SQL statements to create a table called Books with columns BookID (integer), Title (text), Author (text), YearPublished (integer), and Price (decimal). Then insert these exact rows into Books: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, 10.99), (2, '1984', 'George Orwell', 1949, 8.99), (3, 'To Kill a Mockingbird', 'Harper Lee', 1960, 7.99).

DBMS Theory

-- Create the Books table and insert the given rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set up the projection columns for Title and Author

Create a SQL query and assign it to a variable called query_title_author that selects only the Title and Author columns from the Books table.

DBMS Theory

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  Author TEXT,
  YearPublished INTEGER,
  Price DECIMAL(5,2)
);

INSERT INTO Books (BookID, Title, Author, YearPublished, Price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, 10.99),
(2, '1984', 'George Orwell', 1949, 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 1960, 7.99);

-- Write a SQL query selecting Title and Author columns and assign it to query_title_author
-- Your code here

Hint

Use SELECT Title, Author FROM Books; and assign it to query_title_author.

3

Write the projection query for Title and Price

Create a SQL query and assign it to a variable called query_title_price that selects only the Title and Price columns from the Books table.

DBMS Theory

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  Author TEXT,
  YearPublished INTEGER,
  Price DECIMAL(5,2)
);

INSERT INTO Books (BookID, Title, Author, YearPublished, Price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, 10.99),
(2, '1984', 'George Orwell', 1949, 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 1960, 7.99);

query_title_author = "SELECT Title, Author FROM Books;"

-- Write a SQL query selecting Title and Price columns and assign it to query_title_price
-- Your code here

Hint

Use SELECT Title, Price FROM Books; and assign it to query_title_price.

4

Complete the projection operation example

Add a comment explaining that these queries show how to perform projection by selecting specific columns from the Books table.

DBMS Theory

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  Author TEXT,
  YearPublished INTEGER,
  Price DECIMAL(5,2)
);

INSERT INTO Books (BookID, Title, Author, YearPublished, Price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, 10.99),
(2, '1984', 'George Orwell', 1949, 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 1960, 7.99);

query_title_author = "SELECT Title, Author FROM Books;"

query_title_price = "SELECT Title, Price FROM Books;"

-- Add a comment explaining these queries show projection operation
-- Your code here

Hint

Add a clear comment describing that the queries show how to select specific columns (projection).

Practice

1. What does the projection operation in a database primarily do?

easy

A. Select specific columns from a table

B. Combine rows from two tables

C. Delete rows from a table

D. Sort rows in ascending order

Solution

1. Step 1: Understand the purpose of projection

   Projection is used to choose certain columns from a table, not rows or sorting.

2. Step 2: Compare options with definition

   Only Select specific columns from a table matches the definition of projection as selecting columns.

3. Final Answer:

   Select specific columns from a table -> Option A

4. Quick Check:

   Projection = Selecting columns [OK]

Hint: Projection means picking columns only, not rows or sorting [OK]

Common Mistakes:

• Confusing projection with selection (which filters rows)
• Thinking projection changes row order
• Mixing projection with join operations

2. Which of the following is the correct SQL syntax for projection to get columns name and age from a table students?

easy

A. SELECT * FROM students WHERE name, age;

B. SELECT name age FROM students;

C. SELECT name AND age FROM students;

D. SELECT name, age FROM students;

Solution

1. Step 1: Recall correct SELECT syntax

   The correct syntax uses SELECT followed by column names separated by commas, then FROM table.

2. Step 2: Check each option

   SELECT name, age FROM students; matches the correct syntax: SELECT name, age FROM students;

3. Final Answer:

   SELECT name, age FROM students; -> Option D

4. Quick Check:

   Correct SELECT syntax = SELECT name, age FROM students; [OK]

Hint: Use commas between columns after SELECT, then FROM table [OK]

Common Mistakes:

• Using * instead of column names for projection
• Using AND instead of comma between columns
• Omitting commas between column names

3. Given a table employees with columns id, name, and salary, what will the result of SELECT name FROM employees; be?

medium

A. All rows with only the name column values

B. All rows with id and name columns

C. All rows with all columns

D. Only the first row with name column

Solution

1. Step 1: Understand the SELECT statement

   SELECT name FROM employees returns all rows but only the 'name' column values.

2. Step 2: Compare options with expected output

   All rows with only the name column values correctly describes the output as all rows with only the 'name' column.

3. Final Answer:

   All rows with only the name column values -> Option A

4. Quick Check:

   Projection selects columns, so only 'name' column shown [OK]

Hint: SELECT column returns all rows but only that column [OK]

Common Mistakes:

• Thinking it returns only one row
• Expecting multiple columns without listing them
• Confusing projection with filtering rows

4. Identify the error in this SQL query for projection: SELECT name age FROM employees;

medium

A. SELECT keyword is misspelled

B. Table name is incorrect

C. Missing comma between column names

D. No error, query is correct

Solution

1. Step 1: Analyze the column list syntax

   Column names must be separated by commas in SELECT statements.

2. Step 2: Check the given query

   The query has 'name age' without a comma, which is a syntax error.

3. Final Answer:

   Missing comma between column names -> Option C

4. Quick Check:

   Columns need commas between them [OK]

Hint: Always separate columns with commas in SELECT [OK]

Common Mistakes:

• Forgetting commas between columns
• Assuming space works as separator
• Ignoring syntax errors in column list

5. You have a table orders with columns order_id, customer_id, product, and quantity. You want to create a new table order_summary that only stores order_id and quantity. Which SQL statement correctly uses projection to do this?

hard

A. CREATE TABLE order_summary SELECT * FROM orders;

B. CREATE TABLE order_summary AS SELECT order_id, quantity FROM orders;

C. INSERT INTO order_summary SELECT customer_id, product FROM orders;

D. CREATE order_summary AS SELECT order_id, quantity FROM orders;

Solution

1. Step 1: Understand creating a new table with selected columns

   To create a new table with specific columns, use CREATE TABLE ... AS SELECT with projection.

2. Step 2: Evaluate each option

   CREATE TABLE order_summary AS SELECT order_id, quantity FROM orders; correctly creates the table with only order_id and quantity columns from orders.

3. Final Answer:

   CREATE TABLE order_summary AS SELECT order_id, quantity FROM orders; -> Option B

4. Quick Check:

   Projection + CREATE TABLE AS SELECT = CREATE TABLE order_summary AS SELECT order_id, quantity FROM orders; [OK]

Hint: Use CREATE TABLE ... AS SELECT columns for projection [OK]

Common Mistakes:

• Using SELECT * instead of specific columns
• Omitting CREATE TABLE keyword
• Selecting wrong columns for the new table