SQLite LinkedIn Learning

How should an alias be defined if it has a space in it? -Large*deposits -Large deposits -"Large*deposits" -"Large deposits"

"Large deposits"

If you are writing a statement that returns all the fields in a table, which wildcard would you use? - ; - & - @ - *

*

What is the output of the following statement? SELECT 2*(5+1); -8 -0.333 -12 -11

12

What does a record represent in a database? -A kind of information -A set of information -A type of fields -A table of values

A set of information

What keyword would you use in a SELECT statement to give a column a friendly name? -FROM -LIKE -IS -AS

AS

What is a common problem when working with SQL -Not checking the scope of a destructive action -All of these choices are common problems to watch out for -Accidental 'smart quotes' when copying and pasting -An incorrect order of clauses in a statement

All of these choices are common problems to watch out for

As a database administrator, you receive a request to store images in a column. Which SQL data type is the best choice for storing this data? -Numbers -Binary -Date/Time -Text

Binary

Which choice is NOT a numerical data type? -CHARACTER -FLOAT -REAL -DOUBLE PRECISION

CHARACTER

How would you delete all customer records that have their "Phone field missing? Assume this field is defined as textual. -DELETE FROM Customers WHERE phone IS NULL; -Delete from Customers WHERE Phone <> ""; -DELETE FROM Customers WHERE Phone = Null; -DELETE FROM Customers WHERE Phone > 0;

DELETE FROM Customers WHERE phone IS NULL;

Select the best approach of deleting Jon Ramirez (ID 3452) from a Student table. -DELETE FROM student WHERE Student <> 3452; -DELETE FROM student WHERE StudentID = 3452; -DELETE FROM Student; -DELETE FROM student WHERE Firstname = 'Jon';

DELETE FROM student WHERE StudentID = 3452;

Which data row will be left out with this condition in your statement? WHERE Color!= 'B' OR (Size='L' AND Price>20) -Color is 'B', Size is 'L' and Price is 30. -Color is 'W', Size is 'S' and price is 20. -Color is 'G', Size is 'L' and price is 25 -Color is 'B', Size is 'XL' and price is 10.

Color is 'B', Size is 'XL' and price is 10

Bryan is 13 years old, Lisa is 15, and Mike is 21. Their ages would most likely be stored in a... -Column -Row -Schema -Table

Column

What is the result after executing the statement: SELECT UPPER (SUBSTRING ('Textstring' ,2,4)); -EX -EXT -ext -EXTSTRI

EXT

What is the predicate in the following statement? SELECT Eyecolor, Age FROM Student WHERE Firstname = 'Tim' ORDER BY Lastname ASC; -ORDER BY -Firstname= 'Tim' -Firstname -;

Firstname= 'Tim'

How many clauses are defined in this statement? SELECT Height, Weight, FROM Shapes WHERE Material='wood' ORDER BY Height;

Four

What is the result of running the following statement on a table containing the columns col_1 and col_2: INSTER INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B'); -One row inserted into the Box table -Four rows inserted into the Box table -Two rows inserted into the Box table -Zero rows inserted into the Box table

Four rows inserted into the Box table

Which type of join returns only the matches for items that are in both tables? -Outer join -Right join -Left join -Inner join

Inner join

