October 2024
tcs
SQL “Create an customer Table with following Attributes (Columns )1. Customer SSN ID: A Numeric value for Employee ID (Primary key)2. First Name: A text value for customer name(Maximum 50 characters)3. Last Name: A text value for customer name(Maximum 50 characters)4. Email: A text field to capture the Email of an customer.5. Date Of Birth : A Date value (YYYY-MM-DD)6. Address: A text value to capture street, city details(Maximum 100 characters)7. Contact Number: Numeric value Maximum 10 digits. After Successful Creation of Table insert minimum 10 Demo records” CREATE TABLE customer (customer_ssn_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100),date_of_birth DATE,address VARCHAR(100),contact_number BIGINT CHECK (LENGTH(contact_number) = 10)); INSERT INTO customer (customer_ssn_id, first_name, last_name, email, date_of_birth, address, contact_number)VALUES(1001, ‘John’, ‘Doe’, ‘john.doe@example.com’, ‘1990-01-15’, ‘123 Elm St, New York, NY’, 1234567890),(1002, ‘Jane’, ‘Smith’, ‘jane.smith@example.com’, ‘1985-05-23’, ‘456 Oak Ave, Los Angeles, CA’, 2345678901),(1003, ‘Michael’, ‘Brown’, ‘michael.brown@example.com’, ‘1992-08-10’, ‘789 Pine Blvd, Chicago, IL’, 3456789012),(1004, ‘Emily’, ‘Johnson’, ’emily.johnson@example.com’, ‘1995-12-01’, ‘321 Maple Ln, Houston, TX’, 4567890123),(1005, ‘David’, ‘Davis’, ‘david.davis@example.com’, ‘1988-03-18’, ‘654 Cedar Rd, Phoenix, AZ’, 5678901234),(1006, ‘Sarah’, ‘Wilson’, ‘sarah.wilson@example.com’, ‘1993-07-25’, ‘987 Birch St, Philadelphia, PA’, 6789012345),(1007, ‘Chris’, ‘Miller’, ‘chris.miller@example.com’, ‘1991-11-07’, ‘111 Spruce Dr, San Antonio, TX’, 7890123456),(1008, ‘Laura’, ‘Garcia’, ‘laura.garcia@example.com’, ‘1987-09-14’, ‘222 Fir Pl, Dallas, TX’, 8901234567),(1009, ‘James’, ‘Martinez’, ‘james.martinez@example.com’, ‘1994-04-29’, ‘333 Redwood Ct, San Jose, CA’, 9012345678),(1010, ‘Linda’, ‘Rodriguez’, ‘linda.rodriguez@example.com’, ‘1990-02-22’, ‘444 Palm Way, Austin, TX’, 1234567809); ******************************************************************************************* “As a financial data analyst, i want to search Customer personal Data by there customer Id” “After Successful insertion of records financial data analyst, will beable to retrieve customer Details from customer table by there customer id” SELECT * FROM customerWHERE customer_ssn_id = 1001; — Replace 1001 with the desired customer SSN ID ******************************************************************************************** “As a customer, I want to update my personal information( such address or phone number) in the database, so thatbank has up to date details” “After Successful insertion of customer records, customer will be able to update the personal contact details.The query result should contain the following attributes;Customer SSN id | name( first_name+last_name) | Email | Phone | address” UPDATE customerSET address = ‘456 New Address St, Miami, FL’, — New addresscontact_number = 9876543210 — New phone numberWHERE customer_ssn_id = 1001; — Replace 1001 with the desired customer SSN ID SELECT customer_ssn_id, CONCAT(first_name, ‘ ‘, last_name) AS name, email, contact_number AS phone, addressFROM customerWHERE customer_ssn_id = 1001; — Replace 1001 with the desired customer SSN ID ******************************************************************************************* “As a bank employee, i want to able to Capture Customer Transaction Data in the Database”“Create an Customer_transaction Table with following Attributes (Columns )“”Enter the values for input fields (on Screen) : Customer id (Primary key ) , Customer Name, Account Number , IFSC code, Account balance , Adhara card No. , Pan Card No , Contact Number After Successful Creation of Table insert minimum 10 records” CREATE TABLE customer_transaction (customer_id INT PRIMARY KEY,customer_name VARCHAR(100),account_number VARCHAR(20),ifsc_code VARCHAR(11),account_balance DECIMAL(15, 2),aadhar_card_no VARCHAR(12),pan_card_no VARCHAR(10),contact_number BIGINT CHECK (LENGTH(contact_number) = 10)); INSERT INTO customer_transaction (customer_id, customer_name, account_number, ifsc_code, account_balance, aadhar_card_no, pan_card_no, contact_number)VALUES(1, ‘John Doe’, ‘123456789012’, ‘IFSC0001’, 15000.75, ‘123412341234’, ‘ABCDE1234F’, 1234567890),(2, ‘Jane Smith’, ‘234567890123’, ‘IFSC0002’, 20000.50, ‘234523452345’, ‘FGHIJ5678K’, 2345678901),(3, ‘Michael Brown’, ‘345678901234’, ‘IFSC0003’, 35000.00, ‘345634563456’, ‘JKLMN9012L’, 3456789012),(4, ‘Emily Johnson’, ‘456789012345’, ‘IFSC0004’, 45000.25, ‘456745674567’, ‘OPQRS3456M’, 4567890123),(5, ‘David Davis’, ‘567890123456’, ‘IFSC0005’, 100000.00, ‘567856785678’, ‘TUVWX7890N’, 5678901234),(6, ‘Sarah Wilson’, ‘678901234567’, ‘IFSC0006’, 25000.75, ‘678967896789’, ‘ABCDE1234P’, 6789012345),(7, ‘Chris Miller’, ‘789012345678’, ‘IFSC0007’, 30000.00, ‘789078907890’, ‘FGHIJ5678Q’, 7890123456),(8, ‘Laura Garcia’, ‘890123456789’, ‘IFSC0008’, 75000.50, ‘890189018901’, ‘JKLMN9012R’, 8901234567),(9, ‘James Martinez’, ‘901234567890’, ‘IFSC0009’, 90000.25, ‘901290129012’, ‘OPQRS3456S’, 9012345678),(10, ‘Linda Rodriguez’, ‘012345678901’, ‘IFSC0010’, 50000.00, ‘012301230123’, ‘TUVWX7890T’, 1234567809); ******************************************************************************************* As a bank employee, i want to search customer Data(personal data and transactional data by there Employee Id “Bank employee will be able to retrieve customer account details from transactional details and personal details from customer table (Customer id , Customer Name, Account Number , IFSC code, Account balance , Adhara card No. , Pan Card No, Date Of Birth (DD-MM-YYYY) , Email , Address , Contact Number).” SELECT ct.customer_id, ct.customer_name, ct.account_number, ct.ifsc_code, ct.account_balance, ct.aadhar_card_no, ct.pan_card_no, c.date_of_birth, c.email, c.address, c.contact_numberFROM customer_transaction ctINNER JOIN customer c ON ct.customer_id = c.customer_ssn_idWHERE ct.customer_id = 1; — Replace 1 with the desired customer ID **************************************************************************************** As a customer, I want to check updated account balance after transaction process “After Successful transaction process, records in customer transactions table will be updated . And customer will able to check the updated account balance.(Customer id | name( first_name+last_name) | Email | Account Balance)” UPDATE customer_transactionSET account_balance = account_balance + 5000.00 — Adjust this value for deposit/withdrawalWHERE customer_id = 1; — Replace 1 with the desired customer ID SELECT ct.customer_id, CONCAT(c.first_name, ‘ ‘, c.last_name) AS name, c.email, ct.account_balanceFROM customer_transaction ctINNER JOIN customer c ON ct.customer_id = c.customer_ssn_idWHERE ct.customer_id = 1; — Replace 1 with the desired customer ID UI UX BY SUS /* style.css */ body { font-family: Arial, sans-serif; background-color: #f0f2f5; margin: 0; padding: 0; } .container { width: 50%; margin: 50px auto; padding: 20px; background-color: #fff; border-radius: 8px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.1); } h2 { text-align: center; color: #333; } label { display: block; margin: 10px 0 5px; color: #555; } input, textarea { width: calc(100% – 22px); padding: 10px; margin-bottom: 10px; border: 1px solid #ccc; border-radius: 4px; } button { width: 100%; padding: 10px; background-color: #28a745; color: #fff; border: none; border-radius: 4px; cursor: pointer; font-size: 16px; } button:hover { background-color: #218838; } .success-message, .error-message { text-align: center; margin-top: 20px; font-weight: bold; } .success-message { color: green; } .error-message { color: red; } ************************************************************** <!DOCTYPE html> <html lang=”en”> <head> <meta charset=”UTF-8″> <meta name=”viewport” content=”width=device-width, initial-scale=1.0″> <link rel=”stylesheet” href=”style.css”>