What the operator UNION returns?

• Rows from both queries after eliminating duplications.

• Rows from both queries, including all duplications.

• Rows that are common to both queries.

• All the distinct rows selected by the first query, but not present in the second query result set.

What the INTERSECT operator returns?

• Rows that are common to both queries.

• Rows from both queries after eliminating duplications.

• Rows from both queries, including all duplications.

• All the distinct rows selected by the first query, but not present in the second query result set.

What the MINUS operator returns?

• All the distinct rows selected by the first query, but not present in the second query result set.

• Rows from both queries after eliminating duplications.

• Rows from both queries, including all duplications.

• Rows that are common to both queries.

How the subquery executes?

• The subquery (inner query) executes before the main query (outer query).

• The subquery (inner query) executes after the main query (outer query).

• The subquery (inner query) executes together with the main query (outer query).

• The subquery (inner query) executes by priority to the main query (outer query).

When you do not use the HAVING clause?

• Groups matching the HAVING clause are displayed

• The group function is applied

Column Headings Defaults at SQL*PLUS terminal have to be:

• Default heading display: Uppercase

• Default heading alignment: Left-aligned

• Number column headings are Left-aligned

• Character and Date column headings are Right-aligned

Which syntax of basic select statement is correct:

• Select *|{[Distinct] column|expression [alias],...} From table;

• Select * |{[Distinct] column|expression,...} From table;

• Select *|{column|expression} From table;

• Select *|{[Distinct] column|expression [alias],…} From table Where condition(s);

Which Writing SQL Statements rules is correct?

• SQL statements can be entered on one or more lines.

• SQL statements should be entered on one lines.

• Clauses must placed on separate lines.

• SQL statements must be entered on separate lines.

DEFINE command uses for:

• Command to create and assign a value to a variable

• Command to remove a values from table.

• Command to remove a variable.

• Command to create a value to table.

Indexes must be created manually and serve to speed up access to rows in a table?

• Indexes do not serve to speed up access to rows

You can Not to use constraints to do the following?

• Prevent the creation of a table.

• Prevent the creation of data in a table.

• Enforce rules on the data in a table whenever a row is inserted, updated, or deleted.

Identify the SELECT statements that execute successfully:

• SELECT first_name, last_name, job_id, salary*12 AS Yearly_Sal FROM employees;

• SELECT first_name, last_name, job_id, salary AS yearly sal FROM employees;

• SELECT first_name+last_name AS name, job_Id, salary*12 yearly sal FROM employees;

• SELECT first_name, last_name, job_id, salary AS ‘yearly_sal’ FROM employees;

Table names and column names choose Incorrect point?

• Must be an Oracle server–reserved word

• Must be 1–30 characters long

• Must contain only A–Z, a–z, 0–9, _, $, and #

Which one alias is Incorrect?

• Select job_id ‘id’ from jobs;

• Select job_id as id from jobs;

• Select job_id id from jobs;

• Select job_id “id” from jobs;

Quiz

ORACLE

Ayaulym Burakhanova 176 questions

Take the quiz

Questions in this quiz (176)

1 What kind of objects Generates numeric values?
1. A• Table
2. B• Index
3. C• Synonym
4. D• Sequence
2 What kind of objects Logically represents subsets of data from one or more tables?
1. A• Table
2. B• Synonym
3. C• View
4. D• Index
3What the operator UNION returns?
1. A• Rows from both queries after eliminating duplications.
2. B• Rows from both queries, including all duplications.
3. C• Rows that are common to both queries.
4. D• All the distinct rows selected by the first query, but not present in the second query result set.
4What the INTERSECT operator returns?
1. A• Rows from both queries after eliminating duplications.
2. B• Rows from both queries, including all duplications.
3. C• Rows that are common to both queries.
4. D• All the distinct rows selected by the first query, but not present in the second query result set.
5What the MINUS operator returns?
1. A• Rows from both queries after eliminating duplications.
2. B• Rows from both queries, including all duplications.
3. C• Rows that are common to both queries.
4. D• All the distinct rows selected by the first query, but not present in the second query result set.
6How the subquery executes?
1. A• The subquery (inner query) executes after the main query (outer query).
2. B• The subquery (inner query) executes before the main query (outer query).
3. C• The subquery (inner query) executes together with the main query (outer query).
4. D• The subquery (inner query) executes by priority to the main query (outer query).
7What type of subquery Oracle DBMS does not exist?
1. A• Single-row
2. B• Long-row
3. C• Multiple-row
4. D• All answers is incorrect
8What of the JOIN clauses do not exist at PL/SQL?
1. A• Self join
2. B• Natural join
3. C• Cross join
4. D• Middle join
9What kind of join includes into OUTER JOIN?
1. A• Cross join
2. B• Full join
3. C• Inner join
4. D• Natural join
10The clause which produces the cross-product or Cartesian product of two tables?
1. A• Inner join
2. B• Outer join
3. C• Cross join
4. D• Natural join

