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 –
click here – Planning a New Life in the UK
- 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.
click here – You Should Never Ignore These 5 Romantic Gifts!!
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/