Introduction to SQL Commands

This article enfolds all the fundamental SQL commands that beginners need to start to learn. SQL command serves as an order for dealing with databases. To carry out functions, tasks and manipulate data, programmers need to have all SQL command files. SQL commands are used to look for database and several other functions like adding data, creating a table, dropping a table, and modifying tables. 

SQL Definition

SQL is a database language used to query and manipulate the data in the database. Structured Query Language is the full form of SQL. It is a simple and powerful language used to create, access, and manipulate data and structure in the database. There are various types of SQL commands to carry out functions and tasks. We have listed down some of the types of SQL commands –

  • Data Definition Language (DDL)

 SQL DDL command statements modify, define and remove objects from data dictionary tables maintained by the DBMS. Whenever you use a DDL statement, the DBMS Changes metadata maintained in a data dictionary.

Examples –

CREATE

ALTER

DROP

  • Data Manipulation Language (DML)

 SQL DML commands is one of the database languages that enables users to access or manipulate an organized data model.

Examples –

INSERT

UPDATE

DELETE

  • Data Control Language (DCL)

DCL languages are used to control the user access to the database, tables etc.

Examples –

GRANT

REVOKE

  • Transaction Control Language (TCL)

TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.

Examples –

ROLLBACK

COMMIT

List of the top 10 SQL Commands

This article will envelop the following list of fundamental SQL command list –

CREATE TABLE

The first step to store data in a database is to add a table. To do this, we use the CREATE TABLE command in SQL.

syntax – CREATE DATABASE datbase_name;

EX – CREATE DATABASE my_db

It creates a new table in the specified/current schema or restores an existing table. A table can have various colour the column has:

 – A default value and/or requires a value (NOT NULL)

 – Any referential integrity constraints (primary key, foreign key, etc.)

ALTER TABLE and ADD

The ALTER TABLE statement is used to add, delete or modify columns in an existing table.

The ALTER commands in SQL is also used to add and drop various constraints on an existing table.

ALTER TABLE table_name

ADD column_name datatype;

ALTER TABLE table_name

DROP COLUMN column_name;

ALTER TABLE table_name

MODIFY COLUMN column_name datatype;

syntax –  ALTER TABLE <TABLENAME>ADD<COLUMN

                NAME><DATATYPE><SIZE>;

                 ALTER TABLE <TABLENAME>MODIFY

                  <COLUMN NAME NEWDATATYPE<SIZE>>;

SQL command – ALTER TABLE EMPLOYEE ADD (ENO2 INTEGER);

                           ALTER TABLE EMPLOYEE MODIFY (ENO VARCHAR(20));

INSERT and VALUES

A DML Command for inserting one or more rows into an RDBMS table helps add new value to the table. 

syntax – INSERT INTO table_name

              (column1_name, column2_name,…)

               VALUES ( column1_value, column2_value, … )

 

You can insert commands in SQL to add more values.

UPDATE, SET and WHERE

A command in SQL used to modify the rows in a table of an RDBMS helps to modify the records in the table of a database

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.

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 containing three will get deleted.

SELECT and FROM

The SQL SELECT commands display data. It is possible to pick 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. Perform the above statement will exhibit data belonging to the mentioned columns only. This way, we don’t have to show the entire table.

GROUP BY and COUNT

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

Here is an example of the GROUP BY command –

SELECT COUNT(empID), empCity

FROM Employee

GROUP BY empCity;

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

HAVING

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

Demonstration –

SELECT COUNT(empID), empCity

FROM Employee

GROUP BY empCity

HAVING COUNT(empID) > 5;

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

ORDER BY

The ORDER BY command sorts results according to the items in the SELECT command. We can classify them in descending or ascending 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 last name, first name and employee id of employees belonging to the Employee table. The records are presented in descending order of first names.

Conclusion

We have explored only the basic SQL commands.  Beginners can start on these and build up their expertise on other SQL commands after this standard one. You can join Jigsaw Academy for the data science certification courses and get your data science certificate.

References

https://www.jigsawacademy.com/blogs/data-science/sql-commands/

Leave a comment

Your email address will not be published. Required fields are marked *