DBMS: Questions With Precise Answers (Graded A+)
The database schema is written in
(A) HLL (B) DML (C) DDL (D) DCL Right Ans - (C) DDL Data Definition
Language
The language used in application programs to request data from the DBMS is
referred to as
(A) DML (B) DDL (C) VDL (D) SDL Right Ans - (A) DML Data Manipulation
Language
Count function in SQL returns the number of
(A) values (B) distinct values (C) groups (D) columns Right Ans - (A) values
Which is not a DDL statement ?
(A) Create (B) Alter (C) Delete (D) Drop Right Ans - (B) Alter
What restrictions apply to the use of the aggregate functions within the
SELECT statement? How do nulls affect the aggregate functions? Right Ans
- Except count function other operation will remove nulls and then execute.
Count function will calculate null value as one result
List the order in which the WHERE, GROUP BY, and HAVING clauses are
executed by the database in the following SQL statement. Right Ans - 1.
First Where 2. Second Group by 3. Third Having
Explain how the GROUP BY clause works. What is the difference between
WHERE and HAVING clauses? Right Ans - Group by: find same value of
group field and distinct the same values and merge row together.
where filter original data
having filter after group by data.
Can the ANY and ALL operators be used on the DATE data type? Write a
simple query to prove your answer. Right Ans - 1. SELECT * from Employee
e WHERE e.DOB > ANY(SELECT DOB from Employee WHERE empID='2')
2. SELECT * from Employee e WHERE e.DOB > ALL(SELECT DOB from
Employee WHERE empID='2')
The following SQL lists staffs who work in branch at '163 Main St'.
, SELECT staffNo, fName, lName, position FROM StaffWHERE branchNo =
(SELECT branchNoFROM BranchWHERE street = '163 Main St');
Will there be any problem with this query if there is more than one branch at
'163 Main St'? If yes, then explain the problem and right down the correct
query. Right Ans - 1. if have more than one branch. The results only can list
one staff who work in branchat'163 Main St'
2. Correct query: SELECT staffNo, fName, lName, position FROM Staff WHERE
branchNo IN (SELECT branchNo FROM Branch WHERE street = '163 Main
St');
What is Referential integrity constraint? Right Ans - Value of Foreign key
must refer to the exist value in parent table
What is the difference between primary key and unique key? Right Ans - 1:
primary key cannot be null. Unique key can be null.2: In a table just allow one
primary key exist. Unique can set in a lot of fields
List two examples of database systems other than those listed in Section 1.1 of
the book. Right Ans - 1. Deposit and withdraw money from your debit card.
2. Employee information in company internal system.
Explain what is meant by a database management system, and contrast it with
a File Management System. Right Ans - Database management system can
let program and data independent ,physical structure and store data are
managed by DBMS. also can share a whole resources and can control access
this data compare to a file management system.
Discuss advantages and disadvantages of DBMSs in short. Right Ans - 1.
Advantages: More easy to query data. access control, sharing data,
maintenance,
data independent.2. Disadvantages : database design is complex, cost of DBMS
, hardware cost, size.
What are the 5 major components of the DBMS environment? Right Ans -
hardware, software, data, procedure, people.
A relational database consists of a collection of
A. Tables B. Fields C. Records D. Keys Right Ans - A. Tables
The database schema is written in
(A) HLL (B) DML (C) DDL (D) DCL Right Ans - (C) DDL Data Definition
Language
The language used in application programs to request data from the DBMS is
referred to as
(A) DML (B) DDL (C) VDL (D) SDL Right Ans - (A) DML Data Manipulation
Language
Count function in SQL returns the number of
(A) values (B) distinct values (C) groups (D) columns Right Ans - (A) values
Which is not a DDL statement ?
(A) Create (B) Alter (C) Delete (D) Drop Right Ans - (B) Alter
What restrictions apply to the use of the aggregate functions within the
SELECT statement? How do nulls affect the aggregate functions? Right Ans
- Except count function other operation will remove nulls and then execute.
Count function will calculate null value as one result
List the order in which the WHERE, GROUP BY, and HAVING clauses are
executed by the database in the following SQL statement. Right Ans - 1.
First Where 2. Second Group by 3. Third Having
Explain how the GROUP BY clause works. What is the difference between
WHERE and HAVING clauses? Right Ans - Group by: find same value of
group field and distinct the same values and merge row together.
where filter original data
having filter after group by data.
Can the ANY and ALL operators be used on the DATE data type? Write a
simple query to prove your answer. Right Ans - 1. SELECT * from Employee
e WHERE e.DOB > ANY(SELECT DOB from Employee WHERE empID='2')
2. SELECT * from Employee e WHERE e.DOB > ALL(SELECT DOB from
Employee WHERE empID='2')
The following SQL lists staffs who work in branch at '163 Main St'.
, SELECT staffNo, fName, lName, position FROM StaffWHERE branchNo =
(SELECT branchNoFROM BranchWHERE street = '163 Main St');
Will there be any problem with this query if there is more than one branch at
'163 Main St'? If yes, then explain the problem and right down the correct
query. Right Ans - 1. if have more than one branch. The results only can list
one staff who work in branchat'163 Main St'
2. Correct query: SELECT staffNo, fName, lName, position FROM Staff WHERE
branchNo IN (SELECT branchNo FROM Branch WHERE street = '163 Main
St');
What is Referential integrity constraint? Right Ans - Value of Foreign key
must refer to the exist value in parent table
What is the difference between primary key and unique key? Right Ans - 1:
primary key cannot be null. Unique key can be null.2: In a table just allow one
primary key exist. Unique can set in a lot of fields
List two examples of database systems other than those listed in Section 1.1 of
the book. Right Ans - 1. Deposit and withdraw money from your debit card.
2. Employee information in company internal system.
Explain what is meant by a database management system, and contrast it with
a File Management System. Right Ans - Database management system can
let program and data independent ,physical structure and store data are
managed by DBMS. also can share a whole resources and can control access
this data compare to a file management system.
Discuss advantages and disadvantages of DBMSs in short. Right Ans - 1.
Advantages: More easy to query data. access control, sharing data,
maintenance,
data independent.2. Disadvantages : database design is complex, cost of DBMS
, hardware cost, size.
What are the 5 major components of the DBMS environment? Right Ans -
hardware, software, data, procedure, people.
A relational database consists of a collection of
A. Tables B. Fields C. Records D. Keys Right Ans - A. Tables
