🗄️
Database Language · Arena

SQL

96 challenges · 0 mastered

0%
Arena cleared
0/96 mastered Let's go 🚀
Answer

SQL (Structured Query Language) is a standard language used to communicate with relational databases. It allows you to create, read, update, and delete data stored in tables.

Code Example
SELECT * FROM employees;
💡 Simple Analogy

SQL is like giving instructions to a librarian — you tell them what book (data) you want, and they fetch it for you.

Answer

A Database is an organized collection of structured data stored electronically. A relational database stores data in tables with rows and columns.

Code Example
CREATE DATABASE company_db;
💡 Simple Analogy

A database is like a filing cabinet. Each drawer is a table, each folder is a row, and each label is a column.

Answer

A Table is a collection of related data organized in rows (records) and columns (fields). Each table represents a specific entity like employees, products, or orders.

Code Example
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary DECIMAL(10,2)
);
💡 Simple Analogy

A table is like a spreadsheet — columns are headers (name, salary) and rows are individual records.

Answer

A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.

Code Example
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(100)
);
💡 Simple Analogy

A Primary Key is like an Aadhaar number — no two people can have the same one.

Answer

A Foreign Key is a column that creates a relationship between two tables. It references the Primary Key of another table to enforce referential integrity.

Code Example
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
💡 Simple Analogy

A Foreign Key is like a reference letter — it points to someone (row) in another table.

Answer

DELETE removes specific rows and can use WHERE clause. TRUNCATE removes all rows but keeps the table structure. DROP removes the entire table including its structure.

Code Example
DELETE FROM employees WHERE department = 'HR';
TRUNCATE TABLE temp_logs;
DROP TABLE old_records;
💡 Simple Analogy

DELETE = erase specific pages from a notebook. TRUNCATE = tear out all pages but keep the notebook. DROP = throw the entire notebook away.

Answer

WHERE filters rows before grouping. HAVING filters groups after the GROUP BY clause is applied. HAVING is used with aggregate functions.

Code Example
-- WHERE: filter rows
SELECT * FROM employees WHERE salary > 50000;

-- HAVING: filter groups
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
💡 Simple Analogy

WHERE filters individual students. HAVING filters entire classrooms based on average marks.

Answer

NULL represents a missing or unknown value. It is not the same as zero or an empty string. NULL comparisons require IS NULL or IS NOT NULL.

Code Example
SELECT * FROM employees WHERE phone IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- This will NOT work as expected:
-- SELECT * FROM employees WHERE phone = NULL;
💡 Simple Analogy

NULL means 'I don't know the value' — not zero, not blank, just unknown.

Answer

CRUD stands for Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE) — the four basic operations on database data.

Code Example
-- Create
INSERT INTO employees (name, salary) VALUES ('Manav', 75000);

-- Read
SELECT * FROM employees;

-- Update
UPDATE employees SET salary = 80000 WHERE name = 'Manav';

-- Delete
DELETE FROM employees WHERE name = 'Manav';
💡 Simple Analogy

CRUD covers everything you can do with data: add it, view it, change it, or remove it.

Answer

SELECT retrieves data from one or more tables. It can return specific columns, filtered rows, sorted results, and aggregated data.

Code Example
SELECT name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;
💡 Simple Analogy

SELECT is like asking a question: 'Show me all engineers sorted by salary.'

Answer

INSERT adds new rows into a table.

Code Example
-- Single row
INSERT INTO products (name, price) VALUES ('Laptop', 75000);

-- Multiple rows
INSERT INTO products (name, price) VALUES
  ('Mouse', 500),
  ('Keyboard', 1200),
  ('Monitor', 15000);
💡 Simple Analogy

INSERT is like adding a new contact to your phone.

Answer

UPDATE modifies existing rows in a table. Always use a WHERE clause to avoid updating all rows accidentally.

Code Example
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';
💡 Simple Analogy

UPDATE is like editing a saved contact's phone number.

Answer

DISTINCT removes duplicate rows from the result. GROUP BY groups rows for aggregate calculations like COUNT, SUM, AVG.

Code Example
-- DISTINCT: unique departments
SELECT DISTINCT department FROM employees;

-- GROUP BY: count per department
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
💡 Simple Analogy

DISTINCT says 'show me unique values.' GROUP BY says 'group them and calculate something.'

Answer

WHERE filters rows based on a condition. Only rows that satisfy the condition are included in the result.

Code Example
SELECT * FROM products WHERE price > 1000;
SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;
💡 Simple Analogy

WHERE is like a filter on an e-commerce site — show only products above ₹1000.

Answer

ORDER BY sorts the result set by one or more columns. Default is ascending (ASC). Use DESC for descending.

