Which SQL statement will return an error? 

SELECT * FROM departments; is
a:       

The basic storage structure in
a Relational Database is a _________:       

The DESCRIBE command returns
all rows from a table. True or False?   

You cannot use computers unless
you completely understand exactly how they work. True or False?   

Which example would limit the number of rows returned?         

                SELECT
title FROM d_songs WHERE type_code = 88; (*)

                SELECT
title FROM d_songs WHEN type_code = = 88;

                SELECT
title FROM d_songs WHERE type_code = = 88;

                SELECT
title FROM d_songs WHEN type_code = 88;

You need to display all the
values in the EMAIL column that contains the underscore (_) character as part
of that email address. The WHERE clause in your SELECT statement contains the
LIKE operator. What must you include in the LIKE operator?   

                The
ESCAPE option (\) and one or more percent signs (%) (*)

The EMPLOYEES table includes
these columns:

EMPLOYEE_ID NUMBER(4) NOT NULL

LAST_NAME VARCHAR2(15) NOT NULL

FIRST_NAME VARCHAR2(10) NOT NULL

You want to produce a report that provides the last names,
first names, and hire dates of those employees who were hired between March 1,
2000, and August 30, 2000. Which statements can you issue to accomplish this
task?

                SELECT
last_name, first_name, hire_date

WHERE hire_date BETWEEN ’30-Aug-2000′ AND ’01-Mar-2000′;

                SELECT
last_name, first_name, hire_date

WHERE hire_date BETWEEN ’01-Mar-2000′ AND ’30-Aug-2000′;

(*)

                SELECT
last_name, first_name, hire_date

AND hire_date >= ’01-Mar-2000′ and hire_date <=
’30-Aug-2000′;

                SELECT
last_name, first_name, hire_date

GROUP BY hire_date >= ’01-Mar-2000′ and hire_date <=
’30- Aug-2000′;

Which of the following WHERE
clauses would not select the number 10? 

                WHERE
hours BETWEEN 10 AND 20

Which of the following elements
cannot be included in a WHERE clause?              

When
using the LIKE condition, which symbol represents any sequence of characters of
any length–zero, one, or more characters? 

Which of the following are TRUE
regarding the logical AND operator?

                TRUE
AND TRUE return FALSE

                TRUE
AND FALSE return FALSE (*)

                FALSE
AND TRUE return NULL

                TRUE
AND FALSE return TRUE

What clause must you place in a
SQL statement to have your results sorted from highest to lowest salary?                                                           

                None,
the database always sorts from highest to lowest on the salary column.

Evaluate this SELECT statement:

SELECT first_name, last_name, email

                The
rows will be sorted alphabetically by the LAST_NAME values. (*)

                The
rows will be sorted alphabetically by the FIRST_NAME and then the LAST_NAME
values

                The
rows will be sorted in reverse alphabetical order by the LAST_NAME values.

                The
rows will not be sorted.

The conversion function TO_CHAR
is a single row function. True or False?    

Evaluate this SELECT statement:

Which value is returned by the query?

                The
current date plus 30 hours.

                The
current date plus 30 months.

                No
value is returned because the SELECT statement generates an error.

                The
current date plus 30 days. (*)

Evaluate this function: MOD (25,
2) Which value is returned?  

You query the database with this
SQL statement:

SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4)))
“Default Password”

Which function will be evaluated first?

                All
three will be evaluated simultaneously.

Which query would return a user
password combining the ID of an employee and the first 4 digits of the last
name? 

                SELECT
CONCAT (employee_id, INSTR(last_name,1,4))

                SELECT
CONCAT (employee_id, SUBSTR(last_name,4,1))

                SELECT
CONCAT (employee_id, INSTR(last_name,4,1))

                SELECT
CONCAT (employee_id, SUBSTR(last_name,1,4))

Which statement will return a
listing of last names, salaries, and a rating of ‘Low’, ‘Medium’, ‘Good’ or
‘Excellent’ depending on the salary value?  

