Creating a relational database management system and not knowing the basic commands seems terrible. At the most, you can create a table using the Create command but cannot perform any operations and manage it further. To resolve the problem, you must know some fundamental keywords that will act as commands, and you can create a query and manage the database. So, if you are thinking about the Top 10 Most Basic SQL commands to know, then we have got you all covered with it!
- SELECT Command
- SELECT DISTINCT Command
- WHERE Command
- ORDER BY command
- INSERT INTO command
- UPDATE command
- DELETE command
- SUM command
- INNER JOIN command
- LEFT JOIN command
Top 10 Most Basic SQL Commands To Know
Let's check out each of these with examples.
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
- 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.
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.
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
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:
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.
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.
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
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.
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.
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.
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.
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.
INSERT INTO table_name (column1, column2,column3) VALUES (value1, value2, value3);
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.
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
SET column1 = value1, column2 = value2,...
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.
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.
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.
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.
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
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.
[WHERE StudentId =3];
It will add all the marks obtained by a particular student from all subjects and print the results.
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.
SELECT School.studentName, Attendence.Attendneceocunt
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.
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
LEFT JOIN right_table
ON left_table.column_name = right_table .column_name;
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
LEFT JOIN Students ON School.studentID = Attendence.AttendenceID;
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.
Was This Page Helpful?
Tips & Tricks
- How to Partition A Hard Drive in Windows 10
- Fix Corrupted/Damaged SD Card Without Losing Data
- Recover Deleted or Lost Android Photos/Videos with or without Computer
- [Easy & Fast] How to Recover Deleted Files on PC Windows 10/8/7
- How to Share Files Between Two Laptops with or Without WiFi in Windows 10
- How to Migrate Windows 10 from HDD to SSD
- 6 Ways to Transfer Files from PC to iPhone/iPad With/Without iTunes