DBMS Language
Database server is an important function for many individuals and companies, providing a particular way to organize and store information on a computer. In programming, developers use database language to maintain and monitor an electronic database and its management system. This language is used to perform variety of tasks that help database language, also known as query language or data query language. They are classified of programming language that developers use to define an access database which is collection of organized data that users can access electrically.
These languages also allow users to complete tasks like: Controlling access to data, Defining and updating data, Add searching for information with the database management system.
There are different examples of database language available. Some of most popular database languages are: SQL, X-QUERY, ORACLE, Graph-QL, etc.
Structured Query language (SQL)
It is one of the most well-known and longest running database language initially created in 1917’s IBM computer
scientists. It is a ISO, ANSI standard language for every RDBMS, MY-SQL, MS-SQL, ORACLE, MS-Access, etc. SQL let you to access and manipulate the database. The user can make, read, update and delete relational database.
Advantages of SQL:
i) Fast and efficient query process
ii) No need of coding skills
iii) Large user community
iv)Data integrity
v) Scalability
vi) Standardization
vii) Flexibility
viii) Consistency
ix) Data Durability, etc.
Disadvantages of SQL:
i) Complex Interface
ii) More Costly
iii) Partial Control
Types of SQL:
SQL Command
SQL commands are instruction that is used to communicate with the database.
1) Data Definition Language
DDL changes the structure of database table like creating a table, deleting the table, altering the table, etc. All the commands of DDL are auto committed that means it permanently saves all the changes in the database. It is also used to create and modify the structure of database objects which include views, schemas, indexes, etc.
Some commands in DDL are:
i) CREATE: This command is used to create a new table, view of table or order object in the database
Syntax for creating table and database :
CREATE TABLE <table-name>( set of columns with data-types);
CREATE DATABASE <database-name>;
ii) ALTER: It is used to alter or change the structure of database objects with DDL command.
Syntax for Alter command:
ALTER TABLE <table-name>
ADD
<column-name> data-type (length of column);
iii) DROP: We can delete the table definition and its contents from the SQL system using this DDL command.
Syntax for DROP command:
DROP TABLE <table-name>;
iv) TRUNCATE: Using this command we can delete records of the table and free up the space.
Syntax for TRUNCATE command:
TRUNCATE TABLE <table-name>;
v) RENAME: Using this command, we can change the name of existing table.
Syntax for RENAME command:
RENAME <old table name> to <new table name>;
2)Data Manipulation Language
User of database can alter the data that is already present in the tables by using DML. It is used to modify the structure of database by inserting data, updating, deleting data, etc.
Some command of DML are:
i) SELECT: We can access the data inside the table using this command.
Syntax for select command:
SELECT *FROM <table-name>;
ii) INSERT Command: This command is used to enter the information or values into a row. We can connect one or more
records to a single table within a repository using this instruction. This is often used to connect an unused tag to the documents.
Syntax:
INSERT INTO <table_name> (‘column_name1’ <datatype>, ‘column_name2’ <datatype>)
VALUES (‘value1’, ‘value2’);
Example :
INSERT INTO students (‘stu_id’ int, ‘stu_name’ varchar(20), ‘city’ varchar(20))
VALUES (‘1’, ‘NepalEnote’, ‘Attariya’);
iii) UPDATE Command:This command is used to alter existing table records. Within a table, it modifies data from one or more records. This command is used to alter the data which is already present in a table.
Syntax:
UPDATE <table_name>
SET <
column_name = value>
WHERE condition;
Example:
UPDATE students
SET due_fees = 20000
WHERE stu_name = ‘Mini’;
iv) DELETE Command: It deletes all archives from a table. This command is used to erase some or all of the previous table’s records. If we do not specify the ‘WHERE’ condition then all the rows would be erased or deleted.
Syntax:
DELETE FROM <table_name>
WHERE <condition>;
Example:
DELETE FROM students
WHERE stu_id = ‘001’;
Advantages of DML
i) DML statements could alter the data that is contained or stored in the database.
ii) It delivers effective human contact with the machine.
iii) User could specify what data is required.
iv) DML aims to have many different varieties and functionalities between vendors providing databases.
Disadvantages of DML
i) We cannot use DML to change the structure of the database.
ii) Limit table view i.e., it could conceal some columns in tables.
iii) Access the data without having the data stored in the object.
iv) Unable to build or erase lists or sections using DML.
2) Data Control Language
DCL stands for Data Control Language in Structured Query Language (SQL). These commands are used to control privilege in the database. The privileges (Right to access the data) are required for performing all the database operations like creating tables, views, or sequences. DCL command is a statement that is used to perform the work related to the rights, permissions, and other control of the database system.
There are 2 commands in DCL:
i) GRANT: This command is used to grant permission to the user to perform a particular operation on a particular object. If you are a database administrator and you want to restrict user accessibility such as one who only views the data or may only update the data. You can give the privilege permission to the users according to your wish.
Syntax:
GRANT privilege_list
ON Object_name
TO user_name;
ii) REVOKE: This command is used to take permission/access back from the user. If you want to return permission from the database that you have granted to the users at that time you need to run REVOKE command.
Syntax:
REVOKE privilege_list
ON object_name
FROM user_name;
Following commands are granted to the user as a Privilege List Through DCL:
i) EXECUTE
ii) UPDATE
iii) SELECT
iv)DELETE
v) ALTER
vi)ALL
Advantages Of DCL commands
i) It allows to restrict the user from accessing data in database.
ii) It ensures security in database when the data is exposed to multiple users.
iii) It is the wholesome responsibility of the data owner or data administrator to maintain the authority of grant and revoke privileges to the users preventing any threat to data.
iv) It prevent other users to make changes in database who have no access to Database
SQL Clause
Clauses are in-built functions available to us in SQL. With the help of clauses, we can deal with data easily stored in the table. Clauses help us filter and analyze data quickly. When we have large amounts of data stored in the database, we use Clauses to query and get data required by the user.
Some clause in SQL are:-
i) WHERE: WHERE keyword is used for fetching filtered data in a result set. It is used to fetch data according to particular criteria. WHERE keyword can also be used to filter data by matching patterns.
Syntax:
SELECT column1,column2
FROM table_name
WHERE condition;
Example:
SELECT * FROM Customers
WHERE CustomerID=1;
ii) WITH: The clause is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query that is associated with the WITH clause. Queries that have an associated WITH clause can also be written using nested sub-queries but doing so add more complexity to read/debug the SQL query. WITH clause is not supported by all database system. The name assigned to the sub-query is treated as though it was an inline view or table.
Syntax:
WITH cte_name (column1, column2, …) AS
(SELECT column1, column2, …
FROM your_table
WHERE conditions)
SELECT *FROM cte_name
WHERE additional_conditions;
iii) HAVING: The HAVING clause in SQL is used in combination with the GROUP BY clause to filter groups of rows based on specified conditions. While the WHERE clause filters individual rows before the aggregation, the HAVING clause filters grouped rows after the aggregation.
Syntax:
SELECT column1, aggregate_function(column2), …
FROM table_name
WHERE conditions
GROUP BY column1
HAVING condition;
iv) AND: The AND clause in SQL is not a standalone clause but rather a logical operator that allows you to combine multiple conditions in a WHERE or HAVING clause to filter rows based on more than one condition simultaneously. Syntax:
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND …;
v) OR: The OR clause in SQL is logical operator that allows you to combine multiple conditions in a WHERE or HAVING clause to filter rows based on any of the specified conditions being true.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR …;
- vi) ORDER BY:The ORDER BY clause in SQL is used to sort the result set returned by a query based on one or more columns. It allows you to specify the order in which rows should appear in the result set.
In default ORDER BY shows in Ascending Order.
Syntax:
SELECT column1, column2, …
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;
SQL Join
It is used to combine data or rows from two or more tables based on a common field between them. There are different types of join in SQL:
i) INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,….
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
In the figure below, concept of Inner join is described or showed. - ii) LEFT JOIN: This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null. 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;
In the figure below the concept of left join is displayed.
iii) RIGHT JOIN: RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,….
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
iv) FULL JOIN: FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,….
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
v) NATURAL JOIN: Natural join can join tables based on the common columns in the tables being joined. A natural join returns all rows by matching values in common columns having same name and data type of columns and that column should be present in both tables. Both tables must have at least one common column with same column name and same data type. The two tables are joined using Cross join. DBMS will look for a common column with same name and data type Tuples having exactly same values in common columns are kept in result.
Syntax:
SELECT *FROM
table1 NATURAL JOIN table2;
SQL ALIAS: Aliases in SQL Server are the temporary names given to tables or columns to make it easy to read and maintain the data. Aliases help you to provide different names to columns and tables temporarily so that users can easily understand the data of the table and it does not change any data of the table or column.
Some ways of using Alias:
i) Column Aliases: Column aliases are temporary names that are given to the columns for specific queries this makes output data more readable and easy to understand especially when dealing with complex queries like join queries and also refers to columns more easily in GROUP BY, ORDER BY, and WHERE clauses. Column aliases are defined in the SELECT statement.
Syntax:
SELECT column_name AS alias_name
FROM table_name;
ii) Table Aliases: Table aliases are temporary name that are given to the table especially when working with multiple tables in a query. Table aliases are defined in FROM clause of query after original table name using AS keyword.
Syntax:
SELECT alias.column_name
FROM table_name AS alias;
SQL VIEW
Views in SQL are a kind of virtual table. A view also has rows and columns like tables, but a view doesn’t store data on the disk like a table. View defines a customized query that retrieves data from one or more tables, and represents the data as if it was coming from a single source. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain conditions.
Syntax for creating View
CREATE VIEW view_name AS
SELECT column1, column2…..
FROM table_name
WHERE condition;
Uses of a View
A good database should contain views for the given reasons:
i) Restricting data access : Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table.
ii) Hiding data complexity :A view can hide the complexity that exists in multiple joined tables.
iii) Simplify commands for the user :Views allow the user to select information from multiple tables without requiring the users to actually know how to perform a join.
iv) Store complex queries :Views can be used to store complex queries.
v) Rename Columns : Views can also be used to rename the columns without affecting the base tables provided the number of columns in view must match the number of columns specified in a select statement. Thus, renaming helps to hide the names of the columns of the base tables.
vi) Multiple view facility : Different views can be created on the same table for different users.