(CASE WHEN sal<5000 THEN ‘Low’

     WHEN sal<10000
THEN ‘Medium’

     WHEN sal<20000
THEN ‘Good’

(CASE WHEN salary<5000 THEN ‘Low’

     WHEN salary<10000
THEN ‘Medium’

     WHEN
salary<20000 THEN ‘Good’

(*)

(CASE WHEN salary<5000 THEN ‘Low’

     WHEN sal
<10000 THEN ‘Medium’

     WHEN sal
<20000 THEN ‘Good’

(RATING WHEN salary<5000 THEN ‘Low’

     WHEN
salary<10000 THEN ‘Medium’

     WHEN
salary<20000 THEN ‘Good’

When executed, which statement displays a zero if the
TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?        

                SELECT
TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance
+ housing_balance “Balance Due”

                SELECT
tuition_balance + housing_balance

                SELECT
NVL (tuition_balance + housing_balance, 0) “Balance Due”

                SELECT
NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance +
housing_balance “Balance Due”

Which statement about group
functions is true?             

                NVL and
COALESCE, but not NVL2, can be used with group functions to replace null
values.

                COALESCE,
but not NVL and NVL2, can be used with group functions to replace null values.

                NVL and
NVL2, but not COALESCE, can be used with group functions to replace null
values.

                NVL,
NVL2, and COALESCE can be used with group functions to replace null values. (*)

The following script will run
successfully. True or False?

SELECT TO_CHAR(TO_DATE(’25-Dec-2004′,’dd-Mon-yyyy’))

FROM dual    

Which statement will return the
salary (for example, the salary of 6000) from the Employees table in the
following format?   $6000.00            

                SELECT
TO_CHAR(salary, ‘$99999.00’) SALARY

                SELECT
TO_CHAR(sal, ‘$99999.00’) SALARY

                SELECT
TO_CHAR(salary, ‘$99999’) SALARY

                SELECT
TO_CHAR(salary, ‘99999.00’) SALARY

You have been asked to create a
report that lists all customers who have placed orders of at least $2,500. The
report’s date should be displayed using this format:

Day, Date Month, Year (For example, Tuesday, 13 April, 2004
).

Which statement should you issue?      

                SELECT
companyname, TO_CHAR (sysdate, ‘fmDay, dd Month, yyyy’), total

FROM customers NATURAL JOIN orders

                SELECT
companyname, TO_CHAR (sysdate, ‘fmdd, dy month, yyyy’), total

FROM customers NATURAL JOIN orders

                SELECT
companyname, TO_DATE (sysdate, ‘dd, dy month, yyyy’), total

FROM customers NATURAL JOIN orders

                SELECT
companyname, TO_DATE (date, ‘day, dd month, yyyy’), total

FROM customers NATURAL JOIN orders

Which of the following database design concepts is
implemented with a self join?           

                Recursive
Relationship (*)

Which SELECT statement implements
a self join?          

                SELECT
p.part_id, t.product_id

WHERE p.part_id = t.product_id;

                SELECT
p.part_id, t.product_id

WHERE p.part_id = t.product_id;

                SELECT
p.part_id, t.product_id

WHERE p.part_id = t.product_id (+);

                SELECT
p.part_id, t.product_id

WHERE p.part_id =! t.product_id;

You need to join all the rows in
the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of
join should you create?    

Which of the following conditions
will cause an error on a NATURAL JOIN?        

                If it
selects rows from the two tables that have equal values in all matched columns.

                If the
columns having the same names have different data types. (*)

                When
the NATURAL JOIN clause is based on all columns in the two tables that have the
same name.

                When
you attempt to use two tables that have a common field.

You can do nonequi-joins with
ANSI-Syntax. True or False? 

Which query will retrieve all the rows in the EMPLOYEES
table, even if there is no match in the DEPARTMENTS table?                

                SELECT
e.last_name, e.department_id, d.department_name

RIGHT OUTER JOIN departments d ON (e.department_id =
d.department_id);

                SELECT
e.last_name, e.department_id, d.department_name

JOIN departments d USING (e.department_id =
d.department_id);

                SELECT
e.last_name, e.department_id, d.department_name

LEFT OUTER JOIN departments d ON (e.department_id =
d.department_id);

                SELECT
e.last_name, e.department_id, d.department_name

NATURAL JOIN departments d;

You need to display all the rows
(both matching and non-matching) from both the EMPLOYEE and EMPLOYEE_HIST
tables. Which type of join would you use? 

The EMPLOYEE_ID column in the
EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.

The EMPLOYEE_ID column in the ORDERS table contains null
values for rows that you need to display.

Which type of join should you use to display the data?   

Oracle proprietary JOINS can use
the WHERE clause for conditions other than the join-condition. True or False?                                                    

You have been asked to create a
report that lists all corporate customers and all orders that they have placed.
The customers should be listed alphabetically beginning with the letter ‘A’,
and their corresponding order totals should be sorted from the highest amount
to the lowest amount.

Which of the following statements should you issue?  

                SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount

FROM customers c, orders o

WHERE c.custid = o.custid

ORDER BY companyname ASC, amount ASC;

                SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount

FROM customers c, orders o

WHERE c.custid = o.custid

ORDER BY companyname, amount;

                SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount

FROM customers c, orders o

WHERE c.custid = o.custid

ORDER BY amount DESC, companyname;

                SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount

FROM customers c, orders o

WHERE c.custid = o.custid

ORDER BY companyname, amount DESC;

Evaluate this SELECT statement:

Which result will the query display?

                The
total of the SALARY column for all employees that have a salary greater than
30000

                The
query generates an error and returns no results.

                The
number of rows in the EMPLOYEES table that have a salary greater than 30000 (*)

                The
number of employees that have a salary less than 30000

You can use GROUP functions in
all clauses of a SELECT statement. True or False?           

You need to calculate the
standard deviation for the cost of products produced in the Birmingham
facility. Which group function will you use? 

GROUPING SETS is another
extension to the GROUP BY clause and is used to specify multiple groupings of
data but provide a single result set. True or False? 

Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

GROUP BY GROUPING SETS(…….);

Select the correct GROUP BY GROUPING SETS clause from the
following list:

                GROUP
BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id),
(department_id, manager_id)

                GROUP
BY GROUPING SETS ((department_id, manager_id), (department_id, job_id),
(manager_id, job_id)) (*)

                GROUP
BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary),
(manager_id, job_id))

                GROUP
BY GROUPING SETS (department_id, salary), (department_id, job_id),
(department_id, manager_id)

MINUS will give you rows from the first query that are not
present in the second query. (True or False?)            

Which statement about group
functions is true?     

                Group
functions can be used in a WHERE clause.

                Group
functions can only be used in a SELECT list.

                A query
that includes a group function in the SELECT list must include a GROUP BY
clause.

                Group
functions ignore null values. (*)

What is the best explanation as
to why this SQL statement will NOT execute?

SELECT department_id “Department”, AVG
(salary)”Average”

                Salaries
cannot be averaged as not all the numbers will divide evenly.

                You
cannot use a column alias in the GROUP BY clause. (*)

                The
department id is not listed in the departments table.

                The
GROUP BY clause must have something to GROUP.

Which of the following SQL
statements could display the number of people with the same last name:  

                SELECT
first_name, last_name, COUNT(employee_id)

                SELECT
employee_id, DISTINCT(last_name)

                SELECT
employee_id, COUNT(last_name)

                SELECT
last_name, COUNT(last_name)

You need to create a report to
display the names of products with a cost value greater than the average cost
of all products. Which SELECT statement should you use?  

WHERE cost > (SELECT AVG(cost)

                SELECT
AVG(cost), product_name

FROM (SELECT AVG(cost) FROM product)

Examine the data in the PAYMENT table:

PAYMENT_ID     CUSTOMER_ID  PAYMENT_DATE     PAYMENT_TYPE               PAYMENT_AMOUNT

86590586             8908090                10-Jun-2003        BASIC                   859.00

89453485             8549038                15-Feb-2003       INTEREST             596.00

85490345             5489304                20-Mar-2003      BASIC                    568.00

This statement fails when executed:

SELECT customer_id, payment_type

     WHERE
payment_amount = 596.00 OR payment_date = ’20-Mar-2003′);

Which change could correct the problem?

                Remove
the quotes surrounding the date value in the OR clause.

                Change
the comparison operator to a single-row operator.

                Remove
the parentheses surrounding the nested SELECT statement.

                Change
the outer query WHERE clause to ‘WHERE payment_id IN’. (*)

Which statement about the ANY
operator, when used with a multiple-row subquery, is true?                                                                                   

                The ANY
operator can be used with the LIKE and IN operators.

                The ANY
operator can be used with the DISTINCT keyword.

                The ANY
operator compares every value returned by the subquery. (*)

                The ANY
operator is a synonym for the ALL operator.

Evaluate this SELECT statement
that includes a subquery:

SELECT last_name, first_name

     WHERE
salesperson_id = 20);

