Code the things below in mySQL. The following queries should be included in the

Question

Code the things below in mySQL.

The following queries should be included in the script:

Create a view named Under_100. It consists of the item_id, title, artist, unit_price and order_qty for every print with a unit_price under 100 dollars.

Create a view named Allen. It consists of the customer_id, customer_name, customer_phone, title, and artist of each print ordered.

Create a view named orders. It consists of the item_id, title, artist, unit_price and order_qty for every print ordered in the range of 2014-01-01 and 2014-02-28.

Create a view named zip_27. It consists of the customer_name, customer_phone, title, artist and date_shipped of each print ordered by a customer whose zip code begins with 27.

Create the following indexes. Use the indicated index name.

Create an index named customer_id on the customer_id field in the customers table.

Create an index named name on the customer_name field in the customers table.

Create an index named shipped on the customer_id and ship_date in the orders table.

Drop the name index.

Specify the integrity constraint that the unit_price of any print must be more than $35.

Ensure that the following are foreign keys (that is, specify referential integrity) within the prints database.

customer_id is a foreign key in the orders table.

Item_id is a foreign key in the orderline table.

Add to the items table a new character field named type that is one character in length.

Change the type field in the items table to M for the print titled Skies Above.

Change the length of the artist field in the items table to 30.

What command would you use to delete the orders table from the prints database? (Do not delete the orders table.)

TABLES:
customers
customer_id customer_name customer-add customer_city customer_state customer_zip customer_phone
1000 Cora Blanca 1555 Seminole Ct. Charlotte NC 28210 704/552.1810
1100 Yash Reed 878 Madison Ave. Greensboro NC 27407 336/316-5434
1200 John Mills 4200 Olive Ave. Columbia SC 29206 803/432.6225
1300 David Cox 608 Old Post Rd. Decatur GA 30030 404/243.7379
1400 Tina Evans 235 Easton Ave. Jacksonville FL 32221 904/992-7234
1500 Will Allen 2508 W. Shaw Rd. Raleigh NC 27542 919/809.2545
1600 James Boyd 200 Pembury Ln. Columbia SC 29206 803/432-7600
1700 Will Parsons 4990 S. Pine St. Raleigh NC 27545 919/355/0034
1800 Walter Kelly 1200 Little St. Columbia SC 29206 803/432-1987
1900 Ann Damian 7822 N. Ridge Rd. Jacksonville FL 32216 904/725-4672
2000 Grace Hull 4090Caldweld St. Charlotte NC 28205 704/365.7655
2100 Jane Brown 3320 W. Main St. Charlotte NC 28210 704/372/9000
2200 Betty Draper 1600 Sardis Rd. Sarasota FL 32441 918/941-9121
items
item_id title artist unit_price on_hand
100 Under the Sun Donald Arley 46.80 340
200 Dark Lady Keith Morris 120.99 250
300 Happy Days Andrea Reid 78.00 210
350 Top of the Mountain Janice Jones 110.00 290
400 Streets from Old Sharon Brune 123.00 320
450 The Hunt Walter Alford 39.99 390
600 Rainbow Row Judy Ford 46.00 350
700 Skies Above Alexander Wilson 98.00 275
800 The Seas and Moon Susan Beeler 67.81 235
850 Greek Isles Benjamin Caudle 76.00 300

orders
order_id customer_id order_date ship_date
1 1200 2013-10-23 2013-10-28
2 1500 2013-10-30 2013-11-03
3 1500 2013-11-09 2013-11-14
4 2100 2013-11-15 2013-11-20
5 1600 2013-11-15 2013-11-20
6 1900 2013-12-15 2013-12-19
7 2200 2013-12-18 2013-12-22
8 1600 2013-12-20 2013-12-22
9 1000 2014-01-18 2014-01-23
10 2200 2014-01-31 2014-02-04
11 1500 2014-02-01 2014-02-06
12 1400 2014-02-27 2014-03-02
13 1100 2014-03-10 2014-03-15
14 1400 2014-03-14 2014-03-19

orderline
order_id item_id order_qty
1 800 2
1 600 1
2 700 3
2 399 2
3 850 1
4 200 4
4 100 1
4 850 1
5 450 1
6 800 2
7 300 2
7 600 2
8 100 1
9 100 3
10 450 6
10 600 8
10 200 4
11 700 2
12 300 3
12 700 4
13 200 2
13 600 10
13 450 4
14 700 8
14 200 6
14 800 4
14 450 2

Explanation / Answer

Create a view named Under_100. It consists of the item_id, title, artist, unit_price and order_qty for every print with a unit_price under 100 dollars.

CREATE VIEW Under_100 AS

SELECT items.item_id, title, artist, unit_price, order_qty

FROM items, orderline

WHERE items.item_id = orderline.item_id

AND unit_price < 100.00;

Create a view named Allen. It consists of the customer_id, customer_name, customer_phone, title, and artist of each print ordered.

CREATE VIEW Allen AS

SELECT customers.customer_id, customer_name, customer_phone, title, artist

FROM customers, items, orderline, orders

WHERE customers.customer_id = orders.customer_id

AND orders.order_id = orderline.order_id

AND orderline.item_id = items.item_id;

4. Create a view named zip_27. It consists of the customer_name, customer_phone, title, artist

and date_shipped of each print ordered by a customer whose zip code begins with 27. */

CREATE VIEW zip_27 AS

SELECT customer_name, customer_phone, title, artist, ship_date

FROM customers, items, orders, orderline

WHERE customers.customer_id = orders.customer_id

AND orders.order_id = orderline.order_id

AND orderline.item_id = items.item_id

AND customer_zip LIKE '27%';

5. Create the following indexes. Use the indicated index name.

DEBUG COMMAND: SHOW INDEX FROM insert_name_of_column;

CREATE INDEX customer_id

ON customers (customer_id);

CREATE INDEX name

ON customers (customer_name);

CREATE INDEX shipped

ON orders (customer_id , ship_date);

Drop the name index.

DROP INDEX name ON customers;

   Specify the integrity constraint that the unit_price

of any print must be more than $35.

ALTER TABLE items

ADD CHECK (unit_price > 35.00);

. Ensure that the following are foreign keys

(that is, specify referential integrity) within the prints database.

/* A - customer_id is a foreign key in the orders table. */

ALTER TABLE items;

/* B - Item_id is a foreign key in the orderline table. */

ALTER TABLE items;

Add to the items table a new character field named type that is one character in length.

ALTER TABLE items

ADD TYPE CHARACTER(1);

/* . Change the type field in the items table to M for the print titled Skies Above. */

UPDATE items

SET TYPE='M'

WHERE title = 'Skies Above';

/*   Change the length of the artist field in the items table to 30. */

ALTER TABLE items

CHANGE COLUMN artist TO CHARACTER(30);

What command would you use to delete the orders table from the prints database?

(Do not delete the orders table.)

DROP TABLE orders;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.