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).

1. 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.

2. Data-Manipulation: Commands used to manipulate data within the database, such as inserting, updating, or deleting records.
3. 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.

4. 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)

                               )

1. 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’

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.