You're reading from The SQL Workshop

Product type Book

Published in Dec 2019

Publisher Packt

ISBN-13 9781838642358

Pages 288 pages

Edition 1st Edition

Languages

SQL

Concepts

Databases

Authors (3):

Frank Solomon

Prashanth Jayaram

Awni Al Saqqa

View More author details

Table of Contents (13) Chapters

Preface

1. SQL Basics

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

About

This section is included to assist the students to perform the activities present in the book. It includes detailed steps that are to be performed by the students to complete and achieve the objectives of the book.

1. SQL Basics

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

Solution:

Create the Products table with the same column names that were provided in the Excel spreadsheet:

use packt_online_shop;
CREATE TABLE Products
(
    ProductID INT NOT NULL,
    ProductCategoryID INT NOT NULL,
    SupplierID INT NOT NULL,
    ProductName CHAR(50) NOT NULL,
    NetRetailPrice DECIMAL(10, 2) NULL,
    AvailableQuantity INT NOT NULL,
    WholesalePrice DECIMAL(10, 2) NOT NULL,
    UnitKGWeight DECIMAL(10, 5) NULL,
    Notes VARCHAR(750) NULL,
    PRIMARY KEY (ProductID)
);

Enter the values into the Products table:

INSERT INTO Products ( ProductID, ProductCategoryID, SupplierID, ProductName, NetRetailPrice, AvailableQuantity, WholesalePrice, UnitKGWeight, Notes )
VALUES...

2. Manipulating Data

Activity 2.01: Inserting Additional values to the Products Table

Solution:

Create the FoodProducts table with default values:

CREATE TABLE FoodProducts
(
ProductID INT NOT NULL AUTO_INCREMENT,
ProductCategoryID INT NOT NULL DEFAULT 1,
SupplierID INT NOT NULL DEFAULT 1,
ProductName CHAR(50) NOT NULL,
NetRetailPrice DECIMAL(10, 2) NULL DEFAULT 5.99,
AvailableQuantity INT NOT NULL,
WholesalePrice DECIMAL(10, 2) NOT NULL DEFAULT 3.99,
UnitKGWeight DECIMAL(10, 5) NULL,
Notes VARCHAR(750) NULL,
PRIMARY KEY (ProductID)
);

Insert multiple values:

insert into FoodProducts ( ProductName, AvailableQuantity, UnitKGWeight, Notes ) values ('Pancake batter', 50, 0.25, 'Contains eggs'),
('Breakfast cereal', 10, 0.25, 'Add milk'),
('Siracha sauce', 10, 0.25, 'Spicey');

Observe the result:
```
select * from foodProducts;
```
Your output should be as follows:

Figure 2.14: Populated Products...

3. Normalization

Activity 3.01: Building a Relationship between the Orders and the OrderItems table

Solution:

Create the OrderItems table:

Use packt_online_shop;
CREATE TABLE OrderItems
(
    OrderItemID INT NOT NULL AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    Discount DECIMAL(10, 2) NULL,
    Notes VARCHAR(750) NULL,
    PRIMARY KEY (OrderItemID)
);

Create the Orders (child) table:

