Page 57 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes This command is used to add new field DOB in Student table. It’s datatype is date. This is also
used for drop column from the table. It will drop the DOB field by query given below:
Alter table Student drop column DOB;

Drop Command

The drop table command is used to delete a table and all rows in the table.

To delete an entire table including all of its rows, issue the drop table command followed by the
tablename. drop table is different from deleting all of the records in the table.
Deleting all of the records in the table leaves the table including column and constraint
information. Dropping the table removes the table definition as well as all of its rows.
drop table “tablename”

Example: drop table myemployees;

3.9 Set Operations

The SQL operations union, intersect, and except operate on relations and correspond to the
relational-algebra operations U, n, and -. Like union, intersection, and set difference in relational
algebra, the relations participating in the operations must be compatible; that is, they must have
the same set of attributes.
select customer-name
from depositor

and the set of customers who have a loan at the bank, which can be derived by
select customer-name
from borrower

You will refer to the relations obtained as the result of the preceding queries as d and b,
respectively.

The Union Operation

To find all customers having a loan, an account, or both at the bank, we write
(select customer-name
from depositor)
union
(select customer-name
from borrower)

The union operation automatically eliminates duplicates, unlike the select clause. Thus, in the
preceding query, if a customer-say, Jones-has several accounts or loans (or both) at the bank,
then Jones will appear only once in the result.
If we want to retain all duplicates, we must write union all in place of union:
(select customer-name from depositor)
union all (select Customer-name from borrower)

50 LOVELY PROFESSIONAL UNIVERSITY