SQL(Structured Query Language) commands are instructions. It is used to interact with the database, and it is also used to perform specific tasks, functions, and data queries. SQL can perform Several tasks like creating a table, adding data to tables, dropping the table, modifying the table, set permission for users. Here, I explain different SQL commands and respective syntax so that this quick read can be your refresher.
I begin with a broad classification of SQL commands.. 🚀
Depending on which operation the SQL code will be doing, there are five different types of SQL commands, as shown below.
1. DDL: Data Definition Language
2. DML: Data Manipulation Language
3. DCL: Data Control Language
4. TCL: Transaction Control Language
5. DQL: Data Query Language
I explain these types and the SQL commands used in the subsequent sections below.
DDL changes the format of the table, such as creating a table, deleting a table, and altering a table.
All the command of DDL is auto-committed which means it permanently save all the changes in the database. Below are the DDL commands and their syntax.
CREATE TABLE
CREATE TABLE
Creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table.
CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);
ALTER
ALTER TABLE
Let’s you alter the table as per your requirement.
For example, you can add, remove and rename the columns as shown below.
-- Add a column ALTER TABLE table_name ADD column_name datatype;
-- Remove a column ALTER TABLE table_name DROP COLUMN column_name;
-- Rename a column ALTER TABLE table_name CHANGE COLUMN old_name new_name;
Moreover, ALTER
lets you change the table name itself with the below syntax.
ALTER TABLE old_table_name RENAME TO new_table_name;
DROP
It is used to delete both the structure and record stored in the table.
DROP TABLE table_name;
TRUNCATE
It is used to delete all the rows from the table and free the space containing the table.
TRUNCATE TABLE table_name;
DML commands are used to Customize the database, and it is responsible for all forms of changes in the database.
The command of DML is not auto-committed, which means it can’t permanently save all the changes in the database. They can be rollback. Below are the DML commands and their syntax.
INSERT
INSERT
Statements are used to add a new row to a table.
INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, value_2, value_3);
UPDATE
UPDATE
statements allow you to edit rows in a table.
UPDATE table_name SET column_name = value WHERE condition;
DELETE
DELETE
statements are used to remove rows from a table.
DELETE FROM table_name WHERE some_column = some_value;
DCL commands grants and takes back authority from any database user. It works as shown below.
GRANT
GRANT command is for giving users access to a database.
GRANT SELECT, UPDATE ON My_TABLE TO FIRST_USER, SECOND_USER;
REVOKE
It is used to take back permissions from the user.
REVOKE SELECT, UPDATE ON My_TABLE TO FIRST_USER, SECOND_USER;
Transaction Control Language — as its name suggests — is used to control the actions done by other non-auto-committed commands such as INSERT
, DELETE
and UPDATE
.
As I mentioned, DML commands are not auto-committed so TCL commands can be used with the DML. Below are the commonly used TCL statements.
COMMIT
This is used for saving every transaction to the database.
DELETE FROM VENDOR WHERE AGE = 29; COMMIT;
ROLLBACK
This command aims to undo the transactions that are not saved to the database.
DELETE FROM VENDOR WHERE AGE = 29; ROLLBACK;
SAVEPOINT
This is used for returning a transaction to a specific point without affecting the whole transaction.
SAVEPOINT SAVEPOINT_NAME;
DQL is used to fetch the data from the database. SELECT is the only and essential command widely and commonly used by all data analysts and scientists.
SELECT
The prime purpose of this statement is to get data from a database. Every query will begin with SELECT, followed by the names of columns you want to get from the table.
A particular column from the table can be selected with,
SELECT column_name FROM table_name;
And all the columns can be determined by using * as shown below,
SELECT * FROM table_name;
This article has taught you about the various SQL commands and simple SQL queries with examples. Some of the main concepts learnt in this article are as follows:
Data Definition Language(DDL) helps you define the database structure or schema.
Data Manipulation Language (DML) allows you to modify the database instance by inserting, changing, and deleting its data.
DCL (Data Control Language) includes commands like GRANT and REVOKE, which help give “rights & permissions.”
Transaction control language or TCL commands deal with the transaction within the database.
Data Query Language (DQL) is used to fetch the data from the database.
The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.
My name is Aparna Gurav, ServiceNow Developer Engineer/Platform Engineer/ at Sportradar Skilled in JavaScript/ SQL /SAP/ ITSM, CMDB,/Advanced Excel/Microsoft Power BI/System Admin Currently staying in Munich Germany. I like to learn New Things, Reading books and write blogs and Articles.
How to Work With CSV Files in Python?
Top 100 Data Science Interview Questions & ...
Introduction to SQL Commands and Sub Languages
How to Use DDL Commands in SQL
Structured Query Language (SQL) for All
SQL Data Manipulation Language (DML) Commands
One-Stop Guide to Your SQL
Most Important SQL Queries for Beginners
A Beginner’s Guide to MySQL: Part 2
Getting Started with Databases like MySQL
We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.
Show details
This site uses cookies to ensure that you get the best experience possible. To learn more about how we use cookies, please refer to our Privacy Policy & Cookies Policy.
It is needed for personalizing the website.
Expiry: Session
Type: HTTP
This cookie is used to prevent Cross-site request forgery (often abbreviated as CSRF) attacks of the website
Expiry: Session
Type: HTTPS
Preserves the login/logout state of users across the whole site.
Expiry: Session
Type: HTTPS
Preserves users' states across page requests.
Expiry: Session
Type: HTTPS
Google One-Tap login adds this g_state cookie to set the user status on how they interact with the One-Tap modal.
Expiry: 365 days
Type: HTTP
Used by Microsoft Clarity, to store and track visits across websites.
Expiry: 1 Year
Type: HTTP
Used by Microsoft Clarity, Persists the Clarity User ID and preferences, unique to that site, on the browser. This ensures that behavior in subsequent visits to the same site will be attributed to the same user ID.
Expiry: 1 Year
Type: HTTP
Used by Microsoft Clarity, Connects multiple page views by a user into a single Clarity session recording.
Expiry: 1 Day
Type: HTTP
Collects user data is specifically adapted to the user or device. The user can also be followed outside of the loaded website, creating a picture of the visitor's behavior.
Expiry: 2 Years
Type: HTTP
Use to measure the use of the website for internal analytics
Expiry: 1 Years
Type: HTTP
The cookie is set by embedded Microsoft Clarity scripts. The purpose of this cookie is for heatmap and session recording.
Expiry: 1 Year
Type: HTTP
Collected user data is specifically adapted to the user or device. The user can also be followed outside of the loaded website, creating a picture of the visitor's behavior.
Expiry: 2 Months
Type: HTTP
This cookie is installed by Google Analytics. The cookie is used to store information of how visitors use a website and helps in creating an analytics report of how the website is doing. The data collected includes the number of visitors, the source where they have come from, and the pages visited in an anonymous form.
Expiry: 399 Days
Type: HTTP
Used by Google Analytics, to store and count pageviews.
Expiry: 399 Days
Type: HTTP
Used by Google Analytics to collect data on the number of times a user has visited the website as well as dates for the first and most recent visit.
Expiry: 1 Day
Type: HTTP
Used to send data to Google Analytics about the visitor's device and behavior. Tracks the visitor across devices and marketing channels.
Expiry: Session
Type: PIXEL
cookies ensure that requests within a browsing session are made by the user, and not by other sites.
Expiry: 6 Months
Type: HTTP
use the cookie when customers want to make a referral from their gmail contacts; it helps auth the gmail account.
Expiry: 2 Years
Type: HTTP
This cookie is set by DoubleClick (which is owned by Google) to determine if the website visitor's browser supports cookies.
Expiry: 1 Year
Type: HTTP
this is used to send push notification using webengage.
Expiry: 1 Year
Type: HTTP
used by webenage to track auth of webenagage.
Expiry: Session
Type: HTTP
Linkedin sets this cookie to registers statistical data on users' behavior on the website for internal analytics.
Expiry: 1 Day
Type: HTTP
Use to maintain an anonymous user session by the server.
Expiry: 1 Year
Type: HTTP
Used as part of the LinkedIn Remember Me feature and is set when a user clicks Remember Me on the device to make it easier for him or her to sign in to that device.
Expiry: 1 Year
Type: HTTP
Used to store information about the time a sync with the lms_analytics cookie took place for users in the Designated Countries.
Expiry: 6 Months
Type: HTTP
Used to store information about the time a sync with the AnalyticsSyncHistory cookie took place for users in the Designated Countries.
Expiry: 6 Months
Type: HTTP
Cookie used for Sign-in with Linkedin and/or to allow for the Linkedin follow feature.
Expiry: 6 Months
Type: HTTP
allow for the Linkedin follow feature.
Expiry: 1 Year
Type: HTTP
often used to identify you, including your name, interests, and previous activity.
Expiry: 2 Months
Type: HTTP
Tracks the time that the previous page took to load
Expiry: Session
Type: HTTP
Used to remember a user's language setting to ensure LinkedIn.com displays in the language selected by the user in their settings
Expiry: Session
Type: HTTP
Tracks percent of page viewed
Expiry: Session
Type: HTTP
Indicates the start of a session for Adobe Experience Cloud
Expiry: Session
Type: HTTP
Provides page name value (URL) for use by Adobe Analytics
Expiry: Session
Type: HTTP
Used to retain and fetch time since last visit in Adobe Analytics
Expiry: 6 Months
Type: HTTP
Remembers a user's display preference/theme setting
Expiry: 6 Months
Type: HTTP
Remembers which users have updated their display / theme preferences
Expiry: 6 Months
Type: HTTP
Used by Google Adsense, to store and track conversions.
Expiry: 3 Months
Type: HTTP
Save certain preferences, for example the number of search results per page or activation of the SafeSearch Filter. Adjusts the ads that appear in Google Search.
Expiry: 2 Years
Type: HTTP
Save certain preferences, for example the number of search results per page or activation of the SafeSearch Filter. Adjusts the ads that appear in Google Search.
Expiry: 2 Years
Type: HTTP
Save certain preferences, for example the number of search results per page or activation of the SafeSearch Filter. Adjusts the ads that appear in Google Search.
Expiry: 2 Years
Type: HTTP
Save certain preferences, for example the number of search results per page or activation of the SafeSearch Filter. Adjusts the ads that appear in Google Search.
Expiry: 2 Years
Type: HTTP
Save certain preferences, for example the number of search results per page or activation of the SafeSearch Filter. Adjusts the ads that appear in Google Search.
Expiry: 2 Years
Type: HTTP
Save certain preferences, for example the number of search results per page or activation of the SafeSearch Filter. Adjusts the ads that appear in Google Search.
Expiry: 2 Years
Type: HTTP
These cookies are used for the purpose of targeted advertising.
Expiry: 6 Hours
Type: HTTP
These cookies are used for the purpose of targeted advertising.
Expiry: 1 Month
Type: HTTP
These cookies are used to gather website statistics, and track conversion rates.
Expiry: 1 Month
Type: HTTP
Aggregate analysis of website visitors
Expiry: 6 Months
Type: HTTP
This cookie is set by Facebook to deliver advertisements when they are on Facebook or a digital platform powered by Facebook advertising after visiting this website.
Expiry: 4 Months
Type: HTTP
Contains a unique browser and user ID, used for targeted advertising.
Expiry: 2 Months
Type: HTTP
Used by LinkedIn to track the use of embedded services.
Expiry: 1 Year
Type: HTTP
Used by LinkedIn for tracking the use of embedded services.
Expiry: 1 Day
Type: HTTP
Used by LinkedIn to track the use of embedded services.
Expiry: 6 Months
Type: HTTP
Use these cookies to assign a unique ID when users visit a website.
Expiry: 6 Months
Type: HTTP
These cookies are set by LinkedIn for advertising purposes, including: tracking visitors so that more relevant ads can be presented, allowing users to use the 'Apply with LinkedIn' or the 'Sign-in with LinkedIn' functions, collecting information about how visitors use the site, etc.
Expiry: 6 Months
Type: HTTP
Used to make a probabilistic match of a user's identity outside the Designated Countries
Expiry: 90 Days
Type: HTTP
Used to collect information for analytics purposes.
Expiry: 1 year
Type: HTTP
Used to store session ID for a users session to ensure that clicks from adverts on the Bing search engine are verified for reporting purposes and for personalisation
Expiry: 1 Day
Type: HTTP
Cookie declaration last updated on 24/03/2023 by Analytics Vidhya.
Cookies are small text files that can be used by websites to make a user's experience more efficient. The law states that we can store cookies on your device if they are strictly necessary for the operation of this site. For all other types of cookies, we need your permission. This site uses different types of cookies. Some cookies are placed by third-party services that appear on our pages. Learn more about who we are, how you can contact us, and how we process personal data in our Privacy Policy.
Edit
Resend OTP
Resend OTP in 45s