Roll No: 49
Student Name: Tribhuvan Renuka Sambhaji
Program and Output:
Group A : Lab Assignment No.1
TITLE: Study of Open Source Relational Databases : MySQL
-
//USE DATABASE
mysql> use info;
Database changed
//SHOW DATABASES
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| host |
| info |
| mysql
|
| performance_schema |
| test
|+--------------------+
6 rows in set (0.00 sec)
//CREATE TABLE
mysql> create table info(roll_no int, name varchar(30), class varchar(20), marks float, dob
date);
Query OK, 0 rows affected (0.20 sec)
//INSERT VALUES IN TABLE
mysql> insert into info values('1','harsha','be','98.0','1996-07-12');
Query OK, 1 row affected (0.05 sec)
mysql> insert into info values('2','tej','te','98.1','1997-07-12');
Query OK, 1 row affected (0.25 sec)
mysql> insert into info values('2','keshav','te','98.2','1998-05-11');
Query OK, 1 row affected (0.08 sec)
//DISPLAY TABLE
mysql> select *from info;
+---------+--------+-------+-------+------------+
| roll_no | name | class | marks | dob
|
+---------+--------+-------+-------+------------+
|
1 | harsha | be | 98 | 1996-07-12 || 2 | tej | te
| 98.1 | 1997-07-12 |
| 2 | keshav | te | 98.2 | 1998-05-11 |
+---------+--------+-------+-------+------------+
3 rows in set (0.00 sec)
//DISPLAY PARTICULAR COLUMN
mysql> select roll_no,marks from info;
+---------+-------+
| roll_no | marks |
+---------+-------+
| 1 | 98 |
| 2 | 98.1 |
| 2 | 98.2 |
+---------+-------+
3 rows in set (0.00 sec)
//DELETE PARTICULAR ROW
mysql> delete from info where name='tej';
Query OK, 1 row affected (0.16 sec)
mysql> select *from info;
+---------+--------+-------+-------+-----------+| roll_no |
name | class | marks | dob
|
+---------+--------+-------+-------+------------+
| 1 | harsha | be | 98 | 1996-07-12 |
| 2 | keshav | te | 98.2 | 1998-05-11 |
+---------+--------+-------+-------+------------+
2 rows in set (0.00 sec)
//CREATE NEW DATABASE
mysql> create database new1;
Query OK, 1 row affected (0.03 sec)
//SHOW DATABASES
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| host |
| info |
| mysql |
| new1 |
| performance_schema || test
|
+--------------------+
7 rows in set (0.00 sec)
//DROP DATABASE
mysql> drop database new1;
Query OK, 0 rows affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| host |
| info |
| mysql
|
| performance_schema |
| test
|
+--------------------+
6 rows in set (0.00 sec)
mysql> use info;
Database changed
mysql> select*from info;
+---------+--------+-------+-------+------------+
| roll_no | name | class | marks | dob
|
+---------+--------+-------+-------+------------+
| 1 | harsha | be | 98 | 1996-07-12 |
| 2 | keshav | te | 98.2 | 1998-05-11 |
+---------+--------+-------+-------+------------+
2 rows in set (0.00 sec)
//Update row of the table
mysql> update info set class='secomp' where roll_no='1';
Query OK, 1 row affected (0.17 sec)
Rows matched: 1 Changed: 1 Warnings:
0
//For Display the table
mysql> select*from info;
+---------+--------+--------+-------+------------+
| roll_no | name | class | marks | dob|
+---------+--------+--------+-------+------------+
| 1 | harsha | secomp | 98 | 1996-07-12 |
| 2 | keshav | te
| 98.2 | 1998-05-11 |
+---------+--------+--------+-------+------------+
2 rows in set (0.00 sec)
//For Add column into the table
mysql> alter table info add sirname varchar(20);
Query OK, 2 rows affected (0.55 sec)
Records: 2 Duplicates: 0 Warnings: 0
//DISPLAY TABLE
mysql> select*from info;
+---------+--------+--------+-------+------------+---------+
| roll_no | name | class | marks | dob
| sirname |
+---------+--------+--------+-------+------------+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te
| 98.2 | 1998-05-11 | NULL |
+---------+--------+--------+-------+------------+---------+
2 rows in set (0.09 sec)
// Modify Datatype of particular column
mysql> alter table info modify roll_no
float;
Query OK, 2 rows affected (0.48 sec)
Records: 2 Duplicates: 0 Warnings: 0
//DISPLAY TABLE
mysql> select*from info;
+---------+--------+--------+-------+------------+---------+
| roll_no | name | class | marks | dob
| sirname |
+---------+--------+--------+-------+------------+---------+|
1 | harsha | secomp | 98 | 1996-07-12 | NULL ||
2 | keshav | te
| 98.2 | 1998-05-11 | NULL |
+---------+--------+--------+-------+------------+---------+
2 rows in set (0.00 sec)
//For Description of table
mysql> desc info;
+---------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | float
| name
| YES |
| NULL |
| varchar(30) | YES |
| class | varchar(20) | YES |
| marks | float
| dob
| date
| YES |
| YES |
| NULL
|
|
|
| NULL |
|
| NULL |
| NULL |
| sirname | varchar(20) | YES || NULL
|
|
|
|
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
//For delete the particular column
mysql> alter table info drop sirname;
Query OK, 2 rows affected (0.36 sec)
Records: 2 Duplicates: 0 Warnings: 0
//For Display the table
mysql> select*from info;
+---------+--------+--------+-------+------------+
| roll_no | name | class | marks | dob
|
+---------+--------+--------+-------+------------+
| 1 | harsha | secomp | 98 | 1996-07-12 |
| 2 | keshav | te
| 98.2 | 1998-05-11 |
+---------+--------+--------+-------+------------+
2 rows in set (0.00 sec)
mysql> alter table info add sirname varchar(20);
Query OK, 2 rows affected (0.53 sec)
Records: 2 Duplicates: 0 Warnings: 0
//For Display the table
mysql> select*from info;
+---------+--------+--------+-------+------------+---------+
| roll_no | name | class | marks | dob
| sirname |
+---------+--------+--------+-------+------------+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te| 98.2 | 1998-05-11 | NULL |
+---------+--------+--------+-------+------------+---------+
2 rows in set (0.00 sec)
//Renaming the column
mysql> alter table info change name fullname varchar(20);
Query OK, 2 rows affected (0.61 sec)
Records: 2 Duplicates: 0 Warnings: 0
//Display table
mysql> select*from info;
+---------+----------+--------+-------+------------+---------+
| roll_no | fullname | class | marks | dob
| sirname |
+---------+----------+--------+-------+------------+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te
| 98.2 | 1998-05-11 | NULL |
+---------+----------+--------+-------+------------+---------+
/
//Insert values in table
mysql> insert into info values('101', 'sai', 'se','80','2017-1-12','xyz');
Query OK, 1 row affected (0.08 sec)
mysql> insert into info values('102', 'ram','se','90','2018-1-12','uvw');
Query OK, 1 row affected (0.22 sec)
//Display table
mysql> select*from info;
+---------+----------+--------+-------+------------+---------+
| roll_no | fullname | class | marks | dob
| sirname |+---------+----------+--------+-------+------------
+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te
| 101 | sai
| 102 | ram| se
| 98.2 | 1998-05-11 | NULL |
| 80 | 2017-01-12 | xyz
| se
| 90 | 2018-01-12 | uvw
|
|
+---------+----------+--------+-------+------------+---------+
4 rows in set (0.00 sec)
//Creating View
mysql> create view te as select roll_no, fullname,class from info;
Query OK, 0 rows affected (0.20 sec)
//Display table
mysql> select*from te;
+---------+----------+--------+
| roll_no | fullname | class |
+---------+----------+--------+
| 1 | harsha | secomp |
| 2 | keshav | te
| 101 | sai
| 102 | ram
| se
| se
|
|
|
+ + +
//USE DATABASE
mysql> use info;
Database changed
//SHOW DATABASES
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| host |
| info |
| mysql
|
| performance_schema |
| test
|+--------------------+
6 rows in set (0.00 sec)
//CREATE TABLE
mysql> create table info(roll_no int, name varchar(30), classvarchar(20), marks float, dob
date);
Query OK, 0 rows affected (0.20 sec)
//INSERT VALUES IN TABLEmysql> insert into info values('1','harsha','be','98.0','1996-07-
12');
Query OK, 1 row affected (0.05 sec)
mysql> insert into info values('2','tej','te','98.1','1997-07-12');
Query OK, 1 row affected (0.25 sec)
mysql> insert into info values('2','keshav','te','98.2','1998-05-11');
Query OK, 1 row affected (0.08 sec)
//DISPLAY TABLE
mysql> select *from info;
+---------+--------+-------+-------+------------+
| roll_no | name | class | marks | dob
|
+---------+--------+-------+-------+------------+
|
1 | harsha | be | 98 | 1996-07-12 || 2 | tej | te
| 98.1 | 1997-07-12 |
| 2 | keshav | te | 98.2 | 1998-05-11 |
+---------+--------+-------+-------+------------+
3 rows in set (0.00 sec)
//DISPLAY PARTICULAR COLUMN
mysql> select roll_no,marks from info;
+---------+-------+
| roll_no | marks |
+---------+-------+
| 1 | 98 |
| 2 | 98.1 |
| 2 | 98.2 |
+---------+-------+
3 rows in set (0.00 sec)
//DELETE PARTICULAR ROW
mysql> delete from info where name='tej';
Query OK, 1 row affected (0.16 sec)
mysql> select *from info;
+---------+--------+-------+-------+-----------+| roll_no |
name | class | marks | dob
|
+---------+--------+-------+-------+------------+
| 1 | harsha | be | 98 | 1996-07-12 |
| 2 | keshav | te | 98.2 | 1998-05-11 |
+---------+--------+-------+-------+------------+
2 rows in set (0.00 sec)
//CREATE NEW DATABASE
mysql> create database new1;
Query OK, 1 row affected (0.03 sec)
//SHOW DATABASES
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| host |
| info |
| mysql |
| new1 |
| performance_schema || test
|
+--------------------+
7 rows in set (0.00 sec)
//DROP DATABASE
mysql> drop database new1;
Query OK, 0 rows affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| host |
| info |
| mysql
|
| performance_schema |
| test
|
+--------------------+
6 rows in set (0.00 sec)
mysql> use info;
Database changed
mysql> select*from info;
+---------+--------+-------+-------+------------+
| roll_no | name | class | marks | dob
|
+---------+--------+-------+-------+------------+
| 1 | harsha | be | 98 | 1996-07-12 |
| 2 | keshav | te | 98.2 | 1998-05-11 |
+---------+--------+-------+-------+------------+
2 rows in set (0.00 sec)
//Update row of the table
mysql> update info set class='secomp' where roll_no='1';
Query OK, 1 row affected (0.17 sec)
Rows matched: 1 Changed: 1 Warnings:
//For Display the table
mysql> select*from info;
+---------+--------+--------+-------+------------+
| roll_no | name | class | marks | dob|
+---------+--------+--------+-------+------------+
| 1 | harsha | secomp | 98 | 1996-07-12 |
| 2 | keshav | te
| 98.2 | 1998-05-11 |
+---------+--------+--------+-------+------------+
2 rows in set (0.00 sec)
//For Add column into the table
mysql> alter table info add sirname
varchar(20); Query OK, 2rows affected (0.55 sec)
Records: 2 Duplicates: 0 Warnings: 0
//DISPLAY TABLE
mysql> select*from info;
+---------+--------+--------+-------+------------+---------+
| roll_no | name | class | marks | dob
| sirname |
+---------+--------+--------+-------+------------+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te
| 98.2 | 1998-05-11 | NULL |
+---------+--------+--------+-------+------------+---------+
2 rows in set (0.09 sec)
// Modify Datatype of particular column
mysql> alter table info modify roll_no
float; Query OK, 2 rows affected (0.48 sec)
Records: 2 Duplicates: 0 Warnings: 0
//DISPLAY TABLE
mysql> select*from info;
+---------+--------+--------+-------+------------+---------+
| roll_no | name | class | marks | dob
| sirname |
+---------+--------+--------+-------+------------+---------+|
1 | harsha | secomp | 98 | 1996-07-12 | NULL ||
2 | keshav | te
| 98.2 | 1998-05-11 | NULL |
+---------+--------+--------+-------+------------+---------+
2 rows in set (0.00 sec)
//For Description of table
mysql> desc info;
+---------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | float
| name
| YES |
| NULL |
| varchar(30) | YES |
| class | varchar(20) | YES |
| marks | float
| dob
| date
| YES |
| YES |
| NULL
|
|
|
| NULL |
|
| NULL |
| NULL |
| sirname | varchar(20) | YES || NULL
|
|
|
|
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
//For delete the particular column
mysql> alter table info drop
sirname;
Query OK, 2 rows affected (0.36 sec)
Records: 2 Duplicates: 0 Warnings: 0
//For Display the table
mysql> select*from info;
+---------+--------+--------+-------+------------+
| roll_no | name | class | marks | dob
|
+---------+--------+--------+-------+------------+
| 1 | harsha | secomp | 98 | 1996-07-12 |
| 2 | keshav | te
| 98.2 | 1998-05-11 |
+---------+--------+--------+-------+------------+
2 rows in set (0.00 sec)
mysql> alter table info add sirname varchar(20);
Query OK, 2 rows affected (0.53 sec)
Records: 2 Duplicates: 0 Warnings: 0
//For Display the table
mysql> select*from info;
+---------+--------+--------+-------+------------+---------+
| roll_no | name | class | marks | dob
| sirname |
+---------+--------+--------+-------+------------+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te| 98.2 | 1998-05-11 | NULL |
+---------+--------+--------+-------+------------+---------+
2 rows in set (0.00 sec)
//Renaming the column
mysql> alter table info change name fullname varchar(20);
Query OK, 2 rows affected (0.61 sec)
Records: 2 Duplicates: 0 Warnings: 0
//Display table
mysql> select*from info;
+---------+----------+--------+-------+------------+---------+
| roll_no | fullname | class | marks | dob
| sirname |
+---------+----------+--------+-------+------------+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te
| 98.2 | 1998-05-11 | NULL |
//Insert values in table
mysql> insert into info values('101', 'sai', 'se','80','2017-1-12','xyz');
Query OK, 1 row affected (0.08 sec)
mysql> insert into info values('102', 'ram','se','90','2018-1-12','uvw');
Query OK, 1 row affected (0.22 sec)
//Display table
mysql> select*from info;
+---------+----------+--------+-------+------------+---------+
| roll_no | fullname | class | marks | dob
| sirname |+---------+----------+--------+-------+------------
+---------+
| 1 | harsha | secomp | 98 | 1996-07-12 | NULL |
| 2 | keshav | te
| 101 | sai
| 102 | ram| se
| 98.2 | 1998-05-11 | NULL |
| 80 | 2017-01-12 | xyz
| se
| 90 | 2018-01-12 | uvw
|
|
+---------+----------+--------+-------+------------+---------+
4 rows in set (0.00 sec)
//Creating View
mysql> create view te as select roll_no, fullname,class from info;
Query OK, 0 rows affected (0.20 sec)
//Display table
mysql> select*from te;
+---------+----------+--------+
| roll_no | fullname | class |
+---------+----------+--------+
| 1 | harsha | secomp |
| 2 | keshav | te
| 101 | sai
| 102 | ram
| se
| se
|
|
|
+---------+----------+-
