D8

Question 1

Retrieve tutor's name, who have more than experience others along with their students name. Tables: office {id (PK), locations, name}, tutor {id (PK), name officeid (FK references office (id)), experience},  students{id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

Accepted Answer

select t.name, s.name from tutor t join students s on t.id=s.tutorid where experience = (select max (experience) from tutor);

Answer

select t.name, s.name from tutor t join students s on t.id=s.id where experience = (select max (experience) from tutor);

Answer

select t.name, s.name, max(experience) from tutor t join students s on t.id=s.tutorid;

Answer

select t.name, s.name, max(experience) from tutor t join students s on t.id=s.tutorid

Question 2

convert students' name to upper letters. Tables: office {id (PK), locations, name}, tutor {id (PK), name, officeid (FK references office (id)), experience}, students {id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

Accepted Answer

select upper (name) from students;

Answer

select ucase (name) from students;

Answer

select Name from students;

Answer

select lower (name) from students;

Question 3

Retrieve student's name, which starts from A. Tables: office {id (PK), locations, name}, tutor {id (PK), name officeid (FK references office (id)), experience},  students{id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

Accepted Answer

select name from students where name like 'A_%';

Answer

select name from students where name like 'a%';

Answer

select name from students where name like '%A%';

Answer

select name from students where name like 'A';

Question 4

Find an average scholarship of students. Tables: office {id (PK), locations, name}, tutor {id (PK), name officeid (FK references office (id)), experience},  students{id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

Accepted Answer

select avg (scholarship) from students;

Answer

select averg (scholarship) from students;

Answer

select av (scholarship) from students;

Answer

select avrg (scholarship) from students;

Question 5

Retrieve quantity of students in the students table - students {id, name, scholarship, registereddate, tutorid}

Accepted Answer

select count(id) from office;

Answer

Select avg (id) from office;

Answer

select sum(id) from office;

Answer

select max(id) from office;

Question 6

Retrieve all students name with the department name, but excluding department CSSE. Tables: office {id (PK), locations, name}, tutor {id (PK), name officeid (FK references office (id)), experience},  students{id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

Accepted Answer

select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id = t.officeid where o.name <> 'CSSE';

Answer

select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id = t.officeid where o.name = 'CSSE';

Answer

select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id = t.officeid group by o.name, s.name;

Answer

select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id = t.officeid

Question 7

Which function is used to retrieve quantity of rows

Accepted Answer

count

Answer

sum

Answer

max

Answer

avg

Question 8

define inner join

Accepted Answer

selects all rows from both tables as long as there is a match between the columns in both tables

Answer

returns all rows from the left table (1) and from the right table(2)

Answer

returns all rows from the right table(2), with the matching rows in the right table(2)

Answer

returns all rows from the left table(1), with the matching rows in the right table(2)

Question 9

define right join

Accepted Answer

returns all rows from the right table(2), with the matching rows in the left table(1)

Answer

returns all rows from the left table(1) and from the right table(2)

Answer

selects all rows from both tables as long as there is a match between the columns in both tables

Answer

returns all rows from the left table(1), with the matching rows in the right table(2)

Question 10

Define avg function

Accepted Answer

Returns average value

Answer

Returns the first value

Answer

Returns total value

Answer

Converts to lowercase

Question 11

Retrieve library name, book name, number of copies, which have more than 25 copies. Book {bookid, title, publisher_name, price}. BookCopies {bookid, libraryid, noOfCopies [references Book(bookid)]}. Library(libraryid)), cardno (references Borrower (cardno)), dateout, duedate}.

Accepted Answer

select l.libraryname, b.title, bc.no_of_copies from book b join book_copies bc on b.bookid=bc.bookid join library l on l.libraryid = bc.libraryid where bc.no_of_copies>25;

Answer

select l.libraryname, b.title, bc.no_of_copies from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>25;

Answer

select l.libraryname, b.title, bc.no_of_copies from book join library l on l.libraryid = bc.libraryid where bc.no_of_copies>25;

Answer

select l.libraryname, b.title, bc.no_of_copies from book b join book_copies bc on b.bookid=bc.bookid join library l l.libraryid = bc.libraryid where bc.no_of_copies>25;

Question 12

Which of the following query is incorrect for retrieving all students name with the department name, but excluding department CSSE.Tables: office {id (PK), locations, name}, tutor {id (PK), name officeid (FK references office (id)), experience},  students{id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

Accepted Answer

select s.name, o.name from students s join tutor t on t.id = s.tutorid join office o on o.id=t.officeid where o.name <= 'CSSE';

Answer

select s.name, o.name from students s join tutor t on t.id = s.tutorid join office o on o.id=t.officeid where o.name not in ('CSSE');

Answer

select s.name, o.name from students s join tutor t on t.id = s.tutorid join office o on o.id=t.officeid where o.name <> 'CSSE';

Answer

select s.name, o.name from students s join tutor t on t.id = s.tutorid join office o on o.id=t.officeid where o.name not like 'CSSE';

Question 13

When an insertion anomally occurs?

Accepted Answer

we are prevented from inserting some data into a relation until other data can be supplied

Answer

deletion leads to an unintended loss of data

Answer

it is possible that not all data needs to be changed will always be changed

Answer

it does not occur

Question 14

What kind symbol is used to find one character in wildcards?

Accepted Answer

_

Answer

in

Answer

$

Answer

?

Question 15

Retrieve the number of departments in the department table. Tables: Department {id,name}. Employee {id, name, salary, dep_id (references Department(id))}

Accepted Answer

select count(id) from department

Answer

select sum (id) from department

Answer

select id from department

Answer

select coun(id) from department

Question 16

Retrieve quantity of offices in the office table - office {id, locations, name}

Accepted Answer

Select count (id) from office;

Answer

Select avg (id) from office;

Answer

Select max(id) from office;

Answer

Select sum(id) from office;

Question 17

Retrieve office name from the table office {id, locations, name}

Accepted Answer

Select name from office;

Answer

Select *form office;

Answer

Select *from office;

Answer

Select from office;

Question 18

Retrieve the lowest experience from tutor. Tables: office {id (PK), locations, name}, tutor {id (PK), name officeid (FK references office (id)), experience},  students{id (PK), name, scholarship, registereddate, tutorid (FK references tutor (id))}.

Accepted Answer

select min (experience) from tutor;

Answer

select max (t.experience) from tutor t group by t.name;

Answer

select min(t.experience) from tutor t group by t.name;

Answer

select max (experience) from tutor;

Question 19

How many types of anomalies exist

Accepted Answer

3

Answer

4

Answer

2

Answer

1

D8

Create a free account to finish this quiz

Questions in this quiz (19)

Information

Feedback

What do you think of the new look?