Scribd
Upload
79 views10 pages

Cardinality and Normalization Quiz

The document contains a series of quizzes covering various topics related to database management, including ERD and normalization, SQL types, database constraints, SQL functions, SQL joins, and database objects. Each quiz consists of multiple questions with answers that address key concepts such as cardinality, normalization, SQL constraints, and the use of SQL statements. The quizzes aim to test knowledge and understanding of fundamental database principles and practices.

Uploaded by

Abhishek Sehrawat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
79 views10 pages

Cardinality and Normalization Quiz

The document contains a series of quizzes covering various topics related to database management, including ERD and normalization, SQL types, database constraints, SQL functions, SQL joins, and database objects. Each quiz consists of multiple questions with answers that address key concepts such as cardinality, normalization, SQL constraints, and the use of SQL statements. The quizzes aim to test knowledge and understanding of fundamental database principles and practices.

Uploaded by

Abhishek Sehrawat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

ERD and Normalization Quiz


Question 1. ________ Defines the possible number of
occurrences in one entity that is associated with the number of
occurrences in another.
Answer: Cardinality

Question 2. The criteria to be fulfilled for a table to be in 1NF are


Answer: Each record needs to be unique,
Each table column should contain a single value

Question 3. Normalization is used for?


Answer: Eliminating redundant data

Question 4. Which of the following are true about a Primary


Key?
Answer: A primary key value must be unique
The primary key cannot be changed

Question 5. Students as a group are associated with multiple


faculty members, and faculty members, in turn, are associated
with multiple students. For this scenario, the cardinality
between the student entity and the faculty entity is _____?
Answer: many to many

2. SQL Type Quiz


Question 1. Which keyword is used while adding a constraint to
an existing table to avoid verifying old values within the column?
Answer: DEFERRABLE

Question 2. What are the various keywords used along with the
ALTER statement?
Answer: DROP, RENAME, ADD, MODIFY

Question 3. Select the suitable option for Check Constraint?


Answer: All of the these

Question 4. How can we define constraints with the user-defined


name?
Answer: data type constraint

Question 5. Which SQL statement is used to remove all the data


from a table permanently?
Answer: TRUNCATE

3. Database Constraint Quiz

Question 1. How to check constraint names and constraint types


on the column of a particular table?
Answer: Select constraint_name, constraint_type from user_constraints
where table_name =""

Question 2. Select the Suitable option on the Foreign key on a


Column.
Answer: All of the these

Question 3. Select correct syntax to drop check constraint.


Answer: alter table drop constraint

Question 4. Which type of Constraint doesn't allow null values in


a column?
Answer: Not Null

Question 5. Which of the below option ensure that the value of


COLUMN(named as budget) is non-negative?
Answer: check(budget>=0)

4. Functions in SQL Quiz

Question 1. Which SELECT statement should you use to extract


the year from the system date and display it in the format
"2021"?
Answer: SELECT TO_CHAR(SYSDATE, 'yyyy') FROM dual;

Question 2. Select LENGTH('Tata Consultancy'); Returns


Answer: 16

Question 3. Which SQL statement returns a numeric value?


Answer: SELECT sysdate-hire_date FROM EMP;

Question 4. What is true of using group functions on columns


that contain NULL values?
Answer: Group functions on columns ignore NULL values.

Question 5. SELECT ROUND(TRUNC(MOD(1600, 10),-1),2)


FROM dual; What will be displayed?
Answer: 0

5. SQL Joins Quiz

Question 1. To avoid a Cartesian product?


Answer: Always include a valid join condition in the WHERE clause
Question 2. For joining N tables how many join conditions are
required?
Answer: N-1

Question 3. What is true about joining a table through an


equijoin?
Answer: You can join n tables (all having single-column primary keys) in a
SQL statement by specifying a minimum of n-1 join conditions.

Question 4. In which case would you use a FULL OUTER JOIN?


Answer: You want all unmatched data from both tables.

Question 5. In which of the cases would you use the Using


clause?
Answers: All

6. Database Object Quiz

Question 1. Which is a database object from which multiple users


can generate unique identification number or Random number
with fixed interval?
Answer: Sequence
Question 2. How many types of indices are there in the SQL
server?
Answer: Two

Question 3. Select the suitable option to get the next value of the
sequence?
Answer: .nextval

Question 4. Select the suitable option for clustered index


creation?
Answer: create fulltext index
special system stored procedures

Question 5. Examine the structure of the EMployee table:


Column name Data type RemarksEmployee_ID NUMBER NOT
NULL, Primary KeyEMP_NAME VARCHAR2(20) NOT NULL
SAL NUMBERGR_ID NUMBER References EMPLOYEE_ID
column DEPARTMENT_ID NUmber Foreign key to
DEPARMENT_ID column of the DEPARTMENT table You need
to create a view called EMP_VU that allows the user to insert
rows through the view. Which SQL statement, when used to
create the EMP_VU view. allows the user to insert rows?
Answer: CREATE VIEW emp_vu AS SELECT department_id, emp_name,
job_id, department_id FRoM employees WHERE mgr_id IN (102, 120).
Question 1. Select The suitable option from below to create an index
on the "job" column in the emp table?

Answer: Create Index emp_ind on emp(job);

Question 2. Which of the below constraints will make sure to allow,


only the values of a column (in a master) to the respective column of
a child table?

Answer: foreign key

Questions 3. Select a suitable option for Normalization importance on


a database?

Answer: To avoid redundancy

