Scribd
Upload
27 views2 pages

SQL Ques

A MySQL table named 'SBOP' is created to store account information, including account number, name, balance, date of opening, and transaction count. Several records are inserted, with one record missing a transaction value and another having a balance but no date of opening. The table is altered to add an address field, and queries are performed to retrieve specific data based on transaction counts and date formats.

Uploaded by

chamritrnd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
27 views2 pages

SQL Ques

A MySQL table named 'SBOP' is created to store account information, including account number, name, balance, date of opening, and transaction count. Several records are inserted, with one record missing a transaction value and another having a balance but no date of opening. The table is altered to add an address field, and queries are performed to retrieve specific data based on transaction counts and date formats.

Uploaded by

chamritrnd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

mysql> create table SBOP(account_no char(30),name char(30),balance

decimal,date_of_open date,transaction int);


Query OK, 0 rows affected (0.12 sec)

mysql> insert into sbop values('SB-1','ANIL',150000.00,'2011-02-24',7);


Query OK, 1 row affected (0.03 sec)

mysql> insert into sbop(account_no,name,balance,transaction) values('SB-


2','AMIT',23567.89,8);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sbop values('SB-3','SAKSHI',45000.00,'2012-02-04',5);


Query OK, 1 row affected (0.01 sec)

mysql> insert into sbop(account_no,name,balance,date_of_open) values('SB-4',


'GOPAL',23812.35,'2013-09-22');
Query OK, 1 row affected (0.01 sec)

mysql> insert into sbop values('SB-5','DENNIS',63459.80,'2009-11-10',15);


Query OK, 1 row affected (0.01 sec)

1)
mysql> select account_no,name,date_of_open from sbop where transaction>8;
+------------+--------+--------------+
| account_no | name | date_of_open |
+------------+--------+--------------+
| SB-5 | DENNIS | 2009-11-10 |
+------------+--------+--------------+
1 row in set (0.01 sec)

2)
mysql> select* from sbop where transaction is null;
+------------+-------+---------+--------------+-------------+
| account_no | name | balance | date_of_open | transaction |
+------------+-------+---------+--------------+-------------+
| SB-4 | GOPAL | 23812.3 | 2013-09-22 | NULL |
+------------+-------+---------+--------------+-------------+
1 row in set (0.00 sec)

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

mysql> select* from sbop;


+------------+--------+---------+--------------+-------------+---------+
| account_no | name | balance | date_of_open | transaction | address |
+------------+--------+---------+--------------+-------------+---------+
| SB-1 | ANIL | 150000 | 2011-02-24 | 7 | NULL |
| SB-2 | AMIT | 23567.9 | NULL | 8 | NULL |
| SB-3 | SAKSHI | 45000 | 2012-02-04 | 5 | NULL |
| SB-4 | GOPAL | 23812.3 | 2013-09-22 | NULL | NULL |
| SB-5 | DENNIS | 63459.8 | 2009-11-10 | 15 | NULL |
+------------+--------+---------+--------------+-------------+---------+
7 rows in set (0.00 sec)

4)
mysql> select date_format(date_of_open,'%M') as MONTH,DAY(date_of_open) as da
y from sbop;
+-----------+------+
| MONTH | day |
+-----------+------+
| February | 24 |
| NULL | NULL |
| February | 4 |
| September | 22 |
| November | 10 |
+-----------+------+
7 rows in set (0.01 sec)

You might also like