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