Create Table Orders(
    OrderID INT NOT NULL AUTO_INCREMENT,
    CustomerID INT NOT NULL,
    OrderNumber CHAR(50) NOT NULL,
    OrderDate DATETIME NOT NULL,
    ShipmentDate DATETIME NULL,
    OrderStatus CHAR(10) NULL...

4. The SELECT Statement

Activity 4.01: Displaying Particular Columns from the Table

Solution:

In the New Query window, switch to the PACKT_ONLINE_SHOP database:
```
USE PACKT_ONLINE_SHOP
```

Write the following query, to extract the required data, in the desired format:

SELECT FirstName as 'First Name', LastName as   'Last Name', Phone as 'Phone Number'
FROM Customers

Run the query. Your output should be as follows:

Figure 4.21: Columns from the Customers table

The query will list all the rows of the FirstName, LastName, Phone columns renamed as First Name, Last Name, and Phone Number.

Activity 4.02: Extracting the Top Five Highest Paid Items

Solution:

Execute the following query:

SELECT 
    Products.ProductName as 'Product Name',
    Products.NetRetailPrice as 'Product Retail Price',
    Products.AvailableQuantity as &apos...

5. Shaping Data with the WHERE Clause

Activity 5.01: Combining Conditions to Extract Store Data

Solution:

First, enter the SELECT statement. This is used so as to display the results using the requested column names:

SELECT   
    ProductName as 'Product Name',  
    NetRetailPrice as 'Product Retail Price', 
    AvailableQuantity as 'Available Quantity'    
From Products   
WHERE NetRetailPrice <= 24.99   
    AND AvailableQuantity >=38

Execute the query, you should get the following output in the result grid:

Figure 5.19: Columns from the Customers table

6. JOINS

Activity 6.01: Implementing JOINS

Solution:

Look at the tables involved in obtaining and identifying the common columns. If you look closely at the following diagram, you will notice that the data that's required is scattered across not just two tables but three, that is, Orders, OrderItems, and Products.
In cases like these, we'll have to perform multiple joins, with the first join being between the Orders and OrderItems table to get price details, and the second join being between the OrderItems and Products table to get the product information.

In a new query window, implement this logic into the query:

SELECT Orders.OrderNumber,
OrderItems.UnitPrice,
OrderItems.Quantity,
Products.ProductName
FROM Orders JOIN OrderItems ON Orders.OrderID =   OrderItems.OrderID 
     JOIN Products ON OrderItems.ProductID = Products.ProductID

In this query, we joined the Orders and OrderItems tables using the OrderID and joined the Products and OrderItems tables using...

7. Subqueries, Cases, and Views

Activity 7.01: Finding the Product Category Name Using a Subquery

Solution:

Enter the following query:

USE packt_online_shop;
SELECT  PC.ProductCategoryName
FROM    ProductCategories PC
WHERE   ProductCategoryID IN
(SELECT ProductCategoryID FROM Products WHERE   ProductName = 'habanero peppers');

Run the query. Your output will be as follows:

Figure 7.10: Category of the habanero peppers food item

Activity 7.02: Categorizing the Shipments Using CASE Statements

Solution:

Enter the following query:

USE packt_online_shop;
SELECT OrderNumber, ShipmentDate,
CASE
WHEN ShipmentDate < ' 2010-12-10' THEN 'Past Shipment Date'
WHEN ShipmentDate >= ' 2010-12-10' AND
ShipmentDate < ' 2019-12-18' THEN
'Recent Shipment Date'
ELSE 'Future Shipment Date'
END AS 'Shipment Date Category'
FROM Orders;

Execute the query. You should...

8. SQL Programming

Activity 8.01: Building a Stored Procedure

Solution:

Execute the following query:

CREATE DEFINER=`root`@`localhost` PROCEDURE `spFilterOrdersByItemQuantity`(IN orderItemQuantityVal int)
BEGIN
 SELECT  OI.OrderID, SUM(OI.Quantity)      AS 'Total Order Item Quantity'
 FROM  OrderItems OI
 GROUP BY OI.OrderID
 HAVING SUM(OI.Quantity) <= orderItemQuantityVal
 ORDER BY OI.OrderID;
END

Test the stored procedure using the following query:

USE packt_online_shop;
CALL spFilterOrdersByItemQuantity(25);

Activity 8.02: Working with MySQL Functions

Solution:

Write the following query:

CREATE DEFINER =`root`@`localhost` FUNCTION `fnProductTotalOrderQty`(ProductID INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE retVal INT;
SET retVal = (
SELECT
CASE
    WHEN     SUM(OI.quantity) IS NULL THEN 0
ELSE
    SUM(OI.quantity)
END AS 'quantity'
FROM     OrderItems...

9. Security

Activity 9.01: Grant UPDATE permission on a table in MySQL

Solution:

Update the permission for TEMP_ACCOUNT_2 using the following code:

USE packt_online_shop; 
GRANT UPDATE ON TABLE packt_online_shop.products TO 'TEMP_ACCOUNT_2';

Write the following code to view the
```
SHOW GRANTS FOR 'TEMP_ACCOUNT_2';
```
Execute the query, you should get the result:

Figure 9.6: All permissions provided to TEMP_ACCOUNT_2

10. Aggregate Functions

Activity 10.01: Working with Aggregates

Write the following code in a new query tab:

SELECT OI.OrderID, P.ProductName,
    SUM(OI.Quantity) AS
      'COUNT OF EACH SEPARATE PRODUCT IN THE ORDER',
          SUM(SUM(OI.Quantity)) OVER(PARTITION BY OI.OrderID)
          AS 'TOTAL COUNT OF PRODUCTS IN THE ORDER',
          MAX(MAX(OI.Quantity)) OVER(PARTITION BY OI.OrderID)
          AS 'COUNT OF THE PRODUCT WITH THE HIGHEST 
          ORDER COUNT IN THE ORDER'
FROM      OrderItems OI INNER JOIN
          Products P ON OI.ProductID = P.ProductID
GROUP BY  OI.OrderID, P.ProductName
ORDER BY  OI.OrderID;

Execute the query, it should...

11. Advanced SQL

Activity 11.01: Implementing the LIKE Operator

Solution:

Enter the following query:

SELECT O.OrderID, O.CustomerID, O.OrderNumber, O.OrderDate,       O.ShipmentDate, O.OrderStatus, O.Notes
FROM   Orders O
WHERE  O.Notes LIKE '%CUST%' AND O.ShipmentDate > '01051995'

We have set two filter conditions. Only when both are satisfied will the product be displayed.

On execution of the query, your result will look similar to the following:

Figure 11.22: Customer notes post May 5, 1995

Notice that there are only three orders that have an order date post May 1, 1995 and have CUST as part of the Notes section.

Activity 11.02: Using Transactions

Solution:

Execute the following code to verify the existence of the specific OrderItems rows:

USE PACKT_ONLINE_SHOP; 
 
SELECT    OrderItemID, OrderID, ProductID, Quantity,
          UnitPrice, Discount, Notes
FROM     OrderItems
WHERE    OrderID = 5;

The output...

What Next?

Now that you've mastered the fundamentals of SQL, continue to build your knowledge and advance your career with one of our other Workshops…

The Python Workshop

Learn how to write clean, concise code with Python 3
Automate essential day-to-day tasks with Python scripts
Tackle entry-level data science problems and create engaging visualizations
Get started with predictive machine learning models

...

The rest of the chapter is locked

You have been reading a chapter from

The SQL Workshop

Published in: Dec 2019 Publisher: Packt ISBN-13: 9781838642358

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

Start free trial

Renews at $15.99/month. Cancel anytime}

Authors (3)

Frank Solomon

Frank Solomon started out building front-end and database software with Microsoft stack tools such as SQL Server and ASP and then extended into ASP.NET, C#, VB.NET, JavaScript, and more. He learns about new products, languages, and technologies all the time, and he pushed into technical writing as a way to present and express his research and discoveries. He works as a contractor right now. He has worked in start-ups himself, and he knows that the hard work of writers and developers makes the end user experience easier.

See other products by Frank Solomon

Prashanth Jayaram

Prashanth Jayaram is a database technologist, blogger, engineering lead, automation expert, TechNet wiki ninja, PowerShell Geek, and technologist enthusiast with extensive experience in designing database solutions. He is the author of PowerShell 6.0 Linux Administration Cookbook and has hands-on experience with the next generation database technologies. He has been awarded as ABOVE and BEYOND and Best SQL Author 2018 towards his contribution to SQL Server technology. He has articulated over 200+ articles widespread across SQL, NoSQL, PowerShell, Python, SQL on Linux, SQL on Azure, and SQL on AWS arenas.

See other products by Prashanth Jayaram

Awni Al Saqqa

Awni Al Saqqa is a Microsoft Technology Specialist in MS SQL Server and a certified solutions developer since 2007. He has over a decade of experience with database development and administration on SQL Server, Oracle, and MySQL. He is a solutions architect, who is hands-on in many enterprise projects for different business sectors, such as education, hospitality, retail, manufacturing, marketing, and more, which has given him the perfect combination between business and technical experience.

See other products by Awni Al Saqqa