Data Analytics with R, Excel & Tableau

Basic SQL Commands

Introduction:

In this article, we will learn the working of regularly used SQL commands with the help of a sample data set. 

Before moving further, let us understand What is a Database and Importance of SQL.

What is  Database

A Database is a collection of tables as a unit. The purpose of a database is to store and retrieve related information from a table or from multiple tables. 

Oracle Database was the first database which was designed for enterprise grid computing, and cost effective way to manage information and applications.

What is Structured Query Language?

SQL is Query language in Oracle Database. A structured Query language for sorting, manipulating and retrieving data from oracle database. It is a very popular query language where the primary objective was created to give the possibility to common people get interested data from database. With the help of its english like simple syntax any new learner can learn this query language in lesser time.  The important feature of SQL is that most DB engines are compatible with all the SQL code. So once you learn SQL it should be similar to work across any relational databases.

To understand SQL operations in detail, we will be using the below Student and Department table.

Student Table

Department Table

Now first we will learn about the use of DML Commands:-

what is DML commands: 

Abbreviation of DML is Data Manipulation Language. Data manipulation language(DML) is used for mapping table data in a database, like retrieve data from database tables, inserting new record, update existing records, and remove unwanted rows from tables, these common operations are collectively known as Data manipulation language.

SELECT:–  

The main use of select statements is to retrieve zero or more records from one or multiple tables. 

NOTE 1:- If you want to display a particular column data specifically then after select statement you can pass the column_names to display only those column records. 

SYNTAX:-

SELECT  STUDENT_ID,FIRST_NAME,PHONE_NUMBER

FROM TABLE NAME;

NOTE :- If you want to retrieve all columns data from a table using this syntax

(select * from table name)

EXAMPLE:-

OUTPUT:-

INSERT:-

The insert statement is used to insert new data into a particular table. You can insert new data in multiple ways.

Syntax:-

INSERT INTO TABLE_NAME VALUES(‘Values1’,’Values2’,’Values3’,’Values3’);

NOTE:- if you have any confusion regarding colunm_name and it’s a sequence of colomn_name so apply this method.

.

INSERT INTO  students

(student_id,first_name,last_name,email,phone_number,admission_ date,course,course_fee,mentor_id,department_id)
VALUES('112','Mohan','Dollas','[email protected]','6878984532','07-AUG-19','Data Analytics','45789','101','90');

Output:

Now new record inserted into students table.

NOTE:– Before going to update and Delete Statements.first of all, understanding SQL clause because in that statement have used clause. so before going to these statements we aware  about clause.

Let’s learn about SQL Clause:-

The clause is put some specific conditions in your statement.after using clause you can filter the record according to requirements. Now we talk about WHERE, GROUP BY, HAVING and ORDER BY.

WHERE

Where clause used to put a specific condition(Filtration) in your statement. Where clause used to extract only those records which fulfills the condition defined within in the select statement. your condition.  

Syntax:

Select * from table_name
Where  Colomn_name=’Value’;

Output:

GROUP BY

The Group By clause is used in SQL statement and this clause is used in aggregate functions.Group by clause is used to retrieve data in the form of a group of row.

Syntax:-

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)

Example:-

Output:-

HAVING

The Having clause same as where clause but Having clause only applicable for Aggregate Function like(SUM, COUNT, AVG, MIN, MAX).

Syntax:-

SELECT column_name(s)
FROM table_name
HAVING condition

Example:-

Output:-

ORDER BY

Order By clause is part of SQL statements and this clause is used to set the row of Records in ascending and descending order like sorting. Use Order by keyword to sort your record according to your objective.

Syntax:-

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

Example:-

Output:-

Now we have understand about SQL Clause so now start remaining Update, Delete statements.

UPDATE 

Update Statement is used to update or modify an existing record in the table. For example, if you want to update a record with a new value you can use this syntax.  

Syntax:-

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:-

Output:-

NOTE:-After updating the record like this.

DELETE 

Delete statements are used to delete records in existing records in your table.

Syntax:-

DELETE FROM table_name; 

(These syntaxes to use delete all records in your table )

NOTE:- if you want to delete specific record so You can add one or one more than condition in your statements. If your condition will be match than the specific record is deleted.

Syntax:-

DELETE FROM table_name WHERE condition;

Example:- one condition or one more than condition.

Output:-

Comparison Operator

A comparison (or relational) operator is a mathematical symbol which is used to compare two values. Comparison operators are used to specify the condition and that compare one expression to another value or expression. The most popular three comparison operators are IN, NOT, BETWEEN .

IN

IN operator is used in where clause and the IN operator are specified with set of values. You can add multiple values in one condition like pass list of data in condition and you can also pass a select statement in IN operator.

Syntax:-

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
WHERE column_name IN (SELECT STATEMENT);

Example:-

Output:-

NOT

The NOT operator is just opposite of IN operator i.e, the NOT operator extracts only those records where the records are not matched with the passing list of value.

Syntax:-

SELECT column1,column2,..
FROM table_name
WHERE NOT condition;

Example:-

Output:-

BETWEEN

The Between operator is also used within where clause but the Between operator are used to specify the range of value like an upper limit and lower limit. You can also say the starting point and ending point (data in fixed range).

Syntax:- 

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:-

Output:-

Conclusion :

From the above example, we believe this blog helped you to understand DML(Data manipulation Language) commands , SQL Clause and Few most popular comparison operators. 

Suggested readings

R vs Python combat

Keep visiting our site www.acadgild.com for more updates on Data Analytics and other technologies. Click here to learn data science course in Bangalore.

Badal Kumar

Data Analyst at Aeon Learning

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Articles

Close
Close