|MUS-562||Music Video Production||Music||2|
Answer the questions based on the above relations
1. Which of the following command is used to display the departments of the instructor relation ?
a) Select * from instructor where Dept_name = Finance;
b) Select * from instructor ;
c) Select dept_name from instructor;
d) Select dept_name for instructor where Name=Jackson;
Explanation: Only one field is necessary for the query and where clause is not needed for the selection.
2. How can we select the elements which have common Dept_name in both the relation ?
a) Select * from instructor i , course c where i.Dept_name=c.Dept_name;
b) Select Dept name from instructor ,Course ;
c) Select * from instructor i , course c ;
d) Select Dept_name from instructor where Dept_name = NULL;
Explanation: Here only the common elements are displayed .
3. Select distinct Dept_name from instructor ;
How many row(s) are displayed ?
Explanation: Distinct keyword eliminates the the common Dept_name .
4. Suppose the Authority want to include a new instructor for the title Neuroscience what command should be inserted ?
a) Insert into instructor values(12111,Emma,NeuroScience,200000);
b) Insert into course values(12111,Introduction,NeuroScience,2);
c) Insert into instructor values(12111,Emma,Biology,200000);
Insert into course values(BIO-112,Introduction to Neuro Science,NeuroScience,2);
d) Insert into course values(12111,Emma,NeuroScience,200000);
Explanation: The values have to be inserted into both the relations to be intact .
5. If a person all the people in Music department gets fired which of the following has to be performed on the instructor relation ?
a) Delete Dept_name=Music in instructor;
b) Delete from instructor where Dept_name=Music;
c) Remove Dept_name= Music
d) All of the mentioned
Explanation: Delete from table_name where condition .
SELECT DISTINCT T.name FROM instructor AS T, instructor AS S WHERE T.salary > S.salary AND S.dept name = ’Comp.Sci.’;
What will be displayed as the value of name for the above query ?
c) Hayley and Crick
Explanation: Only the greatest salary in Comp.Sci dept is selected for the query.
SELECT Name FROM instructor WHERE salary > SOME (SELECT salary FROM instructor WHERE dept_name = 'Comp.Sci.');
How many rows are selected ?
Explanation: This displays the names of instructors with salary greater than that of some (at least one) instructor in the Biology department .
8. How will you select the Names whose first letter is E ?
a) SELECT Name FROM instructor WHERE Name LIKE ’A%; b) SELECT Name FROM course WHERE Name LIKE ’A%; c) SELECT Dept_name FROM instructor WHERE Name LIKE ’A%; d) SELECT Name FROM instructor WHERE Dept_name LIKE ’A%;
Explanation: % is used to indicate that some characters may appear .
9. Which function is used to find the count of distinct departments?
Explanation: Count (distinct ID) is the correct usage .
10. Which function is used to identify the title with Least scope ?
Explanation: Max is used to find the highest element and Min is used to find the lowest element .
Sanfoundry Global Education & Learning Series – Database Management System.