WorksheetsSQL Concepts Quiz
Total questions: 128
Worksheet time: 1hrs 7mins
In SQL, what is the primary advantage of using stored procedures?
They allow for the creation of tables
They enhance security by restricting access to data
They provide a way to encapsulate and reuse a set of SQL statements
They are more efficient than regular SQL queries
Which type of user-defined function in SQL returns a single value?
Scalar function
Table-valued function
Inline function
Aggregate function
Two concurrent executing transactions T1 and T2 are allowed to update the same record in an uncontrolled manner. In such a scenario. The following problem may not occur if database system has no concurrency module and allows concurrent execution of above two transactions. Which of the following is correct in this case?
Transaction failure
Dirty read problem
Lost update problem
Inconsistent database state
What is the main disadvantage of having too many indexes on a table?
Improved query performance
Increased storage space
Enhanced data integrity
Fast data modification operations
Given table A with n rows and table B with m rows. How many rows of table result by Cartesian product on AxB?
m*n rows
m*n +1 rows
m*n-1 rows
m+n rows
Which of the following is NOT the binary operation in the relational Algebra?
Project
Cartesian product
Union
Set difference
A key:
must always be composed of two or more columns.
can only be one column.
identifies a row.
identifies a column.
This simple SQL queries, uses the three keywords that characterize SQL is ____ ?
Count, Sum and Avg
Insert, Update and Delete
Select, From, and Where
And, Or, and Not
In the relational model, how are relationships represented between tables?
By adding attributes to each table
By creating a new table for each relationship
By adding foreign keys to related tables
By creating a primary key in each table
What is a weak entity in an ERD?
An entity with no attributes
An entity that participates in a one-to-many relationship
An entity that cannot exist without being related to another entity
An entity with a composite key
A primary key is combined with a foreign key creates:
Parent-Child relationship between the tables that connect them.
Many to many relationship between the tables that connect them.
Network model between the tables that connect them.
None of the mentioned.
The key for a weak entity set is_____?
Zero or more attributes
The set of attributes of supporting relationships
It is possible for an entity set's key to be composed of attributes, some or all of which belong to another entity set
The set of attributes of supporting entity sets
What are some benefits of normalization when converting an ERD to a relational model?
Increased storage space efficiency
Reduced update anomalies
Simplified database queries
Enhanced data security
_____ returns a relation instance containing all tuples that occur in both R and S
Intersection
Set-difference
Union
Cross-product
To modify the schema of an existing relation use ______
Create table
Modify table
Alter table
Drop table
According to the technology deployed by the database management system, which of the following is correct?
Locks are used to maintain transactional integrity and consistency.
Cursors are used to maintain transactional integrity and consistency.
Procedures are used to maintain transactional integrity and consistency.
Functions are used to maintain transactional integrity and consistency.
Choose the incorrect statement about DELETE and TRUNCAT in SQL Server.
DELETE is used for unconditional removal of data records from Tables; TRUNCATE is used for conditional removal of data records from Tables.
DELETE is a DML command and the operations are logged, whereas TRUNCATE is a DDL command and the operations are not logged.
DELETE removes records and records each deletion in the transaction log whereas TRUNCATE deallocates pages and records each deallocation in the transaction log.
TRUNCATE is generally considered quicker as it makes less use of the transaction log
The result which operation contains all pairs of tuples from the two relations, regardless of whether their attribute values match.
Join
Cartesian product
Intersection
Set difference
Which of the following is true about stored procedure in MS-SQL Server?
They include procedural and SQL statements.
They are stored in Rules folder
They do not need to have a unique name
They are same as void function
What is NOT true about the cursor in SQL?
It is considered the best practice to use when we want insert, update, or delete data in a table of SQL Server.
The cursor allows users to process data from a result set, one row at a time.
Cursors are an alternative to commands, which operate on all rows in a result set at the same time.
Unlike commands, cursors can be used to update data on a row-by-row basis.
How can you delete a user-defined function in SQL?
DELETE FUNCTION function_name
REMOVE FUNCTION function_name
DROP FUNCTION function_name
ERASE FUNCTION function_name
Which command is used to remove a relation from an SQL?
Drop table
Delete
Purge
Remove
Domain constraints, functional dependency and referential integrity are special forms of _____
Foreign key
Primary key
Assertion
Referential constraint
In an employee table to include the attributes whose value always have some value which of the following constraint must be used?
Null
Not null
Unique
Distinct
Which SQL statement is used to remove an existing index from a table?
Atomicity, Consistency, Isolation, Durability
REMOVE INDEX
DELETE INDEX
UNINDEX TABLE
Choose the correct statement that belongs to DML.
SELECT, DELETE, INSERT
CREATE, UPDATE, SELECT
TRUNCAT, DELETE, UPDATE
UPDATE, DELETE, ALTER
A Delete command operates on _____ relation.
One
Two
Several
Null
You have a table named Books that has columns named Book Title and Description. There is a full-text index on these columns. You need to return rows from the table in which the word 'computer' exists in either column. Which code segment should you use?
SELECT * FROM Books WHERE FREETEXT(*, 'computer')
SELECT * FROM Books WHERE Book Title LIKE '%computer%'
SELECT * FROM Books WHERE Book Title = '%computer%' OR Description = '%computer%'
SELECT * FROM Books WHERE FREE
Which of the following is false about a foreign key?
Whereas only one foreign key is allowed in a table.
It refers to the field in a table which is the primary key of another table.
It can contain duplicate values and a table in a relational database.
It can also contain NULL values.
Which of the following is expressed by an E-R diagram?
Relation between process and relationship
Relation between entity and process
Relation between processes
Relation between entities
Which of the following statements about relational databases is true?
The database is built on a relational data model.
The database is created from excel software.
Relational databases are only relations with attributes that are not primary keys.
Relational database is the relationship between rows and columns in a data table.
What is the purpose of data normalization in the context of the database design?
Ensure data security
Avoid information anomalies
Make sure data is inherited
Ensuring better data storage
Given following schema R(A, B, C, D) and S={A→B, BC, C→D}Compute {A}+?
AB
ABC
ABCD
BCD
Suppose that we have a relation that possesses data about an individual entity. In Addition, it should not have any multi-valued dependencies. Which of the following is the highest normal form for this relation?
1NF
2NF
3NF
4NF
What is relational algebra?
Relational algebra is a set of operations on relations.
Relational algebra is the decomposition of relations.
Relational algebra is eliminated from relations.
Relational algebra is gone from relations.
For the following relations and a Set operation: Employee Name | City | Gender Nguyen Trung Truc | HCM | Male Le Hong Son | HCM | Male Customer Name | City | Gender Nguyen Trung Truc | HCM | Male Tran Tuyet Nhi | HN | Female and Employee Customer What is the result of the Union operation?
Name | City | Gender Nguyen Trung Truc | HCM | Male Le Hong Son | HCM | Male
Name | City | Gender Nguyen Trung Truc | HCM | Male Tran Tuyet Nhi | HN | Female
Name | City | Gender Nguyen Trung Truc | HCM | Male Le Hong Son | HCM | Male Nguyen Trung Truc | HCM | Male Tran Tuyet Nhi | HN | Female
Name | City | Gender Nguyen Trung Truc | HCM | Male Le Hong Son | HCM | Male Tran Tuyet Nhi | HN | Female
What is the foreign key?
It is a relational constraint that enforces referential integrity between two tables
It is a relational constraint that that ensures the values in a specific column or set of columns are unique across all the rows in a table
The key that need to make a data in another table
A collection of data that allows to access and use of that data
Database data models usually have a way to go describe limitations on what the data can be, is called
Constraints on the data
Operations on the data
Structure of the data
definition of data
(a) A variable is local, and its value is not preserved by the DBMS after a run-ning of the function or procedure.
What is an SQL virtual table that is constructed from other tables?
Just another table
A view
A relation
Query results
In SQL, to update a relation's schema, which one of the following statements can be used?
Insert
Update
Alter
Alter table
What happens if an error occurs during a transaction and it is not explicitly handled or rolled back?
The transaction is automatically committed
The transaction is automatically rolled back
The database becomes read-only
The transaction remains in a pending state
Foreign key is the one in which the ______ of one relation is referenced in another relation.
Foreign key
Primary key
References
Check constraint
The DROP TABLE statement:
deletes the table structure only.
deletes the table structure along with the table data.
works whether or not referential integrity constraints would be violated.
is not an SQL statement.
Which of the following aggregate functions does not ignore nulls in its results?
MIN
MAX
COUNT (*)
COUNT
Which of the following statements is false regarding SQL Correlated sub-queries?
A correlated subquery is not evaluated once for each row processed by the parent statement.
Correlated subqueries are used for row-by-row processing.
Each subquery is executed once for every row of the outer query.
Subqueries always process the innermost query first and the work outward.
Suppose relation R(A, B) has the following tuples: AB ------------------ 1 a 2 b 3 a 4 d 5 e 6 a 7 g The SQL statement to count distinct rows from column B in relation R
SELECT COUNT DISTINCT B AS 'Total_rows' FROM R;
SELECT (COUNT DISTINCT B) AS 'Total_rows' from R;
SELECT DISTINCT B AS 'Total_rows' from R;
SELECT COUNT (DISTINCT(B)) AS 'total_rows' FROM R;
Which statement is used to change the data type of a column named E in table A?
ALTER TABLE A ALTER COLUMN E [DataType]
ALTER TABLE A ALTER COLUMN E
ALTER TABLE A ALTER E [DataType]
ALTER TABLE A DROP COLUMN E [DataType]
What happens to derived attributes when converting an ERD to a relational model?
They become primary keys in the related tables
They are calculated and stored as regular attributes in the related tables
They become foreign keys in the related tables
They are ignored during the conversion process
What does the attribute domain define in the relational model?
The primary key of the table
The foreign key references
The cardinality of relationships
The data type and range of values that an attribute can hold
To covert an entity-relationship diagram to a logical diagram, which of the following will create a new relation that will typically include primary keys from both entities and any additional attributes related to the relationship?
A many-to-many relationship
A recursive relationship
A one-to-many relationship
A one-to-one relationship
What is a derived attribute in an ERD?
An attribute that is calculated from other attributes
An attribute that cannot be calculated
An attribute that is the primary key of an entity
An attribute that represents a relationship
Which of the following statements is true about data normalization:
1NF: Is a relationship with a primary key and no repeating groups
2NF: Is 1NF without transitive functional dependencies
3NF is: 1 NF without partial functional dependencies
All of the above answers are wrong.
Which normalization level ensures that each attribute is dependent only on the primary key?
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Suppose that there is a relation R (A, B, C, D, E, G) And functional dependencies S = {AB→C, DEG, CA, BEC, BC→D, CG → BD, ACD → B, CE → AG} Compute {AB}+
ABCD
ABCDE
ABCDG
ABCDEG
Which of the following represents Armstrong's Axiom of Transitivity?
If AB, then AC → BC
If A B and B → C, then A → C
If A B, then B → A
If A B, then A → BC
Which statement is the best represents Armstrong's Axiom of Decomposition?
If A B and A → C, then A → BC
If A B and B → C, then A → C
If A B, then AC → BC
If A B, then B → C
What are logical constraints?
The relationships between attributes are represented by relational operations.
The relationships between attributes are represented by comparisons operator.
The relationships between attributes are represented by mathematical expressions.
The relationships between attributes are represented by functional dependencies.
What is a database?
A collection of information that exists over a long period of time. A collection of related data and managed by a database management system.
The database is built based on the relational data model.
Database used to create, update and exploit relational databases.
The database is built based on the relational data model and exploits the relational database.
In query compiler, which unit builds a tree structure from the text form of the query?
A query parser
A query preprocessor
A query optimizer
A query processor
What are the disadvantages of network data model?
Not support the high-level query language
Not support the relationships between nodes
Not support the database management system
Not support the storage method of very large amounts of data
What is information?
A collection of unprocessed data
A collection of processed data
A collection of processed information
A collection of unprocessed information
Which of the following is not a data model in the database?
Conceptual data model
Object-Oriented data model
Network data model
Hierarchical data model
In query compiler, which unit transforms the initial query plan into the best available sequence of operations on the actual data?
A query parser
A query preprocessor
A query optimizer
A query processor
The ability to query data, as well as insert, delete, and alter tuples, is offered by ____
TCL (Transaction Control Language)
DCL (Data Control Language)
DDL (Data Definition Language)
DML (Data Manipulation Language)
The conceptual model is:
Independent of both hardware and software
Dependent on both hardware and software.
Dependent on software.
Dependent on hardware.
A data model is a notation for describing data or information. The description generally consists ?
Structure of the data
Operations on the data
Constraints on the data.
All of the others
What is a Relational Database?
A collection of two or more tables
A database that is able to process queries, forms, reports and macro
The same as an Excel file database
A collection of two or more tables that are joined using relationships
A _____ is a relation name, together with the attributes of that relation.
Schemas
Instance
Database
Attributes
The values appearing in given attributes of any tuple in the referencing relation must likewise occur in specified attributes of at least one tuple in the referenced relation, according to ________ constraint integrity
Referential
Primary
Referencing
Specific
For the following relation: Student ID | Name | City | Gender SE1234 | Le Hong Phong | HCM | Male SE1235 | Le Hong Hanh | HCM | Female SE1236 Tran Binh Trong | HN | Male Use Algebraic query language to list all name and city of students in HCM and Male?
Name, City (City='HCM' AND Gender='Male' (Student))
Name, City([City='HCM' AND Gender='Male' (Student))
Name, City (City='HCM' && Gender='Male' (Student))
Name, City (City='HCM' && Gender='Male' (Student)
If attribute A determines both attributes B and C, then it is also true that:
(B,C) -> A.
C -> A.
B -> A.
A -> B
Which operation is commonly used to display all the properties derived from the original property?
Union
Intersection
Closure
Projection
If a relation is in BCNF, then it is also in
1 NF
2 NF
3 NF
All of the mentioned
In relational database design, which of the following is known as a set of entities of the same type that share the same properties or attributes?
Entity Relation model
Entity set
Field set
Record set
An E/R diagram is a graphical representation of sets, __________?
Entity
Attributes
Relationships
All of the others
To convert from ER Diagram to Relational Model, 1-M relationship:
Put key attribute of one-side to M-side
Put key attribute of M-side to one-side
Generate 1 relation, Primary key of this relation combined from two relations
Build a table with two columns, one column for each participating entity set's primary key
Which key does not accept the null value?
Unique Key
Primary Key
Foreign Key
Candidate key
Which of the following is correct?
SELECT datepart(DATE, '10-jan-24')
SELECT datepart('10-jan-24', DATE)
SELECT datepart('10-jan-24', DAY)
SELECT datepart(DAY,'10-jan-24')
Regarding difference between a primary key vs. unique key, which of the following is false?
There can be only one unique key for a table
A primary key is a key that uniquely identifies each record in a table but cannot store NULL values
A unique key prevents duplicate values in a column and can store NULL values.
Only one primary key is allowed to use in a table.
Which statement is used to disable a foreign key constraint named fk_name during INSERT and UPDATE transactions in table B?
(a)
Which statement is used to disable a foreign key constraint named fk_name during INSERT and UPDATE transactions in table B?
ALTER TABLE B DROP CONSTRAINT fk_name
ALTER TABLE B DISABLE CONSTRAINT fk_name
ALTER TABLE B DISABLE fk_name
ALTER TABLE B NOCHECK CONSTRAINT fk_name
You have a column named TelephoneNumber that stores numbers as varchar(20). You need to write a query that returns the first three characters of a telephone number. Which expression should you use?
CHARINDEX('[0-9][0-9][0-9]', TelephoneNumber, 3)
SUBSTRING (Telephone Number, 3, 1)
SUBSTRING(TelephoneNumber, 3, 3)
LEFT(TelephoneNumber, 3)
Which SQL statement selects all rows from table called Contest, with column ContestDate having values greater or equal to May 25,2006?
SELECT * FROM Contest WHERE ContestDate >= '05/25/2006'
SELECT * FROM Contest GROUPBY ContestDate >= '05/25/2006'.
SELECT * FROM Contest WHERE ContestDate < '05/25/2006'.
SELECT * FROM Contest HAVING ContestDate >= '05/25/2006'.
In MS-SQL Server, which of the following is true about join operator?
SELECT * FROM tbICUSTOMER, tbIORDER
Cartesian join
Equi-join
Natural join
Outer join
UPDATE
Where
Set
In
Select
Which one of the following SQL statements returns the name of the employee (employeename, dept_code, salary) receiving the maximum salary in a particular department?
Select employeename, dept_code, salary From employee Where employee.salary in (select (salary) from Employee group by dept_code having Max(salary))
Select employeename, dept_code, salary From employee Where employee.salary in (select Max(salary)from Employee group by dept_code)
Select employeename, dept_code, salary From employee Where employee.salary in (select Max(salary)from Employee group by dept_code having Max(salary))
Select employeename, dept_code, salary From employee Where employee.salary =(select Max(salary)from Employee group by dept_code
Insert into Students (32, N'lona Bush', N'Female'); In the given query which of the keyword has to be inserted?
Into
Set
Where
Values
Foreign key constraints are created by using the " "keyword to refer to the primary key of another table.
Refer
References
Referential
All of the others
What is not the purpose of the index in MS-SQL Server?
To use less hardware resources
To enhance the query performance
To provide an index to a record
To perform fast in searching
The transaction methods is used with the Connection object to save or cancel changes made to the data source.
Begin Transaction, Rollback Transaction
Begin Transaction, Commit Transaction
Begin Transaction, Commit Transaction, Rollback Transaction
Commit Transaction, Rollback Transaction
The deadlock state can be changed back to stable state by using statement.
Delete
Deadlock
Commit
Rollback
Which one of the following is not true for a view?
View never contains derived columns.
A view definition is permanently stored as part of the database.
View is a virtual table.
View is derived from other tables
Which is the SQL statement that creates an updatable view? In this question, all the tables in the SQL statements are updatable.
CREATE VIEW Ordered_Product(Product_No) AS SELECT DISTINCT Product_No FROM Order
CREATE VIEW Order_List(Order_No, Product_Name, Order_Quantity) AS SELECT Order_No,Product_Name, Order_Quantity FROM Order, Product WHERE Order Product_No=Product.Product_No
CREATE VIEW Product_Order(Product_No, Order_Quantity) AS SELECT Product_No,SUM(Order_Quantity) FROM Order GROUP BY Product_No
CREATE VIEW Expensive_Product(Product_No, Product_Name) AS SELECT Product_No,Product_Name FROM Product WHERE Product_Unit_Price>1000
Triggers are stored blocks of code that have to be called in order to operate.
True
False
In SQL, when does a BEFORE INSERT trigger fire?
Before the execution of a SELECT statement
Before a new record is inserted into a table
Before a transaction is committed
Before a table is dropped
In SQL, what is the purpose of the CLOSE statement in relation to cursors?
To close the database connection
To close the cursor and release associated resources
To close the transaction
To close the result set
Which SQL statement is used to declare a cursor?
OPEN
DECLARE
CREATE CURSOR
BEGIN CURSOR
How is User-defined function different from Store Procedure?
User-defined functions do not have input parameters, Store Procedures do
User-defined functions cannot contain transactions, Store Procedures can
Store Procedure does not have return results, User-defined function does
All of the mentioned answers are incorrect
How to execute a procedure?
execute name_sp
exe name_sp
call name_sp
All the answers above
a and b are correct
A stored procedure in SQL is a?
Block of functions
Group of Transact-SQL statements compiled into a single execution plan
Group of distinct SQL statements
None of the mentioned
_______has a right side that is a subset of its left side.
A trivial FD
A non_trivial FD
A key of relation
A super key of relation
Given R as a relation, which of the following statements is correct about normalizing data in 3NF form:
If R: Has a primary key, no repeating groups, no multivalued attributes/composite attributes
If R: Has a primary key, no repeating groups, no multivalued attributes/complex attributes, no partial functional dependencies.
If R: Has a primary key, no repeating groups, no multivalued/composite attributes, no transitive dependencies.
If R: Has a primary key, no repeating groups, no multivalued attributes/complex attributes, no transitive functional dependencies, no partial functional dependencies.
If R: has complete functional dependency.
Which of the following statements is true regarding Third Normal Form (3NF)?
It eliminates transitive dependencies
It allows for redundant data
It only considers partial dependencies
It doesn't address any anomalies in a database
When is it recommended to use a cursor?
When is it recommended to use a cursor in SQL?
Always, as cursors are the most efficient way to process data
When processing data row by row is necessary or unavoidable
Only when creating complex joins between tables
Never, as set-based operations are always more efficient
How can you invoke a user-defined function in a SQL query?
Using the EXECUTE statement
Using the CALL statement
Using the SELECT statement
Using the RUN statement
_______is a set of one or more attributes taken collectively to uniquely identify a record.
Primary Key
Foreign key
Super key
Candidate key
Which of the following belongs to DDL (data definition language)?
create, alter, drop
Insert, update, delete, select
Truncate, select, invoke
Deny, create, drop
Which of the following is not a feature of DBMS?
Minimum duplication of data
Redundancy of data
Single-user access only
Support ACID Property
What is a database?
Organized collection of information that cannot be accessed, updated, and managed
Collection of data or information without organizing
Organized collection of data or information that can be accessed, updated, and managed
Organized collection of data that cannot be updated
What is the full form of DBMS?
Data of Binary Management System
Database Management System
Database Management Service
Data Backup Management System
Which of the following is the appropriate characteristic of a database?
Because a database is created to suit the format of the data, it cannot respond flexibly to data format changes.
The procedure for making backups is complicated.
It is difficult to share data between operations due to an exclusive control function.
It can be accessed by multiple users at the same time due to an exclusive control function.
A database administrator: a person or persons responsible for the structure or schema of the database.
True
False
What does an RDBMS consist of?
Collection of Tables
Collection of Records
Collection of Keys
Collection of Field
The descriptive property possessed by each entity set is ______
Entity
Attribute
Relation
Model
The ___ operation allows the combining of two relations by merging pairs of tuples, one from each relation, into a single tuple.
Select
Join
Union
Intersection
For the following relations and a Set operation: Employee Name | City | Gender Nguyen Trung Truc | HCM | Male Le Hong Son | HCM | Male Customer Name | City | Gender Nguyen Trung Truc | HCM | Male Tran Tuyet Nhi | HN | Female and Employee \ Customer What is the result of the Difference operation?
Name | City | Gender Le Hong Son | HCM | Male
Name | City | Gender Nguyen Trung Truc | HCM | Male Tran Tuyet Nhi | HN | Female
Name | City | Gender Nguyen Trung Truc | HCM | Male Le Hong Son | HCM | Male
Name | City | Gender Nguyen Trung Truc | HCM | Male Le Hong Son | HCM | Male Tran Tuyet Nhi | HN | Female
In the relational model of data, what is the result of an algebraic query language?
Data series
Data file
Relation
database
What are logical constraints?
The relationships between attributes are represented by relational operations.
The relationships between attributes are represented by comparisons operator.
The relationships between attributes are represented by mathematical expressions.
The relationships between attributes are represented by functional dependencies
What is Database Normalization?
A process whereby a limit is put on the number of fields a record can contain
The process of ensuring that each table has a key
The process of ensuring that a relational database has at least two tables in it
A process whereby the design of a table (relation) is decomposed into more tables that more precisely fit the relational mode
Consider a relation R(A,B,C,D,E) with functional dependencies: AB->C, B->C, E->D and C->E. What is/are the key(s) for R
AC
AD
AB
AE
Suppose that we have a relation R (B, C, D) that satisfies FD's: D->B and D->C. Which of the following FD is equivalent to the given dependency functions?
D->BC
D->DB
DB->B
DC->DB
Which normalization level ensures that there are no repeating groups in tables?
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
The highest normal form for relation schema R(ABCD) with functional dependencies: F = {AB-> C; D->B, C->ABD} is:
2NF
1NF
3NF
BCNF
What is the primary goal of normalization in database design?
To increase data redundancy
To simplify data retrieval
To introduce duplicate data
To violate functional dependencies
What is the purpose of a foreign key in an ERD?
It defines the data type of attributes
It determines the cardinality of relationships
It serves as the primary identifier for a table
It establishes a link between two tables
In SQL, ERD use three types of principle elements to form relationships:
Entity sets, Constraints and Relationships
Attributes, Constraints, and Relationships
Entity sets, Attributes, and Relationships
Entity sets, Attributes and Constraints
