Friday, October 7, 2016

Muhammad Akbar

Types of Database languages(DBMS)-Tutorial

Types of Database languages

We need a method to create all the logical objects like tables, views, procedures and packages in the database and we need some interface between the user and the database, so that we can access the data stored in it. We also need a standardized method to organize these tables and views in the database.
DBMS is software that defines different operations to be carried out in the database. It varies from creating a database, tables, index, constraints to manipulating the data in the database like inserting, deleting, updating, retrieving, sorting etc. In order to perform all these operations, DBMS defines two forms of database languages.
Below are the database languages. Click them for more details.
 DDL Data Definition Language
 DML Data Manipulation Language
 DCL Data Control Language
 TCL Transaction Control Language


Data Definition Language - DDL


DDLs are used to define the metadata of the database. i.e.; using this, we create schema, tables, constraints, indexes in the database.  DDLs are also used to modify Schema, tables index etc. Basically, using DDL statements, we create skeleton of the database. It helps to store the metadata information like number of schemas and tables, their names, columns in each table, indexes, constraints etc in the database.
Some of the DDL commands are

Create

Create is used to create schema, tables, index, and constraints in the database. The basic syntax to create table is as follows.
CREATE TABLE tablename (Column1 DATATYPE, Column2 DATATYPE, … ColumnN DATATYPE);

CREATE TABLE STUDENT (STUDENT_ID CHAR (10), STUDENT_NAME CHAR (10));
This DDL statement creates STUDENT table with his ID and Name.
An index on the table is created as follows. These created indexes are not visible to the users, but they internally help to run the query quickly.

CREATE INDEX index_name on Table_name (column_name)
CREATE INDEX std_name ON STUDENT (STUDENT_FIRST_NAME, STUDENT_LAST_NAME);
Here std_name is the index, which is created on First name and last name in STUDENT table.
Usually constraints are created along with table creation. Constraints are defined on the columns of the table. They define the characteristic of the column. There are different types of Constraints present.
    1. NOT NULL – This constraint forces the column to have non-null value. We cannot enter/update any NULL value into such columns. It must have valid value all the time. For example, each student in STUDENT table should have class specified. No student can exist without class. Hence class column in the STUDENT table can be made NOT NULL.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);
    1. UNIQUE – This constraint ensures, the column will have unique value for each row. The column value will not repeat for any other rows in the table.
Passport number of individual person is unique. Hence passport column in the PERSON table is made UNIQUE. It avoids duplicate entry of passport number to other persons.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL UNIQUE
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);
OR
CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER
CONSTRAINT uc_StdID UNIQUE (STUDENT_ID));
    1. PRIMARY KEY – This constraint is another type of UNIQUE constraint. This constraint forces the column to have unique value and using which, we can uniquely determine each row.
As we have seen in STUDENT example, STUDENT_ID is the primary key in STUDENT tables.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL PRIMARY KEY,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);
OR
CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER
CONSTRAINT pk_StdID PRIMARY KEY (STUDENT_ID));
    1. FOREIGN KEY – This constraint helps to map two or more tables in the database. It enforces parent-child relationship in the DB. Foreign key in the child table is the column which is a primary key in the parent table.
For example, each employee works for some department. Hence to map employee and department tables, we have to have DEPARMENT_ID of DEPARTMENT table in EMPLOYEE table too.  DEPARTMENT_ID is the primary key in DEPARTMENT table (Parent table) and is foreign key in EMPLOYEE table (Child table).

CREATE TABLE EMPLOYEE (EMPLOYEE_ID VARCHAR2 (10) PRIMARY KEY,
EMP_NAME VARCHAR2 (50),
DOB DATE,
……
DEPT_ID NUMBER
CONSTRAINT fk_DeptId FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID));
    1. CHECK – This constraint is used to check for specific conditions on the column. For example, if age has to be entered between 25 and 32, we can use CHECK Constraint. This will not allow to enter the age32.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER CHECK (AGE >= 25 and AGE<= 32));
    1. DEFAULT – This constraint specifies the default value to be entered when no value is entered to it. Suppose whenever we enter an entry in the STUDENT table, apart from Student details we also have to store the date when it is being entered. This entry would always be SYSDATE. Instead of entering it each time when we do an entry, if we set the default value of this column as SYSDATE, this column will be always inserted with SYSDATE. If we need to override this value with any other date, then we have to explicitly insert the new date.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER,
