Basic SQL Commands: An Easy Guide With 10 Commands

Introduction To SQL Commands

This article covers all the basic SQL commands that you need to know to get started on the topic. SQL commands serve as instructions given to deal with a database. Programmers need to have a list of all SQL commands to carry out tasks, functions, and manipulate data. SQL commands can be used to search the database and to do other functions like creating tables, adding data to tables, modifying data, and dropping tables.

Contents At A Glance

1] Definition

2] List of commands

1. CREATE TABLE
2. ALTER TABLE and ADD
3. INSERT and VALUES
4. UPDATE, SET and WHERE
5. DROP
6. DELETE
7. SELECT and FROM
8. GROUP BY and COUNT
9. HAVING
10. ORDER BY

SQL Definition

SQL is the abbreviation for Structured Query Language. The following are the various types of SQL commands –

Data Definition Language (DDL)

We use SQL DDL commands to define the database.

Examples –

CREATE

ALTER

DROP

Data Manipulation Language (DML)

We use SQL DML commands to manipulate the database’s data.

Examples –

INSERT

UPDATE

DELETE

Data Control Language (DCL)

DCL commands deal with the user permissions and controls of the database system.

Examples –

GRANT

REVOKE

Transaction Control Language (TCL)

TCL statements deal with database transactions.

Examples –

ROLLBACK

COMMIT

List Of Top 10 SQL Commands

This article will cover the following list of basic SQL command list –

1. CREATE TABLE

CREATE TABLE creates a table in the database. 

The example mentioned below will demonstrate the use of CREATE TABLE.

CREATE TABLE Employee (

    empID int,

    firstName varchar(255),

    lastName varchar(255),

    empSalary int

);

After CREATE TABLE, we add the table name. We specify its column names with their respective datatypes in round brackets and separate each name-datatype pair with a comma.

2. ALTER TABLE and ADD

ALTER TABLE changes the table structure. ALTER TABLE is not the same as the UPDATE command. However, many get confused between the two. We need to remember that there are no ALTER commands in SQL. The correct syntax is ALTER TABLE.

Let us understand the ALTER TABLE command first –

ALTER TABLE Employee

ADD empCity varchar(255);

Here, we specify the table we want to alter after ALTER TABLE. We then choose how we would like to change the table. Using the ADD command, we add an extra column to our Employee table.

3. INSERT and VALUES

INSERT commands in SQL add values to the table.

Let us look at an example –

INSERT INTO Employee

VALUES (1, ‘John’, ‘Doe’, ‘Bangalore’);

Here, we add our first row to our Employee table. While using INSERT INTO, we need to add values for each column of the table. We add the respective values after the VALUES command, in the same order.

But how do we insert values for only specific table rows? Let us look at an example –

INSERT INTO Employee (empID, firstName, lastName)

VALUES (1, ‘Jane’, ‘Doe’);

In this case, we specify the column names in parenthesis and mention their respective values. The order of columns should be the same as their order during the time of table creation. The values should also follow the same order. 

4. UPDATE, SET and WHERE

The UPDATE command is used to update existing rows in a table. Thus, when we want to make changes to the table, like adding or removing columns, we use ALTER TABLE. When we want to change the table rows, we use the UPDATE command.

The following example shows us how to use UPDATE –

UPDATE Employee

SET firstName = ‘Joe’, lastName = ‘Blow’

WHERE empID = 1;

5. DROP

The DROP statement drops or loses an existing table or a database.

Let us look at an example –

DROP TABLE Employee;

This deletes the Employee table.

6. DELETE

We use the DELETE statement to delete existing records in a table.

Demonstration –

DELETE FROM Employee WHERE empID=3;

Here, the row containing employee id as three will get deleted.

7. SELECT and FROM

The SQL SELECT commands display data. It is possible to choose the data columns to display in the result. We write column names after SELECT and the table name after FROM.

Let us look at the following code –

SELECT empID, firstName, lastName

FROM Employee;

Here, we want to show the employee id, the employee’s first and last name from the Employee table. Executing the above statement will display data belonging to the mentioned columns only. This way, we don’t have to show the entire table.

8. GROUP BY and COUNT

The GROUP BY command lets you group rows and aggregate data.

Here is a demonstration of the GROUP BY command –

SELECT COUNT(empID), empCity

FROM Employee

GROUP BY empCity;

This code will display the number of employees belonging to each city. The COUNT command counts the number of empIDs.

9. HAVING

HAVING enables you to filter the data aggregated by the GROUP BY clause to display a limited recordset.

Demonstration –

SELECT COUNT(empID), empCity

FROM Employee

GROUP BY empCity

HAVING COUNT(empID) > 5;

Here, we will only see records with cities having less than five employees.

10. ORDER BY

The ORDER BY command sorts results according to the items in the SELECT command. We can sort them in an ascending or descending order. The default sort order is ascending (ASC). We use DESC for sorting in descending order.

Example –

SELECT empID, firstName, lastName

FROM Employee

ORDER BY firstName DESC;

This code displays the employee id, first name and last name of employees belonging to the Employee table. The records are displayed in descending order of first names.

Conclusion

In this article, we learned about the basic SQL commands with examples. For beginners, these commands must be known and understood. There are a bunch of various other SQL commands that you can learn after understanding these standard ones. Having a good command of the Structured Query Language is pivotal for Data Scientists.

Are you a Data Science enthusiast? To learn more about Data Science concepts, tools and more, visit Jigsaw Academy’s 11-month Postgraduate Diploma In Data Science. This in-person course has been ranked #2 among ‘Top 10 Full-Time Data Science Courses in India’ in 2019, 2018, 2017. Lastly, it provides certification from Manipal Academy of Higher Education, a guaranteed placement policy, and much more.

ALSO READ

• How to Become a Data Engineer? Explained in 5 Easy Points