Which statement is true about the given subquery?                                                               

                The
outer query executes before the nested subquery.

                Both
the inner and outer queries must return a value, or an error occurs.

                An
error occurs if either the inner or outer queries do not return a value.

                The
results of the inner query are returned to the outer query. (*)

Single row subqueries may not
include this operator:   

The WITH clause enables a SELECT
statement to define the subquery block at the start of the query, process the
block just once, label the results, and then refer to the results multiple
times. True or False?              

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.

  • Python
  • PHP
  • CSS
  • android
  • html5
  • SEO
  • Javascript
  • Adobe Photoshop
  • WordPress
  • Perl
  • Joomla
  • Twitter Bootstrap
  • EX4
  • Magento
  • Ruby on Rails
  • jQuery
  • Drupal
  • C Programming
  • C++
  • C#
  • SQL
  • Node.js
  • Django
  • CCNA

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…

Answer: • Only a function can accept parameters.

Question:* Which SQL statement will return an error?

Answer: • USE PEOPLE; UPDATE people SET name = ‘john’ WHERE name != ‘john’

Question:* Other things being equal, which queries are the fastest?

Answer: • Queries including uncorrelated subqueries.


Error handling overview

Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened, or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily, we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc.

In this article, we’ll take a closer look at the TRY… CATCH statement: the syntax, how it looks, how it works and what can be done when an error occurs. Furthermore, the method will be explained in a SQL Server case using a group of T-SQL statements/blocks, which is basically SQL Server way of handling errors. This is a very simple yet structured way of doing it and once you get the hang of it, it can be quite helpful in many cases.

On top of that, there is a RAISERROR function that can be used to generate our own custom error messages which is a great way to translate confusing error messages into something a little bit more meaningful that people would understand.

