Week3 - Restricted and Stored Data / Explore

The questions can come in any order, so make sure you are selecting right option for all questions.

*The Oracle WHERE clause:
A.Limits the column data that are returned.
B.Limits the row data are returned. *

    (b) B is True

*Select name from instructor where salary <= 100000 and salary >= 90000; This query can be replaced by which of the following ? *

    (a) Select name from instructor where salary between 90000 and 100000;

*For like predicate which of the following is true.
i) % matches zero of more characters.
ii) _ matches exactly one character. *

    (c) Both of them

Select name from instructor where dept name = ’Physics’ order by name; By default, the order by clause lists items in ______ order.

    (d) Ascending

*Select emp_name from department where dept_name like ’ _____ Computer Science’;
Which one of the following has to be added into the blank to select the dept_name which has Computer Science as its ending string ? *

    (a) %

What is meaning of LIKE '%0%0%'

    (d) Feature has two 0's in it, at any position

Which of the Query is used to display all the Student details who are living in the city 'Pune'.

    (c) Select * from STUDENT where city='Pune';

With Oracle, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

    (b) SELECT * FROM Persons WHERE FirstName='Peter'

*What will be the output of a query given below?
SELECT * FROM person
WHERE person_name='Rani'; *

    (a) Show all columns but only those rows which belongs to person_name'Rani'

**employee_id Name Salary
1001 Annie 6000
1009 Ross 4500
1018 Zeith 7000

above are the Employee table details.
Select * from employee where employee_id>1009;

Which of the following employee id will be displayed? **

    (d) 1018

**employee_id Name Salary
1001 Annie 6000
1009 Ross 4500
1018 Zeith 7000
above are the Employee table details.

select name from employee where employee_id=1009 or salary=4500;

Which of the following employee name will be displayed?**

    (c) Ross

What will be the output of a query given below?
SELECT person_id, fname, lname
FROM person
WHERE person_id=4;

    (a) Show only columns(person_id, fname, lname) but only those rows which belongs to person_id 4

All the questions are related to the ER Diagram displayed below: College Management System

Question 1
Choose the query to display names of the students that start with the letter 'A', ordered in ascending order.

    (a) select student_name from Student where student_name like 'A%' order by student_name asc;

**Question 2
What will be the result of below query, if the question is to display the value of the marks secured by the student_id 10 in the subject_id 1.

select value from Mark where student_id = 10 and subject_id = 1;**

    (b) Accepted

**Question 3
Fill the blank space, such that below query is used to display the records of the students with address, ordered in descending order based on student id.

select _____ from Student where address _________ order by ________ desc;**

    (a) *, is not null, student_id

**Question 4
Fill the blank space, such that below query is used to display names of the departments belonging to the block number 3, ordered in department name in descending order.

select ______ from Department where department_block_number = ________ order by department_name _________;**

    (a) department_name, 3, desc

Question 5
Choose the query to display names of the students that start with letter 'A' and end with the letter 'a', ordered in ascending order.

    (a) select student_name from Student where student_name like 'A%' and student_name like '%a' order by student_name asc;