Database Management System Module 4

MODULE 4
CHAPTER1:SQL(Advanced Queries)

More Complex SQL Retrieval Queries

We described some basic types of retrieval queries in SQL. Because of the generality and
expressive power ofthe language, there are many additional features that allow users to specify
more complex retrievals from the database.

Comparisons Involving NULL and Three-Valued Logic

SQL has various rules for dealing with NULL values. NULL is used to represent a missing value, but
that it usually has one of three different interpretations—value unknown (exists but is not known),value
not available (exists but is purposely withheld),or value not applicable (the attribute is undefined for this
tuple).

Consider the following examples to illustrate each of the meanings of NULL.

1. Unknown value. A person’s date of birth is not known, so it is represented by NULL in the database.
2. Unavailable or with held value. A person has a home phone but does not want it to be listed, so it
is withheld and represented as NULL in the database.
3. Not applicable attribute. An attribute Last College Degree would be NULL for a person
who has no college degrees because it does not apply to that person.
It is often not possible to determine which of the meanings is intended; for example, a NULL for
the home phone of a person can have any of the three meanings. Hence, SQL does not distinguish
between the different meanings of NULL.
In general, each individual NULL value is considered to be different from every other
NULLvalue in the various database records. When a NULL is involved in a comparison operation, the
result is considered to be UNKNOWN (it may be TRUE or it may be FALSE). Hence, SQL uses athree-
valued logic with values TRUE, FALSE, and UNKNOWN instead of the standard two-valued
(Boolean) logic with values TRUE or FALSE. It is therefore necessary to define the results (or truth
values) of three-valued logical expressions when the logical connectives AND, OR, andNOT areused.
Table 5.1 shows the resulting values.

InTables5.1(a)and5.1(b),therowsandcolumnsrepresentthevaluesoftheresultsofcomparison
conditions,whichwouldtypicallyappear intheWHEREclauseofanSQLquery. Eachexpressionresult

Dept of AIML,CBIT-Kolar Page1

Database Management System Module 4
would have a value of TRUE, FALSE, or UNKNOWN. The result of combining the two values using
theAND logical connective is shown by the entries inTable5.1(a).Table5.1(b)shows the result of using
the OR logical connective . For example, the result of (FALSE AND UNKNOWN) is FALSE, whereas
the result of (FALSE OR UNKNOWN) is UNKNOWN. Table 5.1(c) shows the result of the NOT
logical operation. Notice that in standard Boolean logic, only TRUE or FALSE values are permitted;
there is no UNKNOWN value.
SQL allows queries that check whether an attribute value is NULL. Rather than using= or <> to
compare an attribute value to NULL,SQL uses the comparison operators IS or IS NOT. This is because
SQL considers each NULL value as being distinct from every other NULL value, so equality
comparison is not appropriate.
Query18. Retrieve the names of all employees who do not have supervisors.
SELECT Fname, Lname
FROM EMPLOYEE WHERE Super_ssn ISNULL;

Nested Queries, Tuples, and Set/Multiset Comparisons

Some queries require that existing values in the database be fetched and then used in a
comparison condition. Such queries can be conveniently formulated by using nested queries, which are
complete select-from- where blocks within the WHERE clause of another query. That other query is
called the outer query.

IN operator:
Which is a comparison operator that compares a valuev with a set (or multiset) of values Vand
evaluates to TRUE if v is one of the elements in V.
ex: Query 17. Retrieve the Social Security numbers of all employees who work on project
numbers1, 2,or 3.

SELECT DISTINCT Essn

FROM WORKS_ON WHERE Pno IN(1,2,3);

SQL allows the use of tuples of values in comparisons by placing them within parentheses. To
illustrate this, consider the following query:
SELECT DISTINCT Essn
FROM WORKS_ON WHERE (Pno ,Hours)IN(SELECT Pno, Hours
FROM WORKS_ONWHERE Essn=‘123456789’);
This query will select the Essns of all employees who work the same (project, hours)
combination on some project that employee ‘John Smith’ (whose Ssn = ‘123456789’) works on. In this
example, the IN operator compares the subtuple of values in parentheses(Pno, Hours)with in each tuple
in WORKS_ON with the set of type- compatible tuples produced by the nested query.
In addition to the IN operator, a number of other comparison operators can be used to compare a
single value v (typically an attribute name) to a set or multiset v (typically a nested query).The = ANY
(or = SOME) operator returns TRUE if the value v is equal to some value in the set V and is hence
equivalent to IN. The two keywords ANY and SOME have the same effect. Other operators that can be
combined with ANY (or SOME) include >, >=,
<, <=, and <>. The keyword ALL can also be combined with each of these operators. For example, the
comparison condition (v>ALL V) returns TRUE if the value is greater than all the values in the set (or
multiset) V.
An example is the following query, which returns the names of employees whose salary is
greater than the salary of all the employees in department 5:
SELECT Lname, Fname
FROM EMPLOYEE WHERE Salary>ALL(SELECT Salary FROM
EMPLOYEEWHERE Dno=5 );

