SQL Commands: The List of Database Queries & Statements

SQL Commands: The List of Database Queries & Statements

Introduction

Some things in life only get better and precious as they become older. Some of those are Gold, Wine, and SQL. No, your eyes aren’t deceiving you. The early manifestations of SQL first appeared in 1970 when Edgar Codd stressed the need to find a new way to organise data in databases. LinkedIn calls learning SQL commands “the greatest career decision you will make”. 

Today, Structured Query Language (SQL) is the industry standard for accessing and querying data in relational databases. Because of SQL‘s simplicity of use and ubiquity, the developers of many “NoSQL” or non-relational data stores, such as Hadoop, have adopted subsets of SQL or created their own SQL-like query languages. Let us see the basic SQL commands that you should be aware of.

Understanding SQL Commands

You need to understand that commands and queries are not interchangeable. Commands are words that makeup queries. One query contains multiple commands. We will use a database named Interview_Skills, where a table called Skills exists. 

#1 Creating the database Interview_Skills
Syntax: CREATE DATABASE database_name;
Query:  
#2 Creating the table Skills
Syntax: CREATE TABLE table_name;
Query:  
#3 Adding a column Serial_Number to the table
Syntax: ALTER TABLE table_name ADD column_name column-type; 
Query: 

Note: Column-type is the type of data that should be present in that column. Here, INT is used for numbers. For insight into the many column types present, click here!

#4 Two columns are better than one! 
Syntax: ALTER TABLE table_name ADD (column_1 column-type,  column_2 column-type);  
Query:  

You can extend this for n columns.

#5 Change char to varchar in the Name column
Syntax: ALTER TABLE table_name MODIFY column_name column_type;
Query:  

This can also be extended for n columns.

#6 Why Serial_Number when S_No_ is enough?
Syntax: ALTER TABLE table_name RENAME COLUMN old_name to new_name; 
Query:  
#7 Do I even need S_No_?
Syntax: ALTER TABLE table_name DROP COLUMN column_name; 
Query: 
#8 Let me just delete the whole table
Syntax: DROP TABLE "table_name";
Query:
#9 Let me just delete the whole table Part 2
Syntax: DELETE FROM table_name;
Query:
#10 Let me just delete the whole table Part 3
Syntax: TRUNCATE TABLE Skills;
Query:

Above are three different ways to delete a table with slight advantages of their own.

Truncate is much faster than Delete due to the usage of lesser resources, while Drop is preferred over Delete in case you just want to change the data and leave the structure intact.

#11 A clean slate
Syntax: DROP DATABASE database_name;
Query:

Frequently Asked Questions

What are the five basic SQL commands?

The five basic SQL commands are:

1. ALTER: It is used to add, modify, or delete columns in a table.
2. UPDATE: It is used to change the data present in rows of the table.
3. DELETE: It is used to delete only the data in the table.
4. INSERT: It is used to insert data into the table with/without specifying the columns under which it goes.
5. CREATE: It creates a new table.

The key to understanding SQL is in analysing what commands are similar for a table and database and what commands are not. To help you understand the nuances, here’s our million-word speaking cheatsheet.

What are the types of SQL commands?

SQL commands are classified into the following five types:

Data Definition Language (DDL): SQL commands that affect the table’s structure come under DDL.
The changes resulting from a DDL command are stored in the table as long as the table exists.

Some DDL commands are:


Data Manipulation Language (DML): SQL commands that modify the database but leave the table structure intact fall under DML. The changes made by a DML command are permanent.


Data Control Language (DCL): SQL commands here change access provided to a user either by granting or withdrawing it. 




Data Transaction Language (DTL): SQL commands that work with transactions (i.e., any work, operation) in the database comprise DTL. DTL is always accompanied by DML commands.



Data Query Language (DQL): SQL command that is used to query the database. Only one command exists: SELECT

What is an example of an SQL query?

Consider our table of SQL commands that belongs to a database in SQL. Let this table be called ‘Commands’.

When we want to view the entire table, we use the SQL command:


Where,
SELECT is a command for querying
indicates ALL rows in the table
FROM indicates where the data should be taken from.
COMMANDS is the name of our table.

Where do you write SQL commands?

SQL commands can be written anywhere, right from coding grounds on the internet to tutorials. However, if you want complete freedom over the data you work with, download the MySQL 8.0 Command-Line Client for practising MySQL-based queries.

Is SQL a coding language?

No, SQL is a query language. Coding involves a sequence of instructions, while query deals with how to derive a particular set of data.

What does ‘*’ mean in SQL?

‘*’ means ALL columns that are in the table inside the database should be displayed upon running the query. Otherwise, we use the name of the specific columns after the SELECT command.

What is Grant in SQL?

SQL Grant command can be used to provide access or privileges on the database objects to the users.

The GRANT command has the following syntax:

GRANT privilege_name ON object_name
TO {user_name | PUBLIC | role_name} [with GRANT option];

What are SQL DCL commands?

DCL commands are used in the database to monitor access and handle permissions for users. We can conveniently authorise or reject such activities for users on tables or records using them.

What are SQL joins?

A JOIN clause is used to join rows from two or more tables centred on a common column. It is used to join two tables together or to extract data from them.

There are four kinds of joins:
1. Inner join
2. Left Join
3. Right Join
4. Full Join

Key Takeaways

In conclusion, cementing your SQL knowledge as you go step by step requires two things. One, code as you go. This is where CodeStudio comes into the picture; not only do you get to meet other such enthusiasts, you also gain perspective on the skill set your dream company expects you to showcase in your interviews. Two, cement practice with the knowledge of established concepts. Our carefully chosen set of books that can teach you SQL at a pace you appreciate and the depth you want are here.

Exit mobile version