MySQL for Beginners I
MySQL Audience
This tutorial is prepared for the beginners to help them understand the basics-to-advanced concepts related to MySQL languages.
Who am I?
Java Developer by profession. Exploring MySQL for performing complex queries. You can check my GitHub account you will find more to learn. If you want any help or wanna give any suggestion contact me.
Important
There will be MySQL for Beginners part II of this Blog where I will cover difference between group by and order by, Where and Having clause, and some complex queries like finding Organization Hierarchy and other commonly asked Interview Questions)
MySQL — Introduction
MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various web-based software applications.
Lets get our hands dirty…
First thing first..
Step 1.Creating Database and Tables.
create database mysql_for_beginners;
use mysql_for_beginners;
show tables;
You can see there is no table present in this database.
Lets create some tables now.
Datatypes used in these tables are int, date, varchar and enum(To give option value).Primary key of employee table is emp_no and dept_no in department table.
But these table is not connected yet so we have to create a new table which connect both employee and department table using foreign key concept.
create table emp_dept(emp_no int references employee.emp_no,dept_no int references department.dept_no,from_date date,to_date date);
Using “emp_no int references employee.emp_no” the emp_dept will be connected to employee table.Same using “dept_no int references department.dept_no” will connect emp_dept to department table since both emp_no and dept_no are the primary key of their respective tables.
Note: There is No primary Key in emp_dept table. So if one think can we create a table without primary key in it? The answer is YES. but its not a good practice to create a table without a primary key as if we want to refer a specific table we need a primary key.
Note: To delete a Table use DROP TABLE EMPLOYEE and to delete a database use DROP EMP_DATABASE.
Step 2. Inserting Data into Tables.
There are two ways to insert data into table.
(i). Inserting data one by one.
insert into employee values(4120,’1995–02–18',’Vishal’,’Sharma’,’M’,’2020–02–01');
insert into employee values(4140,’1995–01–08',’Swati’,’Singh’,’F’,’2019–06–13');
insert into employee values(4150,’1997–10–03',’Ankit’,’sagar’,’M’,’2020–04–07');
insert into employee values(4160,’1996–07–18',’Divya’,’chouhan’,’F’,’2020–05–01');
(ii). Inserting data in a single row. Values separated by a comma(,).
insert into department values(2001,’HR’),(3001,’Sales’),(4001,’Tech’),(5001,’Support’);
insert into emp_dept values(4120,4001,’2020–02–01',NULL),(4150,4001,’2020–04–07',NULL),(4140,2001,’2020–06–13',’2020–10–23'),(4160,3001,’2020–05–01',NULL);
Note: A field with a NULL value is a field with no value.
Step 3. Fetching data from tables.
select * from employee;
select * from department;
select * from emp_dept;
Topic 1. SQL WHERE clause.
(i). Using WHERE clause find the employee whos emp_no is 4160.
select * from employee where emp_no=4160;
‘*’ is used to find all data of a table. You can replace it with first_name or any other field of same table.
Topic 2. Use of SQL AND,OR and NOT statements.
(i)Employee number, First name of those employee who are Male and Employee number is greater than 4130.
select emp_no,first_name from employee where gender=’M’ AND emp_no>4130;
In case of AND if both of the values are true the result will be true else false.
Employee number, First name of those employee who are Male or Employee number is greater than 4130.
select emp_no,first_name from employee where gender=’M’ OR emp_no>4130;
In case of OR if any of the values are true the result will be true.
(iii).Employees who are Male but their Employee No. is not greater than 4140.
select emp_no,first_name from employee where gender=’M’ AND NOT emp_no>4140;
Topic 3. Use of Order By clause.
(i).Employee details sort by hire date.(By default the sorting will be done in ascending order.)
select * from Employee order by hire_date;
select * from Employee order by birth_date;
DESC keyword is used to sort in descending order.
Topic 4. MIN(),MAX(),AVG() functions in MySQL.
Creating a new table Salary and Inserting some data into this table.
create table salary(salary_id int primary key,amount varchar(10) not null,emp_no int references employee(emp_no));
insert into salary values(101,’82000',4120),(102,’22000',4140),(103,’32000',4150),(104,’23000',4160);
(i) Finding Employee no who get highest salary.
select emp_no,max(amount) from salary;
we can set alias for the output of function to be shown as column name using “as” keyword.
select emp_no,max(amount) as RAEES from salary;
(ii) Finding Employee no who get lowest salary.
select emp_no,min(amount) as INTERN from salary;
(iii) Finding Average Salary of all Employee.
select avg(amount) as Basic_Salary from salary;
Topic 5. SQL COUNT()and SUM() Functions.
(i) Counting total number of MALE Employee in the company.
select count(emp_no) as total_male_employee from employee where gender=’M’;
(ii) Finding total of the salaries where employee number after 4140.
select sum(amount) as total from salary where emp_no>4140;
Topic 6. SQL LIKE Operator.
There are two wildcards often used in conjunction with the LIKE operator:
- % — The percent sign represents zero, one, or multiple characters
- _ — The underscore represents a single character
(i). Finding Department no from department table where department name=’HR’ .
select dept_no,dept_name from department where dept_name LIKE ‘HR’;
(ii). Finding Department no from department table where department name starting with ‘S’ character .
select dept_no,dept_name from department where dept_name LIKE ‘S%’;
(iii). Finding Department no from department table where department name starting with ‘H’ character and followed by only one charater .
select dept_no,dept_name from department where dept_name LIKE ‘H_’;
Topic 7. SQL IN Operator.
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
(i)Finding all values from department table where department name is ‘Tech’ OR ‘Sales’.
select * from department where dept_name IN (‘Tech’,’Sales’);
Topic 8. SQL BETWEEN Operator.
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
(i). Details of employee who’s hire date in in between provided dates(both inclusive).
select * from employee where hire_date BETWEEN ‘2020–02–03’ AND ‘2020–06–03’;
Note: The BETWEEN operator is inclusive: begin and end values are included.
Topic 9. SQL JOINS.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
(i). INNER JOIN
Finding FirstName,LastName,Gender from Table employee and Department Number and Joining Date from table emp_dept using INNER JOIN.
select first_name,last_name,gender,dept_no,from_date from employee as e INNER JOIN emp_dept as ed on e.emp_no=ed.emp_no;
(ii). LEFT JOIN: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
select first_name,last_name,gender,dept_no,from_date from employee as e left join emp_dept as ed on e.emp_no=ed.emp_no;
NOTE. Since there is no corresponding entry for employee Remesh is available in emp_dept table so we can’t see his record using inner join as inner join gives only matching values from both table.
(ie. employee.emp_no=emp_dept.emp_no)
(iii). RIGHT JOIN: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
select d.dept_no,dept_name,emp_no,from_date joining_date from emp_dept ed INNER JOIN department d on d.dept_no=ed.dept_no;
select d.dept_no,dept_name,emp_no,from_date joining_date from emp_dept ed RIGHT JOIN department d on d.dept_no=ed.dept_no;
(iii). FULL OUTER JOIN
You don’t have FULL JOINS on MySQL, but you can sure emulate them using UNION.
select d.dept_no,dept_name,emp_no,from_date joining_date from emp_dept ed left join department d on d.dept_no=ed.dept_no UNION ALL select d.dept_no,dept_name,emp_no,from_date joining_date from department d left join emp_dept ed on d.dept_no=ed.dept_no;
Topic 10. SELF JOINS.
A self JOIN is a regular join, but the table is joined with itself.
Creating a new table(Employees) where Employee and employee manager are stored in same table.(Manager is also an Employee having emp_id as others);
create table employees(emp_id int primary key,name varchar(20) not null,manager_id int);
insert into employees values(4130,’Shivam Sharma’,2010),(4133,’Ankit Sagar’,2001),(2010,’Nikhil Mittal’,1000),(2001,’Manoj Purohit’,1000),(1000,’Deepak Mittal’,null);
Note: Here in the Table structure you can see that the manager_id column of employee Deepak Mittal(emp_id 1000) is NULL. So we can say that there is no boss of Deepak or He’s the CEO of the company.
(i). Using self join find the employee and their manager.
select e.name as employee,m.name as manager from employees e join employees m on e.manager_id=m.emp_id;
(ii). Fetching also the name of Employee who has no manager.
select e.name as employee,m.name as manager from employees e left join employees m on e.manager_id=m.emp_id;
(iii). Printing “BOSS” in place of “NULL”;
select e.name as employee,IFNULL(m.name,’BOSS’) as manager from employees e left join employees m on e.manager_id=m.emp_id;
(iv). Finding all employee name who’s manager is “Deepak mittal”.
select e.name as employee,m.name as manager from employees e join employees m where e.manager_id=m.emp_id and m.name=’Deepak Mittal’;
Hope you’re learning good. End of part I..click here for part II.