1-Clearly define and describe your findings in your own words

2- Provide recommendations for backing up and securing database

Please use below queries to get answers of above two questions:

create table customers (

CUST_NUM varchar(5),

F_NAME varchar(15),

L_NAME varchar(15),

EMAIL varchar(55),

PHONE integer,

CITY varchar(15),

primary key (CUST_NUM)

);

create table store (

STORE_ID integer,

NAME_ varchar(15),

CITY varchar(15),

PHONE integer,

MANAGER varchar(15),

primary key (STORE_ID)

);

create table accessories (

ACC_ID varchar(15),

NAME_ varchar(15),

PRICE integer ,

primary key (ACC_ID)

);

create table flower_arrangement (

ARRANGEMENT_ID varchar(5),

NAME_ varchar(15),

STYLE varchar(15),

OCCASION varchar(15),

PRICE integer,

STORE_ID integer,

primary key (ARRANGEMENT_ID),

FOREIGN KEY (STORE_ID) references store (STORE_ID),

);

create table orders (

ORDER_NUM integer,

DESCRIPTION_ varchar(15),

MONTH_ varchar(5),

YEAR_ integer,

TOTAL integer,

TYPE_ varchar(15),

STORE_ID integer,

CUST_NUM varchar(5),

ACC_ID varchar(15),

primary key (ORDER_NUM),

FOREIGN KEY (STORE_ID) references store (STORE_ID),

FOREIGN KEY (CUST_NUM) references customers (CUST_NUM),

FOREIGN KEY (ACC_ID) references accessories (ACC_ID)

);

create table item (

ITEM_ID varchar(5),

NAME_ varchar(15),

QUANTITY integer,

ORDER_NUM integer,

ARRANGEMENT_ID varchar(5),

primary key (ITEM_ID),

FOREIGN KEY (ORDER_NUM) references orders (ORDER_NUM),

FOREIGN KEY (ARRANGEMENT_ID) references flower_arrangement (ARRANGEMENT_ID)

);

1. Database script with INSERT statements to populate tables.

INSERT INTO customers VALUES('C01','David','Smith','dsmith@jkl.com','12345678','Sydney');

INSERT INTO customers VALUES('C02','Sachin','Kumar','sk18@xyz.com','34653874','Melbourne');

INSERT INTO customers VALUES('C03','Mehak','kumari','amks789@abc.com','53687632','Perth');

INSERT INTO customers VALUES('C04','Manohar','Lal','mlpoi098@html.com','47123798','Adelaide');

INSERT INTO customers VALUES('C05','Ashu','Rana','ar91@dbms.com','23782834','Sydney');

INSERT INTO store VALUES('1','Big_Basket','Sydney','27747234','Kim');

INSERT INTO store VALUES('3','Easy_Flowers','Perth','45343623','Len');

INSERT INTO store VALUES('2','More_Flowers','Melbourne','23687326','Bill');

INSERT INTO store VALUES('4','Pilot_Florist','Darwin','37747234','Bimer');

INSERT INTO store VALUES('5','Pearl_Flowery','Adelaide','76348235','Jill');

INSERT INTO accessories VALUES('A01','Teddy bear','50');

INSERT INTO accessories VALUES('A02','Chocolate','20');

INSERT INTO accessories VALUES('A03','Basket','30');

INSERT INTO accessories VALUES('A04','Plant','25');

INSERT INTO flower_arrangement VALUES('F01','Iris','Vase','Anniversary','80','1');

INSERT INTO flower_arrangement VALUES('F02','Orchids','centrepiece','Congratulations','150','5');

INSERT INTO flower_arrangement VALUES('F03','Lilies','Hamper','Thank you','100','1');

INSERT INTO flower_arrangement VALUES('F04','Rainbow','Bouquet','Wedding','110','2');

INSERT INTO flower_arrangement VALUES('F05','Sunflowers','Boxed','Get Well','60','3');

INSERT INTO orders VALUES('1500','Car_Deco_Rose','Dec','2019','170','InPerson','1','C01','A01');

INSERT INTO orders VALUES('1501','Party_Deco_Lily','Jan','2020','400','InPerson','3','C02','A02');

INSERT INTO orders VALUES('1502','Jasmine_on_car','Feb','2020','450','Online','2','C03','A03');

INSERT INTO orders VALUES('1503','Marigold_Bunch','Feb','2020','650','Online','1','C04','A04');

INSERT INTO orders VALUES('1504','Lotus_Deco','Mar','2020','250','Phone','5','C05','A02');

INSERT INTO item VALUES('I01','Rose','5','1500','F01');

INSERT INTO item VALUES('I02','Lilly','2','1501','F02');

INSERT INTO item VALUES('I03','Jasmine','6','1502','F03');

INSERT INTO item VALUES('I04','Marigold','2','1503','F04');

INSERT INTO item VALUES('I05','Lotus','1','1504','F02');

2. SQL statements to fulfill requirements with screenshots

1. Display details of all flower arrangements that are sold at Fragrance Flowers group.

SELECT *

FROM flower_arrangement;

2. Display details of all customers from Melbourne whose first name starts with P.

SELECT *

FROM customers

where city='Melbourne' and F_Name like 'P%';

3. Display details of all orders that have been placed online.

SELECT *

FROM orders

where type_='Online';

4. Display order number and description (type, total etc.) for orders that are placed at Sydney stores.

SELECT b.order_num,b.description_,b.type_,b.total

FROM store a

inner join

orders b

on a.store_id=b.store_id

where a.city='Sydney';

5. Write an SQL statement to display details of all stores that have been managed by either Len, Kim or Shen.

select *

from store

where MANAGER in ('Len','Kim','Shen');

6. Display number of customers for all cities

select city, count(cust_num) as number_of_customers

from customers

group by city;

7. Display sum of order total for February month.

select sum(Total) as Total_sales

from orders

where month_='Feb';

8. Display the order number, flower arrangement ID (arrangeID) and total price for orders that have been placed by customers in 2020. Display the result in ascending order of order number.

select b.ORDER_NUM,a.ARRANGEMENT_ID,b.TOTAL

from flower_arrangement a

inner join orders b

on a.STORE_ID=b.STORE_ID

where b.YEAR_=2020

order by b.ORDER_NUM asc;