In this post you can find Complete and recently updated Correct Question and answers of SQL. All Answers updated regularly with new questions. Upwork SQL test answers of 2016.

Question:* What is the typical filename extension of a SQL file?

Answer: • .sql

Question:* Which of the following is NOT an explicit data type reference?

Answer: • null

Question:* Which of these is not a language element of SQL?

Answer: • None... all are language elements of SQL.

Question:* What keyword is used in conjunction with the INNER JOIN keywords to return rows when there is at least one match in both tables?

Answer: • ON

Question:* What is the term for a set of data elements (values) organized using rows and columns?

Answer: • table

Question:* Which clause indicates the table(s) from which data is to be retrieved?

Answer: • FROM

Question:* Which statement can be used to repeat the execution of a code block as long as a specified condition returns TRUE?

Answer: • WHILE statement

Question:* 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?

Answer: • 30

Question:* True or False? This query is valid the way it is structured. SELECT * FROM Prospects WHERE assignment_type <> 'Team' AND criteria is not null

Answer: • True

Question:* Which symbol can be used to indicate a "wild card" to substitute for one or more characters when searching for string in a database?

Answer: • %

Question:* How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

Answer: • UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

Question:* What is the function that combines two strings and returns the combined string?

Answer: • CONCAT()

Question:* What data is this statement trying to query from the Customers Table? SELECT * FROM Customers WHERE City LIKE '[!bsp]%'

Answer: • Customers in cities NOT starting with "b" or "s" or "p."

Question:* Choose correct clause: SELECT CountryCode, COUNT() FROM City GROUP BY CountryCode _____ COUNT() > 20;

Answer: • HAVING

Question:* Which of these is NOT a valid data type for a character string?

Answer: • TEXTCHAR

Question:* A trigger is a database object that is attached to a table. It is most similar to what other database process?

Answer: • stored procedure

Question:* What is an alternate way of writing the following statement: WHERE "column_name" IN ('value1')

Answer: • WHERE "column_name" = 'value1'

Question:* Which statement removes all rows from the "orders" table without removing the table structure?

Answer: • TRUNCATE orders

Question:* Which of the following DROP statements is incorrect?

Answer: • DROP ROW

Question:* 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

Answer: • SELECT, FULL OUTER JOIN, ORDER BY

Question:* What KEYWORD is used to filter groups?

Answer: • HAVING

Question:* Which does not describe a database element?

Answer: • organic list

Question:* How do you select a column named "FirstName" from a table named "Persons"?

Answer: • SELECT FirstName FROM Persons;

Question:* SELECT * FROM tablea, tableb WHERE tablea.DepartmentID = tableb.DepartmentID; Which of these keywords will have the same effect as the above query?

Answer: • Inner Join

Question:* Indexes can be created on existing tables so that information can be retrieved more quickly. Specifically, what are indexes created on?

Answer: • columns

Question:* Which of the following names is NOT a SQL based RDBMS?

Answer: • MongoDB

Question:* What is the operator for nonequality?

Answer: • less than greater than (<>)

Question:* What character is used to connect a table name with a column name to create a fully qualified column name?

Answer: • dot (.)

Question:* Which keyword is used more than once in a SQL statement that contains a subquery?

Answer: • SELECT

Question:* What is the correct syntax to concatenate the contents of one column (col1) to the contents of another column (col2) in a query?

Answer: • concat(col1, col2)

Question:* Which of the following is NOT true about a primary key constraints?

Answer: • For every primary key there must be a foreign key.

Question:* Which will select the `name` of 'John' from the 'Person' table where `num_friends` is greater than 1?

Answer: • SELECT name FROM Person WHERE num_friends > 1 AND name = 'John'

Question:* The HAVING clause can be used for what purpose?

Answer: • To be used for filtering based on the outcome of aggregate functions.

Question:* Where is the GROUP BY clause placed in the sequence of statements?

Answer: • before ORDER BY

Question:* What keyword is used to check for a range of values?

Answer: • BETWEEN

Question:* What happens if you omit the WHERE clause in a SQL DELETE query?

Answer: • All records will be deleted.

Question:* An asterisk after SELECT can be used to return all ________ of a table.

