In this blog we will learn some important SQL Classes to update, insert, delete and retrieved data from database.
Table of content
INSERTING Data
Inserting: Inserting single row
INSERTING Multiple Rows
INSERT Data Using INTO
INSERTING Retrieved Data
INSERT Data Using WHERE Clause
NSERT Data Using WHERE Clause
INSERTING Data
In SQL, there are basically two ways to INSERT data INTO a table: One is to INSERT it one row at a time, the other is to INSERT multiple rows at a time. Let's first look at how we may INSERT data one row at a time:
Use CreateSQLClass.txt file for database
Sample Basic Syntax
INSERT INTO tablename (column,column,column,column,column,column,column,column) VALUES( 'VALUE', 'VALUE', 'VALUE', 'VALUE', NULL, NULL ) Inserting
INSERTING Single Row
The syntax for INSERT data INTO the customers table one row at a time is as follows. Note: Use the SQLClass database
Write Exercise Code:
INSERT INTO Customers
(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES( '100010', 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', NULL, NULL, NULL
)Result
(1 row affected)
Total execution time: 00:00:00.008
Now I can check to confirm the data has been inserted by
SELECT * from Customers
The previous syntax for INSERTING data will work but it is not the recommend way. You should avoid INSERTING data this way. This method depends on the columns being the order you specified. Also, when you type in the values make sure you use a single quote.
The following is a better way to INSERT new single record. This way the columns are specified:
Write Exercise Code
INSERT INTO customers
( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES
( '100011', 'Elmer Fudd', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', 'Joe Smith', 'elmer@fudd.com'
)Result
(1 row affected)
Total execution time: 00:00:00.008
Now I can check to confirm the data has been inserted by
SELECT * from Customers
INSERTING Multiple Rows
Because the INSERT statement only allows you to INSERT one record at a time you need to statements to enter multiple records. You could leave same data or change before each executing.
The syntax for INSERTING multiple rows of data INTO the customers table is as follows:
Write Exercise Code:
INSERT INTO customers
( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES ( '100012', 'Pep E. Lapew', '100 Main Street', 'Los Angeles', 'CA', '91108','USA', 'Pep E. Lapew', 'plapew@plapew.org' )INSERT INTO customers
( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES ( '100013', 'M. Martin', '42 Galaxy Way', 'New York','NY', '11213','USA', 'M. Martin', 'mmartin@mmartin.org' ) Result
(1 row affected)
(1 row affected)
Total execution time: 00:00:00.010
INSERT Data Using INTO
The INSERT statement generally is used to add one row at a time. Another way to add data is using the INTO in a SELECT statement. This allows you to INSERT data from a single table or multiple tables using table joins. You can think of the SELECT INTO as being an export operation.
The INTO statement allows you to copy the data from one table into a new table. The keyword INTO actually creates the new table.
The syntax for using the INTO in a SELECT statement is as follows: Write Exercise Code:
SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country,
cust_contact, cust_email
INTO customers2
From Customers;
-- Now let’s insert a row to the new customers2 table
INSERT INTO customers2 ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES ( '100014', 'J. Verne', '12 Timetravel Way', 'New York','NY', '11213','USA', 'J. Verne', 'jverne@jverne.org' )
Note: When using this syntax, it does not re-create the Primary Key. On the new table Customers2 add Primary key.
To do this, use the following syntax:
ALTER TABLE customers2
ADD CONSTRAINT pk_customers2 PRIMARY KEY (cust_id);Results
Commands completed successfully.
Total execution time: 00:00:00.011
INSERTING Retrieved Data
The INSERT statement generally is used to add one row at a time. Another way to add data is using the result set from a SELECT statement; also known as the INSERT SELECT. It is made up of both the INSERT and the SELECT statement. You can think of the INSERT SELECT as being an import operation.
If you wanted to add customers from another table to the customers table all at one time you would use the INSERT SELECT statement.
Note: This code will copy duplicate records into your Customers table.
The syntax for INSERTING a result set from a SELECT statement is as follows:
Write Exercise Code
INSERT INTO customers ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
Select cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email
From Customers2
WHERE cust_id=’100014’INSERT Data Using WHERE Clause
The INSERT statement generally is used to add one row at a time. You could also use the WHERE clause to filter specific rows. This method allows you to limit what data you are INSERTING.
Note: Check the cust_id in Customer2 table to make sure customer number '111111' is there. Otherwise you will get an error. If you don't have customer number '111111' go ahead and add it.
The following code will INSERT the new single record.
Write Exercise Code
INSERT INTO customers2
( cust_id, cust_name, cust_addres s, cust_city, cust_state, cust_zip, cust_country , cust_contact , cust_email )
VALUES ( '111111' , 'Elmer Fudd', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', 'Joe Smith', 'elmer@fudd.co m' )
The syntax for using the WHERE clause in a SELECT statement is as follows:
Write Exercise Code:
INSERT INTO customers ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email
FROM Customers2
WHERE cust_id = '111111'
Updating and Deleting Data
Once there's data in the table, we might find that there is a need to modify the data. To do so, we can use the UPDATE command. The UPDATE statement always begins with the name of the table being UPDATED.
The SET command is used to assign new VALUE to a column. The UPDATE statement ends with a WHERE clause. Without a WHERE clause, the UPDATE statement will UPDATE all rows in the table.
The syntax to UPDATE a single column is as follows
Write Exercise Code
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = '100011'Result
(1 row affected)
Total execution time: 00:00:00.005
Note: just make sure there is a cust_id number “100011.” If not use another cust_id that is in your dataset.
UPDATING Multiple Columns
To UPDATE multiple columns we use a slightly different syntax. To UPDATE multiple columns you use a single SET command and separate the column names with a comma. No comma after the last column. For example:
Write Exercise Code
UPDATE customers
SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com'
WHERE cust_id = '100011' DELETE Column VALUE
To delete a columns VALUE, we set it to NULL.
You do this as follows:
Write Exercise Code
UPDATE customers
SET cust_city = NULL
WHERE cust_id = '111111'DELETING Single Row
Sometimes we may wish to get rid of records from a table. To do so, we can use the DELETE FROM command. Using the WHERE clause, we can specify which row to delete.
To do this use, the following syntax:
Write Exercise Code
DELETE FROM customers
WHERE cust_id = '100012'DELETING ALL Rows
Sometimes we may wish to get rid of records from a table. To do so, we can use the DELETE FROM command.
To DELETE all rows in a table we don't use the WHERE clause. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. In order not to delete the records in the Customer table lets use the Customers2 table.
To do this, use the following syntax:
Write Exercise Code
DELETE FROM customers2 Drop tables:
Drop Table removes all the data and the entire definition of the table, including any primary keys, indexes, foreign keys, and any link to the table.
Write Exercise Code
Drop Table Customer2;Truncate ALL Rows
TRUNCATE TABLE removes all rows from a table just like the Delete from command. TRUNCATE TABLE is faster and uses fewer system resources because it does not log every delete transaction in the log file. .
To do this, use the following syntax:
First lets add data back into the Customer2 table. Execute this code
Write Exercise Code
Drop Table Customer2;
SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email
INTO Customers2
From Customers;
Then let’s execute this code to truncate the Customers2 table and then execute the select statement that follows to see that all records were deleted.
Write Exercise Code
Truncate Table customers2;
Select * from customers2;