Skip to main content

DATA MANIPULATION & CONTROL IN DBMS

 DATA MANIPULATION & CONTROL

After reading this chapter you will able to -

  state SQL, DDL, DML, DCL Statements

  Select, group by & having clause 

   String & set operations

   Aggregate Functions 

  Nested Sub Queries  

 Embedded & Dynamic SQL

In this chapter we study the query language : 

Structured Query Language (SQL) which uses a combination of Relational algebra and Relational calculus. It is a data sub language used to organize, manage and retrieve data from relational database, which is managed by Relational Database Management System (RDBMS). Vendors of DBMS like Oracle, IBM, DB2, Sybase, and Ingress use SQL as programming language for their database. 

 SQL originated with the system R project in 1974 at IBM's San Jose Research Centre. Original version of SQL was SEQUEL which was an Application Program Interface (API) to the system R project. The predecessor of SEQUEL was named SQUARE. 

 SQL-92 is the current standard and is the current version. The SQL language can be used in two ways :

  Interactively or 

  Embedded inside another program. 

The SQL is used interactively to directly operate a database and produce the desired results. The user enters SQL command that is immediately executed. Most databases have a tool that allows interactive execution of the SQL language. These include SQL Base's SQL Talk, Oracle's SQL Plus, and Microsoft's SQL server 7 Query Analyzer. 

 The second way to execute a SQL command is by embedding it in another language such as Cobol, Pascal, BASIC, C, Visual Basic, Java, etc. The result of embedded SQL command is passed to the variables in the host program, which in turn will deal with them. 

The combination of SQL with a fourth-generation language brings together the best of two worlds and allows creation of user interfaces and database access in one application. 

SUBDIVISIONS OF SQL -

 Regardless of whether SQL is embedded or used interactively, it can be divided into three groups of commands, depending on their purpose.

 • Data Definition Language (DDL).

 • Data Manipulation Language (DML).

 • Data Control Language (DCL). 

Data Definition Language : Data Definition Language is a part of SQL that is responsible for the creation, updation and deletion of tables. It is responsible for creation of views and indexes also. The list of DDL commands is given below : 

 CREATE TABLE 

 ALTER TABLE 

 DROP TABLE 

 CREATE VIEW 

 CREATE INDEX 

Data Manipulation Language : Data manipulation commands manipulate (insert, delete, update and retrieve) data. The DML language includes commands that run queries and changes in data. It includes the following commands : 

 SELECT

 UPDATE 

 DELETE

 INSERT

Data Control Language : The commands that form data control language are related to the security of the database performing tasks of assigning privileges so users can access certain objects in the database. The DCL commands are : 

 GRANT R

EVOKE 

 COMMIT 

 ROLLBACK 

DATA DEFINITION LANGUAGE-

The SQL DDL provides commands for defining relation schemas, deleting relations, creating indices, and modifying relation schemas. The SQL DDL allows the specification of not only a set of relations but also information about each relation including : 

 • The schema for each relation. 

 • The domain of values associated with each attribute. 

• The integrity constraints. 

 • The set of indices to be maintained for each relation. 

 •The security and authorization information for each relation. 

 • The physical storage structure of each relation on disk. 

Domain/Data Types in SQL : 

The SQL - 92 standard supports a variety of built-in domain types, 

including the following : 

 (1) Numeric data types include 

 • Integer numbers of various sizes 

 INT or INTEGER 

 SMALLINT 

 • Real numbers of various precision 

 REAL 

 DOUBLE PRECISION 

 FLOAT (n) 

 • Formatted numbers can be represented by using 

 DECIMAL (i, j) or 

 DEC (i, j) 

 NUMERIC (i, j) or NUMBER (i, j) 

 where, i - the precision, is the total number of decimal digits 

 and j - the scale, is the number of digits after the decimal point. 

 The default for scale is zero and the default for precision is 

implementation defined. 

(2) Character string data types - are either fixed - length or varying - 

length. 

 CHAR (n) or CHARACTER (n) - is fixed length character 

string with user specified length n. 

 VARCHAR (n) - is a variable length character string, with user 

- specified maximum length n. The full form of 

CHARACTER VARYING (n), is equivalent. 

(3) Date and Time data types : 

 There are new data types for date and time in SQL-92. 

DATE - It is a calendar date containing year, month and 

day typically in the form 

yyyy : mm : dd 

TIME - It is the time of day, in hours, minutes and 

seconds, typically in the form 

HH : MM : SS. 

 Varying length character strings, date and time were not 

part of the SQL - 89 standard. 

In this section we will study the three Data Definition Language Commands : 

 CREATE TABLE 

 ALTER TABLE 

 DROP TABLE 

CREATE TABLE Command : The CREATE TABLE COMMAND is used to specify a new relation by giving it a name and specifying its attributes and constraints. The attributes are specified first, and each attribute is given a name, a data type to specify its domain of values and any attribute constraints such as NOT NULL. The key, entity integrity and referential integrity constraints can be specified within the CREATE TABLE statement, after the attributes are declared. 

