Cyber Olympiad Class 10 - Sample question paper 13

Q1. Which type of join in SQL returns all records when there is a match in either the left or right table?

a) INNER JOIN

b) LEFT JOIN

c) RIGHT JOIN

d) FULL OUTER JOIN

Q2. In database terminology, what is the purpose of the term "ACID" properties?

a) A set of rules for data modeling

b) A type of indexing method

c) Ensuring reliable processing of database transactions

d) A data type for numerical values

Q3. Which SQL command is used to remove all records from a table without removing the table itself?

a) DELETE

b) TRUNCATE

c) DROP

d) REMOVE

Q4. What is the purpose of the SQL aggregate function MAX()?

a) Find the average value of a column

b) Retrieve the maximum value from a column

c) Count the number of records in a table

d) Concatenate values from multiple rows

Q5. In a relational database, what is a foreign key?

a) A key used for encryption

b) A key that uniquely identifies each record in a table

c) A key that references the primary key in another table

d) A key used for sorting records

Q6. What is the purpose of the SQL command GROUP BY?

a) Arrange records in ascending order

b) Combine multiple columns into one

c) Filter records based on a condition

d) Group rows that have the same values in specified columns

Q7. What is the purpose of the SQL command HAVING?

a) Specify conditions for filtering rows

b) Join multiple tables

c) Sort records in descending order

d) Specify conditions for filtering groups

Q8. Which type of relationship exists when one record in Table A can have only one related record in Table B, and each record in Table B can have only one related record in Table A?

a) One-to-One (1:1)

b) One-to-Many (1:N)

c) Many-to-One (N:1)

d) Many-to-Many (N:N)

Q9. What is the purpose of the SQL command UNION?

a) Combine rows from two or more tables

b) Remove duplicate rows from a result set

c) Sort records in descending order

d) Rename a table

Q10. What is the purpose of the SQL command COMMIT?

a) Add a new record to a table

b) Delete a record from a table

c) Save all changes made during the current transaction

d) Roll back changes made during the current transaction

Q11. What is the purpose of the SQL command ROLLBACK?

a) Add a new record to a table

b) Delete a record from a table

c) Save all changes made during the current transaction

d) Undo changes made during the current transaction

Q12. In a relational database, what is the purpose of the UNIQUE constraint?

a) Ensure data integrity and uniqueness in a column

b) Create a relationship between tables

c) Sort records in ascending order

d) Combine multiple columns into one

Q13. What is the purpose of the SQL command ALTER TABLE?

a) Add a new record to a table

b) Delete a record from a table

c) Modify the structure of a table

d) Sort records based on a condition

Q14. What is the purpose of the SQL command CASCADE?

a) Sort records in ascending order

b) Delete all records from a table

c) Perform multiple operations in a single transaction

d) Propagate changes made to the parent table to its related tables

Q15. In a database, what is the purpose of the CHECK constraint?

a) Enforce data integrity constraints

b) Combine multiple columns into one

c) Specify conditions for filtering rows

d) Create a relationship between tables

Q16. What is the purpose of the SQL command GRANT?

a) Add a new record to a table

b) Modify the structure of a table

c) Assign privileges to users or roles

d) Delete a record from a table

Q17. In Microsoft Access, what is the purpose of the "Relationships" window?

a) Design queries

b) Create forms

c) Define relationships between tables

d) Design printable layouts for presenting data

Q18. What is the purpose of the SQL command INDEX?

a) Sort records in ascending order

b) Improve search and retrieval performance

c) Combine multiple columns into one

d) Create a backup of a table

Q19. What is the purpose of the SQL command DELETE?

a) Remove a table from the database

b) Remove all records from a table

c) Modify the structure of a table

d) Add a new record to a table

Q20. What is the purpose of the SQL command UPDATE?

a) Add a new record to a table

b) Delete a record from a table

c) Modify existing records in a table

d) Create a relationship between tables

Q21. What is the purpose of the SQL command SELECT DISTINCT?

a) Select all records from a table

b) Select unique values from a column

c) Rename a column in a result set

d) Add a new record to a table

Q22. In a relational database, what is the purpose of the term "Normalization"?

a) Sorting records in ascending order

b) Designing user interfaces

c) Organizing data to minimize redundancy and dependency

d) Creating relationships between tables

Q23. What is the purpose of the SQL command INNER JOIN?

a) Combine records from both tables, including non-matching ones

b) Combine only matching records from both tables

c) Sort records in descending order

d) Create a new table with combined records

Q24. What is the purpose of the SQL command COMMIT?

a) Add a new record to a table

b) Delete a record from a table

c) Save all changes made during the current transaction

d) Roll back changes made during the current transaction

Q25. In a relational database, what is the purpose of the term "Data Integrity"?

a) Ensuring accurate and consistent data

b) Sorting records in ascending order

c) Creating relationships between tables

d) Designing user interfaces

Q26. What is the purpose of the SQL command DESC?

