SQL Practice Problems 🎯

Welcome to the SQL Practice Problems lesson! In this tutorial, we'll work together to enhance your SQL skills with practical examples and exercises. By the end of this lesson, you'll be able to query, manipulate, and analyze data like a pro! 💡

What is SQL? 📝

SQL (Structured Query Language) is a standard language used to communicate with and manipulate databases. It allows us to create, modify, and retrieve data stored in a database, as well as perform various operations on it.

Tables and Schemas 📝

Before we dive into SQL queries, let's talk about tables and schemas. A table is similar to a spreadsheet, where data is organized in rows and columns. A schema is a collection of tables that together make up a database.

Basic SQL Commands 📝

Creating a Table

To create a table, use the CREATE TABLE statement followed by the table name and its columns.

sql

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    gender VARCHAR(10)
);

Inserting Data

To insert data into a table, use the INSERT INTO statement.

sql

INSERT INTO students (id, name, age, gender)
VALUES (1, 'Alice', 25, 'Female');

Selecting Data

To retrieve data from a table, use the SELECT statement.

sql

SELECT * FROM students;

Updating Data

To update data in a table, use the UPDATE statement.

sql

UPDATE students SET age = 26 WHERE id = 1;

Deleting Data

To delete data from a table, use the DELETE FROM statement.

sql

DELETE FROM students WHERE id = 1;

Advanced SQL Concepts 📝

JOINs

Joining tables allows us to combine data from multiple tables based on a common column. There are four types of JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Aggregate Functions

Aggregate functions, like COUNT, SUM, MIN, MAX, and AVG, allow us to perform calculations on groups of data.

Subqueries

Subqueries are nested queries that return a result set, which can then be used in another query.

Practice Problems 🎯

Now, it's time to test your skills! Try solving these practice problems on your own.

Problem 1

Create a table named courses with columns id, name, description, and price. Insert some sample data.

sql

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2)
);

INSERT INTO courses (id, name, description, price)
VALUES (1, 'Web Development', 'Learn to code and build websites', 999.99);

-- Add more rows here...

Problem 2

Retrieve all courses and their descriptions.

sql

SELECT name, description FROM courses;

Problem 3

Update the description of the course with ID 1.

sql

UPDATE courses SET description = 'An in-depth look at web development, including front-end and back-end technologies' WHERE id = 1;

Problem 4

Find the total number of courses in the database.

sql

SELECT COUNT(*) FROM courses;

Problem 5

Find the average price of all courses.

sql

SELECT AVG(price) FROM courses;

Keep practicing, and soon you'll be an SQL master! 💡

Happy coding! ✅