Which statement is true regarding this query executed on an SQLite database? INSERT INTO Parts (Price, Description) VALUES (35.00, Battery') ; -It will add a new row to the table -It will update an existing row in the table -It will fail if the table has additional columns besides price and description -It will fail if the description column is defined as a numeric field.

It will add a new row to the table

Which SQL clause will you add to this query in order to associate the "Phone" field with the "Number1" fields in the "contacts" table? -JOIN Contacts ON Phone=Number1 -JOIN ON Customers.Phone=Contacts.Number1 -JOIN Contacts ON Customers.Phone=Contacts.Number1 -CONNECT Customers.Phone=Contacts.Number1

JOIN Contacts ON Customers.Phone=Contacts.Number1

Your SQL statement is returning a TransactionDate field. What can you add to it in order to list the dates from latest to earliest. -ORDER BY TransactionDate Current -ORDER BY TransactionDate ASC ORDER BY TransactionDate -ORDER BY TransactionDate DESC

ORDER BY TransactionDate DESC

If the WHERE clause contains multiple expressions to evaluate, you can use ... to enforce the order in which the expressions must be evaluated -Parentheses -comma -AND statement -OR statement

Parentheses

Which records will be shown when the query ends with LIMIT 10 OFFSET 5? -Records 6 through 15 -Records 5 through 15 -Records 5 through 10 -Records 6 through 10

Records 6 through 15

Which SQL query demonstrates a common mistake? - SELECT * FROM Bank Deposits; -SELELCT * FROM Accounts WHERE Name='John Smith' ; -DELETE FROM Accounts WHERE AccountID is Null; -SELECT * FROM 'Bank Deposits' ;

SELECT * FROM Bank Deposits;

This statement will return a row for every row in the Customer table, and also associated information from the CustomerDetail table if there is any... -SELECT * FROM CustomerDetail RIGHT JOIN CustomerDetail on Customer.ID = CustomerDetail.CustomerID; -SELECT * FROM Customer LEFT JOIN CustomerDetail ON Customer.ID = CustomerDetail.CustomerId; -SELECT * FROM Customer WHERE CustomerDetail.CustomerID = Customer.ID; -SELECT * FROM Customer;

SELECT * FROM Customer LEFT JOIN CustomerDetail ON Customer.ID = CustomerDetail.CustomerId;

To sort a list of students in descending order by last name, you can use which statement? -SELECT Firstname, Lastname FROM Student ORDER BY Lastname DESC; -SELECT Firstname, ORDER BY Lastname DESC; -SELECT Firstname, Lastname FROM Student ORDER BY lastname -SELECT Firstname, Lastname FROM Student WHERE ORDER BY Lastname DESC;

SELECT Firstname, Lastname FROM Student ORDER BY Lastname DESC;

Choose the correct statement that returns the students from all states except New York. -SELECT Firstname, Lastname FROM Student WHERE >= 'NY'; -SELECT Firstname, Lastname FROM Student WHERE State is 'CA' AND NOT 'NY'; -SELECT Firstname, Lastname FROM Student WHERE State != 'NY'; -SELECT Firstname, Lastname FROM student WHERE State = 'CA', 'GA', 'NH';

SELECT Firstname, Lastname FROM Student WHERE State != 'NY';

In order to get a list of participants who earned the maximum score on our quiz, we might write a query that looks like: -SELECT Firstname, Lastname, quiz_points FROM people WHERE MAX (quiz_points); -SELECT Firstname, Lastname, MAX(quiz_points) FROM people; -SELECT Firstname, lastname, quiz_points FROM people WHERE quiz_points=(SELECT MAX(quiz_points) FROM PEOPLE);

SELECT Firstname, lastname, quiz_points FROM people WHERE quiz_points=(SELECT MAX(quiz_points) FROM PEOPLE);

Which query will return the highest sale amount for every office? -SELECT Office FROM Sales GROUP BY MAX(amount); -SELECT MAX(amount, office) FROM Sales; -SELECT MAX (amount) FROM Sales GROUP BY Office; -SELECT MAX(amount), Office FROM Sales;

SELECT MAX (amount) FROM Sales GROUP BY Office;

This statement will replace the letter "d" in stidents' first names with a dash character: -SELECT REPLACE(firstname, 'd', '-') FROM students; -SELECT REPLACE (firstname, "-", 'd') FROM students; -SELECT firstname, REPLACE ('d', '-') FROM students; -SELECT REPLACE (firstname, 'D', '-') FROM student

SELECT REPLACE(firstname, 'd', '-') FROM students;

For each state, this statement shows the number of quiz participants... -SELECT state, COUNT (quiz_points) FROM people GROUP BY state; -SELECT state, COUNT(quiz_points) FROM people; -SELECT BY state, COUNT(quiz_points) FROM people; -SELECT GROUP BY state, COUNT(quiz_points) FROM people;

SELECT state, COUNT (quiz_points) FROM people GROUP BY state;

What is the correct order of clauses in an SQL query that includes WHERE? -SELECT, FROM, WHERE -WHERE, SELECT, FROM -SELECT, WHERE, FROM -FROM, WHERE, SELECT

SELECT, FROM, WHERE

What does the VARCHAR data type usually store? -A varying sequence of true or false values -Names of variables used within SQL -Text with variable length -Numbers with low precision

Text with variable length

Replace the ??? placeholder with the proper SQL code that returns the last four digits of an ID number stored in the field called Number: SELECT ??? FROM IDInformation; -LAST (Number, 4) -CAST (Number, 4, 0) -SUBSTR (Number, 4) -SUBSTR (Number, -4)

SUBSTR (Number, -4)

What is the best way to share a long SQL code snippet with another developer? -Replace all quotes with smart quotes, and send the code in an email body -Save the code as a plain text, and send it as a file attachment -Copy the entire code into an email body, and send the email -Save the code in a word processing format, and send it as an attachment

Save the code as a plain text, and send it as a file attachment

What could this query possibly return? SELECT Name FROM Students WHERE DateAttended=(SELECT MAX (EventDate) FROM Events); -The names of all students who attend at least one event -The date of the latest event that had students attending -The names of the students who attended the largest numbers of events -The names of all students who attended the most recent event

The names of all students who attended the most recent event

The "Items" table has one "Name; column with 12 items in it. What will this query return? SELECT 'Name' FROM items; -The text "name" showing 12 times -The values of all 12 items -The text "name" showing once -The text "items" showing once

The text "name' showing 12 times

Which statement is true regarding the DB Browser tool? - This tool must connect to a database server -This tool must be purchased -This tool works consistently across all major platforms -This tool does not offer a log of historical commands

This took works consistently across all major platforms

What is the DISTINCT clause used for? - To sort the values in a column in a certain order -To count how many rows are represented in the data -To return the number of characters in a data value -To return the unique values from a column.

To return the unique values from a column

Write a statement to update the middle name of the customer if that value was not provided -UPDATE Customer SET Middlename = 'NONE' WHERE Middlename = NULL; -UPDATE Customer SET Middlename = 'NONE' WHERE Middlename is NULL; -UPDATE Customer SET Middlename = 'NULL'; -UPDATE Customer SET Middlename = 'NONE' where Middlename <> NULL;

UPDATE Customer SET Middlename = 'NONE' WHERE Middlename is NULL;

Select the WHERE clause that returns all records with the text "priority" in the Comment column. -WHERE Comment LIKE '%priority%' -WHERE Comment LIKE 'priority%' -WHERE Comment LIKE 'priority' -WHERE Comment LIKE '%priority'

WHERE Comment LIKE '%priority%'

Is there a potential issues with this query running on a national reservation system? UPDATE Reservations SET Airline= 'Fast Wings' WHERE Destination= 'Chicago' ; -Yes, the query's syntax is incorrect -Yes, the query may update wrong records -No, this query appears to be perfect in all respects -Yes, the field name specified in the SET clause must be the same in the WHERE clause.

Yes, the query may update wrong records

Is it possible to group by two fields, and if so what would be the correct syntax? -Yes, using this syntax: GROUP BY field 1 GROUP BY field 2 -No, applying multiple grouping in the same statement is not allowed -Yes, using this syntax: GROUP BY Field 1, Field 2 -Yes, multiple grouping is allowed, but only if the grouped fields have the same data type.

Yes, using this syntax: GROUP BY Field 1, Field 2