Scribd Logo
Upload

Welcome to Scribd!

  • 30 views

    Program 1

    Uploaded by

    jiciy61793
    The document describes a table named SBOP containing account details and provides MySQL commands to create the table, insert records, alter the table, and select data from the table. The commands create a database and table, insert 5 records, add an address column, display the day of open for accounts, count the number of records, filter records by name, and round the balance of an account.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Program 1

    Uploaded by

    jiciy61793
    30 views3 pages
    The document describes a table named SBOP containing account details and provides MySQL commands to create the table, insert records, alter the table, and select data from the table. The commands create a database and table, insert 5 records, add an address column, display the day of open for accounts, count the number of records, filter records by name, and round the balance of an account.

    Original Title

    Program-1

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes a table named SBOP containing account details and provides MySQL commands to create the table, insert records, alter the table, and select data from the table. The commands create a database and table, insert 5 records, add an address column, display the day of open for accounts, count the number of records, filter records by name, and round the balance of an account.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    30 views3 pages

    Program 1

    Uploaded by

    jiciy61793
    The document describes a table named SBOP containing account details and provides MySQL commands to create the table, insert records, alter the table, and select data from the table. The commands create a database and table, insert 5 records, add an address column, display the day of open for accounts, count the number of records, filter records by name, and round the balance of an account.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 3

    Q.

    Consider the following table named sbop with details of account holders, write
    commands of mysql for 1 to 4 and output for 5 to 7.

    mysql> create database 12Sejal;


    Query OK, 1 row affected (0.05 sec)

    mysql> use 12Sejal;


    Database changed

    mysql> insert into SBOP


    -> values('SB-1','Mr.Anil',15000.00,'2011-02-24',7);
    Query OK, 1 row affected (0.05 sec)

    mysql> insert into SBOP


    -> values('SB-2','Mr.Amit',23567.89,null,8);
    Query OK, 1 row affected (0.04 sec)

    mysql> insert into SBOP


    -> values('SB-3','Mrs.Sakshi',45000.00,'2012-02-04',8);
    Query OK, 1 row affected (0.05 sec)

    mysql> insert into SBOP


    -> values('SB-4','Mr.Gopal',23812.35,'2013-09-22',null);
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into SBOP


    -> values('SB-5','Mr.Dennis',63459.80,'2009-11-10',15);
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from SBOP;


    +------------+------------+----------+------------+-------------+
    | Account_No | name | balance | dateofopen | transaction |
    +------------+------------+----------+------------+-------------+
    | SB-1 | Mr.Anil | 15000 | 2011-02-24 | 7 |
    | SB-2 | Mr.Amit | 23567.89 | NULL | 8 |
    | SB-3 | Mrs.Sakshi | 45000 | 2012-02-04 | 8 |
    | SB-4 | Mr.Gopal | 23812.35 | 2013-09-22 | NULL |
    | SB-5 | Mr.Dennis | 63459.8 | 2009-11-10 | 15 |
    +------------+------------+----------+------------+-------------+
    5 rows in set (0.04 sec)

    1. To display acc no,name,date of open of account holder whose transaction > 8.

    mysql> select Account_No,name,dateofopen from sbop where transaction>8;


    +------------+-----------+------------+
    | Account_No | name | dateofopen |
    +------------+-----------+------------+
    | SB-5 | Mr.Dennis | 2009-11-10 |
    +------------+-----------+------------+
    1 row in set (0.00 sec)

    2. Display all information of holders whose transaction = 0

    mysql> select * from sbop where transaction=0;


    Empty set (0.00 sec)

    3. Add column address with dtype and size as varchar 25


    mysql> alter table sbop add column address varchar(25);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> select * from sbop;


    +------------+------------+----------+------------+-------------+---------+
    | Account_No | name | balance | dateofopen | transaction | address |
    +------------+------------+----------+------------+-------------+---------+
    | SB-1 | Mr.Anil | 15000 | 2011-02-24 | 7 | NULL |
    | SB-2 | Mr.Amit | 23567.89 | NULL | 8 | NULL |
    | SB-3 | Mrs.Sakshi | 45000 | 2012-02-04 | 8 | NULL |
    | SB-4 | Mr.Gopal | 23812.35 | 2013-09-22 | NULL | NULL |
    | SB-5 | Mr.Dennis | 63459.8 | 2009-11-10 | 15 | NULL |
    +------------+------------+----------+------------+-------------+---------+
    5 rows in set (0.00 sec)

    4. Display the month day with reference to date of open for all account holders

    mysql> select dayofmonth(dateofopen) from sbop;


    +------------------------+
    | dayofmonth(dateofopen) |
    +------------------------+
    | 24 |
    | NULL |
    | 4 |
    | 22 |
    | 10 |
    +------------------------+
    5 rows in set (0.00 sec)

    5. Select count(*) from SBOP

    mysql> select count(*) from sbop;


    +----------+
    | count(*) |
    +----------+
    | 5 |
    +----------+
    1 row in set (0.04 sec)

    6. Select name, balance from SBOP where name like '%i'

    mysql> select name,balance from sbop where name like '%i';


    +------------+---------+
    | name | balance |
    +------------+---------+
    | Mrs.Sakshi | 45000 |
    +------------+---------+
    1 row in set (0.00 sec)

    7. Select round (balance,-3) from SBOP where account no='SB-5';

    mysql> select round(balance,-3) from SBOP where Account_No='SB-5';


    +-------------------+
    | round(balance,-3) |
    +-------------------+
    | 63000 |
    +-------------------+
    1 row in set (0.00 sec)

    You might also like