SQL review

Use the _____ keyword to remove duplicates from a result.

DISTINCT

How can someone determine the type of a value in SQL?

How can someone determine the type of a value in SQL?

used to drop tables if name exists

IF EXISTS

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.

What clause combines multiple data sources in a single Presto query?

JOIN

How can someone run a SELECT query within another SELECT query as a subselect query?

SELECT * from (SELECT FirstName, LastName FROM People WHERE PersonID = '2000');

How would you quickly calculate the average value of the data in a column called "kernels" in a table called "Corn"?

SELECT AVG(kernels) FROM Corn

Which option is an example of SQL syntax to add one day to the current day?

SELECT DATETIME('now', '+1 day');

SELECT TRIM('The test was a treat','t');

The test was a trea

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?

Binary

Which conditional expression will trap errors in your SQL statement?

Try

Which date function will aggregate a date value to the month level?

date_trunc

In T-SQL, what date function will return the last day of a given month?

eomonth

Which SELECT clause can you use to test if the outer queries value is present in the sub-query

exists

Which function will convert a string to a JSON object?

json_parse

Which function will let you combine maps?

map_concat

Standard SQL uses _____ to delimit a literal string.

single quotes

What is the standard way to concatenate two strings?

using the concatenation operator | |

Which type of join returns only the matches for items that are in both tables?

Inner Join

Which SQL clause will you add to this query in order to associate the "Phone" field with the "Number1" field in the "Contacts" table? SELECT * FROM Customers

JOIN Contacts ON Customers.Phone=Contacts.Number1

Transactions ensure that a number of statements are performed as a unit.

TRUE

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

Why would you use the SUBSTR() function with only two arguments?

to retrieve a substring from a specified starting point to the end of the string

Which clause do you use to create a common table expression in T-SQL?

with

What do you call reverse alphabetical order in SQL jargon?

descending

The SUBSTR() function is standard SQL

false

The WHERE clause is used to

filter query results by row

Use the TYPEOF() function to

find the type of an expression

What would be the result for the duration of Sanctuary in the following table if the durations were created with m || ':' || s AS duration? 5:05

5:5

The type of trigger to use for adding timestamps is

AFTER INSERT

The _____ is used to change a table schema.

ALTER

What is the correct syntax when using a transaction in SQL?

