Chapter 2: Database Languages and their information

Others

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.

  1. Data-Manipulation: Commands used to manipulate data within the database, such as inserting, updating, or deleting records.
  2. 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.
  1. 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.NoNeed & UsageHe SQL DDL statement
1Create schema objectsCreate
2Alter schema objectsAlter
3Delete schema objectsDrop
4Rename schema objectsRename

DDL Example:

Example:         create table instructor (
                             ID                char(5),
                             name           varchar(20),
                            
dept_name  varchar(20),
                             salary           numeric(30)

                               )

  1. Instructor Table
IDNameDepartmentSalary
22222EinsteinPhysics95000
12121WuFinance90000
32343El SaidHistory60000
45565KatzComp. Sci.75000
98345KimElec. Eng.80000
76766CrickBiology72000
10101SrinivasanComp. Sci.65000
58583CalifieriHistory62000
83821BrandtComp. Sci.92000
15151MozartMusic40000
33456GoldPhysics87000
76543SinghFinance80000
  • Department Table
DepthnameBuildingBudget
Comp. Sci.Taylor100000
BiologyWatson90000
Elec. Eng.Taylor85000
MusicPackard80000
FinancePainter120000
HistoryPainter50000
PhysicsWatson70000

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. NoNeed and UsageThe SQL DML Statement
1Remove rows from tables or viewsDELETE
2Add new rows of data into table or viewINSERT
3Retrieve data from one or more tablesSELECT
4Change column values in existing rows of a table or viewUPDATE

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 UsageAge
1Grant and take away privileges and rolesGrant
2Add a comment to the data dictionaryComment

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.

0 Comments

You may find interest following article

Chapter 4 Relational Algebra

Relational Algebra The part of mathematics in which letters and other general symbols are used to represent numbers and quantities in formula and equations. Ex: (x + y) · z = (x · z) + (y · z). The main application of relational algebra is providing a theoretical foundation for relational databases, particularly query languages for such databases. Relational algebra...

Chapter 3 Components of the Database System Environment

Components of the Database System Environment There are five major components in the database system environment and their interrelationships are. Hardware Software Data Users Procedures Hardware:  The hardware is the actual computer system used for keeping and accessing the database. Conventional DBMS hardware consists of secondary storage devices, usually...