Handling errors using TRY…CATCH

Here’s how the syntax looks like. It’s pretty simple to get the hang of. We have two blocks of code:

BEGIN TRY  

     –code to try

END TRY  

BEGIN CATCH  

     –code to run if an error occurs

–is generated in try

END CATCH

Anything between the BEGIN TRY and END TRY is the code that we want to monitor for an error. So, if an error would have happened inside this TRY statement, the control would have immediately get transferred to the CATCH statement and then it would have started executing code line by line.

Now, inside the CATCH statement, we can try to fix the error, report the error or even log the error, so we know when it happened, who did it by logging the username, all the useful stuff. We even have access to some special data only available inside the CATCH statement:

  • ERROR_NUMBER – Returns the internal number of the error
  • ERROR_STATE – Returns the information about the source
  • ERROR_SEVERITY – Returns the information about anything from informational errors to errors user of DBA can fix, etc.
  • ERROR_LINE – Returns the line number at which an error happened on
  • ERROR_PROCEDURE – Returns the name of the stored procedure or function
  • ERROR_MESSAGE – Returns the most essential information and that is the message text of the error

That’s all that is needed when it comes to SQL Server error handling. Everything can be done with a simple TRY and CATCH statement and the only part when it can be tricky is when we’re dealing with transactions. Why? Because if there’s a BEGIN TRANSACTION, it always must end with a COMMIT or ROLLBACK transaction. The problem is if an error occurs after we begin but before we commit or rollback. In this particular case, there is a special function that can be used in the CATCH statement that allows checking whether a transaction is in a committable state or not, which then allows us to make a decision to rollback or to commit it.

Let’s head over to SQL Server Management Studio (SSMS) and start with basics of how to handle SQL Server errors. The AdventureWorks 2014 sample database is used throughout the article. The script below is as simple as it gets:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

USE AdventureWorks2014

GO

— Basic example of TRY…CATCH

BEGIN TRY

— Generate a divide-by-zero error  

  SELECT

    1 / 0 AS Error;

END TRY

BEGIN CATCH

  SELECT

    ERROR_NUMBER() AS ErrorNumber,

    ERROR_STATE() AS ErrorState,

    ERROR_SEVERITY() AS ErrorSeverity,

    ERROR_PROCEDURE() AS ErrorProcedure,

    ERROR_LINE() AS ErrorLine,

    ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

GO

This is an example of how it looks and how it works. The only thing we’re doing in the BEGIN TRY is dividing 1 by 0, which, of course, will cause an error. So, as soon as that block of code is hit, it’s going to transfer control into the CATCH block and then it’s going to select all of the properties using the built-in functions that we mentioned earlier. If we execute the script from above, this is what we get:

Basic SQL Server try catch script executed in Management Studio that returns an error

We got two result grids because of two SELECT statements: the first one is 1 divided by 0, which causes the error and the second one is the transferred control that actually gave us some results. From left to right, we got ErrorNumber, ErrorState, ErrorSeverity; there is no procedure in this case (NULL), ErrorLine, and ErrorMessage.

Now, let’s do something a little more meaningful. It’s a clever idea to track these errors. Things that are error-prone should be captured anyway and, at the very least, logged. You can also put triggers on these logged tables and even set up an email account and get a bit creative in the way of notifying people when an error occurs.

If you’re unfamiliar with database email, check out this article for more information on the emailing system: How to configure database mail in SQL Server

The script below creates a table called DB_Errors, which can be used to store tracking data:

— Table to record errors

CREATE TABLE DB_Errors

         (ErrorID        INT IDENTITY(1, 1),

          UserName       VARCHAR(100),

          ErrorNumber    INT,

          ErrorState     INT,

          ErrorSeverity  INT,

          ErrorLine      INT,

          ErrorProcedure VARCHAR(MAX),

          ErrorMessage   VARCHAR(MAX),

          ErrorDateTime  DATETIME)

GO

Here we have a simple identity column, followed by username, so we know who generated the error and the rest is simply the exact information from the built-in functions we listed earlier.

Now, let’s modify a custom stored procedure from the database and put an error handler in there:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

ALTER PROCEDURE dbo.AddSale @employeeid INT,

                   @productid  INT,

                   @quantity   SMALLINT,

                   @saleid     UNIQUEIDENTIFIER OUTPUT

AS

SET @saleid = NEWID()

  BEGIN TRY

    INSERT INTO Sales.Sales

         SELECT

           @saleid,

           @productid,

           @employeeid,

           @quantity

  END TRY

  BEGIN CATCH

    INSERT INTO dbo.DB_Errors

    VALUES

  (SUSER_SNAME(),

   ERROR_NUMBER(),

   ERROR_STATE(),

   ERROR_SEVERITY(),

   ERROR_LINE(),

   ERROR_PROCEDURE(),

   ERROR_MESSAGE(),

   GETDATE());

  END CATCH

GO

