SQL Scenario-Based Questions 🎯

Welcome to the SQL Scenario-Based Questions lesson! In this tutorial, we'll dive deep into SQL (Structured Query Language) by solving various real-world scenarios. By the end of this lesson, you'll have a solid understanding of SQL, making you ready to work on your own projects! 💡

Introduction 📝

SQL is a language used to communicate with and manipulate databases. It's an essential skill for any developer, allowing us to create, read, update, and delete data efficiently. In this lesson, we'll explore SQL concepts from the ground up, and by the end, you'll be able to tackle more complex scenarios. 💡

Getting Started 🎯

Before we dive into the scenarios, let's set up a simple database to work with. In SQL, a database is a collection of tables, and we'll create a table to store books and their details.

sql

CREATE DATABASE booksDB;
USE booksDB;

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    author VARCHAR(255),
    publication_year INT,
    pages INT
);

Now that we have a table, let's insert some data:

sql

INSERT INTO books (title, author, publication_year, pages) VALUES
('The Catcher in the Rye', 'J.D. Salinger', 1951, 277),
('To Kill a Mockingbird', 'Harper Lee', 1960, 323);

Scenario 1 - Selecting Data 🎯

In this first scenario, we'll learn how to select data from our table.

sql

SELECT * FROM books;

Output:

id  title              author         publication_year  pages
1   The Catcher in the Rye  J.D. Salinger  1951  277
2   To Kill a Mockingbird  Harper Lee  1960  323

Scenario 2 - Filtering Data 🎯

Now, let's filter our data based on the author.

sql

SELECT * FROM books WHERE author = 'J.D. Salinger';

Output:

id  title              author         publication_year  pages
1   The Catcher in the Rye  J.D. Salinger  1951  277

Scenario 3 - Ordering Data 🎯

We can order our data by different columns. For example, let's sort our books by their publication year.

sql

SELECT * FROM books ORDER BY publication_year;

Output:

id  title              author         publication_year  pages
1   The Catcher in the Rye  J.D. Salinger  1951  277
2   To Kill a Mockingbird  Harper Lee  1960  323

Scenario 4 - Updating Data 🎯

In this scenario, we'll learn how to update data in our table. Let's update the author of 'The Catcher in the Rye' to 'Ernest Hemingway' (just kidding! 😉).

sql

UPDATE books SET author = 'Ernest Hemingway' WHERE id = 1;

Now, if you run the SELECT * FROM books; query, you'll notice that the author of 'The Catcher in the Rye' has been changed.

Scenario 5 - Deleting Data 🎯

Finally, let's learn how to delete data from our table. For this scenario, let's delete a book with an id of 3 (we don't have one yet, so this will be an example).

sql

DELETE FROM books WHERE id = 3;

Quiz

That's it for our SQL Scenario-Based Questions lesson! Now that you've learned the basics, you can start exploring more complex SQL concepts and real-world projects. Happy coding! 🎉