Basic SQL

✕

  1. Table Creation

    Question 1 of 5

      Connect your PostgreSQL database in DBeaver and:
      1. Create database named sample_db.
      2. Inside sample_db, create all tables from lecture slide with proper data types.
      3. Add records in each table as shown in lecture slide.
      4. Refresh database in DBeaver and validate table structure.
      5. Run SELECT * query on each table to validate inserted records.

  2. Table with Constraints

    Question 2 of 5

    • Drop tables created in previous exercise
      • Create all tables from lecture slide with below constraints:
      1. Customer - id => Serial, PRIMARY KEY - registered_date => DEFAULT value now - name => NOT NULL - phone => UNIQUE, CHECK: LENGTH = 10 - address => DEFAULT value NEPAL
      2. Employee - id => Serial, PRIMARY KEY - name => NOT NULL - joined_date => DEFAULT value now - department => CHECK: department IN ('HR', 'SALES', 'TECH') - salary => CHECK: salary > 0 - manager_id => FOREIGN KEY referencing employee(id)
      3. Product - id => Serial, PRIMARY KEY - name => NOT NULL - price => CHECK: price > 0
      4. Transaction - id => Serial, PRIMARY KEY - customer_id => FOREIGN KEY referencing customer(id) - cashier_id => FOREIGN KEY referencing employee(id) - product_id => FOREIGN KEY referencing product(id) - product_qty => CHECK: quantity > 0 - txn_date => DEFAULT value now

  3. Alter Table

    Question 3 of 5

    • Remove column discount_price from transaction.
    • Add column total_price in transaction with proper data type & constraint.
    • Rename column cashier_id to employee_id in transaction
    • Add mobile_number in employee making it unique.
    • Rename table customer to customer_info.
    • Add column brand in product with default value Generic.
    • Remove is_vip from customer_info, add customer_type with default value Regular.

  4. Restoring Data

    Question 4 of 5

    • Download DDL Script and DML scripts.
    • Open both scripts in text editor and understand the commands.
    • Run commands from DDL.sql to create tables, DML.sql to add data in your database.
    • Validate table structure and data with DBeaver GUI.
    • Make ER diagram of the database with DBeaver.

  5. Querying Data

    Question 5 of 5

    • Get first_name, last_name, phone, address of all customer & export as csv.
    • List all the products with price above 500.
    • List all the transaction that happen in month of June, 2025.
    • List Name, Address, Email of customer from Kathmandu / Pokhara. If there is no email, you have to display -. Sort result by name & address.
    • List HR employees who have already resigned from the mart.
    • Find top 5 expensive product. Exclude brands ElectroVision, BeanBrew.
    • List customer who uses Gmail.
    • Show all unique location from customer table.
    • Find the customer from Rasuwa who don't have email address.
    • Find the employee who don't have supervisor.
    • Find Name, Email and Phone number of customer from Dhangadi.
    • Find Name, Department of top 10 highest paid employee.
    • Get top 10 product that are low in stock excluding brand ComfortSit.
    • Find customer whose number doesn't start with 98.
    • Practice Questions from Hacker Rank.