Altering this stored procedure simply wraps error handling in this case around the only statement inside the stored procedure. If we call this stored procedure and pass some valid data, here’s what happens:

Script for inserting valid data through a stored procedure into Sales table

A quick Select statement indicates that the record has been successfully inserted:

Script for validating if data is inserted successfully into the table

However, if we call the above-stored procedure one more time, passing the same parameters, the results grid will be populated differently:

Script for inserting invalid data that would cause raise error SQL state

This time, we got two indicators in the results grid:

0 rows affected – this line indicated that nothing actually went into the Sales table

1 row affected – this line indicates that something went into our newly created logging table

So, what we can do here is look at the errors table and see what happened. A simple Select statement will do the job:

Script for retrieving data from the errors table

Here we have all the information we set previously to be logged, only this time we also got the procedure field filled out and of course the SQL Server “friendly” technical message that we have a violation:

Violation of PRIMARY KEY constraint ‘PK_Sales_1′. Cannot insert duplicate key in object’ Sales.Sales’. The duplicate key value is (20).

How this was a very artificial example, but the point is that in the real world, passing an invalid date is very common. For example, passing an employee ID that doesn’t exist in a case when we have a foreign key set up between the Sales table and the Employee table, meaning the Employee must exist in order to create a new record in the Sales table. This use case will cause a foreign key constraint violation.

The general idea behind this is not to get the error fizzled out. We at least want to report to an individual that something went wrong and then also log it under the hood. In the real world, if there was an application relying on a stored procedure, developers would probably have SQL Server error handling coded somewhere as well because they would have known when an error occurred. This is also where it would be a clever idea to raise an error back to the user/application. This can be done by adding the RAISERROR function so we can throw our own version of the error.

For example, if we know that entering an employee ID that doesn’t exist is more likely to occur, then we can do a lookup. This lookup can check if the employee ID exists and if it doesn’t, then throw the exact error that occurred. Or in the worst-case scenario, if we had an unexpected error that we had no idea what it was, then we can just pass back what it was.

Advanced SQL error handling

We only briefly mentioned tricky part with transactions, so here’s a simple example of how to deal with them. We can use the same procedure as before, only this time let’s wrap a transaction around the Insert statement:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

ALTER PROCEDURE dbo.AddSale @employeeid INT,

                   @productid  INT,

                   @quantity   SMALLINT,

                   @saleid     UNIQUEIDENTIFIER OUTPUT

AS

SET @saleid = NEWID()

  BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO Sales.Sales

         SELECT

           @saleid,

           @productid,

           @employeeid,

           @quantity

    COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

    INSERT INTO dbo.DB_Errors

    VALUES

  (SUSER_SNAME(),

   ERROR_NUMBER(),

   ERROR_STATE(),

   ERROR_SEVERITY(),

   ERROR_LINE(),

   ERROR_PROCEDURE(),

   ERROR_MESSAGE(),

   GETDATE());

— Transaction uncommittable

    IF (XACT_STATE()) = 1

      ROLLBACK TRANSACTION

— Transaction committable

    IF (XACT_STATE()) = 1

      COMMIT TRANSACTION

  END CATCH

GO

So, if everything executes successfully inside the Begin transaction, it will insert a record into Sales, and then it will commit it. But if something goes wrong before the commit takes place and it transfers control down to our Catch – the question is: How do we know if we commit or rollback the whole thing?

If the error isn’t serious, and it is in the committable state, we can still commit the transaction. But if something went wrong and is in an uncommittable state, then we can roll back the transaction. This can be done by simply running and analyzing the XACT_STATE function that reports transaction state.

This function returns one of the following three values:

  1 – the transaction is committable

-1 – the transaction is uncommittable and should be rolled back

  0 – there are no pending transactions

The only catch here is to remember to actually do this inside the catch statement because you don’t want to start transactions and then not commit or roll them back:

Script for modifying the stored procedure for inserting sales data to either rollback or commit transaction

How, if we execute the same stored procedure providing e.g. invalid EmployeeID we’ll get the same errors as before generated inside out table:

T-SQL code for inserting invalid data that would cause raise error SQL state

The way we can tell that this wasn’t inserted is by executing a simple Select query, selecting everything from the Sales table where EmployeeID is 20:

A Select statement that proves nothing was inserted into Sales table with the employee ID of 20

Generating custom raise error SQL message

Let’s wrap things up by looking at how we can create our own custom error messages. These are good when we know that there’s a possible situation that might occur. As we mentioned earlier, it’s possible that someone will pass an invalid employee ID. In this particular case, we can do a check before then and sure enough, when this happens, we can raise our own custom message like saying employee ID does not exist. This can be easily done by altering our stored procedure one more time and adding the lookup in our TRY block:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

ALTER PROCEDURE dbo.AddSale @employeeid INT,

                   @productid  INT,

                   @quantity   SMALLINT,

                   @saleid     UNIQUEIDENTIFIER OUTPUT