Dept of AIML,CBIT-Kolar Page2

Database Management System Module 4

Correlated Nested Queries

Whenever a condition in the WHERE clause of a nested query references some attribute of a relation
declared in the outer query, the two queries are said to be correlated. We can understand a correlated
query better by considering that the nested query is evaluated once for each tuple (or combination of
tuples) in the outer query.
Example for Correlated Nested Querie:
Retrieve the name of each employee who has a dependent with thesame first nameand is the samesex as
the employee.
Q16: SELECT E.Fname, E.Lname FROM
EMPLOYEE AS E WHERE E.Ssn IN ( SELECT
Essn FROM DEPENDENT ASD
WHERE E.Fname=D.Dependent_name AND E.Sex=D.Sex);

For example, we can think of below Query as follows: For each EMPLOYEE tuple, evaluate the nested
query, which retrieves the Essn values for all DEPENDENT tuples with the same sex and name as that
EMPLOYEE tuple; if the Ssn value of the EMPLOYEE tupleis in the result of the nested query,then
select that EMPLOYEE tuple.
In general query written with nested select-from-where blocks and using the = or IN comparison
operators can always be expressed as a single block query. For example,Q16 maybe written as inQ16A:
Q16A:SELECTE.Fname,E.Lname
FROMEMPLOYEEASE,DEPENDENTASD
WHEREE.Ssn=D.EssnANDE.Sex=D.SexANDE.Fname=D.Dependent_name;

TheEXISTSandUNIQUEFunctionsinSQL

Dept of AIML,CBIT-Kolar Page3

Database Management System Module 4
EXISTS and NOT EXISTS are typically used in conjunction with a correlated nested query. In Q16B,the
nested query references the Ssn, Fname, and Sex attributes of the EMPLOYEE relation from the outer
query.We can think of Q16Bas follows: For each EMPLOYEE tuple, evaluate the nested query, which
retrieves all DEPENDENT tuples with the same Essn, Sex, and Dependent_nameas the EMPLOYEE
tuple; if at least one tuple EXISTS in the result of thenested query, then select that EMPLOYEE tuple.
In general,EXISTS(Q)returnsTRUEifthereisatleastonetupleintheresultofthenested query Q,and it
returns FALSE otherwise.
On the other hand, NOTEXISTS(Q)returns TRUE if there are no tuples in the result of nested
query Q, and it returns FALSE otherwise. Next, we illustrate the use of NOT EXISTS.
Query6. Retrieve the names of employees who have no dependents.
SELECT
Fname,
Lname
FROM
EMPLOYE
E
WHERE NOTEXISTS (SELECT* FROM DEPENDENT
WHERESsn=Essn);

In Q6, the correlated nested query retrieves all DEPENDENT tuples related to a particular
EMPLOYEE tuple. If none exist, the EMPLOYEE tuple is selected because the WHERE-clause
condition will evaluate to TRUE in this case. We can explain Q6 as follows: For each EMPLOYEE
tuple, the correlated nested query selects all DEPENDENT tuples whose Essn value matches the
EMPLOYEE Ssn; if the result isempty, no dependents are related to the employee, so we select that
EMPLOYEE tuple and retrieve its Fname and Lname.
Explicit Sets and Renaming of Attributes in
SQL Explicit Sets
We have seen several ueries with a nested query in the WHERE clause.It is also possibletouse
an explicitset of values in the WHERE clause, rather than a nested query.Such a set is enclosed in
parentheses in SQL.
Query17.RetrievetheSocialSecuritynumbersofallemployeeswhoworkonproject numbers 1,2,or3.
SELECT DISTINCT Essn FROM WORKS_ON WHERE Pno IN(1,2,3);

