Elance SQL Test Answers 2015
·
What is the typical filename extension of a
SQL file?
.sequel
.dataquery
.sql
.query
UNION ALL is different from a UNION command in
that...
UNION ALL will include null values
UNION ALL will only select distinct values
UNION ALL will not eliminate duplicate rows
UNION ALL is an invalid command
True or False? This query is valid the way it
is structured. SELECT * FROM Prospects WHERE assignment_type <> 'Team'
AND criteria is not null
False
True
What is the result of "select * from
table where 1"
Return the first row from table
Return all the rows from table
Which of these is not a language element of
SQL?
None... all are language elements of SQL.
Expressions
Clauses
Statements
Queries
SELECT * FROM tablea, tableb WHERE
tablea.DepartmentID = tableb.DepartmentID; Which of these keywords will have
the same effect as the above query?
Full Outer Join
Left Outer Join
Cross Join
Inner Join
Right Outer Join
Where is the GROUP BY clause placed in the
sequence of statements?
before WHERE
before FROM
before ORDER BY
before SELECT
What is the correct syntax to concatenate the
contents of one column (col1) to the contents of another column (col2) in a
query?
concat(col1, col2)
sql_concat(col1, col2)
join(col1, col2)
col1 + col2
concatenate(col1, col2)
Which symbol can be used to indicate a
"wild card" to substitute for one or more characters when searching
for string in a database?
*
#
&
@
%
What is the name of a mechanism used to
associate tables within a SELECT statement?
connection
association
join
link
How many primary keys can a table have?
tables cannot have primary keys
between one and five
no limit
one
at least one, but not more than two
Which of the following is the correct syntax
to update a table?
UPDATE "table name" WHERE
<constraint> SET "column name" = <value>;
UPDATE "table name" SET "column
name" = <value> WHERE <constraint>;
UPDATE "table name" AND SET "column
name" = <value> WHERE <constraint>;
SET "column name" = <value> WHERE
<constraint> UPDATE "table name";
Which keyword is used to sort retrieved data
in reverse order?
BACKWARDS
UPSIDEDOWN
REVERSE
DESC
What is the definition of a foreign key?
A primary key in a relational table that has a
non-numeric value.
A primary key with a null value.
The secondary primary key in a table.
A field in a relational table that matches the
primary key column of another table.
You can add a row using SQL in a database with
which of the following?
CREATE
MAKE
ADD
INSERT
Which aggregate function returns the total of
the values in a column?
TOTAL()
ALL()
SUM()
ADD()
What is one objective of database
normalization?
using multiple tables
defining indexes
using norm-based ASCII
reducing redundancy
True or false The ORDER BY keyword is used to
sort the result-set by one or more columns.
False
True
Which command is used to sort retrieved data?
SORT
NUMERIC
ORDER BY
ALPHA
The FROM keyword is used to identify which
piece of information?
table name
row name
column name
database name
What function returns the lowest value in a
column?
LOWEST()
LOW()
SMALLEST()
MIN()
What does the acronym SQL stand for?
Segmented Query Language
Structured Query Language
Simple Query Language
Standard Query Language
Specified Query Language
Which of the following is NOT a valid
aggregate function?
Round()
Sum()
Max()
Min()
Avg()
The command to remove rows from a table
'CUSTOMER' is:
DELETE FROM CUSTOMER WHERE ...
REMOVE FROM CUSTOMER ...
DROP FROM CUSTOMER ...
UPDATE FROM CUSTOMER ...
Which keyword is used to retrieve only certain
rows of data?
ROWS
FILTER
WHERE
RECORD
Which keyword is used to assign an alias?
ALIAS
RENAME
ASSIGN
AS
When sorting by multiple columns, which
character is used to separate column names?
backslash (\)
coma (,)
dash (-)
semicolon (;)
What keyword is used to retrieve table data?
RETRIEVE
SELECT
FETCH
GET
Which of the following is NOT included as a
field in the timestamp data type?
Year
Day
Century
Hour
Month
What is DDL Stand for?
Data Definition Language
Data Determined Login
Data Deprecation Logic
Data Distribution Layout
What statement must be placed in the blank
space at the beginning of this query in order for it to be valid? ______
Movie.title, COUNT(*) AS Directors FROM Movie JOIN Movie_director ON Movie.isbn
= Movie_director.isbn GROUP BY Movie.title
ON
SELECT
FROM
WHERE
ORDER BY
Of the following sequences which one is in the
correct order?
GROUP BY | FROM | SELECT | HAVING
FROM | GROUP BY | HAVING | SELECT
SELECT | FROM | GROUP BY | HAVING
HAVING | FROM | SELECT | GROUP BY
Which character is used to end a SQL
statement?
semicolon (;)
colon (:)
exclamation (!)
period (.)
Which SQL statement is used to update data in
a database?
CHANGE
UPDATE ROW
UPDATE DATA
UPDATE
RENAME
Which of the following is NOT a basic SQL
statement?
SELECT
QUERY
DELETE
UPDATE
INSERT
Which character is used to retrieve all
columns of data?
exclamation (!)
pound sign (#)
asterisk (*)
dollar sign ($)
What function counts the number of rows in a
column?
COUNT()
TOTAL()
ADD()
NUMBER()
Which of the following statements can be used
to undo a transaction?
UNDO
RESET
ROLLBACK
REDO
What is a primary key?
A reference to a row in another table
A unique identifier within all record sets.
The index that is accessed most often
A table
The first attribute of a table.
What function returns the highest value in a
column?
HIGH()
BIGGEST()
HIGHEST()
MAX()
What does SQL stand for?
Simultaneous Quiz Language
Sequential Quest for Learning
Static Question Logic
Structured Query Language
The 'JOIN' keyword is used to:
Join two databases together permanently.
Join two databases in a query operation.
Join two tables in a query operation.
Join two tables end-to-end permanently.
What keyword is used to check for no value?
NULL
NONE
EMPTY
VOID
What is another name for a table row?
query
data
record
view
=, <>, >, <, >=, <=,
BETWEEN, LIKE, and IN are referred to as _____
variables
filters
parameters
operators
Which SQL function or feature returns a single
value, calculated from values in a column?
aggregate function
bundle feature
consolidation function
collapse query
When accessing data from a table which keyword
is used immediately before the table name? (Example: SELECT column_name _______
table_name;)
JOIN
WHERE
FROM
INSERT
INSIDE
What function is used to remove padded spaces?
TRIM()
RPAD()
LPAD()
PAD()
What keyword is used in conjunction with the
WHERE clause when creating a subquery?
SUB
QUERY
EMBED
IN
A primary key made up of more than one column
is referred to as what kind of key?
dynamic key
spare key
artificial key
monk key
composite key
Which of the following is NOT a language
element of SQL?
Statement
Query
Clause
Expression
Data mining
What is the difference between DROP and
DELETE.
DELETE removes a row in the table and DROP removes
the entire table.
DELETE removes an entire table and DROP removes all
the rows in the table.
Both delete an entire table.
Both delete a specified row in a table.
What syntax would you use to write a query to
select all teams that won either 2, 4, 6 or 8 games?
SELECT team_name FROM teams WHERE team_won IN (2,
4, 6, 8)
SELECT team_name FROM teams WHERE team_won ON (2,
4, 6, 8)
SELECT team_name FROM teams WHERE team_won LIKE (2,
4, 6, 8)
SELECT team_name FROM teams WHERE team_won MAP (2,
4, 6, 8)
What is one of the purposes of normalization?
Eliminate rows
Fix tables
Join tables
Add primary keys
Eliminate redundancy
What is the name for a query embedded inside
another query?
cartesian join
inner join
embedded query
subquery
What does RDBMS mean?
Ratio Database Management System
Rational Database Manager System
Relational Database Manager System
Rational Database Management System
Relational Database Management System
In SQL what is the meaning of NULL?
Empty string
0
no value
If tableA is CROSS JOINED to tableB, tableA
has 10 rows and tableB has 5 rows, what is the greatest possible size of the
result set?
20
25
50
5
15
What clause is used to sort data and group it?
SORT BY
SORT
GROUP BY
GROUP
What term is used to describe the
"layout" of a database or the blueprint that outlines the way data is
organized into tables?
program
view
query
schema
What function calculates a column's average
value?
MEAN()
MEDIAN()
AVERAGE()
AVG()
What keyword is used with aggregate functions
to include only unique values in the calculation?
DISTINCT
NO_DUPLICATES
UNIQUE
SINGLE
An asterisk after SELECT can be used to return
all ________ of a table.
row IDs
null values
foreign keys
primary keys
columns
What happens if you omit the WHERE clause in a
SQL DELETE query?
An error message will appear.
All records will be deleted.
The entire database will be deleted.
All tables will be deleted.
What keyword is used to check for a range of
values?
WITHIN`
RANGE
BETWEEN
CONTAINS
The HAVING clause can be used for what
purpose?
To be used for filtering based on the outcome of
non-aggregate functions.
To validate a constraint.
To be used for filtering based on the outcome of
aggregate functions.
To verify that at database connection is available.
Which of the following names is NOT a SQL
based RDBMS?
SQL Server
MongoDB
Oracle
MySQL
SQLite
The HAVING clause is used in conjunction with
(and immediately after) what other clause?
FROM
WHERE
GROUP BY
SELECT
How can you change "Hansen" into
"Nilsen" in the "LastName" column in the Persons table?
UPDATE Persons SET LastName='Nilsen' WHERE
LastName='Hansen'
UPDATE Persons SET LastName=Nilsen WHERE
LastName=Hansen
MODIFY Persons SET LastName='Nilsen' WHERE
LastName='Hansen'
UPDATE Persons SET LastName=Nilsen WHERE LastName='Hansen'
What keyword is used to create a table alias?
NICKNAME
AS
NEW_TABLE
ALIAS
The DDL term "DROP" does what?
Moves data from one table down to the next.
Deletes all data from a table in a very fast way,
deleting the data inside the table and not the table itself.
Eliminates errors from the current table.
Deletes a database, table, index or column.
Modifies the structure of an existing object in
various ways.
Which keyword is used more than once in a SQL
statement that contains a subquery?
SELECT
ORDER BY
DISTINCT
GROUP BY
Which of the following are type(s) of DML
Triggers?
Instead of Trigger: Instead of Triggers are fired
in place of the triggering action such as an insert, update, or delete.
After Trigger: After triggers execute following the
triggering action, such as an insert, update, or delete.
All of these
What character is used to connect a table name
with a column name to create a fully qualified column name?
dash (-)
plus (+)
underscore (_)
dot (.)
How do you select a column named
"FirstName" from a table named "Persons"?
SELECT * FROM Persons where Persons = FirstName;
SELECT FirstName FROM Persons;
SELECT First_Name FROM Persons;
SELECT * FROM Persons where FirstName = Persons;
What is the term for a column (or set of
columns) whose values uniquely identify every row in a table?
foreign key
unique identifier
secondary key
primary key
What is the term for a set of data elements
(values) organized using rows and columns?
table
index
query
program
Which of the following is NOT true about a
primary key constraints?
A primary key column cannot contain NULL values.
The value of a primary key constraint uniquely
identifies each record in a table.
Primary keys must contain unique values.
For every primary key there must be a foreign key.
Which of these is NOT a valid data type for a
character string?
CHAR
TEXTCHAR
VARCHAR
NVARCHAR
NCHAR
Which statement can be used to repeat the
execution of a code block as long as a specified condition returns TRUE?
GOTO statement
SIGNAL statement
WHILE statement
SUBQUERY statement
SELECT statement
What data is this statement trying to query
from the Customers Table? SELECT * FROM Customers WHERE City LIKE '[!bsp]%'
Customers in cities starting with "b" or
"s" or "p."
Customers in cities starting with "bsp."
Customers in cities NOT starting with "b"
or "s" or "p."
Customers in cities containing any combination of
the letters "b" and "s" and "p."
Customers in cities ending with "b" or
"s" or "p."
What of the following is an operator for
nonequality?
less than greater than (<>)
greater than less than (><)
equal equal (==)
equal exclamation (=!)
Which will select the `name` of 'John' from
the 'Person' table where `num_friends` is greater than 1?
SELECT * FROM Person WHERE num_friends MORE THAN 1
AND name = 'John'
SELECT "John" as 'name' FROM `Person`
WHERE `num_friends` > 1
SELECT CONCAT(firstname, lastname) FROM Person
WHERE num_friends > 1 AND name = "John"
SELECT name FROM Person WHERE num_friends > 1
AND name = 'John'
Suppose table A has 5 rows and table B has 6
rows. You perform a cross join on these two tables. How many rows will it have?
6
11
25
30
5
What is the function that combines two strings
and returns the combined string?
CONCAT()
COMBINE()
CONCATENATE()
GROUP()
JOIN()
What visual technique is commonly used to
format subqueries?
color coding
indenting
numbering
bolding
Which clause indicates the table(s) from which
data is to be retrieved?
HAVING
FROM
SELECT
WHERE
GROUP BY
Choose correct clause: SELECT CountryCode,
COUNT(*) FROM City GROUP BY CountryCode _____ COUNT(*) > 20;
SELECT
ORDER BY
HAVING
WHERE
LIMIT
Which statement removes all rows from the
"orders" table without removing the table structure?
TRUNCATE orders
SANITIZE orders
REMOVE ROWS FROM orders
DROP TABLE orders
Which of the following DROP statements is
incorrect?
DROP INDEX
DROP DATABASE
DROP ROW
DROP TABLE
What KEYWORD is used to filter groups?
MINIMIZE
GROUPS
SORT
HAVING
FILTER
What keyword is used in conjunction with the
INNER JOIN keywords to return rows when there is at least one match in both
tables?
CONNECT
WHEN
IN
ON
If a foreign key constraint is violated, the
default action taken by the DBMS is what?
The DBMS throw an error but implements the violated
changes
The value is set to NULL
It is not possible to violate a foreign key
constraint. The modification is rejected
The value is cascaded
Indexes can be created on existing tables so
that information can be retrieved more quickly. Specifically, what are indexes
created on?
rows
columns
duplicate values
null values
A trigger is a database object that is
attached to a table. It is most similar to what other database process?
view
cursor
stored procedure
result set
What is an alternate way of writing the
following statement: WHERE "column_name" IN ('value1')
WHERE "column_name" = 'value1'
WHERE "column_name" > 'value1'
WHERE "column_name" INCLUDES 'value1'
WHERE "column_name" < 'value1'
WHERE "column_name" CONTAINS 'value1'
What is PL/SQL?
An open source version of SQL
It is a proprietary version of SQL used by Oracle
It is a proprietary version of SQL developed by
Microsoft
It is a proprietary version of SQL developed for
Visual Studio
It is a proprietary version of SQL developed by
MySQL
Which does not describe a database element?
tuple
stored procedure
table schema
organic list
index
What is the name of a result that returns all
the rows in all the tables listed in the query?
null value
table constraint
primary key
Cartesian product
mega join
This example illustrates use of the FULL JOIN
action. Which clauses must fill the three blanks for the query to be valid?
_______ e1."Event_Name", v2."Venue" FROM "events"
e1 _______ "venues" v2 ON (e1."VenueNo" =
v2."VenueNo") _______ e1."Event_Name" ASC,
v2."Venue" ASC
WHERE, FULL OUTER JOIN, HAVING
HAVING, WHERE, ORDER BY
SELECT, OUTER JOIN, GROUP BY
SELECT, FULL OUTER JOIN, ORDER BY
GROUP BY, FULL OUTER JOIN, ORDER BY
Which of the following is NOT an explicit data
type reference?
smallint
integer
character
float
null
Which wildcard character means "match any
number of occurrences of any character"?
percent (%)
at (@)
ampersand (&)
asterisk (*)
What keyword is used with aggregate functions
to include every value in the calculation?
ALL
TOTAL
EVERY
DUPLICATES_ALLOWED
What is the name of the category of functions
used to summarize data?
grouping
primary
summary
aggregate
What is the first query to run in a statement
that contains subqueries?
first
last
outermost
innermost
What records would the result set of this
query include? SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.key =
tableB.key
All records from tableA; 0 or more records from
tableB
0 or more records from tableA; all records from
tableB
Only records where key is in both tableA and tableB
All records from tableA; all records from tableB
What is the proper syntax of the keyword LIMIT
to display 5 results after starting at record 4?
LIMIT 4, 5
LIMIT 5, 4
LIMIT 0, 5
LIMIT 0, 4
Which is the correct order for a proper SQL
query?
SELECT, FROM, WHERE, HAVING, GROUP BY, ORDER BY
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING
SELECT, FROM, GROUP BY, HAVING, ORDER BY, WHERE
SELECT, FROM, WHERE, HAVING, ORDER BY, GROUP BY
What is the correct procedure to create and
use a cursor?
Declare cursor > Open cursor > Fetch row from
the cursor > Process fetched row > Deallocate cursor > Close cursor
Open cursor > Fetch row from the cursor >
Process fetched row > Close cursor > Deallocate cursor
Declare cursor > Open cursor > Fetch row from
the cursor > Process fetched row > Close cursor > Deallocate cursor
Open cursor > Fetch row from the cursor >
Process fetched row > Deallocate cursor > Close cursor
Which type of join will yield a Cartesian
product?
Natural Join
Right Outer Join
Left Outer Join
Full Join
Cross Join
The UNION ALL operator performs which of the
following actions?
Returns the output from the query before and the
query after the operator excluding duplicates.
Returns the output from the query before and the
query after the operator including duplicates.
Concatenates all of the columns in the table
specified with an optional user defined delimiter between.
Joins all of the tables that have a primary or foreign
key relationship to the specified table.
Which of these is not a valid constraint?
CHECK
UNIQUE
PRIMARY KEY
NOT NULL
EXISTS
Where can we find subquery within another
statement?
In the HAVING clause.
In the FROM clause.
In the SELECT clause.
In the JOIN clause.
In all these clauses.
Which of the following is NOT a property of a
relational table?
Every table must include a foreign key.
Each column must have a unique name.
Column values are of the same kind.
The sequence of columns is insignificant.
Which statement is not correct for TRUNCATE
command?
TRUNCATE cannot active a trigger
TRUNCATE can be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table
If you INNER JOIN tableA (which has 10 rows)
with tableB (which has 5 rows), what is the smallest possible amount of rows
that can be returned?
50
5
15
20
0
How many clustered indexes can a table have?
0
128
2
1
no limit
How can you insert several records in TABLE1
that already exist in TABLE2?
insert into TABLE1 (FIELD1) select FIELD2 , FIELD3,
FIELD4 from TABLE2
insert into TABLE1 (FIELD1) select FIELD2 from
TABLE2
insert into TABLE1 (FIELD1) values (select FIELD2
from TABLE2)
insert into TABLE1 (FIELD1) values (TABLE2.FIELD2)
What is the correct syntax for using a CASE
statement?
CASE {value/column} WHEN {Boolean Condition} THEN
{Value} ELSE CREATE PROCEDURE
CASE WHEN {Boolean Condition} END
CASE {value/column} WHEN {Boolean Condition} THEN
{Value} ELSE {Value} END CASE
None of these
CASE {value/column} WHEN {Boolean Condition} THEN
{Value} ELSE {Value} END
Which of the following statements about
indexes is NOT correct?
Adding additional indexes can result in reduced
write speeds.
Adding additional indexes can greatly improve
performance in an OLAP environment.
Adding additional indexes cannot decrease the
performance of your database.
Adding additional indexes increases the storage
space required for your database.
select ISNUMERIC(' ') what is the invisible
character in this statement?
0
TAB
SPACE
SHIFT
Which of the following KEYWORDS will return
the first NON-NULL value from a list of columns?
ISNULL
PARSENULL
NOTNULL
GATHER
COALESCE
Which of the following is a valid isolation
level?
Read Commited
Concurrent
Repeatable Write
Serial
Write Committed
What is the difference between a unique key
and primary key?
A primary key can be used as part of a foreign key
relationship but a unique key cannot
They are the same
A unique key will allow NULL values
A unique key can be used as part of a foreign key
relationship but a primary key cannot
A primary key by default has an index, a unique key
does not
What is a collation?
A concatenation of two columns containing
characters.
A character encoding for a character data type.
An alphabet for a character data type.
A set of rules that sort and compare characters.
Which of the following types of triggers can
be used with a view?
After Update
Before Update
Instead of Update
Before Insert
After Delete
What code would find the position of the character
'D' in the string 'ABCDE' - starting at position 1?
PATINDEX('D','ABCDE',1)
VARCHAR(1,'D','ABCDE')
CHARINDEX('D','ABCDE',1)
CHARINDEX('ABCDE','D',1)
VARCHAR('ABCDE','D',1)
If tableA is LEFT JOINED to tableB, tableA has
10 rows and tableB has 5 rows, what is the greatest possible size of the result
set?
50
10
15
5
20
What is the equivalent sql statement of
following statement? Select sum(column1) a, count(column2) b from TableA where
1=2
Select sum(column1) a, count(column2) b from TableA
where column1=column2
Select sum(*) a, count(*) b from TableA where 1=2
Select sum(1) a, count(2) b from TableA where
column1=column2
Select 0 a, 0 b from TableA
Select sum(column1) a, count(*) b from TableA
A recursive CTE would contain which of the following?
Union All
Except
Intersect
Union
What is the first in the Execution of SQL
SELECT
WHERE
TOP
ON
FROM
Which statement about non-clustered index is
true?
The leaf nodes of a non-clustered index contain the
data pages.
A non-clustered index is a special type of index
that reorders the way records in the table are physically stored.
In non-clustered index , the logical order of the
index always matches the physical stored order of the rows on the disk.
The leaf nodes of a non-clustered index contain the
index rows.
When using logical (truth value) operations,
how many logical results are there and what are they?
2: 1 and 0
3: True, False and Null
3: True, False and Nothing
3: True, False and Unknown
2: True and False
Which SQL statement will return an error?
SELECT ALL FROM table WHERE state IS Alabama
SELECT ALL FROM people WHEN date > '2011-01-01'
None of these statements will return an error
USE PEOPLE; UPDATE people SET name = 'john' WHERE
name != 'john'
A function differs from a procedure in all the
following ways EXCEPT...
Only a function can accept parameters.
A function cannot utilize error handling
(TRY...CATCH).
A function must return a value or table.
Only a function can be called in a SELECT clause.
A function cannot perform permanent environmental
changes to a server.
Other things being equal, which queries are
the fastest?
Queries including correlated subqueries.
Not relevant.
Queries including uncorrelated subqueries.
Subscribe to:
Posts (Atom)
