SQL is one of the most important for maintaining the database. In DBMS basic introduction to SQL, tell the basics about SQL like what is SQL, Why it is used, its importance, etc.
In order to read, and fetch, the data in the specific format from the database. It is widely used by business people in order to check the progress by analysing the proper data, and these data are being fetched from the database by writing queries in the form of SQL.
Let’s read about DBMS basic introduction to SQL and learn them.
What is SQL?
SQL or Structured Query Language is a programming language that is designed for the purpose to manipulate and manage the relational database. SQL provides a very interactive and unique way to connect and interact with databases. With the help of SQL, users can create, retrieve, delete and update the data stored in the structured format.
Data is stored in the form of rows and columns in tables in the relational database. SQL gives a set of commands called queries, that help users to perform a lot of operations with the data within these tables. In DBMS basic introduction to SQL, Knowing what SQL is a very important thing for data engineers.
In database management systems like PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database and many others, SQL in DBMS is widely used in these. It is a powerful tool which allows users to interact and manipulate the data in the database.
Domain Types in SQL
Domain Types in SQL tell us what type of value a particular attribute in the table can have. Basically, it defines the data types or the possible types of values the attribute can have. Many different types of data are supported in DBMS basic introduction to SQL that the attribute can have.
Here are some of the common Domain Types in SQL.
- Numeric Types
- Character Types
- Date and Time Types
- Binary Types
- Boolean Types
- User-Defined Types
1) Numeric Types
In DBMS basic introduction to SQL, the Numerical type of data comes under this Domain Type in SQL. Some of the examples are:
- INTEGER or INT: It is for representing whole numbers. It can be a positive or negative value.
- NUMERIC or DECIMAL: It is used to represent the numbers with decimal precision.
- REAL or FLOAT: It is used to represent decimal values, also known as floating point values. It holds values with approximate precision.
2) Character Types
In DBMS basic introduction to SQL, the Textual type of data comes under this Domain Type in SQL. Some of the examples are:
- CHARACTER or CHAR: It is used to represent the string type of values that are of fixed length.
- VARCHAR or CHARACTER VARYING: It is used to represent the string type of values that are of variable length.
- TEXT: It is used to represent large data. It is like a large block of textual data.
3) Date and Time Types
In DBMS basic introduction to SQL, the Data and Time types of values come under this Domain Type in SQL. Some of the examples are:
- DATE: It is used to represent only Dates (year, month, day).
- TIME: It is used to represent only Time (hours, minutes, seconds).
- TIMESTAMP: It is used to represent the combination of both Data and Time.
4) Binary Types
In DBMS basic introduction to SQL, Binary types of data such as files or images come under this Domain Type in SQL. Some of the examples are:
- BINARY LARGE OBJECT or BLOB: It is used to represent a large block of binary data. It can be either images or files.
- VARBINARY or BINARY VARYING: It is used to represent a large block of binary data of varying lengths.
5) Boolean Types
In DBMS basic introduction to SQL, the Boolean values such as True or False come under this Domain Type in SQL.
6) User-Defined Types
In DBMS basic introduction to SQL, the User can define their own domain based on the requirements. It is provided by some of the database systems.
These are just some of the examples of the Domain Types. Meanwhile, it varies from Database system to system, and what database is being used. Some database provides additional domain types beyond those that are listed here.
Basic SQL Query Structure
Basic SQL structure talks about the clauses through which users can manipulate and retrieve the data. Basically, what data is to be retrieved on what condition?
DBMS basic introduction to SQL, Let’s see some of the basic SQL Query Structures.
The basic clauses in DBMS basic introduction to SQL are:
- SELECT: This clause specifies what all columns need to be retrieved from the tables. It basically tells what all columns or attributes will be returned by the query.
- FROM: This clause is used to specify the table name from which the data needs to be retrieved. Basically to identify the source from where the data needs to be retrieved.
- WHERE: This clause is used to specify the filters or the conditions. It is the optional clause. It makes sure that whatever the data is being fetched should meet the conditions that are given in this where clause.
- GROUP BY: This clause is used to group the data based on one or more columns. It is basically used with aggregate functions such as SUM, COUNT, AVG, etc.
- HAVING: This clause is used to filter the grouped data based on certain conditions provided. It is similar to the where clause, the only condition is that it is used with the grouped data.
- ORDER BY: This clause is used to sort the data based on certain columns or attributes. It can be either in ascending or descending order.
- LIMIT: This clause is used to limit the number of rows returned by the query.
Know more about Advanced SQL in DBMS
Let’s see one common example in DBMS basic introduction to SQL to implement these clauses.
SELECT col1, col2, col3 …
FROM table_name
WHERE {condition}
GROUP BY col1, col2, col3 …
HAVING {condition}
ORDER BY col1, col2, col3 …
LIMIT NumberOfRows;
Constraints in SQL
Constraints in SQL tell about the various rules and conditions that are needed to be maintained and followed by data that are being stored in the tables in the databases. In DBMS basic introduction to SQL maintains the data integrity and also enforces the business rules. It also defines the restrictions and limitations on the data that can be stored or modified. It also maintains the consistency, accuracy and validity of the data in the database.
Let’s see some of the constraints in SQL that need to be applied.
- Primary Key Constraint: Primary key is used to uniquely identify each row in the table. It ensures each row is unique, and doesn’t allow NULL values.
- Foreign Key Constraint: This constraint is used to specify the relationship between 2 tables by referencing the primary key of the table as the foreign key of another table. It is used to ensure the referential integrity.
- Unique Constraint: This Constraint ensures that the values in a particular column or in the group of columns are unique, no duplicates are allowed. Unique Constraints allow the NULL values, unlike the primary key constraints.
- Check Constraints: Check Constraint specifies that a particular type of value or in a particular condition, can be stored in particular columns. It allows users to set a particular condition that the data should meet.
- Not Null Constraints: Not Null Constraints specifies that the particular column cannot have null values. It ensures that the particular columns should always have some values, it should not be null or empty.
- Default Constraints: This constraint specifies some default value for a column which means if a new entry is made, and no value is provided for that column, it will fill that place with the default value provided.
Read more about this in Relational Model in DBMS
In DBMS basic introduction to SQL, While Creating or altering the table, the constraints can be defined at that time only. Constraints play an important and crucial role in maintaining the integrity of the data within the database.
DBMS basic introduction to SQL, from this article, users can learn about the basic introduction to SQL, and make their knowledge up in terms of the database.