Renaming of Attributes
In SQL,it is possible to rename any attribute that appears in the result of a query by adding the
qualifier AS followed by the desired new name. Hence, the AS construct can be used to alias both
attribute and relation names ,and it can be used in both the SELECT and FROM clauses.
For example, to retrieve the last name of each employee and his or her supervisor,while
renamingtheresultingattributenamesasEmployee_nameandSupervisor_name.Thenewnameswill appear as
column headers in the query result.
SELECT E.Lname AS Employee_name, S.Lname AS
Supervisor_name FROM EMPLOYEEAS E,
EMPLOYEE AS S
WHEREE. Super_ssn=S.Ssn;

Joined Tables in SQL and Outer Joins

The concept of a joined table(or joined relation)was in corporate into SQL to permit users to

Dept of AIML,CBIT-Kolar Page4

Database Management System Module 4
specify a table resulting from a join operation in the FROM clause of a query. This construct may be
easier to comprehend than mixing together all the select and join conditions in the WHERE clause.
For example, consider queryQ1, which retrieves the name and address of every employee who
works for the ‘Research’ department. It may be easier to specify the join of the EMPLOYEE and
DEPARTMENT relations first, and then tos elect the desired tuples and attributes. This can be written
in SQL as in Q1A:
Q1A:SELECT Fname,Lname,Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
The FROM clause in Q1Acontains a single joined table. The attributes of such a table are all the
attributes of the first table, EMPLOYEE, followed by all the attributes of the second table,
DEPARTMENT.
Theconceptofajoinedtablealsoallowstheusertospecifydifferenttypesofjoin,suchas
NATURAL JOIN and various types of OUTERJOIN.
In a NATURAL JOIN on two relations R and S,no join condition is specified; an implicit
EQUIJOIN condition for each pair of attributes with the same name from R and S is created. Each such
pair of attributes is included only once in the resulting relation.
If the names of the join attributes are not the same in the base relations, it is possible to rename
the attributes so that they match,and then to apply NATURAL JOIN. In this case, the AS construct can
be used to rename a relation and all its attributes in the FROM clause. This is illustrated in Q1B, where
the DEPARTMENT relation is renamed as DEPT and its attributes are renamed as Dname,Dno (to
match the name of the desired join attribute Dno in the EMPLOYEE table), Mssn, and Msdate. The
implied join condition for this NATURAL JOIN is EMPLOYEE.Dno=DEPT.Dno, because this is the
only pair of attributes with the same name after renaming:
Q1B:SELECT Fname,Lname,AddressFROM
(EMPLOYEE NATURAL JOIN(DEPARTMENTASDEPT(Dname,Dno,Mssn,Msdate)))
WHERE Dname=‘Research’;

The default type of join in a joined table is called an inner join, where a tuple is included in
the result only if a matching tuple exists in the other relation
There are a variety of outer joinoperations.
1) LEFTOUTER JOIN (every tuple in the left table must appear in the result;if it
does not haveamatchingtuple,it ispaddedwithNULLvalues for theattributesofthe
right table).
2) RIGHTOUTERJOIN(everytupleinthe righttablemustappearinthe result;if
itdoesnothavea matchingtuple,it is padded withNULLvalues for theattributes of the
left table).
3) FULLOUTERJOIN:Itisacombinationofleftandrightouterjoins.
In the latter three options, the keyword OUTER may be omitted.If the join attributes have the
same name,one can also specify the natural join variation of outer joins by using the keyword
NATURAL before the operation (for example, NATURAL LEFT OUTER JOIN).
The keyword CROSS JOIN is used to specify the CARTESIAN PRODUCT
operationalthoughthis shouldbeused only withtheutmost carebecauseit generates allpossible
tuplecombinations.
It is also possible to nest join specifications; that is, one of the tables in a join may itself be a
joined table. This allows the specification of the join of three or more tables as a single joined
table,which is called a multiway join.

EX: SELECT Pnumber, Dnum, Lname, Address, Bdate

FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber)
JOIN EMPLOYEE ON Mgr_ssn=Ssn)WHERE Plocation=‘Stafford’;

Dept of AIML,CBIT-Kolar Page5

Database Management System Module 4

