Database Languages
- A DBMS must provide appropriate languages and interfaces for each category of users to express database queries and updates.
- Database Languages are used to create and maintain database on computer.
- There are large numbers of database languages like Oracle, MySQL, MS Access, dBase, FoxPro etc.
Database Languages: Refers to the languages used to interact with databases, such as SQL (Structured Query Language).
- Data-Definition: Involves commands used to define and modify database structures, like creating or altering tables.
Create Table Test (
Title Varchar2(20));
Create Table Test: An SQL command to create a new table named “Test”.Title Varchar2(20): Specifies a column named “Title” with a data type of VARCHAR2 and a maximum length of 20 characters.
- Data-Manipulation: Commands used to manipulate data within the database, such as inserting, updating, or deleting records.
- Update: An SQL command used to modify existing records in a table. Insert: An SQL command used to add new records to a table. Delete: An SQL command used to remove records from a table. Query: Refers to the process of requesting data from a database, typically using SELECT statements.
- Data-Control: Commands related to controlling access to the data, such as granting or revoking permissions. GRANT Connect, Resource TO xUser: An SQL command to grant specific privileges (Connect and Resource) to a user named “xUser”.
Data Definition Language (DDL)
- SQL statements commonly used in Oracle and MS Access can be categorized as
- Data Definition Language (DDL),
- Data Control Language (DCL)
- Data Manipulation Language (DML).
- Data Definition Language specify the database schema.
- It is a language that allows the users to define data and their relationship to other types of data.
- It is mainly used to create databases, data dictionary and tables within databases.
- It is also used to specify the structure of each table, set of associated values with each attribute, integrity constraints, security and authorization information for each table and physical storage structure of each table on disk.
- The following table gives an overview about usage of DDL statements in SQL
| S.No | Need & Usage | He SQL DDL statement |
| 1 | Create schema objects | Create |
| 2 | Alter schema objects | Alter |
| 3 | Delete schema objects | Drop |
| 4 | Rename schema objects | Rename |
DDL Example:
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(30)
)
- Instructor Table
| ID | Name | Department | Salary |
| 22222 | Einstein | Physics | 95000 |
| 12121 | Wu | Finance | 90000 |
| 32343 | El Said | History | 60000 |
| 45565 | Katz | Comp. Sci. | 75000 |
| 98345 | Kim | Elec. Eng. | 80000 |
| 76766 | Crick | Biology | 72000 |
| 10101 | Srinivasan | Comp. Sci. | 65000 |
| 58583 | Califieri | History | 62000 |
| 83821 | Brandt | Comp. Sci. | 92000 |
| 15151 | Mozart | Music | 40000 |
| 33456 | Gold | Physics | 87000 |
| 76543 | Singh | Finance | 80000 |
- Department Table
| Depthname | Building | Budget |
| Comp. Sci. | Taylor | 100000 |
| Biology | Watson | 90000 |
| Elec. Eng. | Taylor | 85000 |
| Music | Packard | 80000 |
| Finance | Painter | 120000 |
| History | Painter | 50000 |
| Physics | Watson | 70000 |
Write SQL code for creating the following
CREATE TABLE department (
dept_name CHAR(20),
building CHAR(15),
budget NUMERIC(12,2)
);
This SQL statement defines a table named department with three columns:
- dept_name of type CHAR(20)
- building of type CHAR(15)
- budget of type NUMERIC(12,2)
Data Manipulation Language (DML)
- It is a language that provides a set of operations to support the basic data manipulation operations on the data held in the databases.
- It allows users to insert, update, delete and retrieve data from the database.
- The part of DML that involves data retrieval is called a query language.
- Two classes of DML languages
- Procedural – user specifies what data is required and how to get those data
- Declarative (nonprocedural) – user specifies what data is required without specifying how to get those data
- SQL is the most widely used query language
- The following table gives an overview about the usage of DML statements in SQL:
SQL DML Statements and Their Usage
| S. No | Need and Usage | The SQL DML Statement |
| 1 | Remove rows from tables or views | DELETE |
| 2 | Add new rows of data into table or view | INSERT |
| 3 | Retrieve data from one or more tables | SELECT |
| 4 | Change column values in existing rows of a table or view | UPDATE |
SQL: widely used non-procedural language
- Example: Find the name of the instructor with ID 22222
- select name
from instructor
where instructor.ID = ‘22222’
- select name
Example: Find the ID and building of instructors in the Physics dept.
select instructor.ID, department.building
from instructor, department
where instructor.dept_name =
department.dept_name and
department.dept_name = ‘Physics’
Finds the names of all instructors in the History department.
- Finds the names of all instructors in the History department.
SELECT instructor.name
FROM instructor
WHERE instructor.dept_name = ‘History’;
In this corrected query:
- instructor.dept_name is used instead of instructor.dept.name to correctly reference the department name column.
Find the instructor ID and department name of all instructors associated with a department with budget of greater than $95,000.
SELECT instructor.ID, department.dept_name
FROM instructor
JOIN department ON instructor.dept_name = department.dept_name
WHERE department.budget > 95000;
In this version:
- The JOIN clause is used to explicitly join the instructor and department tables on the dept_name column.
- The WHERE clause filters the results to include only those departments with a budget greater than 95000.
Data Control Language (DCL)
- DCL statements control access to data and the database using statements such as GRANT and REVOKE.
- A privilege can either be granted to a User with the help of GRANT statement.
- The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX etc.
- In addition to granting of privileges, you can also revoke (taken back) it by using REVOKE command.
The following table gives an overview about the usage of DCL statements in SQL:
Privileges and Data Dictionary Usage
| S. No. | Need and Usage | Age |
| 1 | Grant and take away privileges and roles | Grant |
| 2 | Add a comment to the data dictionary | Comment |
Database Language
- In practice, the data definition and data manipulation languages are not two separate languages. Instead they simply form parts of a single database language such as Structured Query Language (SQL).
- SQL represents combination of DDL ,DML and DCL, as well as statements for constraints specification and schema evaluation.