Code Example
SELECT name, salary FROM employees
ORDER BY salary DESC;

SELECT * FROM products
ORDER BY category ASC, price DESC;
💡 Simple Analogy

ORDER BY is like sorting search results by price — low to high or high to low.

Answer

LIKE is used for pattern matching in WHERE clause. % matches any sequence of characters. _ matches exactly one character.

Code Example
-- Names starting with 'A'
SELECT * FROM employees WHERE name LIKE 'A%';

-- Names ending with 'kumar'
SELECT * FROM employees WHERE name LIKE '%kumar';

-- Exactly 5-letter names
SELECT * FROM employees WHERE name LIKE '_____';
💡 Simple Analogy

LIKE is like using wildcard search — 'A%' finds Amit, Anita, Arjun.

Answer

IN allows matching a column against a list of values instead of writing multiple OR conditions.

Code Example
-- Instead of:
SELECT * FROM employees WHERE dept = 'HR' OR dept = 'IT' OR dept = 'Finance';

-- Use IN:
SELECT * FROM employees WHERE dept IN ('HR', 'IT', 'Finance');
💡 Simple Analogy

IN is a shorthand for 'match any of these values.'

Answer

BETWEEN filters values within a range (inclusive of both endpoints).

Code Example
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
💡 Simple Analogy

BETWEEN is like saying 'give me everything from 40K to 80K, including both.'

Answer

Aggregate Functions perform calculations on a set of values and return a single result. Common ones are COUNT, SUM, AVG, MIN, and MAX.

Code Example
SELECT COUNT(*) AS total_employees FROM employees;
SELECT AVG(salary) AS avg_salary FROM employees;
SELECT MAX(salary) AS highest_salary FROM employees;
SELECT SUM(amount) AS total_sales FROM orders;
💡 Simple Analogy

Aggregate functions answer questions like 'how many?', 'what's the total?', 'what's the average?'

Answer

GROUP BY groups rows sharing the same value in specified columns and is used with aggregate functions to get per-group results.

Code Example
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
💡 Simple Analogy

GROUP BY is like organizing students by class and then counting how many are in each.

Answer

COUNT(*) counts all rows including those with NULL values. COUNT(column) counts only rows where that specific column is NOT NULL.

Code Example
-- Counts all rows (including NULLs)
SELECT COUNT(*) FROM employees;

-- Counts only rows where phone is not NULL
SELECT COUNT(phone) FROM employees;
💡 Simple Analogy

COUNT(*) counts everyone. COUNT(phone) counts only those who have a phone number.

Answer

No. WHERE cannot filter on aggregate results. Use HAVING to filter after GROUP BY. WHERE filters rows before aggregation, HAVING filters groups after.

Code Example
-- ❌ This will fail:
-- SELECT department FROM employees WHERE COUNT(*) > 5 GROUP BY department;

-- ✅ Correct:
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
💡 Simple Analogy

WHERE works before grouping, HAVING works after grouping.

Answer

Common data types include INT (whole numbers), VARCHAR (variable-length text), DECIMAL (precise numbers), DATE (dates), BOOLEAN (true/false), and TEXT (long text).

Code Example
CREATE TABLE products (
  id INT,
  name VARCHAR(100),
  price DECIMAL(10,2),
  is_active BOOLEAN,
  created_at DATE,
  description TEXT
);
💡 Simple Analogy

Data types define what kind of value a column can hold — number, text, date, etc.

Answer

Constraints are rules applied to columns to enforce data integrity. Common constraints are PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT.

Code Example
CREATE TABLE employees (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  age INT CHECK (age >= 18),
  status VARCHAR(20) DEFAULT 'active'
);
💡 Simple Analogy

Constraints are like rules on a form — 'this field is required', 'this must be unique.'

Answer

UNIQUE ensures that all values in a column are different. Unlike PRIMARY KEY, a table can have multiple UNIQUE columns, and UNIQUE allows one NULL (in most databases).

Code Example
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  phone VARCHAR(15) UNIQUE
);
💡 Simple Analogy

No two users can have the same email or phone number.

Answer

DEFAULT provides a fallback value when no value is supplied during INSERT.

Code Example
CREATE TABLE orders (
  id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
💡 Simple Analogy

If you don't specify a status, it automatically becomes 'pending.'

Answer

CHECK ensures that values in a column satisfy a specific condition.

Code Example
CREATE TABLE employees (
  id INT PRIMARY KEY,
  age INT CHECK (age >= 18 AND age <= 65),
  salary DECIMAL(10,2) CHECK (salary > 0)
);
💡 Simple Analogy

CHECK is like form validation — 'age must be 18 or above.'