Not all SQL implementations have implemented the new syntax of joined tables. In some systems, a
different syntax was used to specify outer joins by using the comparison operators+=, =+, and +=+ for
left, right, and full outer join, respectively, when specifying the join condition .For example, this syntax
is available in Oracle. To specify the left outer join using this syntax, we could write the query as
follows:
SELECTE. Lname, S.Lname
FROM EMPLOYEEE,EMPLOYEES
WHERE E.Super_ssn+=S.Ssn;

Aggregate Functions in SQL

Aggregate functions are used to summarize information from multiple tuples into a single-tuple
summary. Grouping is used to create subgroups of tuples before summarization. Grouping and
aggregation are required in many database applications, and we will introduce their use in SQL through
examples.
A number of built-in aggregate functions exist: COUNT, SUM, MAX, MIN, and AVG.The
COUNT function returns the number of tuples or values as specified in a query.The functions
SUM,MAX, MIN,and AVG can be applied to a set or multiset of numeric values and return,
respectively,the sum,maximum value,minimum value, and average (mean) of those values.
Query19. Find the sum of the salaries of all employees, the maximum salary, the minimum
salary, andthe average salary.
SELECT SUM(Salary),MAX(Salary),MIN(Salary),AVG
(Salary)FROM EMPLOYEE;

If we want to get the preceding function values for employees of a specific department—say, the
‘Research’ department—we can write Query 20, where the EMPLOYEE tuples are restricted by the
WHERE clause to those employees who work for the ‘Research’department.
Query 20. Find the sum of the salaries of all employees of the ‘Research’ department, as
well as the maximum salary,the minimum salary,and the average salary in this department.
SELECT SUM (Salary), MAX (Salary), MIN (Salary),
AVG(Salary)FROM(EMPLOYEEJOINDEPARTMENT
AND no=Dnumber)
WHERE Dname=‘Research’;

Queries 21 and 22. Retrieve the total number ofemployees inthe company (Q21) and the number
of employees in the ‘Research’ department (Q22).

will not be counted .In general, NULL values are discarded when aggregate functions are applied to a
particular column (attribute).
Grouping: The GROUP BY and HAVING Clauses
GROUP BY clause
SQLhasaGROUPBYclause.TheGROUPBYclausespecifiesthegroupingattributes,which should also

Dept of AIML,CBIT-Kolar Page6

Database Management System Module 4
appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group
of tuples appears along with the value of the grouping attribute(s).
In many cases we want to apply the aggregate functions to subgroups of tuples in a relation, where the
subgroups are based on some attribute values. For example, we may want to find the average salary of
employees in each department or the number of employees who work on each project.In these cases we need
to partition the relation into non overlapping subsets (or groups) of tuples.Each group (partition) will consist
of the tuples that have the same value of some attribute(s), called the grouping attribute(s). We can then
apply the function to each such group independently to produce summary information about each group.

HAVINGclause
SQL provides a HAVING clause,which can appear in conjunction with a GROUPBY clause. HAVING
provides a condition on the summary information regarding the group of tuples associated with each
value of the grouping attributes. Only the groups that satisfy the condition are retrieved in the result of
the query.This is illustrated by Query 26.

Dept of AIML,CBIT-Kolar Page7

Database Management System Module 4

Dept of AIML,CBIT-Kolar Page8

Database Management System Module 4
DiscussionandSummaryofSQLQueries:

In order to formulate queries correctly, it is useful to consider the steps that define the meaning or
semantics of each query. A query is evaluated conceptually by first applying the FROMclause (to
identify all tables involved in the query or to materialize any joined tables), followed by the WHERE
clause to select and join tuples, and then by GROUP BY and HAVING. Conceptually, ORDER BY is
applied at the end to sort the query result.
Specifying Constraints as Assertions and Actions as Triggers
Inthis section,we introduce two additional featuresofSQL:theCREATEASSERTION
Statement and the
CREATE TRIGGER statement.

CREATE ASSERTION, which can be used to specify additional types of constraints that are
outside the scope of the built-in relational model constraints (primary and unique keys, entity integrity,
and referential integrity)that we presented early.
CREATE TRIGGER,which can be used to specify automatic actions that the database system
will perform when certain events and conditions occur.This typeof functionality is generally referred to
as active databases.

Specifying General Constraints as Assertions

in SQLASSERTIOS
In SQL,users can specify general constraints—those that do not fall into any of the categories described
via declarative assertions, using the CREATE ASSERTION statement of the DDL. Each assertion is
given a constraint name and is specified via a condition similar totheWHERE clauseof an SQL query.