Syntax of create table command : 

 CREATE TABLE table_name ( 

 Column_name 1 data type [NOT NULL], 

 : 

 : 

 Column_name n data_type [NOT NULL]); 

 The variables are defined as follows : 

 If NOT NULL is not specified, the column can have NULL values. 

 table_name - is the name for the table. 

 column_name 1 to column_name n - are the valid column names 

or attributes. 

 NOT NULL – It specifies that column is mandatory. This feature 

allows you to prevent data from being entered into table without 

certain columns having data in them. 

Examples of CREATE TABLE Command : 

 (1) Create Table Employee 

 (E_name varchar2 (20) NOT NULL, 

 B_Date Date, 

 Salary Decimal (10, 12) 

 Address Varchar2 (50); 

 (2) Create Table Student 

 (Student_id Varchar2 (20) Not Null, 

 Last_Name Varchar2 (20) Not Null, 

 First_name Varchar2 (20), 

 BDate Date, 

State Varchar2 (20), 

 City Varchar2 (20)); 

 (3) Create Table Course 

 (Course_id Varchar2 (5), 

 Department_id Varchar2 (20), 

 Title Varchar2 (20), 

 Description Varchar2 (20));

The Primary Key : 

A table's primary key is the set of columns that uniquely identifies 

each row in the table. CREATE TABLE command specifies the 

primary key as follows : 

 create table table_name ( 

 Column_name 1 data_type [not null], 

 : 

 : 

Column_name n data type [NOT NULL], 

[Constraint constraint_name] 

[Primary key (Column_name A, Column_name B… Column_name 

X)]); 

Variables are defined as follows : 

table_name is the name for the table. 

column_name 1 through column_name n are the valid column names 

data_type is valid datatype 

constraint which is optional 

constraint_name identifies the primary key 

column_name A through column_name X are the table's columns that 

compose the primary key. 

Foreign Key : A foreign key is a combination of columns with values based on the primary key values from another table. A foreign key constraint also known as a referential integrity constraint, specifies 

that the values of the foreign key correspond to actual values of 

primary key in other table. 

Create table command specifies the foreign key as follows : 

 Create Table table_name 

 (Column_name 1 data type [NOT NULL], 

 : 

 : 

 Column_name N data type [NOT NULL], 

 [constraint constraint_name 

 Foreign key (column_name F1 … Column_name FN) 

references referenced-table (column_name P1, … column_name 

PN

)]); 

table_name - is the name for the table. 

Column_name 1 through column_name N are the valid columns. 

constraint_name is the name given to foreign key. 

referenced_table - is the name of the table referenced by the foreign 

key declaration. 

column_name F1

 through column_name FN

 are the columns that 

compose the foreign key. 

Column_name P1

 through column_name PN

 are the columns that 

compose the primary key in referenced-table. 



Comments

Post a Comment

Popular posts from this blog

what is computer network and their types ?

   what is network ? -  A network is a group of two or more computer systems, which are connected together. It also consist of a collection of computers, printers, scanner and other devices that are liked together. Networking has single purpose to share information as fast as possible.   The basic types of network- 1) LAN - LAN stands for Local Area Network. It is used to network computers within a limited area like office, school by using the network media. 2) CAN - Campus Area Network is an interconnection of local area networks within limited geographical area. campus network can be additional to the set of wireless connections. connects several buildings to the same network. Example, corporate office campus, university. 3) MAN - MAN means Metropolitan Area Network, which optimized for a larger geographic area than a LAN, ranging from several blocks of buildings to entire city. 4) WAN - WAN is Wide Area Network that is network connection of wide area such as the w...
Post a Comment
Read more

Levels of Software Testing

   Levels of Software Testing There are various testing levels one of which is unit testing in which the smallest testable part of an application is testing for correctness. In integration testing we check the system when we linking  the various modules.  In system testing we check the system as a whole from customers’ viewpoint. Acceptance testing tries to check whether the system is acceptable by most of the users. Alpha testing is carried out at developer’s site and beta is at customer’s site. A Testers workbench is a virtual environment used to verify the correctness or soundness of a design or model. 11 step testing process is a experience based practical approach for solution to a test assignment.     UNIT TESTING -        Unit testing is a software development process in which the smallest testable parts of an application, called units, are individually and independently scrutinized for proper operation. Unit testing is often...
Post a Comment
Read more

PL / SQL IN DBMS

                                          PL / SQL   Pl/SQL    state Loops in PL/SQL   Built in Function     Cursor Management     Exception INTRODUCTION   PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages,  resulting in a structural language that is more powerful than  SQL.PL/SQL is not case sensitive. ‘C’ style comments (/* ……… */)  may be used in PL/SQL programs whenever required.   All PL/SQL programs are made up of blocks, each block performs  a logical action in the program. A PL/SQL block consists of three parts  1. Declaration section   2. Executable section   3. Exception handling section  Only the executable section is required. The other sections are  optional.  A PL/SQL block h...
Post a Comment
Read more