SQL Level 1

One might agree that Excel files containing huge data, that is stored cumulatively, makes it bulky and difficult to work with.

MySQL, on the other hand, is capable of storing large amount of datasets, that can be updated from time to time without consuming too much space in our system.

We just have to run simple queries in the system to get the required information whenever and wherever required.

Let's create some sample data sets to being with and run basic calculations over the same.

In MySQL Workbench we will first create a database 'WebContent'. This will be used to store all our data sets.

#Type below command and hit shift+enter to run it.

create database WebContent;
use WebContent;

I have created a sample data set 'Emp' with Emp Payroll related details.

Data can be imported/exported in excel or csv. format into the MySQL system

Emp Datset

#To view contents from data set:

select * from Emp;

Find the Total Number of Employees in the 'Emp' dataset.

There are 27 employees in our sample dataset.

select COUNT(Emp_ID) as Emp_Count from Emp;

(count of rows in column 'Emp_ID' from 'Emp' dataset)

Find out Department wise Total Number of Employees in the 'Emp' dataset.

There are 14 employees in Collection Department and 13 employees in Sales Department.

select Department, COUNT(Emp_ID) as Emp_Count from Emp group by Department;

(Show Column 'Department' & count of rows in column 'Emp_ID' grouped by categories in column 'Department')

Display Total commission paid to respective departments.

Total commission paid to Collection department is 17400 and to Sales department is 21100.

select Department, SUM(commission) as Emp_Commission from Emp group by Department;

(Show Column 'Department' & Sum total in column 'commission' grouped by categories in column 'Department'.

Simply replaced COUNT from the previous command to SUM.)

Display Average Commission of each department.

The Average Commission of Collection Department is 1242.86 and for Sales Department is 1623.08

select Department, AVG(commission) as Emp_Commission from Emp group by Department;

(Show Column 'Department' & Average in column 'commission' grouped by categories in column 'Department'.

Simply replaced SUM from the previous command to AVG.)

Display top 5 employees with commission amount above average.

Employees with ID 20, 17, 14, 26 & 25 have the highest commission (more than avg).

SELECT * from Emp where commission > (SELECT AVG(commission) from Emp) order by commission DESC limit 5;

(Show top 5 Employees with the highest commission or higher than Avg. Commission from data 'Emp' )

SQL: COUNT

SQL: SUM

SQL: Average

Display bottom 5 employees with commission amount below average.

Employees with ID 27, 11, 24, 19 & 6 have the lowest commission (less than avg).

SELECT * from Emp where commission < (SELECT AVG(commission) from Emp) order by commission ASC limit 5;

(Show bottom Employees with the lowest commission or lower than Avg. Commission from data 'Emp' )

Below listed are some of the commands that can be used to derive basic information related to a dataset.

Scroll left to right to view Purpose column

Sr.No	Command	Purpoase
1	SELECT	To display content from dataset. All SQl commands starts with 'Select'.
2	WHERE	To display data with a condition
3	LIMIT	Data filter for numaric columns
4	ORDER BY	Display data arranged in particular order (Numarical/Alphabetical)
5	GROUP BY	To display combined figures of categorical data
6	AVG	To display Average figure of numarical column
7	SUM	To display Sum Total figure of numarical column
8	COUNT	To display total count of contents in a column (numaric/alphabetic)
9	AND	To display data with condition1 AND condition2 AND so on...
10	OR	To display data either with condition1 OR condition2 OR so on...
11	MIN	Data filter for numaric columns with minimum value
12	MAX	Data filter for numaric columns with maximum value
13	LIKE	Data filter with columns containing sertain alphabets (xyz%- Starts with xyz \| %xyz- ends with xyz \| %xyz% - contains xyz)
14	BETWEEN	Data filter in a column with values between certain range.
15	NOT BETWEEN	Data filter in a column with values not between certain range.

SQL: Basic Commands

Download MySQL Script