Answer: • columns

Question:* The HAVING clause is used in conjunction with (and immediately after) what other clause?

Answer: • GROUP BY

Question:* How many primary keys can a table have?

Answer: • one

Question:* The DDL term "DROP" does what?

Answer: • Deletes a database, table, index or column.

Question:* What keyword is used to create a table alias?

Answer: • AS

Question:* In SQL what is the meaning of NULL?

Answer: • no value

Question:* What clause is used to sort data and group it?

Answer: • GROUP BY

Question:* Which SQL function or feature returns a single value, calculated from values in a column?

Answer: • aggregate function

Question:* 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?

Answer: • 50

Question:* Which of the following are type(s) of DML Triggers?

Answer: • All of these

Question:* What is the name for a query embedded inside another query?

Answer: • subquery

Question:* What syntax would you use to write a query to select all teams that won either 2, 4, 6 or 8 games?

Answer: • SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

Question:* Which of the following is NOT a language element of SQL?

Answer: • Data mining

Question:* What keyword is used with aggregate functions to include only unique values in the calculation?

Answer: • DISTINCT

Question:* What is one of the purposes of normalization?

Answer: • Eliminate redundancy

Question:* What term is used to describe the "layout" of a database or the blueprint that outlines the way data is organized into tables?

Answer: • schema

Question:* Which of the following is the correct syntax to update a table?

Answer: • UPDATE "table name" SET "column name" = <value> WHERE <constraint>;

Question:* What is the term for a column (or set of columns) whose values uniquely identify every row in a table?

Answer: • primary key

Question:* What is the difference between DROP and DELETE.

Answer: • DELETE removes a row in the table and DROP removes the entire table.

Question:* A primary key made up of more than one column is referred to as what kind of key?

Answer: • composite key

Question:* What function calculates a column's average value?

Answer: • AVG()

Question:* What does RDBMS mean?

Answer: • Relational Database Management System

Question:* What keyword is used in conjunction with the WHERE clause when creating a subquery?

Answer: • IN

Question:* What is the name of a mechanism used to associate tables within a SELECT statement?

Answer: • join

Question:* What function is used to remove padded spaces?

Answer: • TRIM()

Question:* When accessing data from a table which keyword is used immediately before the table name? (Example: SELECT column_name _______ table_name;)

Answer: • FROM

Question:* Which keyword is used to sort retrieved data in reverse order?

Answer: • DESC

Question:* Which aggregate function returns the total of the values in a column?

Answer: • SUM()

Question:* The FROM keyword is used to identify which piece of information?

Answer: • table name

Question:* True or false The ORDER BY keyword is used to sort the result-set by one or more columns.

Answer: • True

Question:* What is one objective of database normalization?

Answer: • reducing redundancy

Question:* Which of the following is NOT a valid aggregate function?

Answer: • Round()

Question:* Which keyword is used to assign an alias?

Answer: • AS

Question:* What is another name for a table row?

Answer: • record

Question:* Which keyword is used to retrieve only certain rows of data?

Answer: • WHERE

Question:* Which of the following statements can be used to undo a transaction?

Answer: • ROLLBACK

Question:* What is the definition of a foreign key?

Answer: • A field in a relational table that matches the primary key column of another table.

Question:* What does the acronym SQL stand for?

Answer: • Structured Query Language

Question:* What function returns the lowest value in a column?

Answer: • MIN()

Question:* 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

Answer: • SELECT

Question:* Which command is used to sort retrieved data?

Answer: • ORDER BY

Question:* The 'JOIN' keyword is used to:

Answer: • Join two tables in a query operation.

Question:* What is a primary key?

Answer: • A unique identifier within all record sets.

Question:* Which of the following is NOT a basic SQL statement?

Answer: • QUERY

Question:* What keyword is used to check for no value?

Answer: • NULL

Question:* What keyword is used to retrieve table data?

Answer: • SELECT

Question:* What is DDL Stand for?

Answer: • Data Definition Language

Question:* Which of the following is NOT included as a field in the timestamp data type?

Answer: • Century

Question:* Which character is used to retrieve all columns of data?

Answer: • asterisk (*)

