• Chennai, Bangalore & Online: 93450 45466Coimbatore: 95978 88270Madurai: 97900 94102

  • SQL For Data Science: For Beginners



    Data has become the fuel for the organizations to strategize and formulate the entire models as per trending patterns and requirements. Databases are where these data reside, but working with them requires a programming language.

    SQL or Structured Query Language is a primary language for working with databases. It is used widely for data analysis or data science, it is a must to know the language for driving, reports, manipulation, or analysis of a dataset.

    With SQL you can update, add or delete records from the database.The top Relational Database Management Systems(RDBMS) such as PostgreSQL, MySql, SQLite, MariaDB, Oracle are based on the SQL.

    This blog is your way to go for analyzing simple datasets using SQL.

    Here is what we will discuss step by step in this blog,
    Basics Of SQL
    Create Table
    Insert into Table
    Updating rows in Table
    Deleting rows from Table
    Counting records.
    Deleting Table
    Advanced SQL
    Functions for aggregation
    Finding extreme values
    Slicing the data
    Limiting the data
    Filtering the data
    Grouping the data
    Let us now learn the Sql starting with the Basics Of SQL…

    Basic Of SQL

    Creating Database A SQL Database is a godown for storing structured data. Here is how you would create a database in MySQL.

    — syntax for creating a database

    CREATE DATABASE <databasename>

    USE <databasename>

    Here the CREATE DATABASE command creates the database with the database name and the USE command will activate the database with the database name followed.

    — Example for creating a database

    CREATE DATABASE FITA

    USE FITA

    After creating the database let us now see how to create a table in the database. Creating Tables The data in databases are stored in Tables with attributes and the type of data. A database can have more than one table. creating tables is just as creating a database

    — syntax for creating a table

    CREATE TABLE <tablename>

    (

    variable1 datatype 1,

    variable 2 datatype 2,

    variable n datatype n,

    ..

    );

    Here the CREATE TABLE will create a table with a table name. The variables here are the name of the attributes, which will be the columns of the table.SQL data can have numeric, character or string Date and Time, Binary, and Boolean data type. A semicolon indicates the termination.

    — Example for creating a table

    CREATE TABLE Students

    (

    roll number INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

    student_name TEXT,

    marks INTEGER,

    grade VARCHAR,

    passed BOOLEAN

    );

    Here the roll number is of type integer and the primary key is for identifying records with this unique value. There could be only one primary key for a record.

    AUTOINCREMENT is used to increment the value automatically, that is you won’t need to pass the value for the roll number and it will be automatically set.

    After creating the tables in the database let us now see how to insert rows in the table.

    Insert into table

    To insert a record into the table you will need to specify all the values with the respective data type in the table.

    — Syntax for inserting a record

    INSERT INTO TABLE <tablename> VALUES

    (

    value 1,

    value 2,

    ..

    value n

    );

    Add 5 students marks, name grade, and whether passed to the Student’s table.
    INSERT INTO Students VALUES (NULL,“Atufa”,70,”B”,true); INSERT INTO Students VALUES (NULL,“Puja”,85,”A”,true); INSERT INTO Students VALUES (NULL,“Jane”,50,”B”,true); INSERT INTO Students VALUES (NULL,“Rosy”,45,”C”,true); INSERT INTO Students VALUES (NULL,“Harry”,20,”F”,false);
    Notice that we didn’t pass the roll number because it has been set to AUTO_INCREMENT. Note that these commands need not to be written in capital letters on different lines necessarily, this is just to distinguish the SQL commands from the values. After inserting rows in the database let us now see how to retrieve data from the table in the database.

    Retrieving data

    Retrieve all the records from the table.
    — Example for selecting all the records   SELECT * FROM Students
    Here the SELECT command is for retrieving data, and * for all the records. Output for the above query
    roll_number student_name marks grade passed
    1 Atufa 70 B 1
    2 Puja 85 A 1
    3 Jane 50 B 1
    4 Rosy 45 C 1
    5 Harry 20 F 0
    Selecting specific attributes
    To select records from the specific columns of the table
    Retrieve all the names and whether passed condition of the students from the table.
    — Example for selecting records for specific attributes   SELECT student_name, passed FROM STUDENTS;
    Output for the above query
    student_name passed
    Puja 1
    Jane 1
    Rosy 1
    Harry 0
    Here the 0 represents false value or failed and 1 represents true value or passed.
    Selecting specific records
    To select specific rows from the table with a condition using WHERE command.
    Retrieve all the records of the students who got B grade.
    SELECT * FROM Students WHERE grade == ‘B’;
    Output for the above query
    roll_number student_name marks grade passed
    1 Atufa 70 B 1
    3 Jane 50 B 1
    Or to select specific records from specific columns with a condition using the WHERE command. Here is an exercise
    Retrieve all the names of the students who achieved A grade.
    SELECT student_name FROM Students WHERE grade == ‘A’;
    Output for the above query
    student_name
    Puja
    Let’s do one more,
    Select all the names of the students who have passed or achieved more than 35 marks.
    SELECT student_name FROM Students WHERE marks>=35;
    Output for the above query
    student_name
    Atufa
    Puja
    Jane
    Rosy
    Now let’s do one more powerful query
    Retrieve all the records of the passed students ordered by their grades.
    SELECT * FROM Students WHERE marks>=35 ORDER BY grade;
    Here the ORDER BY will arrange the records based on the attribute passed, or in this case by grades. Output for the above query
    roll_number student_name marks grade passed
    2 Puja 85 A 1
    1 Atufa 70 B 1
    3 Jane 50 B 1
    4 Rosy 45 C 1
    You can also pass an optional command ASC or DSC for ascending or descending order to the query. By default, it is set to ASC. After retrieving the data from the database let us now see how to update the records from the table in the database.

    Updating records from Table

    To update records from the table you will simply need to use the UPDATE and SET command. Here are some examples
    Update the grades for marks greater than or equal to 70 to ‘B’.
    UPDATE Students SET grade = ‘A’ WHERE marks>=70; SELECT marks, grade FROM Students;
    Output for the above query
    marks grade
    70 A
    85 A
    50 B
    45 C
    20 F

    Check out this complete Online Data Science Course by FITA, which includes Supervised, Unsupervised machine learning algorithms, Data Analysis Manipulation and visualization, reinforcement testing, hypothesis testing, and much more to make an industry required data scientist at an affordable price, which includes certification, support with career guidance assistance  with an active placement cell,to make you an industry required certified data scientist.

    After learning to update the records from the database let us now see how to count the records from the table in the database. Counting records The count() function can be used to count. Here are some examples
    Count the number of records in the table
    SELECT COUNT(*) FROM Students; — outputs 5
    Count the number of students passed
    SELECT COUNT(*) FROM Students WHERE marks>=35; — outputs 4
    After learning to count the records from the database let us now see how to delete the records from the table in the database. Deleting records from Table. DELETE can be used to delete any record or row from the table. Here are some exercises.
    delete all the records of the failed students.
    DELETE FROM Students WHERE grade == ‘F’; SELECT * FROM Students;
    Output for the above query
    roll_number student_name marks grade passed
    1 Atufa 70 A 1
    2 Puja 85 A 1
    3 Jane 50 B 1
    4 Rosy 45 C 1
    We are starting a new academic year with new students so delete all the records from the table.
    DELETE FROM Students;
    This will delete all the records of the table without its attributes. The last task in the basics of SQL is to delete the table.

    Deleting Table

    To delete a table from the database using the DROP command with the table name.
    We are closing the school and starting a new business due to lockdown, and don’t need the students’ table anymore.
    Here is the command to delete the table for students.
    DROP TABLE Students;

    ADVANCED SQL QUERIES

    Let us now check some of the advanced sql queries, for summing, filtering, and limiting the data. Aggregation Functions in SQL Here we will use the Students tables with the previous 5 records.
    SUM() function.
    Calculate the SUM() of the marks of students and hence calculate the average.
    SELECT SUM(marks) FROM Students; — outputs 270
    Now the average can be calculated by dividing this value(270) by 5 which results in 54. You can also use the WHERE here to add specific records.
    AVG() function
    We had used the SUM() function before and divided it by the number of rows to calculate the average. This can be automated with the AVG() function.
    Calculate the average marks for the passed students
    ECT AVG(marks) FROM Students WHERE marks>=35; — outputs 62.5SEL
    You can also calculate the standard deviation of a column using the STDDEV() function, to calculate the difference between actual and the mean value.
    MIN() and MAX() function
    These functions return the highest and the lowest value of the specified column.
    Find the maximum marks of the student with the grade ‘B’.
    SELECT student_name,MAX(marks) FROM Students WHERE grade ==’B’;
    Output for the above query
    student_name max(marks)
    Jane 50

    Check out this SQL Training in Chennai. FITA provides a complete SQL course that covers all the beginning and the advanced queries of SQL.

    Additionally it covers all the elements of ANSI SQL, Data Definition Language (DDL) and Data Manipulation Language (DML) using both SQL Server and Oracle, with an active placement cell,to make a certified Database Professional.

    After learning the aggregate functions of SQL, for summing up the data, finding the minimum or maximum value from the data or the average let us now learn to filter the records from the database.

    Filtering the data

    Filters in SQL include WHERE, ORDER BY, BETWEEN, LIKE, GROUP BY, HAVING. We have already covered ORDER BY and WHERE. Let’s take a look at others.
    GROUP BY
    This statement usually works with aggregate functions (MAX, MIN, SUM, COUNT, AVG) to create sets of the same values. Here is an example
    SELECT COUNT(marks), grade FROM Students GROUP BY grade;
    Output for the above query
    count(marks) grade
    2 A
    1 B
    1 C
    LIKE
    LIKE is like a regex that matches the passed statement among the specified records. Here is an example
    SELECT * FROM Students WHERE student_name LIKE ‘%Harry’;
    It would be more efficient to use this statement if the table had full names of the students stored.
    BETWEEN
    BETWEEN is used to retrieve records from a specific range. Here is an example of retrieving student names and their grades between the marks 50 to 75.
    SELECT student_name, grade FROM Students WHERE marks BETWEEN 50 AND 75;
    Output for the above query
    student_name grade
    Atufa A
    Jane B
    HAVING
    HAVING is just like WHERE but used with aggregate functions, since using WHERE with aggregate functions results in an error.
    Retrieve the number of students passed (marks greater than 35) with their grades.
    If you try to do it like this,
    SELECT grade, COUNT(*) FROM Students GROUP BY grade WHERE marks>35;
    It will result in a syntax error near ‘WHERE’.So instead of using WHERE here use HAVING.
    SELECT grade, COUNT(*) FROM Students GROUP BY grade HAVING marks>35;
    Output for the above query
    grade count(*)
    A 2
    B 1
    C 1
    Other filters include AND, OR, NOT, IN, ANY, ALL, EXISTS, UNION, and JOINS (OUTER JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and SELF JOIN). After learning the queries for filtering the records from the database, for selecting records based on conditions, let us now learn to limiting the records from the database.

    Limiting the data

    Let’s say you have a database with millions of records, but you just want a glance at the first three, you can do this with the LIMIT clause.
    SELECT * FROM Students LIMIT 3;
    Which will give you the first three records in the Students table.
    Now you need those 3 records from the 3rd roll number, you can do this with the OFFSET clause.
    SELECT * FROM Students LIMIT 3 OFFSET 2;
    Output for the above query
    roll_number student_name marks grade passed
    3 Jane 50 B 1
    4 Rosy 45 C 1

    This was a quick start tutorial for SQL and its implementations. To get the in depth knowledge of Python along with its various applications and real-time projects, you can enroll in Python Course in Chennai or Python Training in Bangalore or enroll for a Data science course at Chennai or Data science course in Bangalore or in virtual classes for these courses, which includes Supervised, Unsupervised machine learning algorithms, Data Analysis Manipulation and visualization, reinforcement testing, hypothesis testing and much more with an active placement cell, to make an industry required data scientist at an affordable price, which includes certification, support with career guidance assistance to make you an industry required certified python developer and a certified data scientist.

    FITA’s courses training is delivered by professional experts who have worked in the software development and testing industry for a minimum of 10+ years, and have experience of working with different software frameworks and software testing designs.






    Quick Enquiry

    Contact Us

    Chennai

      93450 45466
     support@fita.in

    Coimbatore

     95978 88270
     support.coimbatore@fita.in

    Bangalore

     93450 45466

    For Hiring

     93840 47472
     hr@fita.in

    Corporate Training

     90036 23340


    FITA Academy Branches

    Chennai
    Others
    FITA Academy - Velachery
    37F Velachery Main Road,
    Velachery, Chennai - 600042
    Tamil Nadu
    Next to Adyar Ananda Bhavan

        :   93450 45466 / 044-42084566

       :   support@fita.in

    FITA Academy - Anna Nagar
    No 14, Block No, 338, 2nd Ave,
    Ranganathan Garden, Anna Nagar,
    Chennai 600 040, Tamil Nadu
    Next to Santhosh Super Market

        :   93450 45466

       :   support@fita.in

    FITA Academy - T Nagar
    05, 5th Floor, Challa Mall,
    T Nagar,
    Chennai 600 017, Tamil Nadu
    Opposite to Pondy Bazaar Globus

        :   93450 45466

       :   support@fita.in

    FITA Academy - Tambaram
    Nehru Nagar, Kadaperi,
    GST Road, West Tambaram,
    Chennai 600 045, Tamil Nadu
    Opposite to Saravana Jewellers Near MEPZ

        :   93450 45466

       :   support@fita.in

    FITA Academy - Thoraipakkam
    5/350, Old Mahabalipuram Road,
    Okkiyam Thoraipakkam,
    Chennai 600 097, Tamil Nadu
    Next to Cognizant Thoraipakkam Office and Opposite to Nilgris Supermarket

        :   93450 45466

       :   support@fita.in

    FITA Academy - Coimbatore
    First Floor, Promenade Tower,
    171/2A, Sathy Road, Saravanampatty,
    Coimbatore - 641035
    Tamil Nadu

        :   93450 45466

       :   support@fita.in

    FITA Academy - Madurai
    No.2A, Sivanandha salai,
    Arapalayam Cross Road,
    Ponnagaram Colony,
    Madurai - 625016, Tamil Nadu

        :   97900 94102

       :   support.madurai@fita.in

  • Trending Courses

    JAVA Training In Chennai Dot Net Training In Chennai Software Testing Training In Chennai Cloud Computing Training In Chennai AngularJS Training in Chennai Big Data Hadoop Training In Chennai Android Training In Chennai iOS Training In Chennai Web Designing Course In Chennai PHP Training In Chennai Digital Marketing Training In Chennai SEO Training In Chennai

    Oracle Training In Chennai Selenium Training In Chennai Data Science Course In Chennai RPA Training In Chennai DevOps Training In Chennai C / C++ Training In Chennai UNIX Training In Chennai Placement Training In Chennai German Classes In Chennai Python Training in Chennai Artificial Intelligence Course in Chennai AWS Training in Chennai Core Java Training in Chennai Javascript Training in ChennaiHibernate Training in ChennaiHTML5 Training in ChennaiPhotoshop Classes in ChennaiMobile Testing Training in ChennaiQTP Training in ChennaiLoadRunner Training in ChennaiDrupal Training in ChennaiManual Testing Training in ChennaiSpring Training in ChennaiStruts Training in ChennaiWordPress Training in ChennaiSAS Training in ChennaiClinical SAS Training in ChennaiBlue Prism Training in ChennaiMachine Learning course in ChennaiMicrosoft Azure Training in ChennaiUiPath Training in ChennaiMicrosoft Dynamics CRM Training in ChennaiUI UX Design course in ChennaiSalesforce Training in ChennaiVMware Training in ChennaiR Training in ChennaiAutomation Anywhere Training in ChennaiTally course in ChennaiReactJS Training in ChennaiCCNA course in ChennaiEthical Hacking course in ChennaiGST Training in ChennaiIELTS Coaching in ChennaiSpoken English Classes in ChennaiSpanish Classes in ChennaiJapanese Classes in ChennaiTOEFL Coaching in ChennaiFrench Classes in ChennaiInformatica Training in ChennaiInformatica MDM Training in ChennaiBig Data Analytics courses in ChennaiHadoop Admin Training in ChennaiBlockchain Training in ChennaiIonic Training in ChennaiIoT Training in ChennaiXamarin Training In ChennaiNode JS Training In ChennaiContent Writing Course in ChennaiAdvanced Excel Training In ChennaiCorporate Training in ChennaiEmbedded Training In ChennaiLinux Training In ChennaiOracle DBA Training In ChennaiPEGA Training In ChennaiPrimavera Training In ChennaiTableau Training In ChennaiSpark Training In ChennaiGraphic Design Courses in ChennaiAppium Training In ChennaiSoft Skills Training In ChennaiJMeter Training In ChennaiPower BI Training In ChennaiSocial Media Marketing Courses In ChennaiTalend Training in ChennaiHR Courses in ChennaiGoogle Cloud Training in ChennaiSQL Training In ChennaiCCNP Training in Chennai

  • Are You Located in Any of these Areas

    Adyar, Adambakkam, Anna Salai, Ambattur, Ashok Nagar, Aminjikarai, Anna Nagar, Besant Nagar, Chromepet, Choolaimedu, Guindy, Egmore, K.K. Nagar, Kodambakkam, Koyambedu, Ekkattuthangal, Kilpauk, Meenambakkam, Medavakkam, Nandanam, Nungambakkam, Madipakkam, Teynampet, Nanganallur, Navalur, Mylapore, Pallavaram, Purasaiwakkam, OMR, Porur, Pallikaranai, Poonamallee, Perambur, Saidapet, Siruseri, St.Thomas Mount, Perungudi, T.Nagar, Sholinganallur, Triplicane, Thoraipakkam, Tambaram, Vadapalani, Valasaravakkam, Villivakkam, Thiruvanmiyur, West Mambalam, Velachery and Virugambakkam.

    FITA Velachery or T Nagar or Thoraipakkam OMR or Anna Nagar or Tambaram branch is just few kilometre away from your location. If you need the best training in Chennai, driving a couple of extra kilometres is worth it!