Database Primer - SQL
I'm on a roll with these database primers! Today I want to provide a broadstroke view of some of the functioanlity and purpose of SQL, and Data Definition Language.
Within SQL, there are languages that allow you to create, manipulate and manage data within databases. Data Definition Language, or DDL, is a language tool within SQL that allows the creation of objects within a database such as tables, indexes, and views as well as the access rights to those structures (Coronel, 2019, p. 244). Another SQL language set is Data Manipulation Language, or DML. DML allows you to manipulate the data within database tables. These portions include commands that insert, update, or delete structures within databases (Coronel, 2019, pp. 244-247). These structures include indexes, tables, and the schemas themselves. To properly use a SQL Database, a user must be aware of the different languages available within SQL such as DDL, DML and DCL.
A rule of thumb when trying to differentiate between DDL, DML, and DCL is to identify what their purpose within a database is. DML’s purpose is to focus on the database schema. This contrasts with the functions of the SQL command languages DDL and DCL. DDL is used to manipulate the data within the database tables themselves, with commands such as CREATE, DROP, TRUNCATE, COMMENT, and ALTER (Geeks For Geeks, 2024). Whereas DCL is concerned with the management rights of who can access said data, with commands such as GRANT and REVOKE. Such commands are what allows database creation, and day-to-day operations at scale for businesses.
The SQL languages of DDL, DCL, and DML are used with SQL commands. Much like any programming language, there are specific commands that trigger actions. With Data Manipulation Language the more common commands used include INSERT, UPDATE, and DELETE. INSERT allows a user to add new rows to a table. An example would be if we had an Employees table that held columns for employeeID, employee_FName, employee_LName. We would do the following to add a new employee to this table:
INSERT INTO Table_Employees (employeeID, employee_FName, employee_LName, age)
VALUES (144, ‘Jeremiah’, ‘Johnson’, 38);
UPDATE is another commonly used command with DML. Rather than adding new rows, or deleting existing rows, UPDATE focuses on altering the data within an existing row. Update can change a singular value in a record, and it can also change multiple records within one command. An example of the UPDATE command would be if we wanted to change the age of our employee Jeremiah Johnson. In this example, the usage of WHERE is essential to identify which record is updated, otherwise all of the records in the table will update rather than just employeeID 144 for Jeremiah Johnson:
UPDATE Table_Employees
SET age = 39
WHERE employeeID = 144;
The last commonly used command within DML is DELETE. Whereas INSERT deals with adding records, and UPDATE handles the altering of said records, DELETE allows you to remove records. In the previous example we added, and then updated the data belonging to employeeID 144. However, if the employee had resigned and we no longer had need to keep their record, we would need to remove their record from the database. You cannot update the rows to clear the data, as this would result in NULL data that would prevent data normalization. Instead, you must DELETE. An example of this would be:
DELETE FROM Table_Employees
However, there may be circumstances where a user might need to delete multiple records at once. In this example, let’s say we need to delete hundreds of records as the data for a certain range has reached data retention policy limitations. Rather than using the command one at a time, you could affix a criteria to identify and delete swathes of applicable records. An example might be if we had a column named employee_Retired that identified if an employee had retired or not. If we wanted to remove all employees that had retired after 1200 days, we could do:
DELETE FROM Table_Employees WHERE retired = retired > 1199;
Within SQL, there are languages that allow you to create, manipulate and manage data within databases. Data Definition Language, or DDL, is a language tool within SQL that allows the creation of objects within a database such as tables, indexes, and views as well as the access rights to those structures (Coronel, 2019, p. 244). Another SQL language set is Data Manipulation Language, or DML. DML allows you to manipulate the data within database tables. These portions include commands that insert, update, or delete structures within databases (Coronel, 2019, pp. 244-247). These structures include indexes, tables, and the schemas themselves. To properly use a SQL Database, a user must be aware of the different languages available within SQL such as DDL, DML and DCL.
A rule of thumb when trying to differentiate between DDL, DML, and DCL is to identify what their purpose within a database is. DML’s purpose is to focus on the database schema. This contrasts with the functions of the SQL command languages DDL and DCL. DDL is used to manipulate the data within the database tables themselves, with commands such as CREATE, DROP, TRUNCATE, COMMENT, and ALTER (Geeks For Geeks, 2024). Whereas DCL is concerned with the management rights of who can access said data, with commands such as GRANT and REVOKE. Such commands are what allows database creation, and day-to-day operations at scale for businesses.
The SQL languages of DDL, DCL, and DML are used with SQL commands. Much like any programming language, there are specific commands that trigger actions. With Data Manipulation Language the more common commands used include INSERT, UPDATE, and DELETE. INSERT allows a user to add new rows to a table. An example would be if we had an Employees table that held columns for employeeID, employee_FName, employee_LName. We would do the following to add a new employee to this table:
INSERT INTO Table_Employees (employeeID, employee_FName, employee_LName, age)
VALUES (144, ‘Jeremiah’, ‘Johnson’, 38);
UPDATE is another commonly used command with DML. Rather than adding new rows, or deleting existing rows, UPDATE focuses on altering the data within an existing row. Update can change a singular value in a record, and it can also change multiple records within one command. An example of the UPDATE command would be if we wanted to change the age of our employee Jeremiah Johnson. In this example, the usage of WHERE is essential to identify which record is updated, otherwise all of the records in the table will update rather than just employeeID 144 for Jeremiah Johnson:
UPDATE Table_Employees
SET age = 39
WHERE employeeID = 144;
The last commonly used command within DML is DELETE. Whereas INSERT deals with adding records, and UPDATE handles the altering of said records, DELETE allows you to remove records. In the previous example we added, and then updated the data belonging to employeeID 144. However, if the employee had resigned and we no longer had need to keep their record, we would need to remove their record from the database. You cannot update the rows to clear the data, as this would result in NULL data that would prevent data normalization. Instead, you must DELETE. An example of this would be:
DELETE FROM Table_Employees
However, there may be circumstances where a user might need to delete multiple records at once. In this example, let’s say we need to delete hundreds of records as the data for a certain range has reached data retention policy limitations. Rather than using the command one at a time, you could affix a criteria to identify and delete swathes of applicable records. An example might be if we had a column named employee_Retired that identified if an employee had retired or not. If we wanted to remove all employees that had retired after 1200 days, we could do:
DELETE FROM Table_Employees WHERE retired = retired > 1199;
Comments
Post a Comment