DBMS Language

A Database Language is used to maintain, monitor, organize, and access electronic databases and their Database Management Systems.

Database languages allow users and developers to perform different database-related tasks such as:

• Controlling access to data
• Defining data
• Updating data
• Searching for information
• Managing database contents

Database languages are also known as Query Languages or Data Query Languages.

Some popular database languages mentioned in the notes are:

• SQL
• XQuery
• Oracle
• GraphQL

Structured Query Language (SQL)

SQL stands for Structured Query Language.

It is one of the most well-known database languages.

According to the provided notes, SQL is an ISO and ANSI standard language used with relational database management systems such as:

• MySQL
• MS SQL Server
• Oracle
• MS Access

SQL allows users to access and manipulate databases.

Using SQL, users can perform operations such as:

• Create data
• Read data
• Update data
• Delete data

Advantages of SQL

The major advantages of SQL include:

1. Fast and efficient query processing.
2. No need for extensive coding skills.
3. Large user community.
4. Data integrity.
5. Scalability.
6. Standardization.
7. Flexibility.
8. Consistency.
9. Data durability.

Disadvantages of SQL

Some disadvantages of SQL include:

1. Complex interface.
2. Higher cost.
3. Partial control.

Types of SQL Commands

SQL commands are instructions used to communicate with a database.

The major categories discussed in the notes are:

1. Data Definition Language
2. Data Manipulation Language
3. Data Control Language

1. Data Definition Language

DDL stands for Data Definition Language.

DDL is used to create and modify the structure of database objects.

It can be used for operations such as:

• Creating tables
• Deleting tables
• Altering tables
• Creating views
• Creating schemas
• Creating indexes

According to the notes, DDL commands are auto-committed, which means changes are permanently saved in the database.

Common DDL commands include:

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

CREATE Command

The CREATE command is used to create a new database object such as a table or database.

Syntax for Creating a Table

CREATE TABLE <table-name>(set of columns with data-types);

Syntax for Creating a Database

CREATE DATABASE <database-name>;

ALTER Command

The ALTER command is used to modify or change the structure of an existing database object.

Syntax

ALTER TABLE <table-name>

ADD

<column-name> data-type(length of column);

DROP Command

The DROP command is used to delete a table definition and its contents from the database.

Syntax

DROP TABLE <table-name>;

TRUNCATE Command

The TRUNCATE command is used to delete records from a table and free the space occupied by them.

Syntax

TRUNCATE TABLE <table-name>;

RENAME Command

The RENAME command is used to change the name of an existing table.

Syntax

RENAME <old table name> TO <new table name>;

2. Data Manipulation Language

DML stands for Data Manipulation Language.

DML allows database users to manipulate data that is already stored inside tables.

It is mainly used for operations such as:

• Retrieving data
• Inserting data
• Updating data
• Deleting data

Common DML commands include:

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

SELECT Command

The SELECT command is used to access or retrieve data from a table.

Syntax

SELECT * FROM <table-name>;

INSERT Command

The INSERT command is used to enter new information or values into a table.

One or more records can be inserted into a table.

Syntax

INSERT INTO <table_name> (‘column_name1’, ‘column_name2’)

VALUES (‘value1’, ‘value2’);

Example

INSERT INTO students (stu_id, stu_name, city)

VALUES (‘1’, ‘NepalEnote’, ‘Attariya’);

UPDATE Command

The UPDATE command is used to modify data that is already present in a table.

It can update one or more records.

Syntax

UPDATE <table_name>

SET column_name = value

WHERE condition;

Example

UPDATE students

SET due_fees = 20000

WHERE stu_name = ‘Mini’;

DELETE Command

The DELETE command is used to remove records from a table.

If the WHERE condition is not specified, all rows may be deleted.

Syntax

DELETE FROM <table_name>

WHERE <condition>;

Example

DELETE FROM students

WHERE stu_id = ‘001’;

Advantages of DML

The advantages of DML include:

1. DML statements can modify data stored in a database.
2. It provides effective interaction between users and the database.
3. Users can specify what data is required.
4. DML provides different functionalities for manipulating database data.

Disadvantages of DML

Some disadvantages mentioned in the notes are:

1. DML cannot be used to change the structure of the database.
2. It may provide a limited view of a table.
3. It may access data without directly storing it in an object.
4. DML cannot create or delete database structures.

3. Data Control Language

DCL stands for Data Control Language.

DCL commands are used to control privileges and permissions in a database.

Privileges determine what operations a user can perform.

DCL is mainly concerned with:

• Rights
• Permissions
• Access control
• Database security

There are two main DCL commands:

1. GRANT
2. REVOKE

GRANT Command

The GRANT command is used to provide permission to a user to perform a specific operation on a particular database object.

A database administrator can assign different levels of access to different users.

For example, one user may only be allowed to view data, while another may be allowed to update it.

Syntax

GRANT privilege_list

ON object_name

TO user_name;

REVOKE Command

The REVOKE command is used to remove previously granted permissions from a user.

Syntax

REVOKE privilege_list

ON object_name

FROM user_name;

Privileges That Can Be Granted Through DCL

The following privileges are mentioned in the notes:

1. EXECUTE
2. UPDATE
3. SELECT
4. DELETE
5. ALTER
6. ALL

Advantages of DCL Commands

The advantages of DCL include:

1. Restricts unauthorized users from accessing database data.
2. Improves database security when multiple users have access.
3. Allows the database owner or administrator to grant and revoke privileges.
4. Prevents users without permission from modifying the database.

SQL Clause

SQL Clauses are used to work with and filter data stored in database tables.

Clauses help users:

• Filter data
• Analyze data
• Sort results
• Group results
• Retrieve required information

