SQL
In the statement SELECT * FROM Customers WHERE Age > 50;, what is the formal name for FROM Customers?
- Clause - It modifies the verb SELECT
In standard SQL, which value is interpreted as false?
0
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.
Why is a junction table necessary when combining data between tables?
A relationship is provided between the tables so they can be linked.
Which statement is true about the id column in a SQL table?
A table can only have one id column.
The type of trigger to use for adding timestamps is _____.
AFTER INSERT
Which constraint ensures a column cannot have any duplicate values including null values?
UNIQUE NOT NULL
Which code is the correct way to update the GPA for the student in row 7 from the student table?
UPDATE student SET GPA=3.5 WHERE id=7;
The _____ clause is used with SELECT to filter rows.
WHERE
To delete a row use the DELETE statement with the _____ clause.
WHERE
Which clause correctly tests for a NULL value in column a?
WHERE a IS NULL - NULL is a state or a lack of a value.
To insert a blank row use the _____ clause.
DEFAULT VALUES
The _____ statement is used to remove rows from a table.
DELETE
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';
What is the correct code to remove row 3 from the student table?
DELETE FROM student WHERE id=3;
Which SQL keyword is used to list data in largest to smallest order?
DESC
The SELECT _____ statement removes duplicates from a result set.
DISTINCT
Use the _____ keyword to remove duplicates from a result.
DISTINCT
To delete a table, use the _____ statement.
DROP TABLE
Which statement is true about inserting data to a table?
You must execute the select statement after the insert into statement to see the result with the added row.
What is a complex view?
a view that is created from more than one table
A joined query can include _____ tables.
any number of
How do you specify the columns to be selected?
by name or column heading
What does the CRUD operations acronym stand for?
create, read, update, delete
What do you call reverse alphabetical order in SQL jargon?
descending
The WHERE clause is used to _____.
filter query results by row
Use the TYPEOF() function to _____.
find the type of an expression
What type of relationship requires the use of a junction table?
many-to-many
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
What is a PRIMARY KEY?
the unique key for a table when exposed as a column
Why would you use a trigger?
to automatically execute a group of statements whenever a specified event occurs
Why is it preferable to use a named global space than the default global space for variables?
to avoid collisions and clutter
What is the purpose of the JOIN clause?
to combine results from related rows in multiple tables
In the statement COUNT(*), what does the * mean?
to consider all rows
Why is aggregate data used?
to derive information from more than one row at a time
Why would you use transactions instead of individual instructions?
to maintain data integrity and improve performance
Why would you use column constraints?
to make sure that values obey desired rules or behaviors - You can specify default values or checks on values using constraints.
What is the function of an AS clause?
to name a column
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
When should you create a view from a SELECT statement?
when you want to modify the existing displayed data
Numeric types are standardized across platforms.
FALSE
The LENGTH() function is standard SQL.
FALSE
The NULL value is equivalent to a zero.
FALSE
What is the purpose of a foreign key?
Foreign keys refer to the primary key in another table to link the two tables together. They define the relationship between two tables.
Use the _____ clause to collate groups of results before calling an aggregate function.
GROUP BY
Which statement is not an SQL keyword used in the CASE statement?
IF - The CASE statement is similar to an IF-THEN-ELSE statement, but it uses WHEN instead of IF.
The _____ statement is used to add a row to a table.
INSERT
How are transactions handled within a database?
If any of the operations fail, then the entire group of operations fail.
What is returned by SUBSTR('Longs Peak', 1, 5);?
Longs
How do you add a new column and its data type to an existing table?
Once a table has been created, use the ALTER TABLE statement to add a new column and its data type.
You can undo an unfinished transaction by using the _____ statement.
ROLLBACK
Which SQL function is used to round a numeric number?
ROUND
Which clause allows you to get rows and columns from a table in the database?
SELECT
Which choice is the best way to represent a string?
SELECT 'Karen''s Classroom';
What is an example of a query to get only data containing the word "Apple"?
SELECT * FROM Fruits WHERE Name = 'Apple';
Which code returns the first 10 students who have a 4.0 GPA from the Grade table ordered alphabetically?
SELECT * FROM Grade WHERE GPA=4.0 ORDER BY Name LIMIT 10;
What is the proper syntax to remove spaces from both the start and end of a field?
SELECT TRIM (' Hello ');
Why is it important to convert all fields to the same case?
SQLite compares data based on case.
What is the purpose of using question marks for the values in the insert and update functions?
The question marks are place holders until the data can be retrieved from the execute function.
What is the result of an inner join?
The results will include the rows from both tables where the join condition is met.
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.
Which code is used to return the shop locations and customers who shopped in cities that start with the letters "san" in California?
This code ensures that only the shop locations that begin with the letters "san" are returned.
What is the correct format for creating an ID column for the Vehicles table in SQLite?
CREATE TABLE vehicles (VehicleID INTEGER PRIMARY KEY);
Column names must be separated by...
Commas
The type of trigger to use for preventing updates to reconciled rows is _____.
BEFORE UPDATE
Why do you need to be careful and consult documentation when altering an existing table?
Syntax capabilities vary widely from one implementation of SQL to another.
A JOINed query can be used as a data source in a subselect.
TRUE
A VIEW is like a stored subselect.
TRUE
A joined query operates on columns from two or more tables in one query.
TRUE
A transaction can drastically improve the performance of a set of write operations.
TRUE
Column constraints are applied when the table is created.
TRUE
Every database management system uses its own syntax for ID columns.
TRUE
SQL dates and times are expressed in the UTC time zone.
TRUE
SQL provides a conditional expression that allows different results based on a logical value.
TRUE
The ORDER BY clause can use multiple columns for ordering criteria.
TRUE
The UPPER() and LOWER() functions can be used to fold the case of a string for sorting and comparisons.
TRUE
Transactions ensure that a number of statements are performed as a unit.
TRUE
You can use the COUNT(*) function to count all the rows in a table.
TRUE
Which statement is true about the DROP TABLE statement?
The DROP TABLE statement enables you to delete a table from a database and if the table did not exist then attempting to DROP it would cause an error to occur.
If you use UPDATE and SET without a WHERE clause, what happens?
The entire table is updated. - Without a restriction, the statement applies to the whole table.