Self Learning Task 5

Here, I'm going to talk about SQL basics that is being taught on KhanAcademy.com
SQL stands for Structured Query Language and is used to manage data in a database. There are 2 types of basic databases, Relational and Non-Relational. SQL is used to manage and organize relational databases.
Here, the instructor starts off the course by explaining the carious types of databases and why we use sql. 
SQL is a very simple and easy to understand language. It is very similar to the English language. Syntax used is also explicit and easy to remember.

To create tables:- CREATE TABLE table_name (id  INTEGER, name  VARCHAR);

When this is executed, a table is created having 2 columns, id and name. id  can store only integer numbers and name string characters of variable length. You may also specify the maximum length that can be stored by specifying it in parenthesis.

CREATE TABLE table_name (id  INTEGER, name  VARCHAR(20));

So here, name  can store strings only less than  or equal to 20 characters in length.

Inserting Values into tables:- INSERT INTO table_name VALUES (2,"Peter");

This would store '2' in the id column and "Peter" in the name column. You need to specify string characters within double quotes " ".

The SELECT statement is used to access or read the contents of a table.

SELECT * FROM table_name ;

The '*' operator is used to select all attributes of a table. So the above query would show us both the name and the id columns.

SELECT name FROM table_name ; 

This query would just show the name column of the table.

We can also use some aggregate functions. For example, consider a table consisting of a list of shopping items and their prices. We can obtain the sum or average of the price column(any numeric column) by using the sum or average function

SELECT SUM(price) FROM shopping_list;

SELECT COUNT(price) FROM shopping_list;   --- shows the number of items in the list

SELECT MAX(price) FROM shopping_list;        --- shows the costliest/highest price from the list

SELECT MIN(price) FROM shopping_list;         --- shows the cheapest/lowest price from the list

SELECT AVG(price) FROM shopping_list;         --- shows the average price of an item

WHERE clause:- This is used to check certain condition/s within a query. For example,

SELECT item FROM shopping_list where price> 20;

This would display the items which have a price greater than 20.

> greater then, < less than and <> is used as not equal to

SELECT item  FROM shopping_list   where item_category <> "beverage";

AND and OR operators;

These operators can be used in queries. When we use AND it checks all the criteria and displays the desired only if all the conditions evaluate to true. OR displays even if only of the condition evaluates to true.

SELECT name FROM  MEMEBERS WHERE age>25 AND city =="Bangalore" AND gender=="female";

This would display the names of all the members who satisfy the above three criteria, i.e, a female residing in Bangalore with age greater than 25 years.

SELECT name FROM  MEMEBERS WHERE age>25 OR city =="Bangalore" OR gender=="female";

This would display the list of names including anyone who is older than 25 years of age, all members residing in Bangalore and all the Female members. 

IN:- This works just like  where clause but it has a list of values instead of just one. For example,

SELECT item  FROM shopping_list   where item_category IN ("beverage","bread","electronic","dairy");

This would display a list of all the items that belong to any of the categories in the list.

Similarly, NOT IN clause could be used.

SELECT item  FROM shopping_list   where item_category NOT IN ("beverage","bread","electronic","dairy");

This would display a list of all items which do not belong to any of the categories in the list.

ORDERBY clause:- This is used in ordering of the displayed values. At any given time, the displayed elements can be ordered on the basis of only one column.

SELECT item FROM shopping_list 

ORDER BY price;

This would display the list of shopping items in increasing order of their prices, cheapest to highest. This order is the default.

We can use the DESC keyword to display in the decreasing order.

SELECT item FROM shopping_list 

ORDER BY price DESC;

GROUP BY:- This is used in collaboration with Select statement to arrange identical data into groups.

SELECT item, SUM(item) from shopping_list GROUP BY item_category;

This would group the items into groups based on their item category along with the sum of items in that particular category.

JOIN:- This is used when you want to join the contents of two different tables into one. 

SELECT o.order_id,s.customer_name, o.order_date

FROM Orders as o

INNER JOIN Customers as c ON o.customer_id=c.customer_id;

This would display a table having the matching customer id's in both the tables, meaning, it will display only customers who have placed orders.

There are different kinds of join, left join, right join, inner join, outer join. Depending on the problem statement, a particular one must be chosen.

CASE :- This is used when we want to display a particular attribute after categorizing based on some value. It works just like an if-else loop of other programming languages. 

Ex:

SELECT item ,

            CASE 

                    WHEN item_price < 51 THEN "Cheap"

                    WHEN item_price >50 AND item_price <75 THEN "Affordable"

                    ELSE item_price <74 THEN "Expensive"

            END as tag

FROM shopping_list;

The columns here would display the item names and a tag based on it's price ,i.e. cheap, affordable or expensive.