It has become essential for anyone wanting to work in this field to learn about databases as basic tasks about data such as storage, and retrieval, which are required in data science are done through databases.
Databases work on the query system which is generally based on a query language, and the most well-known language is SQL.
Learning SQL comprises learning smaller components within SQL that help you work on structured data.
A beginner would want a list of SQL Commands that gets the learner some understanding of the range of commands SQL provides and as you keep on studying you will find that SQL has many commands that are generally used to get the different tasks done.
These commands broadly come under 5 subsidiaries which are
1. Data query language DQL
2. Data definition language DDL
3. Data control language DCL
4. Data manipulation language DML
5. Transaction control language TCL
Data query language DQL
It comprises mainly the "SELECT" command, which is widely regarded as the most used statement in SQL. It is used to access one or more database tables or views.
EXAMPLE:
SELECT entry_name AS first_name
FROM namelisting
WHERE entry_name LIKE %AvidLearner% AND age BETWEEN 18 AND 28;In this example, we are selecting the column entry_name with an alias first_name from table namelisting where the said column is being searched based on the parameters "entry_name and age.
The Where Clause: in the third line we can see that a condition is added to the search.
e.g. name specific condition and age restraint in the above pic.
Important note:
by using the code " SELECT * " we are selecting all the fields (fields are the SQL terminology meaning columns).
Data definition language DDL
As in the name it is used to "define" data structures with the function of modification.
A subset of SQL, DDL commands are used to create, modify, add, and remove records or fields, or tables in a database.
The main commands that are mostly used are:
1. CREATE
Create command is used for the creation of databases or tables in existing databases. It is the initial command used when one needs a DB or table to store data.
SYNTAX:
This is the syntactical arrangement for creating tables in SQL.
CREATE TABLE TABLENAME(COL_1,COL_2,…)EXAMPLE:
CREATE DATABASE learnerdb;2. ALTER
Alter command can add, modify, drop, or rename a field(column) or rename a whole table. It is the most used command in "altering" a table structure.
SYNTAX:
ALTER TABLE table_name
ADD name_of_column column-definition;EXAMPLE:
ALTER TABLE learner_salary
ADD initial_salary varchar(50)3. DROP
It is used to delete the existing DB objects and structures. It deletes a view of a table or a whole table depending upon the syntax. Once a table is dropped, all records and fields are deleted including the table from your database.
SYNTAX:
DROP DATABASE name_of_database;DROP TABLE name_of_table;DROP OBJECT name_of_object;EXAMPLE:
DROP TABLE learnerdb;4. TRUNCATE
It is very similar to the drop command as it also deletes the data inside a table, the differences arise from the fact that the table itself isn't deleted in the truncate command.
SYNTAX:
TRUNCATE TABLE name_of_table;
EXAMPLE:
TRUNCATE TABLE collegeIntegral;
Data control language DCL
Privileges are of the following types:
a. System privileges: Session, Table-related Permission
b. Object privileges: Command Permission
1. GRANT:
Grant is used when a specific user is to be given a specific access privilege to a part (view) or the whole database.
SYNTAX:
GRANT privileges ON object TO user;EXAMPLE:
GRANT ALL ON learnerdb TO CollegeIntegral;2. REVOKE:
Revoke is the opposite of grant as the name suggests. Revoke command removes user access privileges of a specific user to a part of a table(view), a part of a database, or the whole database.
SYNTAX:
REVOKE privileges ON object FROM user;EXAMPLE:
REVOKE ALL ON learnerdb FROM randomblog;
Data manipulation language DML
All the updates, access, modification, deletion, and record manipulations come under the data manipulation language. The commands INSERT, UPDATE, DELETE are part of the data manipulation sub-sphere. The SELECT statement also come under its influence partially but it is generally accepted that the select statement is considered a part of DQL.
1. INSERT:
INSERT data into tables in a database, INSERT also can be used to add records and fields to a database. It can be used by the column name insertion method or by the value insertion method.
SYNTAX:
First syntax is for value insertion method & second insert syntax is column_name+value entry method.
INSERT INTO name_of_table
VALUES(v1, v2, v3, v4, v5, …);
INSERT INTO name_of_table(c1, c2, c3, c4, c5, . . .)
VALUES(v1, v2, v3, v4, v5, …);EXAMPLE:
INSERT INTO learnerdb (learnerID, learner_vehicle,
learner_contact, learner_whereabout)
VALUES (Levy C.I.', 'rv', '34', 'NY');
Modify Data in tables or you can say, Update some records or fields or tables in a database.
SYNTAX:
UPDATE name_of_table
SET c1= v1, c2 = v2, …
WHERE conditions_applied_here;
UPDATE learnerdb
SET learner_vehicle = 'rv'
WHERE learnerID = '1';
DELETE command helps in deleting one or more records & thus helping in removing obsolete data from database tables.
SYNTAX:
DELETE FROM name_of_table
WHERE condition;EXAMPLE:
DELETE learnerdb
WHERE learnerID = '0';
Transaction control language TCL
Transaction Control Language is under work when transaction-based queries are applied to a database. Clustered SQL statements based on the logic of work can be considered a transaction. Since a Transaction changes the state of data commands like COMMIT, ROLLBACK, SAVEPOINT are very useful to minimize errors.
1. COMMIT:
It saves the transaction made in a database, meaning that whatever changes eg. insert, update, delete are applied to the database all are saved upon the usage of the COMMIT command and it is irreversible.
SYNTAX:
COMMIT;EXAMPLE:
UPDATE learnerdb SET name = 'learner' WHERE name = 'learned';
COMMIT;2. SAVEPOINT:
SAVEPOINT as the name suggests creates a "save point" in case a rollback is required. It stores the current values, records, and fields in a database as they were at the time of creating a save point.
Should a rollback is called, save point is used to update the values as they were.
A rollback can be called for multi-lateral reasons, so it is best practice to create save points, it always helps if the data isn't correct and needs comparison or a transaction failed and we need to return to the previous scenario.
SYNTAX:
SAVEPOINT name_of_save_point;EXAMPLE:
UPDATE learnerdb SET name = 'learner' WHERE name = 'learned';
SAVEPOINT L1;
UPDATE learnerdb SET name = 'learned' WHERE name = 'learner';
ROLLBACK TO L1;
It is the undo command of SQL, it undoes the insert, update or delete transactions applied within a database table and restores preceding records.
SYNTAX:
ROLLBACK TO name_of_save_point;EXAMPLE:
UPDATE learnerdb SET name = 'learned' WHERE name = 'learner';
ROLLBACK;