Complete this assessment to review your understanding of the following modules
- -Introducing Database Concepts
- -Relational Concepts
- -Creating Databases and Database Objects
- -Using DML Statements
- -SQL Server Administration Fundamentals
- -Introducing Transact-SQL
- -Querying Tables with Select
- -Querying Multiple Tables with Joins and Using Set Operators
- -Using Functions and Aggregating Data
- -Working with Subqueries, Table Expressions, and Data
- -Modifying Data and Using T-SQL in Programming
- -T-SQL – Error Handling and Transactions
To effectively use this website to answer your alison questions with ease, checkout this short tutorial I put together to help you in the video below.
1. ______ functions operate on elements from a single row as inputs and return a single value. Fill in the blank.
2. Temporary tables are used to hold temporary results. They are created with a ___ prefix. Drag the correct answer into the box.
3. What data type provides support for international characters such as the Chinese or Russian alphabet? Choose one.
4. True or False: You can specify your order of query results to be ascending or descending using the ASC or DESC. The default order is the order the data in your table is in.
5. Match the term with its correct function.
Combines two Boolean expressions and returns TRUE if either is TRUE -> OR
Combines two Boolean expressions and returns TRUE one when both are TRUE -> AND
Specifies and inclusive range to test -> BETWEEN
6. Database are stored on database servers which are dedicated ________ or virtual servers that host the database files. Fill in the blank.
7. ______ queries return only distinct rows that appear in the first table but not the second table. Fill in the blank.
8. For the _______ form of normalization if the contents of a group of fields apply to more that a single record, ensure those fields are in the table. Drag the correct answer into the box.
9. True or False: When unpivoting data you will get back all the data you pivoted, even columns not on the pivoted table.
10. There are MIN, MAX, AVG and ______ aggregate functions. Fill in the blank.
11. True or False – Unique constraint allows you to enforce uniqueness in columns other than the primary key.
12. DBMS stands for what? Choose one.
Database Manipulation System
Database Master System
Database Management System
14. A ___________ backup contains all the data that has change since the last backup of the same type. Drag the correct answer into the box.
15. GUI stands for what? Choose one.
Graphical User Interface
Graphical User Interaction
Graphical User Incorporation
16. A ___________ is a group of task that together form a single unit of work. Fill in the blank.
17. Which of these are true about user-defined functions? Choose three.
Can JOIN user-defined functions
Cannot JOIN stored procedures
Can be called from within another SQL statement
Can use nondeterministic functions
Cannot be used to modify SQL Server configuration
18. Which of these is a column or combination of columns that are used to establish a link between data in two tables. ? Choose one.
19. Which SELECT statement will return the ids and names of the employees that work in engineering and design? Choose one.
SELECT First Name Last Name Id Num Address FROM Employees WHERE JobTitle = ‘Engineer’ or JobTitle = ‘Designer’
SELECT FirstName, LastName, IdNum FROM Employees WHERE JobTitle = ‘Engineer’ or JobTitle = ‘Designer’
SELECT * FROM Employees WHERE JobTitle = ‘Engineer’ and JobTitle = ‘Designer’
20. ________ is used to check if a predicate is TRUE to perform a task, otherwise perform a different task. Drag the correct answer into the box.
21. Match the data types with their category.
Other -> xml
string -> varchar
Exact numeric -> int
Approximate numeric -> float
22. When using UPDATE, you must specify with a _________ clause or all rows will be affected. Fill in the blank.
23. Which of these commands clears the entire table? Choose one.
24. Which type of join creates Cartesian products on purpose? Choose one.
25. Match the operator to its description.
Provides shortcut for defining grouping sets in which all possible combinations of grouping sets -> CUBE
Provides shortcut for defining grouping sets with combinations that assume input columns form a hierarchy -> ROLLUP
26. While _________ are not essential for your code to work as they do not affect it, they are important for describing your code. Fill in the blank.
27. Your primary key on one table can be a _______ key on another table. Fill in the blank.
28. _______ conversion allows compatible data types to be automatically converted. Fill in the blank.
29. A primary key must be unique but sometimes it may not be possible to make it unique using one column, so you use multiple columns instead. This is called a ___________ key.”Fill in the blank.
30. GROUPING SETS subclause builds on the ________ clause. Drag the correct answer into the box.
31. ___ allows you to limit the number or percentage of rows returned by a query. Fill in the blank.
32. True or False – Foreign key constraint allows you to establish a link between data in tow tables, but you can only use a single column.
33. A view is a ________ table consisting of different columns form one or more tables. Fill in the blank.
34. The _______ user account is included in every database and is used by any user who accesses the database but does not have a user account within the database. Type the correct answer into the text box. Fill in the blank.
35. The _____________ clause provides a search condition that each group must satisfy. Fill in the blank.
36. If you want to generate identity for two different tables, with neither having the same identifier you use _________. Fill in the blank.
37. When using the DELETE statement, you must use the _____ clause or all rows will be deleted. Fill in the blank.
38. When using the UPDATE and DELETE statements if you don’t put in a ___________ clause it causes all the information in the table to be updated or deleted. Fill in the blank.
39. True or False – A primary key can contain a null value.
40. ________ data is rotating data from a rows-based orientation to a column-based orientation. Fill in the blank.
41. What does DML stand for? Choose one.
Data Management Language
Data Manipulation Language
Data Method Language
42. Naming conventions for your code and objects is considered good practice one of the most common types is _________ where the first letter of the identifier and the first letter or every subsequent concatenated word is capitalized. Drag the correct answer into the box.
43. When using JOINs there are two places you can place your join, either in the WHERE and FROM clauses. Which is the better method? Choose one.
44. The first normal form means the data is in an entity format which means which of the following conditions have been met? Choose three.
Ensure repeating groups have individual tables
Create separate table for each set of related data
Eliminate repeating groups in individual tables
Identify each set of related data with primary key
Identify each set of related data with foreign key
45. The ______ clause allows you to combine related data from multiple tables as one result set. Type the correct answer into the text box.
46. _____ JOINS return all rows from the left table with all rows from the right table. Drag the correct answer into the box.
47. Which of these apply when creating a derived table?
The table have the same names for all columns they have in their original tables
The table must use an order by clause
The table must have an alias
The table must not be referred to multiple times in the same query
48. Which of these is an example of RDBMS? Choose one.
49. What character do you use for commenting a single line of code? Choose one.
50. Match the form of descriptions to the terms.
An organized collection of data, typically stored in electronic format. -> Database
A collection of applications that allows users and other programs to capture and analyze data by providing you with the -> Relational Database Management System
A software system designed to allow the definition, creation, querying and updating of data stored in relational database -> Database Management System
51. _________ functions would include COUNT, AVG, MAX, MIN, SUM. Fill in the blank.
52. ____ represents a missing or unknown value. Fill in the blank.
53. ______________ is the process of organizing data in a database that include establishing relationships between the tables. Fill in the blank.
54. When using the SELECT statement to retrieve all columns what symbol do you use instead of manually typing out all fields to return all? Choose one.
55. The __ predicate determines whether a specified value match any value in a subquery or list. Fill in the blank.
56. In the three-tiered approach to accessing content in a database, the last tier for accessing content is called __________. Fill in the blank.
57. The __________ function returns the item at the specified index from a list of values. Fill in the blank.
58. SQL stands for what? Choose one.
Software Query Language
Secure Query Language
Structured Query Language
System Query Language
59. _______ functions are applied to a window, or set of rows, including ranking, offset, aggregate and distribution functions. Fill in the blank.
60. What does CTE stand for? Choose one.
Command Table Extensions
Command Table Expressions
Common Table Expressions
Common Table Extensions
61. Select the DELETE statement that will delete products that have taken longer than 20 hours to make, but have also sold nothing. Choose one
DELETE FROM productProduction WHERE productionTime > 20 AND Sales =0;
DELETE FROM productProduction WHERE productionTime > 20 OR Sales =0;
62. You can insert the results returned by the query or stored procedure into an existing table using ______ _______. Drag the correct answer into the box.
63. A _______ INSERT can be used to import a data file into a table with a user-specified format. Fill in the blank.
64. When using the LIKE predicate what is the symbol for wildcards? Choose one.
65. A database user is a database level security principal that must be mapped to a _____ at the server level in order for the user to connect to the database. Fill in the blank.
66. True or False: An Outer Join keeps all the items from the inner table and then filters what does not match on the outer table.
67. A TRY ________ block involves trying a bit of code that may cause an error. Fill in the blank.