…..
CREATED_DATE DATE DEFAULT SYSDATE);

Alter

Suppose we have created a STUDENT table with his ID and Name. Later we realize that this table should have his address and Age too. What do we do at this stage? We will add the column to the existing table by the use of ALTER command.
This command is used to modify the structure of the Schema or table. It can even used to add, modify or delete the columns in the table. The syntax for alter statement is as follows.
To add a new column: ALTER TABLE table_name ADD column_name datatype;
To delete a column: ALTER TABLE table_name DROP COLUMN column_name;
To modify a column: ALTER TABLE table_name MODIFY column_name datatype;
To rename table: ALTER TABLE table_name RENAME TO new_table_name;
To rename the column: ALTER TABLE table_name RENAME COLUMN old_Column_name to new_Column_name;
Suppose we want to add Address column to the STUDENT table.

ALTER TABLE STUDENT ADD Address varchar2 (100);
Once we add new columns to the existing table, the column value for the existing data would be NULL. If we need value in them, either we have to set some default value or we need to explicitly update each column with proper value.
Suppose we want to Drop Age column from STUDENT table.

ALTER TABLE STUDENT DROP COLUMN AGE;
Once column is dropped, the entire information in the column is lost.
Suppose we want to modify the DOB column to have only year in it.

ALTER TABLE STUDENT MODIFY DOB NUMBER (4);
Note that, in order to change the one datatype to another datatype, that particular column should not have any value. If we are changing the length of the column, then we can do this with data in the column. If the length of the column is reduced, then the value in the column will be trimmed to adjust with new length.
Suppose name column is modified from Varchar2 (20) to Varchar2 (10) and one of the name was ‘Albert Einstein’. After column modification, the name would be automatically trimmed to ‘Albert Ein’
Alter command is even used to modify the indexes and constraints of the table.

ALTER INDEX index_name RENAME TO new_index_name;
DROP: - DROP statement is used to remove the table or index from the database. It can even be used to remove the database.  Once the DROP statement is executed, the object will not available for use.
 
DROP TABLE table_name;
DROP INDEX index_name;
DROP DATABASE database_name;
DROP TABLE STUDENT;

Truncate

Truncate statement is used to remove the content of the table, but keeps the structure of the table. This simply removes all the records from the table. No partial removal of data is possible here. It also removes all the spaces allocated for the data.

TRUNCATE TABLE table_name;
TRUNCATE TABLE STUDENT;

Comment

We have created some tables and columns in the database. After some days, when developer visits back these tables and columns, wonders why he has created it. He has forgotten, what was the purpose of creating those columns in the table? So what could be done in this case? We need some method to store the description of the tables and columns. Some columns would take only few values like 0 for vegetarian, 1 for eggetarian and 2 for non-vegetarian. But seeing 0, 1 or 2, we will not know what it is.  If we store those values somewhere, it would be helpful in future to enter the values. For this purpose, we use a command – COMMENT.
As the name says, it allows adding comments to tables, views and columns in the database. These comments are stored in the data dictionary of the database. This helps the developer to understand what the column is.
Comment of Table/view: COMMENT ON TABLE table_name IS 'text';
Comment of Column: COMMENT ON COLUMN table.column IS 'text'
For Example:

COMMENT ON TABLE STUDENT IS This table has all the details of Students’;
COMMENT ON COLUMN STUDENT.DOB IS 'Date of Birth of the Student'
If we need to drop the comment, then we just have to give the comment without any text.

COMMENT ON TABLE STUDENT IS ‘’;
COMMENT ON COLUMN STUDENT.DOB IS ‘’;

Data Manipulation Language - DML


When we have to insert records into table or get specific record from the table, or need to change some record, or delete some record or perform any other actions on records in the database, we need to have some media to perform it. DML helps to handle user requests. It helps to insert, delete, update, and retrieve the data from the database. Let us see some of them.

Select

Select command helps to pull the records from the tables or views in the database. It either pulls the entire data from the table/view or pulls specific records based on the condition. It can even retrieve the data from one or more tables/view in the database.
The basic SELECT command is

SELECT * FROM table_name; -- retrieves all the rows and columns from table table_name and displays it in tabular form.
SELECT COLUMN1, COLUMN2, COLUMN3 from table_name; -- retrieves only 3 columns from table table_name
SELECT t1.COLUMN1, t2.COLUMN1
FROM table_name1 t1, table_name2 t2
WHERE t1.COLUMN2 = t2.COLUMN2; -- Combines 2 tables and retrieves specific columns from both the tables.
Some examples of SELECT:

SELECT * FROM STUDENT; -- All the columns are retrieved and displayed in below format

SELECT STUDENT_NAME, ADDRESS FROM STUDENT; -- Retrieves only name and address from STUDENT table

SELECT e.EMPLOYEE_NAME, e.ADDRESS, e.PHONE_NUMBER, d.DEPARTMENT_NAME
FROM EMPLOYEE e, DEPARTMENT d
WHERE E.DEPARTMENET_ID = D.DEPARTMENT_ID; -- Displays employee name, address, phone number and the department name for which he works, by joining EMPLOYEE and DEPARTMENT tables.
General syntax of SELECT is:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
Where
WHERE Clause - here we can specify the filter conditions to the query. We can add any number of conditions.
GROUP BY - We can combine specific categories of columns together and show the results. For example, there are multiple employees working in different department. Using group by option we can display how many employees are working in each department.

SELECT d.DEPATMENT_NAME, COUNT (e.DEPATMENT_ID) total_emp_count
FROM EMPLOYEE e, DEPARTMENT d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID  this condition pulls the matching employees
GROUP BY e.DEPARTMENT_ID;
In the above query instead of count (e.DEPARTMENT_ID), we can give count (1). Both are same.
Some of the Group functions are
Count - it counts the total number of records in the table/s after applying ‘where’ clause. If where clause is not specified, it gives the total number of records in the table. If Group by clause is applied, it filters the records based on where clause (if any), then groups the records based on the columns in group by clause and gives the total count of records in each grouped category.
SUM – It totals the value in each numeric column. We can use this function to find the total marks of a student, total salary of an employee in a specific period etc.
AVG – It gives the average value of a column, provided column has numeric value. E.g.: Average age of students present in particular class.
MAX – It gives the maximum value in a column. For example, highest scorer in the class can be retrieved by MAX function.
MIN– It gives the minimum value in a column. For example, lowest paid employee in a department can be obtained by MIN.
These group functions can be used with group by clause or without it.
Having Clause - Using this clause we can add conditions to the grouped categories and filter the records. For examples, if we want to display the details of the department which has more than 100 employees.

SELECT d.DEPATMENT_NAME, COUNT (e.DEPATMENT_ID) total_emp_count
FROM EMPLOYEE e, DEPARTMENT d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID  this condition pulls the matching employees
GROUP BY e.DEPARTMENT_ID
HAVING COUNT(e.DEPATMENT_ID)>100; -- filters more than 100 employees present in specific department. We cannot give this condition in the where clause as this is the result of Group by clause.
ORDER BY - This clause helps to sort the records that are retrieved. By default, it displays the records in ascending order of primary key. If we need to sort it based on different columns, then we need to specify it in ORDER BY clause. If we need to order by descending order, then DESC keyword has to be added after the column list.

SELECT * FROM EMPLOYEE
ORDER BY EMPLOYEE_NAME DESC; -- Displays all records in descending order of employee name
We can combine two or more columns into one column by using || in select statement.

SELECT EMP_FIRST_NAME ||    || EMP_LAST_NAME  as emp_name
FROM EMPLOYEE WHERE EMP_ID = 1001; -- Here first name and last name of employee with id 1001 to show it as emp_name

Insert

Insert statement is used to insert new records into the table. The general syntax for insert is as follows:

INSERT INTO TABLE_NAME
(col1, col2, col3,...colN) VALUES (value1, value2, value3,...valueN);
It inserts value to each column from 1 to N. When we insert the data in this way, we need to make sure datatypes of each column matches with the value we are inserting. Else, data will not be inserted or will insert wrong values. Also, if there is any foreign key constraint on the table, then we have to make sure foreign key value already exists in the parent table.

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, ADDRESS, DEPT_ID) VALUES
(10001, Joseph’, Troy’, 11101); -- Here dept_id 11101 is a foreign key and it has be inserted into department table before inserting into the employee table.
We can insert the values into the table without specifying the columns, provided we enter the value in the order the table structure is.  Imagine table structure for Employee is as follows and then we can insert the record without specifying column names as below:


INSERT INTO EMPLOYEE VALUES (10001, Joseph’, Troy’, 11101);
If we are specifying the column list then we need not insert it in the order of table structure. It inserts the values in the order the column is listed in the INSERT statement.

INSERT INTO EMPLOYEE (EMP_ID, DEPT_ID, ADDRESS, EMP_NAME) VALUES
(10001, 11101,’Troy’, Joseph’);
We can even copy some of the column values from the existing table. Suppose we have to copy emp_id, emp_name from Employee table to some temporary table. Then we can write as follows: (Assuming here that datatype in both the tables are same and emp_name has enough buffers to store both first name and last name combined).


INSERT INTO TEMP_EMP
SELECT e.EMP_ID, e.EMP_FIRST_NAME ||  ‘|| e.EMP_LAST_NAME as emp_name
FROM EMPLOYEE e;

Update

Update statement is used to modify the data value in the table. General syntax for update is as below:

UPDATE table_name
SET column_name1 = value1,
column_name2 = value2,
...
column_nameN = valueN,
[WHERE condition]
Imagine, an employee has changed his address and it needs to be updated in the Employee table.

UPDATE EMPLOYEE
SET ADDRESS = Clinton Township
WHERE EMP_ID = 10110;
If we do not specify ‘WHERE’ condition in the ‘UPDATE’ statement, then it will update the whole table with new address. Hence we have to specify which record/employee has to be updated with new address.
Suppose there is increment in the salary of all the employees by 10% and this has to be updated in the Employee table. How will we write update statement? Do we need to specify the WHERE clause? Can we specify the arithmetic calculation in the UPDATE statement?

UPDATE EMPLOYEE SET Salary = salary+ (salary*0.1);
This is how we update the salary.

Delete

Using Delete statement, we can delete the records in the entire table or specific record by specifying the condition.

DELETE FROM table_name [WHERE condition];
Suppose we have to delete an employee with id 110 from Employee table. Then the delete statement would be

DELETE FROM EMPLOYEE WHERE EMP_ID = 110;
If we do not specify the condition, then it would delete entire record from the Employee table. This statement is different from TRUNCATE in two ways:
  • Using DELETE statement, we can delete few records by specifying the condition. If we do not specify the condition, it deletes entire records in table. Whereas TRUNCATE deletes all the records in the table.
  • DELETE statement simply removes records from the table, whereas TRUNCATE statement frees the space occupied by the data. Hence TRUNCATE is more efficient than DELETE, when we have to empty the table.

Data Control Language - DCL


Suppose we have created a table STUDENT in the database. Now who can view or access these tables? No one other than who has created it! We need to explicitly tell the database, who can view this table and what kind of access like read, write, delete should be given to the other users.
Similarly, some of the users who were accessing this table have left the organization. But still they have access to this table, which is not acceptable. Hence we need to remove their access on such tables and database.
DCL languages are used to control the user access to the database, tables, views, procedures, functions and packages. They give different levels of access to the objects in the database.

GRANT

GRANT provides the privileges to the users on the database objects. The privileges could be select, delete, update and insert on the tables and views. On the procedures, functions and packages it gives select and execute privileges. We can either give all the privileges or any one or more privileges to the objects. The syntax of GRANT is as below:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
Where
  • Privilege_name is the level of access given to the users. Some of the access rights are ALL, DELETE, UPDATE, INSERT, EXECUTE and SELECT.
  • Object_name is the name of a database object like TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE and SEQUENCE.
  • User_name is the name of the user to whom an access is being granted.
  • PUBLIC is used to grant access rights to all users.
  • ROLES are a set of privileges grouped together.
  • WITH GRANT OPTION - allows a user to grant access rights to other users. i.e.; usually grants are given by the user who has created the database objects. But with this option, the users who have got the access rights can also provide the grants and access other tables/views.

GRANT SELECT, INSERT ON STUDENT TO Mathew;
Here SELECT and INSERT grants are given to Mathew on STUDENT table.

GRANT EXECUTE on sp_getStudentNames to Joseph;
This grant gives the execution rights to Joseph on the stored procedure sp_getStudentNames.

REVOKE

REVOKE removes the privileges given on the database objects. We can remove all the privileges or remove one or more privileges from the objects.

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

REVOKE INSERT ON STUDENT FROM Mathew; -- Removes the INSERT grant from Mathew on STUDENT Table

Transaction Control Language - TCL