AS

SET @saleid = NEWID()

  BEGIN TRY

  IF (SELECT COUNT(*) FROM HumanResources.Employee e WHERE employeeid = @employeeid) = 0

      RAISEERROR (‘EmployeeID does not exist.’, 11, 1)

    INSERT INTO Sales.Sales

         SELECT

           @saleid,

           @productid,

           @employeeid,

           @quantity

  END TRY

  BEGIN CATCH

    INSERT INTO dbo.DB_Errors

    VALUES

  (SUSER_SNAME(),

   ERROR_NUMBER(),

   ERROR_STATE(),

   ERROR_SEVERITY(),

   ERROR_LINE(),

   ERROR_PROCEDURE(),

   ERROR_MESSAGE(),

   GETDATE());

   DECLARE @Message varchar(MAX) = ERROR_MESSAGE(),

        @Severity int = ERROR_SEVERITY(),

        @State smallint = ERROR_STATE()

   RAISEERROR (@Message, @Severity, @State)

  END CATCH

GO

If this count comes back as zero, that means the employee with that ID doesn’t exist. Then we can call the RAISERROR where we define a user-defined message, and furthermore our custom severity and state. So, that would be a lot easier for someone using this stored procedure to understand what the problem is rather than seeing the very technical error message that SQL throws, in this case, about the foreign key validation.

With the last changes in our store procedure, there also another RAISERROR in the Catch block. If another error occurred, rather than having it slip under, we can again call the RAISERROR and pass back exactly what happened. That’s why we have declared all the variables and the results of all the functions. This way, it will not only get logged but also report back to the application or user.

And now if we execute the same code from before, it will both get logged and it will also indicate that the employee ID does not exist:

Custom raise error SQL Server message returned by executing the script and inserting invalid data through a stored procedure

Another thing worth mentioning is that we can actually predefine this error message code, severity, and state. There is a stored procedure called sp_addmessage that is used to add our own error messages. This is useful when we need to call the message on multiple places; we can just use RAISERROR and pass the message number rather than retyping the stuff all over again. By executing the selected code from below, we then added this error into SQL Server:

Script for storing message code, severity, and state in an instance of the SQL Server Database Engine used to add our own raise error SQL message

This means that now rather than doing it the way we did previously, we can just call the RAISERROR and pass in the error number and here’s what it looks like:

The custom raise error SQL Server message with code, severity, and state in results grid of Management Studio

The sp_dropmessage is, of course, used to drop a specified user-defined error message. We can also view all the messages in SQL Server by executing the query from below:

SELECT * FROM master.dbo.sysmessages

List of all SQL Server error messages showed in results grid of Management Studio

There’s a lot of them and you can see our custom raise error SQL message at the very top.

I hope this article has been informative for you and I thank you for reading.

References

  • TRY…CATCH (Transact-SQL)
  • RAISERROR (Transact-SQL)
  • System Functions (Transact-SQL)
  • Author
  • Recent Posts

Bojan Petrovic

Bojan aka “Boksi”, an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology, is a software analyst with experience in quality assurance, software support, product evangelism, and user engagement.

He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.

Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.

See more about Bojan at LinkedIn

View all posts by Bojan Petrovic

Bojan Petrovic

Before we dive into our topic, let’s take a moment to ponder on what SQL is and why error handling in it is important. SQL, or Structured Query Language, is a language designed specifically for managing and manipulating databases. As with any language, errors are bound to occur while writing SQL queries, and understanding how to handle these errors can be crucial to both the health of our databases and our sanity as developers. This is where error handling comes in. It is a mechanism which helps us handle runtime errors effectively and ensures that our application doesn’t break unexpectedly. Now, without further ado, let’s dig deeper into this topic.

Understanding Errors in SQL

It’s crucial to understand that errors can and will occur while working with SQL. These could be due to various reasons – invalid data input, logical errors, system failures or even connection issues. Typically, when SQL Server encounters an error during the execution of a script, it stops and returns an error message. There could be several error messages as a single line could produce multiple errors.

For example, consider the following simple SQL statement:

SELECT * FROM Employees

Code language: SQL (Structured Query Language) (sql)

If the Employees table does not exist, this SQL statement will trigger an error. And unless this error is properly caught and handled, it could result in the termination of the program or unexpected behavior.

Types of Errors

There are two main types of errors in SQL: compile-time errors and runtime errors.

Compile-time Errors

Compile-time errors occur during the compile time of the SQL program. These include syntax errors like missing semicolons or misspelled keywords. For instance:

SELEC * FROM Employees;

Code language: SQL (Structured Query Language) (sql)

Here, SELEC should be SELECT.

Runtime Errors

Runtime errors occur during the execution of the SQL program. These include logical errors like division by zero or data type mismatches. For instance:

SELECT EmployeeAge/DaysWorked as AvgWorkPerDay FROM Employees;

Code language: SQL (Structured Query Language) (sql)

