Database Multiple Choice Questions on “Querying database part-1 DDL”.
SQL data definition for part of the university database.
CREATE TABLE department (dept_name VARCHAR (20), building VARCHAR (15), budget NUMBER, PRIMARY KEY (dept_name)); CREATE TABLE course (course_id VARCHAR (7), title VARCHAR (50), dept_name VARCHAR (20), credits NUMERIC (2,0), PRIMARY KEY (course_id), FOREIGN KEY (dept_name) __________ department); CREATE TABLE instructor (ID VARCHAR (5), name VARCHAR (20) NOT NULL, dept_name VARCHAR (20), salary NUMERIC (8,2), FOREIGN KEY (dept_name) _______ department); CREATE TABLE SECTION (course_id VARCHAR (8), sec_id VARCHAR (8), semester VARCHAR (6), YEAR NUMERIC (4,0), building VARCHAR (15), room_number VARCHAR (7), time_slot id VARCHAR (4), PRIMARY KEY (course_id, sec_id, semester, YEAR), FOREIGN KEY (_______) ______ course); CREATE TABLE teaches (ID VARCHAR (5), course_id VARCHAR (8), sec_id VARCHAR (8), semester VARCHAR (6), YEAR NUMERIC (4,0), PRIMARY KEY (ID, course_id, sec_id, semester, YEAR), FOREIGN KEY (course_id, sec_id, semester, YEAR) REFERENCES SECTION, FOREIGN KEY (ID) _______ instructor);
Answer questions based on the above commands
1. Which is the main relation which is used in the university database which is referenced by all other relation of the university?
a) Teaches
b) Course
c) Department
d) Section
Answer: c
Clarification: Department is the only relation which forms the main part of the university database.
2. The department relation has the an entry budget whose type has to be replaced by
a) Varchar (20)
b) Varchar2 (20)
c) Numeric (12,2)
d) Numeric
Answer: c
Clarification: Department is the only relation which forms the main part of the university database.
3. In the course relation, the title field should throw an error in case of any missing title. The command to be added in title is
a) Unique
b) Not null
c) 0
d) Null
Answer: b
Clarification: By specifying not null the value cannot be left blank.
4. In the above DDL command the foreign key entries are got by using the keyword
a) References
b) Key reference
c) Relating
d) None of the mentioned
Answer: a
Clarification: References (table_name) give the prior table name for the entry.
5. Identify the error in the section relation
a) No error
b) Year numeric (4,0)
c) Building varchar (15)
d) Sec_id varchar (8)
Answer: a
Clarification: The building and the sec_id have varchar values and year is of numeric type. So no such errors are found in the relation.
6. The following entry is given in to the instructor relation .
(100202,Drake,Biology,30000)
Identify the output of the query given
a) Row(s) inserted
b) Error in ID of insert
c) Error in Name of insert
d) Error in Salary of the insert
Answer: b
Clarification: The varchar(5) value cannot hold the entry 100202.
7. Which of the following can be used as a primary key entry of the instructor relation.
a) DEPT_NAME
b) NAME
c) ID
d) All of the mentioned
Answer: c
Clarification: The value ID can only be primary key unlike dept_name which is used as a foreign key.
8. In the section relation which of the following is used as a foreign key?
a) Course_id
b) Course_id,sec_id
c) Room_number
d) Course_id,sec_id,room_number
Answer: a
Clarification: Course_id is the only field which is present in the course relation.
9. In order to include an attribute Name to the teaches relation which of the following command is used?
a) Alter table teaches include Name;
b) Alter table teaches add Name;
c) Alter table teaches add Name varchar;
d) Alter table teaches add Name varchar(20);
Answer: d
Clarification: The form of the alter table command is
alter table r add AD;
where r is the name of an existing relation, A is the name of the attribute to be added, and D is the type of the added attribute.
10. To replace the relation section with some other relation the initial step to be carried out is
a) Delete section;
b) Drop section;
c) Delete from section;
d) Replace section new_table ;
Answer: b
Clarification: Droping the table drops all the references to that table.