Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
The SQL Workshop

You're reading from   The SQL Workshop Learn to create, manipulate and secure data and manage relational databases with SQL

Arrow left icon
Product type Paperback
Published in Dec 2019
Publisher Packt
ISBN-13 9781838642358
Length 288 pages
Edition 1st Edition
Languages
Concepts
Arrow right icon
Authors (3):
Arrow left icon
Prashanth Jayaram Prashanth Jayaram
Author Profile Icon Prashanth Jayaram
Prashanth Jayaram
Frank Solomon Frank Solomon
Author Profile Icon Frank Solomon
Frank Solomon
Awni Al Saqqa Awni Al Saqqa
Author Profile Icon Awni Al Saqqa
Awni Al Saqqa
Arrow right icon
View More author details
Toc

Table of Contents (13) Chapters Close

Preface 1. SQL Basics FREE CHAPTER 2. Manipulating Data 3. Normalization 4. The SELECT Statement 5. Shaping Data with the WHERE Clause 6. JOINS 7. Subqueries, Cases, and Views 8. SQL Programming 9. Security 10. Aggregate Functions 11. Advanced SQL Appendix

Populating Your Tables

Once the table has been created, the next logical step is to insert values into the table. To do this, SQL provides the INSERT statement. Let's try adding a row of data to the Student table of the studentdemo database that we created previously.

Here is the SQL statement to achieve this. First, switch to the studentdemo database and enter the following query:

USE studentdemo;
INSERT INTO Student (StudentID, StudentName, grade, age, course) VALUES ('S001', 'Prashanth Jayaram', 'A', 36, 'Computer Science');

If you check the contents of the database after running this query, you should see something like this:

Figure 1.14: Values inserted into the database

Figure 1.14: Values inserted into the database

Note

To see the contents of this database, follow the process you used in the earlier exercises. Right-click the table and choose Select Rows - Limit 1000.

Adding single rows like this in multiple queries will be time-consuming. We can add multiple rows by writing a query like the following one:

INSERT INTO Student (StudentID, StudentName, grade, age, course) VALUES ('S002', 'Frank Solomon', 'B', 35, 'Physics'), ('S003', 'Rachana Karia', 'B', 36, 'Electronics'), ('S004', 'Ambika Prashanth', 'C', 35, 'Mathematics');

The preceding query looks like this on the Query tab.

Figure 1.15: Adding multiple rows in an INSERT query

Figure 1.15: Adding multiple rows in an INSERT query

When you run the query, all three rows will be added with a single query:

Figure 1.16: Output of multiple row insertion

Figure 1.16: Output of multiple row insertion

Exercise 1.02: Inserting Values into the Customers Table of the PACKT_ONLINE_SHOP Database

Now that we have the Customers table ready, let's insert values into the table using a single query. We have the data from an already existing Excel spreadsheet. We will be using that data to write our query. Here is what the Excel file looks like:

Figure 1.17: Source data in an Excel spreadsheet

Figure 1.17: Source data in an Excel spreadsheet

Note

You can find the csv format of the file here: https://packt.live/369ytTu.

To move this data into the database, we will need to perform the following steps:

  1. Switch to the PACKT_ONLINE_SHOP database:
    use PACKT_ONLINE_SHOP;
  2. Insert the values based on the Excel spreadsheet provided wherever we have blank data. We will use NULL to do this:
    INSERT INTO Customers (FirstName, MiddleName, LastName, HomeAddress, Email, Phone, Notes)
    VALUES('Joe', 'Greg', 'Smith', '2356 Elm St.', '[email protected]', '(310) 555-1212', 'Always gets products home delivered'),
    ('Grace', 'Murray', 'Hopper', '123 Compilation Street', '[email protected]', '(818) 555-3678', 'Compiler pioneer'),
    ('Ada', NULL, 'Lovelace', '22 Algorithm Way', '[email protected]', '(717) 555-3457', 'First software engineer'),
    ('Joseph', 'Force', 'Crater', '1313 Mockingbird Lane', '[email protected]', '(212) 555-5678', 'Works everyday'),
    ('Jacqueline', 'Jackie', 'Cochran', '1701 Flightspeed Avenue', '[email protected]', '(717) 555-3457', 'Researcher'),
    (NULL, 'Paul', 'Jones', '126 Bonhomme Richard Ave.', '[email protected]', '(216) 555-6232', 'Admiral');
  3. When you execute the query and check the contents of the Customers table, you should see the following output.
    Figure 1.18: The Customers table after inserting the values from the excel sheet

Figure 1.18: The Customers table after inserting the values from the excel sheet

With this, you have successfully populated the Customers table.

Activity 1.01: Inserting Values into the Products Table in the PACKT_ONLINE_SHOP Database

Now that we've migrated the customer's data into the database, the next step is to migrate the product data from the Excel spreadsheet to the database. The data to be entered into the database can be found at https://packt.live/2ZnJiyZ.

Here is a screenshot of the Excel spreadsheet:

Figure 1.19: Source data in an Excel spreadsheet

Figure 1.19: Source data in an Excel spreadsheet

  1. Create a table called Products in the Packt_Online_Shop database.
  2. Create the columns as present in the Excel sheet.
  3. Use the INSERT statement to input the required data into the table.

    Note

    The solution for this activity can be found via this link.

You have been reading a chapter from
The SQL Workshop
Published in: Dec 2019
Publisher: Packt
ISBN-13: 9781838642358
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image