Important Mysql Commands with Output

Q.1.Write a query to all databases of MySQL.

mysql> show Databases;
Output
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
Q.2.Write a query to create a database school
mysql> create database school;
Q.3.Write a query to open the database school.
mysql> use school;
Q.4.Write a query to create a table student as per the following structure.
Field Name Datatype Constraint
Roll int(5)
Name Varchar(15)
Marks decimal
Ans:-
mysql> create table student(roll int(5),Name varchar(15),Marks decimal);
Q.5.Write a query to display all the tables present in the current database
school.
mysql> show tables;
output
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
Q.6.Write a query to display the structure of the table student.
mysql> describe student;
output
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| roll | int(5) | YES | | NULL | |
| Name | varchar(15) | YES | | NULL | |
| Marks | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Q.7.Write a query to insert records in to the table student.
mysql> insert into student values(1,'Rahul',89.56);
output
Query OK, 1 row affected, 1 warning (0.05 sec)
Q.8.Write a query to display all records of student table.
mysql> select * from student;
output
+------+-------+-------+
| roll | Name | Marks |
+------+-------+-------+
| 1 | Rahul | 90 |
+------+-------+-------+
1 row in set (0.02 sec)
Q.9.Write a query to insert records in the column roll and name only.
mysql> insert into student(roll,name)values(12,'Ram');
output
Query OK, 1 row affected (0.03 sec)
mysql> select * from student;
+------+-------+-------+
| roll | Name | Marks |
+------+-------+-------+
| 1 | Rahul | 90 |
| 12 | Ram | NULL |
+------+-------+-------+
2 rows in set (0.00 sec)
Q.10.Write a query to insert values in to the table student with atleast one null
value.
mysql> insert into student values(6,NULL,95);
output
Query OK, 1 row affected (0.05 sec)
Checking the changes
mysql> select * from student;
+------+-------+-------+
| roll | Name | Marks |
+------+-------+-------+
| 1 | Rahul | 90 |
| 12 | Ram | NULL |
| 6 | NULL | 95 |
+------+-------+-------+
3 rows in set (0.00 sec)
Q.11.Write a query to add primary key in the column roll of the table student.
mysql> alter table student add primary key(roll);
output
Query OK, 3 rows affected (0.25 sec)
Records: 3 Duplicates: 0 Warnings: 0
Checking the changes
mysql> describe student;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| roll | int(5) | NO | PRI | 0 | |
| Name | varchar(15) | YES | | NULL | |
| Marks | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
Checking the working process of primary key by giving same rollno 6.
Plz observe the error message.
mysql> insert into student values(6,'Shyam',97);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
Q.12.Write a query to remove primary key from the table student.
mysql> alter table student drop primary key;
output
Query OK, 3 rows affected (0.34 sec)
Records: 3 Duplicates: 0 Warnings: 0
Q.13.Write a query to change the column name from roll to roll_no.
mysql> alter table student change roll roll_no int(5);
output
Query OK, 3 rows affected (0.20 sec)
Records: 3 Duplicates: 0 Warnings: 0
Checking the changes
mysql> describe student;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| roll_no | int(5) | YES | | NULL | |
| Name | varchar(15) | YES | | NULL | |
| Marks | decimal(10,0) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Q.14.Write a query to change the size of the datatype of
mysql> alter table student modify name varchar(20);
output
Query OK, 3 rows affected (0.28 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> describe student;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| roll_no | int(5) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| Marks | decimal(10,0) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Q.15.Write a query to add a new column addess with default value ‘Surat’
mysql> alter table student add address varchar(20) default 'Surat';
output
Query OK, 3 rows affected (0.28 sec)
Records: 3 Duplicates: 0 Warnings: 0
Q.16.Write a query to display the name column with heading ‘Student Name’
mysql> select name as 'student name' from student;
output
+--------------+
| student name |
+--------------+
| Rahul |
| NULL |
| Ram |
+--------------+
3 rows in set (0.00 sec)
Q.17.Write a query to add a new column ‘school_name with the value SVV
mysql> alter table student add school_name char(10) default 'SVV';
Query OK, 3 rows affected (0.25 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+---------+-------+-------+---------+-------------+
| roll_no | name | Marks | address | school_name |
+---------+-------+-------+---------+-------------+
| 1 | Rahul | 90 | Surat | SVV |
| 6 | NULL | 95 | Surat | SVV |
| 12 | Ram | NULL | Surat | SVV |
+---------+-------+-------+---------+-------------+
3 rows in set (0.00 sec)
Q.18.Write a query to delete the column school_name from the table student.
mysql> alter table student drop school_name;
Query OK, 3 rows affected (0.25 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+---------+-------+-------+---------+
| roll_no | name | Marks | address |
+---------+-------+-------+---------+
| 1 | Rahul | 90 | Surat |
| 6 | NULL | 95 | Surat |
| 12 | Ram | NULL | Surat |
+---------+-------+-------+---------+
3 rows in set (0.00 sec)
Q.19.Write a query to delete the table student.
mysql>drop table student;
Q.20.Write a query to delete the database school.

mysql>drop database school;