Question

3. Write a script that adds rows to the database that you created in exercise 2. Add two rows to the Users and Products tables. Add three rows to the Downloads table: one row for user 1 and product 2; one row for user 2 and product 1; and one row for user 2 and product 2. Use the SYSDATE function to insert the current date into the download_date column. Use the sequences created in the previous exercise to get the values for the user_id, download_id, and product_id columns. Write a SELECT statement that joins the three tables and retrieves the data from these tables like this: Sort the results by the email address in descending sequence and the product name in ascending sequence. 4. Write an ALTER TABLE statement that adds two new columns to the Products table created in exercise 2. Add one column for product price that provides for three digits to the left of the decimal point and two to the right. This column should have a default value of 9.99. Add one column for the date and time that the product was added to the database. 5. Write an ALTER TABLE statement that modifies the Users table created in exercise 2 so the first_name column can store NULL values and can store a maximum of 20 characters. Code an UPDATE statement that inserts a NULL value into this column. It should work since this column now allows NULL values. Code another UPDATE statement that attempts to insert a first name that’s longer than 20 characters. It should fail due to the length of the column.

Answer 1

1.

use my_guitar_shop;

CREATE INDEX index_zipcode ON customers(zip_code);

Screenshot of execution:

Query 1 × 1use my_guitar_shop; 2CREATE INDEX index zipcode ON customers (zip code); Output Action Output Time Action Message 0 15:07:42 use my_guitar shop 0 row(s) affected O 2 15:07:43 CREATE INDEX index zipcode ON customers(zip. Orow(s) affected Records: 0 Duplicates: 0 ▼E-my-guitar-shop ▼ Rp Tables addresses administrators categories ▼ Eil customers Columns Indexes PRIMARY email address index zipcode oreign Keys Triggers Download employees audit order items orders products ProductsAudit User

2.

drop database my_web_db;

create database my_web_db;

use my_web_db;

Screenshot of execution:

Limit to 1000 rows 1 drop database my_web_db; 2create database my_web_db; 3use my_web_db; 4 5 Output | Action Output Time Message Action 3 1 15:28:05 drop database my_web_db Error Code: 1008. Cant drop database m.. O 2 15:28:05 create database my _web_db 1 row s) affected 3 15:28:05 use my_web_db 0 row(s) affected

CREATE TABLE USERS
(
user_id int Primary key NOT NULL,
email_address varchar(100),
first_name varchar(45),
last_name varchar(45)
);

CREATE TABLE PRODUCTS
(
product_id int Primary key NOT NULL,
product_name varchar(20)
);

CREATE TABLE DOWNLOADS
(
download_id int Primary key NOT NULL,
user_id int REFERENCES users(user_id),
download_date datetime,
filename varchar(50),
product_id int REFERENCES products(product_id)
);

Screenshot of execution:

