ASSIGNMENT-4
mysql> create database charity;
Query OK, 1 row affected (0.00 sec)
mysql> use charity;
Database changed
mysql> create table charity (P_ID int primary key,Last_Name
varchar(20),First_Name varchar(20),address varchar(20),city
varchar(20),contribution float);
Query OK, 0 rows affected (0.09 sec)
mysql> select * from charity;
+------+------------------+---------------+----------------------+---------+-------------
| P_ID | Last_Name | First_Name | address | city | contribution
+------+-----------------+------------+----------------------+---------+-------------------
| 1 | Bindra | jaspreet | 5B-Gomti Nager | Lucknow | 3500.5
| 2 | Rana | Monica | 21A-Bandra | Mumbai | 2768
| 3 | Singh | Jatinder | 8-Punjab Bagh | Delhi | 2000
| 4 | Arora | Satinder | K1-SherePunjabColony | Mumbai | 1900
| 5 | Krishnan | Vineeta | A75-Adarsh Nagar | null | NULL
+------+-----------+------------+----------------------+---------+------------------------
5 rows in set (0.00 sec)
mysql> select lower(first_name) from charity;
+-------------------+
| lower(first_name) |
+-------------------+
| jaspreet |
| monica |
| jatinder |
| satinder |
| vineeta |
+-------------------+
5 rows in set (0.35 sec)
mysql> select upper(last_name) from charity where city='mumbai';
+------------------+
| upper(last_name) |
+------------------+
| RANA |
| ARORA |
+------------------+
2 rows in set (0.01 sec)
mysql> select p_id,left(first_name,3) from charity;
+------+--------------------+
| p_id | left(first_name,3) |
+------+--------------------+
| 1 | jas |
| 2 | Mon |
| 3 | Jat |
| 4 | Sat |
| 5 | Vin |
+------+--------------------+
5 rows in set (0.02 sec)
mysql> select CONCAT(first_name,' ',last_name) from charity;
+----------------------------------+
| CONCAT(first_name,' ',last_name) |
+----------------------------------+
| jaspreet Bindra |
| Monica Rana |
| Jatinder Singh |
| Satinder Arora |
| Vineeta Krishnan |
+----------------------------------+
5 rows in set (0.00 sec)
mysql> select P_id,length(address) from charity;
+------+-----------------+
| P_id | length(address) |
+------+-----------------+
| 1| 14 |
| 2| 10 |
| 3| 13 |
| 4| 20 |
| 5| 16 |
+------+-----------------+
5 rows in set (0.01 sec)
mysql> select P_id,right(city,2) from charity;
+------+---------------+
| P_id | right(city,2) |
+------+---------------+
| 1 | ow |
| 2 | ai |
| 3 | hi |
| 4 | ai |
| 5 | ll |
+------+---------------+
5 rows in set (0.00 sec)
mysql> select last_name,first_name from charity where
instr(first_name,'at')=2 or instr(first_name,'at')=3;
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Singh | Jatinder |
| Arora | Satinder |
+-----------+------------+
2 rows in set (0.00 sec)
mysql> select instr(last_name,'a') from charity;
+----------------------+
| instr(last_name,'a') |
+----------------------+
| 6|
| 2|
| 0|
| 1|
| 7|
+----------------------+
5 rows in set (0.00 sec)
mysql> select first_name,last_name from charity where first_name like '%a';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Monica | Rana |
| Vineeta | Krishnan |
+------------+-----------+
2 rows in set (0.00 sec)
mysql> select concat(first_name,' ',last_name) from charity;
+----------------------------------+
| concat(first_name,' ',last_name) |
+----------------------------------+
| jaspreet Bindra |
| Monica Rana |
| Jatinder Singh |
| Satinder Arora |
| Vineeta Krishnan |
+----------------------------------+
5 rows in set (0.00 sec)
mysql> select p_id,last_name,ROUND(contribution) from charity;
+------+-----------+---------------------+
| p_id | last_name | ROUND(contribution) |
+------+-----------+---------------------+
| 1 | Bindra | 3500 |
| 2 | Rana | 2768 |
| 3 | Singh | 2000 |
| 4 | Arora | 1900 |
| 5 | Krishnan | NULL |
+------+-----------+---------------------+
5 rows in set (0.00 sec)
mysql> select p_id,last_name,truncate(contribution,0) from charity;
+------+-----------+---------------------+
| p_id | last_name | truncate(contribution,0) |
+------+-----------+---------------------+
| 1 | Bindra | 3500 |
| 2 | Rana | 2768 |
| 3 | Singh | 2000 |
| 4 | Arora | 1900 |
| 5 | Krishnan | NULL |
+------+-----------+---------------------+
5 rows in set (0.00 sec)
mysql> select last_name,contribution,ROUND(contribution/10,2) from
charity;
+-----------+--------------+--------------------------+
| last_name | contribution | ROUND(contribution/10,2) |
+-----------+--------------+--------------------------+
| Bindra | 3500.5 | 350.05 |
| Rana | 2768 | 276.80 |
| Singh | 2000 | 200.00 |
| Arora | 1900 | 190.00 |
| Krishnan | NULL | NULL |
+-----------+--------------+--------------------------+
5 rows in set (0.02 sec)
