ГОС по БД #2

Question 1

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 (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;

Question 2

Retrieve book name, number of books, price and publisher, for those books which have more than 20 copies.  Tables : BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}

Accepted Answer

select count (b.bookid),b.title, b.price, b.publisher_name from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>20 group by b.title, b.price, b.publisher_name;

Answer

select count (b.bookid),b.title, b.price, b.publisher_name from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>20;

Answer

select count (b.bookid),b.title, b.price, b.publisher_name from book b join book bc on b.bookid=bc.bookid where bc.no_of_copies>20 group by b.title, b.price, b.publisher_name;

Answer

select b.bookid,b.title, b.price, b.publisher_name from book b join book_copies bc on b.bookid=bc.bookid where bc.no_of_copies>20 group by b.title, b.price, b.publisher_name;

Question 3

Retrieve  card number, book name, borrower name, who have borrowed in 12 July, bookid (references Book (bookid)), libraryid (references Library (libraryid)), cardno 2013.  Tables: BOOK {bookid, title, publisher_name, price}. BOOKLOANS {bookloans, cardno(references Borrower (cardno)),bookid (references Book (bookid)) dateout, duedate}. BORROWER {cardno, c_name, b_address, phoneno}

Accepted Answer

select bl.cardno, b.title, br.c_name from book b join bookloans bl on bl.bookid=b.bookid join borrower br on br.cardno=bl.cardno where bl.dateout='12-07-2013'

Answer

select bl.cardno, b.title, br.c_name from book b join bookloans bl on bl.bookid=b.bookid join borrower br on br.cardno=bl.cardno where bl.duedate='12-07-2013'

Answer

select bl.cardno, b.title, br.c_name from book b join bookloans bl on bl.bookid=b.bookid where bl.dateout='12-07-2013'

Answer

select bl.cardno, b.title, br.c_name from book b join borrower br on br.cardno=bl.cardno where bl.dateout='12-07-2013'

Question 4

Retrieve  max price for Fariza's publication. BOOK {bookid, title, publisher_name, price}.BOOK_AUTHORS {author_name, bookid (references Book (bookid))}

Accepted Answer

select max (b.price)from book b join book_authors ba on b.bookid=ba.bookid where ba.author_name = 'Fariza';

Answer

select max (b.price)from book b join book_authors ba on b.bookid=ba.bookid where ba.author_name = Fariza;

Answer

select max (b.price)from book b join book_authors ba b.bookid=ba.bookid where ba.author_name = 'Fariza';

Answer

select max (b.price)from book b join book_authors ba on b.bookid=ba.bookid; #subToPewDiePie

Question 5

Retrieve book name, total number of book copies. BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}

Accepted Answer