Common SQL clauses and operators discussed in the notes include:

1. WHERE
2. WITH
3. HAVING
4. AND
5. OR
6. ORDER BY

WHERE Clause

The WHERE clause is used to retrieve filtered data based on a specific condition.

It can also be used to filter data using particular criteria or patterns.

Syntax

SELECT column1, column2

FROM table_name

WHERE condition;

Example

SELECT * FROM Customers

WHERE CustomerID = 1;

WITH Clause

The WITH clause is used to define a temporary relation whose result can be used by the associated query.

It can make complex queries easier to read compared with deeply nested subqueries.

According to the provided notes, the WITH clause may not be supported by every database system.

Syntax

WITH cte_name (column1, column2, …) AS

(

SELECT column1, column2, …

FROM your_table

WHERE conditions

)

SELECT * FROM cte_name

WHERE additional_conditions;

HAVING Clause

The HAVING clause is commonly used with the GROUP BY clause.

It filters groups of rows based on specified conditions.

The difference between WHERE and HAVING is:

• WHERE filters individual rows before aggregation.
• HAVING filters grouped rows after aggregation.

Syntax

SELECT column1, aggregate_function(column2), …

FROM table_name

WHERE conditions

GROUP BY column1

HAVING condition;

AND Operator

The AND operator combines multiple conditions.

All specified conditions must be true for a row to be included in the result.

Syntax

SELECT column1, column2, …

FROM table_name

WHERE condition1 AND condition2 AND …;

OR Operator

The OR operator combines multiple conditions where any one of the conditions may be true.

Syntax

SELECT column1, column2, …

FROM table_name

WHERE condition1 OR condition2 OR …;

ORDER BY Clause

The ORDER BY clause is used to sort the result returned by a query based on one or more columns.

By default, ORDER BY sorts data in ascending order.

It can sort results in:

• ASC – Ascending order
• DESC – Descending order

Syntax

SELECT column1, column2, …

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;

SQL Join

An SQL Join is used to combine rows or data from two or more tables based on a common field between them.

The types of joins discussed in the notes are:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
5. NATURAL JOIN

1. INNER JOIN

The INNER JOIN selects rows from both tables where the specified join condition is satisfied.

It combines records when the values in the common field match.

Syntax

SELECT table1.column1, table1.column2, table2.column1, …

FROM table1

INNER JOIN table2

ON table1.matching_column = table2.matching_column;

Figure: Add INNER JOIN diagram here.

2. LEFT JOIN

A LEFT JOIN returns all rows from the table on the left side of the join and matching rows from the table on the right side.

If there is no matching row in the right table, the result contains NULL values for that table.

LEFT JOIN is also known as LEFT OUTER JOIN.

Syntax

SELECT table1.column1, table1.column2, table2.column1, …

FROM table1

LEFT JOIN table2

ON table1.matching_column = table2.matching_column;

Figure: Add LEFT JOIN diagram here.

3. RIGHT JOIN

A RIGHT JOIN works similarly to a LEFT JOIN.

It returns:

• All rows from the right table.
• Matching rows from the left table.

If no matching row exists in the left table, the result contains NULL values.

RIGHT JOIN is also called RIGHT OUTER JOIN.

Syntax

SELECT table1.column1, table1.column2, table2.column1, …

FROM table1

RIGHT JOIN table2

ON table1.matching_column = table2.matching_column;

4. FULL JOIN

A FULL JOIN combines the results of LEFT JOIN and RIGHT JOIN.

It returns all rows from both tables.

When no matching row exists, the result contains NULL values for the missing side.

Syntax

SELECT table1.column1, table1.column2, table2.column1, …

FROM table1

FULL JOIN table2

ON table1.matching_column = table2.matching_column;

5. NATURAL JOIN

A NATURAL JOIN joins tables using columns that have:

• The same column name
• The same data type

Both tables must have at least one common column.

The DBMS identifies matching common columns and returns tuples where their values match.

Syntax

SELECT * FROM

table1 NATURAL JOIN table2;

SQL Alias

An Alias is a temporary name given to a table or column.

Aliases make SQL queries easier to:

• Read
• Understand
• Maintain

They do not permanently change the actual name of the table or column.

There are two main types of aliases:

1. Column Alias
2. Table Alias

1. Column Alias

A Column Alias is a temporary name given to a column for a particular query.

It makes the output easier to understand, especially when working with complex queries.

Syntax

SELECT column_name AS alias_name

FROM table_name;

2. Table Alias

A Table Alias is a temporary name given to a table.

It is particularly useful when working with multiple tables.

A table alias is generally defined in the FROM clause.

Syntax

SELECT alias.column_name

FROM table_name AS alias;

SQL View

A View in SQL is a type of virtual table.

Like a normal table, a view contains:

• Rows
• Columns

However, a view does not store the actual data on disk in the same way as a regular table.

A view represents the result of a customized query that retrieves data from one or more tables.

A view may contain:

• All rows from a table
• Selected rows
• Selected columns
• Data from multiple tables

Syntax for Creating a View

CREATE VIEW view_name AS

SELECT column1, column2, …

FROM table_name

WHERE condition;

Uses of a View

Views are useful for several purposes.

1. Restricting Data Access

Views provide an additional level of security by allowing users to access only selected rows and columns.

2. Hiding Data Complexity

A view can hide the complexity of queries involving multiple joined tables.

3. Simplifying Commands for Users

Views allow users to retrieve information from multiple tables without having to understand how complex joins are performed.

4. Storing Complex Queries

Views can be used to represent and reuse complex queries.

5. Renaming Columns

Views can be used to display different column names without changing the names in the original tables.

This can make data easier to understand.

6. Multiple View Facility

Different views can be created from the same table for different users according to their requirements.