Question:* When sorting by multiple columns, which character is used to separate column names?

Answer: • coma (,)

Question:* Of the following sequences which one is in the correct order?

Answer: • SELECT | FROM | GROUP BY | HAVING

Question:* Which SQL statement is used to update data in a database?

Answer: • UPDATE

Question:* Which character is used to end a SQL statement?

Answer: • semicolon (;)

Question:* What function counts the number of rows in a column?

Answer: • COUNT()

Question:* What function returns the highest value in a column?

Answer: • MAX()

Question:* What does SQL stand for?

Answer: • Structured Query Language

Question:* What visual technique is commonly used to format subqueries?

Answer: • indenting

Question:* If a foreign key constraint is violated, the default action taken by the DBMS is what?

Answer: • It is not possible to violate a foreign key constraint. The modification is rejected

Question:* What keyword is used with aggregate functions to include every value in the calculation?

Answer: • ALL

Question:* Which wildcard character means "match any number of occurrences of any character"?

Answer: • percent (%)

Question:* What is the result of "select * from table where 1"

Answer: • Return all the rows from table

Question:* UNION ALL is different from a UNION command in that...

Answer: • UNION ALL will not eliminate duplicate rows

Question:* A Cross Join is equivalent to:

Answer: • A Cartesian Product

Question:* What is the name of a result that returns all the rows in all the tables listed in the query?

Answer: • Cartesian product

Question:* What is the first query to run in a statement that contains subqueries?

Answer: • innermost

Question:* What records would the result set of this query include? SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.key = tableB.key

Answer: • All records from tableA; 0 or more records from tableB

Question:* What is the proper syntax of the keyword LIMIT to display 5 results after starting at record 4?

Answer: • LIMIT 4, 5

Question:* Which is the correct order for a proper SQL query?

Answer: • SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Question:* What is the name of the category of functions used to summarize data?

Answer: • aggregate

Question:* What is the correct procedure to create and use a cursor?

Answer: • Declare cursor > Open cursor > Fetch row from the cursor > Process fetched row > Close cursor > Deallocate cursor

Question:* The UNION ALL operator performs which of the following actions?

Answer: • Returns the output from the query before and the query after the operator including duplicates.

Question:* What is the correct syntax for using a CASE statement?

Answer: • CASE {value/column} WHEN {Boolean Condition} THEN {Value} ELSE {Value} END

Question:* Where can we find subquery within another statement?

Answer: • In all these clauses.

Question:* 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?

Answer: • 0

Question:* Which of these is not a valid constraint?

Answer: • EXISTS

Question:* Which of the following is NOT a property of a relational table?

Answer: • Every table must include a foreign key.

Question:* Which of the following statements about indexes is NOT correct?

Answer: • Adding additional indexes cannot decrease the performance of your database.

Question:* What is the difference between a unique key and primary key?

Answer: • A unique key will allow NULL values

Question:* How can you insert several records in TABLE1 that already exist in TABLE2?

Answer: • insert into TABLE1 (FIELD1) select FIELD2 from TABLE2

Question:* What is a collation?

Answer: • A set of rules that sort and compare characters.

Question:* How many clustered indexes can a table have?

Answer: • 1

Question:* Which of the following types of triggers can be used with a view?

Answer: • Instead of Update

Question:* Which of the following is a valid isolation level?

Answer: • Read Commited

Question:* What code would find the position of the character 'D' in the string 'ABCDE' - starting at position 1?

Answer: • CHARINDEX('D','ABCDE',1)

Question:* Which of the following KEYWORDS will return the first NON-NULL value from a list of columns?

Answer: • COALESCE

Question:* 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?

Answer: • 50

Question:* A recursive CTE would contain which of the following?

Answer: • Union All

Question:* What is the equivalent sql statement of following statement? Select sum(column1) a, count(column2) b from TableA where 1=2

Answer: • Select 0 a, 0 b from TableA

Question:* A function differs from a procedure in all the following ways EXCEPT...

Tuesday, December 15, 2015

SQL test answers of 2016.

Question:* What is the typical filename extension of a SQL file?

Answer: • .sql

Question:* Which of the following is NOT an explicit data type reference?

Answer: • null

