A query is a request for data or information from a database table or combination of tables. This data may be generated as results returned by Structured Query Language (SQL) or as pictorials, graphs, or complex results, e.g., trend analyses from data-mining tools.

This post shows the commonly used Database Queries.

List all Database

SHOW DATABASES;

Use employee Database

USE employees;

List Tables

SHOW TABLES;

SELECT only 100 records from the employee Table

 SELECT
	*
FROM
	employees.employees LIMIT 100;

Select all Female Employees

 SELECT
	*
FROM
	employees
WHERE
	gender = 'F';

Checking a list The word IN allows us to check if an item is in a list

 SELECT
	*
FROM
	employees.employees
WHERE
	last_name IN(
		'Reistad',
		'Simmel',
		'Pettey'
	) LIMIT 10;

Describe salaries database

DESCRIBE salaries;

Describe employee’s database to Check Schema

 DESCRIBE employees;

SELECT
	*
FROM
	salaries limit 10;

Display all Employees and Salary data whose Salary is between 40000 and 50000

BETWEEN allows range checking (range specified is inclusive of boundary values)

 SELECT
	*
FROM
	employees e
INNER JOIN salaries s ON
	e.emp_no = s.emp_no
WHERE
	s.salary BETWEEN 40000 AND 50000 LIMIT 500;

SELECT all data from employee table who join the department after 1980

	SELECT * FROM employees WHERE hire_date > 1980 LIMIT 20;

Find Minimum Salaries of Employees

	SELECT e.first_name,e.last_name, MIN(s.salary) FROM employees e INNER JOIN salaries s 
	ON
	e.emp_no = s.emp_no;

Find all the Employees whose last name starts with “A”

LIKE operator of SQL is used to fetch filtered data by searching for a particular pattern in the where clause.

When ‘%’ symbol is used after A, it means that any character is fine after A.

SELECT * FROM employees.employees WHERE last_name LIKE 'A%';

Find all the Employees whose last name ends with A

When ‘%’ is used before A, it means that we are only interested in the last_name which ends in character A

SELECT * FROM employees.employees WHERE last_name LIKE '%A';

Generate row number in SQL Without ROWNUM

SELECT name ,salary , (SELECT COUNT(*) FROM EMPLOYEE
e WHERE o.name >= e.name) row_num
FROM EMPLOYEE o
ORDER BY row_num

Select the first 5 records from a table

SQL server (Use TOP Command)

SELECT TOP 5 * FROM EMPLOYEE;

Oracle (Use ROW NUM Command)

SELECT * FROM EMPLOYEE WHERE ROWNUM <=5

# Generic Method

SELECT name FROM EMPLOYEE o WHERE 
(SELECT COUNT(*) FROM EMPLOYEE i WHERE i.name < o.name)
< 5;