STRUCTURED QUERY LANGUAGE

SQL is a query processing language used for dealing with data in relational databases. According
to the client-server model, a database server is a computer program that provides several services
for our database to other programs or computers

SQL commands are instructions. It is used to communicate with the database. It is also used to
perform specific tasks, functions, and queries of data.

SQL can perform various tasks like create a table, add data to tables, drop the table, modify the
table, set permission for users.

SQL commands are grouped into below major categories depending on their functionality

1) Data Definition Language (DDL)

2) Data Manipulation Language (DML)
3) Transaction Control Language (TCL)
4) Data Control Language (DCL)
5) Data Query Language (DQL)

Data Definition Language (DDL) :-DDL commands are used to create, alter, drop, rename of a
database objects like table, view, sequence, index etc .These commands works on structure.
Below are listed command under DDL
1) CREATE
2) ALTER
3) DROP
4) RENAME
5) TRUNCATE
Data Manipulation Language (DML):-DML commands work on records in a table. These are
basic operations we perform on data such as inserting new records, deleting unnecessary records,
and updating existing records.
Below are listed command under DDL
1) UPDATE
2) DELETE
 3) INSERT
Transaction Control Language (TCL):- The commands of SQL that are used to control the
transactions made against the database.
Below are listed command under DDL
1) COMMIT
2) ROLLBACK
3) SAVEPOINT
Data Control Language (DCL):-These commands of SQL are used to give the permission or
revoke the permission on System/User's objects to user/role in the database
Below are listed command under DCL
1) GRANT
2) REVOKE
Data Query Language (DCL):-This command is used to select the data from database tables,
view, synonyms and sequence etc.
Below are listed command under DDL
1) SELECT

SQL Data Type

Data types define the ways to identify the type of data and their associated operations.
Example: -
i. CHAR (fixed length character value between 1 and 32,767 characters)
ii. VARCHAR (variable length character value between 1 and 32,767 characters)
iii. NUMBER (fixed-decimal, floating-decimal or integer values)
iv. BOOLEAN (logical data type for TRUE FALSE or NULL values)
v. DATE (stores date and time information)
vi. LONG (character data of variable length)
SQL COMMANDS

1. CREATE DATABASE

Used to create a new database;

Syntax: CREATE DATABASE dbname;

Example: CREATE DATABASE student_database;

2. USE

To create any database object eg a table, one has to select to which database will it be stored.
USE command is used to select a database.

Syntax: USE dbname;

Example USE student_database;

3. DROP DATABASE

Sometimes a database is not relevant or obsolete on our server instance. In that case, we can
delete or remove them from the server. When we remove the database, it will also permanently
delete all the tables, indexes, and constraints. Therefore, we should have to be very careful while
dropping the database because we will lose all the data available in the database.

Syntax: DROP DATABASE dbname;

Example: DROP DATABASE student_database;

If the database is not available on the server instance, SQL Server will throw an error message.
To avoid this error, one can first check whether the database exists before dropping eg

DROP DATABASE IF EXISTS student_database;

4. CREATE TABLE

A table enables the user to store data and display information in the database. It arranges the data
in rows and columns. It can have several rows and columns, and each column has a data type
associated with it that specifies the type of data it can store, such as numbers, strings, or temporal
data.

Syntax:

CREATE TABLE table_name (

Column1 datatype constraint
Column2 datatype constraint
…
ColumnN datatype constraint);
Example:

5. DELETE OR DROP TABLE

SQL Server allows DBA to remove a table, which is not relevant or obsolete in our database.
When we remove a table, the complete data and the whole structure or definition are deleted
permanently from the table. Therefore, we should have to be very careful while dropping a table
from the specified database. If the SQL Server does not find the target database table, it will
throw an error message.

Syntax: DROP TABLE IF EXISTS tablename;

6. INSERT

INSERT statement in SQL Server is used for adding records in a table within the specified
database.

SQL Server performs insertion operation in two ways within a single query:

o Add data in a single row

Syntax: INSERT INTO table_name ( column 1, column 2 ….. column N) VALUES ( value1,
value2, … value N);

o Add data in multiple rows

Syntax: INSERT INTO table_name

VALUES (value1, value2, … value N) (value1, value2, … value N) (value1, value2, … value
N);

7. UPDATE
UPDATE statement in SQL Server is a DML statement used to update or modify the already
existing records into a table or view. This statement is required in the real-life scenario where our
data stored in the table changes regularly. The UPDATE query is always recommended to use
with the SET and WHERE clause.

Syntax

UPDATE table_name
SET column1 = new_value1,
column2 = new_value2, ...
[WHERE Clause]

Example

UPDATE Student _table

SET Marks = 492
WHERE Name = 'Alan Simmons';

8. DELETE Data
In SQL Server database, DELETE statement is used to delete records from the table.
Syntax: DELETE FROM tablename WHERE condition;
Example: DELETE FROM student WHERE fname = 'jane';

9. ALTER TABLE
ALTER command in SQL Server is used to make modifications in an existing table. These
alterations can be adding a column, deleting a column, changing the size, modifying the data
type, adding or removing indexes, and adding or deleting constraints in a table definition.

i. Add New Column

A database developer several times need to add a new column to the existing table. They can do
this by using the ALTER TABLE ADD COLUMN command. It will always add the new
column at the last position in the table. The syntax to add a new column is given below:

ALTER TABLE table_name ADD column_name data_type column_constraints;

Example

ALTER TABLE student ADD Id int UNIQUE;

ii. Delete Column

We can also use the ALTER command to remove single or multiple columns from the table.
SQL Server provides the ALTER TABLE DROP COLUMN statement to delete the existing
column from the table

Syntax: ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE Student DROP COLUMN Phone_number;

If we want to delete more than one column, we can use the following syntax:
ALTER TABLE table_name
DROP COLUMN column_name1, DROP COLUMN column_name2...

iii. Modify Column Data Type

We can also use the ALTER command to change the column's data type into the specified table.
SQL Server provides the ALTER TABLE ALTER COLUMN statement to modify the column
data type. We can do this by using the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name new_data_type(size);

In this syntax, we will first specify the table name in which we want to make the modification.
Then, we will specify the column name that we are going to modify and finally define its new
data type with size.

Example:

ALTER TABLE [Link] ALTER COLUMN fname varchar(50);

View in SQL Server

A view is a database object that has no values. It is a virtual table, which is created according
to the result set of an SQL query. However, it looks similar to an actual table containing rows
and columns. Therefore, we can say that its contents are based on the base table. It is operated
similarly to the base table but does not contain any data of its own. 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 reflected in the
views also.

This diagram illustrates the concept of a view that included columns from more than one table.
Here we have two tables named 'Table A' and 'Table B,' and by using a SQL statement, a view is
created containing data from both tables.

Uses of views

The primary use of view in SQL Server is to implement the security mechanism. It prevents
users from seeing specific columns and rows from tables. It only shows the data returned by the
query that was declared when the view was created. The rest of the information is completely
hidden from the end-user.

TYPES OF VIEWS

a. User-Defined Views

Users define these views to meet their specific requirements. It can also divide into two type:
simple view, complex view. The simple view is based on the single base table without using any
complex queries. The complex view is based on more than one table along with group by clause,
order by clause, and join conditions.

b. System-Defined Views

System-defined views are predefined and existing views stored in SQL Server, such as Tempdb,
Master, and temp.

We can create a new view by using the CREATE VIEW and SELECT statement. SELECT
statements are used to take data from the source table to make a VIEW.

The following syntax is used to create a view in SQL Server:

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

CREATE VIEW course_enrolled

AS
SELECT first_name, last_name, course, amount_paid
FROM Student;

We can verify the view data using the SELECT statement as below:

SELECT * FROM course_enrolled;