Top 10 Most Basic SQL Commands To Know

Let's check out each of these with examples.

1. SELECT Command

The SELECT command is a fundamental part of SQL and RDBMS, used to retrieve data from a database. It allows users to fetch data either from the entire table or a specific subset, depending on their requirements. The retrieved data is then stored in a table called the result-set, providing the desired output.

To write a SELECT query in SQL, start with the keyword "SELECT" followed by the fields you want to retrieve, separated by commas. If you want to retrieve specific fields, list their names. For example, "SELECT field1, field2 FROM table_name;" or "SELECT * FROM table_name;" to retrieve all fields.

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
The SQL command to create a table in a database is `CREATE TABLE table_name (column1 data_type, column2 data_type, ..

SELECT and FROM are keywords

You can fetch data elements from the entire table without specifying column names.

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 * FROM table;

Example:

Select StudentName from School

The command will display a list of all students in the School database from the Student Name column. If you want to see all records, you can use an asterisk (*) instead of a specific column name.

2. SELECT DISTINCT Command

The SELECT DISTINCT command is used in a query to display unique values from a specified column of the table only, where 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 SQL query selects the unique values from the 'Subjects' column in the 'Student' table, effectively removing any duplicates, and displays the result.

To remove duplicate column values in a result set, you can use the `DISTINCT` keyword. This keyword is used to select unique rows from a table. When combined with the `SELECT` statement, it helps to eliminate duplicate values in the result set.

SELECT DISTINCT

column1_name,

column2_name,

column3_name

columnn_name

FROM

table_name;

The command requires specifying the name of all columns in a serial order, eliminating duplication from the sum of columns in the result set. It displays only different sets of values from all columns in the result set.

Example

SELECT DISTINCT address_area FROM student

To avoid duplicate values in a query, you can use a command to find unique district names. For instance, if students live in the same district, a general query might result in duplicate values. In such cases, running a command to find unique district names can be helpful.

3. WHERE Command

When you want to extract specific data from a table, you use the WHERE command and specify the criteria, such as "age>18" to get data from the student table of students who are 20 years old.

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 fields of the table, table_name is the name of the table, column_name is the column being checked, operator is the condition checking operator, and value is the data to be retrieved.

Example:

Select StudentName from School Where Subject="Physics"

This will list all students who have taken Physics as one of their subjects, filtering out any students who don't have it as one of their subjects.

4. ORDER BY command

The ORDER BY command is used to sort data fetched in an SQL query in ascending or descending order, either in one or multiple columns.

You've likely created a table of student progress reports, and now you can sort it in descending order to quickly find the maximum value.

The syntax for Order By Command

SELECT * FROM table_name

ORDER BY column_name ASC|DESC

In SQL, the syntax to delete a record from a table is DELETE FROM table_name WHERE condition; where table_name is the name of the table from which you want to delete a record, and condition is the criteria for selecting which record to delete. For example, DELETE FROM customers WHERE country='USA'; would delete all records from the customers table where the country is 'USA'.

The column name you want to sort by and the order, either ascending (ASC) or descending (DESC), should be specified in the sort command.

You can sort multiple columns in a specified order by separating the column names with a comma (,) operator. For example, if you want to sort columns A, C, and E in ascending order, you can use the formula `=SORT(A:C,E,F)` or `=SORT(A:E,A,C,E)`. The order of the columns in the formula corresponds to the desired order of sorting. You can also use the `=SORTBY` function to sort by multiple columns.

Syntax of ORDER BY command for multiple columns

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

Here, you specify the order of each column, either ascending or descending, allowing you to choose the sorting pattern for each column without needing multiple queries.

Example:

Select StudentName from School Order by Roll No

To display students based on their roll number, you can use the command `sort by roll_number` which will display students in ascending order by default. If you need to sort in descending order, you can use `sort by roll_number desc`. This command allows you to sort students based on their roll number instead of a name.

5. INSERT INTO Command

To insert a row into a table, you can use the INSERT INTO command. This command allows you to add a new row to the table by specifying the values to be inserted into each column.

Syntax: Insert into Table

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

To insert a new column into a table in SQL, you can use the ALTER TABLE command with the ADD COLUMN clause. The basic syntax is: ALTER TABLE table_name ADD COLUMN column_name data_type; where table_name is the name of the table into which you want to insert the column, column_name is the name of the new column, and data_type is the data type of the new column.

You can use the following SQL query to add new columns to an existing table:

Syntax to insert into multiple columns:

The INSERT INTO command is also used to add values in multiple columns, in addition to adding a single value to a single column.

Syntax:

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

Example:

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

To add values to the column 'RNO' of the table 'Student' with the values 10625, 'NAME' with the value 'SHRUTI', and 'CLASS' with the value 20, you can use the following SQL command: INSERT INTO Student (RNO, NAME, CLASS) VALUES (10625, 'SHRUTI', 20).

6. UPDATE Command

The UPDATE command is used to upgrade data in a SQL table column name. It allows you to update values in a single column or multiple columns as per your prerequisites, making it a brilliant feature.

The syntax for Update Command

UPDATE table_name

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

Where columnname="columnvalue";

To update a table, you can use the UPDATE SQL command. The basic syntax is: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; where table_name is the name of the table you're working on, column1 and column2 are the columns you want to update, and value1 and value2 are the new values you want to assign to them. You can also add a condition to specify which rows to update. For example, if you want to update all rows where the age is 25, you would add WHERE age = 25 at the end.

Update and Set are the query keywords.

Example:

UPDATE School

StudentName = 'Sachin', Subject= 'Chemistry'

WHERE StudentID = 1;

A student's details can be updated, such as their name and subject, using the StudentID 1. This is particularly useful in cases where a student's name or subject changes during the course, allowing for the most up-to-date information to be recorded.

7. DELETE Command

When you want to delete existing values from a table, you use the DELETE command, allowing you to delete one or multiple values based on a specified condition.

The syntax for Delete Command

DELETE FROM table_name WHERE given_condition;

You can delete a record from a table using the SQL command `DELETE FROM table_name WHERE given_condition`. This command removes the specified record(s) based on the condition provided, so for example, to delete all records from a table called "employees" where the employee's ID is 123, you would use `DELETE FROM employees WHERE id = 123`. Be careful when using the DELETE command as it permanently removes data from the table. Make sure to backup your data before executing the command.

It's crucial to use the WHERE keyword when specifying conditions for deletion in a database table, as this ensures that only records meeting the specified criteria are deleted, and not all data in the table. If the condition is present in multiple records, they will be deleted wherever the condition is specified. If the DELETE keyword is not used, the entire table will be cleared.

Example:

Delete from School Where StudentID="3"

The record of Student with ID 3 has been deleted from the School Table, ensuring that only that specific record is deleted due to Student ID being a unique identifier.

8. SUM Command

The SUM command calculates the total of a specific column or expression, and can be used with the DISTINCT keyword to sum only unique values. It's a numeric function that ignores any NULL values in the table.

The syntax for SUM Command

SELECT SUM(aggregate_expression)

FROM tables

[WHERE conditions];

To calculate the sum of a specific column in a query, simply specify the column name in the query, and the database will sum up all the values in that column. If you want to calculate the sum of a different expression, use that expression in the query instead.

Example:

SELECT SUM(Marks)

FROM School

[WHERE StudentId =3];

The program will add up all the marks obtained by a particular student from all subjects and print out the results.

9. INNER JOIN Command

The INNER JOIN command is used when multiple tables are present, combining rows from two tables and displaying the result set until 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

The query allows for a variable number of columns, from 1 to n, to be selected from the table. The inner join keyword combines specific columns from both tables, displaying the result in a result set.

Example:

SELECT School.studentName, Attendence.Attendneceocunt

FROM School

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

The query performs an inner join on the Student Attendance table, which is connected to the Student ID in the Student table, to fetch the Student Name and Attendance based on the matching Student ID.

10. LEFT JOIN Command

When you have multiple tables, the first table is considered the left table and the second one is the right table, allowing you to create a query from both tables simultaneously.

This command retrieves rows from the left table and matching rows from the table on the right, or the table on the right if there are no matching rows in the left table. The result set will contain null values if there are no matching rows in the left table. The command will return all columns from both tables. The command is used to combine data from two tables based on a common column.

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

The LEFT JOIN is used to validate if a record in one table is also present in another table by returning all records from the left table and only matching records from the right table.

SELECT School.studentName, Attendence.Attendneceocunt

FROM School

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

Conclusion

To get the desired output, you can use the SELECT command to display a specific set of records. If you want to avoid duplication in the result set, you can use SELECT DISTINCT. You can also use INSERT, UPDATE, and DELETE queries to manage your database. When working with multiple tables, INNER JOIN and LEFT JOIN can be helpful.

Top 10 Most Basic SQL Commands To Know