Do you think IIT Guwahati certified course can help you in your career?
No
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 and queries you should know.
SQL (Structured Query Language) commands are statements or instructions used to interact with a database.
SQL commands perform various operations on data stored in a Database, such as querying data, inserting new data, updating existing data, deleting data, creating tables, editing tables, indexing, etc.
SQL commands are usually entered into a SQL interpreter or client tool such as MySQL, Oracle, Microsoft SQL Server, PostgreSQL, etc.
The SQL interpreter or client tool then communicates with the database to execute the command and return a result in the son of the son.
SQL commands are necessary for managing and manipulating data in a relational database system.
They provide a customized way for developers, administrators, and users to interact with the database and perform various operations on the data.
Types of SQL Commands
There are four types of SQL Commands:
Data manipulation language(DML) Commands: DML commands are used to manipulate data stored in a database. Commonly used DML commands include SELECT, INSERT, UPDATE, DELETE, etc. These commands are sometimes used to retrieve data, insert new data, modify existing data, and delete data from a database.
Data Definition Language (DDL) Commands: DDL commands define a database schema or structure. DDL commands commonly include CREATE, ALTER, DROP, TRUNCATE, etc. These commands are used to create a new table, modify an existing table structure, delete a table, truncate a table, and so on.
Data Control Language (DCL) Commands: DCL commands control access to the database. Commonly used DCL commands include GRANT, REVOKE, etc. These commands are used to grant or revoke privileges for users or roles to perform specific operations on the database.
Transaction Control Language (TCL) Commands: TCL commands are used to manage transactions in a database. Commonly used TCL commands include COMMIT, ROLLBACK, SAVEPOINT, etc. These commands are used to commit or roll back jobs and set savepoints in a transaction.
In a relational database system, data is stored in tables. Each table contains columns (also called fields) that represent the data attributes and rows (also called records) that represent individual data instances.
Example:
Serial Number
Customer Name
Email
Phone No.
01
Rahul Joshi
rahuljoshi28@gmail.com
9875634321
02
Rohan Sharma
rohansharma2924@gmail.com
8759345257
03
Roshni Kumari
roshnikumar314@gmail.com
9678424627
In the above table, each row represents, and the columns represent customer attributes, such as their name, email address, and phone number.
You need to understand that basic SQL 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.
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.
What are the types of SQL commands?
SQL commands are classified into the following five types:
1. 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 create, drop, alter and truncate. 2. 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. Commands are insert, update and delete. 3. Data Control Language (DCL): SQL commands here change access provided to a user either by granting or withdrawing it. Commands are grant and revoke. 4. 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. Commands are commit, rollback and savepoint. 5. 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: Select * From COMMANDS;
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.
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.
Conclusion
In conclusion for basic SQL commands and queries, cementing your SQL knowledge as you go step by step requires two things. One, code as you go. This is where Coding Ninjas Studio comes into the picture; not only do you get to meet other such enthusiasts, but 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.