12 Basic SQL Commands for Beginners

1. Select

This select command is one of the simplest and most essential commands of SQL. As the name suggests, this command is used to select/retrieve the data from a database.

SELECT * FROM table;

This command can be broken down into two parts. The first part (Select *) specifies which columns need to be retrieved from a table, and the second part (FROM table) specifies the name of the table from which data needs to be retrieved.

2. Insert

We talked about retrieving the records from a table, but what about inserting a new record? The insert command is used to insert a new record into a table.

INSERT into table_name(column1, column2,...) VALUES (value1, value2,...);

We can specify the name of the table in which we want to insert a new record, column names, and values that need to be inserted.

The VALUES clause is used to specify that these are the values that need to be inserted.

3. Alias

In SQL, aliases are used to temporarily rename a table. An alias is created with the as keyword. We use the as command to provide a temporary name to an entity. We can give a temporary name to a table or any column inside the table.

Table alias syntax:

SELECT name, age FROM person as alias_name ;

Column alias syntax:

SELECT name as alias_name FROM person;

Aliases are also useful to avoid ambiguity errors when we join two tables and they both contain columns with the same name.

4. Where

We have seen that the select command is used to retrieve the data from a database, but what if we want to filter the records based on some conditions?

The where command allows you to apply some conditions on the select query to filter the data. We can use the where clause to limit the number of records in a table. The where clause is used at the end of the statement.

SELECT age, name FROM person p WHERE p.age > 10 ;

We can apply multiple conditions in a where clause by separating them with the AND operator.

SELECT age, name FROM person p WHERE p.age>10 AND p.age<20;

5. Order By

The Order By command is used to sort records based on specified columns. You can sort the records in ascending or descending order.

SELECT age, name FROM person ORDER BY age DESC;

We need to specify the names of the columns and the order: ASC for ascending and DESC for descending order.

6. Join

The join command is used to combine two or more tables present in a database based on the common column present in both tables.

We need to specify the name of the column based on which one we want to join both the tables.

SELECT s.name, s.rollNo, s.courseId  FROM Student s JOIN Department d WHERE s.courseId = d.courseId ;

7. In

In SQL, the In operator is used to retrieve records based on the values present in the list of values.

SELECT name, salary FROM employee WHERE salary IN(20000, 25000, 30000);

The script above will retrieve all the records from the employee table where the salary of an employee will be either 20000, 25000, or 30000.

We can also use the NOT IN clause to exclude the records that match the values present in the list.

8. Alter

The alter command is used to change the structure of the table. Using this alter command, we can ADD a new column to our table, DROP a column from the table, and also MODIFY the existing column present in the table.

Add column:

ALTER TABLE person ADD salary integer;

This script will add a column salary of integer type in the person table.

Drop column:

ALTER TABLE person DROP COLUMN age;

This script will drop the column named age from the person table.

Modify column:

ALTER TABLE person MODIFY salary float;

The script above will modify the type of the salary column from integer to float in the person table.

9. Update

After inserting the values in the table, wrong values could be inserted in some rows and need to be corrected.

In such cases, the Update command is used to update the values in specified rows. We can use the where clause to specify which records need to be updated. We can update the values of more than one column at a time.

UPDATE people SET age=25 WHERE name='John' ; 

The query above will update the value of the age column of the record named ‘John’ to 25.

10. Delete

The Delete command allows you to remove records from a table. You can delete specific records from a table by using the where clause along with the delete clause.

Its syntax is very simple and easy to use:

DELETE FROM person WHERE name='John' ;

The delete command must be used carefully because you cannot retrieve the data once the records are deleted.

11. Create Table

As the name suggests, the create command is used to create a new table in the SQL database. Its syntax is:

CREATE TABLE person(age integer, name varchar(50), salary integer);

You need to specify the name of the table (i.e. person in our example) and the column names that you want to be in your table, along with their respective data types.

12. Drop Table

The drop command is used to drop the table from a database. If we compare the drop command to the delete command, the delete command is used to delete the specific rows from a table. Meanwhile, the drop command is used to drop the whole table from a database.

Its syntax is :

DROP TABLE table_name ;

After executing this command, you will see that the whole table is removed from the database.