BEGIN TRANSACTION; INSERT INTO customers ('Susan', "123 Hillside Rd', 2000); END TRANSACTION;

Which trigger would prevent updating rows 1 and 3 in the following table named wSales?

CREATE TRIGGER uWS BEFORE UPDATE on wSales BEGIN SELECT RAISE(ROLLBACK, 'cannot update') FROM wSales WHERE id = NEW.id AND reconcil = 0; END ;

How can we create a view that joins all the data from two tables where the IDs are equal?

CREATE VIEW customersSale AS SELECT *.customers, *.sales FROM customers AS c JOIN sales AS s on c.id = s.id;

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 'XL' and Price is 10.

What is the correct syntax for deleting only rows in a table named Vehicles where the Name equals "Ford"?

DELETE FROM Vehicles WHERE Name='Ford';

Which SQL keyword is used to list data in largest to smallest order?

DESC

The SELECT _____ statement removes duplicates from a result set.

DISTINCT

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 Customer LEFT JOIN CustomerDetail ON Customer.Id = CustomerDetail.CustomerId;

You are trying to tally the instances of unique entries in a column called "Chief" in a table called "Country" that contains duplicates, as in the column in the image shown below. How can you accomplish this?

SELECT COUNT(DISTINCT Chief) FROM Country

To sort a list of students in descending order by last name, you can use this statement:

SELECT FirstName, LastName FROM Student ORDER BY LastName DESC;

Which query will return the highest sale amount for every office?

SELECT MAX(Amount) FROM Sales GROUP BY Office;

This statement will replace the letter "d" in students' first names with a dash character:

SELECT REPLACE(first_name, 'd', '-') FROM Student;

What is the proper syntax to remove spaces from both the start and end of a field?

SELECT TRIM (' Hello ');

In order to get a list of participants who earned the maximum score on our quiz, we might write a query that looks like this:

SELECT first_name, last_name, quiz_points FROM people WHERE quiz_points=(SELECT MAX(quiz_points) FROM people);

Triggers are widely implemented, but they're often implemented differently.

TRUE

Why is it preferable to use a named global space than the default global space for variables?

The default space would get too crowded.

Used to specify SQL search

WHERE ORDER BY DESC

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

WHERE Comment LIKE '%priority%'

wildcard

WHERE Name LIKE '%island%'; '_a%' - used to find if a is the second letter

Which clause correctly tests for a NULL value in column a?

WHERE a IS NULL

Which clause allows you to store the results of a query inside an alias?

WITH

How can a constraint be placed on a table where a field will contain the value "Mickey" if nothing is provided?

CREATE TABLE People (FirstName TEXT DEFAULT 'Mickey', address TEXT, city, TEXT);

create a column for table WITH primary key called id

CREATE TABLE test ( id INTEGER PRIMARY KEY, a INTEGER b TEXT );

To save a query as a view, use the _____ statement.

CREATE VIEW

What is wrong with the following statement? CREATE VIEW tracKVieW AS SELECT id, album_id, track_number, duration / 60 AS m, duration % 60 AS s FROM track; END VIEW;

END VIEW will cause an error.

Which Boolean operator would filter a list of names to match only those containing "r" as the third letter?

Names LIKE '__r%'

Which type of join will only return results where the condition is true?

inner

A LEFT JOIN includes rows that match the JOIN criteria as well as non-matching rows from the _____ table.

left

The ROUND() function will _____.

round a numeric value to a number of decimal places

Besides the rank() function, what t-sql method can you use to return a ranking of results?

row_number

To calculate a running total, what range should you use in your OVER clause?

rows unbounded preceding

What are the three arguments for the SUBSTR() function in SQLite?

string to evaluate, starting position, length of string

To what does a table schema refer?

the number of, titles for, and data types in columns

Why is aggregate data used?

to derive information from more than one row at a time

Which of these non-relational databases can Presto query?

Apache Kafka

Why would you use transactions instead of individual instructions?

Why would you use transactions instead of individual instructions?

Which function will order the values inside an array?

array_sort

The type of trigger to use for preventing updates to reconciled rows is

BEFORE UPDATE

What is the result of SELECT 30/7, 30 % 7?

30/7 30%7 - 4 x 7 + 2 = 30 4 2

In standard SQL, which value is interpreted as false?

0

How should an alias be defined if it has a space in it?

"large deposits"

What is the difference between a WHERE clause and a HAVING clause?

A HAVING clause operates on aggregated data, and a WHERE clause operates on unaggregated data.

how to add a column to a table

ALTER TABLE test ADD e TEXT DEFAULT 'panda';

What keyword would you use in a SELECT statement to give a column a friendly name?

AS

What is the correct format for creating an ID column for the Vehicles table in SQLite?

CREATE TABLE vehicles (VehicleID INTEGER PRIMARY KEY);

Which component of Presto parses queries?

Coordinator

This is used to set a constant for null fields

DEFAULT ex CREATE TABLE test ( a TEXT, b TEXT, c TEXT DEFAULT 'panda' ); INSERT INTO test ( a, b ) VALUES ( 'one', 'two' );

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;

Use the _____ clause to collate groups of results before calling an aggregate function.

GROUP BY

Is it possible to group by two fields, and if so what would be the correct syntax?

GROUP BY Field1, Field2

You can undo an unfinished transaction by using the _____ statement.

ROLLBACK

Which grouping clause returns a hierarchical result grouping result set?

ROLLUP

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;

SUBSTR(Number, -4)

What is the best way to share a long SQL code snippet with another developer?

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

A VIEW is like a stored subselect.

TRUE

A transaction can drastically improve the performance of a set of write operations.

TRUE

Aggregate functions operate on groups of rows.

TRUE

SQL dates and times are expressed in the UTC time zone.

TRUE

What does the VARCHAR data type usually store?

The VARCHAR type is used for storing a variable number of characters.

Why can the use of real data types cause difficulties?

They sacrifice precision for scale.

How do transactions improve performance?

They speed up execution by making reading and writing more efficient.

Write a statement to update the middle name of the customer if that value was not provided.

UPDATE Customer SET MiddleName = 'NONE' WHERE MiddleName IS NULL;

What must you also include in a SELECT statement when performing an aggregation?

group by

Is there a potential issue with this query running on a national reservation system? UPDATE Reservations SET Airline='Fast Wings' WHERE Destination='Chicago';

Yes, the query may update wrong records.

How can you access the most recently changed id in a trigger acting on the following table?

by using NEW.id

The essence of subselection is

nested select statements

Which records will be shown when the query ends with LIMIT 10 OFFSET 5?

records 6 through 15

Microsoft Windows UNC

specifies a common syntax to describe the location of a network resource, such as a shared file, directory, or printer

Why is the preferred method for inserting data so involved?

to exclude inputs that would pose security risks

Two tables are defined as up id tt 0 t0 1 t1 and down id tb 1 b1 2 b2

tt tb t1 b1

Rubin

unwanted PUP

When should you create a view from a SELECT statement?

when you want to modify the existing displayed data

Which format conforms to the SQL standard for datetime?

year-month-day hour:minute:second - Largest unit on left