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.

No comments:

Post a Comment