SQL - Page 2 of 3 - Code Snippets

Switch case (INSERT)

Snippets

INSERT INTO c_office

(office_code,

parent_code)

SELECT DISTINCT office_code,

CASE

WHEN Char_length (office_code) = 4 THEN

Substring (office_code, 1, 2)

WHEN Char_length (office_code) = 7 THEN

Substring (office_code, 1, 4)

WHEN Char_length (office_code) = 10 THEN

Substring (office_code, 1, 7)

END

FROM office_user

SELECT

(CASE

WHEN Substring (dsor_code, 1, 2) = 'MR' THEN

Substring (dsor_code from 3)

WHEN Substring (dsor_code, 1, 2) = 'KW' THEN

Substring (dsor_code from 3)

ELSE

Substring (dsor_code from 1)

END)::Integer

AS dorder,

Substring (dsor_code, 1, 2) as dtype,

dsor_year, dsortype_code, dsor_code, dsor_description, unit_code, rate

FROM m_dsor WHERE dsor_year=2014 and dsortype_code=5 ORDER BY dtype, dorder

KILL ALL EXISTING CONNECTION FROM DB – PostgreSQL

Snippets

1 2	SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();

Creating a copy of a database in PostgreSQL

Snippets

1	CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Backup & Restore Database in PostgreSQL

Snippets

1) Backup data with pg_dump

1 2	pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "/usr/local/backup/10.70.0.61.backup" old_db

To list all of the available options of pg_dump , please issue following command.

pg_dump -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt

How to Back Up and Restore a MySQL Database

Snippets

If you’re storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss. This tutorial will show you two easy ways to backup and restore the data in your MySQL database. You can also

MySql Login, Database & User Creation

Snippets

Step 1: Login to MySQL ( you will need an account )

1	mysql -u [user] -p

Enter password:- Enter your mysql password

Step 2: Create the Database

1	mysql> create database [database_name];

Step 3: Verify that it’s there

1	mysql> show databases;

Step 4: Create the User

1	mysql> CREATE USER [new_user]@[hostname] IDENTIFIED BY [password];

Example:- CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password123';

Step 5: Grant privileges while assigning the password

1	mysql> GRANT ALL PRIVILEGES ON [database] . * TO [new_user]@[hostname];

Example:- GRANT ALL



                
										
                				
                    
                        
							                            Concatenate multiple MySQL rows into one field
                        
                                                   
							 
                
		
	
		
	
	
	
						 Snippets
				
		
	
	
	                                
                        
                    
					
 
 
 
 
 
 
 
 
 
 
 
 
 




	


					
					


		
		
			SELECT SC.StudentID, GROUP_CONCAT((SELECT C.CourseName FROM courses C WHERE C.CourseID=SC.CourseID) SEPARATOR ', ') FROM studentcourses SC GROUP BY SC.StudentID
			
				
					
				
					1
				
						SELECT SC.StudentID, GROUP_CONCAT((SELECT C.CourseName FROM courses C WHERE C.CourseID=SC.CourseID) SEPARATOR ', ') FROM studentcourses SC GROUP BY SC.StudentID
					
				
			
		

 
															
                

                
										
                				
                    
                        
							                            SQL Introduction
                        
                                                   
							 
                
		
	
		
	
	
	
						 Snippets
				
		
	
	
	                                
                        
                    
					
 
 
 
 
 
 
 
 
 
 
 
 
 




	


					
					SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language.
SQL

Allows users to access data in relational database management systems.
Allows users to describe the data.
Allows users to define the data in database and manipulate that data.
Allows to embed															
                

                
										
                				
                    
                        
							                            MySQL Search and Replace Query
                        
                                                   
							 
                
		
	
		
	
	
	
						 Snippets
				
		
	
	
	                                
                        
                    
					
 
 
 
 
 
 
 
 
 
 
 
 
 




	


					
					


		
		
			UPDATE  `MySQL_Table` SET  `MySQL_Table_Column` = REPLACE(`MySQL_Table_Column`, 'oldString', 'newString') WHERE  `MySQL_Table_Column` LIKE 'oldString%';
			
				
					
				
					1
				
						UPDATE  `MySQL_Table` SET  `MySQL_Table_Column` = REPLACE(`MySQL_Table_Column`, 'oldString', 'newString') WHERE  `MySQL_Table_Column` LIKE 'oldString%';
					
				
			
		

 
															
                

                
										
                				
                    
                        
							                            UPDATE query based on SELECT Query
                        
                                                   
							 
                
		
	
		
	
	
	
						 Snippets
				
		
	
	
	                                
                        
                    
					
 
 
 
 
 
 
 
 
 
 
 
 
 




	


					
					MySQL update join syntax:


		
		
			update tableA a
left join tableB b on a.name_a = b.name_b
set validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here
			
				
					
				
					1
2
3
4
				
						update tableA a
left join tableB b on a.name_a = b.name_b
set validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here
					
				
			
		

ANSI SQL syntax:


		
		
			update tableA set validation_check = 
    (SELECT if(start_DTS > end_DTS,'VALID','') as validation_check
        FROM tableA
        LEFT JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)
			
				
					
				
					1
2
3
4
5
				
						update tableA set validation_check = 
    (SELECT if(start_DTS > end_DTS,'VALID','') as validation_check
        FROM tableA
        LEFT JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)
					
				
			
		

or


		
		
			UPDATE [table_name] AS T1,
      (SELECT [column_name] 
        FROM [table_name] 
        WHERE [column_name] = [value]) AS T2 
  SET T1.[column_name]=T2.[column_name] + 1
WHERE T1.[column_name] = [value];
			
				
					
				
					1
2
3
4
5
6
				
						UPDATE [table_name] AS T1,
      (SELECT [column_name] 
        FROM [table_name] 
        WHERE [column_name] = [value]) AS T2 
  SET T1.[column_name]=T2.[column_name] + 1
WHERE T1.[column_name] = [value];
					
				
			
		

 
															
                

                
								                
								
				
                    Load More