Limit to 1000 rows * 1 · CREATE TABLE USERS 2 user id int Primary key NOT NULL email address varchar (100 5 first name varchar(45), 6 last_name varchar(45) 7 8 9CREATE TABLE PRODUCTS 10 11 product id int Primary key NOT NULL 12 product_name varchar (20) 13 14 15 CREATE TABLE DOWNLOADS 16 17 download id int Primary key NOT NULL, 18 user id int REFERENCES users (user id) 19 download date datetime, 20 filename varchar(50), 21 product id int REFERENCES products (product id) 22 ) Output di Action Output Time Action Message 0 115:39:19 CREATE TABLE USERS (user id int Pri.. 0 row(s) affectecd 2 15:39:19 CREATE TABLE PRODUCTS (product 0rows) affected 3 15:39:19 CREATE TABLE DOWNLOADS (downl… 0row(s) affected my-web-db Tables ▼ DOWNLOADS PRODUCTS USERS

3.

Screenshot of execution:

Limit to 1000 rows 1insert into USERS values (100, '[email protected], 'John', 'Smith) 2insert into USERS values (101, '[email protected], 'Jane', 'Doe; 3 insert into PRODUCTS values (77, Local Music Vol 2"); 4 insert into PRODUCTS values(78, 'Local Music Vol 1) 5 insert into DOWNLOADS values (200,100, now(), pedals_are_falling.mp3',78); 6 insert into DOWNLOADs values (201,101,now(), 'turn_signal.mp3',77); 7 insert into DOWNLOADS values (202,101, now(), 'one_horsetown.mp3',78); ), one_horse_town.mp3',78); Output Action Output Duration / Fetc 0.046 sec Time Action Message 1 09:38:42 insert into USERS values 100. [email protected]. 1 row(s) affected O 209:38:42 insert into USERS values(101, [email protected]. 1 row(s) affected 0.047 sec 1 rows) affected 0.031 sec 3 09:38:42 insert into PRODUCTS values(77, Local Music Vol 2) 409:38:42 insert into PRODUCTS values(78, Local Music Vol 1) 5 09:38:42 insert into DOWNLOADS values(200,100.now0.pedals are... 1 rows) affected 6 09:38:42 insert into DOWNLOADS values(201,101 now0.tum_signa. 1 rows) affected 1 row(s) affected 0.031 sec 0.047 sec 0.031 sec 0 7 09:38:42 insert into DOWNLOADS values(202.101now0.one horse. 1 row(s) affected 0.031 sec

USERS table: Limit to 1000 rows' 1select from USERS; 2 3 4 Filter Rows: Result Grid Edit: user id email address first name last name 100 [email protected] John Smith [email protected] NULL 101 Doe NULL NULL NULL PRODUCTS table: Limit to 1000 rows 1 select from PRODUCTS 2 3 4. Fiter Rows: Result Grid Edit: Expo product id product_name Local Music Vol 2 Local Music Vol 1 78 NULL NULL DOWNLOADS table:

Limit to 1000 rows 1select from DOWNLOADS; 2 4 Result Grid| ElE Filter Rows: | Wrap Cel Content: 61 田 Edit: t: download id user id download_date filename product_id 2019-05-09 04:08:42 pedals are falling.mp3 78 100 200 bi 101 201 2019-05-09 04:08:42 turn signal.mp3 2019-05-09 04:08:42 one horse town.mp3 78 NULL 202 101 NULL

Query to that joins the three tables and retrieves the data:

select email_address, first_name, last_name, download_date, filename, product_name
from USERS, PRODUCTS, DOWNLOADS
where USERS.user_id=DOWNLOADS.user_id
and PRODUCTS.product_id=DOWNLOADS.product_id;

Output:

Limit to 1000 rows 1select email_address, first_name, last_name, download date, filename, product_name 2 from USERS, PRODUCTS, DOWNLOADS 3 where USERS.user_id-DOWNLOADS. user id 4 and PRODUCTS.product_id-DOWNLOADS.product id; oduct : Result GridFiber Rows I wrap Cal Content! Exporti . first_name last_name download date filename email_address [email protected] John İ[email protected] Jane product_name 2019-05-09 04:08:42 pedals are falling.mp3 Local Music Vol 1 2019-05-09 04:08:42 turn signal.mp3 Smith Local Music Vol2 Doe [email protected] Jane 2019-5-09 04:08:42 one horse town.mp3 Local Music Vol 1 Doe

SELECT statement that joins the three tables and retrieves the data. Sort the results by the email address in descending sequence and the product name in ascending sequence

select email_address, first_name, last_name, download_date, filename, product_name
from USERS, PRODUCTS, DOWNLOADS
where USERS.user_id=DOWNLOADS.user_id
and PRODUCTS.product_id=DOWNLOADS.product_id
order by email_address desc, product_name asc;

Output:

gQua lunt to 1000 rows , 1select email_address, first_name, last_name, download_date, 2 from USERS, PRODUCTS, DOWNLOADS 3 where USERS.user id-DOWNLOADS.user_id and PRODUCTS.product_id=DOWNLOADS .productid 5 order by email address desc, product_name asc; filename, product_name 6 Result GridEFiter Export; wrap Cel Content first name last_name download date filename email_address product_name [email protected] Johr Smith 2019-05-09 04:08:42 pedals are falling.mp3 Local Music Vol 1 İ[email protected] Jane 2019-05-09 04:08:42 one horse town.mp3 2019-05-09 04:08:42 turn signal.mp3 Local Music Vol 1 Doe [email protected] ane Local Music Vol 2 Doe

4.

ALTER TABLE PRODUCTS

ADD price real(5,2) default 9.99,

ADD dateAdded datetime;

Screenshot of execution:

Query 1 × f A01 8100冏1 層?!! Limit to 1000 rows 1 ▼| 1· ALTER TABLE PRODUCTS 2 ADD price real(5,2) default 9.99, 3 ADD dateAdded datetime; Output : Action Output Time Action 12:39:42 ALTER TABLE PRODUCTS ADD price real(5.2) default 9.99. ADD dateAdded datetime

5.

ALTER TABLE USERS

MODIFY first_name varchar(20) NULL;

Screenshot of execution:

Query 1 × Limit to 1000 rows 1 ALTER TABLE USERS first-name varchar(20) MODIFY NULL; Output Action Output Action 1 12:53:17 ALTER TABLE USERS MODIFY first_name varchar(20) NULL Inserting a null value for first name Query 1 x Limit to 1000 rows - 1insert into USERS values (102, '[email protected],' ', 'Park); Output Action Output Time Message Action O 1 12:53:17 ALTER TABLE USERS MODIFY first_name varchar(20) NULL 0 row(s) affected Records: 0 I 2 1255:06 insert into USERS values(102. [email protected] Park) 1 row s) affected Inserting first name with more than 20 characters Query 1 Un to 1000rows is 1insert into USERS values (103, "[email protected], 3ohn Kenneth Thomas Victor Williams', 'Park); 2 Output Action Output Time Action 1 12:57 52 net rto USERS values(103 Johpaul@g alco khn Kenneth Thomas wctor waar 1 rowis)dfected, I wang(s) 1265 Data t uncated for cour n Int_Ta e' at row 1 ▲