Running SQL Script

In this article, we will learn how to run a SQL script. Running SQL Script is not too tough, you can follow our procedures to run it easily.

What is SQL

What is SQL? So, SQL is a list of commands saved as a SQL script file. You can use the SQL scripts to create edit, delete, and many more to the script files. 

Things should remember before using SQL Script

There are some things we should remember before using SQL scripts. 

1. The commands of SQL *Plus are ignored when we run the SQL script. 
2. SQL Commands and SQL Script doesn’t relate..
3. If you want you can cut/paste the SQL commands in the SQL script editor.  

How to access SQL scripts

Now we will discuss how you can access SQL scripts. So first log in to the workspace home page. Now we have two ways to do it. In the first method you have to click on the SQL workshop icon and after go to SQL Script then go to drill down and last go to the SQL script page. In the second method you can open the option of SQL workshop now there you will see the SQL script option here. You can create, edit and view your SQL script. 

Do not miss: command “python setup.py egg_info” failed with error code 1

How to create a SQL script?

There is three-way to create an SQL script. 

By the script editor

For this, first, go to the workspace homepage and click on the SQL workshop and then SQL script. Now the SQL script page will appear. Next, click on the create option and the script editor page will show. Now enter a name for the script. Now enter all commands you want to include in the script. At last, save your file to the repository. And your SQL file is created. 

By uploading the SQL script

For creating a SQL script by upload the SQL script. For this first, go to the workspace homepage and click on the SQL workshop and then SQL script. Now the SQL script page will appear. Tap on the upload option. Now browse and locate the SQL file you want to upload. You can rename the SQL script file now if you want. After this tap on the upload option to the repository. And your SQL file is created.

Delete the SQL script in the script editor

If you want to delete the SQL script file in the Script editor follow these steps. First, attach the SQL Workshop and Now the SQL Scripts on the Workshop page. Now open that script which you want to remove Tap on the Delete button to delete the script and your script will be removed.

Copy the SQL Script

To copy the SQL script in the script, follow these.  First, attach the SQL Workshop and Now the SQL Scripts on the Workshop page. Now load the script which is to be copied in the editor. Now write the name of a copied script into the script. Tap on the Save option to save the copy of the script. NOW your SQL script is copied in the SQL repository.  

How to delete Duplicate Rows

In this, we will learn how to duplicate rows in the SQL table.

Prepare the sample data

The script creates the table name zed

1. DROP TABLE IF EXISTS zed; 
2. //to create a table
3. CREATE TABLE zed (  
4. id INT PRIMARY KEY AUTO_INCREMENT,
5. //to add data  
6. first_name VARCHAR(35) NOT NULL,  //for first name
7. last_name VARCHAR(30) NOT NULL, //last name
8.  email VARCHAR(220) NOT NULL, //email  
9.     age VARCHAR(20) NOT NULL  //age
10. ); 

We have added dates in the above table.

1. the titles  
2. VALUES (‘tobey’,’Tonysoon’,’[email protected]’,’21’),  
3. (‘henriques’,’Jonas’,’[email protected]’,’18’),  
4. (‘Tony’,’Heaven’,’[email protected]’,’23’),  
5. (‘steven’,’Jackson’,’[email protected]’,’22’),  
6. (‘david’,’Bean’,’[email protected]’,’23’),  
7. (‘Tom ‘,’Baker’,’[email protected]’,’20’),  
8. (‘peter’,’Barnes’,’[email protected]’,’17’),  
9. (‘Mischa ‘,’Barton’,’[email protected]’,’18’),  
10. (‘david’,’Bean’,’[email protected]’,’16’),  
11. (‘philip’,’peternett’,’[email protected]’,’25’),  
12. (‘steven’,’Krane’,’[email protected]’,’25’),  
13. (‘Tony’,’Heaven’,’[email protected]’,’20’),  
14. (‘gwen’,’Blessed’,’[email protected]’,’20’);  
15. (‘tobey’,’Tonysoon’,’[email protected]’,’30’),

The query returns data from the zed table:

1. SELECT * FROM zed //to return data  
2. ORDER BY email;  

id	first_name	last_name	Email	age
7	peter	Barnes	[email protected]	21
13	gwen	Blessed	[email protected]	18
10	philip	peternett	[email protected]	23
1	tobey	Tonysoon	[email protected]	22
14	tobey	Tonysoon	[email protected]	23
8	Mischa	Barton	[email protected]	20
11	steven	Krane	[email protected]	17
4	steven	Jackson	[email protected]	18
2	henriques	Jonas	[email protected]	16
3	Tony	Heaven	[email protected]	25
12	Tony	Heaven	[email protected]	25
5	david	Bean	[email protected]	20
9	david	Bean	[email protected]	20
6	Tom	Baker	[email protected]	30

