71. Write a query to calculate publication and cancellation rate for each user from a table with user IDs, actions, and dates.
Answer:
To calculate publication and cancellation rate per user, assume the table is structured like:
sql
CREATE TABLE UserActions (
user_id INT,
action VARCHAR(50), — e.g., ‘publish’, ‘cancel’
action_date DATE
);
Here is the SQL query:
sql
CopyEdit
SELECT
user_id,
ROUND(SUM(CASE WHEN action = ‘publish’ THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 2) AS publish_rate,
ROUND(SUM(CASE WHEN action = ‘cancel’ THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 2) AS cancel_rate
FROM
UserActions
GROUP BY
user_id;
Explanation:
- This query uses conditional aggregation to count publish and cancel actions.
- We divide each count by the total number of actions per user.
- ROUND(…, 2) is used to limit the result to two decimal points for readability.
72. Write a query to calculate net worth change per user from a transaction table and order by net change descending.
Answer:
Assume a table structure like:
sql
CREATE TABLE Transactions (
sender_id INT,
receiver_id INT,
amount DECIMAL(10,2),
transaction_date DATE
);
Here’s the query:
sql
SELECT
user_id,
SUM(net_change) AS net_worth_change
FROM (
SELECT
sender_id AS user_id,
-amount AS net_change
FROM
Transactions
UNION ALL
SELECT
receiver_id AS user_id,
amount AS net_change
FROM
Transactions
) AS NetChanges
GROUP BY
user_id
ORDER BY
net_worth_change DESC;
Explanation:
- This query calculates how much money each user lost (sender) or gained (receiver).
- UNION ALL is used to combine both views of the transaction.
- Final aggregation groups all changes per user and sorts them in descending order.
73–75. (Placeholder: Let me know the actual questions and I’ll generate advanced answers.)
76. What is the SELECT statement?
Answer:
The SELECT statement is the most fundamental SQL command used to query and retrieve data from one or more tables in a relational database. It allows developers to extract specific rows and columns using conditions, joins, grouping, and sorting.
✅ Basic Syntax:
sql
SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column
ORDER BY column;
Advanced Usage Includes:
- JOINs to combine tables.
- GROUP BY for aggregations.
- Subqueries for nested logic.
- Window functions like ROW_NUMBER(), RANK().
77. What is an Alias in SQL?
Answer:
An alias is a temporary name given to a table or column in a query to make the output more readable or to simplify long expressions.
Column Alias Example:
sql
SELECT first_name AS “First Name”, salary * 12 AS “Annual Salary”
FROM employees;
Table Alias Example:
sql
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
Advanced Tip:
Aliases are especially useful in complex subqueries and self-joins. Using descriptive aliases enhances code readability and maintainability.
78. What is Denormalization in SQL?
Answer:
Denormalization is the process of intentionally introducing redundancy into a normalized database structure to improve read performance.
Why Denormalize?
- To reduce expensive JOIN operations.
- To optimize complex read-heavy applications like dashboards or reporting systems.
Example: Instead of joining orders and customers every time, customer info like name and email can be duplicated into the orders table.
Trade-off:
- Increases data redundancy.
- Makes data maintenance harder during updates.
- Suitable for data warehouses and OLAP systems, but not ideal for transactional OLTP systems.
79. What is the difference between DROP and TRUNCATE in SQL?
| Feature | DROP | TRUNCATE |
| Purpose | Deletes the table structure | Deletes all rows in the table |
| Table existence | Table is removed completely | Table structure remains |
| Rollback Support | Cannot be rolled back | Can be rolled back in some RDBMS |
| Triggers | Triggers do not fire | Triggers do not fire |
| Speed | Slower (removes dependencies) | Faster (minimal logging) |
80. What is the difference between DELETE and TRUNCATE in SQL?
Answer:
| Feature | DELETE | TRUNCATE |
| Condition Support | Can use WHERE clause | Deletes all rows, no condition |
| Rollback Support | Fully rollback-supported | Rollback depends on DB system |
| Logging | Fully logged | Minimal logging |
| Triggers | Triggers fire | Triggers do NOT fire |
| Speed | Slower for large datasets | Much faster |
Use DELETE:
- When you need to selectively remove data.
- When using triggers or need rollback.
Use TRUNCATE:
- When you want to quickly empty a table.
81. What do you mean by Data Integrity in SQL?
Answer:
Data Integrity ensures the accuracy, consistency, and reliability of data within a relational database. It enforces rules and constraints during data input, update, or deletion to prevent incorrect or unauthorized data. Key types of data integrity include:
- Entity Integrity: Ensures each row in a table is uniquely identified using a Primary Key.
- Referential Integrity: Ensures relationships between tables remain consistent using Foreign Keys.
- Domain Integrity: Ensures data entries are valid based on data type, format, or range.
- User-Defined Integrity: Involves custom business rules and logic defined by the user.
82. How can you fetch the first 5 characters of a string in SQL?
Answer:
To fetch the first 5 characters of a string column in SQL, you can use the SUBSTRING() or LEFT() function:
sql
SELECT SUBSTRING(StudentName, 1, 5) AS ShortName FROM Students;
— OR
SELECT LEFT(StudentName, 5) AS ShortName FROM Students;
These functions are useful in formatting reports, masking data, or shortening string output.
83. What is a Stored Procedure in SQL?
Answer:
A Stored Procedure is a precompiled collection of one or more SQL statements that perform a task. It’s stored in the database and can be executed multiple times.
Advantages:
- Increases performance by reducing network traffic.
- Promotes code reuse.
- Enhances security using permission-based execution.
Example:
sql
CREATE PROCEDURE GetStudentsByCourse
@CourseName VARCHAR(50)
AS
BEGIN
SELECT * FROM Students WHERE Course = @CourseName;
END;
Call with:
sql
EXEC GetStudentsByCourse ‘SQL Programming’;
84. What is Collation in SQL?
Answer:
Collation refers to a set of rules that determine how string comparison and sorting are performed in SQL. It affects:
- Case sensitivity
- Accent sensitivity
- Character width
Example:
sql
SELECT * FROM Users WHERE Name COLLATE Latin1_General_CS_AS = ‘John’;
This query ensures the name ‘John’ is treated case-sensitively.
85. What is a Data Warehouse?
Answer:
A Data Warehouse is a central repository that stores data collected from multiple sources for analytical and reporting purposes.
- Supports data mining, OLAP, and BI tools.
- Contains Data Marts (subject-specific views).
- Follows ETL (Extract, Transform, Load) processes.
Use Case:
Businesses use data warehouses to analyze sales trends, customer behavior, and financial performance.
The Next 15 Questions-III: SQL
