TYPES OF COMMAND IN SQL | LEARN SQL

Hey, in previous blogs we had learnt about the introduction, history, installation,  and creation updation, and deletion of tables or a database today we are continuing with the types of command

TYPES OF COMMAND IN SQL

1)Data Definition Language (DDL): Data Definition Language (DDL) is a set of SQL commands used to define the database structure or schema. DDL statements are used to create, modify, or delete database objects such as tables, indexes, views, sequences, and constraints.

Here are 2 common DDL commands we have learned to use in SQL:

• CREATE:  This command is used to create a new database object such as a table, view, index, or sequence.

• DROP: This command is used to delete an existing database object such as a table, view, index, or sequence.

2)Data Manipulation Language (DML): Data Manipulation Language (DML) is a set of SQL commands used to manipulate the data stored in a database. DML commands are used to insert, update, delete, and retrieve data from tables. Here are some common DML commands used in SQL:

• SELECT: Data can be retrieved from one or more tables using the SELECT command.You can specify the columns and rows to be returned using various clauses like WHERE, ORDER BY, GROUP BY, etc.

• INSERT: This command is used to insert new rows of data into a table. You can specify the columns and values to be inserted.

3)Data Control Language (DCL): Data Control Language (DCL) is a set of SQL commands used to control access to database objects. DCL commands are used to grant or revoke privileges to the users or roles.

4)Transaction Control Language (TCL): Transaction Control Language (TCL) is a set of SQL commands used to manage the transactions in the database. TCL commands are used to ensure the consistency and integrity of the data by controlling the transactions.

5)Session Control Commands: SQL does not have a set of specific "Session Control Commands" (SCC) as a standard part of its syntax. However, different database management systems (DBMS) may provide their own proprietary SCCs to manage database sessions. These SCCs can help control how users interact with the database, track usage and resource allocation, and manage the database connections and sessions.

Note*:- We hasn’t studied commands about Data Control Language (DCL) , Transaction Control Language (TCL), or Session Control Commands we’ll be studying it in further Blogs.

SELECT COMMAND IN SQL

Data can be obtained from one or more tables using the SELECT statement in SQL. In a SELECT statement, the following fundamental syntax is used:

SELECT column1, column2, ...

FROM table_name

WHERE COLUMN_NAME = value;

FOR EXAMPLE:-

OUTPUT:-

SELECT IN SQL

In SQL, the WHERE clause is used in a SELECT, UPDATE, or DELETE statement to filter the rows returned based on a specified condition. The basic syntax of a WHERE clause is as follows:

SELECT column1, column2, ...

FROM table_name

WHERE some_column = some_value;

FOR EXAMPLE:-

It will show students with stream commerce

KEYS IN SQL

In SQL, keys are used to uniquely identify rows in a table. There are three types of keys commonly used in SQL: primary keys, foreign keys, and unique keys.

• Primary Key: A column or group of columns known as a primary key uniquely identifies each row in a table.
  It is used to enforce entity integrity, which means that each row in the table represents a unique entity. Primary keys must be unique and cannot contain null values.

• Foreign Key: A column or group of columns that make reference to the primary key of another table are known as foreign keys. It is used to enforce referential integrity, which means that the values in the foreign key column(s) must match the values in the primary key column(s) of the referenced table. Foreign keys are used to establish relationships between tables.

• Unique Key: A unique key is a column or set of columns that uniquely identifies each row in a table, similar to a primary key. However, unlike a primary key, a unique key can contain null values. Unique keys are used to enforce entity integrity, just like primary keys.

THERE IS NO SYNTX FOR KEYS THESE WILL BE INSERTED AT THE TIME OF TABLE CREATION.

IDENTIFIER

The names of things like databases, tables, or columns are where identifiers are most frequently employed.

Rules for identifiers:-

* begin with a Unicode letter or an underscore (_). Letters, underscores, numerals (0–9), or dollar signs ($) are all acceptable as additional characters.

* keyword is not accepted as an identifier.

KEYWORDS

These are reserved words and can have specific meanings in statements to perform various operations in the database.

Views in SQL:

Views are database objects with no value; that's why it does not store physically. Views are a great tool for hiding or abstracting complex queries because of this characteristic.

Although it does not actually have rows and columns, it resembles one. Consequently, it can be said that its contents are based on the base table. Although it doesn't have any data of its own, it operates similarly to the base table. Its name is always unique, like tables. The views differ from tables as they are definitions that are created on top of other tables (or views). If any changes occur in the underlying table, the same changes are reflected in the views also.

Uses of views

Implementing the security mechanism is the main function of views in SQL Server. Specific columns and rows in tables are hidden from users by this. It only shows the data returned by the query that was declared when the view was created. The user is never made aware of the remaining details.

Types of views

1)User-Defined Views: Users define these views to meet their specific requirements.

2)System-Defined Views: System-defined views are pre-built, pre-existing views that are saved in SQL Server and include Tempdb, Master, and temp.

Syntax of creating views in sql:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

FOR EXAMPLE:-

OUTPUT:-

Rename views
SP_RENAME View_Old_Name, View_New_Name

Drop Views in SQL Server

DROP VIEW [IF EXISTS] schema_name.view_name;

DROP VIEW Student

so that's it for today guys, follow us to learn more about SQL topics.....

happy learning.....