Practice Questions — Setting Up MySQL and Your First Query

CREATE DATABASE IF NOT EXISTS college;
USE college;

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
    course_id INT,
    course_name VARCHAR(60),
    duration_months INT
);

INSERT INTO courses VALUES
    (1, 'Python Fundamentals',    4),
    (2, 'MySQL Masterclass',      3),
    (3, 'Full Stack Web Dev',     6);

SELECT * FROM courses;
-- Expected output:
-- +-----------+---------------------+-----------------+
-- | course_id | course_name         | duration_months |
-- +-----------+---------------------+-----------------+
-- |         1 | Python Fundamentals |               4 |
-- |         2 | MySQL Masterclass   |               3 |
-- |         3 | Full Stack Web Dev  |               6 |
-- +-----------+---------------------+-----------------+

CREATE DATABASE IF NOT EXISTS school;
USE school;
DROP TABLE IF EXISTS students;

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    class INT,
    marks INT
);

INSERT INTO students VALUES
    (1, 'Aarav Sharma', 10, 87),
    (2, 'Priya Iyer',   10, 92),
    (3, 'Rohan Verma',  11, 78),
    (4, 'Ananya Reddy', 11, 95),
    (5, 'Vikram Singh', 12, 65);

SELECT name, marks FROM students;
-- Output:
-- +----------------+-------+
-- | name           | marks |
-- +----------------+-------+
-- | Aarav Sharma   |    87 |
-- | Priya Iyer     |    92 |
-- | Rohan Verma    |    78 |
-- | Ananya Reddy   |    95 |
-- | Vikram Singh   |    65 |
-- +----------------+-------+

CREATE DATABASE IF NOT EXISTS store;
USE store;

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    price INT
);

INSERT INTO products VALUES (1, 'Pen', 10);

SELECT * FROM products;
-- Fixes made:
-- 1. Added semicolons at the end of each statement.
-- 2. Added commas between column definitions.
-- 3. Added the missing opening parenthesis after CREATE TABLE products.
-- 4. Replaced double quotes with single quotes around 'Pen'.
-- 5. (Good practice) Added IF NOT EXISTS / IF EXISTS for re-runnability.
-- 6. Uppercased keywords for readability.

CREATE DATABASE IF NOT EXISTS hr;
USE hr;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT,
    name VARCHAR(50),
    department VARCHAR(30),
    salary INT
);

INSERT INTO employees VALUES
    (101, 'Kavya Nair',    'HR',        60000),
    (102, 'Arjun Mehta',   'IT',        85000),
    (103, 'Sneha Patel',   'Finance',   72000),
    (104, 'Rahul Joshi',   'IT',        95000),
    (105, 'Meera Gupta',   'Marketing', 65000),
    (106, 'Ishaan Rao',    'Sales',     58000);

SELECT name, salary FROM employees;
-- Output:
-- +--------------+--------+
-- | name         | salary |
-- +--------------+--------+
-- | Kavya Nair   |  60000 |
-- | Arjun Mehta  |  85000 |
-- | Sneha Patel  |  72000 |
-- | Rahul Joshi  |  95000 |
-- | Meera Gupta  |  65000 |
-- | Ishaan Rao   |  58000 |
-- +--------------+--------+

-- Step 1: Which databases exist?
SHOW DATABASES;

-- Step 2: Switch to the one we care about.
USE ecommerce;

-- Step 3: What tables are inside?
SHOW TABLES;

-- Step 4: For each table, see the structure.
DESCRIBE orders;
DESCRIBE customers;
DESCRIBE products;

-- Step 5: Count rows in a specific table.
SELECT COUNT(*) AS total_orders FROM orders;

-- Step 6: Peek at the first few rows.
SELECT * FROM orders LIMIT 5;

-- This sequence — SHOW DATABASES, USE, SHOW TABLES, DESCRIBE,
-- COUNT(*), LIMIT 5 — is the universal "orient yourself in a new DB" workflow.

/*
   Bookstore seed script.
   Purpose: reset the books table with a clean 5-row dataset.
   Safe to run multiple times — uses IF NOT EXISTS / IF EXISTS.
*/

-- 1. Create the database if missing.
CREATE DATABASE IF NOT EXISTS bookstore;

-- 2. Switch to it.
USE bookstore;

-- 3. Reset the books table for a clean slate.
DROP TABLE IF EXISTS books;

-- 4. Define the schema.
CREATE TABLE books (
    book_id INT,
    title VARCHAR(100),
    author VARCHAR(60),
    price INT
);

-- 5. Seed data.
INSERT INTO books VALUES
    (1, 'Let Us C',                   'Yashwant Kanetkar', 350),
    (2, 'DBMS Concepts',               'Henry F. Korth',    600),
    (3, 'Head First Java',             'Kathy Sierra',      500),
    (4, 'Clean Code',                  'Robert C. Martin',  450),
    (5, 'Python Crash Course',         'Eric Matthes',      550);

-- 6. Verify.
SELECT COUNT(*) AS total_books FROM books;
SELECT * FROM books;

-- Expected:
-- total_books = 5
-- 5 rows returned from SELECT *.