_edited_edited.png)
Database Management System (DBMS)
A database management system is being used to manage databases such as MYSQL, POSTGRE SQL, Oracle and Microsoft SQL Server. DBMS helps users to create, retrieve, update and delete data in a very efficient and well-structured manner. DBMS supports various data models such as relational, network and hierarchical models.
What is RDBMS
There are different types of DBMS. The most commonly used type is RDBMS(Relational Database Management System). As the name suggested it builds the relation in the database by storing the data in tabular format with rows and columns.SQL( Structured Query Language ) is being used by RDBMS to access and manage the database. Atomicity, Consistency, Isolation and Durability are the main properties of RDBMS. MYSQL, Oracle, POSTGRESQL, and Microsoft SQL servers are popular examples of RDBMS.
Data Types
SQL data types are a primary focus for relational database schema design. The data type is very crucial for data integrity, query performance, and efficient storage.
Different data types are
To manage and manipulate relational databases we use a standardized programming language which is called SQL ( structured query language). There are several operations can be performed on the stored data in a relational database such as inserting, updating, deleting and querying data. It is also used to create and modify tables, indexes and views.
What are SQL commands
SQL commands are like instructions to a table to perform specific operations perform various tasks like creating a table, adding data in the table, modifying data in the table, deleting data from a table, dropping a table, setting permission for users etc.
SQL commands are categorized into five categories.
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Data Query Language (DQL)
• Transaction Control Language (TCL)
Data Definition Language (DDL) – It is a subset of SQL commands used to define, alter, and delete database structures such as tables and schemas. Common DDL commands are CREATE, DROP, ALTER, TRUNCATE, and RENAME.
CREATE -It is used to create a new table and its columns in the database.
Syntax: CREATE TABLE table_ name ( Column1 datatype, Column 2 datatype,Column 3 datatype,……);
Example:
CREATE TABLE Persons (ID int, Last_Name varchar(255), FirstName varchar(255),price varchar(255), quantity varchar(255));
DROP - This command is used to drop an existing table from the database.
Syntax: DROP TABLE table_name;
Example: DROP TABLE Persons;
TRUNCATE- This command is used to delete the data inside the table.
Syntax: TRUNCATE TABLE table_name;
Example: TRUNCATE TABLE Persons;
ALTER- This command is used to change the table structure like to add a new column, deleting an existing column, modifying an existing column or renaming the existing column in a table.
Syntax: To add a column in the table
ALTER TABLE table _name ADD column_name datatype;
Example: ALTER TABLE Persons ADD nationality Varchar(255);
Syntax-To drop a column from a table.
ALTER TABLE table _name DROP column_name
Rename – This command is used to rename the table.
Syntax: Rename<OLD_TABLENAME>to <NEW_TABLENAME>;
Example: ALTER TABLE Persons RENAME COLUMN lastname TO family_name;
Data Manipulation Language (DML)- These commands are used to manipulate data in the database. Common DML commands are Insert, Update, Delete, and Select.
SELECT- This command is used to retrieve records from a table.
Syntax: SELECT column1,column2,… FROM table_name;
Example:
select inventory_id, film_id from inventory;
Some clauses are used with select commands to retrieve data from a table in the database. Such as
WHERE Clause: It is used with the SELECT command to filter data based on condition.
Syntax: SELECT column1,column2,… FROM table_name WHERE condition;
GROUP BY Clause: It is used with aggregate functions (SUM, to group according to one or more columns.
Syntax: SELECT column1,aggregate_function(column2) From table_name GROUP By column1;
ORDER BY Clause :It is used to sort the results in ascending or descending order.
Syntax: SELECT column,column2 FROM table_name ORDER BY column1[ASC/DSC], column2[ASC/DSC];
INSERT- This command is used to insert data into the table.
Syntax: INSERT INTO table_name(column1,column2,….)VALUES(values1,values2…);
Example; INSERT INTO Persons(ID,price)VALUES(12,120);
UPDATE: This command is used to update data in the table.
Syntax: UPDATE table_name SET column1=value1,column2=value2 where condition;
Example: UPDATE persons set the first name='Tom' where id=12;
DELETE: Delete records from the database table.
Syntax: DELETE FROM table-name where condition;
Data Control Language(DCL): It primarily interacts with the database system’s rights, permits and restrictions. Common DCL commands are GRANT and REVOKE.
GRANT -It grants database access to specific database objects.
Syntax: GRANT <obj_priv> ON <obj_name> To <username>;
Example: GRANT SELECT, UPDATE ON employees TO user_name;
REVOKE- Removes previously given grant from the user account to certain database objects.
Syntax: REVOKE <obj_priv> ON <obj_name> FROM <username>;
Example: REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
Transaction Control Language(TCL)- Transaction has two results either success or failure. It ensures data consistency. The most common TCL commands are Commit and Rollback.
Commit: This command is used to save all transactions.
Syntax: COMMIT;
Rollback: The rollback command is used to revert the changes that are made from the transaction and can be used if the commit is not executed before.
Syntax: ROLLBACK;
Conclusion:
We use RDBMDS and SQL for managing data. This programming language is essential for data scientists and data engineers to retrieve data from databases. No coding experience is required and it is easy to use due to its natural language-like syntax. Data can be retrieved quickly and efficiently.
SQL commands (DDL, DML, DCL, DQL, TCL) are the fundamentals for effective database management. Understanding each of these commands will enhance our database skills.
I have tried to give a brief idea of important SQL commands with categories, syntax and examples. In today's date, AI and SQL are becoming more integrated's and data analysis and management capabilities can be enhanced using AI tools.
I hope this blog will help you for better understanding of basic SQL queries. Thank you.
Reference: