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
EXAMPLE:-
INSERT:-
The insert statement is used to insert new data into a particular table. You can insert new data in multiple ways.
Syntax:-
NOTE:- if you have any confusion regarding colunm_name and it’s a sequence of colomn_name so apply this method.
.
INSERT INTOÂ students
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:
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:-
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:-
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:-
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:-
Example:-
Output:-
NOTE:-After updating the record like this.
DELETEÂ
Delete statements are used to delete records in existing records in your table.
Syntax:-
(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:-
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:-
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:-
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:-
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.
