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