Database Design Coding Practice Challenges | CodeNivo

Question 1

7. Retrieve Related Data with JOIN

Accepted Answer

SELECT
    s.student_name,
    c.course_name
FROM enrollments AS e
JOIN students AS s
    ON e.student_id = s.student_id
JOIN courses AS c
    ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_id;

Answer

-- Practice query for Database Relationships: One-to-One, One-to-Many, and Many-to-Many

Question 2

6. Combine Constraints in a Realistic Table

Accepted Answer

CREATE TABLE customers (
    customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(254) NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL
        CONSTRAINT customers_full_name_not_blank
        CHECK (length(trim(full_name)) > 0),
    account_status VARCHAR(20) NOT NULL DEFAULT 'active'
        CONSTRAINT customers_status_valid
        CHECK (account_status IN ('active', 'suspended', 'closed')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO customers (email, full_name)
VALUES ('asha@example.com', 'Asha Sharma');

SELECT
    customer_id,
    email,
    full_name,
    account_status,
    created_at
FROM customers;

Answer

-- Practice query for Constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT

Question 3

10. Practice Exercises

Accepted Answer

CREATE TABLE inventory_items (
    item_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku VARCHAR(40) NOT NULL UNIQUE,
    item_name VARCHAR(120) NOT NULL
        CHECK (length(trim(item_name)) > 0),
    unit_price NUMERIC(10, 2) NOT NULL
        CHECK (unit_price >= 0),
    quantity INTEGER NOT NULL DEFAULT 0
        CHECK (quantity >= 0),
    item_status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (item_status IN ('active', 'inactive'))
);

INSERT INTO inventory_items (sku, item_name, unit_price)
VALUES ('INV-100', 'USB Hub', 799.00);

SELECT item_id, sku, item_name, unit_price, quantity, item_status
FROM inventory_items;

Answer

-- Practice query for Constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT

Question 4

13. Practice Exercises

Accepted Answer

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

CREATE TABLE book_authors (
    book_id INTEGER NOT NULL REFERENCES books(book_id),
    author_id INTEGER NOT NULL REFERENCES authors(author_id),
    PRIMARY KEY (book_id, author_id)
);

SELECT
    a.author_name,
    b.title
FROM book_authors AS ba
JOIN authors AS a
    ON a.author_id = ba.author_id
JOIN books AS b
    ON b.book_id = ba.book_id
ORDER BY a.author_name, b.title;

Answer

-- Practice query for Database Relationships: One-to-One, One-to-Many, and Many-to-Many

Question 5

9. Practice Exercises

Accepted Answer

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date DATE NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

Answer

-- Practice query for First, Second, and Third Normal Forms

Question 6

12. Practice Exercises

Accepted Answer

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id)
);

Answer

-- Practice query for Database Normalization

Question 7

6. Insert Data into Related Tables

Accepted Answer

BEGIN;

INSERT INTO students (student_id, student_name)
VALUES
    (1, 'Asha'),
    (2, 'Ravi');

INSERT INTO courses (course_id, course_name)
VALUES
    (101, 'SQL'),
    (102, 'Python');

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 101),
    (1, 102),
    (2, 101);

COMMIT;

SELECT student_id, course_id
FROM enrollments
ORDER BY student_id, course_id;

Answer

-- Practice query for Database Relationships: One-to-One, One-to-Many, and Many-to-Many

Question 8

7. Full Normalization Example: From One Table to Three

Accepted Answer

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    instructor_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);

SELECT
    s.student_name,
    c.course_name,
    c.instructor_name,
    e.grade
FROM enrollments AS e
JOIN students AS s
    ON s.student_id = e.student_id
JOIN courses AS c
    ON c.course_id = e.course_id
ORDER BY s.student_id, c.course_id;

Answer

-- Practice query for Database Normalization

Question 9

7. Querying the Normalized Design

Accepted Answer

SELECT
    s.student_name,
    c.course_name,
    e.grade
FROM enrollments AS e
JOIN students AS s
    ON s.student_id = e.student_id
JOIN courses AS c
    ON c.course_id = e.course_id
ORDER BY s.student_id, c.course_id;

Answer

-- Practice query for First, Second, and Third Normal Forms

student_name	course_name
Asha	SQL
Asha	Python
Ravi	SQL

Database Design Problems