SQL programming language is the language used to communicate with the database to do some specific tasks like creating new databases/schemas/tables, inserting data into tables, selecting data from the table. Very huge analysis reports also can be done using the queries. We will discuss some of them later.
Install any open source database like mysql or SQL server. Download My SQL from the following link: https://dev.mysql.com/downloads/installer/
All the queries that are being used here will be in MySQL. More than 90% of the basic query structure will be same in all the databases. We will also discuss some of the differences among databases at the end.
There are different types of queries:
1. DDL (Data Definition Language):
The queries which are used to define the data (datatypes of the fields, tables definition etc..)
2. DML (Data Manipulation Language):
Manipulation means inserting the data , updating the existing data , selecting the existing data, ... the queries which are used for all those comes under this section.
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)
Most of the analysis, reports, interview questions are on "how to select the data".
So first, we will discuss about selecting the data from the tables in a database. Just keep the below SQL structure in mind.You will be able to clear most of the queries by understanding the below structure completely.
SELECT
Col1,
Col2,
Col3
FROM
{TABLE_NAME }
{JOIN}..... ON...
WHERE
{conditions}
GROUP BY ....
HAVING ....
ORDER BY ....
LIMIT ...
For better understanding of the above structure, we will go with one example.
Ex: Let's assume we have a box with 6 red cubes, 5 blue cubes, 7 yellow cubes and 3 white cubes
Ex-1: "I want all cubes" Just think the same sentence in general by adding some more details to it.
General: Pick all the cubes from the box.
(Lets see this in SQL)
SQL:
SELECT
*
FROM
BOX;
Lets see the transition:
Pick all the cubes from the box. --> General English
SELECT * FROM BOX; --> SQL
(BOX is considered as the "table")
We can write any simple SQL statement by thinking as above. Now, let's try another example.
Ex-2: I want all red cubes.
General: Pick all the cubes from the box which are red in color.
SQL:
SELECT
*
FROM
BOX
WHERE
colour = 'red';
Let's see the translation:
Pick all the cubes from the box which are red in color.
SELECT * FROM BOX WHERE color = 'red';
(color is a property of cube . However color is considered as one of the column of table, BOX)
-- In practical, the properties of an object are converted into columns of a table in database.
Ex-3: I want only 6 cubes.
General: Pick all the cubes from the box till you pick 6 cubes.
SQL:
SELECT
*
FROM
BOX
LIMIT 6;
Ex-4: I want 2 red cubes.
(Try this without scrolling down)
General: Pick all the cubes from the box which are red in color till you pick 2 cubes.
SQL:
SELECT
*
FROM
BOX
WHERE
color = 'red'LIMIT 2;
Nice!!! Till now we have covered SELECT, WHERE, LIMIT. Same way we will learn other parts of the SELECT query.
Ex-5: I want to know how many cubes are present in the box
General: Pick each cube of the box and count
SQL:
SELECT
COUNT(*)
FROM
BOX;
NOTE: Here, we have seen one new thing count(*). This is called "aggregate function" - This is so important to know.
aggregate function? Aggregation is calculating something from in a group of items.
Lets see an example to understand this more. We all know how the marks sheet looks like. Now, lets assume you are the class teacher and you are seeing the below sheet:
By looking at all the marks that scored by "Preetham" and "Kiran", Few questions will arise in your mind like,
1. how many subjects were failed by "Preetham"?
2. how many subjects were failed by each person?
3. what is the total scored by "Preetham"?
4. what is the total scored by each person?
So, we will solve all the 4 above questions and will learn about aggregate functions and we will also learn another important thing "GROUP BY". This is the important thing to learn.
1. how many subjects were failed by "Preetham"?:
Here you know how to select only the records of "Preetham"
SQL: where name = 'Preetham'
so now we need to count the subjects that scored less than 35 marks(which are considered as failed subjects).
lets write a SQL for the same:
SELECT
count(subject)
FROM marks_sheet
WHERE name = 'Preetham'
AND marks < 35;
Observe this, we are looking 6 subject's marks of 'Preetham'. This is the group. Upon this group, we are applying "count" function. So "count" function is an aggregate function.
Let's see next question:
2. how many subjects were failed by each person?
Observe the difference of above question and this. Now we need to consider each student as different group. So here, we need to use "GROUP BY". and later you know what magic you can do with the aggregate functions.
Before starting the query, Lets think about the output that we are looking for: (Do this frequently while writing any query)
Just observe the above output, We need each student and their count of subjects that are failed.
All the data of preetham should be wrapped to one record. (in other way, it should be added to "group by"). And then need to check all the subjects with marks less than 35 for "preetham" and count the subjects (this is nothing but aggregate functions).
Now lets start the query:
SELECT
name,
count(subject)
FROM marks_sheet
WHERE marks < 35
GROUP BY name;
Lets examine the difference between above two queries:
one statement is removed: "WHERE name = 'Preetham'"
one statement is added: "GROUP BY name"
this explains a lot. Instead of saying only look the group "Preetham" (in the first query) we are saying that "look into each name" with a statement "GROUP BY name".
Things to remember about "GROUP BY":
1. "GROUP BY column". The result will contain the distinct values present in this column.
In the above example, what are the distinct values present for the column, name: Preetham and Kiran.
So the result will contain 2 records.
2. All the other columns that are mentioned in the select statement other than the columns that are mentioned after "GROUP BY" (ie., in the above example subject) must be using aggregate function (like count(subject) ).
3. "GROUP BY colum1, column2" - This statement will wrap the whole list to the distinct values of the combination of column1 and column2 and the aggregate values follows.
lets try to solve 3rd and 4th questions:
3. what is the total scored by "Preetham"?
SELECT
sum(marks)
FROM marks_sheet
where name = 'Preetham';
Now try 4th:
4. what is the total scored by each person?
SELECT
name,
sum(marks)
FROM marks_sheet
GROUP BY name;
The next and final section is "having"
"Having" clause works as same as "where" clause.
Similarities:
Both "having" and "where" is used to filter the records
Differences:
"where" clause will be applied in the records of the actual tables.
"having" clause will be applied in the records that are obtained after grouping and aggregation.
Install any open source database like mysql or SQL server. Download My SQL from the following link: https://dev.mysql.com/downloads/installer/
All the queries that are being used here will be in MySQL. More than 90% of the basic query structure will be same in all the databases. We will also discuss some of the differences among databases at the end.
There are different types of queries:
1. DDL (Data Definition Language):
The queries which are used to define the data (datatypes of the fields, tables definition etc..)
In the above picture, Table name, columns in the table, datatype of each column. etc..... talks about data. The queries that used to create, alter, drop, truncate these structures are called DDL statements.
2. DML (Data Manipulation Language):
Manipulation means inserting the data , updating the existing data , selecting the existing data, ... the queries which are used for all those comes under this section.
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)
Most of the analysis, reports, interview questions are on "how to select the data".
So first, we will discuss about selecting the data from the tables in a database. Just keep the below SQL structure in mind.You will be able to clear most of the queries by understanding the below structure completely.
SELECT
Col1,
Col2,
Col3
FROM
{TABLE_NAME }
{JOIN}..... ON...
WHERE
{conditions}
GROUP BY ....
HAVING ....
ORDER BY ....
LIMIT ...
For better understanding of the above structure, we will go with one example.
Ex: Let's assume we have a box with 6 red cubes, 5 blue cubes, 7 yellow cubes and 3 white cubes
Ex-1: "I want all cubes" Just think the same sentence in general by adding some more details to it.
General: Pick all the cubes from the box.
(Lets see this in SQL)
SQL:
SELECT
*
FROM
BOX;
Lets see the transition:
Pick all the cubes from the box. --> General English
SELECT * FROM BOX; --> SQL
(BOX is considered as the "table")
We can write any simple SQL statement by thinking as above. Now, let's try another example.
Ex-2: I want all red cubes.
General: Pick all the cubes from the box which are red in color.
SQL:
SELECT
*
FROM
BOX
WHERE
colour = 'red';
Let's see the translation:
Pick all the cubes from the box which are red in color.
SELECT * FROM BOX WHERE color = 'red';
(color is a property of cube . However color is considered as one of the column of table, BOX)
-- In practical, the properties of an object are converted into columns of a table in database.
Ex-3: I want only 6 cubes.
General: Pick all the cubes from the box till you pick 6 cubes.
SQL:
SELECT
*
FROM
BOX
LIMIT 6;
Ex-4: I want 2 red cubes.
(Try this without scrolling down)
General: Pick all the cubes from the box which are red in color till you pick 2 cubes.
SQL:
SELECT
*
FROM
BOX
WHERE
color = 'red'LIMIT 2;
Nice!!! Till now we have covered SELECT, WHERE, LIMIT. Same way we will learn other parts of the SELECT query.
Ex-5: I want to know how many cubes are present in the box
General: Pick each cube of the box and count
SQL:
SELECT
COUNT(*)
FROM
BOX;
NOTE: Here, we have seen one new thing count(*). This is called "aggregate function" - This is so important to know.
aggregate function? Aggregation is calculating something from in a group of items.
Lets see an example to understand this more. We all know how the marks sheet looks like. Now, lets assume you are the class teacher and you are seeing the below sheet:
Student ID
|
Name
|
Subject
|
Marks
|
1
|
Preetham
|
Telugu
|
90
|
1
|
Preetham
|
Hindi
|
34
|
1
|
Preetham
|
English
|
60
|
1
|
Preetham
|
Maths
|
100
|
1
|
Preetham
|
Science
|
35
|
1
|
Preetham
|
Social
|
70
|
2
|
Kiran
|
Telugu
|
34
|
2
|
Kiran
|
Hindi
|
60
|
2
|
Kiran
|
English
|
70
|
2
|
Kiran
|
Maths
|
75
|
2
|
Kiran
|
Science
|
80
|
2
|
Kiran
|
Social
|
95
|
By looking at all the marks that scored by "Preetham" and "Kiran", Few questions will arise in your mind like,
1. how many subjects were failed by "Preetham"?
2. how many subjects were failed by each person?
3. what is the total scored by "Preetham"?
4. what is the total scored by each person?
So, we will solve all the 4 above questions and will learn about aggregate functions and we will also learn another important thing "GROUP BY". This is the important thing to learn.
1. how many subjects were failed by "Preetham"?:
Here you know how to select only the records of "Preetham"
SQL: where name = 'Preetham'
so now we need to count the subjects that scored less than 35 marks(which are considered as failed subjects).
lets write a SQL for the same:
SELECT
count(subject)
FROM marks_sheet
WHERE name = 'Preetham'
AND marks < 35;
Observe this, we are looking 6 subject's marks of 'Preetham'. This is the group. Upon this group, we are applying "count" function. So "count" function is an aggregate function.
Let's see next question:
2. how many subjects were failed by each person?
Observe the difference of above question and this. Now we need to consider each student as different group. So here, we need to use "GROUP BY". and later you know what magic you can do with the aggregate functions.
Before starting the query, Lets think about the output that we are looking for: (Do this frequently while writing any query)
Name |
# of subjects failed
|
Preetham
| |
Kiran
|
Just observe the above output, We need each student and their count of subjects that are failed.
All the data of preetham should be wrapped to one record. (in other way, it should be added to "group by"). And then need to check all the subjects with marks less than 35 for "preetham" and count the subjects (this is nothing but aggregate functions).
Now lets start the query:
SELECT
name,
count(subject)
FROM marks_sheet
WHERE marks < 35
GROUP BY name;
name
|
count
|
Preetham
|
1
|
Kiran
|
1
|
Lets examine the difference between above two queries:
one statement is removed: "WHERE name = 'Preetham'"
one statement is added: "GROUP BY name"
this explains a lot. Instead of saying only look the group "Preetham" (in the first query) we are saying that "look into each name" with a statement "GROUP BY name".
Things to remember about "GROUP BY":
1. "GROUP BY column". The result will contain the distinct values present in this column.
In the above example, what are the distinct values present for the column, name: Preetham and Kiran.
So the result will contain 2 records.
2. All the other columns that are mentioned in the select statement other than the columns that are mentioned after "GROUP BY" (ie., in the above example subject) must be using aggregate function (like count(subject) ).
3. "GROUP BY colum1, column2" - This statement will wrap the whole list to the distinct values of the combination of column1 and column2 and the aggregate values follows.
lets try to solve 3rd and 4th questions:
3. what is the total scored by "Preetham"?
SELECT
sum(marks)
FROM marks_sheet
where name = 'Preetham';
Now try 4th:
4. what is the total scored by each person?
SELECT
name,
sum(marks)
FROM marks_sheet
GROUP BY name;
The next and final section is "having"
"Having" clause works as same as "where" clause.
Similarities:
Both "having" and "where" is used to filter the records
Differences:
"where" clause will be applied in the records of the actual tables.
"having" clause will be applied in the records that are obtained after grouping and aggregation.
No comments:
Post a Comment