3 - Populating & Retrieving Data in Databases

Examine the following code. What will the value of cost be if the statement finds a NULL value?

$0.01

Given the following table called Students, how many records would be returned using the following SQL database query? SELECT Student ID FROM Students WHERE Financial Aid = Yes AND GPA > 3.0

3

The GROUP BY clause is:

A query clause

Examine the following CASE statement. What is missing?

An END statement

You have been provided the table below. The field names are studentID, firstName and lastName and all are of type nchar(20). You need to combine the lastName and firstName fields to give the full name of the students. For example, the full name of the studentID should be displayed as 'Bates, Bernice'. Which two SQL functions used together will produce the correct results? Note: the name should not have any trailing spaces.

CONCAT and TRIM

Given the sample table below, what will the SQL function return?

Co

_____ operators are used to compare values, while _____ operators make it possible to combine two or more conditions in a single statement.

Comparison; boolean

Which SQL function can be used when you want to get results on how many days have passed after the dates in the field, such as in a billing cycle?

DATEDIFF

INSERT is part of a categorization of SQL statements called _____

Data Manipulation Language (DML)

It is mandatory to specify the sort order for each column by adding the keywords ASC (for an ascending sort) or DESC (for a descending sort) after each column name in the ORDER BY clause of a SELECT statement. Select the most appropriate answer

False. It is optional to provide the ASC keyword as SQL assumes an ascending order sort to be the default order but you must specify the DESC keyword for those columns that you want to be sorted in a descending order.

Pick the result that would be returned from the following SQL query: SELECT top 5 FirstName, LastName FROM Actors ORDER BY LastName ASC;

FirstName | LastName -------------------------- Ben | Afleck Angela | Basset Chadwick | Bosman Robert | Downey Zac | Efron

Which statement will correctly insert a new record into the genre table?

INSERT INTO genre (genre) VALUES ('Rock');

NVL can be used with the following data types:

String or Numeric

NVL is a function used for _____

Substitution

In order to ensure a NULL inside a numeric field is replaced with a string, you use the _____ keyword

TO_CHAR

Consider that your database tables have fields with extra spaces and padding in them. Which function should you use to ensure that these extras are not interfering with your queries?

TRIM

Examine the following code. What is missing?

The field to evaluate

In what scenario would you want to use the UPPER function in SQL?

The text values might be mixed-case

Why is SQL so widely used?

This programming language can be applied to any database management system once learned.

It is possible to provide a column name that is not a part of the resultset in an ORDER BY clause of a SELECT statement.

True

It is possible to provide column position numbers from the resultset in an ORDER BY clause of an SELECT statement instead of providing column names.

True

How should the field names in the INSERT INTO statement and the values in the VALUES statement be arranged?

Values must match fields

Required parts of the CASE statement are:

WHEN, THEN, END

When using the ORDER BY clause, where is it located?

following the GROUP BY clause

What will the following SQL function return, based on the sample table, users?

make

Write a SELECT SQL statement to retrieve records whose ages are not equal to 50.

select * from Actors where Age <> 50

Write a SELECT SQL statement to retrieve records whose ages are greater than 50.

select * from Actors where Age > 50

What notation must be used to end a GROUP BY clause or any SQL statement?

the semicolon;

How many columns can be used to sort the data in an ORDER BY clause?

One or more columns, with each one in either ASC or DESC order

How does the ORDER BY clause differ from the GROUP BY clause?

It orders the data in the group based on the specified column.

Examine the following SQL statement. What will be displayed in the query if the artistID is 14?

Journey

What happens if you leave off the ELSE clause in an SQL CASE statement and not conditions are met?

NULL is returned

Which SQL statement is used to sort the result of a database query?

ORDER BY

There is only one way to sort the data retrieved from a database using SQL. Which clause of the SELECT statement allows you to do that?

ORDER BY clause

If a field allows blank or NULL values, it is _____ to specify it in the INSERT statement.

Optional

NVL can be used in the following databases:

Oracle

Pick the result that would be returned from the following SQL query: SELECT FirstName FROM Actors WHERE Sex = 'Male' and Age BETWEEN '40' AND '50';

Paul Ben Ryan Chadwick James

It's best to use ISNULL for mathematical operations because:

Performing them on a NULL value can cause errors (It's best to account for them by using ISNULL and replacing them with something you can use - such as 0 or 50 for a weighted average)

The ELSE clause in a CASE statement is _____

Recommended

Consider the tables shown below. The first table is the original table, named CountryCode that lists all Country Codes and Country names, and the second table list out all countries with the CountryCode starting with the character 'U'. Which SQL statement can accomplish this?

SELECT * FROM CountryCode WHERE countrycode LIKE 'U%'

Given a table called Employees with a field called Last Name, using SQL, how would you select all the records with the last name 'Jones'?

SELECT * FROM Employees WHERE Last Name = 'Jones'

Consider the two tables given below. The first table is the original table named podelivery. Select the correct SQL SELECT statement that will give you the output as shown in the second table?

SELECT * FROM podelivery WHERE ponumber LIKE 'C%' ORDER BY ponumber DESC, poitem DESC, dateofdelivery DESC

Write a SELECT SQL statement to retrieve records for which the FirstName starts with the letter 'C', the LastName contains the letter 'o', is between the age of 40 and 43, and is Female.

SELECT * from Actors WHERE FirstName like 'C%' and LastName like '%o%' and Sex = 'Female' and Age BETWEEN '40' and '43'

You can insert multiple rows into the table by adding _____ to the INSERT statement.

SELECT ... FROM ... WHERE

Which statement correctly displays 99999 for NULL Zip Codes in the Customer_Contact table?

SELECT NVL(ZipCode, 99999) FROM Customer_Contact;

Which is the correct syntax for replacing a blank customer SSN with 555-55-5555?

SELECT customerName,ISNULL(SSN, '555-55-5555')FROM tblCustomer;

Consider the tables given below. The first table is named PODelivery. The second table contains the summarized information from the first table. The quantity delivered for each ponumber/poitem is summarized and shown. Which SQL statement will accomplish this?

SELECT ponumber, poitem, SUM(quantityDelivered) AS 'Qty Delivered' FROM podelivery GROUP BY ponumber, poitem ORDER BY ponumber, poitem;

The GROUP By clause is always paired with _____ and must precede _____, if it is used.

SELECT, ORDER BY

Using ISNULL to replace a NULL value with another value is used mainly in _____.

SQL Server