See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions
GATE
GMAT
CBSE
NCERT
Career
Interview
Railway
UPSC
NID
NIFT-UG
NIFT-PG
PHP
AJAX
JavaScript
Node Js
Shell Script
Research
Database Management System 10CS54 VTU unit-5
UNIT 5 SQL The Relational Database Standard
5.1 Update Statements in SQL
The Insert Command
INSERT INTO EMPLOYEE
VALUES (‘Richard’,’K’,’Marini’,653298653’,’30dec52’,98 Oak Forest, Katy,
TX’,’M’,37000,’987654321’,4)
More on Insert
Use explicit attribute names:
INSERT INTO EMPLOYEE (FNAME, LNAME,SSN)
VALUES (‘Richard’,’Marini’, ‘653298653’
The DELECT Command
DELETE FROM EMPLOYEE
WHERE LNAME=‘Brown’
The UPDATE Command
Used to modify values of one or more selected tuples
Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5
respectively
UPDATE PROJECT
SET PLOCATION = ‘Bellaire’, DNUM=5
Where PNUMBER=10;
5.2 Views in SQL
A view refers to a single table that is derived from other tables
CREATE VIEW WORKS_ON1
AS SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER
More on View
CREATE VIEW DEPT_INFO(DEPT_NAME, NO_OF_EMPLS, TOTAL_SAL)
AS SELECT DNAME, COUNT(*), SUM(SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME
More on view
Treat WORKS_ON1 like a base table as follows
SELECT FNAME, LNAME
FROM WORKS_ON1
WHERE PNMAE=‘PROJECTX’
Main advantage of view:
Simplify the specification of commonly used queries
More on View
A View is always up to date;
A view is realized at the time we specify(or execute) a query on the view
DROP VIEW WORKS_ON1
Updating of Views
Updating the views can be complicated and ambiguous
In general, an update on a view on defined on a single table w/o any aggregate functions can be
mapped to an update on the base table
More on Views
We can make the following observations:
A view with a single defining table is updatable if we view contain PK or CK of the base table
View on multiple tables using joins are not updatable
View defined using grouping/aggregate are not updatable
Specifying General Constraints
Users can specify certain constraints such as semantics constraints
CREATE ASSERTION SALARY_CONSTRAINT
CHECK ( NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M,
DEPARTMENT D
WHERE E.SALARY > M. SALARY AND E.DNO=D.NUMBER AND D.MGRSSN=M.SSN))
5.3 Additional features
Granting and revoking privileges
Embedding SQL statements in a general purpose languages (C, C++, COBOL, PASCAL)
SQL can also be used in conjunction with a general purpose programming language, such as
PASCAL, COBOL, or PL/I. The programming language is called the host language. The
embedded SQL statement is distinguished from programming language statements by prefixing
it with a special character or command so that a preprocessor can extract the SQL statements. In
PL/I the keywords EXEC SQL precede any SQL statement. In some implementations, SQL
statements are passed as parameters in procedure calls. We will use PASCAL as the host
programming language, and a "$" sign to identify SQL statements in the program. Within an
embedded SQL command, we may refer to program variables, which are prefixed by a "%" sign.
The programmer should declare program variables to match the data types of the database
attributes that the program will process.These program variables may or may not have names
that are identical to their corresponding attributes.
Example: Write a program segment (loop) that reads a social security number and prints out
some information from the corresponding EMPLOYEE tuple
E1: LOOP:= 'Y';
while LOOP = 'Y' do
begin
writeln('input social security number:');
readln(SOC_SEC_NUM);
$SELECT FNAME, MINIT, LNAME, SSN, BDATE,
ADDRESS, SALARY
INTO %E.FNAME, %E.MINIT, %E.LNAME, %E.SSN,
%E.BDATE, %E.ADDRESS, %E.SALARY
FROM EMPLOYEE
WHERE SSN=%SOC_SEC_NUM ;
writeln( E.FNAME, E.MINIT, E.LNAME, E.SSN,
E.BDATE, E.ADDRESS, E.SALARY);
writeln('more social security numbers (Y or N)? ');
readln(LOOP)
end;
In E1, a single tuple is selected by the embedded SQL query; that is why we are able to assign
its attribute values directly to program variables. In general, an SQL query can retrieve many
tuples. The concept of a cursor is used to allow tupleatatime processing by the PASCAL
programCURSORS: We can think of a cursor as a pointer that points to a single tuple (row)
from the result of a query.The cursor is declared when the SQL query command is specified. A
subsequent OPEN cursor command fetches the query result and sets the cursor to a position
before the first row in the result of the query; this becomes the current row for the cursor.
Subsequent FETCH commands in the program advance the cursor to the next row and copy its
attribute values into PASCAL program variables specified in the FETCH command. An implicit
variable SQLCODE communicates to the program the status of SQL embedded commands. An
SQLCODE of 0 (zero) indicates successful execution. Different codes are returned to indicate
exceptions and errors. A special END_OF_CURSOR code is used to terminate a loop over the
tuples in a query result. A CLOSE cursor command is issued to indicate that we are done with
the result of the query
When a cursor is defined for rows that are to be updated the clause FOR UPDATE OF must be
in the cursor declaration, and a list of the names of any attributes that will be updated
follows.The condition WHERE CURRENT OF cursor specifies that the current tuple is the one
to be updated (or deleted)
Example: Write a program segment that reads (inputs) a department name, then lists the names
of employees who work in that department, one at a time. The program reads a raise amount for
each employee and updates the employee's salary by that amount.
E2: writeln('enter the department name:'); readln(DNAME);
$SELECT DNUMBER INTO %DNUMBER
FROM DEPARTMENT
WHERE DNAME=%DNAME;
$DECLARE EMP CURSOR FOR
SELECT SSN, FNAME, MINIT, LNAME, SALARY
FROM EMPLOYEE
WHERE DNO=%DNUMBER
FOR UPDATE OF SALARY;
$OPEN EMP;
$FETCH EMP INTO %E.SSN, %E.FNAME, %E.MINIT,
%E.LNAME, %E.SAL;
while SQLCODE = 0 do
begin
writeln('employee name: ', E.FNAME, E.MINIT, E.LNAME);
writeln('enter raise amount: '); readln(RAISE);
$UPDATE EMPLOYEE SET SALARY = SALARY + %RAISE
WHERE CURRENT OF EMP;
$FETCH EMP INTO %E.SSN, %E.FNAME, %E.MINIT,
%E.LNAME, %E.SAL;
end;
$CLOSE CURSOR EMP;
5.4 Database Programming
Objective:
To access a database from an application program (as opposed to interactive
interfaces)
Why?
An interactive interface is convenient but not sufficient
A majority of database operations are made thru application programs
(increasingly thru web applications)
Embedded commands:
Database commands are embedded in a general-purpose programming language
Library of database functions:
Available to the host language for database calls; known as an API
API standards for Application Program Interface
A brand new, full-fledged language
Minimizes impedance mismatch
Impedance Mismatch
Incompatibilities between a host programming language and the database model, e.g.,
type mismatch and incompatibilities; requires a new binding for each language
set vs. record-at-a-time processing
need special iterators to loop over query results and manipulate individual
values
Client program opens a connection to the database server
Client program submits queries to and/or updates the database
When database access is no longer needed, client program closes (terminates) the
connection
5.5 Embedded SQL
Most SQL statements can be embedded in a general-purpose host programming language
such as COBOL, C, Java
An embedded SQL statement is distinguished from the host language statements by
enclosing it between EXEC SQL or EXEC SQL BEGIN and a matching END-EXEC or
EXEC SQL END (or semicolon)
Syntax may vary with language
Shared variables (used in both languages) usually prefixed with a colon (:) in SQL
Variables inside DECLARE are shared and can appear (while prefixed by a colon) in
SQL statements
SQLCODE is used to communicate errors/exceptions between the database and the
program
int loop;
EXEC SQL BEGIN DECLARE SECTION;
varchar dname[16], fname[16], …;
char ssn[10], bdate[11], …;
int dno, dnumber, SQLCODE, …;
EXEC SQL END DECLARE SECTION;
Connection (multiple connections are possible but only one is active)
CONNECT TO server-name AS connection-name
AUTHORIZATION user-account-info;
Change from an active connection to another one
SET CONNECTION connection-name;
Disconnection
DISCONNECT connection-name;
loop = 1;
while (loop) {
prompt (“Enter SSN: “, ssn);
EXEC SQL
select FNAME, LNAME, ADDRESS, SALARY
into :fname, :lname, :address, :salary
from EMPLOYEE where SSN == :ssn;
if (SQLCODE == 0) printf(fname, …);
else printf(“SSN does not exist: “, ssn);
prompt(“More SSN? (1=yes, 0=no): “, loop);
END-EXEC
A cursor (iterator) is needed to process multiple tuples
FETCH commands move the cursor to the next tuple
CLOSE CURSOR indicates that the processing of query results has been completed
Objective:
5.6 Dynamic SQL
Composing and executing new (not previously compiled) SQL statements at run-time
a program accepts SQL statements from the keyboard at run-time
a point-and-click operation translates to certain SQL query
Dynamic update is relatively simple; dynamic query can be complex
because the type and number of retrieved attributes are unknown at compile time
EXEC SQL BEGIN DECLARE SECTION;
varchar sqlupdatestring[256];
EXEC SQL END DECLARE SECTION;
…prompt (“Enter update command:“, sqlupdatestring);
EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring;
EXEC SQLSQLJ: a standard for embedding SQL in Java
An SQLJ translator converts SQL statements into Java
These are executed thru the JDBC interface
Certain classes have to be imported
E.g., java.sql
EXECUTE sqlcommand;
Environment record:
Keeps track of database connections
Connection record:
Keep tracks of info needed for a particular connection
Statement record:
Keeps track of info needed for one SQL statement
Description record:
Keeps track of tuples
Load SQL/CLI libraries
Declare record handle variables for the above components (called: SQLHSTMT,
SQLHDBC, SQLHENV, SQLHDEC)
Set up an environment record using SQLAllocHandle
Set up a connection record using SQLAllocHandle
Set up a statement record using SQLAllocHandle
Prepare a statement using SQL/CLI function SQLPrepare
Bound parameters to program variables
Execute SQL statement via SQLExecute
Bound query columns to a C variable via SQLBindCol
Use SQLFetch to retrieve column values into C variables
5.7 Database stored procedures and SQL/PSM
Persistent procedures/functions (modules) are stored locally and executed by the
database server
As opposed to execution by clients
Advantages:
If the procedure is needed by many applications, it can be invoked by any of them
(thus reduce duplications)
Execution by the server reduces communication costs
Enhance the modeling power of views
Disadvantages:
Every DBMS has its own syntax and this can make the system less portable
A stored procedure
CREATE PROCEDURE procedure-name (params)
local-declarations
procedure-body;
A stored function
CREATE FUNCTION fun-name (params) RETRUNS return-type
local-declarations
function-body;
Calling a procedure or function
CALL procedure-name/fun-name (arguments);
SQL/PSM:
Part of the SQL standard for writing persistent stored modules
SQL + stored procedures/functions + additional programming constructs
E.g., branching and looping statements
Enhance the power of SQL
CREATE FUNCTION DEPT_SIZE (IN deptno INTEGER)
RETURNS VARCHAR[7]
DECLARE TOT_EMPS INTEGER;
SELECT COUNT (*) INTO TOT_EMPS
FROM SELECT EMPLOYEE WHERE DNO = deptno;
IF TOT_EMPS > 100 THEN RETURN “HUGE”
ELSEIF TOT_EMPS > 50 THEN RETURN “LARGE”
ELSEIF TOT_EMPS > 30 THEN RETURN “MEDIUM”
ELSE RETURN “SMALL”
ENDIF;
Editors
Checkout our YouTube channel for video tutorials and learn more about Eduladder.
New updates
Career 2.0
You might be intrested on below oppertunities Show me All
Posted By:Eduladder
Posted By:Eduladder
Similar Questions
Notes
Database Management Systems - 10CS54 VTU notesManagement And Entrepreneurship Unit-1ADVANCED DBMS 10CS751CE2029 MANAGEMENT OF IRRIGATION SYSTEMSDatabase Management SystemDatabase Management System 10CS54 VTU unit-7TeradataSOFTWARE ENGINEERING(10IS51)Computer Applications in Business NotesCE 2026 TRAFFIC ENGINEERING AND MANAGEMENT