This SQL query returns the duplicate emails from the zed table that we have created.

1. SELECT  
2.     email, COUNT(email) //to select the email  
3. FROM  
4.     zed //to select file
5. GROUP BY  
6.     email  //select group
7. HAVING  
8. COUNT (email) > 1;  //to count email

email	COUNT(email)
[email protected]	2
[email protected]	2
[email protected]	2

We found three duplicate emails. 

For Delete duplicate rows

1. DELETE t1 FROM zed t1 //to select the file  
2. INNERJOIN zed t2   
3. WHERE  //select
4.     t1.id < t2.id AND  
5.     t1.email = t2.email //to show email

Output: 

Query OK, three rows affected (0.10 sec)

There are three rows removed. Now We will run the query, for finding the duplicate emails.

1. SELECT  
2.     email,   
3. COUNT (email) //to select the count of email which is repeat  
4. FROM  
5.     zed //select file 
6. GROUP BY  
7.     email  //to group
8. HAVING  
9. COUNT (email) > 1;

The query returns the blank set. Now to verify the data, run this SQL query:

SELECT * FROM zed; //to select the file

id	first_name	last_name	Email	age
7	peter	Barnes	[email protected]	21
13	gwen	Blessed	[email protected]	18
10	philip	peternett	[email protected]	23
1	tobey	Tonysoon	[email protected]	22
8	Mischa	Barton	[email protected]	20
11	steven	Krane	[email protected]	17
4	steven	Jackson	[email protected]	18
2	henriques	Jonas	[email protected]	16
3	Tony	Heaven	[email protected]	25
5	david	Bean	[email protected]	20
6	Tom	Baker	[email protected]	30

The rows id of  9, 12 & 14 will be removed from this table.  Now We will remove the duplicate rows:

1. DELETE c1 FROM zed c1  
2. INNERJ OIN zed c2   
3. WHERE  
4.     c1.id > c2.id AND  
5.     c1.email = c2.email; 

id	first_name	last_name	email	age
1	peter	Barnes	[email protected]	21
2	tobey	Tonysoon	[email protected]	22
3	gwen	Blessed	[email protected]	18
4	henriques	Jonas	[email protected]	16
5	steven	Krane	[email protected]	17
6	philip	peternett	[email protected]	23
7	steven	Jackson	[email protected]	18
8	david	Bean	[email protected]	20
9	Mischa	Barton	[email protected]	20
10	Tony	Heaven	[email protected]	25
11	Tom	Baker	[email protected]	30

Delete duplicate rows using an intermediate table

First, create a table:

CREATE TABLE source_copy LIKE source; //to create table source

       Add the distinct rows from the database:

1. INSERT INTO source_copy  //to insert the source copy 
2. SELECT * FROM source //to select from source 
3. GROUP BY col;  //to group them

Now take the real table and rename the primary table to the real one.

1. DROP TABLE source;  //to drop table source
2. ALTER TABLE source_copy RENAME TO source; //to copy rename source 

Delete duplicate rows using the Function

This option will only work on MySQL version 8.02

1. SELECT id, email, ROW_NUMBER() //  //to select data
2. OVER (PARTITION BY email  //for selecting same data 
3. ORDER BY email  
4.     ) AS row_num  
5. FROM zed; //from file 
6. SELECT id  //to select id
7. FROM (SELECT id,  //to select id
8. ROW_NUMBER() OVER (  //select row number
9. PARTITION BY email ORDER BY email) AS row_num //for parition 
10. FROM  
11. zed //from this file 
12. ) t  
13. WHERE  
14. row_num> 1; //to show row number

Output:

id

9

12

14

How to run the SQL script?

Now we will discuss how we can run SQL script. To run the SQL script first open the command prompt window on your pc. Now type this in the command prompt. 

sqlcmd -S myserver/instanceName -i C:/myscript.sql

After this press enter and your SQL script will run from the command prompt. This is the easiest and best way to run the SQL script.

Conclusion:

In this article, we discussed SQL script and some of his operations. Hopefully, this will help. You can also subscribe to our YouTube channel Gossipfunda for more amazing tips and tricks.