Modern Database Systems SQL Quiz
This Quiz is all about testing the basics skills in SQL. It covers SQL Server architecture, tools, security and datatypes, Implementing data integrity, subqueries, joins and T-SQL Enhancements.
Questions and Answers
- 1.
The primary - foreign key relations are used to
- A.
Cross-reference database tables
- B.
To index the database.
- C.
Clean-up the database
Correct Answer
A. Cross-reference database tablesExplanation
The primary-foreign key relations are used to cross-reference database tables. This means that the primary key of one table is used as a foreign key in another table to establish a relationship between the two tables. This allows for data consistency and integrity, as it ensures that any data referenced in one table exists in the related table. It also enables efficient querying and retrieval of data across multiple tables by using the foreign key to join the tables together.Rate this question:
-
- 2.
The UNION SQL clause can be used with
- A.
The DELETE and UPDATE clauses
- B.
The SELECT clause only
- C.
The UPDATE clause only
Correct Answer
B. The SELECT clause onlyExplanation
The correct answer is "the SELECT clause only" because the UNION SQL clause is used to combine the result sets of two or more SELECT statements into a single result set. It is not used with the DELETE or UPDATE clauses, which are used to modify data in a table. Therefore, the UNION clause can only be used with the SELECT clause.Rate this question:
-
- 3.
Can you join a table to itself?
- A.
YES
- B.
NO
Correct Answer
A. YESExplanation
Yes, you can join a table to itself. This is known as a self-join and it is used when you want to combine rows from the same table based on a related column. By using aliases to differentiate between the two instances of the table, you can create a join condition and retrieve the desired information. Self-joins are commonly used in scenarios where you have hierarchical data or when you need to compare records within the same table.Rate this question:
-
- 4.
What is the difference between the WHERE and HAVING SQL clauses?
- A.
The WHERE SQL clause condition(s) is applied to all rows in the result set before the HAVING clause is applied (if present). The HAVING clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group.
- B.
The HAVING SQL clause condition(s) is applied to all rows in the result set before the WHERE clause is applied (if present). The WHERE clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group
- C.
The Having clause is identical with the where clause
Correct Answer
A. The WHERE SQL clause condition(s) is applied to all rows in the result set before the HAVING clause is applied (if present). The HAVING clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group.Explanation
The WHERE clause is used to filter rows based on a specific condition before the HAVING clause is applied. It is used with SELECT SQL statements and specifies a search condition for individual rows. On the other hand, the HAVING clause is used to filter rows based on a specific condition after the WHERE clause. It is used with SELECT SQL statements and specifies a search condition for aggregate functions or groups.Rate this question:
-
- 5.
The difference between the DELETE and TRUNCATE SQL clauses is _________________
- A.
The TRUNCATE clause is identical to the DELETE clause
- B.
The DELETE clause deletes all rows in a database table, while the TRUNCATE clause can have a WHERE condition and might or might not delete all rows in a table.
- C.
The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table
Correct Answer
A. The TRUNCATE clause is identical to the DELETE clauseExplanation
The given answer states that the TRUNCATE clause is identical to the DELETE clause. This means that both clauses can be used to delete data from a database table. However, there may be some differences in their functionalities or syntax, which are not mentioned in the given options.Rate this question:
-
- 6.
What is the ABS SQL function used for?
- A.
To return the minimum value of a numeric expression.
- B.
To return the maximum value of a numeric expression
- C.
To return the absolute, positive value of a numeric expression.
- D.
To return the average value of a numeric expression.
Correct Answer
C. To return the absolute, positive value of a numeric expression.Explanation
The ABS SQL function is used to return the absolute, positive value of a numeric expression. This means that it will return the value without considering its sign, always returning a positive value.Rate this question:
-
- 7.
SSIS stands for
- A.
SQL Integration Services
- B.
SQL Integrity Services
- C.
SQL Information Services
Correct Answer
A. SQL Integration ServicesExplanation
SSIS stands for SQL Integration Services. It is a component of Microsoft SQL Server that is used for building enterprise-level data integration and data transformation solutions. SSIS provides a platform for extracting, transforming, and loading (ETL) data from various sources into data warehouses or other target systems. It offers a wide range of tools and features to automate the process of data integration, including data cleansing, data profiling, and data quality services. With SSIS, developers can create packages to perform complex data integration tasks and schedule them to run at specific times or events.Rate this question:
-
- 8.
SQL server Management Studio replaces
- A.
Enterprise Manager
- B.
Query Analyzer
- C.
Both Enterprise Manager and Query Analyzer
- D.
None
Correct Answer
C. Both Enterprise Manager and Query AnalyzerExplanation
SQL Server Management Studio (SSMS) replaces both Enterprise Manager and Query Analyzer. SSMS is a comprehensive integrated environment for managing SQL Server databases. It combines the functionalities of both Enterprise Manager and Query Analyzer into a single tool. With SSMS, users can perform tasks such as managing databases, creating and executing queries, designing tables and views, and monitoring server performance. It provides a more efficient and streamlined experience for database administrators and developers by consolidating the features of the previous tools into one unified interface.Rate this question:
-
- 9.
------------------------------ is used to browse servers and view Log files
- A.
Template Explorer
- B.
SQL server Profiler
- C.
Object Explorer
Correct Answer
C. Object ExplorerExplanation
Object Explorer is used to browse servers and view Log files. It provides a graphical interface to navigate and manage the objects within a SQL Server instance. It allows users to connect to different servers, explore server objects such as databases, tables, views, and also view log files for troubleshooting and monitoring purposes. With Object Explorer, users can easily browse and access the necessary information and logs related to the server and its objects.Rate this question:
-
- 10.
The command prompt utility used to copy data between SQL Server and User File is_____________
- A.
Bcputility
- B.
Dtautility
- C.
Osqlutility
- D.
Sqlcmdutility
Correct Answer
A. BcputilityExplanation
The correct answer is bcputility. Bcp utility is a command-line tool that allows for the import or export of data between SQL Server and user files. It can handle large volumes of data and offers various options for data format and transformation. This utility is commonly used for tasks such as bulk data loading, data migration, and data backups.Rate this question:
-
- 11.
The Master database in sql holds ------------------------ for SQL Server
- A.
Temporary objects
- B.
Templates
- C.
Initialization Info
Correct Answer
C. Initialization InfoExplanation
The Master database in SQL holds initialization information for SQL Server. This includes information about the server configuration, system databases, and other essential settings required for the proper functioning of SQL Server.Rate this question:
-
- 12.
The Integrity which makes sure that keyvalues are consistent across tables
- A.
Entity Integrity
- B.
Domain Integrity
- C.
Referential Integrity
- D.
User Defined Integrity
Correct Answer
C. Referential IntegrityExplanation
Referential Integrity ensures that the relationships between tables are maintained and that any foreign key values in one table correspond to valid primary key values in another table. It guarantees the consistency and accuracy of data by preventing the creation of orphaned records or invalid references. This ensures that key values are consistent across tables and maintains the integrity of the database.Rate this question:
-
- 13.
Select the Exact Numeric Datatypes in SQL( any 3)
- A.
Smallmoney
- B.
Image
- C.
Tinyint
- D.
Bigint
Correct Answer(s)
A. Smallmoney
C. Tinyint
D. BigintExplanation
The question asks for the exact numeric datatypes in SQL. The correct answer is smallmoney, tinyint, and bigint.
- smallmoney is a datatype that stores monetary values with a decimal point, with a range of -214,748.3648 to 214,748.3647.
- tinyint is a datatype that stores small integers with a range of 0 to 255.
- bigint is a datatype that stores large integers with a range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
These datatypes are used to store specific numeric values in SQL databases.Rate this question:
-
- 14.
All Integrity Categories Support User-Defined Integrity
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
This statement is true because all integrity categories, such as entity integrity, referential integrity, and domain integrity, can be defined and enforced by the user. User-defined integrity allows users to set rules and constraints on the data in a database to ensure its accuracy, consistency, and reliability. By defining these integrity categories, users can specify the conditions that must be met for the data to be considered valid, and the database management system will enforce these rules to maintain the integrity of the data.Rate this question:
-
- 15.
Primary Key Constraint Enforces ----------------------of the table
- A.
Referential Integrity
- B.
Entity Integrity
- C.
Domain Integrity
Correct Answer
B. Entity IntegrityExplanation
Entity Integrity is the correct answer because the primary key constraint enforces the uniqueness and non-nullability of the primary key attribute(s) in a table. It ensures that each row in the table can be uniquely identified and that no primary key value is null or duplicated. This constraint is essential for maintaining the integrity and consistency of the data within the table.Rate this question:
-
- 16.
A _______________constraint enforces referential Integrity
- A.
Primary key
- B.
Foreign key
- C.
Unique key
- D.
Check
Correct Answer
B. Foreign keyExplanation
A foreign key constraint enforces referential integrity by ensuring that values in a column of one table match the values in a primary key column of another table. This constraint helps maintain the consistency and accuracy of data between related tables in a database. By specifying a foreign key constraint, any attempt to insert or update data that violates the relationship between the tables will be rejected, preventing inconsistencies and maintaining data integrity.Rate this question:
-
- 17.
Forign Key Constraint Does not create indexes Automatically
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
Foreign key constraints do not automatically create indexes because they serve different purposes. A foreign key constraint is used to enforce referential integrity between two tables, ensuring that values in one table's foreign key column match the values in another table's primary key column. On the other hand, indexes are used to improve query performance by allowing faster data retrieval. While foreign key constraints can benefit from existing indexes, they are not automatically created because they serve different functionalities.Rate this question:
-
- 18.
Single Column can have multiple Check Constraints
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
A single column in a database table can indeed have multiple check constraints. Check constraints are used to enforce specific conditions on the values that can be stored in a column. By applying multiple check constraints to a single column, you can impose different conditions or rules on the acceptable values for that column. This allows for more flexibility and granularity in defining the data validation rules for the column.Rate this question:
-
- 19.
Only ___________ null value(s) can appear in a column with unique statement constraint
- A.
One
- B.
Two
- C.
Three
- D.
Zero
Correct Answer
A. OneExplanation
A column with a unique statement constraint means that each value in the column must be unique and cannot be repeated. Therefore, if there is a null value in the column, it would violate the unique constraint because null values are not considered equal to each other. So, the only null value that can appear in a column with a unique statement constraint is one.Rate this question:
-
- 20.
A check Constraint cannot be placed on columns with text,ntext,___________ and ______________ datatypes
- A.
Image
- B.
Varbinary
- C.
Rowversion
- D.
Binary
Correct Answer(s)
A. Image
C. RowversionExplanation
A check constraint is a rule that limits the values that can be inserted or updated in a column. It ensures that the data meets certain conditions. In this case, a check constraint cannot be placed on columns with the text, ntext, image, rowversion, binary, and varbinary datatypes. Therefore, the correct answer is image and rowversion.Rate this question:
-
- 21.
DBCC check constraint Returns any rows that violate rules.What does DBCC stands for?
- A.
Data Consistency Checker
- B.
Database Consistency Checker
- C.
Default Consistency Checker
Correct Answer
B. Database Consistency CheckerExplanation
DBCC stands for "Database Consistency Checker". It is a command in SQL Server that is used to check the consistency of the database, including checking the integrity of the data and the constraints defined on the tables. In this context, the DBCC check constraint command specifically checks for any rows in the database that violate the defined constraints. Therefore, the correct answer is "Database Consistency Checker".Rate this question:
-
- 22.
Default Constraint Enforces ____________________
- A.
Domain Integrity
- B.
Referential Integrity
- C.
Entity Integrity
Correct Answer
A. Domain IntegrityExplanation
The default constraint enforces domain integrity. Domain integrity ensures that the data entered into a column or attribute of a table follows the defined data type, format, and range of values. The default constraint specifies a default value for a column when no value is provided during an insert operation. This constraint helps maintain the integrity of the domain by ensuring that a valid default value is assigned to the column if no other value is specified.Rate this question:
-
- 23.
The SQL Server uses ___________ keyword to store the computed columns in a table
- A.
SAVE_AS
- B.
ROWVERSION
- C.
PERSISTED
Correct Answer
C. PERSISTEDExplanation
The SQL Server uses the "PERSISTED" keyword to store computed columns in a table. This keyword ensures that the computed column values are physically stored in the table, rather than being calculated on the fly whenever the column is accessed. By using the "PERSISTED" keyword, the computed column values are stored and maintained in the table, allowing for faster retrieval and improved performance.Rate this question:
-
- 24.
What is the purpose of the SQL AS Clause?
- A.
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.
- B.
The AS clause is used with the JOIN clause only.
- C.
The AS clause defines a search condition.
Correct Answer
A. 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.Explanation
The AS clause in SQL is used to rename a column in the result set or to assign a name to a derived column. It allows for more meaningful and descriptive column names to be displayed in the output of a query. This can make the results easier to understand and interpret. Additionally, the AS clause can be used in conjunction with other SQL clauses, such as SELECT and FROM, to create aliases for tables or columns, which can simplify the syntax and improve readability of the query.Rate this question:
-
- 25.
The LIKE Keyword is used along with ______________
- A.
GROUP BY clause.
- B.
ORDER BY clause
- C.
JOIN clause.
- D.
WHERE clause.
Correct Answer
D. WHERE clause.Explanation
The LIKE keyword is used along with the WHERE clause. The WHERE clause is used to filter rows based on a specific condition. The LIKE keyword is used to perform pattern matching within the WHERE clause. It is used to search for a specified pattern in a column.Rate this question:
-
- 26.
What does the SQL FROM clause do?
- A.
Specifies the columns we are retrieving.
- B.
Specifies a search condition
- C.
Specifies the tables to retrieve rows from.
Correct Answer
C. Specifies the tables to retrieve rows from.Explanation
The SQL FROM clause is used to specify the tables from which we want to retrieve rows. It is used in conjunction with the SELECT statement to determine the source tables for the data. By specifying the tables in the FROM clause, we can retrieve data from multiple tables by joining them together.Rate this question:
-
- 27.
Which of the following SQL Statements is right?
- A.
SELECT FROM Sales WHERE Date BETWEEN ('10/12/2005', '01/01/2006')
- B.
SELECT FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'
- C.
SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'
Correct Answer
C. SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'Explanation
The correct answer is "SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006'". This is the correct SQL statement because it selects all columns from the "Sales" table where the "Date" column is between '10/12/2005' and '01/01/2006'. The BETWEEN keyword is used to specify a range, and the correct syntax is to use the AND keyword to separate the start and end dates.Rate this question:
-
- 28.
What Will be the result of this following statement : Select left('Have a Nice Day',4)
- A.
Have
- B.
Nice Day
- C.
Day
- D.
Have a Nice Day
Correct Answer
A. HaveExplanation
The given statement is using the LEFT function in SQL to select the leftmost 4 characters from the string 'Have a Nice Day'. The LEFT function returns the specified number of characters from the start of the string. In this case, it will return 'Have', which is the leftmost 4 characters of the given string.Rate this question:
-
- 29.
Select the Non-Deterministic Functions
- A.
DATEADD
- B.
GETDATE
- C.
GETUTCDATE
- D.
MONTH
- E.
DATENAME
Correct Answer(s)
B. GETDATE
C. GETUTCDATE
E. DATENAMEExplanation
The correct answer includes the functions GETDATE, GETUTCDATE, and DATENAME. These functions are non-deterministic because their results can vary each time they are executed. GETDATE returns the current date and time, which is always changing. GETUTCDATE returns the current UTC date and time, which can also change. DATENAME returns a specific part of a date or time, such as the month or day, and the result can vary depending on the input. Therefore, these functions are considered non-deterministic.Rate this question:
-
- 30.
PATINDEX is a string function
- A.
True
- B.
False
Correct Answer
A. TrueExplanation
PATINDEX is indeed a string function in SQL. It is used to search for a specific pattern within a string and returns the starting position of the first occurrence of the pattern. It is commonly used in SQL queries to perform pattern matching operations on strings. Therefore, the statement "PATINDEX is a string function" is correct.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 22, 2023Quiz Edited by
ProProfs Editorial Team -
Jan 16, 2012Quiz Created by
Kiranjot Kaur
Back to top