Here, if DaysWorked is zero for any employee, it will throw a division by zero error.

Error Handling in SQL

To handle these errors effectively, most SQL environments offer error handling mechanisms. We will primarily discuss error handling in SQL Server, which uses a mechanism known as T-SQL error handling.

TRY…CATCH in SQL Server

In SQL Server, the TRY…CATCH construct is used for error handling. This construct catches and handles exceptions in a similar way to other programming languages.

Here’s an example:

BEGIN TRY -- Generate a divide-by-zero error. SELECT 1 / 0 AS Error; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

Code language: SQL (Structured Query Language) (sql)

In the above code, the TRY block contains the code that may potentially cause an error. The CATCH block contains the code that will be executed if an error occurs.

ERROR_NUMBER() and ERROR_MESSAGE() are system functions which return the error number and error message respectively when called within the CATCH block.

Implementing Error Handling

Now that we have discussed the basics of error handling in SQL, let’s implement it in a more realistic scenario.

Let’s consider a scenario where we need to insert data into a database. Let’s assume that our table, Employees, has three columns: ID, Name, and Age.

INSERT INTO Employees (ID, Name, Age) VALUES (1, 'John Doe', 30);

Code language: SQL (Structured Query Language) (sql)

What if the ID is already present in the database? The insertion will fail, and we should handle this error. Here’s how we can do it:

BEGIN TRY INSERT INTO Employees (ID, Name, Age) VALUES (1, 'John Doe', 30); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

Code language: SQL (Structured Query Language) (sql)

In the above SQL script, if an error occurs during the INSERT operation (perhaps because an employee with the same ID already exists), SQL Server will immediately transfer the control to the CATCH block. The CATCH block will then print out the error number and error message.

Note: It’s important to understand that the TRY...CATCH block can only catch runtime errors, not compile-time errors.

Using THROW to Rethrow Errors

Sometimes, we might want to rethrow an error after catching it. This is often useful when we want to add additional information to the error or perform some actions before the error is propagated further. This can be done using the THROW statement.

Consider the following example:

BEGIN TRY INSERT INTO Employees (ID, Name, Age) VALUES (1, 'John Doe', 30); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; THROW; -- Rethrowing the error. END CATCH;

Code language: SQL (Structured Query Language) (sql)

In this example, if an error occurs, we first catch the error, print out the error number and message, and then rethrow the error using the THROW statement.

Using @@ERROR

In addition to TRY...CATCH, SQL Server also provides a global variable @@ERROR that we can use to check if the last executed statement was successful or not.

@@ERROR will be 0 if the last statement was successful; if not, it will be a number different than 0 which corresponds to the error number.

Here’s how we can use @@ERROR:

INSERT INTO Employees (ID, Name, Age) VALUES (1, 'John Doe', 30); IF @@ERROR <> 0 BEGIN PRINT 'An error occurred while inserting data into the Employees table.'; END

Code language: SQL (Structured Query Language) (sql)

In this example, if an error occurs during the INSERT operation, we check @@ERROR and print an error message accordingly.

Frequently Asked Questions (FAQ)

1. What is error handling in SQL?

Error handling in SQL is a mechanism that allows us to catch and handle errors that occur during the execution of SQL scripts. It prevents the termination of the program and allows us to take appropriate actions when an error occurs.

2. What is the difference between compile-time and runtime errors?

Compile-time errors occur during the compilation of the SQL script. These are typically syntax errors. Runtime errors, on the other hand, occur during the execution of the SQL script. These include logical errors and exceptions.

3. How can I handle errors in SQL Server?

SQL Server provides the TRY...CATCH construct for error handling. You can write the code that might cause an error inside the TRY block, and the code to handle the error in the CATCH block.

4. What does the THROW statement do in SQL Server?

The THROW statement is used to rethrow an error in SQL Server. This is often used in a CATCH block to propagate the error further after catching and handling it.

5. What is @@ERROR in SQL Server?

@@ERROR is a global variable in SQL Server that you can use to check if the last executed statement was successful or not. If the last statement was successful, @@ERROR will be 0; if not, it will be a number different from 0, which corresponds to the error number.

6. Can TRY…CATCH in SQL Server catch compile-time errors?

No, the TRY...CATCH construct in SQL Server can only catch runtime errors. Compile-time errors must be resolved before the script can be executed.

7. Can I use multiple TRY…CATCH blocks in SQL Server?

Yes, you can use multiple TRY...CATCH blocks in SQL Server. This allows you to handle different types of errors in different ways. However, if an error is not caught in the TRY block where it occurs, control is passed to the next higher TRY...CATCH block (if any) or to the host application.

8. What happens when an error occurs in SQL Server and it’s not handled?

When an error occurs in SQL Server and it’s not handled, the error is returned to the caller and the execution of the script is terminated.

