SQL Commands

---

PART – 1

---

Structured Query Language or SQL is a language that is used to manage and manipulate relational databases. SQL allows users to interact with databases by querying, updating and managing data. We can primarily divide SQL commands into three types: DDL, DML and DQL. This blog is the first part of the series where we will discuss what is DDL, DML and DQL. We will also understand the syntax of some of these commands with examples. I am using MYSQL database management system.

DDL

Data Definition Language or DDL is used to work with the schema of the database. The commands under DDL include CREATE, ALTER, DROP, TRUNCATE and RENAME. Below table summarizes what each of these commands does.

DML

Data Manipulation Language or DML is used to modify the data in the database. The commands under DML include INSERT, UPDATE and DELETE. Below table summarizes what each of these commands does.

DQL

Data Query Language or DQL is used to query the data in the database. The command under DQL is SELECT command. Below table summarizes what this command does.

Demonstration

Now that we understood the purpose of these SQL commands, we will now understand how to use them. I have downloaded and configured MySQL 9.1 and am using the CLI for the following demonstration.

We will create a new database called thinknyx and add a table named employees inside that database. We will also explore how to add records into this table.

Create and Select Database:

We will first create our thinknyx database and then select that database. The syntax for the same is:

CREATE DATABASE dbname;
USE dbname;

We will now use this in our demonstration:

CREATE DATABASE thinknyx;
USE thinknyx;

Create Database table:

We will use DDL commands to work with database schema. To create a table, we need to use the CREATE command. The syntax to use the same is as follows:

CREATE TABLE tablename (column1 datatype, column2 datatype,..);

We will create a table by the name employees. We will create three columns- id, name and designation. Let the datatype of id be int. For name and designation, we will use varchar.

CREATE TABLE employees (id int, name varchar(50), designation varchar(50));

Insert records:

To add new records, we will have to use INSERT DML command as learnt earlier. The syntax is as follows:

INSERT INTO tablename VALUES (value1, value2,..);

Let’s insert two records into our table. We have three columns – id, name and designation. So, we will add values for each of these columns. For the first record:

INSERT INTO employees VALUES (1,'alice','software engineer');

Similarly, let’s insert the second record:

INSERT INTO employees VALUES (2,'john','tester');

Retrieve Data:

To retrieve data, we have to use the SELECT DQL command. The syntax is:

SELECT * FROM tablename;

To retrieve records from employees table:

SELECT * FROM employees;

Summary

In this blog, we covered what DDL, DML and DQL commands are. We understood how to create a table, insert records and retrieve data from the table. In the next part of this series, we will dive deeper into more SQL commands.

By- Deepthi Narayan