Top 10 Most Basic SQL Commands To Know

Let's check out each of these with examples.

1. SELECT Command

SELECT Command is widely used in SQL and forms the base of RDBMS. The primary function of the command is to retrieve data from a database. Two ways are using which you can do so. You can retrieve data values from the whole table or a particular set. As per your prerequisites, you can choose the one you need. Further, the fetched data's output is stored in a table known as the result-set.

While giving a query, you have to use the SELECT keyword and the fields whose data you wish to extract. Besides, if required, you can also state the field names.

The syntax of Select Command

SELECT column1name, column2name FROM table_name

Here,

column1name: you have to enter the name of column/field 1
column2name: you have to enter the name of column/field 2
table_name: stands for the name of the table whose database you have created

SELECT and FROM are keywords

Alternatively, you do not have to specify column names when you want to fetch data elements from the entire table.

Syntax to use the SELECT command to select all the columns:

SELECT * FROM table_name

Here, this query will display the results of all the table elements.

Example:

Select StudentName from School

This command will display a list of all students in the School database from the Student Name column. If you wish to see all the records, you can use the * instead of a particular column name.

2. SELECT DISTINCT Command

The Select distinct command is used in a query where you want to display unique values from a specified column of the table only. Here, the SELECT DISTINCT keyword specifies its role.

The syntax of DISTINCT Command

SELECT DISTINCT column_name FROM table_name

Example:

SELECT DISTINCT Subjects FROM Student

The above SQL query looks for the Subjects column from the Student table and displays a list of unique subjects in the output. No repetition of subjects will be seen.

The cool part is that it removes the duplication of column values in the result set. Moreover, when you have to utilize the columns from the table, the syntax would be as given below:

SELECT DISTINCT

column1_name,

column2_name,

column3_name

columnn_name

FROM

table_name;

In the above command, you must specify the name of all the columns in a serial order. It will eliminate duplication from a sum of columns in the result set. To sum up, it will display only different sets of values from all the columns in the result set.

Example

SELECT DISTINCT address_area FROM student

For example, some students may live in the same district, and if you run a general query, it will result in duplicate values. If you want to find unique district names, then you can use this command.

3. WHERE Command

When you want to derive data from the table matching specific criteria, the WHERE Command is used. Suppose you have a student table and you wish to extract data of all the students whose age is 20, then you have to use the command with a criterion of "age>18".

The syntax of WHERE Command:

SELECT column1_name, column2name FROM table_name WHERE column_name

operator value;

Here column1_name and column2_name specify the name of fields of the table. table_name represents the name of the table whose database is created. column_name is used to state the column against whom you are checking. The operator is the one that you use for checking conditions. Besides, value is the data element that you wish to retrieve.

Example:

Select StudentName from School Where Subject="Physics"

This will list all students who have taken Physics as one of their subjects. If a student doesn't have Physics as one of its subjects, it will be filtered out.

4. ORDER BY command

The ORDER BY command is another useful one to create an SQL query. It is used when you intend to sort the data fetched in ascending or descending order in either one or more than one column.

Suppose you have created a table of progress reports of students, and then you can arrange it in descending order to check the maximum value.

The syntax for Order By Command

SELECT * FROM table_name

ORDER BY column_name ASC|DESC

In the above query syntax, table_name is the name with which you javelin created the table.

Column_name is the name of the field whose results you wish to sort. ASC or DESC keywords specify whether the sorted range should be in ascending or descending order.

According to your preferences, you might be in a situation where you want to sort multiple columns in a specified order. Further, you can separate the column names with a comma (,) operator.

Syntax of ORDER BY command for multiple columns

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

Here you have to specify for each column that you want its data in an ascending or descending order. The benefit is that you can select your preference for each column and avoid giving multiple queries for different columns of sorting pattern.

Example:

Select StudentName from School Order by Roll No

This syntax will display students based on their roll number, which defaults to ascending order. You can use this command if you need to sort by roll number instead of a name.

5. INSERT INTO Command

You might often fall into a situation wherein you want to insert a row in the table. To do so, the INSERT INTO command is used.

Syntax: Insert into Table

INSERT INTO table_name VALUES (value1, value2, value3,…);

table_name: name of the table in which you want to insert the column

value1, value2 and value3 are the names of new columns you want to insert. It can vary from 1 to n.

Syntax to insert into multiple columns:

The INSERT INTO command is also used if you add values in multiple columns.

Syntax:

