PROGRAM-5
Create a table student with attributes name,fname,rollno,marks,grade and
use create,insert,describe,select,update,alter,delete commands.
CREATE A TABLE
mysql> create table stu4(name char(30),fname varchar(60),rollno int,marks int,gr
ade char(50));
Query OK, 0 rows affected (0.01 sec)
INSERTING DATA INTO A TABLE
mysql> insert into stu4 value('Sonu','Amit kumar',101,85,'A');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu4 value('Ravi','Sumit kumar',102,80,'A');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu4 value('Sandeep','Ranbir Singh',103,75,'B');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu4 value('Akash','Sudhir Singh',104,70,'C');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu4 value('Narender','Ajit Singh',105,60,'D');
Query OK, 1 row affected (0.00 sec)
VIEWING TABLE CONTENTS
mysql> select * from stu4;
+----------+--------------+--------+-------+-------+
| name | fname | rollno | marks | grade |
+----------+--------------+--------+-------+-------+
| Sonu | Amit kumar | 101 | 85 | A |
| Ravi | Sumit kumar | 102 | 80 | A |
| Sandeep | Ranbir Singh | 103 | 75 | B |
| Akash | Sudhir Singh | 104 | 70 | C |
| Narender | Ajit Singh | 105 | 60 | D |
+----------+--------------+--------+-------+-------+
5 rows in set (0.00 sec)
USE OF DESCRIBE STATEMENT
mysql> describe stu4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | char(30) | YES | | NULL | |
| fname | varchar(60) | YES | | NULL | |
| rollno | int(11) | YES | | NULL | |
| marks | int(11) | YES | | NULL | |
| grade | char(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
SELECT SPECIFIC ROWS USING WHERE CLAUSE
mysql> select * from stu4 where grade="A";
+------+-------------+--------+-------+-------+
| name | fname | rollno | marks | grade |
+------+-------------+--------+-------+-------+
| Sonu | Amit kumar | 101 | 85 | A |
| Ravi | Sumit kumar | 102 | 80 | A |
+------+-------------+--------+-------+-------+
2 rows in set (0.03 sec)
SELECT SPECIFIC ROWS USING AND OPERATOR
mysql> select * from stu4 where fname="Ranbir Singh" and rollno="103";
+---------+--------------+--------+-------+-------+
| name | fname | rollno | marks | grade |
+---------+--------------+--------+-------+-------+
| Sandeep | Ranbir Singh | 103 | 75 | B |
+---------+--------------+--------+-------+-------+
1 row in set (0.00 sec)
SELECT SPECIFIC ROWN USING OR OPERATOR
mysql> select * from stu4 where fname="Ranbir Singh" or rollno="101";
+---------+--------------+--------+-------+-------+
| name | fname | rollno | marks | grade |
+---------+--------------+--------+-------+-------+
| Sonu | Amit kumar | 101 | 85 | A |
| Sandeep | Ranbir Singh | 103 | 75 | B |
+---------+--------------+--------+-------+-------+
2 rows in set (0.00 sec)
SELECT SPECIFIC COLUMN
mysql> select name,rollno,grade from stu4;
+----------+--------+-------+
| name | rollno | grade |
+----------+--------+-------+
| Sonu | 101 | A |
| Ravi | 102 | A |
| Sandeep | 103 | B |
| Akash | 104 | C |
| Narender | 105 | D |
+----------+--------+-------+
5 rows in set (0.02 sec)
USE OF UPDATE STATEMENT
mysql> update stu4 set marks=marks+5;
mysql> select * from stu4;
+----------+--------------+--------+-------+-------+
| name | fname | rollno | marks | grade |
+----------+--------------+--------+-------+-------+
| Sonu | Amit kumar | 101 | 90 | A |
| Ravi | Sumit kumar | 102 | 85 | A |
| Sandeep | Ranbir Singh | 103 | 80 | B |
| Akash | Sudhir Singh | 104 | 75 | C |
| Narender | Ajit Singh | 105 | 65 | D |
+----------+--------------+--------+-------+-------+
5 rows in set (0.00 sec)
USE OF ALTER STATEMENT
mysql> alter table stu4 add mobno int;
mysql> select * from stu4;
+----------+--------------+--------+-------+-------+-------+
| name | fname | rollno | marks | grade | mobno |
+----------+--------------+--------+-------+-------+-------+
| Sonu | Amit kumar | 101 | 90 | A | NULL |
| Ravi | Sumit kumar | 102 | 85 | A | NULL |
| Sandeep | Ranbir Singh | 103 | 80 | B | NULL |
| Akash | Sudhir Singh | 104 | 75 | C | NULL |
| Narender | Ajit Singh | 105 | 65 | D | NULL |
+----------+--------------+--------+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> alter table stu4 drop column mobno;
mysql> select * from stu4;
+----------+--------------+--------+-------+-------+
| name | fname | rollno | marks | grade |
+----------+--------------+--------+-------+-------+
| Sonu | Amit kumar | 101 | 90 | A |
| Ravi | Sumit kumar | 102 | 85 | A |
| Sandeep | Ranbir Singh | 103 | 80 | B |
| Akash | Sudhir Singh | 104 | 75 | C |
| Narender | Ajit Singh | 105 | 65 | D |
+----------+--------------+--------+-------+-------+
5 rows in set (0.00 sec)
DELETING ONE ROW FROM TABLE
mysql> delete from stu4 where name="Ravi";
Query OK, 1 row affected (0.03 sec)
mysql> select * from stu4;
+----------+--------------+--------+-------+-------+
| name | fname | rollno | marks | grade |
+----------+--------------+--------+-------+-------+
| Sonu | Amit kumar | 101 | 90 | A |
| Sandeep | Ranbir Singh | 103 | 80 | B |
| Akash | Sudhir Singh | 104 | 75 | C |
| Narender | Ajit Singh | 105 | 65 | D |
+----------+--------------+--------+-------+-------+
