מרץ25
Written by:
ronen ariely
25/03/2021 21:04 
Introduction to query types
Structured Query Language (SQL) commands can be categorize into several groups, according to the impact on the database objects and the way we use the data. The most common categories include:
- DCL: Data Control Language
- TCL: Transaction Control Language
- DDL: Data Definition Language
- DML: Data Manipulation Language
- DQL: Data Query Language
Name | Description | Queries |
| DCL | Data Control Language: dealing with permissions | GRAND, REVOKE
|
| TCL | Transaction Control Language: Control the execution of transaction | COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
|
| DDL | Data Definition Language: Define the database schema | CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME
|
| DML | Data Manipulation Language: Changing data | INSERT, UPDATE, DELETE
|
| DQL | Data Query Language: Getting data | SELECT |
how this is related to the way you should raise questions
When you raise a question whether it is in a community open forum or a payment support system, the basic information which you should always provide is how to reproduce your scenario.
Speaking about questions related to tables in databases, you will probably get request to provide the DDL+DML. So, what these terms actually means in the scope of the question and what exactly we are required to do when we are asked to provide DDL+DML for example?
As explained above the term DDL - Data Definition Language, related to queries, which define the database structure. In order to reproduce your scenario you should always provide the queries to create all the objects which are relevant to the question, like queries to create the tables, indexes, keys, constraints and so on.
When your question related performance or getting data from tables, then the people that participate in the discussion must have some data for the sake of the discussion about your expected result SET. This means that you must provide queries to INSERT some sample data to the demo tables and this is the meaning of the request to provide DML - Data Manipulation Language, queries that change the data (INSERT/UPDATE/DELETE).
NOT a replacement for DDL+DML!
Describe table with words
Option 1: My table includes fields: Col 1, col2 and col3.
Option 2: This is my table:
A description of a table will never be full and might even include wrong and/or confusing information! Putting aside the fact the tables does not have fields but rows and columns, the above information does not provide information like what are the data type of the column, what are the indexes of the table, and so on
In fact, the more elements there are and the more connections/relations there are between the elements (e.g. keys) the explanations usually becomes cumbersome and unclear. Moreover, as mentioned, anyone who comes to help other and will try to solve the problem will have to perform unnecessary work of writing the queries in order to build the table in his database.
So better you do it once and provide the DDL+DML.
Using Images to describe tables
This is probably the worse option!
What can we do with images?!? We cannot query the image! We cannot even use it to build queries as we can when we have text. Apart of giving a desplay this is useless and in case if we will build the table in our database, then we will be able to see the table in our application.
DO not imposes unnecessary work on whoever comes to help you!
You should provide the DDL+DML!
So in short, whether you raise a question in a community open forum or you open a support ticket in a payment support system, you should always provide:
- Queries to CREATE your table(s) including relevant objects like indexes and constraints
- Queries to INSERT sample data.
- The desired result given the sample, as text or image of excel for example.
- A short description of the business rules, and how you got 1-2 of the results
- Which version of SQL Server you are using (this will help to fit the query to your version).
Using this information, anyone that read the question can reproduce the scenario and provide a solution which was tested.
Read More