/* You are working with a `logs` table that contains the following columns:  

| Column Name  | Data Type   | Description                                      |  
|--------------|-------------|--------------------------------------------------|  
| `log_id`     | INT         | Unique ID for each log entry                     |  
| `log_message`| VARCHAR(255)| A message that includes a timestamp in the format `YYYY-MM-DD HH:MM:SS - Message Content` |  
| `log_type`   | VARCHAR(50) | Type of the log (e.g., INFO, ERROR, WARNING)     |  

Write a query to:  
1. Extract the date (`YYYY-MM-DD`) from the `log_message` column.  
2. Extract the hour (`HH`) from the timestamp in the `log_message` column.  
3. Display the extracted date, extracted hour, and the `log_type` for all logs where the `log_type` is 'ERROR'.  

Expected Output:

For a sample log:  
`2024-12-14 15:45:23 - Database connection failed`  

The output should include:  
| Date       | Hour | Log_Type |  
|------------|------|----------|  
| 2024-12-14 | 15   | ERROR    |  
*/

CREATE TABLE logs (  
    log_id INT PRIMARY KEY,  
    log_message VARCHAR(255) NOT NULL,  
    log_type VARCHAR(50) NOT NULL  
);  

INSERT INTO logs (log_id, log_message, log_type) VALUES  
(1, '2024-12-14 15:45:23 - Database connection failed', 'ERROR'),  
(2, '2024-12-14 10:12:34 - User logged in', 'INFO'),  
(3, '2024-12-13 08:30:00 - Disk space running low', 'WARNING'),  
(4, '2024-12-13 22:18:11 - Unauthorized access attempt', 'ERROR'),  
(5, '2024-12-12 09:20:00 - Backup completed successfully', 'INFO'),  
(6, '2024-12-14 01:05:42 - Memory usage critical', 'ERROR'),  
(7, '2024-12-11 23:59:59 - Scheduled maintenance started', 'INFO'),  
(8, '2024-12-12 14:22:10 - Failed to save user preferences', 'ERROR'),  
(9, '2024-12-13 17:15:45 - API request timeout', 'ERROR'),  
(10, '2024-12-14 18:30:00 - Service unavailable due to high traffic', 'ERROR'),  
(11, '2024-12-12 11:45:20 - Email server connection lost', 'ERROR'),  
(12, '2024-12-11 03:55:15 - Data replication failure', 'ERROR');  

SELECT
    DATE(CAST(SUBSTRING(log_message, 1, 19) AS DATETIME)) AS Date,
    HOUR(CAST(SUBSTRING(log_message, 1, 19) AS DATETIME)) AS Hour,
    log_type
FROM logs
WHERE log_type = 'ERROR'
ORDER BY Date, Hour;