Applying changes to the table data without affecting the other attributes

To fetch the perfect and exact data, required

Data would not lost

Question 4. Which of the following is true with respect to referential


integrity?

Answer: Every foreign key value must match a primary key value in the
associated table i.e. parent table

Question 5. What is the grant statement to grant statement to grant


select, insert, delete privileges on table: Emp to user : TestUser? Emp
is the table NameTestUser is the User Name

Answer: Grant select, insert, delete on Emp to TestUser


Question 6. Which is below statement is used to remove access or
privileges on the database object to users?

Answer: Revoke

Question 7. Which of the following is the type of constraint?

Answer: CHECK, DEFAULT, PRIMARY KEY, UNIQUE KEY

Question 8. Take structure EmpId | EmpName | ProjId | MNGR-Id |


Location |

Assume that the respective data values/attributes referred in the


options were exists in the table. By referring to the above table
structure, for which of the below questions, are we would require to
apply the concept of SubQuery?

Answer: Query to get employee, whose designation is same of swarna's


designation

Query to get employee details belonging to dept of RAJA

Questions 9. The formal classification describing the level of


database normalization in a data model are called Normal Forms
Select True or False

Answer: True

Questions 10. From the table below EmpId | EmpName | ProjId |


[Link].................
Answer: Table 1 EmpId | EmpNameTable : ProjectId | ProjectNameTable3:
Empid | ProjId | [Link]

Question 11. Which SQL statement returns a numeric value?

Answer: SELECT sysdate-hire_date from EMP;

Question 12. From the table below EmpId | EmpName | ProjId |


[Link].................

Answer: EmpId and ProjId together

Question 13. Refer to the table below EmpId | EmpName | ProjId |


............................... by referring to the above table, Select a
suitable query that gets all employees whose project is the same as a
project of 'd'?

Answer: SELECT * FROM EMP WHERE ProjId = (SELECT ProjId FROM


EMP WHERE EMPNAME ="d')

Question 14. table below EmpId | EmpName | ProjId | >1 a p1 >> 2


p2>>>3 c p3......... by referring the above table, Select a suitable query
which would letch EMPID EMPNAME ProName for Each employee?

Answer: SELECT [Link]. [Link] From EMP A


Proj B where [Link] = [Link]

Question 15. The DDL command is?

Answer: ALTER, DROP, CREATE


Question 16. We can use Transactional control statements in the
Database Triggers? Select True or False

Answer: False

Question 17. Type of SQL statement are?

Answer: DDL, DML, TCL

Question 18. Select a suitable option for SubQuery?

Answer: Inner query executes first and then based on the output of inner
query, the outer query executes

Question 19. A column with reference to a record in a table should not


have multiple values. Which of the normal form, Talks about the
above statement?

Answer: First

Question 20. DCL Commands are used to enforce database security


in multiple user environments. Select True or False?

Answer: True

Common questions

Powered by AI

Normalization is crucial in database management to eliminate redundant data and ensure data integrity. The first normal form (1NF) ensures that columns contain atomic, or single values per record, maintaining data consistency and preventing repetition .

Constraints in SQL are rules applied to data columns to enforce data integrity. A NOT NULL constraint prevents null values in a column, ensuring that every row has a value for that column. In contrast, a CHECK constraint imposes specific conditions that data must fulfill, such as the requirement that all values in a 'budget' column must be non-negative .

To create a view that allows data insertions, the SQL must define a view on a single base table without joining other tables. All non-nullable columns must be included unless they have default values, and the view must not contain group functions or use distinct. This compliance is necessary to ensure the view remains updatable by maintaining a direct correspondence with the base table structure and constraints .

A FULL OUTER JOIN in SQL is used when you want to combine all records from two tables and include unmatched rows from both sides, with nulls in columns missing data from either side. This is useful for scenarios when you need a complete set of matches and non-matches between datasets .

Cardinality in database relationships defines the number of possible occurrences between entities, showing how instances of one entity relate to instances of another. In a many-to-many scenario, such as between student and faculty entities, each student can link to multiple faculty members and each faculty member can associate with multiple students, necessitating a junction table or a relationship table to properly manage these connections .

Transactional control statements, such as COMMIT and ROLLBACK, are disallowed within database triggers because they could interfere with the transaction handling of the application using the database. Triggers are meant to execute automatically in response to specific events, and allowing transactional control could result in uncommitted operations, leading to data inconsistencies .

The TRUNCATE statement in SQL is used to remove all rows from a table quickly and efficiently. Unlike the DELETE statement, which can remove specific rows based on conditions and requires logging for each row deleted, TRUNCATE does not log individual row deletions and resets any AUTO_INCREMENT counters .

Using the ALTER statement with keywords like ADD, MODIFY, and DROP allows significant schema modification in a database. ADD is used to introduce new columns; MODIFY alters existing column definitions, such as data type changes; and DROP removes columns from a table. Each action needs careful planning to avoid potential data loss or integrity issues .

Group functions, like SUM or AVG, ignore NULL values when processing data. This behavior is significant because it allows accurate aggregation over datasets without being skewed by missing data, ensuring the results reflect only the present and valid entries .

Sequences in SQL are database objects used to generate unique identifiers, particularly in environments where such identifiers need to be incremented automatically and without conflicts. Sequences offer advantages such as guaranteed uniqueness, scalability for concurrent access, and the flexibility to specify increment patterns, which is difficult to achieve with manual methods or simple counting .

You might also like