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, Let us now learn the Sql starting with the Basics Of SQL…
Basic Of SQLCreating 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>
-- Example for creating a database
CREATE DATABASE FITA
-- 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,
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 tableTo 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
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);
-- Example for selecting all the records SELECT * FROM Students
-- Example for selecting records for specific attributes SELECT student_name, passed FROM STUDENTS;
SELECT * FROM Students WHERE grade == ‘B’;
SELECT student_name FROM Students WHERE grade == ‘A’;
SELECT student_name FROM Students WHERE marks>=35;
SELECT * FROM Students WHERE marks>=35 ORDER BY grade;
Output for the above query
Updating records from TableTo update records from the table you will simply need to use the UPDATE and SET command. Here are some examples
UPDATE Students SET grade = 'A' WHERE marks>=70; SELECT marks, grade FROM Students;
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
SELECT COUNT(*) FROM Students; -- outputs 5
SELECT COUNT(*) FROM Students WHERE marks>=35; -- outputs 4
DELETE FROM Students WHERE grade == 'F'; SELECT * FROM Students;
DELETE FROM Students;
Deleting TableTo delete a table from the database using the DROP command with the table name. Here is the command to delete the table for students.
DROP TABLE Students;
ADVANCED SQL QUERIESLet 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.
SELECT SUM(marks) FROM Students; -- outputs 270
ECT AVG(marks) FROM Students WHERE marks>=35; -- outputs 62.5SEL
SELECT student_name,MAX(marks) FROM Students WHERE grade =='B';
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 dataFilters 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. 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;
SELECT * FROM Students WHERE student_name LIKE '%Harry';
SELECT student_name, grade FROM Students WHERE marks BETWEEN 50 AND 75;
SELECT grade, COUNT(*) FROM Students GROUP BY grade WHERE marks>35;
SELECT grade, COUNT(*) FROM Students GROUP BY grade HAVING marks>35;
Limiting the data
SELECT * FROM Students LIMIT 3;
SELECT * FROM Students LIMIT 3 OFFSET 2;
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.