Suppose we have inserted some records in to Employee table. Now we need to save them. How? Similarly, we have updated something wrong on the table. After updating we realized that its wrong. Now we need to unsave the changes that have been done. How? We have deleted something or inserted something which is not correct. It has to be undone. All these saving and undoing the tasks can be done by TCL. Some of the commands in TCLs are as below:

COMMIT

COMMIT saves the transaction on the database. The transaction can be insert, delete or update. Once the COMMIT is issued, the changes are saved permanently in the database. It cannot be undone.

UPDATE STUDENT SET STUDENT_NAME = Mathew WHERE STUDENT_NAME = Mahtwe’;
COMMIT;
Above set of transactions, updates the wrong student name to the correct one and saves the changes permanently in the database. Update transaction is complete only when commit is issued, else there will be lock on ‘Mahtwe’ record till the commit or rollback is issued.
Below diagram shows that ‘Mahtwe’ is updated to ‘Mathew’ and still there will be a lock on his record. Once Commit is issued, updated value is permanently saved to database and lock is released.

ROLLBACK

ROLLBACK command is used to undo the insert, delete or update transaction in the database. It undoes the transaction performed on the table and restores the previous stored value.

UPDATE STUDENT SET STUDENT_NAME = Stewart WHERE STUDENT_NAME = Mathew;
ROLLBACK;
Here, after updating the student name, user realizes that he has updated the wrong record and he wants to undo his update. What he does is, he issues ROLLBACK command and undoes his update. When he issues update statement Mathew’s record will be locked for update and will be updated to ‘Stewart’. But lock will not be released – meaning update is not saved fully into the database and transaction is not complete. Once the rollback is issued, it undoes the update and restores the value to ‘Mathew’ and save the changes permanently. Hence there will not be any changes done to Mathew.

SAVEPOINT

Suppose there are set of update, delete transactions performed on the tables. But there are some transactions which we are very sure about correctness. After that set of transactions we are uncertain about the correctness. So what we can do here is we can set a SAVEPOINT at the correct transaction telling the database that, in case of rollback, rollback till the savepoint marked. Hence the changes done till savepoint will be unchanged and all the transactions after that will be rolled back.
Have look at below transactions.
    • It updates ‘Mahtwe’ to ‘Mathew’. Hence we have lock on Mathew record.
    • It also updates Joseph’s record for his Age to 15.
Here, say we have set the SAVEPOINT after first transaction. Then second transaction for age update is issued and we see that it is a wrong update, but the name update is correct. Here we have to rollback only the last transaction and retain the first transaction. So we issue Rollback till the savepoint. What it does is, it reverts all the transaction till the savepoint. Although there is no commit, the transactions till savepoint is retained and saved later upon commit. Hence you can see the lock on Mathew’s record.

UPDATE STUDENT SET STUDENT_NAME = Mathew WHERE STUDENT_NAME = Mahtwe’;
SAVEPOINT S1;
UPDATE STUDENT SET AGE = 15 WHERE STUDENT_ID = 100;
ROLLBACK to S1;

In the case of multiple transactions, savepoint can be given after each transactions and transaction can be rolled back to any of the transactions.

TRANSACTION T1;   -- Transaction can be insert, update or delete 

SAVEPOINT S1;
TRANSACTION T2;
SAVEPOINT S2;
TRANSACTION T3;
SAVEPOINT S3;
TRANSACTION T4;
ROLLBACK TO S1; -- This will rollback all the changes by T1 and T2 and will have only the changes done on T1.

AUTOCOMMIT

AUTOCOMMIT command automatically commits each transaction after its execution. If this command is set, then no need to explicitly issue commit. We cannot rollback our transactions, if AUTOCOMMIT is on. This needs to be set /unset before we begin any transactions.

SET AUTOCOMMIT ON; -- Sets AUTOCOMMIT to ON
SET AUTOCOMMIT ON; -- Sets AUTOCOMMIT to OFF

                

Database Interface


Muhammad Akbar

About Muhammad Akbar -

Author Description here.. Nulla sagittis convallis. Curabitur consequat. Quisque metus enim, venenatis fermentum, mollis in, porta et, nibh. Duis vulputate elit in elit. Mauris dictum libero id justo.

Subscribe to this Blog via Email :

Hello Every One thanks For Visit My site You Can Suggest any Idea About site And Related Education Information Provide me Its Best Content For you am Include in this Site And feel free contact And email now I hope This site very help full you