SQL Revision Part -1

-

 Let’s start with SQL basics.

SQL is a standard language for storing, manipulating and retrieving data in databases.

We will revise all the command and syntax used in the SQL. I assume that reader is well acquainted with the SQL or even has some idea about it. I hope you have worked with “imdb” dataset and know how to load it in the MySQL command line client. We will extensively use imdb database in the entire bootcamp

             1. USE :

The "USE imdb " command will load the database imdb into SQL command line.



            2. SHOW TABLES:

            Displays all the tables present in the database.


            From here we can see that there are total of 7 tables present in the imdb database.


     3. DESCRIBE or DESC:

       It describes the structure of the Table.



            4. SELECT:

            Used to select data from a database.

        

            Syntax:

SELECT column1, column2, ...  

FROM table_name;

        

      If you have to select every column along with its data from movies table :     

            SELECT * FROM movies;

             


       

      If you have to select every column along with its data from directors table  :

            SELECT * FROM directors;


 

      To select only a particular column from a table use the following command:

 

           SELECT name, year FROM movies; 

            


           SELECT * FROM movies_genres;



 


   5. LIMIT:

        

 Used to specify the number of records to return. As the database contains a large number of records, to load all of it impacts performance, therefore LIMIT is used.

       

            Syntax: 

SELECT column_name(s) 

FROM table_name 

LIMIT number;

            Example:

 To display only top 10 rows of the database we use the following Command
6. OFFSET 
    Used to identify the starting point to return rows from result.
           Syntax :
SELECT column_name(s)
FROM table_name
OFFSET rows_to_skip ;



               Example:
   Without Offset


With Offset:


    7. ORDER BY

        Used to sort the result-set in ascending or descending  order. It sorts the                           records in ascending order by default. To sort   the records in descending                      order, use the DESC keyword.

    Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

               Example:

               Ascending

                    SELECT name,rankscore,year FROM

movies ORDER BY year DESC LIMIT 10;

DESCENDEING


    

    8. DISTINCT:

Used to return only distinct values

Syntax :

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example:


Without DISTINCT


                     With DISTINCT 


            9. WHERE

Used to filter records

 

Syntax :  

 

SELECT column1, column2, ...

FROM table_name
WHERE condition;

Example:

SELECT name,year,rankscore FROM movies WHERE rankscore>9.1 ;


    SELECT name,year,rankscore FROM movies WHERE rankscore<2 ;



Comments

Post a Comment

Popular posts from this blog

Covariance and Correlation

-
Image
While dealing with any ML project one spends most of the time in data preprocessing .Data pre-processing is nothing but playing with the features present in the dataset ,data cleaning,data transformation ,removing or imputing missing feature values etc. Feature engineering corresponds to developing  new features based on current information . It may happen that few features are related to each other be it directly or inversely . Consider three cases:  Let's say radius and circumference are features present in the dataset ,so we know that they are directly dependent .Here if radius increases ,perimeter also increases.       2. Let's say speed and time are features present in the dataset ,so we know that they are inversely               proportional  .Here if speed increases ,time required to cover a distance decreases.         3. Let's say height and hair color are features present in the datas...
Read more

Split it up - Part 1

-
Image
M achine  L earning ???? How can a machine even learn??  For a long period of time I always wondered what this fancy term meant and how would it be actually possible ..!! Let's figure it out today. To get an essence of what happens when we say Machine is learning ,why not understand in the first place how we learn. Well..!!! let us take a glance at those days when we learnt mathematics as a kid. I still remember how my maths teacher used to teach us basic addition and subtraction in class .Initially she helped us to count using fingers and then add or subtract those numbers. With a lot of practice and sometimes getting beaten up by her we finally learnt addition /subtraction. Mathematically speaking after lot of practice /training , our brain learnt a function of addition where if we get numbers to add ,it processed in a way to give the sum. Similarly Machine Learning is all about finding that magic function "f" wherein ,we give some input and desire some output . (EXC fo...
Read more

Why activation function is needed in Neural Networks???

-
Image
Today allow me to share with you, the relationship between a spider and an activation function   Activation function is used in Neural network to introduce the non-linearity. Without the proper activation function, neural network will simply be large Linear Model.   Consider the case where you have a data which is linearly separable  Constructing a model to separate yellow and orange data points is very simple. A simple logistic regression will do this job very perfectly  Now consider the case where your data set looks like something shown below What do you think now?? will a simple linear model be able to carve out the hyperplane in such a way to classify both yellow and orange points. I don’t think so. And it is the fact that a simple linear model may not be able to find complex patterns in a data.  So now we go to big brother of linear model -Neural network. Neural Network has this super ability to both carve out linear...
Read more