Dept of AIML,CBIT-Kolar Page9

Database Management System Module 4

The basic technique for writing such assertions is to specify a query that selects any tuples that violate
the desired condition. By including this query inside a NOT EXISTS clause, the assertion will specify
that the result of this query must be empty so that the condition will always be TRUE. Thus,the
assertion is violated if the result of the query is not empty .In the preceding example,the query selects all
employees whose salaries are greater than the salary of the manager of their department. If the result of
the query is not empty,the assertion is violated.

Introduction to Triggers in SQL

Another important statement in SQL is CREATE TRIGGER. In many cases it is convenient to specify
the type of action to be taken when certain events occur and when certain conditions are satisfied.For
example,it may be useful to specify a condition that, if violated, causes some user to beinformed of the
violation.The CREATE TRIGGER statement is used to implement such actions in SQL.
A typical trigger has three components:
 Event: When this event happens, the trigger is activated
 Condition(optional):If the condition is true, the trigger executes ,otherwise skipped
 Action: The actions performed by the trigger
 Theactionistobeexecutedautomaticallyiftheconditionissatisfiedwheneventoccurs.

 Trigger: Events
Three event types
⚫ Insert
⚫ Update
⚫ Delete
Two triggering times
⚫ Before the event
⚫ After the event

Dept of AIML,CBIT-Kolar Page10

Database Management System Module 4
Two granularities
⚫ Execute for each row
⚫ Execute for each statement

Dept of AIML,CBIT-Kolar Page11

Database Management System Module 4
TriggerCondition:

Trigger:Action

Example1

Dept of AIML,CBIT-Kolar Page12

Database Management System Module 4
EXAMPLE2

Views (virtualtable)inSQL
Concept of a View in SQL

 A view is a single table that is derived from one or more base tables or other views
 Views neither exist physically nor contain data itself,it depends on the base tables for its
existence
 A view contains rows and columns, just like a real table.The fields in a view are fields
from one or more real tables in the database.
Specification of Views in SQLSyntax:
CREATE VIEW view_name AS SELECT column_name (s)FROM
table_name WHERE condition
Example
CREATEVIEWWORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT,WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber;
 We can specify SQL queries on view
Example #
 Retrieve the Last name and First name of all employees who work on ‘Product X’
SELECT
Fname,
Lname
FROM
WO RKS_ON1
WHERE Pname=‘ProductX’;
 A view always shows up-to-date
 If we modify the tuples in the base tables on which the view is defined, theview must
Automatically reflect these changes
 If we do not need a view any more, we can use the DROPVIEW command
DROPVIEW WORKS_ON1;

View Implementation and View Update View Implementation

 The problem of efficiently implementing a view for quering is
complex Two main approaches have been suggested

Dept of AIML,CBIT-Kolar Page13

Database Management System Module 4
Query Modification
 Modifying the view query into a query on the underlying base tables
 Disadvantage: in efficient for views defined via complex queries that are time-consuming to
execute, especially if multiple queries are applied othe view with in a short period of time.
Example
 The query example#wouldbeautomaticallymodifiedtothefollowingquerybytheDBMS
SELECT Fname, Lname
FROM EMPLOYEE,PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber AND Pname=”Product X’;

View Materialization
 Physically create a temporary view table when the view is first queried
 Keep that table on the assumption that other queries on the view will follow
 Requires efficient strategy for automatically updating the view table when the base tables are
updated, that is Incremental Update
 Incremental Update determines what new tuples must be inserted, deleted, or modified in a
materialized view table when a change is applied to one of the defining base table
View Update
 Updating of views is complicated and can be ambiguous
 An update on view defined on a single table without any aggregate functions can be mapped to
an update on the underlying base table under certain conditions.
 View involving joins, an update operation may be mapped to update operations on the
underlying base relations in multiple ways.
Example
 Update the Pname attribute of‘ johnsmith’ from‘ Product X’
to ‘Product Y’UPDATE WORKS_ON1
SET Pname=‘ProductY’
WHERE Lname=‘smith’
AND Fname=‘john’ AND Pname
= ‘ProductX’
 This query can be mapped into several updates on the base relations to give the desired effect
on the view.
 Two possible updates (a)and(b)on the base relations corresponding to above query.
