In my previous articles I have explained different SQL Queries with real life examples. In this article I would like to concentrate on MySQL database queries. The MySQL is also one of the popular database management system which is used by mid-level companies in their products. I would like to explain different MySQL queries which are really used in day-to-day activities in the industry. I will try to explain the concept which is used for MySQL Queries with the examples. As MySQL is open source database so many companies are widely using MySQL as database management system.
Different MySQL Examples :
In this section I would like to give different MySQL Queries examples from database creation to different complex sql queries in MySQL.
1.How to Open the MySQL prompt using root MySQL user?
Answer :
The first and very important to open up the MySQL prompt with using the root user of MySQL.
Open MySQL with Super User with following command
$Sudo MySQL
Or
$mysql -u root -p
You will see direct MySQL Prompt,
MySQL>
Now user is connected to MySQL.
2.How to create database in MySQL?
Answer :
We need to use Create Database Query to create new database in MySQL.
MySQL> Create Database ‘Cust_test’;
The above query will create a new database in MySQL named Cust_test.
MySQL>use Cust_test;
The above query will be used to enter in to the specified database.
3.How to create table in MySQL?
Answer :
This is another very simple query.Unlike the oracle and SQL server;MySQL is using create table statement to create the table in specific database.
Example :
Create table Customer_Data
(Customer_id real,
Customer_Name varchar2(30));
The MySQL datatypes are bit different than Oracle or SQL Server datatypes. The above statement is used to create the Customer_Data table.
4.Can We insert multiple values in one statement in MySQL? If Yes How?
Answer :
Yes we can insert multiple values in One statement in MySQL. Following statement is used to insert multiple values in table,
Insert into Customer_Data
(Customer_id,Customer_Name)
Values(1,’Amit’),
(2,’Pradnya’),
(3,’Sai’),
(4,’Bikesh’);
Using above query you can add the multiple values in same table at a same time. You will get output after executing this query as follows :
Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
5.How to select the customer data where customer id is 1.
Answer :
This is simple query. User needs to use simple where condition like we are using in oracle or sql server.
Select * from Customer_data where Customer_id=1;
In this query we are using = (equal to) operator. Everyone has question in mind that which are different MySQL operators used,
| MySQL Operator | Work of Operator |
|---|---|
= | This operator is used to test equality condition. |
!= | This operator is used to test inequality condition. |
< | This operator is used to test less-than condition |
> | This operator is used to test greater-than condition |
<= | This operator is used to test less-than or equal-to condition |
>= | This operator is used to test greater-than or equal-to condition |
BETWEEN | tests whether a value lies within a given range |
IN | tests whether a row’s value is contained in a set of specified values |
EXISTS | tests whether rows exist, given the specified conditions |
LIKE | tests whether a value matches a specified string |
IS NULL | tests for NULL values |
IS NOT NULL | tests for all values other than NULL |
6. How to fetch the values from Customer_Information table where name starts with A.
Answer:
User need to use the like operator to fetch the customer_information table,
Select * from Customer_data where customer_name Like ‘A%’;
7.How to give the aliases to table in MySQL?
Answer:
Select Customer_Id as Cust_id,Customer_name as Cust_name from Customer_Information;
Some MySQL Queries with different MySQL Functionalities :
Lets consider Employee table,
8.How to fetch the count of Employees where department is ‘IT’?
Answer :
To fetch the values user needs to use count aggregate function ,
Select count(*) from Employees where department_name=’IT’;
9.How to fetch employees where salary is between 1000 to 20000.
Answer :
User can use the MySQL between..And Operator,
Select * from Employees where Salary between 1000 and 20000;
10.How to fetch the departmentwise count of Employees with descending order.
Answer :
Select department_name,count(Employee_name) as count_emp from Employees
group by department_name
order by count_emp Desc;
11.What is query to fetch Firstname from Employee_Full_Name columns from Employees table. In between first name and last name blanks space is there.
Answer:
User needs to use MID function in MySQL queries ,
SELECT MID(Employee_Full-Name, 0, LOCATE(‘ ‘,Employee_Full-Name)) FROM Employees;
12.Suppose there are 2 tables one is Employees table and other is Salary table. We need to fetch the records from Employee table who are not receiving salaries.
Answer :
In this scenario user needs to check the Employee table records which are not present in Employee_salary table. So we will use Left Join
SELECT E.Employee_FullName, S.Salary FROM Employees E LEFTJOIN Employees_Salary S ON E.EmpId = S.EmpId;
13.How to fetch Employee_name and his Manager_name from Employees table.
Answer :
Here I am considering in Employees table manager id is also there. The self join can resolve this issue,
Select E.Employee_name as Employee,M.Employee_name as Manager From
Employee E,Employee M where E.Employee_id=M.Manager_id;
14.What is query to fetch Employee data which exist in Employee_Salary table.
Answer:
SELECT * FROM Employees E WHERE EXISTS (SELECT * FROM Employees_Salary S WHERE E.EmpId = S.EmpId);
15.How to Find out the Position of ‘m’ in word Amit using MySQL?
Answer :
We need to use Locate function to find out specific position of letter in the word.
Select LOCATE(‘m’,FIRST_NAME) from Employees where first_name=’Amit’;
16.How to concate first_name and last_name columns from Employees table in MySQL.
Answer :
We have concat function in MySQL to concate two strings or columns.
Select concat(FIRST_NAME,’_’,LAST_NAME) from Employees;
17.How to Find out duplicate records from the table.
Answer :
I have already explained detailed process for finding out and deleting the duplicate records in the table.
SELECT Employee_id, Department, Salary, COUNT(*) FROM Employee_Salary GROUPBY Employee_id, Department, Salary, HAVINGCOUNT(*) > 1;
18.How to find out the joining day,Joining month and joining year of Employee?
Answer :
We need to use MySQL date function to fetch this data,
select year(date_of_joining),month(date_of_joining), DAY(date_of_joining) from Employees;
19.Write a query to fetch all employees who has joined in year 2019.
Answer :
We need to use Year function in MySQL,
Select * from Employees where Year(Date_of_Joining)=2019;
20.How to find out Joining date with time in Employees table using MySQL?
Answer:
Select CONVERT(DATE_FORMAT(joining_date,’%Y-%m-%d-%H:%i:00′),DATETIME) from Employees;
21.How to Find out current date in database in MySQL?
Answer :
We need to use now() function to Find out current date in MySQL.
Select now();
I have tried to cover most important MySQL queries in this article. If you like this article or if you have any suggestions kindly comment in to comments section.