Query for viewing the Databases in a MySQL Database
MariaDB [(none)]> show databases;
Output
+--------------------+
| Database |
+--------------------+
| information_schema |
| lab |
| mysql |
| performance_schema |
| phpmyadmin |
+--------------------+
5 rows in set (0.002 sec)
Query for Creating a Database in MySQL
MariaDB [(none)]> create database satish;
Output
Query OK, 1 row affected (0.184 sec)
Query for viewing the created database inside a MySQL Database
MariaDB [(none)]> show databases;
Output
+--------------------+
| Database |
+--------------------+
| information_schema |
| lab |
| mysql |
| performance_schema |
| phpmyadmin |
| satish |
+--------------------+
6 rows in set (0.001 sec)
Query for Dropping a database in MySQL Database
MariaDB [(none)]> drop database satish;
Output
Query OK, 0 rows affected (0.115 sec)
Query for switching a databases in MySQL Database
MariaDB [(none)]> use satish;
Output
Database changed
Query for creating a table by name Employee
MariaDB [satish]> create table employee(
-> eid int(10),
-> name varchar(10),
-> phone char(10),
-> salary int(10),
-> address varchar(20),
-> joindate date);
Output
Query OK, 0 rows affected (0.448 sec)
Query to Describe the employee table
MariaDB [satish]> desc employee;
Output
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | char(10) | YES | | NULL | |
| salary | int(10) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| joindate | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (1.129 sec)
Query for viewing all the tables in a MySQL Database
MariaDB [satish]> show tables;
Output
+------------------+
| Tables_in_satish |
+------------------+
| employee |
+------------------+
1 row in set (0.015 sec).
Query for Dropping a column (address column) in the employee table
MariaDB [satish]> alter table employee drop column address;
Output
Query OK, 0 rows affected (1.184 sec)
Records: 0 Duplicates: 0 Warnings: 0
The column is dropped when we describe the table as shown below
MariaDB [satish]> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | char(10) | YES | | NULL | |
| salary | int(10) | YES | | NULL | |
| joindate | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.968 sec)
Query for adding a column (address column) to the employee table
MariaDB [satish]> alter table employee add address varchar(20);
Output
Query OK, 0 rows affected (0.997 sec)
Records: 0 Duplicates: 0 Warnings: 0
The column is added and can be seen when we describe the table
MariaDB [satish]> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | char(10) | YES | | NULL | |
| salary | int(10) | YES | | NULL | |
| joindate | date | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.907 sec)
Query for changing the data type for a column in the employee table
MariaDB [satish]> alter table employee modify eid char(10);
Output
Query OK, 0 rows affected (2.737 sec)
Records: 0 Duplicates: 0 Warnings: 0
The data type change is seen when we describe the table
MariaDB [satish]> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | char(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | char(10) | YES | | NULL | |
| salary | int(10) | YES | | NULL | |
| joindate | date | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (1.007 sec)
Query for Inserting a record in the employee table
MariaDB [satish]> insert into employee values(
-> '121','Satish','878877',7000,'1992-01-21','vellore');
Output
Query OK, 1 row affected (0.738 sec)
Query for Viewing records from the employee table
MariaDB [satish]> select * from employee;
Output
+------+--------+--------+--------+------------+---------+
| eid | name | phone | salary | joindate | address |
+------+--------+--------+--------+------------+---------+
| 121 | Satish | 878877 | 7000 | 1992-01-21 | vellore |
+------+--------+--------+--------+------------+---------+
1 row in set (0.022 sec)
Query for Adding a primary key constraint using alter table command
MariaDB [satish]> alter table employee add constraint employee_eid_pr primary key(eid);
Output
Query OK, 0 rows affected, 1 warning (2.273 sec)
Records: 0 Duplicates: 0 Warnings: 1
The primary key constraint is enforced and can be seen when we describe the table
MariaDB [satish]> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | char(10) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | char(10) | YES | | NULL | |
| salary | int(10) | YES | | NULL | |
| joindate | date | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.728 sec)
Query for Modifying a data type using alter table command
MariaDB [satish]> alter table employee modify name varchar(10) not null;
Output
Query OK, 0 rows affected (2.112 sec)
Records: 0 Duplicates: 0 Warnings: 0
The datatype change can be seen when we describe the table
MariaDB [satish]> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | char(10) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| phone | char(10) | YES | | NULL | |
| salary | int(10) | YES | | NULL | |
| joindate | date | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.820 sec)
Query for Adding a unique key constraint on the phone column in the employee table
MariaDB [satish]> alter table employee add constraint employee_phone_uk unique(phone);
Output
Query OK, 0 rows affected (0.899 sec)
Records: 0 Duplicates: 0 Warnings: 0
The unique key constraint being enforced on the table is shown below
MariaDB [satish]> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | char(10) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| phone | char(10) | YES | UNI | NULL | |
| salary | int(10) | YES | | NULL | |
| joindate | date | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.992 sec)
Query for Adding a check constraint on the employee table salary column
MariaDB [satish]> alter table employee add constraint employee_salary_ck check(salary>0);;
Output
Query OK, 1 row affected (2.652 sec)
Records: 1 Duplicates: 0 Warnings: 0
Queries for Creating a department table and establishing department id as the primary key
MariaDB [satish]> create table department (
-> did int(10),
-> dname char(10));
Output
Query OK, 0 rows affected (0.423 sec)
MariaDB [satish]> alter table department add constraint department_did_pr
-> primary key(did);
Output
Query OK, 0 rows affected, 1 warning (1.877 sec)
Records: 0 Duplicates: 0 Warnings: 1
Queries for establishing a foreign key constraint between the employee table
and the department table.
Step 1:
//add the dep_id as a column to the employee table
MariaDB [satish]> alter table employee add dep_id int(10);
Output
Query OK, 1 row affected (0.738 sec)
Step 2:
//update the dep_id column values in the employee table
MariaDB [satish]> update employee set dep_id=424 where eid=121;
Output
Query OK, 1 row affected (0.448 sec)
Rows matched: 1 Changed: 1 Warnings: 0
step 2:
//update the dep_id column values in the employee table
MariaDB [satish]> update employee set dep_id=425 where eid=122;
Output
Query OK, 1 row affected (0.033 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//employee table with values updated in the dep_id column is shown below
MariaDB [satish]> select * from employee;
+-----+--------+--------+--------+------------+---------+--------+
| eid | name | phone | salary | joindate | address | dep_id |
+-----+--------+--------+--------+------------+---------+--------+
| 121 | Satish | 878877 | 7000 | 1992-01-21 | vellore | 424 |
| 122 | Mathew | 767766 | 3000 | NULL | vellore | 425 |
+-----+--------+--------+--------+------------+---------+--------+
2 rows in set (0.001 sec)
//The records from the department table are as shown below
MariaDB [satish]> select * From department;
+-----+-------+
| did | dname |
+-----+-------+
| 424 | SCOPE |
| 425 | SITE |
+-----+-------+
2 rows in set (0.001 sec)
step 3:
Establishing foreign key constraint between employee and department table
MariaDB [satish]> alter table employee add constraint employee_depid_fr
-> foreign key(dep_id) references department(did);
Output
Query OK, 2 rows affected (2.619 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query for Performing Equi Join between employee and department table
MariaDB [satish]> select eid,name,dname from employee,department where dep_id=did;
Output
+-----+--------+-------+
| eid | name | dname |
+-----+--------+-------+
| 121 | Satish | SCOPE |
+-----+--------+-------+
1 row in set (0.032 sec)
Query for Performing left join between employee and department table
MariaDB [satish]> select eid,name,dname from employee left join department on dep_id=did;
Output
+-----+--------+-------+
| eid | name | dname |
+-----+--------+-------+
| 121 | Satish | SCOPE |
| 122 | Mathew | NULL |
+-----+--------+-------+
2 rows in set (0.040 sec)
Query for Performing Right Join between employee and the department table
MariaDB [satish]> select eid,name,dname from employee right join department on
-> dep_id=did;
Output
+------+--------+-------+
| eid | name | dname |
+------+--------+-------+
| 121 | Satish | SCOPE |
| NULL | NULL | SITE |
+------+--------+-------+
2 rows in set (0.001 sec)
Query for Performing Cross join between employee and the department table
MariaDB [satish]> select * from employee cross join department;;
Output
+-----+--------+--------+--------+------------+---------+--------+-----+-------+
| eid | name | phone | salary | joindate | address | dep_id | did | dname |
+-----+--------+--------+--------+------------+---------+--------+-----+-------+
| 121 | Satish | 878877 | 7000 | 1992-01-21 | vellore | 424 | 424 | SCOPE |
| 122 | Mathew | 767766 | 3000 | NULL | vellore | NULL | 424 | SCOPE |
| 121 | Satish | 878877 | 7000 | 1992-01-21 | vellore | 424 | 425 | SITE |
| 122 | Mathew | 767766 | 3000 | NULL | vellore | NULL | 425 | SITE |
+-----+--------+--------+--------+------------+---------+--------+-----+-------+
4 rows in set (0.001 sec)
Query for Finding sum of salary for all employees
MariaDB [satish]> select sum(salary) from employee;
Output
+-------------+
| sum(salary) |
+-------------+
| 70000 |
+-------------+
1 row in set (0.060 sec)
Query for Finding average salary for all employees
MariaDB [satish]> select avg(salary) from employee;
Output
+-------------+
| avg(salary) |
+-------------+
| 17500.0000 |
+-------------+
1 row in set (0.000 sec)
Query for Finding the minimum salary from the employee table
MariaDB [satish]> select min(salary) from employee;
Output
+-------------+
| min(salary) |
+-------------+
| 3000 |
+-------------+
1 row in set (0.172 sec)
Query for Finding the maximum salary from the employee table
MariaDB [satish]> select max(salary) from employee
Output
+-------------+
| max(salary) |
+-------------+
| 50000 |
+-------------+
1 row in set (0.001 se)
Query for finding the count of employees in the table
MariaDB [satish]> select count(salary) from employee;
Output
+---------------+
| count(salary) |
+---------------+
| 4 |
+---------------+
1 row in set (0.001 sec)
Query for using between operator
MariaDB [satish]> select eid,name from employee where salary between 1000 and 10000;
Output
+-----+--------+
| eid | name |
+-----+--------+
| 121 | Satish |
| 122 | Mathew |
| 124 | Chris |
+-----+--------+
3 rows in set (0.060
Query for Finding the total salary for employees grouped by address
MariaDB [satish]> select address,sum(salary) from employee group by address;
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| mumbai | 60000 |
| vellore | 10000 |
+---------+-------------+
2 rows in set (0.018 sec)
Finding the sum of salary for employees grouped by address and filtering only sum of salary that is greater than 10000 rupees using the having clause
MariaDB [satish]> select address,sum(salary) from employee group by address having sum(salary)>10000;
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| mumbai | 60000 |
+---------+-------------+
1 row in set (0.100 sec)
Query for demonstrating the use of order by clause in ordering groups in the ascending order
MariaDB [satish]> select address,sum(salary) from employee group by address order by sum(salary);
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| vellore | 10000 |
| mumbai | 60000 |
+---------+-------------+
2 rows in set (0.001 sec)
Query for Ordering the groups in the descending order using order by clause
MariaDB [satish]> select address,sum(salary) from employee group by address order by sum(salary) desc;
Output
+---------+-------------+
| address | sum(salary) |
+---------+-------------+
| mumbai | 60000 |
| vellore | 10000 |
+---------+-------------+
2 rows in set (0.001 sec)