Question:* Which of these is not a language element of SQL?

Answer: • None... all are language elements of SQL.

Question:* What keyword is used in conjunction with the INNER JOIN keywords to return rows when there is at least one match in both tables?

Answer: • ON

Question:* What is the term for a set of data elements (values) organized using rows and columns?

Answer: • table

Question:* Which clause indicates the table(s) from which data is to be retrieved?

Answer: • FROM

Question:* Which statement can be used to repeat the execution of a code block as long as a specified condition returns TRUE?

Answer: • WHILE statement

Question:* 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?

Answer: • 30

Question:* True or False? This query is valid the way it is structured. SELECT * FROM Prospects WHERE assignment_type <> 'Team' AND criteria is not null

Answer: • True

Question:* Which symbol can be used to indicate a "wild card" to substitute for one or more characters when searching for string in a database?

Answer: • %

Question:* How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

Answer: • UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

Question:* What is the function that combines two strings and returns the combined string?

Answer: • CONCAT()

Question:* What data is this statement trying to query from the Customers Table? SELECT * FROM Customers WHERE City LIKE '[!bsp]%'

Answer: • Customers in cities NOT starting with "b" or "s" or "p."

Question:* Choose correct clause: SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode _____ COUNT(*) > 20;

Answer: • HAVING

Question:* Which of these is NOT a valid data type for a character string?

Answer: • TEXTCHAR

Question:* A trigger is a database object that is attached to a table. It is most similar to what other database process?

Answer: • stored procedure

Question:* What is an alternate way of writing the following statement: WHERE "column_name" IN ('value1')

Answer: • WHERE "column_name" = 'value1'

Question:* Which statement removes all rows from the "orders" table without removing the table structure?

Answer: • TRUNCATE orders

Question:* Which of the following DROP statements is incorrect?

Answer: • DROP ROW

Question:* 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

Answer: • SELECT, FULL OUTER JOIN, ORDER BY

Question:* What KEYWORD is used to filter groups?

Answer: • HAVING

Question:* Which does not describe a database element?

Answer: • organic list

Question:* How do you select a column named "FirstName" from a table named "Persons"?

Answer: • SELECT FirstName FROM Persons;

Question:* SELECT * FROM tablea, tableb WHERE tablea.DepartmentID = tableb.DepartmentID; Which of these keywords will have the same effect as the above query?

Answer: • Inner Join

Question:* Indexes can be created on existing tables so that information can be retrieved more quickly. Specifically, what are indexes created on?

Answer: • columns

Question:* Which of the following names is NOT a SQL based RDBMS?

Answer: • MongoDB

Question:* What is the operator for nonequality?

Answer: • less than greater than (<>)

Question:* What character is used to connect a table name with a column name to create a fully qualified column name?

Answer: • dot (.)

Question:* Which keyword is used more than once in a SQL statement that contains a subquery?

Answer: • SELECT

Question:* What is the correct syntax to concatenate the contents of one column (col1) to the contents of another column (col2) in a query?

Answer: • concat(col1, col2)

Question:* Which of the following is NOT true about a primary key constraints?

Answer: • For every primary key there must be a foreign key.

Question:* Which will select the `name` of 'John' from the 'Person' table where `num_friends` is greater than 1?

Answer: • SELECT name FROM Person WHERE num_friends > 1 AND name = 'John'

Question:* The HAVING clause can be used for what purpose?

Answer: • To be used for filtering based on the outcome of aggregate functions.

Question:* Where is the GROUP BY clause placed in the sequence of statements?

Answer: • before ORDER BY

Question:* What keyword is used to check for a range of values?

Answer: • BETWEEN

Question:* What happens if you omit the WHERE clause in a SQL DELETE query?

Answer: • All records will be deleted.

Question:* An asterisk after SELECT can be used to return all ________ of a table.

Answer: • columns

Question:* The HAVING clause is used in conjunction with (and immediately after) what other clause?

Answer: • GROUP BY

Question:* How many primary keys can a table have?

Answer: • one

Question:* Choose correct clause: SELECT CountryCode, COUNT() FROM City GROUP BY CountryCode _____ COUNT() > 20;

Question:* 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