SELECT
place,
cnt,
sum_
FROM (
SELECT
place,
count(*) as cnt,
sum(weight) as sum_
FROM a
GROUP BY place
) a
ORDER BY place;
Here is what the above code is Doing:
1. The first subquery is grouping the data by place and calculating the count and sum of weight.
2. The second subquery is ordering the data by place.
The result is:
place cnt sum_
A 2 3
B 1 1
C 1 2
SELECT IFNULL(Price, 0) FROM Products; SELECT COALESCE(Price, 0) FROM Products; — Oracle (extra): SELECT NVL(Price, 0) FROM Products; Here is what the above code is Doing: 1. The first SELECT statement returns the Price column as is. 2. The second SELECT statement returns 0 if the Price column is NULL. 3. The third SELECT statement…
select schema_name(t.schema_id) as schema_name, t.name as table_name, t.create_date, t.modify_date from sys.tables t where schema_name(t.schema_id) = ‘Production’ — put schema name here order by table_name; Here is what the above code is Doing: 1. It’s using the sys.tables system view to get a list of all tables in the database. 2. It’s using the schema_name() function…
after logging into your mysql, run the following comaand mysql> “select @@version;” // the command is in green Here is what the above code is Doing: 1. We are importing the mysql.connector module. 2. We are creating a connection to the database. 3. We are creating a cursor object. 4. We are executing the query….
$ mysql -u root -p Enter password: (enter your root password) mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘your_new_password’; mysql> FLUSH PRIVILEGES; mysql> quit Here is what the above code is Doing: 1. ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘your_new_password’; This command will change the authentication method for the root user from auth_socket…
SELECT CURRENT_DATE – INTERVAL 30 DAY Here is what the above code is Doing: 1. We’re selecting the date column from the table. 2. We’re using the DATE_SUB function to subtract 30 days from the date column. 3. We’re using the CAST function to convert the date column to a string. 4. We’re using the…
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.dbid = DB_ID(‘msdb’) ORDER BY deqs.last_execution_time DESC SELECT dest.TEXT AS [Query], deqs.execution_count [Count], deqs.last_execution_time AS [Time] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC Here is what the above code is Doing:…