SQL HackerRank Problems 🎯

Welcome to our SQL tutorial designed to help you solve HackerRank problems! In this comprehensive guide, we'll explore SQL from the ground up, making it easy for both beginners and intermediates. Let's dive right in!

What is SQL? 📝

SQL, or Structured Query Language, is a language used to communicate with and manipulate databases. It allows us to create, read, update, and delete data efficiently.

Getting Started 💡

Before we dive into the problems, let's set up a basic SQL environment. We'll use SQLite, a popular, lightweight SQL database.

sql

-- Create a new SQLite database
SQLite version 3.x> .open mydb.sqlite3
SQLite version 3.x> .tables

Basic SQL Commands 💡

Creating a Table

sql

-- Create a table named 'students'
SQLite version 3.x> CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);

Inserting Data

sql

-- Insert data into the 'students' table
SQLite version 3.x> INSERT INTO students (name, age) VALUES ('John Doe', 25);

Querying Data

sql

-- Query data from the 'students' table
SQLite version 3.x> SELECT * FROM students;

Updating Data

sql

-- Update John Doe's age to 26
SQLite version 3.x> UPDATE students SET age = 26 WHERE name = 'John Doe';

Deleting Data

sql

-- Delete John Doe's record
SQLite version 3.x> DELETE FROM students WHERE name = 'John Doe';

HackerRank Problems 🎯

Now that we've covered the basics, let's solve some SQL problems from HackerRank!

Problem 1: SQL Basics 💡

Problem Statement

Create a table named 'products' with columns 'id', 'product_name', 'price', and 'quantity'. Insert some data and then perform various queries.

Solution

sql

-- Create the 'products' table
SQLite version 3.x> CREATE TABLE products (id INTEGER PRIMARY KEY, product_name TEXT, price REAL, quantity INTEGER);

-- Insert data into the 'products' table
SQLite version 3.x> INSERT INTO products (product_name, price, quantity) VALUES ('Product A', 10.99, 50);
SQLite version 3.x> INSERT INTO products (product_name, price, quantity) VALUES ('Product B', 15.99, 25);
SQLite version 3.x> INSERT INTO products (product_name, price, quantity) VALUES ('Product C', 7.99, 75);

-- Query data from the 'products' table
SQLite version 3.x> SELECT * FROM products;

-- Update the price of Product A
SQLite version 3.x> UPDATE products SET price = 11.99 WHERE product_name = 'Product A';

-- Delete Product C
SQLite version 3.x> DELETE FROM products WHERE product_name = 'Product C';

Problem 2: SQL Joins 💡

Problem Statement

Given two tables 'students' and 'courses', join them based on the 'student_id'.

Solution

sql

-- Create the 'students' table
SQLite version 3.x> CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, student_id INTEGER);
SQLite version 3.x> INSERT INTO students (name, student_id) VALUES ('John Doe', 1);
SQLite version 3.x> INSERT INTO students (name, student_id) VALUES ('Jane Smith', 2);

-- Create the 'courses' table
SQLite version 3.x> CREATE TABLE courses (id INTEGER PRIMARY KEY, course_name TEXT, student_id INTEGER);
SQLite version 3.x> INSERT INTO courses (course_name, student_id) VALUES ('Programming 101', 1);
SQLite version 3.x> INSERT INTO courses (course_name, student_id) VALUES ('Data Structures', 2);

-- Join the 'students' and 'courses' tables
SQLite version 3.x> SELECT students.name, courses.course_name FROM students INNER JOIN courses ON students.student_id = courses.student_id;

Quiz 💡

Problem 1

Given the 'products' table, which command will update the price of 'Product B' to 16.99?

Problem 2

Given the 'students' and 'courses' tables, which command will join them based on the 'student_id' and return the students' names and their respective courses?