Font size
WorksheetsQuiz 1
Total questions: 75
Worksheet time: 56mins
How do you express the time 1:23 PM and 45 seconds in standard SQL:
'1PM:23:45'
time '13:23:45'
'13:23.45'
'1PM:23.45'
Consider the table T(A: int, B: int). How do you declare T in SQL?
create table T (A int, B int, primary key (A), primary key (B))
create table T (A int, B int, primary key (A, B))
create table T (A int primary key, B int primary key)
create table T (A int, B int, primary key)
Consider the tables T(A: int, B: int) et S(#A: int, #B: int, C: int). How do you declare S in SQL?
create table S (A int, B int, C int, foreign key (A, B) references T(A, B))
create table S (A int, B int, C int, references T(A, B))
create table S (A int references T(A), B int references T(B), C int)
create table S (A int foreign key, B int foreign key, C int)
Consider the table T declared as follows: create table T (A varchar(20), B real). Which of the following statements insert the tuple A="Hello", B=1.0 into T?
insert values ('Hello', 1.0) into T
insert into T values ('Hello', 1.0)
insert T ('Hello', 1.0)
insert into T (A, B) values ('Hello', 1.0)
Consider the table T(A: int, B: int). Which of the following statements set attribute A to 3 and attribute B to 0 for all the tuples with A equal to 2?
update T set A = 3, B = 0 where A = 2
update T set A = A + 1, B = 0 where A = 2
update T set A + 1, 0 where A = 2
update T set A = A + 1, set B = 0 where A = 2
How is the date 30 September 2045 expressed in standard SQL?
'09-30-2045'
date '2045-09-30'
date '30-09-2045'
'2045-30-09'
Which of the following SQL statements would delete table T?
drop table T
truncate table T
delete from T
delete from T where true
Which of the following statements are true about the NULL value?
NULL et 'NULL' sont equivalents
NULL et null sont équivalents
NULL signifie valeur inconnue ou valeur non applicable
NULL ne s'applique qu'aux types numériques
Which of the following pairs of words are synonymous?
attribut et ligne (row)
colonne (column) et tuple
table et relation
ligne (row) et tuple
In the declaration of a trigger, which of the following keyword can precede the definition of the triggering event?
during
before
after
when
Consider the tables T(#A) and S(A). Which SQL statement can violate the foreign key constraint?
an insert into S
an update of T
a delete from S
a delete from T
Consider the table T(A, B) declared as follows:
create table T (A int check(A between 0 and 10), B int);
Which statement triggers the check?
update T set B = 1 where A = 3;
delete from T where A = 1;
select distinct A from T;
insert into T values (null, null);
Consider the table T declared as follows:
create table T (A int primary key, B int unique);
with the following population:
Which statement completes successfully?
insert into T values (5, null);
insert into T values (3, 0);
update T set B = 0 where A = 2;
delete from T where A = 1 or B is null;
Consider the tables T and S declared as follows:
create table T (A int primary key);
create table S(B int references T(A) on update cascade on delete set null):
Which of the following statements regarding cascade mode are correct?
an update of T can result in an update of S
an update of S can result in an update of T
a delete from S can result in an update of T
a delete from T can result in a delete from S
Which of the following statements are true regarding the comparison between primary key and unique key?
there can be several unique keys in a table, but there can only be one primary key
a unique key attribute can be null, a primary key attribute cannot
there can be several primary keys in a table, but there can only be one unique key
a primary key attribute can be null, a unique key attribute cannot
A trigger is also called an ECA rule. In this acronym :
A stands for Action
A stands for Atomic
C stands for Concurrent
E stands for Extended
Which of the following mechanisms in a relational DBMS are part of the constraints family?
triggers
foreign keys
tuple-based
assertions
Let a tuple-based constraint be defined on a table T. Which SQL statements passed on table T trigger the constraint check?
insert
select
delete
update
The method executeUpdate is used to execute the following SQL statements:
select
update
insert
delete
PreparedStatement's can be used to execute the following SQL statements:
update
insert
delete
select
The prototype of the methods createStatement and prepareStatement is:
PreparedStatement prepareStatement()
Statement createStatement(String)
PreparedStatement prepareStatement(String)
Statement createStatement()
The interface ResultSet defines the following methods:
next
setString
getString
getFloat
JDBC means:
Java Database Connectivity
Java Data Connection
Java Database Components
Java Base Connection
Comparison between Statement and PreparedStatement :
a PreparedStatement runs more efficiently than a Statement
Statement and PreparedStatement can execute the same SQL statements
a Statement can execute any SQL command whereas a PreparedStatement is limited to the select
a PreparedStatement prevents SQL injection attacks
The executeQuery method of the JDBC library :
returns an object of type ResultSet
is defined in the PreparedStatement interface
is defined in the Statement interface
returns an int
The prototype of the setString method of the PreparedStatement interface is :
void setString(int, String)
void setString(String)
void setString(int)
void setString()
A transaction...:
may contain a single SQL statement
must contain only one SQL statement
may contain several SQL statements
must contain several SQL statements
What is the correct order of isolation levels:
Atomicity < Durability
Read Committed < Repeatable Read
Serializability < Read Uncommitted
Consistency < Repeatable Read
A transaction ends when...:
the commit statement is issued
the rollback statement is issued
a failure (e.g. division by 0) occurs
a constraint is violated
In the ACID acronym:
A stands for Anomaly
I stands for Isolated
C stands for Constraint
D stands for Data
Transactions have the following properties:
Durability
Compatibility
Integrity
Efficiency
Which of the following statements are correct
'Phantom Reads' occurs if the transaction is running with a 'Serilizable' isolation level
'Dirty Read' occurs if the transaction is running with an 'Uncommitted read' isolation level
'Phantom Reads' occurs if the transaction 'runs with a Repeatable Read isolation level
'Dirty Read' occurs if the transaction is executed with a 'Committed read' isolation level
About insolation levels :
The SERIALIZABLE insulation level is the highest
A transaction that updates the database can safely use the READ UNCOMMITTED isolation level
SQL defines three standard isolation levels
Le niveau d'isolation SERIALIZABLE accroît le parallélisme des transactions
The current transaction is validated (commits):
when a new transaction is started
when the rollback order is executed
as soon as the first SQL command of the transaction is successfully executed
if the program running the transaction abruptly terminates in the middle of the transaction
Transactions have the following properties:
isolation
atomicity
durability
consistency
Which one is used as the primary key in MongoDB?
id
_uid
_uuid
_id
What are the fundamental properties to characterise databases?
Partition Tolerance
Availability
Consistency
Atomicity
_________ is a binary serialization format used to store documents in MongoDB.
Sql
BSON
GridFS
JSON
MongoDB is a database _________ that offers high performance.
Key/value
Oriented document
Oriented graph
Oriented column
About JSON. Which statement below is true?
A category of documents generated by a word processor
Json inherits the syntax for creating objects in JavaScript
JSON is a data representation format
An object-oriented programming language
___________ data models allow applications to store related pieces of information in the same database record.
None of the mentioned
Reference
External
Embedded
A collection in MongoDB is group of ............
Schema
Databases
Related documents
Rows
Normalized data models describe relationships using ___________ between documents.
evaluation
relativeness
none of the mentioned
references
Which of the following is not a NoSQL database?
SQL Server
Cassandra
MongoDB
Redis
A collection and a document in MongoDB is equivalent to....................... concepts respectively.
Column and Row
Table and Column
Table and Row
Database and Table
Which MongoDB filters below are equivalent to the following SQL clause:
WHERE att BETWEEN val1 AND val2
{att: { $lte: val2, $gte: val1} }
{ att: { $gte: val1, $lte:val2 } }
{ att: { $gt: val1, $lt:val2} }
{ att: [ val1, val2 ] }
Which of the following methods matches the Order by clause in MongoDB?
sort()
orderBy()
Order()
sortBy()
Which of the following filters translates "the field" foo "is different from 10": (foo!=10)
{ foo: { $ne: 10 } }
{ foo: !10 }
{ foo: {$not: { $eq: 10 } } }
{ $not: {foo: 10 } }
Which documents below match the filter { tab: { $gt: 3, $lt: 0 } :
{ tab: [ 5, -1 ] }
{ tab: [ 5, 0 ] }
{ tab: [ 0,3 ] }
{ tab: [ 0, -1 ] }
How to specify "and" between these two conditions {f1: v1} and {f2: v2}:
{$and: {f1: v1}, {f2: v2}}
{{f1: v1} $and {2: v2}}
{$and: {f1: v1, f2: v2}}
{$and: [{f1: v1}, {f2: v2}]}
"foo" is a field with an array value. Which filter retrieves the documents with a foo array of size 3:
{foo: {$size: 3}}
{foo: 3}
{$size: {foo: 3}}
{foo, $size, 3}
Which of the documents below matches the filter { foo: "a?.*z$" } :
{ foo: "a?.*z$" }
{ foo: "z" }
{ foo: "a?z" }
{ foo: "aaz" }
Which of the following query will show posts collection in sorted format with author key ascending?
db.posts.find().sort('author':1)
None of the mentioned
db.posts.find().sort({'author':1})
db.posts.findOne().sort({'author':0})
A query may include a ___________ that specifies the fields from the matching documents to return.
union
Projection
None of the mentioned
Selection
Which of the following operators can be used in an aggregate() pipeline?
$project
$group
$match
$unwind
Which of the following aggregate pipelines ouput the same result as the SQL query below.
select A, count(*)
from R
where A >= 0
group by A
Note: We only consider the produced VALUES; we do NOT consider colum names or field names.
[ { $match: { A: { $gte: 0 } } }, { $group: { _id: "A", { $sum: 1 } } } ]
[ { $match: { A: { $gte: 0 } } }, { $group: { _id: "$A", { $sum: 1 } } } ]
[ { $group: { _id: "$A", { $sum: 1 } } }, { $match: { A: { $gte: 0 } } } ]
[ { $match: { A: { $gte: 0 } } }, { $group: { _id: "$A", { $sum: "$A" } } } ]
Which SQL query is equivalent to:
db.mycollection.aggregate( [
{ $group: { _id: "$att2", foo: { $min: "$att3"} } } ,
{ $match: { _id: val2 } }
] )
SELECT MIN(att3) AS foo
FROM mycollection
WHERE att2 = val2
GROUP BY att2
SELECT MIN(att3) AS foo
FROM mycollection
GROUP BY att2
WHERE att2 = val2
SELECT MIN(att3) AS foo
FROM mycollection
GROUP BY att2
HAVING att2 = val2
SELECT MIN(att3) AS foo
FROM mycollection
WHERE att2 = val2
When specifying a multi-stage aggregate() pipeline...
the output of stage N is the input of stage N+1
the input of the last stage is the final result returned by the aggregate() method
the input of stage N is the output of stage N-1
the input of stage N is the output of stage N+1
Which of the following $group stage specification is valid:
{ $group: {_id: "$foo", _id: "$bar", maxFoo: { $max: "$foo" } } }
{ $group: { maxFoo: { $max: "$foo" } } }
{ $group: { _id: "$foo" } }
{ $group: { _id: null, maxFoo: { $max: "$foo" } } }
Which of the following operators can be used in an aggregate() pipeline?
In an aggregate() pipeline...
there may be no $match floor
there must be exactly one floor $match
there can be several stages $match
there must be at least one $match floor
what is the relational equivalent of:
db.macollection.aggregate( [
{ $match: { att1: val1 } },
{ $group: { _id: "$att2", truc: { $min: "$att3"} } }
] )
SELECT MIN(att3) AS truc
FROM macollection
WHERE att1 = val1
GROUP BY att2
SELECT MIN(att3) AS truc
FROM macollection
GROUP BY att2
HAVING att1 = val1
SELECT MIN(att3) AS truc
FROM macollection
GROUP BY att2
SELECT MIN(att3) AS truc
FROM macollection
WHERE att1 = val1
what is the relational equivalent of:
db.orders.aggregate( [ { $match: { status: 'A' } }, { $group: { id: "$custid", total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } } ] )
SELECT cust_id, SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total > 250
SELECT cust_id, SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total >= 250
SELECT cust_id, SUM(price) as total FROM orders GROUP BY cust_id HAVING total >= 250 , status='A'
SELECT cust_id, COUNT(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total > 250
what is the relational equivalent of:
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 } } }] )
SELECT SUM(1) AS count
FROM orders
SELECT COUNT(1) AS count
FROM orders
SELECT SUM(*) AS count
FROM orders
SELECT COUNT(*) AS count
FROM orders
The updateOne() method:
can update a document
returns as sole information a boolean indicating whether an update has taken place
returns "matchedCount = true", if an update has taken place
peut mettre à jour plusieurs documents
The UpdateMany() method:
By default upsert = true
By default ordered = true
By default upsert = false
By default ordered = false
The insertOne() method:
returns among other information the number of correctly inserted documents
allows you to insert several documents
allows the insertion of documents with duplicate Id
retourne un seul document
The operators for specifying an update via updateOne() are :
$unset
$get
$size
$set
The deleteOne() method:
returns an error if the specified filter does not match any document
allows you to delete a single document
returns a document
can be used to delete all documents in a collection
The return type of the updateOne() method:
a document that contains a field named "modifiedCount" (among other fields)
an integer
a boolean
a document that contains a field named "matchedCount" (among other fields)
The insertMany() method:
returns a document
takes two parameters, each of which is mandatory
can be used to insert only one document
returns, among other things, the _id value of the inserted documents
The return value of the deleteOne() method is:
an integer, representing the number of deleted documents
a boolean, specifying whether the delete succeeded or not
a document that contains the number of deleted documents (among other informations)
Only the Id of deleted document
Consider a collection that only contains the following document: { _id: 10, bar: "abc" }. Which of the following new insetion on that collection will succeed?
insertOne( { _id: 20, bar: "abc" } )
insertOne( { bar: "abc" } )
insertOne( { _id: 10, bar: "abc" } )
insertOne( { } )
The return type of the deleteMany() method:
a boolean
an integer
a document
an array of documents