11What command included to DML?
1. A• Create
2. B• Insert
3. C• Commit
4. D• Grant
12What command of DML could replace the Update or Insert?
1. A• Alter
2. B• Truncate
3. C• Merge
4. D• Commit
13What command would be appropriate, if we need to Modify rows of any tables?
1. A• Update
2. B• Alter
3. C• Create
4. D• Insert
14Which command uses to erase whole data from table?
1. A• Drop
2. B• Revoke
3. C• Truncate
4. D• Rollback
15Which command restores the database to last commited state?
1. A• Savepoint
2. B• Commit
3. C• Rollback
4. D• Back up
16What kind of the group function uses to compute total number of rows?
1. A• AVG
2. B• SUM
3. C• COUNT
4. D• MAX
17How many rows this command will retrieve? SELECT MIN(hire_date) FROM employees;
1. A0
2. B1
3. C2
4. D3
18What clause uses to show group function for several departments? SELECT department_id, COUNT(last_name) FROM employees;
1. A• HAVING
2. B• ORDER BY
3. C• USING
4. D• GROUP BY
19When you do not use the HAVING clause?
1. A• Rows are grouped
2. B• Groups matching the HAVING clause are displayed
3. C• Rows are sorted
4. D• The group function is applied
20What function uses to convert any type of data to Characters?
1. A• TO_DATE
2. B• TO_VARCHAR2
3. C• TO_NUMBER
4. D• TO_CHAR
21How to show Full name of the month of any DATE type rows?
1. A• MM
2. B• MONTH
3. C• MON
4. D• MI
22Which one is the Conditional Expression which Provide the use of the IF-THEN-ELSE logic?
1. A• CASE
2. B• CONCAT
3. C• COALESCE
4. D• SUBSTRING
23What functions work with any data type and pertain to the use of Null values in the expression list?
1. A• CASE, DECODE
2. B• TO_CHAR, TO_DATE
3. C• NVL, NULLIF, COUNT
4. D• COALESCE, NVL, NVL2
24Where we do not use the Single-row functions?
1. A• SELECT
2. B• GROUP BY
3. C• WHERE
4. D• ORDER BY
25What function does not exists the Case-conversion functions?
1. A• LENGTH
2. B• LOWER
3. C• UPPER
4. D• INITCAP
26What function Character-manipulation functions exists?
1. A• LOWER
2. B• CONCAT
3. C• COUNT
4. D• NVL
27What the result of using function? INITCAP('SQL Course')
1. A• SQL COURSE
2. B• sql course
3. C• Sql Course
4. D• Sql course
28Which function Finds the Numeric position of a named character?
1. A• LENGTH
2. B• LPAD
3. C• SUBSTR
4. D• INSTR
29Column Headings Defaults at SQL*PLUS terminal have to be:
1. A• Default heading alignment: Left-aligned
2. B• Default heading display: Uppercase
3. C• Number column headings are Left-aligned
4. D• Character and Date column headings are Right-aligned
30Which syntax of basic select statement is correct:
1. A• Select * |{[Distinct] column|expression,...} From table;
2. B• Select *|{column|expression} From table;
3. C• Select *|{[Distinct] column|expression [alias],…} From table Where condition(s);
4. D• Select *|{[Distinct] column|expression [alias],...} From table;
31Which Writing SQL Statements rules is correct?
1. A• SQL statements can be entered on one or more lines.
2. B• SQL statements should be entered on one lines.
3. C• Clauses must placed on separate lines.
4. D• SQL statements must be entered on separate lines.
32What kind of Arithmetic Expressions uses with Date type:
1. A• / - divide
2. B• - minus
3. C• % - percent
4. D• multiply
33How to denotes one character at LIKE condition (WHERE Clause)?
1. A• % - percent
2. B• = - equal
3. C• & - ampersand
4. D• _ - underscore
34How many Logical Operators do the Oracle DBMS have?
1. A1
2. B2
3. C3
4. D4
35What Order by clause for “SELECT employee_id, last_name, salary*12 annsal FROM employees” is incorrect?
1. A• ORDER BY 3 desc;
2. B• ORDER BY annsal;
3. C• ORDER BY id desc;
4. D• ORDER BY last_name;
36What kind of signs uses to retrieve Substitution Variable Forms?
1. A• <>, !=
2. B• +, -, *, /
3. C• %, _
4. D• &&, &
37DEFINE command uses for:
1. A• Command to remove a values from table.
2. B• Command to remove a variable.
3. C• Command to create a value to table.
4. D• Command to create and assign a value to a variable
38Indexes must be created manually and serve to speed up access to rows in a table?
1. A• True
2. B• False
3. C• Not sure
4. D• Indexes do not serve to speed up access to rows
39You can Not to use constraints to do the following?
1. A• Prevent the creation of data in a table.
2. B• Prevent the creation of a table.
3. C• Enforce rules on the data in a table whenever a row is inserted, updated, or deleted.
40Identify the SELECT statements that execute successfully:
1. A• SELECT first_name, last_name, job_id, salary AS yearly sal FROM employees;
2. B• SELECT first_name, last_name, job_id, salary*12 AS Yearly_Sal FROM employees;
3. C• SELECT first_name+last_name AS name, job_Id, salary*12 yearly sal FROM employees;
4. D• SELECT first_name, last_name, job_id, salary AS ‘yearly_sal’ FROM employees;
41From what kind of system table we can retrieve NEXTVAL and CURRVAL
1. A• Data
2. B• System
3. C• Dual (?)
4. D• Store
42How called the Nickname for columns?
1. A• Synonym
2. B• Alias
3. C• Macros
4. D• Byname
43. By which command we can connect to Oracle DB at Terminals?
1. A• Sqlplus / sysdba
2. B• PLSQL as system@oracle
3. C• Sqlplus hr@oracle
4. D• PLSQL hr@oracle
44What kind of constraints included into PRIMARY KEY?
1. A• NVL
2. B• Nullif
3. C• Check
4. D• Unique
45Table names and column names choose Incorrect point?
1. A• Must be an Oracle server–reserved word
2. B• Must be 1–30 characters long
3. C• Must begin with a letter
4. D• Must contain only A–Z, a–z, 0–9, _, $, and #
46Which one alias is Incorrect?
1. A• Select job_id as id from jobs;
2. B• Select job_id ‘id’ from jobs;
3. C• Select job_id id from jobs;
4. D• Select job_id “id” from jobs;
47What object Gives alternative names to objects?
1. A• Table
2. B• Synonym
3. C• View
4. D• Index
48What object Improves the performance of some queries?
1. A• Table
2. B• Synonym
3. C• View
4. D• Index
49How to erase any Schema objects?
1. A• Delete
2. B• Drop(?)
3. C• Truncate
4. D• Destroy
50What object have NEXTVAL and CURRVAL Pseudocolumns?
1. A• Table
2. B• Synonym
3. C• Sequences
4. D• Index
51What constraint Defines the column in the child table at the table-constraint level?
1. A• FOREIGN KEY
2. B• UNIQUE
3. C• NOT NULL
4. D• PRIMARY KEY
52What data type have Fixed-length character data?
1. A• VARCHAR2(size)
2. B• CHAR(size)
3. C• VARCHAR(size)
4. D• RAW and LONG RAW
53What data type have Variable-length character data?
1. A• VARCHAR2(size)
2. B• CHAR(size)
3. C• VARCHAR(size)
4. D• RAW and LONG RAW
54What data type have Variable-length numeric data?
1. A• INT(p)
2. B• BIGINT(p)
3. C• NUMBER(p,s)
4. D• LONG
55What data type have Date and time values?
1. A• TIME
2. B• DATE
3. C• CLOB
4. D• BLOB
56What kind of the group function uses to compute average number in rows?
1. A• AVG
2. B• SUM
3. C• COUNT
4. D• MAX
57How many rows this command will retrieve? SELECT MAX(hire_date) FROM employees;
1. A0
2. B1
3. C2
4. D3
58What clause uses to show group function for several departments? SELECT department_id, MAX(salary) FROM employees;
1. A• HAVING
2. B• ORDER BY
3. C• USING
4. D• GROUP BY
59When you do not use the HAVING clause?
1. A• Rows are grouped
2. B• Groups matching the HAVING clause are displayed
3. C• Rows are sorted
4. D• The group function is applied
60Where we do not use the Single-row functions?
1. A• SELECT
2. B• GROUP BY
3. C• WHERE
4. D• ORDER BY
61What function does not exists the Case-conversion functions?
1. A• INSTR
2. B• LOWER
3. C• UPPER
4. D• INITCAP
62What function Character-manipulation functions exists?
1. A• LOWER
2. B• SUBSTR
3. C• COUNT
4. D• NVL
63What Order by clause for “SELECT employee_id, last_name, salary*12 annsal FROM employees” is incorrect?
1. A• ORDER BY 3 desc;
2. B• ORDER BY annsal;
3. C• ORDER BY employee_id desc;
4. D• ORDER BY name;
64What kind of signs uses to show non equal values?
1. A• <>, !=
2. B• +, -, *, /
3. C• &&, &
4. D• %, _
65What kind of signs uses to compute arithmetic operations?
1. A• <>, !=
2. B• +, -, *, /
3. C• %, _
4. D• &&, &
66What kind of constraints included into PRIMARY KEY?
1. A• NVL
2. B• Nullif
3. C• Check
4. D• Not null
67Table names and column names choose Incorrect point?
1. A• Must not be an Oracle server–reserved word
2. B• Must be 1–30 characters long
3. C• Must begin with a number
4. D• Must contain only A–Z, a–z, 0–9, _, $, and #
68Which one alias is Incorrect?
1. A• Select job_id as id from jobs;
2. B• Select job_id as ‘id’ from jobs;
3. C• Select job_id as ID from jobs;
4. D• Select job_id as “id” from jobs;
69Column Headings Defaults at SQL*PLUS terminal have to be:
1. A• Default heading alignment: Left-aligned
2. B• Default heading display: Lowercase
3. C• Number column headings are Right-aligned.
4. D• Character and Date column headings are Right-aligned
70 Which syntax of basic select statement is correct:
1. A• Select * |{[Distinct] column|expression,...} From table;
2. B• Select *|{column|expression} From table;
3. C• Select *|{[Distinct] column|expression [alias],...} From table;
4. D• Select *|{[Distinct] column|expression [alias],…} From table Where condition(s);
71Which Writing SQL Statements rules is correct?
1. A• SQL statements can not to be entered on one or more lines.
2. B• SQL statements should be entered on one lines.
3. C• Clauses usually placed on separate lines.
4. D• SQL statements must be entered on separate lines.
72What kind of Arithmetic Expressions uses with Date type:
1. A• / - divide
2. B• + - add
3. C• % - percent
4. D• multiply
73How to denotes one or more character at LIKE condition (WHERE Clause)?
1. A• % - percent
2. B• = - equal
3. C• & - ampersand
4. D• _ - underscore
74. How many Arithmetic Operators do the Oracle DBMS have?
1. A1
2. B2
3. C3
4. D4
75 What kind of signs uses to skip characters at LIKE function?
1. A• <>, !=
2. B• +, -, *, /
3. C• %, _
4. D• &&, &
76What the result of using function? LOWER('SQL Course')
1. A• SQL COURSE
2. B• sql course
3. C• Sql Course
4. D• Sql course
77What the result of using function? UPPER('SQL Course')
1. A• SQL COURSE
2. B• sql course
3. C• Sql Course
4. D• Sql course
78What the result of using function? CONCAT('Hello', 'World')
1. A• Hello World
2. B• HelloWorld
3. C• elloWorld
4. D• Hello
79What the result of using function? SUBSTR('HelloWorld',1,5)
1. A• Hello World
2. B• HelloWorld
3. C• elloWorld
4. D• Hello
80What the result of using function? ROUND(45.926, 0)
1. A• 45.0
2. B• 45.9
3. C• 46.0
4. D• 0
81What the result of using function? TRUNC(45.926, 0)
1. A• 45.0
2. B• 45.9
3. C• 46.0
4. D• 0
82What the result of using function? MOD(1600, 300)
1. A• 1600
2. B• 300
3. C• 100
4. D• 500
83From what kind of system table we can retrieve SYSDATE?
1. A• Data
2. B• System
3. C• Dual
4. D• Store
84From what kind of system table we can retrieve CURRENT_DATE?
1. A• Data
2. B• System
3. C• Dual
4. D• Store
85What the result of using function? LAST_DAY ('01-FEB-95')
1. A• '28-FEB-95'
2. B• '31-DEC-95'
3. C• '28-JUNE-95'
4. D• '28-FEB-96'
86What the result of using function? ADD_MONTHS (‘31-JAN-96',1)
1. A• '28-MAR-96'
2. B• '31-DEC-96'
3. C• '28-JUNE-96'
4. D• '29-FEB-96'
87Retrieve office id and locations from the table office {id, locations, name}
1. ASelect *from office;
2. BSelect id, locations from office;
3. CSelect locations name from office;
4. DSelect id form office;
88Retrieve quantity of offices in the office table - office {id, locations, name}
1. Ao Select avg (id) from office;
2. Bo Select count (id) from office;
3. Co Select sum (id) from office;
4. Do Select max (id) from office;
89Retrieve total number scholarship in the students table – students {id, name, scholarship, registereddate, tutorid}
1. Ao Select avg (scholarship) from office;
2. Bo Select count (scholarship) from office;
3. Co Select sum (scholarship) from office;
4. Do Select max (scholarship) from office;
90Retrieve quantity of students in the students table – students {id, name, scholarship, registereddate, tutorid}
1. ASelect avg (id) from office;
2. BSelect count (id) from office;
3. CSelect sum (id) from office;
4. DSelect max (id) from office;
91Sort students name by descending order - students {id, name, scholarship, registereddate, tutorid}
1. Ao Select name from students order by desc;
2. Bo Select name from students group by desc;
3. Co Select *from students order by desc;
4. Do Select name form students order by desc;
92Find max scholarship of students - students {id, name, scholarship, registereddate, tutorid}
1. Ao Select avg (scholarship) from students;
2. Bo Select count (scholarship) from students;
3. Co Select sum (scholarship) from students;
4. Do Select max (scholarship) from students;
93 Find min scholarship of students - students {id, name, scholarship, registereddate, tutorid}
1. Ao Select avg (scholarship) from students;
2. Bo Select count (scholarship) from students;
3. Co Select min (scholarship) from students;
4. Do Select max (scholarship) from students;
94Retrieve offices’ name and tutors’, who work in CSSE 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))}.
1. Ao Select o.name, t.name from office o join tutor t on o.id=t.officeid where o.name = ‘CSSE’
2. Bo Select o.name, t.name from office o join tutor t on o.id=t.id where o.name = ‘CSSE’
3. Co Select o.name, t.name from office o join tutor t on o.id=t.officeid where o.name = ‘csse’
4. Do Select o.name, t.name from office o join tutor t on o.id=t.officeid where o.name ‘CSSE’
95Retrieve students’ name, who have more than average scholarship. 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))}.
1. Ao select s.name from students s where s.scholarship > (select scholarship from students);
2. Bo select s.name from students s where s.scholarship > (select avg (scholarship)from students);
3. Co select s.name from students s where s.scholarship > avg (scholarship);
4. Do select s.name from students s where s.scholarship < avg (scholarship);
96Retrieve students’ name, who registered in 10th of Jan 2016. 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))}.
1. Ao select name from students where registereddate = '2016-10-01';
2. Bo select name from students where registereddate = '2016-10-10';
3. Co select name from students where registereddate = 2016-10-10;
4. Do select name from students where registereddate = '2016-01-01';
97Retrieve tutors’ 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))}.
1. Ao select t.name, s.name, max(experience) from tutor t join students s on t.id=s.tutorid;
2. Bo select t.name, s.name from tutor t join students s on t.id=s.id where experience = (select max (experience) from tutor);
3. Co select t.name, s.name from tutor t join students s on t.id=s.tutorid where experience = (select max (experience) from tutor);
98Increase tutors experience and students’ scholarship twice. Retrieve experience and scholarship along with their names. 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))}.
1. Ao select t.name,t.experience*2, s.name, s.scholarship*2 from tutor t join students s on t.id=s.tutorid;
2. Bo select t.name,t.experience*2, s.name, s.scholarship*2 from tutor t join students s on t.id=s.id;
3. Co select t.name,t.experience*2, s.name, s.scholarship*2 from tutor t join tutor s on t.id=s.tutorid;
4. Do select t.name,t.experience*2, s.name, s.scholarship*2 from tutor t join tutor s on t.id=s.id;
99Retrieve office, tutor and students name, but tutors name should have y value. 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))}.
1. Ao select o.name, t.name, st.name from tutor t join office o on o.id=t.officeid join students st on t.id=st.tutorid where t.name like '%y%';
2. Bo select o.name, t.name, st.name from tutor t join office o o.id=t.officeid join students st on t.id=st.tutorid where t.name like '%y%';
3. Co select o.name, t.name, st.name from tutor t join office o on o.id=t.officeid join students st t.id=st.tutorid where t.name like '%y%';
4. Do select o.name, t.name, st.name from tutor t join office o on o.id=t.officeid join students st on t.id=st.id where t.name like '%y%';
100Retrieve information about office and their tutors as well. 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))}.
1. Ao select *from office o join tutor t in o.id=t.officeid;
2. Bo select *from office o join tutor t on o.id=t.officeid;
3. Co select *form office o join tutor t on o.id=t.officeid;
4. Do select *from office o join tutor t on o.id=t.id;
101Retrieve office name and tutor name, which have well experienced tutors. 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))}.
1. Ao select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select max (experience) from tutor);
2. Bo select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select min (experience) from tutor);
3. Co select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select high (experience) from tutor);
4. Do select o.name, t.name from office o join tutor t on o.id=t.officeid where t.experience = (select low (experience) from tutor);
102Retrieve tutors’ name and the number of students for each of them. 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))}.
1. Ao select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.tutorid;
2. Bo select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.tutoridgroup by t.name;
3. Co select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.idgroup by t.name;
4. Do select count (s.name) as numberofstudents, t.name from tutor t join students s on t.id=s.id;
103Retrieve office name and the number of students for each of them. 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))}.
1. Ao select count (s.name) as numberofstudents, o.name from tutor t join students s on t.id=s.tutoridjoin office o on o.id=t.officeid group by o.name;
2. Bo select count (s.name) as numberofstudents, o.name from students s on join office o on o.id=s.id group by o.name;
3. Co select count (s.name) as numberofstudents, o.name from tutor t join students s on t.id=s.tutoridjoin office o o.id=t.officeid group by o.name;
4. Do select count (s.name) as numberofstudents, o.name from tutor t join students s on t.id=s.idjoin office o on o.id=t.id group by o.name;
104Retrieve all students’ name and scholarship, tutors name except students who have scholarship for 3000$. 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))
1. Ao select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship <> 3000;
2. Bo select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship = 3000;
3. Co select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship < 3000;
4. Do select s.name, t.name from students s join tutor t on s.tutorid=t.id where s.scholarship is 3000;
105Define inner join
1. Ao returns all rows from the left table (1), with the matching rows in the right table (2)
2. Bo selects all rows from both tables as long as there is a match between the columns in both tables
3. Co returns all rows from the right table (2), with the matching rows in the right table (1)
4. Do returns all rows from the left table (1) and from the right table (2)
106Define left join
1. Ao returns all rows from the right table (2), with the matching rows in the right table (1)
2. Bo selects all rows from both tables as long as there is a match between the columns in both tables
3. Co returns all rows from the left table (1), with the matching rows in the right table (2)
4. Do returns all rows from the left table (1) and from the right table (2)
107Define an union
1. Ao combines the result set of two or more select statements
2. Bo returns all rows from the left table (1) and from the right table (2)
3. Co returns all rows from the right table (2), with the matching rows in the right table (1)
4. Do returns all rows from the left table (1), with the matching rows in the right table (2)
108INNER JOIN and JOIN are the same
1. ATrue
2. BFalse
109Retrieve students’, scholarship and teacher's’ name, who have more than average scholarship. 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))
1. Ao select s.name, s.scholarship, t.name from students s, tutor t where t.id=s.tutorid and s.scholarship> (select avg(scholarship) from students)
2. Bo select s.name, s.scholarship, t.name from students s, tutor t where t.id=s.tutorid and s.scholarship> avg(scholarship)
3. Co select s.name, s.scholarship, t.name from students s, tutor t where s.scholarship> (select avg(scholarship) from students)
4. Do select s.name, avg(s.scholarship), t.name from students s, tutor t
110Retrieve book title and total number of book copies and price, which has id 1. Book {bookid, title, publisher_name, price}. BookCopies {bookid, libraryid,noOfCopies [references Book (bookid)]}
1. Ao 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;
2. Bo select b.title, sum (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1;
3. Co 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;
4. Do select b.title, count (no_of_copies) from book b join book_copies bc on b.bookid=bc.bookid where b.bookid=1;
111Retrieve 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)]}
1. Ao select b.title, b.price, b.price *2 as increasedprice from book b join book_copies bc on bc.bookid=b.bookid;
2. Bo select b.title, b.price book b join book_copies bc on bc.bookid=b.bookid where bc.no_of_copies > 20;
3. Co impossible to solve
4. Do 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;
112Retrieve 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}.
1. Ao 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;
2. Bo 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;
3. Co 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;
4. Do 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;
113Retrieve the number of departments in the department table. Tables: Department {id, name}. Employee {id, name, salary, dep_id (references Department (id))}
1. Ao select count(id) from department
2. Bo select sum (id) from department;
3. Co select coun(id) from department
4. Do select id from department
114Retrieve total salaries payable to employee. Tables: Department {id, name}. Employee {id, name, salary, dep_id (references Department (id))}
1. Ao select count(salary) from employee;
2. Bo select sum (id) from employee;
3. Co select sum (salary) from employee;
4. Do select max (salary) from employee;
115Retrieve 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))
1. Ao select name from students where name like 'A';
2. Bo select name from students where name like 'A_%';
3. Co select name from students where name like 'a%';
4. Do select name from students where name like '%A%'
116Retrieve 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))
1. Ao 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';
2. Bo select s.name, o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;
3. Co 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';
4. Do 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;
117Find 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))
1. Ao select averg (scholarship) from students ;
2. Bo select avg (scholarship) from students ;
3. Co select av (scholarship) from students ;
4. Do select avrg (scholarship) from students ;
118Retrieve 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))
1. Ao 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;
2. Bo select avg (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid ;
3. Co select avg (scholarship), o.name from students s join tutor t on t.id=s.tutorid group by o.name;
4. Do select avg (scholarship), o.name from students s d join office o on o.id=t.officeid group by o.name;
119Retrieve 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))
1. Ao 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;
2. Bo 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;
3. Co select count (s.id), o.name from students s join tutor t
4. Do 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;
5. Eo on t.id=s.tutorid join office o on o.id=t.officeid;
120Retrieve 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))
1. Ao 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;
2. Bo 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;
3. Co select sum (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;
4. Do select count (scholarship), o.name from students s join tutor t on t.id=s.tutorid join office o on o.id=t.officeid;
121Consider the EMPLOYEES table. Which of the following statements count the number of employees within each department?
1. Ao SELECT department_id, COUNT(last_name) FROM employees GROUP BY department_id
2. Bo SELECT department_name, COUNT(last_name) FROM employees GROUP BY department_id
3. Co SELECT department_id, department_name, COUNT(last_name) FROM employees GROUP BY department_id
4. Do SELECT department_id, COUNT(last_name) FROM departments GROUP_BY last_name
122You can rename a table with the RENAME or the ALTER TABLE command.
1. ATrue
2. BFalse
123Write SQL statement for display the department numbers with more than employees in each dept.
1. Ao Select deptno, count(deptno) from emp group by deptno having count(*)>3;
2. Bo Select deptno, count (deptno) from emp group by deptno having count(*)>2;
3. Co Select deptno, count (emp) from deptno group by emp having count(*)>2;
4. Do Select deptno, count(deptno) from deptno group by emp having count(*)>3;
124You can add and drop columns from a table using the ALTER TABLE command.
1. ATrue
2. BFalse
125Consider the EMPLOYEES table. Which of the following SQL statements is correct to provide the sentence in the format “King has been working since 01.01.1987” with “Employee Information” alias?
1. Ao SELECT last_name ||’has been working since’ || hire_date “Employee Information” FROM employees;
2. Bo SELECT last_name || ‘has been working since’ || hire_date Employee Information FROM employees
3. Co SELECT last_name || ‘has been working since’ || hire_date ‘Employee Information’ FROM employees;
4. Do SELECT last_name || ”has been working since” || hire_date “Employee Information” FROM employees;
126Which keyword is used to provide an alias to a column?
1. Ao COLUMN
2. Bo AS
3. Co ALIAS
4. Do IS
127You can place the subquery in a number of SQL clauses, including the following:
I. WHERE clause
II. HAVING clause
III. FROM clause
1. Ao I and II
2. Bo I,II and III
3. Co I only
4. Do I and III
128Consider the SQL statement. Which of the following clauses does not sort the result rows by salary value in the ascending order?
SELECT last_name “Employee”, salary “Salary”
FROM employees
1. Ao ORDER BY 2;
2. Bo ORDER BY salary;
3. Co ORDER BY “Salary” ASC;
4. Do ORDER BY Salary DESC;
129Which keyword is used to eliminate duplicate rows in the result?
1. Ao DISTINCT
2. Bo DESCRIBE
3. Co HAVING
4. Do GROUP BY
130What will be the result of the following SQL statement? (SYSDATE is a date of your exam)
SELECT TO_CHAR(SYSDATE, ‘Day’) FROM Dual;
1. Ao 31.05.2017
2. Bo Friday
3. Co 31
4. Do 31-05.2017
131What will be the result of the following SQL statement? (SYSDATE)
SELECT TO_CHAR(SYSDATE, ‘DD.MM.YY’) FROM Dual;
1. Ao 31.May.2017
2. Bo An error will occur
3. Co 31.05.2017
4. Do 31.05.17
132Select all statements that correctly describe the diagram below
1. AThe diagram is notated using Crows Foot notation
2. BThe diagram notated using Chen notation
3. CThe diagram has a one-to-many relationship.
4. DA business rule inferred by this diagram could correctly be, "A customer makes one or many payments."
5. EThe diagram has a many-to-many relationship.
133What type of relationship is expressed with the business rules? Each store is managed by one employee; Each employee manages at most one store
1. AM:N (many to many)
2. B1:5 (one to five)
3. C1:1 (one to one)
4. D1:M (one to many)
134Select the alternative that correctly identifies the relationship classification described by the two business rules below. Each student takes one or more classes.Each class may be taken by one or more students.
1. AM:N (many to many)
2. B0:1 (zero to one)
3. C1:M (one to many)
4. D1:5 (one to five)
5. E1:1 (one to one)
135Select all SQL statements that will correctly list all the contents from the PRODUCT table.
1. AGET FROM PRODUCT
2. BSELECT *FROM PRODUCT
3. CSELECT ALL FROM PRODUCT
4. DLIST * FROM PRODUCT
136The student is requested to delete all rows in the Plant table that have a plant_species value of "FicusBenjamina".The student uses the following SQL command: DELETE FROM plant WHERE plant_num = 1; Select all statements that correctly describe the student's solution
1. AThis SQL command deletes some but not all of the rows in the Plant table which have a plant_species value of "FicusBenjamina"
2. BThis SQL command does not delete any rows in the Plant table which have a plant_species value of "FicusBenjamina".
3. CIf the WHERE clause in the SQL statement provided by the student were to be replaced with "WHERE plant_species='FicusBenjamina'", then that SQL command would delete all of the rows in the Plant table which have a plant_species value of "FicusBenjamina".
4. DThis SQL command deletes all of the rows in the Plant table which have a plant_species value of "FicusBenjamina".
5. EThis SQL command deletes row(s) in the Plant table which have a plant_species value of "ChamaedoreaSeifritzii".
137The student is requested to delete all rows in the Plant table that have a plant_species value of "FicusBenjamina".The student uses the following SQL command: DELETE FROM plant WHERE plant_num = 1; Select all statements that correctly describe the student's solution
1. AThis diagram could be part of a conceptual model for a relational database
2. BThe diagram includes five one-to-many relationships.
3. CThe diagram is notated using Crow’s foot notation.
4. DThe diagram includes three one-to-many relationships.
138Examine the following entity relationship diagram, then select all statements that correctly describe the diagram
1. AA DRUM is associated with at most one DRUMMER.
2. BDRUMMER is associated with zero or more DRUMs.
3. CThe DRUM entity has a primary keydrummer_id.
4. DBoth DRUM and DRUMMER are weak entities.
5. EThe identity of every DRUM instance depends on an associated DRUMMER instance.
139You are given the following PLANT table: Note that PLANT_ID is the primary key in this table. Choose the entity relationship diagram in Crow's foot notation which correctly represents this table
1. A
2. B
3. C
4. D
140A database designer first models aircraft using this entity relationship diagram: After doing so, the database designer realizes that the HEIGHT attribute is multi-valued. The designer knows that the wing height, body height, and cock pit height should be stored in the database, and that heights of more aircraft sections may be needed in the future. Select the best implementation of the HEIGHT multi-valued attribute from the given possibilities.
None of the given implementations correctly implement the multivalued HEIGHT attribute.
1. A
2. B
3. C
4. DNone of the given implementations correctly implement the multivalued HEIGHT attribute.
141For the following relational schema, choose the correct business rule
1. AEach Customer has more than one Membership.
2. BOnly one Customer may have the same Membership.
3. CEach Membership has a unique price and title.
4. DIt represents many-to-many (M:N) relationship.
142You are given the following diagram notated using Crow's Foot notation: For each row in Table2, how many related rows can be in Table1? Select all answers that correctly answer this question
1. AOne or more.
2. B1
3. C0
4. DOnly 0, 1, 2, or 3. 
5. EThere is not enough information to determine the number of related rows.
143 Read the business rules below which pertain to entities termed DISK and COMPUTER, then follow the subsequent instructions. Neither DISK nor COMPUTER is a weak entity COMPUTER is not optional to DISK, and DISK is not optional to COMPUTER . Select the correct entity relationship diagram which matches the requirements for the DISK and COMPUTER entities. Choose all designs that correctly implement the business rules.
1. A
2. B
3. C
4. D
144You are given the following basic Crow's foot entity relationship diagram.
1. ANone of the other answers are correct.
2. BEach device may be attached to many computers; each computer is attached to one device.
3. CEach computer is attached to one device; each device is attached to one or more computers.
4. DEach computer may be attached to zero to many devices; each device is attached to one computer.
5. EEach computer is attached to one or more devices; each device is attached to one computer.
145 You are given the following basic Crow's foot entity relationship diagram. Select all business rules that can be correctly inferred from the following diagram.
1. AEach apartment is lived in by one tenant only
2. BEach tenant lives in one to many apartments
3. CEach tenant lives in exactly one apartment
4. DEach tenant must live in many apartments
146Select all that are required of a table so that it is considered to be normalized to second normal form (2NF). (Check all that are required for a table to be in 2NF.)
1. AIf there is a composite primary key, then no columns that are not part of the primary key will depend on only part of a composite primary key
2. BThere are no null values
3. CThere are no transitive dependencies 
4. DThere are many repeating groups in the table
147According to the diagram, which of the following are true? (Check all correct assertions regarding the diagram below.)
1. A50%The diagram has a one-to-many relationship
2. B-50%The diagram is notated using Crow's Foot notation
3. C50%The diagram is a modified UML class diagram
4. D-50%The diagram has a many-to-many relationship
5. E-50%There is no relationship in this diagram
148Select all statements that correctly described first normal form (1NF)
1. AA table that conforms to 1NF must have no repeating groups.
2. BA table that conforms to 1NF must have no missing or null data.
3. CA table that conforms to 1NF must have no transitive dependencies.
4. DA table that conforms to 1NF must have no partial dependencies.
5. EA table that conforms to 1NF must have data arranged in rows and columns.
149Select all that are true of primary keys. (Check all that are true.)
1. APrimary keys must be integers.
2. BPrimary keys are often referenced as foreign keys.
3. CIt is acceptable that a primary key column contains a null value, as long as there is only one null value, to maintain uniqueness.
4. DSurrogate primary keys are especially helpful when there is no natural primary key.
5. EOne way composite primary keys occur is when an existence-dependent entity includes the primary key of the parent entity as part of its composite primary key.
150Which of the following are true of database design? (Check all that are true.)
1. AAn early step is identifying the entities, attributes, and relationships.
2. BThe normalization process might yield additional entities and attributes that need to be added to the ERD.
3. CNormalization is applied at all stages of design of a relational database, beginning with conceptual design.
4. DThe design process for a new database usually begins with conceptual design, then proceeds to logical design, and then to physical design.
151Select all statements that are best practices for database design
1. AThe database tables should only be normalized after performance problems occur.
2. BIt is best to design a relational database schema iteratively by first creating the tables using SQL, viewing the design in the database, then suggesting changes to the structure.
3. CEntities should be normalized before the table structures are created.
4. DRelationships between the entities should be defined before the table structures are created.
152Review the SQL below and check all answers that are correct with respect to the SELECT statement:
1. AThe query will never return any rows; no matter what data is in the CUSTOMER table.
2. BThe SELECT statement is not syntactically correct.
3. CWe cannot determine how many rows the query will return.
4. DThe query will return no rows, because a last_name cannot be both 'Smith', 'Jones', and 'Salk'.
5. EAll answers above are correct.
153 Relationships can be redundant?
1. ATrue
2. BFalse
154What uncommon relationship is described by the statements: "Each DNA SAMPLE may be taken from one and only one PERSON and each PERSON may provide one and only one DNA SAMPLE"
1. AOne to One Optional
2. BMany to Many Mandatory
3. COne to Many Mandatory
4. DOne to Many Optional
155What relationship is described by the statements: "Each CUSTOMER may place one or more ORDERs, each ORDER must be placed by one and only one CUSTOMER"
1. AOne to Many
2. BMany to Many
3. CMany to More
4. DOne to One
5. Enone
156A relationship on an ERD can have attributes.
1. ATrue
2. BFalse
157Which of the following pairs of entities is most likely to be modeled as a 1:1 relationship?
1. Atree and branch
2. Bteacher and subject area
3. Cperson and fingerprint
4. Dcar and wheel
158Which of the following attributes is suitable to be a Unique Identifier?
1. AAddress
2. BFirst name
3. CLast name
4. DPassport ID
159A/an _________ is a piece of information that in some way describes an entity. It is a property of the entity and it quantifies, qualifies, classifies, or specifies the entity.
1. ATable
2. BERD
3. CProcess
4. DAttribute
160All of the following would be instances of the entity PERSON except which?
1. ADavid Jones
2. BAngelina Rosalie
3. CMale
4. DGrace Abinajam
161Which of the following pairs of entities is most likely to be modeled as a M:M relationship?
1. ATREE and BRANCH
2. BTEACHER and SUBJECT AREA
3. CCAR and WHEEL
4. DPERSON and FINGERPRINT
162One to many relationships are the most uncommon type of relationships in an ERD.
1. ATrue
2. BFalse
163 What are the three properties that every relationship should have?
1. AName, optionality, cardinality
2. BA UID bar, a diamond, an arc
3. CName, optionality, arcs
4. DTransferability, degree, name
164Relationships can be either mandatory or optional. True or False?
1. ATrue
2. BFalse
165When reading a relationship between 2 entities, the relationship is read both from left to right and right to left. True o rFalse?
1. ATrue
2. BFalse
166Entities are usually verbs. True or False?
1. ATrue
2. BFalse
167Unique Identifiers….
1. Adistinguish one entity from another
2. Bdistinguish one instance of an entity from all other instances of that entity
3. Cdistinguish all entities in a database
4. Ddistinguishes nothing
168Each row in a database table is called a
1. AAttribute
2. BColumn
3. CRecord
4. DField
169Each column in a database table is called a ________
1. AField
2. BRow
3. CEntity
4. DTuple
5. ERelationship
170 ___________is a field or fields that contain a unique identifier for each record in a primary table.
1. AComposite key
2. BForeign key
3. CPrimary key
4. DDuplicate key
5. ESurrogate key
171_is a field or field sin a related table that refer to the primary key in a primary table.
1. APrimary key
2. BComposite key
3. CForeign key
4. DCandidate key
5. ESurrogate key
172 _exists in a relational database when one record in a primary table has many related records in a related table.
1. AOne-to-one
2. BOne-to-many
3. CMany-to-many
4. DZero-to-one
5. ENo correct answer
173 Which of the following entities most likely contains invalid attributes?
1. AEntity: Home. Attributes: Number of Bedrooms, Owner, Address, Date Built
2. BEntity: Pet. Attributes: Name, Birthdate, Owner
3. CEntity: Car. Attributes: Owner Occupation, Owner Salary, Speed
4. DEntity: Mother. Attributes: Name, Birthdate, Occupation, Salary
174What uncommon relationship is described by the statements: “Each LINE must consist of many POINTS and each POINT must be a part of many LINES”
1. AOne to Many Optional
2. BOne to Many Mandatory
3. CMany to Many Optional
4. DMany to Many Mandatory
175Which symbol is used to show that a particular attribute is mandatory?
1. A#
2. B*
3. C&
4. DO
5. E+
176A table is in 3NF if it has met all database requirements for both 1NF and 2NF and transitive dependencies are eliminated.
1. ATrue
2. BFalse