(a) UPDATE WORKS_ON
SET Pno= (SELECT
PnumberFROMPROJECT
WHERE Pname=‘ProductY’)
WHERE EssnIN(SELECTSsn
FROMEMPLOYEE
WHERELname=‘smith’ANDFname=‘john’)
AND
Pno=(SELECT PnumberFROMPROJECT
WHERE Pname=‘ProductX’);

(b) UPDATE
PROJECT
SET
Pname=‘Pro
ductY’
WHEREPname=‘ProductX’;

Dept of AIML,CBIT-Kolar Page14

Database Management System Module 4
 Update(a) relates ‘john smith’ to the ‘Product Y’ PROJECT tuple in place of the ‘Product X’
PROJECT tuple and is the most likely update.
 Update (b) would also give the desired update effect on the view, but it accomplishes this by
changing the name of the ‘Product X’ tuple in the PROJECT relation to ‘Product Y’
OBSERVATIONSON VIEWS
 A view with a single defining table is updatable if the view attributes contain the primary key of
the base relation, as well as all attributes with the NOT NULL constraint that donot have
default values specified
 Views defined on multiple tables using joins are generally not updatable
 Views defined using grouping and aggregate functions are not updatable
 In SQL, the clause WITH HECKOPTION must be added at the end of the view definition if a
view is to be updated.
Advantages ofViews
 Data independence
 Currency
 Improvedsecurity
 Reducedcomplexity
 Convenience
 Customization
 Dataintegrity
SchemaChangeStatementsinSQL

In this section,we give an overview of the schema evolution commandsavailable in SQL, which can be
used to altera schema by adding or dropping tables, attributes, constraints, and other schema elements.
This can be done while the database is operational and does not require recompilation of the database
schema. Certain checks must be done by theDBMS to ensure that thechangesdo not affect therest of the
database and make it inconsistent.

TheDROPCommand
The DROP command can be used to drop named schemaelements, such as tables, domains, or
constraints.
One can also drop a schema. For example, if a whole schema is no longer needed, the DROP
SCHEMA command can be used. There are two drop behavior options: CASCADE and RESTRICT.
For example, toremovetheCOMPANYdatabaseschema andallitstables, domains, andother elements, the
CASCADE option is used as follows:

DROPSCHEMACOMPANYCASCADE;
If the RESTRICToption is chosen in place of CASCADE,the schema is dropped only if it has no
elements in it; otherwise, the DROP command will not be executed. To use the RESTRICT option, the
user must first individually drop each element intheschema,then drop theschema itself.

DROPTABLECOMMAND:
If a base relation within a schema is no longer needed,the relation and its definition can be deleted by
using the DROP TABLE command. For example, if we no longer wish to keep track of dependents of
employees in the COMPANY .we can get rid of the DEPENDENT relation by issuing the following
command:
DROPTABLEDEPENDENTCASCADE;
If the RESTRICT option is chosen instead of CASCADE, a table is dropped only if it is not referenced
inanyconstraints(forexample,byforeignkeydefinitionsinanotherrelation)orviewsorbyanyother

Dept of AIML,CBIT-Kolar Page15

Database Management System Module 4
elements. With theCASCADE option, all such constraints, views, and other elements that reference the
table being dropped are also dropped automatically fromthe schema, along with the table itself.
Notice thatthe DROPTABLEcommand notonly deletesall the recordsin the table if successful,but also
removes the table definition from the catalog.
TheALTERCommand
The definition of a base table or of other named schema elements can be changed by using the ALTER
command. For base tables, the possible alter table actions include adding or dropping a column
(attribute),changinga column definition,and adding or dropping table constraints.
For example, toadd an attribute for keeping track of jobs of employees to the EMPLOYEE baserelation in the
COMPANY schema ,we can use the command.
ALTERTABLECOMPANY.EMPLOYEEADDCOLUMNJobVARCHAR(12);

We must still enter a value for the new attribute Job for each individual EMPLOYEE tuple. This can be done
either byspecifyinga default clauseor byusingtheUPDATE commandindividuallyoneachtuple.Ifnodefault clause
is specified, the new attribute will have NULLs in all the tuples of the relation immediately after the command is
executed; hence, theNOT NULL constraint is not allowed in this case.

Dept of AIML,CBIT-Kolar Page16

Database Management System Module 4

Dept of AIML,CBIT-Kolar Page17