Question

Using your downloaded DBMS (MS SQL Server or MySQL), write SQL queries that inserts at least three rows in each table.

1. 1. For the On-Demand Streaming System,
      1. First, insert information for multiple users, at least three video items and insert the three different types of subscriptions (Basic, Advanced, Unlimited) into the database.
      2. Then insert at least three user subscriptions.
2. Execute the queries and make sure they run correctly

Answer 1

We will use the sales.promotions table created in the previous tutorial for the demonstration.

If you have not yet created the sales.promotions table, you can use the following CREATE TABLE statement:

CREATE TABLE sales.promotions ( promotion_id INT PRIMARY KEY IDENTITY (1, 1), promotion_name VARCHAR (255) NOT NULL, discount NUMERIC (3, 2) DEFAULT 0, start_date DATE NOT NULL, expired_date DATE NOT NULL );

1) Inserting multiple rows example

The following statement inserts multiple rows to the sales.promotions table:

INSERT INTO sales.promotions ( promotion_name, discount, start_date, expired_date ) VALUES ( '2019 Summer Promotion', 0.15, '20190601', '20190901' ), ( '2019 Fall Promotion', 0.20, '20191001', '20191101' ), ( '2019 Winter Promotion', 0.25, '20191201', '20200101' );

SQL server issued the following message indicating that three rows have been inserted successfully.

(3 rows affected)

Let’s verify the insert by executing the following query:

SELECT * FROM sales.promotions;