- How does SQLite differ from other relational database management systems?
- Can you explain the ACID properties in SQLite?
- What is a table in SQLite?
- How do you create a table in SQLite?
- What is a primary key in SQLite?
- How do you specify a primary key in a table?
- What is a foreign key in SQLite?
- How do you specify a foreign key in a table?
- How do you insert data into a table in SQLite?
- How do you update data in a table in SQLite?
- How do you delete data from a table in SQLite?
- What is a trigger in SQLite?
- How do you create a trigger in SQLite?
- What is a view in SQLite?
- How do you create a view in SQLite?
- How do you select data from a table in SQLite?
- What is the LIKE operator in SQLite?
- How do you use the LIKE operator in SQLite?
- What is a transaction in SQLite?
- How do you begin a transaction in SQLite?
- How do you commit a transaction in SQLite?
- How do you roll back a transaction in SQLite?
- What is indexing in SQLite?
- How do you create an index in SQLite?
- What is a schema in SQLite?
- How do you create a schema in SQLite?
- What is a constraint in SQLite?
- How do you add a constraint to a table in SQLite?
- What is the difference between a NOT NULL and a NULL constraint in SQLite?
- What is the difference between INTEGER and NUMERIC data types in SQLite?
- What is a BLOB data type in SQLite?
- How do you insert BLOB data into a table in SQLite?
- What is a trigger in SQLite?
- How do you write a trigger in SQLite?
- You have a table named "Employees" with the following columns: "ID", "Name", and "Salary". How do you add a primary key constraint to the "ID" column?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", and "OrderDate". How do you add a foreign key constraint to the "CustomerID" column that references the "Customers" table?
- You need to insert a new row into a table named "Products". The "ProductID" column is an integer primary key, and the "ProductName" column is a string. How do you write the SQL statement to insert the new row?
- You have a table named "Customers" with the following columns: "CustomerID", "CustomerName", and "Country". How do you update the "Country" column for a specific customer with the "CustomerID" value of 123?
- You have a table named "Employees" with the following columns: "ID", "Name", and "Salary". How do you delete all rows from the table where the "Salary" column is less than 50000?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", and "OrderDate". How do you write a SELECT statement to retrieve all orders placed by a specific customer with the "CustomerID" value of 123?
- You need to create a view named "HighSalaries" that displays the names of all employees who earn a salary of more than 75000. How do you write the SQL statement to create the view?
- You have a table named "Products" with the following columns: "ProductID", "ProductName", and "Price". How do you write a SELECT statement to retrieve all products whose names contain the word "apple"?
- You have a table named "Employees" with the following columns: "ID", "Name", and "Salary". How do you write a SELECT statement to retrieve the highest salary in the table?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", and "OrderDate". How do you write a SELECT statement to retrieve the total number of orders placed by each customer?
- What is a subquery in SQLite?
- How do you write a subquery in SQLite?
- What is a correlated subquery in SQLite?
- How do you write a correlated subquery in SQLite?
- What is a join in SQLite?
- How do you perform an inner join in SQLite?
- How do you perform a left outer join in SQLite?
- How do you perform a right outer join in SQLite?
- How do you perform a full outer join in SQLite?
- What is a UNION in SQLite?
- How do you perform a UNION in SQLite?
- What is a UNION ALL in SQLite?
- How do you perform a UNION ALL in SQLite?
- What is a CTE in SQLite?
- How do you write a CTE in SQLite?
- What is the difference between a temporary table and a CTE in SQLite?
- What is a recursive CTE in SQLite?
- How do you write a recursive CTE in SQLite?
- What is the difference between a correlated subquery and a CTE in SQLite?
- What is a virtual table in SQLite?
- How do you create a virtual table in SQLite?
- What is an FTS table in SQLite?
- How do you create an FTS table in SQLite?
- What is an index on an expression in SQLite?
- How do you create an index on an expression in SQLite?
- What is a window function in SQLite?
- How do you use a window function in SQLite?
- What is the difference between the ROW_NUMBER and RANK functions in SQLite?
- What is a scalar subquery in SQLite?
- How do you write a scalar subquery in SQLite?
- What is a common table expression (CTE) in SQLite?
- How do you write a recursive CTE in SQLite?
- What is a table-valued function in SQLite?
- How do you create a table-valued function in SQLite?
- What is the difference between a scalar function and a table-valued function in SQLite?
- You have a table named "Customers" with the following columns: "CustomerID", "CustomerName", and "Country". How do you write a SELECT statement to retrieve all customers who live in either the United States or Canada?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", and "OrderDate". How do you write a SELECT statement to retrieve the most recent order placed by each customer?
- You have a table named "Employees" with the following columns: "ID", "Name", and "Salary". How do you write a SELECT statement to retrieve the top 10 highest-paid employees?
- You have a table named "Products" with the following columns: "ProductID", "ProductName", and "Price". How do you write a SELECT statement to retrieve the average price of all products?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", and "OrderDate". How do you write a SELECT statement to retrieve the total number of orders placed in each year?
- You need to create a view named "OrdersWithCustomers" that displays the "OrderID", "OrderDate", "CustomerName", and "Country" for each order. How do you write the SQL statement to create the view?
- You have a table named "Employees" with the following columns: "ID", "Name", and "Salary". You need to write a trigger that automatically inserts a row into a "SalaryChanges" table whenever an employee's salary is updated. How do you write the trigger?
- You have a table named "Customers" with the following columns: "CustomerID", "CustomerName", and "Country". You need to write a stored procedure that returns the total number of customers in each country. How do you write the stored procedure?
- You have a table named "Products" with the following columns: "ProductID", "ProductName", and "Price". You need to create an index on the "Price" column to improve query performance. How do you create the index?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", and "OrderDate". You need to write a SELECT statement that retrieves the top 5 customers who have placed the most orders.
- You have a table named "Employees" with the following columns: "ID", "Name", "Department", and "Salary". You need to write a stored procedure that returns the average salary for each department. How do you write the stored procedure?
- You have a table named "Customers" with the following columns: "CustomerID", "CustomerName", and "Country". You need to write a SELECT statement that retrieves the top 10 countries with the most customers.
- You have a table named "Products" with the following columns: "ProductID", "ProductName", and "Price". You need to create a trigger that automatically updates the "Price" column to 0.00 whenever a product is discontinued. How do you write the trigger?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", "OrderDate", and "TotalAmount". You need to write a SELECT statement that retrieves the top 10 orders with the highest total amount.
- You have a table named "Employees" with the following columns: "ID", "Name", "Department", and "Salary". You need to write a stored procedure that returns the name and salary of the highest-paid employee in each department. How do you write the stored procedure?
- What is the WAL mode in SQLite?
- How do you enable the WAL mode in SQLite?
- What is the difference between WAL and journal mode in SQLite?
- What is the role of the write-ahead log in SQLite?
- How does the write-ahead log affect performance in SQLite?
- What is a savepoint in SQLite?
- How do you create a savepoint in SQLite?
- How do you release a savepoint in SQLite?
- What is a multi-version concurrency control (MVCC) in SQLite?
- How does MVCC work in SQLite?
- What is the difference between pessimistic and optimistic locking in SQLite?
- How does SQLite handle concurrency?
- What is the role of the shared cache in SQLite?
- How do you enable the shared cache in SQLite?
- What is the difference between a shared cache and a private cache in SQLite?
- What is the page cache in SQLite?
- How does the page cache work in SQLite?
- What is the difference between the page cache and the buffer pool in SQLite?
- What is a collation in SQLite?
- How do you create a custom collation in SQLite?
- What is the PRAGMA statement in SQLite?
- How do you use the PRAGMA statement in SQLite?
- What is the purpose of the ANALYZE command in SQLite?
- How do you use the ANALYZE command in SQLite?
- What is the difference between the EXPLAIN and EXPLAIN QUERY PLAN commands in SQLite?
- What is the purpose of the VACUUM command in SQLite?
- How does the VACUUM command work in SQLite?
- What is the difference between the VACUUM and VACUUM INTO commands in SQLite?
- What is the purpose of the ANALYZE command in SQLite?
- How does the ANALYZE command work in SQLite?
- What is the purpose of the PRAGMA journal_mode command in SQLite?
- What is the purpose of the PRAGMA locking_mode command in SQLite?
- How does the PRAGMA cache_size command work in SQLite?
- What is the purpose of the PRAGMA temp_store command in SQLite?
- How does the PRAGMA secure_delete command work in SQLite?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", "OrderDate", and "TotalAmount". You need to write a stored procedure that returns the total revenue for each month of the current year. How do you write the stored procedure?
- You have a table named "Customers" with the following columns: "CustomerID", "CustomerName", and "Country". You need to write a SELECT statement that retrieves the top 5 countries with the highest average order amount.
- You have a table named "Products" with the following columns: "ProductID", "ProductName", "Category", and "Price". You need to create a full-text search index on the "ProductName" column to improve search performance. How do you create the index?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", "OrderDate", and "TotalAmount". You need to write a SELECT statement that retrieves the top 10 customers who have spent the most money.
- You have a table named "Employees" with the following columns: "ID", "Name", "Department", and "Salary". You need to write a stored procedure that returns the average salary for each department, but excludes the salaries of the highest-paid employee in each department. How do you write the stored procedure?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", "OrderDate", and "TotalAmount". You need to create a materialized view that displays the total revenue for each month of the current year. How do you create the materialized view?
- You have a table named "Employees" with the following columns: "ID", "Name", "Department", and "Salary". You need to write a stored procedure that returns the name and salary of the second-highest-paid employee in each department. How do you write the stored procedure?
- You have a table named "Customers" with the following columns: "CustomerID", "CustomerName", and "Country". You need to write a SELECT statement that retrieves the top 5 countries with the highest total revenue, but excludes the revenue from the top 10 customers in each country.
- You have a table named "Products" with the following columns: "ProductID", "ProductName", "Category", and "Price". You need to write a stored procedure that returns the top 5 products in each category, based on the total sales for each product. How do you write the stored procedure?
- You have a table named "Orders" with the following columns: "OrderID", "CustomerID", "OrderDate", and "TotalAmount". You need to create a trigger that automatically updates the "TotalAmount" column whenever a new order is inserted into the table. The new value should be the sum of the prices of all products in the order. How do you write the trigger?
- You have a table named "Employees" with the following columns: "ID", "Name", "Department", and "Salary". You need to write a SELECT statement that retrieves the top 5 departments with the highest total salary.
- You have a table named "Customers" with the following columns: "CustomerID", "CustomerName", and "Country". You need to write a stored procedure that returns the top 5 customers in each country, based on the total amount they have spent. How do you write the stored procedure?
- You have a table named "Products" with the following columns: "ProductID", "ProductName", "Category", and "Price". You need to write a SELECT statement that retrieves the top 10 products with the highest total sales, but excludes the sales of the top 5 customers who have purchased each product.