Database Questions and Answers – Querying database part-1 DDL

This set of Database Multiple Choice Questions & Answers (MCQs) focuses 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

advertisement
advertisement

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
View Answer

Answer: c
Explanation: 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
View Answer

Answer: c
Explanation: 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
View Answer

Answer: b
Explanation: 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
View Answer

Answer: a
Explanation: References (table_name) give the prior table name for the entry.
advertisement

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)
View Answer

Answer: a
Explanation: 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 .

advertisement
(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
View Answer

Answer: b
Explanation: 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
View Answer

Answer: c
Explanation: 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
View Answer

Answer: a
Explanation: 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);
View Answer

Answer: d
Explanation: 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 ;
View Answer

Answer: b
Explanation: Droping the table drops all the references to that table.

Sanfoundry Global Education & Learning Series – Database Management System.

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.