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

Need a 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

Need a 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

Need a 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

Need a hint?

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