Error handling in SQL is a crucial aspect of database programming. It ensures the robustness of the database application and prevents unexpected termination of the program due to errors. The TRY...CATCH construct and @@ERROR variable in SQL Server provide a powerful mechanism to catch and handle errors effectively. Remember, it’s not just about writing code that works, it’s about writing code that works even when things go wrong.

Oracle Academy Test: Final Exam Semester 1 Section 15 – Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

oracle academy test
Oracle Academy Test

 Semester 1 Final Exam covers Sections 11-17 of Database Design.  


 Section 15 
 (Answer all questions in this section) 
     
1.  What would you use in the SELECT clause to return all the columns in the table?  Mark for Review 
(1) Points 
      
    An asterisk (*) (*) 
  
    A minus sign (-) 
  
    A plus sign (+) 
  
    The ALL keyword 
  
  
      
 2.  Evaluate this SELECT statement: 
SELECT (salary * raise_percent) raise 
FROM employees; 


If the RAISE_PERCENT column only contains null values, what will the statement return? 
 Mark for Review 
(1) Points 
      
    Only zeroes 
  
    Only null values (*) 
  
    A null value or a zero depending on the value of the SALARY column 
  
    A null value or a numeric value depending on the value of the SALARY column 
  
      
      Incorrect. See Section 15 Lesson 1.  
  
      
  3.  When listing columns in the SELECT list, what should you use to separate the columns?  Mark for Review 
(1) Points 
      
    Commas (*) 
  
    Semicolons 
  
    Dashes 
  
    Underscores 
        

  
 4.  The EMPLOYEES table contains these columns: 
SALARY NUMBER(7,2) 
BONUS NUMBER(7,2) 
COMMISSION_PCT NUMBER(2,2) 


All three columns contain values greater than zero. There is one row of data in the table and the values are as follows: 


Salary = 500, Bonus = 50, Commission_pct = .5 


Evaluate these two SQL statements: 


1. 
SELECT salary + bonus + commission_pct * salary – bonus AS income 
FROM employees; 


2. 
SELECT (salary + bonus ) + commission_pct * (salary – bonus) income 
FROM employees; 


What will be the result? 
 Mark for Review 
(1) Points 
      
    Statement 1 will return a higher value than statement 2. 
  
    Statement 2 will return a higher value than statement 1. (*) 
  
    Statement 1 will display a different column heading. 
  
    One of the statements will NOT execute. 
  
      
      Incorrect. See Section 15 Lesson 1.  
  
      
  5.  Which SQL statement will return an error?  Mark for Review 
(1) Points 
      
    SEL * FR sky; (*) 
  
    select star from sky; 
  
    SELECT star FROM sky; 
  
    SELECT * FROM sky; 
  
        
      
  6.  You query the database with this SQL statement: 
SELECT * 
FROM transaction 
WHERE product_id = 4569; 


Which SQL SELECT statement capabilities are achieved when this statement is executed? 
 Mark for Review 
(1) Points 
      
    Selection only (*) 
  
    Projection only 
  
    Selection and projection only 
  
    Projection, selection and joining 
  
        
      
7.  In which clause of a SELECT statement would you specify the name of the table or tables being queried?  Mark for Review 
(1) Points 
      
    The FROM clause (*) 
  
    The SELECT clause 
  
    The WHERE clause 
  
    Any of the above options, you can list tables wherever you want to in a SELECT statement. 
   
 Section 16 
 (Answer all questions in this section) 
      
8.  The EMPLOYEES table contains these columns: 
LAST_NAME VARCHAR2(25) 
FIRST_NAME VARCHAR2(25) 
EMAIL VARCHAR2(50) 


You are writing a SELECT statement to retrieve the names of employees that have an email address. 


SELECT last_name||’, ‘||first_name “Employee Name” 
FROM employees;


Which WHERE clause should you use to complete this statement?
 Mark for Review 
(1) Points 
      
    WHERE email = NULL; 
  
    WHERE email != NULL; 
  
    WHERE email IS NULL; 
  
    WHERE email IS NOT NULL; (*) 
  
      
      Incorrect. See Section 16 Lesson 3.  
  
      
 9.  You need to display all the values in the EMAIL column that contains the underscore (_) character as part of that email address. The WHERE clause in your SELECT statement contains the LIKE operator. What must you include in the LIKE operator?  Mark for Review 
(1) Points 
      
    The ESCAPE option (\) and one or more percent signs (%) 
  
    The (+) operator 
  
    A percent sign (%) 
  
    The ESCAPE option (\) (*) 
  
      
      Incorrect. See Section 16 Lesson 2.  
  
      
10.  You want to determine the orders that have been placed by customers who reside in Chicago. You write this partial SELECT statement: 
SELECT orderid, orderdate, total 
FROM orders; 


What should you include in your SELECT statement to achieve the desired results?
 Mark for Review 
(1) Points 
      
    AND city = Chicago; 
  
    AND city = ‘Chicago’; 
  
    WHERE city = ‘Chicago’; (*) 
  
    WHERE city = Chicago; 
  
      

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *