Tags

, , ,

In this tutorials, I want to bring in a few tricks that every beginners in SQL must know. You can also use my sqlfiddle.

/* Below code creates a database and a table into it */
CREATE DATABASE MyEmployees;
USE MyEmployees;
CREATE TABLE Employees(
ID int (3) NOT NULL,
Name varchar (50) NOT NULL,
Age int (3) NOT NULL,
Nationality varchar (50) NOT NULL
);

/*Now we will insert some Sample Data into the table in the database we just created above*/
INSERT INTO Employees
(ID, Name, Age, Nationality)
VALUES
(1, ‘CHIN YEN’, ’19’, ‘China’),
(2, ‘MIKE PEARL’, ’21’, ‘United Kingdom’),
(3, ‘GREEN FIELD’, ’45’, ‘Nethernalnds’),
(4, ‘DEWANE PAUL’, ’57’, ‘Canada’),
(5, ‘MATTS’, ’32’, ‘Australia’),
(6, ‘PLANK OTO’, ’51’, ‘France’),
(7, ‘Manoj Kumar’, ’42’, ‘India’),
(8, ‘Matts’, ’55’, ‘USA’),
(9, ‘Manoj Kumar’, ’32’, ‘USA’),
(10, ‘Osaka Nakasaki’, ’21’, ‘Japan’),
(11, ‘Albert Orja’, ’14’, ‘Nethernalnds’),
(12, ‘Hansui Makasi’, ’60’, ‘Japan’),
(13, ‘Jack Chan’, ’55’, ‘China’),
(14, ‘Kate Winslett’, ’27’, ‘United Kingdom’),
(15, ‘John Mayers’, ’14’, ‘Astralia’);

/*__________________________________________________________________________________*/

Next we will Start Exploring the data from various perspective which
are commented before the operation we want to perform on the data.

/* Select everything from the table */
SELECT * FROM Employees;

/*select only Names from the data*/
SELECT Name FROM employees;

/*select only distinct names*/
SELECT DISTINCT Name FROM employees;

/* How many rows are there in the data */
SELECT COUNT(*) FROM Employees;

/* Full Explanation of How many rows */
EXPLAIN SELECT COUNT(*) FROM Employees;

/* As you’ve seen, COUNT(*) tells you how many rows are in a table.
However, if you want to count the number of non-missing values in
a particular column, you can call COUNT on just that column. */

SELECT COUNT(Name) FROM Employees;

/*It’s also common to combine COUNT with DISTINCT to count the number of distinct values in a column.
For example, this query counts the number of distinct birth dates contained in the people table:*/

SELECT COUNT(DISTINCT Name) FROM Employees;

/*Using Where Clause*/
SELECT * FROM Employees WHERE Nationality = ‘USA’;

/*Using Where and And Clauses*/
SELECT * FROM Employees WHERE Age = ’32’ and Nationality = ‘USA’;

/*Where and Or Clauses */
SELECT * FROM Employees WHERE Age = ’32’ OR Nationality = ‘USA’;

/*Where and Multiple And Clauses */
SELECT Name, Age FROM Employees WHERE Age > ’32’ AND Nationality = ‘USA’;

/*When combining AND and OR, be sure to enclose the individual clauses in parentheses, like so:*/
SELECT * FROM Employees WHERE (Age = ’55’ OR Age 20);

/* NULL and IS NULL Clauses */
SELECT Name FROM Employees WHERE Age IS NULL;
SELECT Name FROM Employees WHERE Age IS NOT NULL;

/*LIKE and NOT LIKE Clauses*/
SELECT Name, Age FROM Employees WHERE Name LIKE ‘M%’;

/*Aggregate functions*/
SELECT AVG(Age) FROM Employees;
SELECT SUM(Age) FROM Employees;
SELECT MAX(Age) FROM Employees;

/*Combining aggregate functions with WHERE*/
SELECT AVG(Age) FROM Employees WHERE age >= 40;
SELECT AVG(Age) FROM Employees WHERE Name LIKE ‘M%’;
SELECT MIN(gross) FROM films WHERE release_year = 1994;
SELECT MAX(gross) FROM films WHERE release_year >= 2000 AND release_year 0;

/* Nationality and their percentage in the total */
Select Nationality,
(Count(Nationality)* 100 / (Select Count(*) From Employees)) as Percentage_Indian
From Employees
Group By Nationality

/* Percentage in the total for an individual country (say, India) */
Select Nationality,
(Count(Nationality)* 100 / (Select Count(*) From Employees)) as Percentage_Indian
From Employees
Where Nationality = ‘India’
Group By Nationality

/* Along with the count of Nationality */
Select Nationality, Count(Nationality) as Count_of_Nationality,
(Count(Nationality)* 100 / (Select Count(*) From Employees)) as Percentage_Indian
From Employees
Where Nationality = ‘Australia’
Group By Nationality

Advertisements