INSERT INTO table_name (column1, column2,column3) VALUES (value1, value2, value3);

Example:

INSERT INTO Student (RNO, NAME, Class) VALUES ('10625', 'SHRUTI', '20');

Here Student, is the name of the table upon which you are working. RNO, NAME and CLASS are the columns in which you want to add values. 10625, SHRUTI, and 20 are the value that you are adding.

6. UPDATE Command

You must use the UPDATE command when you want to upgrade data in a SQL table column name. The brilliant feature is that you can give a query using the keyword to update values in a single column and even multiple as per your prerequisites.

The syntax for Update Command

UPDATE table_name

SET column1 = value1, column2 = value2,...

Where columnname="columnvalue";

Here table_name is the name of the table upon which you are working. Column 1 is the initial name of column 1, and value1 is the new value you want to give it. Similarly, value2 is the new value of the second column you have set.

Update and Set are the query keywords.

Example:

UPDATE School

StudentName = 'Sachin', Subject= 'Chemistry'

WHERE StudentID = 1;

Here StudentID 1 has been updated with new values of Name and Subject. It is useful when someone changes his name or subject during the course.

7. DELETE Command

When you want to delete existing values from the table, then the DELETE command is used. According to your needs, you can delete single values or multiple as per the condition you use.

The syntax for Delete Command

DELETE FROM table_name WHERE given_condition;

Here table_name is the table's name that you have created. given_condition is the condition whose value you want to delete.

It is noteworthy that it is essential to specify conditions using the WHERE keyword. If the condition is present in multiple records, they will be deleted wherever the condition is specified. Besides, if you do not use the DELETE keyword, all the data from the table will be cleared.

Example:

Delete from School Where StudentID="3"

Here the record of Student with ID 3 has been deleted from the School Table. Since Student ID is a unique identifier, it ensures to delete only that record.

8. SUM Command

The SUM command is used when you want to calculate the same of a particular column or expression. Suppose you want to calculate the sum of only distinct values, then you have to use the DISTINCT keyword. It is a Numeric function as the sum can be of number type. If any NULL value is given in the table, it is ignored in the sum function.

The syntax for SUM Command

SELECT SUM(aggregate_expression)

FROM tables

[WHERE conditions];

Here, in the query, if you want to calculate the sum of a particular column, then specify its name. Else, use the expression whose sum you want to calculate.

Example:

SELECT SUM(Marks)

FROM School

[WHERE StudentId =3];

It will add all the marks obtained by a particular student from all subjects and print the results.

9. INNER JOIN Command

The INNER JOIN Command is used when multiple tables are present. It will Club rows from two tables and display the result set till the time a given condition is fulfilled.

The Syntax of INNER JOIN command:

SELECT table1.column1, table1.column2, table2.column1, table2.column2

FROM table1 INNER JOIN table 2

ON table1. columnname=table2.columnname

In the above query, the number of columns can be from 1 to n. Further, the inner join keyword combines particular columns of both tables and displays them in a result set.

Example:

SELECT School.studentName, Attendence.Attendneceocunt

FROM School

INNER JOIN Students ON School.studentID = Attendence.AttendenceID;

Since Student Attendance is maintained in another database and connected to Student ID., here the query performs an inner join to fetch Student Name and Attendance based on Student ID, which is the same on both the tables.

10. LEFT JOIN Command

When you have multiple tables (here2 ), then the first table is the left table, and the second one is the right table. You can thus create a query from multiple tables once.

The command retrieves rows from the left table and the recurring rows in table 2 or the right table. However, if no recurring rows are present in the right one, then the result set displays null.

The syntax for Left Join Command

SELECT column_name

FROM left_table

LEFT JOIN right_table

ON left_table.column_name = right_table .column_name;

Example

Since the LEFT Join returns all from the left table and only matches from the right table, it can be used to validate if the record in one table is also there in the second table.

SELECT School.studentName, Attendence.Attendneceocunt

FROM School

LEFT JOIN Students ON School.studentID = Attendence.AttendenceID;

Conclusion

You would have gotten your answer if you learned about the top 10 most basic Commands. You must select a particular set of records to display, and the SELECT will help you with the same. Uncontrolled duplication cannot occur; however, sometimes, avoiding controlled duplication in the result set is essential, and the SELECT DISTINCT will help you. Further, you can use the INSERT, UPDATE and DELETE queries to manage your database. Also, the INNER JOIN and LEFT JOIN keywords will help when working on multiple tables.

Top 10 Most Basic SQL Commands To Know