SQL Practice Repository

This repository contains SQL scripts for practicing database creation, manipulation, and queries.
It covers two main domains: Library Management and Insurance Domain.

---

1. Library Management Database

Problem Statement

A library management system requires a comprehensive database to handle its functions including managing books, authors, members, loans, and staff.
The database facilitates efficient book tracking, member management, loan processing, and staff operations.

Tables & Purpose

Table	Purpose
authors	Record information about authors who have written books
books	Store detailed information about each book
members	Manage details of library members
loans	Track the borrowing of books by members
staff	Maintain information about library staff

Sample Columns

• Books: book_id (PK), title, author_id (FK), publication_year, genre, isbn, available_copies
• Authors: author_id (PK), first_name, last_name, date_of_birth, nationality
• Members: member_id (PK), first_name, last_name, date_of_birth, contact_number, email, membership_date
• Loans: loan_id (PK), book_id (FK), member_id (FK), loan_date, return_date, actual_return_date
• Staff: staff_id (PK), first_name, last_name, job_title, contact_number, email, hire_date

Queries Applied

DDL

• Add new column to books: sold_copies
• Rename position to job_title in staff
• Drop email column from members

DML

• Insert new data into books, members, loans, staff
• Update member contact number
• Update sold_copies in books
• Delete specific loan
• Insert new loan

Join Queries

1. Retrieve all books along with their authors
2. Find all books currently on loan along with member details
3. List all books borrowed by a specific member
4. Get the total number of books and total available copies for each genre
5. Find all staff members who are librarians and their hire dates

---

2. Insurance Domain Database

Problem Statement

An insurance company requires a comprehensive database to manage its operations effectively.
It captures detailed information about customers, policies, claims, agents, and their interactions.
The goal is to support operations, reporting, and analysis needs.

Tables & Purpose

Table	Purpose
customers	Store detailed customer information
policies	Record insurance policies offered
claims	Track claims made by customers
agents	Maintain information about insurance agents
policy_assignments	Assign policies to customers
claim_processing	Track processing of claims

Sample Columns

• Customers: customer_id (PK), first_name, last_name, date_of_birth, gender, contact_number, email, address
• Policies: policy_id (PK), policy_name, policy_type, coverage_details, premium, start_date, end_date
• Claims: claim_id (PK), claim_date, claim_amount, approved_amount, claim_status, policy_id (FK), customer_id (FK)
• Agents: agent_id (PK), first_name, last_name, contact_number, email, hire_date
• Policy Assignments: assignment_id (PK), customer_id (FK), policy_id (FK), start_date, end_date
• Claim Processing: processing_id (PK), claim_id (FK), processing_date, payment_amount, payment_date

Queries Applied

DDL

• Add new column to agents
• Rename policy_name to policy_title in policies
• Drop address column from customers

DML

• Update policy premium amount
• Delete specific claim
• Insert new policy assignment

Join Queries

1. Retrieve all customers with their assigned policies and agents
2. Find all claims and associated policy details
3. List all claims along with customer details
4. Get total claim amount and number of claims per policy type
5. Find the most recent claim for each customer

---

Tools Used

• DBMS: MySQL / SQL Server / Oracle
• SQL Commands: DDL, DML, DCL, TCL
• Data Types: Numeric, String, Date/Time, Binary
• Platform: GUI (Workbench) & Command Line

---

Author

• Name: Prasanna Hanchinamani
• Email: prasannahanchinamani.15@gmail.com