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

Жарас Мергазы 180 questions

Take the quiz

Questions in this quiz (180)

1What is the Basic unit of storage which composed of rows?
1. A· View
2. B• Table
3. C• Sequence
4. D• Synonym
2 What kind of objects Generates numeric values?
1. A• Table
2. B• Index
3. C• Synonym
4. D• Sequence
3 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
4 Table and column names must be, not longer than?
1. A• 25 characters
2. B• 35 characters
3. C• 30 characters
4. D• 15 characters
5What kind of constraints uses to brake replication?
1. A• UNIQUE
2. B• NOT NULL
3. C• FOREIGN KEY
4. D• CHECK
6What 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.
7What 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.
8What 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.
9How 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).
10What 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

11What operator is not uses to comparison of Multiple-row subqueries?
1. A• IN
2. B• ALL
3. C• BETWEEN
4. D• ANY
12What 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
13What kind of join includes into OUTER JOIN?
1. A• Cross join
2. B• Full join
3. C• Inner join
4. D• Natural join
14The 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
15What command included to DML?
1. A• Create
2. B• Insert
3. C• Commit
4. D• Grant
16What command of DML could replace the Update or Insert?
1. A• Alter
2. B• Truncate
3. C• Merge
4. D• Commit
17What 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
18Which command uses to erase whole data from table?
1. A• Drop
2. B• Revoke
3. C• Truncate
4. D• Rollback
19Which command restores the database to last commited state?
1. A• Savepoint
2. B• Commit
3. C• Rollback
4. D• Back up
20What kind of the group function uses to compute total number of rows?
1. A• AVG
2. B• SUM
3. C• COUNT
4. D• MAX
21How many rows this command will retrieve? SELECT MIN(hire_date) FROM employees;
1. A0
2. B1
3. C2
4. D3
22What 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
23When 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
24What 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
25How to show Full name of the month of any DATE type rows?
1. A• MM
2. B• MONTH
3. C• MON
4. D• MI
26Which 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
27What 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
28Where we do not use the Single-row functions?
1. A• SELECT
2. B• GROUP BY
3. C• WHERE
4. D• ORDER BY
29What function does not exists the Case-conversion functions?
1. A• LENGTH
2. B• LOWER
3. C• UPPER
4. D• INITCAP
30What function Character-manipulation functions exists?
1. A• LOWER
2. B• CONCAT
3. C• COUNT
4. D• NVL
31What the result of using function? INITCAP('SQL Course')
1. A• SQL COURSE
2. B• sql course
3. C• Sql Course
4. D• Sql course
32Which function Finds the Numeric position of a named character?
1. A• LENGTH
2. B• LPAD
3. C• SUBSTR
4. D• INSTR
33Column 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
34Which 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;
35Which 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.
36What kind of Arithmetic Expressions uses with Date type:
1. A• / - divide
2. B• - minus
3. C• % - percent
4. D• multiply
37How to denotes one character at LIKE condition (WHERE Clause)?
1. A• % - percent
2. B• = - equal
3. C• & - ampersand
4. D• _ - underscore
38How many Logical Operators do the Oracle DBMS have?
1. A1
2. B2
3. C3
4. D4
39What 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;
40What kind of signs uses to retrieve Substitution Variable Forms?
1. A• <>, !=
2. B• +, -, *, /
3. C• %, _
4. D• &&, &
41DEFINE 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
42Indexes 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
43You 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.
44Identify 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;
45From what kind of system table we can retrieve NEXTVAL and CURRVAL
1. A• Data
2. B• System
3. C• Dual (?)
4. D• Store
46How called the Nickname for columns?
1. A• Synonym
2. B• Alias
3. C• Macros
4. D• Byname
47. 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
48What kind of constraints included into PRIMARY KEY?
1. A• NVL
2. B• Nullif
3. C• Check
4. D• Unique
49Table 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 #
50Which 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;
51What object Gives alternative names to objects?
1. A• Table
2. B• Synonym
3. C• View
4. D• Index
52What object Improves the performance of some queries?
1. A• Table
2. B• Synonym
3. C• View
4. D• Index
53How to erase any Schema objects?
1. A• Delete
2. B• Drop(?)
3. C• Truncate
4. D• Destroy
54What object have NEXTVAL and CURRVAL Pseudocolumns?
1. A• Table
2. B• Synonym
3. C• Sequences
4. D• Index
55What 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
56What 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
57What 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
58What data type have Variable-length numeric data?
1. A• INT(p)
2. B• BIGINT(p)
3. C• NUMBER(p,s)
4. D• LONG
59What data type have Date and time values?
1. A• TIME
2. B• DATE
3. C• CLOB
4. D• BLOB
60What kind of the group function uses to compute average number in rows?
1. A• AVG
2. B• SUM
3. C• COUNT
4. D• MAX
61How many rows this command will retrieve? SELECT MAX(hire_date) FROM employees;
1. A0
2. B1
3. C2
4. D3
62What 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
63When 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
64Where we do not use the Single-row functions?
1. A• SELECT
2. B• GROUP BY
3. C• WHERE
4. D• ORDER BY
65What function does not exists the Case-conversion functions?
1. A• INSTR
2. B• LOWER
3. C• UPPER
4. D• INITCAP
66What function Character-manipulation functions exists?
1. A• LOWER
2. B• SUBSTR
3. C• COUNT
4. D• NVL
67What 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;
68What kind of signs uses to show non equal values?
1. A• <>, !=
2. B• +, -, *, /
3. C• &&, &
4. D• %, _
69What kind of signs uses to compute arithmetic operations?
1. A• <>, !=
2. B• +, -, *, /
3. C• %, _
4. D• &&, &
70What kind of constraints included into PRIMARY KEY?
1. A• NVL
2. B• Nullif
3. C• Check
4. D• Not null
71Table 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 #
72Which 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;
73Column 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
74 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);
75Which 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.
76What kind of Arithmetic Expressions uses with Date type:
1. A• / - divide
2. B• + - add
3. C• % - percent
4. D• multiply
77How to denotes one or more character at LIKE condition (WHERE Clause)?
1. A• % - percent
2. B• = - equal
3. C• & - ampersand
4. D• _ - underscore
78. How many Arithmetic Operators do the Oracle DBMS have?
1. A1
2. B2
3. C3
4. D4
79 What kind of signs uses to skip characters at LIKE function?
1. A• <>, !=
2. B• +, -, *, /
3. C• %, _
4. D• &&, &
80What the result of using function? LOWER('SQL Course')
1. A• SQL COURSE
2. B• sql course
3. C• Sql Course
4. D• Sql course
81What the result of using function? UPPER('SQL Course')
1. A• SQL COURSE
2. B• sql course
3. C• Sql Course
4. D• Sql course
82What the result of using function? CONCAT('Hello', 'World')
1. A• Hello World
2. B• HelloWorld
3. C• elloWorld
4. D• Hello
83What the result of using function? SUBSTR('HelloWorld',1,5)
1. A• Hello World
2. B• HelloWorld
3. C• elloWorld
4. D• Hello
84What the result of using function? ROUND(45.926, 0)
1. A• 45.0
2. B• 45.9
3. C• 46.0
4. D• 0
85What the result of using function? TRUNC(45.926, 0)
1. A• 45.0
2. B• 45.9
3. C• 46.0
4. D• 0
86What the result of using function? MOD(1600, 300)
1. A• 1600
2. B• 300
3. C• 100
4. D• 500
87From what kind of system table we can retrieve SYSDATE?
1. A• Data
2. B• System
3. C• Dual
4. D• Store
88From what kind of system table we can retrieve CURRENT_DATE?
1. A• Data
2. B• System
3. C• Dual
4. D• Store
89What 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'
90What 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'
91Retrieve 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;
92Retrieve 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;
93Retrieve 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;
94Retrieve 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;
95Sort 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;
96Find 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;
97 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;
98Retrieve 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’
99Retrieve 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);
100Retrieve 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';
101Retrieve 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);
102Increase 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;
103Retrieve 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%';
104Retrieve 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;
105Retrieve 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);
106Retrieve 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;
107Retrieve 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;
108Retrieve 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;
109Define 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)
110Define 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)
111Define 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)
112INNER JOIN and JOIN are the same
1. ATrue
2. BFalse
113Retrieve 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
114Retrieve 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;
115Retrieve 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;
116Retrieve 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;
117Retrieve 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
118Retrieve 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;
119Retrieve 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%'
120Retrieve 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;
121Find 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 ;
122Retrieve 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;
123Retrieve 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;
124Retrieve 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;
125Consider 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
126You can rename a table with the RENAME or the ALTER TABLE command.
1. ATrue
2. BFalse
127Write 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;
128You can add and drop columns from a table using the ALTER TABLE command.
1. ATrue
2. BFalse
129Consider 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;
130Which keyword is used to provide an alias to a column?
1. Ao COLUMN
2. Bo AS
3. Co ALIAS
4. Do IS
131You 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
132Consider 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;
133Which keyword is used to eliminate duplicate rows in the result?
1. Ao DISTINCT
2. Bo DESCRIBE
3. Co HAVING
4. Do GROUP BY
134What 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
135What 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
136Select 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.
137What 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)
138Select 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)
139Select 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
140The 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".
141The 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.
142Examine 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.
143You 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
144A 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. ANone of the given implementations correctly implement the multivalued HEIGHT attribute.
2. B
3. C
4. D
145For 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.
146You 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.
147 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
148You 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.
149 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
150Select 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
151According 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
152Select 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.
153Select 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.
154Which 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.
155Select 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.
156Review 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.
157 Relationships can be redundant?
1. ATrue
2. BFalse
158What 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
159What 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
160A relationship on an ERD can have attributes.
1. ATrue
2. BFalse
161Which 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
162Which of the following attributes is suitable to be a Unique Identifier?
1. AAddress
2. BFirst name
3. CLast name
4. DPassport ID
163A/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
164All of the following would be instances of the entity PERSON except which?
1. ADavid Jones
2. BAngelina Rosalie
3. CMale
4. DGrace Abinajam
165Which 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
166One to many relationships are the most uncommon type of relationships in an ERD.
1. ATrue
2. BFalse
167 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
168Relationships can be either mandatory or optional. True or False?
1. ATrue
2. BFalse
169When 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
170Entities are usually verbs. True or False?
1. ATrue
2. BFalse
171Unique 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
172Each row in a database table is called a
1. AAttribute
2. BColumn
3. CRecord
4. DField
173Each column in a database table is called a ________
1. AField
2. BRow
3. CEntity
4. DTuple
5. ERelationship
174 ___________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
175_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
176 _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
177 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
178What 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
179Which symbol is used to show that a particular attribute is mandatory?
1. A#
2. B*
3. C&
4. DO
5. E+
180A 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