a) Add a new record to a table

b) Delete a record from a table

c) Sort records in descending order

d) Perform a complex calculation

Q27. In Microsoft Access, what is the purpose of the "Lookup" data type?

a) Store large text data

b) Display a list of values for user selection

c) Calculate values based on specified criteria

d) Sort records in ascending order

Q28. What is the purpose of the SQL command GROUP BY?

a) Arrange records in ascending order

b) Combine multiple columns into one

c) Filter records based on a condition

d) Group rows that have the same values in specified columns

Q29. What is the purpose of the SQL command DELETE?

a) Remove a table from the database

b) Remove all records from a table

c) Modify the structure of a table

d) Add a new record to a table

Q30. In Microsoft Access, what is the purpose of the "Parameter Query"?

a) Sort records in ascending order

b) Prompt the user to enter a parameter value during query execution

c) Calculate values based on specified criteria

d) Design printable layouts for presenting data

Q31. Consider a scenario where you have a database with two tables: "Customers" and "Orders." The "Customers" table has a primary key called "CustomerID," and the "Orders" table has a foreign key referencing "CustomerID" in the "Customers" table. What type of relationship is this?

a) One-to-One (1:1)

b) One-to-Many (1:N)

c) Many-to-One (N:1)

d) Many-to-Many (N:N)

Q32. Suppose you want to ensure that the "Email" column in the "Customers" table only contains unique values. Which SQL constraint would you apply?

a) PRIMARY KEY

b) UNIQUE

c) CHECK

d) FOREIGN KEY

Q33. In a database system, explain the significance of the term "Normalization." How does it contribute to efficient data storage and retrieval?

a) It involves sorting data in ascending order for faster retrieval.

b) It organizes data to minimize redundancy and dependency, improving efficiency.

c) It creates complex relationships between tables for better data integrity.

d) It focuses on adding more columns to tables to store diverse data types.

Q34. You are designing a database for an online bookstore. Explain how you would use indexes to enhance the performance of queries related to book titles.

a) Create an index on the "Author" column.

b) Create an index on the "Publication Year" column.

c) Create an index on the "ISBN" column.

d) Create an index on the "Title" column.

Q35. Imagine you are tasked with designing a database for a university that stores student information, course details, and grades. How would you establish a relationship between the "Students" and "Courses" tables?

a) Use a foreign key in the "Students" table referencing the "Courses" table.

b) Use a primary key in the "Students" table referencing the "Courses" table.

c) Create a new table to represent the relationship between students and courses.

d) Use a common column in both tables to establish the relationship.

Q36. Consider a database scenario where you have a table named "Employees" with columns such as "EmployeeID," "FirstName," and "LastName." How would you structure an SQL query to retrieve unique combinations of first and last names?

a) SELECT DISTINCT FirstName, LastName FROM Employees;

b) SELECT FirstName, LastName FROM Employees GROUP BY FirstName, LastName;

c) SELECT UNIQUE FirstName, LastName FROM Employees;

d) SELECT FirstName, LastName FROM Employees WHERE UNIQUE;

Q37. You are tasked with designing a database for a library. How would you structure a query to retrieve books published after the year 2000?

a) SELECT * FROM Books WHERE PublicationYear > 2000;

b) SELECT * FROM Books HAVING PublicationYear > 2000;

c) SELECT * FROM Books ORDER BY PublicationYear DESC;

d) SELECT * FROM Books GROUP BY PublicationYear HAVING PublicationYear > 2000;

Q38. In a database, you have a table named "Products" with columns "ProductID," "ProductName," and "Price." How would you retrieve the top 5 most expensive products?

a) SELECT TOP 5 * FROM Products ORDER BY Price DESC;

b) SELECT * FROM Products ORDER BY Price DESC LIMIT 5;

c) SELECT * FROM Products WHERE ROWNUM <= 5 ORDER BY Price DESC;

d) SELECT * FROM Products LIMIT 5 WHERE ORDER BY Price DESC;

Q39. You are working with a database that stores information about students and their enrolled courses. How would you design a query to find students who have not enrolled in any courses?

a) SELECT * FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID WHERE Enrollments.CourseID IS NULL;

b) SELECT * FROM Students LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID WHERE Enrollments.CourseID IS NULL;

c) SELECT * FROM Students WHERE NOT EXISTS (SELECT * FROM Enrollments WHERE Students.StudentID = Enrollments.StudentID);

d) SELECT * FROM Students EXCEPT SELECT * FROM Enrollments;

Q40. Explain the concept of an SQL subquery and provide an example of how it could be used in a database scenario.

a) A subquery is a query that retrieves data from multiple tables.

b) A subquery is a query nested inside another query and is enclosed in parentheses. It can be used to retrieve data to be used by the main query as a condition.

c) A subquery is a query that contains an aggregate function, such as COUNT or SUM.

d) A subquery is a query that involves sorting data in ascending or descending order.