SQL Essential Training
Which statement is true about selecting columns in SQL?
Column names must be separated by commas.
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.
What does CRUD stand for?
Create Read Update Delete
To insert a blank row use the _____ clause.
DEFAULT VALUES
What clause is used to insert a blank row?
DEFAULT VALUES
The _____ statement is used to remove rows from a table.
DELETE
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';
Which code deletes row 10 from the table sample?
DELETE FROM sample WHERE id=10;
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
What statement is used to delete a table?
DROP TABLE
Which code will delete the table, cars, from a database?
DROP TABLE cars;
Clauses
FROM, WHERE, etc.
T/F: The NULL value is equivalent to a zero.
False
T/F: The SUBSTR() function is standard SQL
False
T/F: a subselect is performed with the SUBSELECT statement
False
T/F: numeric types are standardized across platforms.
False
T/F: the NULL value is equivalent to a zero
False
T/F: the SQL standard provides standardized date and time functions
False
What are databases for?
Organizing and making data available in different forms
Which SQL function is used to round a numeric number?
ROUND ()
Which statement is true about removing rows from a table?
Remove existing rows from a table with the DELETE FROM statement.
What is the purpose of a foreign key?
Foreign keys refer to the primary key in another table to link the two tables together.
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 ___ statement is used to add a row to a table
INSERT
The _____ statement is used to add a row to a table.
INSERT
How can a row of data be inserted into a table named Animals?
INSERT INTO Animals (Name, Age, Type) VALUES ('Fido', 10, 'Dog');
What is the correct code to add a row with values for name, systolic, and diastolic to the patient table?
INSERT INTO patient (name, systolic, diastolic) values ('Lily Parker', 115, 72);
You can undo an unfinished transaction by using the ___ statement.
Rollback
A LEFT JOIN includes rows that match the JOIN criteria as well as non-matching rows from the _____ table.
LEFT
Which SQL statement allows you to retrieve data from a SQL database?
SELECT
Which Boolean operator would filter a list of names to match only those containing "r" as the third letter?
Names LIKE '__r%'
Which Boolean operator would filter a list of names to match only those containing 'r' as the third letter?
Names LIKE '__r%'
What is an example of a query to get only data containing the word "Apple"?
SELECT * FROM Fruits WHERE Name = 'Apple';
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;
How do you return the rows with null values in the age column in the patient table?
SELECT * FROM patient WHERE age IS NULL;
How could you quickly calculate the average value of the data in a column called "kernels" in a table called "Corn"?
SELECT AVG(kernels) FROM Corn;
What is the best way to return the number of rows in the student table?
SELECT COUNT (*) FROM student;
Which code returns the CustomerName column in descending order in the eshop table?
SELECT CustomerName from eshop ORDER BY CustomerName DESC;
What is an example of SQL syntax to add one day to the current day?
SELECT DATETIME ('now', '+1 day');
Which statement removes duplicates from a result set?
SELECT DISTINCT
How do you select the unique row values from the columns CustomerName and ShopLocation in the eshop table?
SELECT DISTINCT CustomerName, ShopLocation FROM eshop;
What is the proper syntax for aliasing a field?
SELECT FirstName AS "First Name";
Which code displays all of the students taking the AP course 'APcalculus'?
SELECT FirstName, LastName FROM students WHERE APcourse = 'APcalculus';
What is the proper syntax to remove spaces from both the start and end of a field?
SELECT TRIM (' Hello ');
How can you determine the type of a value in SQL?
SELECT TYPEOF ()
Which code returns the columns midtermexam, finalexam, and gpa from the grade table?
SELECT midtermexam, finalexam, gpa FROM Grade;
What is the result of an inner join?
The results will include the rows from both tables where the join condition is met.
Which clauses allow you to get rows and columns from a table in the database?
The select statement retrieves data from the database by easily selecting the rows and columns from a table.
What is the result of the following statement: SELECT TRIM ('The test was a treat', 't');
The test was a trea
Why can the use of real data types cause difficulties?
They sacrifice precision for scale
What is the result of the following code? CREATE TABLE patient ( name TEXT, age INTEGER, height INTEGER, weight INTEGER, systolic INTEGER, diastolic INTEGER );
This code creates a table with six columns and no row entries.
Why is it important to convert all fields to the same case?
SQLite compares data based on case
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 LEFT JOIN includes rows that match the JOIN criteria as well as non-matching rows from which table?
The LEFT table
What are the three arguments for the SUBSTR() function in SQLite?
(string to evaluate, starting position, length of string)
In standard SQL, which value is interpreted as false?
0
If you use UPDATE and SET without a WHERE clause, what happens?
The entire table is updated
What is the result of SELECT 30/7, 30 % 7?
4 | 2
What is the difference between a WHERE clause and a HAVING clause?
A HAVING clause operates on aggregated data A WHERE clause operates on unaggregated data
Foreign Keys
A column or group of columns used to represent relationships. Values of the foreign key match values of the primary key in a different (foreign) table.
What does the WHERE clause requirement?
A logical expression ex: WHERE Countries = 'Europe;
Why is a junction table necessary when combining data between tables?
A relationship is provided between the tables so they can be linked
An SQL statement is terminated with___
A semicolon
Which statement is true about the id column in a SQL table?
A table can only have one id column.
The SELECT statement is used for___
All queries
How many tables can a joined query include?
Any number of tables
The type of trigger to use for preventing updates to reconciled rows is...
BEFORE UPDATE
What is the SQL command used to make a new table?
CREATE TABLE <table_name>
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);
How do you create a table and insert rows?
CREATE TABLE test ( a INTEGER, b TEXT ); INSERT INTO test VALUES ( 1, 'two' ); SELECT * FROM test;
What is the correct format for creating an ID column for the Vehicles table in SQLite?
CREATE TABLE vehicles (VehicleID INTEGER PRIMARY KEY);
What is the correct format for creating an ID column for the Vehicles table?
CREATE TABLE vehicles (VehicleID INTEGER PRIMARY KEY);
To save a query as a view, use the ___ statement
CREATE VIEW
Why would you use a trigger?
To automatically execute a group of statements whenever a specified event occurs
Why is aggregate data used?
To derive information from more than one row at a time
Why would you use column constrainsts?
To make sure that values obey desired rules or behaviors
T/F: A JOINed query can be used as a data source in a subselect
True
T/F: A joined query operates on columns from two or more tables in one query.
True
T/F: Column constraints are applied when the table is created.
True
T/F: Every database management system uses its own syntax for ID columns.
True
T/F: SQL Dates and times are express in the UTC time zone
True
T/F: The ORDER BY clause can use multiple columns for ordering criteria.
True
T/F: The WHERE clause is used with UPDATE to determine which rows are updated.
True
T/F: You can query columns in any order.
True
T/F: You can use the COUNT(*) function to count all the rows in a table.
True
T/F: a VIEW is like a stored subselect
True
T/F: a joined query operates on columns from two or more tables in one query.
True
T/F: a transaction can drastically improve the performance of a set of write operations
True
T/F: aggregate functions operate on groups of rows
True
T/F: column constraints are applied when the table is created
True
T/F: every database management system uses its own syntax for ID columns.
True
T/F: the ORDER BY clause can use multiple columns for ordering criteria.
True
T/F: the UPPER() and LOWER() functions can be used to fold the case of a string for sorting and comparisons.
True
T/F: there WHERE clause is used with UPDATE to determine which rows are updated
True
T/F: transactions ensure that a number of statements are performed as a unit
True
T/F: triggers are widely implemented, but they're often implemented differently
True
T/F: you can query columns in any order
True
T/F: you can use the COUNT(*) function to count all the rows in a table
True
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;
What clause correctly tests for a NULL value in a column?
WHERE a IS NULL
Which clause correctly tests for a NULL value in column a?
WHERE a IS NULL
When should you create a view from a SELECT statement?
When you want to modify the existing displayed data
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.
An SQL statement is terminated with _____.
a semicolon
A joined query can include _____ tables.
any number of
How do you specify the columns to be selected?
by name or column heading
In the statement SELECT * FROM Customers WHERE Age > 50;, what is the formal name for FROM Customers?
clause
In the statement: SELECT * FROM Customers WHERE Age > 50; what is the formal name for "FROM Customers"?
clause, it modifies the verb 'SELECT'
How do you execute a query?
click F9
The CREATE TABLE statement _____.
defines the table columns defines the table schema creates a table
What do you call reverse alphabetical order in SQL jargon?
descending
What do you call reverse alphabetical order in SQL?
descending
T/F: the LENGTH() function is standard in SQL
false
T/F: the epxression 17/5 will return the real value 3.4
false
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
The essence of subselection is___
nested select statements, the output from one selection is used as the input to another
The ROUND() Function will...
round a numeric value to a number of decimal places
Standard SQL uses what to delimit a literal string?
single quotes ' '
What clause is used to delete a row?
the WHERE clause with the DELETE statement
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
Statement
the unit of execution ex: SELECT ** FROM Countries WHERE Continent = 'Europe';
How do transactions improve performance?
they speed up execution by making reading and writing more efficient
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, it's often used as a wildcard
What is the WHERE clause used for?
to filter query results by row
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
What is the function of an AS clause?
to name a column
What is the function of the AS clause?
to name a column
Why would you use the SUBST() function with only two arguments?
to retrieve a substring from a specified starting point to the end of the string
T/F: SQL provides a conditional expression that allows different results based on a logical value.
true
Two tables are defined as up: id tt 0 t0 1 t1 and down: id tb 1 b1 2 b2 What is the result of the following query: SELECT up.tt, down.tb FROM up JOIN down ON up.id=down.id
tt tb t1 b1
How can the length of a field be found using SQLite
use the LENGTH() function
What is the standard way to concatenate two strings?
using the concatenation operator ||
Which format conforms to the SQL standard for datetime?
year-month-day hour:minute:second
The type of trigger to use for adding timestamps is...
AFTER INSERT
The _____ is used to change a table schema.
ALTER
What is used to change a table schema?
ALTER
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.
The ___ clause is used with SELECT to filter rows.
WHERE
The _____ clause is used with SELECT to filter rows.
WHERE
To delete a row use the DELETE statement with the _____ clause.
WHERE
The SELECT statement is used for _____.
all queries