SQL

Which of the following SQL statements will select all records with all their columns from a table called Sales.

SELECT * FROM Sales

Which of the following SQL statements has correct syntax? 1.) SELECT * FROM Table1 WHERE Column1 = = 10 2.) SELECT * FROM Table1 WHERE Column1 >= 10 3.) SELECT * FROM Table1 WHERE Column1 => 10

SELECT * FROM Table1 WHERE Column1 >= 10

UPPER(), ABS(), CONCAT() are examples of

Scalar functions which take in some input and return a single value

The ON DELETE clause is used to

Specify behavior to take when a referenced row is deleted

SQL stands for:

Structured Query Language

A join can be used to combine information from two separate tables

TRUE

Use the AS keyword to specify an alias

TRUE

What is the purpose of the SQL AS clause?

The AS SQL clause is used change the name of a column in the result set or to assign a name to a derived column.

Denormalization is

The process of adding redundancy to a database

Constraints are used to specify rules for data in a table.

True

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

True

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

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

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

True

The INNER JOIN is used in order to return all rows and selected columns which satisfy a certain condition.

True

The LIKE operator supports % as a wildcard character.

True

The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true

True

The Primary Key and Foreign Key constraints allow for relationships to be created between tables.

True

True or False: A Statement has no innate protection against SQL injection

True

Which command is used to remove all data from a table but keep its structure?

Truncate. Technically delete as well, but you need the where clause as well. Truncate is the easier, preferred way

What is the difference between UNION and UNION ALL?

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

What is required of a primary key?

Unique Value, Not NULL

The UNIQUE constraint requires that values must be

Unique in the column

The SQL UPDATE statement can...

Update multiple rows at a time

Constraints

Used to restrict values that can be placed in a column

In a prepared statement, data is replaced with

Variable names

What is the difference between WHERE and HAVING?

WHERE clause is used while fetching data. HAVING clause is used later to filter grouped data

What is not a use of the WHERE clause?

WHERE locates which database/schema/table where a row is located. WHERE is used for: WHERE can be used with UPDATE statements, WHERE can be used with DELETE statements, WHERE places a restriction on the rows returned by a query

What is the ResultSet?

an interface

Which one of these does not represent an aggregate function? 1.) Combine 2.) Count 3.) Max 4.) Avg

Combine

CRUD stands for:

Create, Read, Update, Delete

What does the CREATE TABLE statement do?

Creates a new database table

Transaction Control Statements (TCL) manages changes made by which type of statements?

DML

How do you drop a table?

DROP TABLE table_name

A primary key is

Implicitely UNIQUE, Implicitely NOT NULL, A value used to uniquely identify a row

What are the different events in Triggers?

Insert, Update, Delete

Which of the following clauses are added to sort the results of a SELECT statement?

ORDER BY

What is the difference between Order By and Group By?

ORDER BY is used for sorting results, whereas GROUP BY is used with aggregate functions to group results

Which constraint is used to uniquely identify each row?

Primary key

Normalization

Process of structuring a relational database in order to reduce data redundancy and improve data integrity

The CHECK constraint is used to

Require any values placed in a column to satisfy a logical expression

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

What do we call a primary key that consists of multiple columns in a table?

composite key

What is SQL Injection?

which malicious SQL statements are inserted into an entry field for execution

The key, the whole key, nothing but the key

1NF, 2NF, 3NF

What symbol is use to represent everything when selected from a table.

*

A foreign key is

A constraint which defines that a column references a primary key in another table or row

Which relationship(s) would be described as a many to many relationship?

A student and course, A student and a professor

Which of the following is an aggregate function?

AVG()

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

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

A constraint is applied to a row and not a column

False

A subquery is a SQL query that inserts a record into a table.

False

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

False

The Check constraint ensures each row for that column must have a unique value.

False

DDL Statements include commands like INSERT, UPDATE, and DELETE?

False, that is DML

DML statements include commands like CREATE, DROP, and ALTER

False, those commands are for DDL

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

Foreign Key

Referential integrity stipulates

Foreign keys must always reference a valid unique key. A PK may reference a Non-Primary Key but only if the Non-Primary Key is unique. Any PK is automatically unique

Which SQL statement is used to delete data from a database?

delete

The SQL keyword BETWEEN is used:

for ranges.

Which of the following joins will return all records from both tables despite any condition specified?

full join

Which of the following joins is best used to return only records that meet a condition?

inner join

Which SQL statement is used to insert new data in a database?

insert into

Which of the following joins will return all records from the first table despite any condition specified?

left join

What is a Prepared Statement used for?

to speed-up processing time for repetitive queries

What SQL statement is used to update data in a database?

update