SQL & JDBC Quiz

What is the correct syntax to create a comment in SQL?

-- this is a comment

Which is used to execute stored procedures?

CallableStatement

Select the two lines of code required to make a database connection in JDBC.

Class.forName(driver class) DriverManager.getConnection(url, user, password)

CRUD stands for:

Create, Read, Update, Delete

Which constraint allows you to specify a default value (if none is supplied) when inserting new records into a table?

Default

TRUE OR FALSE: It is generally acceptable practice to code database credentials directly into the DriverManager.getConnection() parameters.

False

What do we call a field that references a field in another table?

Foreign Key

Constraints are used...

To restrict values that can be placed in a column

What does JDBC provide?

an API to communicate with a database

What package is Connection located?

java.sql

Which join will return all records from the first table despite any condition specified?

left join

A foreign key is

A constraint which defines that a column references a primary key in another table or row A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

Which command is used to change the structure of an existing table?

ALTER

Name an aggregate function.

AVG(), COUNT(), MAX(), MIN(), SUM()

MAX(), SUM(), and COUNT() are examples of

Aggregate functions (Aggregate functions work on a whole set of data to generate a value based on the whole set)

ACID stands for

Atomicity, Consistency, Isolation, Durability

Which command is used for defining new tables or databases?

CREATE

What is the syntax for removing only records from the Customers table that have a first_name starting with 'S'?

DELETE FROM Customers WHERE first_name LIKE 'S%';

Insert is a part of which SQL sublanguage?

DML (Insert is used to add data to the database or manipulate new data into the database)

Which command is used to remove a table from memory?

DROP

TRUE OR FALSE: A constraint is applied to a row and not a column.

False

TRUE OR FALSE: A subquery is a SQL query that inserts a record into a table.

False

TRUE OR FALSE: In a one-to-one relationship, you typically use a third table as an association or join table.

False

TRUE OR FALSE: DML Statements include commands like CREATE, DROP, and ALTER.

False, these are DDL Statements.

TRUE OR FALSE: DDL Statements include commands like INSERT, UPDATE, and DELETE.

False, these are DML Statements.

Which normalized form introduces the concept of enforcing primary keys and removing composite columns?

First (1NF)

Referential integrity stipulates

Foreign keys must always reference a valid unique key

What is the syntax for adding a record to the Customers table?

INSERT INTO Customers VALUES (null, null, null);

The method next() does what?

It is used to iterate through a ResultSet.

In JDBC, a Statement is unsafe because

It is vulnerable to SQL injection attacks

What does JDBC stand for?

Java Database Connectivity

Which clause is added to sort the results of a SELECT statement?

ORDER BY

In a prepared statement, data is replaced with

Question marks

The __________ transaction isolation level addresses the dirty read concurrency issue.

Read Committed

What is the default transaction isolation level in Oracle SQL?

Read Committed

The __________ transaction isolation level addresses none of the concurrency issues.

Read Uncommitted

The __________ transaction isolation level addresses the unrepeatable read concurrency issue.

Repeatable Read

What JDBC object is used to reference the rows returned by a query?

ResultSet

What is the syntax for retrieving all columns and all rows from a table called "Customers"?

SELECT * FROM Customers

JDBC statements throw which exception(s)?

SQLException

What is the difference between scalar functions and aggregate functions?

Scalar functions return a single value, and aggregate functions return a single value, calculated from values in a column

The __________ transaction isolation level addresses the phantom read concurrency issue.

Serializable

The ON DELETE clause is used to

Specify behavior to take when a referenced row is deleted

Which of the following can easily allow SQL injection? -CallableStatement -Statement -PreparedStatement -SimpleStatement

Statement

SQL stands for:

Structured Query Language

Which subset of SQL is used to manage transactions within a database?

TCL

In SQL, NULL values represent...

The lack of a value for a column

Denormalization is

The process of adding redundancy to a database. The process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields.

TRUE OR FALSE: A database is comprised of tables which are comprised of rows and columns.

True

TRUE OR FALSE: A join can be used to combine information from two separate tables

True

TRUE OR FALSE: A primary key is used to uniquely identify a particular entry or row in a table.

True

TRUE OR FALSE: DDL is a subset of SQL that deals with data creation and includes commands like CREATE

True

TRUE OR FALSE: In a many-to-many relationship, you typically use a third table to manage the actual associations between rows in one table and another.

True

TRUE OR FALSE: In a one-to-many relationship, rows in one table can refer to multiple rows in another, but that other table can only refer to at most one row in the former table.

True

TRUE OR FALSE: In a one-to-one relationship, a record in one table can at most refer to one record in another table.

True

TRUE OR FALSE: Stored Procedures can be called with CallableStatement.

True

TRUE OR FALSE: The IN operator is used to evaluate a condition against a range of values.

True

TRUE OR FALSE: The LIKE operator supports % as a wildcard character.

True

What is the difference between UNION and UNION ALL?

UNION returns only distinct rows, while UNION ALL returns all rows

Which isolation level provides maximum concurrency?

Uncommitted Read

Which constraint forces a unique value for each row (on the specified column)?

Unique

What is required of a primary key? (Select all that apply) -Unique Value -Integer Data Type -Not Null

Unique Value and Not Null

The UNIQUE constraint requires that values must be

Unique in the column

The SQL UPDATE statement can do which of the following? -Update only one row at a time -Update multiple rows at a time -Delete a row based on a WHERE clause -Update the current system time of the database

Update multiple rows at a time

A sequence is

Used to track sequence values

What is SQL injection?

User enters SQL statement into a form instead of a name or other data. Accepted code becomes part of database commands issued. Improper data disclosure, data damage and loss possible. Well-designed applications make injections ineffective.

Which clause is used to filter the results of a SELECT, UPDATE, or DELETE statement?

WHERE

Which is the best join to use to return only records that meet a condition?

inner join

What is the next step after registering the JDBC driver?

obtain a Connection object

What is the difference between WHERE and HAVING?

WHERE filters data prior to aggregation, HAVING filters data after aggregation. HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. WHERE cannot be used to apply restrictions to an aggregate function.

Which of the following is not a use of the WHERE clause? -WHERE can be used with UPDATE statements -WHERE can be used with DELETE statements -WHERE locates which database/schema/table where a row is located -WHERE places a restriction on the rows returned by a query

WHERE locates which database/schema/table where a row is located