Overview of Databases and SQL

What is a Database?

Imagine your school keeps a record of all the students, their grades, and teachers’ notes in a big notebook. A database is like a notebook but on a computer. It’s a place to store and organize lots of information so that it’s easy to find and manage.

For example:

• A library database might store information about books, authors, and borrowers.
• A hospital database might store patients’ details, doctors, and treatments.

What is SQL?

SQL (Structured Query Language) is like a magic spell that helps you talk to the database. It lets you ask questions like:

• “Can you show me all the students in Grade 5?”
• “Can you add a new book to the library?”
• “Can you update the price of apples in the grocery store?”

SQL is powerful and easy to learn. Let’s explore how it works with examples!

SQL Basics with Examples

1. Creating a Table

A table in a database is like a spreadsheet. It has rows and columns. Each row is a record, and each column is a piece of information.

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Grade INT,
    Age INT
);

This creates a table called Students with four columns: ID, Name, Grade, and Age.

2. Inserting Data into a Table

Now, let’s add some students to the Students table.

INSERT INTO Students (ID, Name, Grade, Age)
VALUES (1, 'Alice', 5, 10),
       (2, 'Bob', 6, 11),
       (3, 'Charlie', 5, 10);

3. Reading Data (SELECT)

To see the data in the Students table, we use the SELECT command.

SELECT * FROM Students;

Output:

ID   Name      Grade   Age
1    Alice     5       10
2    Bob       6       11
3    Charlie   5       10

You can also filter data:

SELECT Name FROM Students WHERE Grade = 5;

Output:

Name
Alice
Charlie

4. Updating Data

What if Alice moves to Grade 6? You can update her record.

UPDATE Students
SET Grade = 6
WHERE Name = 'Alice';

5. Deleting Data

If Bob leaves the school, you can remove his record.

DELETE FROM Students
WHERE Name = 'Bob';

Practice Questions

1. Create a table called Books with columns: BookID, Title, Author, and Price.
2. Insert three records into the Books table.
3. Write a query to display all books written by “J.K. Rowling”.
4. Update the price of a book that Title is “Harry Potter” to $25.
5. Delete the book with BookID = 2.

Answers: 1.

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(50),
    Price DECIMAL(5, 2)
);

INSERT INTO Books (BookID, Title, Author, Price)
VALUES (1, 'Harry Potter', 'J.K. Rowling', 20.00),
       (2, 'The Hobbit', 'J.R.R. Tolkien', 15.00),
       (3, '1984', 'George Orwell', 12.00);

SELECT * FROM Books WHERE Author = 'J.K. Rowling';

UPDATE Books SET Price = 25.00 WHERE Title = 'Harry Potter';

DELETE FROM Books WHERE BookID = 2;

Interview Questions

Beginner:

1. What is a database?
   Answer: A database is a collection of organized data stored on a computer that can be easily accessed, managed, and updated.
2. What does SQL stand for?
   Answer: SQL stands for Structured Query Language.
3. Write an SQL query to fetch all records from a table named Employees.
   Answer:

SELECT * FROM Employees;

Intermediate:

4. Explain the difference between DELETE and TRUNCATE.
   Answer:
   • DELETE: Removes specific rows based on a condition. It can be rolled back.
   • TRUNCATE: Removes all rows from a table. It cannot be rolled back.
5. Write a query to count the number of students in Grade 5.
   Answer:

SELECT COUNT(*) FROM Students WHERE Grade = 5;

Advanced:

6. What is the difference between WHERE and HAVING?
   Answer:
   • WHERE: Filters rows before grouping (used with SELECT, UPDATE, DELETE).
   • HAVING: Filters groups after grouping (used with GROUP BY).
7. Write a query to find the second-highest salary from an Employees table.
   Answer:

SELECT MAX(Salary) AS SecondHighest
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

SQL is the language of databases. It’s simple, powerful, and used everywhere. With SQL, you can create tables, add data, read it, update it, and delete it. Practicing these basics will help you become a database wizard!