select b.title, sum (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid group by b.title;

Answer

select b.title, sum (no_of_copies) from book b join book_copies bc b.bookid=bc.bookid group by b.title;

Answer

select b.title, count(no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid group by b.title;

Answer

select b.title, count(no_of_copies) from book b join book_copies bc b.bookid=bc.bookid group by b.title;

Question 6

Retrieve book title and total number of book copies, which has id 1. BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}

Accepted Answer

select b.title, sum (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1 group by b.title;

Answer

select b.title, sum (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1;

Answer

select b.title, count (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1 group by b.title;

Answer

select b.title, count (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1;

Question 7

Retrieve borrower name and number of books that each borrower has.  BOOKLOANS {bookloans, bookid (references Book (bookid)), libraryid (references Library (libraryid)), cardno (references Borrower (cardno)), dateout, duedate}.  BORROWER {cardno, c_name, b_address, phoneno}

Accepted Answer

select br.c_name, count(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno group by br.c_name;

Answer

select br.c_name, count(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno;

Answer

select br.c_name, sum(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno group by br.c_name;

Answer

select br.c_name, sum(bl.bookid) from borrower br join bookloans bl on br.cardno = bl.cardno;

Question 8

Retrieve books name and prices. Increase price twice more for books, which have more than 20 copies;BOOK {bookid, title, publisher_name, price}. BOOKCOPIES {bookid, libraryid,noOfCopies [references Book (bookid)]}

Accepted Answer

select b.title, b.price, b.price *2 as increasedprice from book b join book_copies bc on bc.bookid=b.bookid where bc.no_of_copies > 20;

Answer

select b.title, b.price book b join book_copies bc on bc.bookid=b.bookid where bc.no_of_copies > 20;

Answer

impossible to solve

Answer

select b.title, b.price, b.price *2 as increasedprice from book b join book_copies bc on bc.bookid=b.bookid;

Question 9

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 10

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 coun(id) from department

Answer

select id from department

Question 11

Retrieve total salaries payable to employee. Tables: DEPARTMENT {id, name}. EMPLOYEE {id, name, salary, dep_id (references Department (id))}

Accepted Answer

select sum (salary) from employee;

Answer

select count (salary) from employee;

Answer

select sum (id) from employee;

Answer

select max (salary) from employee;

Question 12

Retrieve office name and tutor name, who have experience equal or more than five years. 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 o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience >=5;

Answer

select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience >5;

Answer

select o.name, t.name from office o join tutor t o.id=t.officeid where t.experience >=5;

Answer

select o.name, t.name from office join tutor on o.id=t.officeid where t.experience >=5;

Question 13

Retrieve tutor name and their office locations, who refer to CSSE and Management. 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, o.locations from office o join tutor t on o.id=t.officeid where o.name in ('CSSE', 'Management');

Answer

select t.name, o.locations from office o join tutor t on o.id=t.officeid where o.name in ('CSSE' or 'Management');

Answer

select t.name, o.locations from office o join tutor t o.id=t.officeid where o.name in ('CSSE', 'Management');

Answer

select t.name, o.locations from office o join tutor t on o.id=t.officeid where o.name in ('CSSE') or ('Management');

Question 14

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 15

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;

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;

Question 16

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 <> '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';

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');

Question 17

Retrieve students name, scholarship and their office name, who have scholarship ranging from 3200 to 4500.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, s.scholarship, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where s.scholarship between 3200 and 4500;

Answer

select s.name, s.scholarship, o.name from students s join tutor t on t.id=s.tutorid where s.scholarship between 3200 and 4500;

Answer

select s.name, s.scholarship, o.name from students join office o on o.id=t.officeid where s.scholarship between 3200 and 4500;

Answer

select s.name, s.scholarship, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid where s.scholarship 3200 and 4500;

Question 18

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

Accepted Answer

-

Answer

?

Answer

$

Answer

in

Question 19

Find an average scholarship of students. Tables: STUDENTS {id (PK), name, scholarship, registereddate, tutorid }

Accepted Answer

select avg (scholarship) from students ;

Answer

select averg (scholarship) from students ;

Answer

select avrg (scholarship) from students ;

Answer

select av (scholarship) from students ;

Question 20

Retrieve average scholarship of students for each department. 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), 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;

Answer

select avg (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid ;

Answer

select avg (scholarship), o.name from students s join tutor t on t.id=s.tutorid group by o.name;

Answer

select avg (scholarship), o.name from students s d join office o on o.id=t.officeid group by o.name;

Question 21

Retrieve student number in each department. 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 count (s.id), 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;

Answer

select sum (s.id), 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;

Answer

select 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;

Answer

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

Question 22

Retrieve student number in each department. 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 count (s.id), 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;

Answer

select sum (s.id), 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;

Answer

select 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;

Answer

select count (s.id), o.name from students s join tutor t

Answer

on t.id=s.tutorid join office o on o.id=t.officeid;

Question 23

Retrieve total amount of scholarship of each department. 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 sum (scholarship), 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;

Answer

select count (scholarship), 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;

Answer

select sum (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;

Answer

select count (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;

Question 24

SQL stands for

Accepted Answer

Structured Query Language

Answer

Sequence Question Language

Answer

Structured Querty Language

Answer

Selection Query Language

Question 25

You can add a row using SQL in a database with which of the following

Accepted Answer

INSERT

Answer

ADD

Answer

CREATE

Answer

UPDATE

Question 26

The command to remove rows from a table “Customer”

Accepted Answer

Delete from Customer

Answer

Remove from Customer

Answer

Drop from Customer

Answer

Update row from Customer

Question 27

The SQL WHERE clause

Accepted Answer

Condition that limits the row data are returned

Answer

Condition that limits the column data that are returned

Answer

Clause that returns all rows

Answer

Clause that returns nothing

Question 28

The command to eliminate a table from a database

Accepted Answer

Drop

Answer

Delete

Answer

Remove

Answer

Update

Question 29

Which of the following is correct order of keywords for SQL Select statement?

Accepted Answer

SELECT, FROM, WHERE

Answer

WHERE, FROM, SELECT

Answer

FROM, WHERE, SELECT

Answer

SELECT, WHERE, FROM

Question 30

SQL data definition commands make up a(n)

Accepted Answer

DDL

Answer

DDC

Answer

DML

Answer

DDD

Question 31

In a relation, the columns are also called attributes

Answer

true

Question 32

Find the SQL statement that is equal to: SELECT NAME FROM CUSTOMER WHERE STATE = 'VA';

Accepted Answer

SELECT NAME FROM CUSTOMER WHERE STATE IN 'VA';

Answer

SELECT NAME IN CUSTOMER WHERE STATE IN 'VA';

Answer

SELECT NAME IN CUSTOMER WHERE STATE = 'VA';

Answer

SELECT NAME IN CUSTOMER WHERE STATE = 'V';

Question 33

In E\R diagrams, we will represent Entities as

Accepted Answer

Boxes with rounded corners

Answer

Links between two entities

Answer

Ovals

Answer

Diamond box

Question 34

In E\R diagrams, we will represent Relationships as

Accepted Answer

Links between two entities

Answer

Boxes with rounded corners

Answer

Ovals

Answer

Diamond box

Question 35

In E\R diagrams, we will represent Attributes as

Accepted Answer

Ovals

Answer

Boxes with rounded corners

Answer

Links between two entities

Answer

Diamond box

Question 36

Many to many relationships are difficult to represent in database, so we need to

Accepted Answer

Split many to many relationship into two one to many relationships

Answer

Split one to many relationship into two one to many relationships

Answer

Split many to many relationship into one to many relationships

Answer

Split many to many relationship into three one to many relationships

Question 37

The result of a SQL SELECT statement is a(n)

Accepted Answer

Table

Answer

Report

Answer

Form

Answer

File

Question 38

Which of the following are the five built-in functions provided by SQL?

Accepted Answer

COUNT, SUM, AVG, MAX, MIN

Answer

COUNT, SUM, AVG, MAX, MULT

Answer

SUM, AVG, MIN, MAX, NAME

Answer

SUM, AVG, MULT, DIV, MIN

Question 39

In a relation, the order of the rows matters

Answer

true

Question 40

Given the functional dependency R → (S,T) , then it is also true that R → S

Answer

true

Question 41

Given the functional dependency R → (S,T) , then it is also true that R → T

Answer

true

Question 42

SQL can be used to

Accepted Answer

All of the these can be done by SQL

Answer

create database structures only

Answer

query database data only

Answer

modify database data only

Question 43

The SQL statement that queries or reads data from a table is _

Accepted Answer

SELECT

Answer

READ

Answer

QUERY

Answer

NONE

Question 44

A subquery in an SQL SELECT statement

Accepted Answer

has a distinct form that cannot be duplicated by a join

Answer

can only be used with two tables

Answer

can always be duplicated by a join

Answer

cannot have its results sorted using ORDER BY

Question 45

The SQL keyword BETWEEN is used

Accepted Answer

for ranges

Answer

as a wildcard

Answer

to limit the columns displayed

Answer

None of the above

Question 46

Sometimes you want to change the structure of an existing table, what are your options?

Accepted Answer

Alter Table

Answer

Change table

Answer

Drop Table

Answer

Create Table

Question 47

how to Add a new column into existing table?

Accepted Answer

ALTER TABLE Student ADD COLUMN sDegree VARCHAR(64) NOT NULL

Answer

ALTER TABLE Student MODIFY COLUMN sDegree CHAR(64) NOT NULL

Answer

ALTER TABLE S ADD COLUMN s VACHAR(64) NOT NULL

Answer

ALTER TABLE Student DROP COLUMN sDegree VARCHAR(64) NOT NULL

Question 48

how to rename a column in existing table?

Accepted Answer

ALTER TABLE Student RENAME COLUMN s to SS VACHAR(64) NOT NULL

Answer

ALTER TABLE Student MODIFY COLUMN sDegree CHAR(64) NOT NULL

Answer

ALTER TABLE Student ADD COLUMN sDegree VARCHAR(64) NOT NULL

Answer

ALTER TABLE Student DROP COLUMN sDegree VARCHAR(64) NOT NULL

Question 49

how to change the row(s) in a table

Accepted Answer

update

Answer

insert

Answer

change

Answer

delete

Question 50

how to remove the row(s) from a table

Accepted Answer

delete

Answer

insert

Answer

update

Answer

change

Question 51

A SELECT statement can be nested inside another query to form a

Accepted Answer

Subquery

Answer

Subselect

Answer

Subresults

Answer

Query in query

Question 52

SQL uses privileges to control access to tables and other database objects, so which is NOT?

Accepted Answer

Drop privilege

Answer

Select privilege

Answer

Update privilege

Answer

Insert privilege

Question 53

To convert any relation into _______, split any nonatomic values

Accepted Answer

First normal form

Answer

Second normal form

Answer

Third normal form

Answer

Fourth normal form

Question 54

A functional dependency (FD) is a

Accepted Answer

link between two sets of attributes in a relation

Answer

link between three sets of attributes in a relation

Answer

link between all sets of attributes in a relation

Answer

link between four sets of attributes in a relation

Question 55

which if the following does not refer to redundancy problems?

Accepted Answer

CREATE anomalies

Answer

INSERT anomalies

Answer

UPDATE anomalies

Answer

DELETE anomalies

Question 56

To convert any relation into _______, remove transitive dependency

Accepted Answer

Third normal form

Answer

First normal form

Answer

Second normal form

Answer

Fourth normal form

Question 57

What is it ?

Accepted Answer

Relation

Answer

Tuples

Answer

Attributes

Answer

Relationships

Question 58

What is it ?

Accepted Answer

Attributes

Answer

Relation

Answer

Tuples

Answer

Relationships

Question 59

What is it ?

Accepted Answer

Tuples

Answer

Relation

Answer

Attributes

Answer

Relationships

Question 60

Choose the correct relational algebra operation

Accepted Answer

Difference

Answer

Union

Answer

Itersection

Answer

Product

Question 61

Choose the correct relational algebra operation

Accepted Answer

Intersection

Answer

Union

Answer

Difference

Answer

Product

Question 62

Choose the correct relational algebra operation

Accepted Answer

Product

Answer

Difference

Answer

Union

Answer

Intersection

Question 63

Choose the correct relational algebra operation

Accepted Answer

Union

Answer

Difference

Answer

Intersection

Answer

Product

Question 64

Are given table union compatible ?

Accepted Answer

yes

Answer

no

ГОС по БД #2

Preguntas del test (77)

Información

Escríbenos

¿Qué te parece el nuevo diseño?