NEW
Font size
WorksheetsDatabase final
Total questions: 60
Worksheet time: 30mins
Which of the following is NOT a standard SQL isolation level?
Serializable
Repeatable Read
Read Exclusive
Read Committed
Analyze the statement: ALTER TABLE orders RENAME COLUMN cust_id TO customer_id;. What is its effect?
Renames the 'orders' table to 'customer_id'
Renames the 'cust_id' column to 'customer_id' in the 'orders' table
Copies data from 'cust_id' to a new column 'customer_id'
Changes the data type of the 'cust_id' column to 'customer_id'
Which property of ACID transactions ensures that all parts of a transaction are treated as a single unit?
Atomicity
Durability
Consistency
Isolation
Consider the statement: CREATE TABLE students (id SERIAL PRIMARY KEY, name VARCHAR(50)); What does the 'SERIAL' keyword indicate?
The 'id' column will have sequential characters
The 'id' column will generate random numbers
The 'id' column will store serial data formats
The 'id' column will auto-increment for each new record
What does the following SQL query return? SELECT COUNT(DISTINCT CustomerID) FROM Orders WHERE OrderDate BETWEEN '2021-01-01' AND '2021-12-31';
Total number of distinct customers who placed orders in 2021
Unique customer IDs from 2021
Count of all orders placed in 2021
Total number of orders in 2021
Which isolation level is the least restrictive, allowing the highest level of concurrency in transactions?
Read Committed
Repeatable Read
Serializable
Read Uncommitted
How would you create a copy of an existing table including only its structure but no data in PostgreSQL?
CREATE TABLE new_table COPY FROM old_table;
CREATE TABLE new_table LIKE old_table;
CREATE TABLE new_table DUPLICATE old_table;
CREATE TABLE new_table AS TABLE old_table;
How are SQL triggers typically used?
To monitor changes in user access patterns and optimize queries.
To back up data whenever a table is modified.
To automate the execution of a stored procedure at a scheduled time.
To perform a set of tasks before or after changes are committed to a table.
Which JOIN operation can potentially return the largest number of rows?
FULL OUTER JOIN
INNER JOIN
LEFT JOIN
CROSS JOIN
Consider two tables, A and B. Table A has 5 rows and table B has 3 rows. How many rows will result from a CROSS JOIN of A and B?
8
15
3
5
What does the EXISTS SQL clause do?
Ensures that all rows meet the specified condition
Validates the existence of a database or table
Tests whether a subquery returns any rows
Checks for the existence of a specific value in a column
How will the following query affect the output? SELECT CustomerID, COUNT() FROM Orders GROUP BY CustomerID HAVING COUNT() > 5;
It will display customers with less than 5 orders
It will list all customers who have more than 5 orders
It will count orders for each customer
It will show all orders
What is the role of an INOUT parameter in a stored procedure?
It is used to input values into a trigger.
It is used to pass a value into the procedure and return a modified value.
It allows the procedure to output multiple values.
It serves as a constant value that cannot be changed.
How does the REFRESH MATERIALIZED VIEW command work in SQL?
It refreshes the user's view permissions on the materialized view.
It updates the view's definition in the database schema.
It re-executes the view's query and updates the stored data.
It checks the view for any syntactical errors.
Which of the following statements is true about table inheritance in PostgreSQL?
A child table inherits only the data of its parent table
Child tables can add their own columns in addition to inherited ones
Modifications in the parent table structure do not affect child tables
Child tables cannot have their own indexes
Which of the following SQL statements correctly demonstrates the use of the HAVING clause?
HAVING Salary > 50000 SELECT * FROM Employees
SELECT COUNT(EmployeeID), Department FROM Employees GROUP BY Department HAVING COUNT(EmployeeID) > 10
SELECT * FROM Employees HAVING Salary > 50000
SELECT Department FROM Employees HAVING COUNT(EmployeeID) > 10
What will the following query display? SELECT ProductName FROM Products WHERE Price > ANY (SELECT Price FROM Products WHERE ProductName = 'Pen');
Names of all products more expensive than the pen
Names of products cheaper than the pen
The price of the pen
Names of all products
Which command is used to remove a table in PostgreSQL?
DROP TABLE
ERASE TABLE
REMOVE TABLE
DELETE TABLE
What happens if you try to update a column through a view that is not part of the view's SELECT statement?
The update will succeed, and the base table will be updated.
The view will be automatically expanded to include the column.
The update will be ignored, but no error will be thrown.
The update will fail because the column is not part of the view.
What is a composite index in a database?
An index that includes all columns of a table.
An index created on multiple columns of a table
An index shared by multiple tables.
A combination of a unique index and a primary index.
Which SQL statement finds the total number of orders for each customer?
SELECT COUNT(OrderID), CustomerID FROM Orders;
SELECT CustomerID FROM Orders COUNT(OrderID);
SELECT OrderID, COUNT(CustomerID) FROM Orders;
SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID;
Which SQL statement correctly performs a LEFT JOIN between "Customers" and "Orders" where some customers may not have orders?
SELECT * FROM Orders LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID
SELECT * FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
SELECT * FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID
SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Which SQL statement is used to start a database transaction?
BEGIN TRANSACTION
CREATE TRANSACTION
START TRANSACTION
INIT TRANSACTION
In PostgreSQL, what does the following command achieve? CREATE TABLE employees_backup (LIKE employees INCLUDING ALL);
Creates 'employees_backup' with additional constraints and indexes from 'employees'
Renames 'employees' to 'employees_backup'
Clones only the structure of 'employees' to 'employees_backup'
Copies both the structure and data of 'employees' to 'employees_backup'
Which SQL clause is used to filter rows after the JOIN has been performed?
WHERE
FILTER
ON
HAVING
What is the primary role of 'REFERENCES' in a 'CREATE TABLE' statement in PostgreSQL?
To reference a unique constraint in the same table
To reference the default value of a column
To create a reference to a column in the same table
To establish a foreign key relationship
What is a trigger in SQL?
A command used to start a stored procedure.
A stored procedure that runs automatically in response to certain events on a particular table or view.
A tool to monitor changes in database performance.
A function that triggers an alert when data is modified.
Which statement about the ORDER BY clause is true?
It filters the results based on a condition
It sorts the result set in ascending or descending order
It selects unique records
It groups the result set into summary rows
In PostgreSQL, how can you create a table 'department' that is a copy of an existing table 'employees'?
COPY TABLE department FROM employees;
CREATE TABLE department (LIKE employees);
DUPLICATE TABLE employees TO department;
CREATE TABLE department COPY * FROM employees;
What does the RIGHT JOIN operation do?
Returns all rows from the right table and matched rows from the left table.
Returns all rows from the left table and matched rows from the right table.
Combines all rows from both tables.
Returns only matching rows from both tables.
In SQL, how can EXPLAIN be used to optimize a query?
It can automatically modify the query for better performance.
It increases the speed of query execution.
It reduces the amount of data scanned by the query.
It provides an estimated cost of the query, helping to identify slow parts of the query.
What is the purpose of CREATE TABLE ... INHERITS in PostgreSQL?
To duplicate a table's data and structure
To rename an existing table
To create a backup of a table
To create a table that inherits properties of another table
In a LEFT JOIN operation, which table is the primary table that retains all its rows in the result set?
The left table
The right table
Both tables
Neither table
What is the correct syntax to create a new table in PostgreSQL with the same indexes as an existing table?
CREATE TABLE new_table AS SELECT * FROM old_table WITH INDEXES;
CREATE TABLE new_table INHERIT old_table INCLUDING INDEXES;
CREATE TABLE new_table (LIKE old_table INCLUDING INDEXES);
CREATE TABLE new_table DUPLICATE old_table INDEXES;
In SQL, which command is used to update the data in a materialized view?
RENEW MATERIALIZED VIEW
REBUILD MATERIALIZED VIEW
UPDATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
What is a self-join in SQL?
Joining a table with itself.
Joining a table with its copy.
Joining a table with a subquery.
Joining two different tables.
What is the primary benefit of using a view in a database?
It speeds up data insertion.
It increases query performance.
It reduces storage space.
It simplifies complex queries.
Analyze this command: DROP TABLE IF EXISTS inventory;. What is its purpose?
To delete the 'inventory' table if it does not exist
To remove the 'inventory' table if it exists
To create an 'inventory' table if it does not exist
To rename an existing 'inventory' table
What is the primary purpose of the EXPLAIN command in SQL?
To fix errors in SQL queries.
To optimize and rewrite inefficient queries automatically.
To display the query execution plan without actually executing the query.
To execute a query and provide a detailed report of the execution plan.
Which type of JOIN returns only the rows that have matching values in both tables?
LEFT JOIN
INNER JOIN
RIGHT JOIN
FULL OUTER JOIN
Which SQL statement finds the total number of orders for each customer?
SELECT COUNT(OrderID), CustomerID FROM Orders;
SELECT OrderID, COUNT (CustomerID) FROM Orders;
SELECT CustomerID, COUNT (OrderID) FROM Orders GROUP BY CustomerID;
SELECT CustomerID FROM Orders COUNT(OrderID);
What is a composite index in a database?
A combination of a unique index and a primary index.
An index created on multiple columns of a table
An index that includes all columns of a table.
An index shared by multiple tables.
In PostgreSQL, what does the following command achieve? CREATE TABLE employees_backup (LIKE employees INCLUDING ALL);
Copies both the structure and data of 'employees' to 'employees_backup'
Renames 'employees' to 'employees_backup'
Clones only the structure of 'employees' to 'employees_backup'
Creates 'employees_backup' with additional constraints and indexes from 'employees'
In SQL, which command is used to update the data in a materialized view?
RENEW MATERIALIZED VIEW
UPDATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
REBUILD MATERIALIZED VIEW
Which SQL statement correctly performs a LEFT JOIN between "Customers" and "Orders" where some customers may not have orders?
SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
SELECT * FROM Orders LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID
SELECT * FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
SELECT * FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID
What is the primary purpose of the EXPLAIN command in SQL?
To display the query execution plan without actually executing the query.
To fix errors in SQL queries.
To execute a query and provide a detailed report of the execution plan.
To optimize and rewrite inefficient queries automatically
What is the correct syntax to create a new table in PostgreSQL with the same indexes as an existing table?
CREATE TABLE new_table INHERIT old_table INCLUDING INDEXES;
CREATE TABLE new_table AS SELECT * FROM old_table WITH INDEXES;
CREATE TABLE new_table (LIKE old_table INCLUDING INDEXES);
CREATE TABLE new_table DUPLICATE old_table INDEXES;
What happens if you try to update a column through a view that is not part of the view's SELECT statement?
The view will be automatically expanded to include the column.
The update will succeed, and the base table will be updated.
The update will be ignored, but no error will be thrown.
The update will fail because the column is not part of the view.
In a LEFT JOIN operation, which table is the primary table that retains all its rows in the result set?
The right table
The left table
Both tables
Neither table
In SQL, how can EXPLAIN be used to optimize a query?
It can automatically modify the query for better performance.
It provides an estimated cost of the query, helping to identify slow parts of the query.
It reduces the amount of data scanned by the query.
It increases the speed of query execution.
What is a self-join in SQL?
Joining a table with its copy.
Joining a table with itself.
Joining two different tables.
Joining a table with a subquery.
Analyze this command: DROP TABLE IF EXISTS inventory;. What is its purpose?
To remove the 'inventory' table if it exists
To create an 'inventory' table if it does not exist
To delete the 'inventory' table if it does not exist
To rename an existing 'inventory' table
Which command is used to remove a table in PostgreSQL?
ERASE TABLE
DROP TABLE
REMOVE TABLE
DELETE TABLE
Which of the following statements is true about table inheritance in PostgreSQL?
A child table inherits only the data of its parent table
Child tables cannot have their own indexes
Child tables can add their own columns in addition to inherited ones
Modifications in the parent table structure do not affect child tables
Which SQL statement is used to start a database transaction?
INIT TRANSACTION
BEGIN TRANSACTION
CREATE TRANSACTION
START TRANSACTION
Which of the following SQL statements correctly demonstrates the use of the HAVING clause?
SELECT COUNT(EmployeeID), Department FROM Employees GROUP BY Department HAVING COUNT (EmployeeID) >10
SELECT Department FROM Employees HAVING COUNT (EmployeelD) > 10
HAVING Salary > 50000 SELECT * FROM Employees
SELECT * FROM Employees HAVING Salary > 50000
What is the primary benefit of using a view in a database?
It increases query performance.
It speeds up data insertion.
It simplifies complex queries.
It reduces storage space.
Which type of JOIN returns only the rows that have matching values in both tables?
FULL OUTER JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN
What will the following query display? SELECT ProductName FROM Products WHERE Price > ANY (SELECT Price FROM Products WHERE ProductName = 'Pen');
The price of the pen
Names of products cheaper than the pen
Names of all products more expensive than the pen
Names of all products
What is the purpose of CREATE TABLE ... INHERITS in PostgreSQL?
To create a table that inherits properties of another table
To rename an existing table
To create a backup of a table
To duplicate a table's data and structure
