ASSIGNMENT 2
SQL DDL COMMANDS
...............................................................
mysql> create database info;
Query OK, I row affected (001 sec)
mysql> use info;
Database changed
Create Table:
mysql> create table pereson_detail (Fname varchar(20), Mname varchar(20), Lname
varchar(20), Address varchar(20),city varchar(20));
Query OK, 0 rows affected (0.05 sec)
Insert Value:
mysql> insert into pereson_detail values('swati', 'vinod', 'gaikwad', 'mudhawa' ,
'pune');
Query OK, I row affected (0.01 sec)
mysql> insert into pereson_detail values('renuka','anil', 'jadhav',
'hadpser','lonavala);
Query OK, I row affected (0.01 sec)
mysql> insert into pereson_detail values('jiya', 'rahul', 'punekar', 'godpadi',
'solapur');
Query OK, I row affected (0.01 sec)
mysql> insert into pereson_detail values('jiya','rahul, 'punekar', 'godpadi',
'delhi');
Query OK, I row affected (001 sec)
mysql> insert into pereson_detail values('akshata','anil','kubhar', 'viman nager',
'mumbai');
Query OK, I row affected (001 sec)
mysql> select*from pereson_detail;
+------+-------+-------+------------+--------+
Fname | Mname | Lname | Address | city |
+------+-------+-------+------------+--------+
swati | vinod |gaikwad| mudhawa |pune |
renuka |anil | jadhav| hadpser |lonavala|
jiya |rahul |punekar| godpadi |solapur |
jiya |rahul |punekar| godpadi | delhi |
akshata| anil |kubhar | viman nager| mumbai |
+------+-------+-------+------------+--------+
5 rows in set (0.00 sec)
Modify Value:
mysql> update pereson_detail set city='chennai' where Fname 'jiya';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select*from pereson_detail;
+------+-------+-------+------------+--------+
Fname | Mname | Lname | Address | city |
+------+-------+-------+------------+--------+
swati | vinod |gaikwad| mudhawa |pune |
renuka |anil | jadhav| hadpser |lonavala|
jiya |rahul |punekar| godpadi |chennai |
jiya |rahul |punekar| godpadi |chennai |
akshata| anil |kubhar | viman nager| mumbai |
+------+-------+-------+------------+--------+
5 rows in set (0.00 sec)
Select Command:
mysql> select fname, Address,city from pereson_detail;
+------+------------+--------+
Fname | Address | city |
+------+------------+--------+
swati | mudhawa |pune |
renuka |hadpser |lonavala|
jiya | godpadi |solapur |
jiya | godpadi | delhi |
akshata| viman nager| mumbai |
+------+------------+--------+
5 rows in set (0.00 sec)
Delete Command:
mysql> delete from pereson_detail where Fname='jiya';
Query OK, 2 rows affected (0.01 sec)
mysql> select*from pereson_detail;
+------+-------+-------+------------+--------+
Fname | Mname | Lname | Address | city |
+------+-------+-------+------------+--------+
swati | vinod |gaikwad| mudhawa |pune |
renuka |anil | jadhav| hadpser |lonavala|
akshata| anil |kubhar | viman nager| mumbai |
+------+-------+-------+------------+--------+
3 rows in set (0.00 sec)
Where:
mysql> select Address from pereson_detail where city='pune';
+------------+
| Address |
+------------+
| mudhawa |
+------------+
1 row in set (0.00 sec)
Order By:
1. DESC:
mysql> select*from pereson_detail order by Lname desc;
+------+-------+-------+------------+--------+
Fname | Mname | Lname | Address | city |
+------+-------+-------+------------+--------+
akshata| anil |kubhar | viman nager| mumbai |
renuka |anil | jadhav| hadpser |lonavala|
swati | vinod |gaikwad| mudhawa |pune |
+------+-------+-------+------------+--------+
3 rows in set (0.01 sec).
2. ASC
mysql> select*from pereson_detail order by Lname asc;
+------+-------+-------+------------+--------+
Fname | Mname | Lname | Address | city |
+------+-------+-------+------------+--------+
swati | vinod |gaikwad| mudhawa |pune |
renuka |anil | jadhav| hadpser |lonavala|
akshata| anil |kubhar | viman nager| mumbai |
+------+-------+-------+------------+--------+
3 rows in set (0.00 sec)
Group by:
Mysql> select sum(Fname), city from pereson_detail group by city;
TABLE
3 rows in set, 3 warnings (0.00 sec)
Having:
mysql> select sum(Mname) from pereson_detail group by city having city
in('pune','mumbai');
TABLE
2 rows in set, 3 warnings (0.00 sec)
Alter:
1. ADD
mysql> alter table pereson_detail add AdherNo int;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select*from pereson_detail;
+------+-------+-------+------------+--------+---------+
Fname | Mname | Lname | Address | city | AdherNo |
+------+-------+-------+------------+--------+---------+
swati | vinod |gaikwad| mudhawa |pune | NULL |
renuka |anil | jadhav| hadpser |lonavala| NULL |
akshata| anil |kubhar | viman nager| mumbai | NULL |
+------+-------+-------+------------+--------+---------+
3 rows in set (0.00 sec)
2. DROP:
mysql> alter table pereson_detail drop AdherNo,
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from pereson_detail;
+------+-------+-------+------------+--------+
Fname | Mname | Lname | Address | city |
+------+-------+-------+------------+--------+
swati | vinod |gaikwad| mudhawa |pune |
renuka |anil | jadhav| hadpser |lonavala|
akshata| anil |kubhar | viman nager| mumbai |
+------+-------+-------+------------+--------+
3 rows in set (0.00 sec)
