1
DBMS I unit and SQL Questions and Answers
Part A (2 Marks Questions)
1. Explain Referential Integrity.
Referential Integrity means relationship between tables. Foreign keys are
used. Foreign key is the column whose values are derived from the Primary key
of the same or some other table.
Format for creating a foreign key is given below.
Syntax: create table (columnname data type (size) constraint
constraint_name references parent table name);
2. Define Instances and schemas.
Instances:
The collection of information stored in the database at a particular moment
is called the instance of the database.
Schemas:
The overall design of the database is called the schema of the database.
3. Define and explain the two types of Data Independence.
Two types of data independence are
i. Physical data independence
ii. Logical data independence
Physical data independence:
The ability to modify the physical schema without causing application
programs to be rewritten in the higher levels. Modifications in physical level
occur occasionally whenever there is a need to improve performance.
Logical data independence:
The ability to modify the logical schema without causing application
programs to be rewritten in the higher level (external level). Modifications in
physical level occur frequently more than that in physical level, whenever there is
an alteration in the logical structure of the database.
4. Define transaction.
A transaction is a collection of operations that performs a single logical
function in a database application. Each transaction is a unit of both atomicity and
consistency. Properties of transaction are atomicity, consistency, isolation, and
durability.
5. Define the type types of DML.
Two types:
Procedural DML
Non-procedural DML
Procedural DML:
It requires a user to specify what data are needed and how to get those
data.
2
Non-Procedural DML:
It requires a user to specify what data are needed without specifying how
to get those data.
6. List out the functions of DBA.
Schema definition
Storage structure and access-method definition
Schema and physical modification
Granting of authorization for data access
Integrity constraint specification
7. What is the need for DBA?
The need of DBA is to have central control of both the data and the
programs that access those data. The person who has such central control
over the system is called the DataBase Administrator.
8. Explain DML pre-compiler.
DML precompiler converts DML statements embedded in an application
program to normal procedure calls in the host language. The precompiler
must interact with the DML compiler to generate the appropriate code.
9. Define file manager and buffer manager.
File manager:
File manager manages the allocation of space on disk storage and the data
structures used to represent information stored on disk.
Buffer manager:
Buffer manager is responsible for fetching data from the disk storage into
the main memory, and deciding what data to cache in memory.
10. Define Data Dictionary.
DDL statements are compiled into a set of tables that is stored in a special
file called data dictionary. Data dictionary contains the meta-data, which in turn is
data about the data.
11. Give the syntax for creating the table with composite primary key.
Multicolumn primary key is called composite primary key.
Syntax: create table (columnname1 data type (size), columnname2
data type (size), constraint constraint_name primary key (columnname1,
columnname2));
12. Write a query to update the account table to replace value 500 to 450 for the
account number ‘A-101’.
Query: update account set balance=450 where acc_no=’A-101’;
3
13. Write a query to display loan number, branch name where loan amount is
between 500 and 1000 using comparison operators.
Query: select loan_no, branch_name from loan where amount>=500 and
amount<=1000;
14. Find all customers having a loan, an account or both at the bank.
select cust_name from depositor union select cust_name from borrower;
15. Find all customers who have an account but no loan at the bank.
select cust_name from depositor minus select cust_name from borrower;
16. Find the names and loan numbers of all the customers who have a loan at the
Perryridge branch.
Query: select distinct cust_name, borrower.loan_no as LID from borrower, loan
where borrower.loan_no=loan.loan_no and branch_name='perryridge';
17. Find the number of depositors for each branch.
Query: select branch_name, count (distinct cust_name) from depositor, account
where depositor.acc_no=account.acc_no Group by branch_name;
18. Find the names of all branches with customers who have an account in the bank
and who live in the Harrison city using Equi-join.
Query: select branch_name from customer,account,depositor where
cust_city='harrison' and customer.cust_name=depositor.cust_name and
depositor.acc_no=account.acc_no;
19. Find all customers who are borrowers from the bank and who appear in the list
of depositors.
Query: select distinct cust_name from borrower where cust_name in (select
cust_name from depositor);
20. Find the names of all branches with customers who have an account in the bank
and who live in the Harrison city using Sub-Queries.
Query: select branch_name from account where acc_no in(select acc_no from
depositor where cust_name in(select cust_name from customer where
cust_city='harrison'));
21. Select the rows from borrower such that the loan numbers are lesser than any
loan number where the branch name is Downtown.
Query: select * from borrower where loan_no < any (Select loan_no from loan
where branch_name='downtown');
22. Explain the use of sequence database object and give an example query to
create a sequence.
Sequence is used for the automatic generation of primary or unique key
integer value.
4
Query: create sequence seq1 increment by 1 start with 100 maxvalue 105
minvalue 100 cycle cache 4;
23. Define self-join and give an example query to illustrate self-join with a sample
table.Joining of a table to itself is called self-join. i.e., it joins one row in a table
to another row.
Query: select emp.emp_name, mngr.emp_name from employ emp, employ mngr
where emp.manager_no=mngr.emp_no;
Sample table: Employ
Emp_no Emp_name Manager_no
E001 Basu navindgi E002
E002 Rukmini E005
E003 Carol E004
E004 Cynthia -
E005 Ivan -
24. Give the query for updating a new table, consumer with the street as ‘South’ if
the customer is living in ‘North’.
Query: Update consumer set cust_street = ‘south’ where cust_name =(select
cust_name from customer where cust_street=’north’);
25. What is a view and give an example query to create a view from an existing
table.
Any relation that is not a part of the logical model but which is made
visible to the user as a virtual (imaginary) relation is called a view.
Query: create view custall (name, city) as (Select cust_name, cust_city from
customer);