1. Now, consider the following instances of the Customer and Reserves tables: (2

Question

1. Now, consider the following instances of the Customer and Reserves tables: (20 Pts) Customer Reserves CID Name Age City State CID Title Joe 27 Berkeley CA 23 akland CA Albany CA Hary 38 Berkeley CA Sleeper Bananas 2 Sleeper Date 01/01/01 01/01/01 01/01/01 2 Betty 3 Sally 49 A Annie Hal 02/01/01 2 Sleeper Interiors 4 Sleeper 02/01/01 02/01/0 03/01/01 1) Write the DDL code to build the above database. (8 Pts) Please determine the data type for each attribute by yourself. Make sure you can set primary key and foreign key correctly. i. . iii. Create table command onlv.

Explanation / Answer

1.CREATE TABLE CUSTOMER(CID INT PRIMARY KEY,NAME VARCHAR2(8)NOT NULL,AGE NUMBER(3),CITY VARCHAR2(12),STATE CHAR(4));

Note: primary key is CID data type is integer.

CREATE TABLE RESERVES(cid int,TITLE varchar2(15),d_ate varchar2(12),constraint fk_cust foreign key(cid) references customer(cid));

Note:foreign key is cid in reserves table . (cid is primary key in customer tavble.)

date attribute is defines d_ate because date is a date types .key words/data types not asigned as a attributes.

2.(a) 7 rows selected.

SQL> select * from customer C, reserves2 R where C.cid=R.cid;

CID NAME AGE CITY STAT CID TITLE D_ATE
---------- -------- ---------- ------------ ---- ---------- --- ------
1 joe 27 Berkely CA 1 Sleeper 01-01-01

1 joe 27 Berkely CA 1 Bananas 01-01-01

2 Bety 23 Oakland CA 2 Sleeper 01-01-01
  

2 Bety 23 Oakland CA 2 Annie Hall 02-01-01

2 Bety 23 Oakland CA 2 Sleeper 02-01-01

2 Bety 23 Oakland CA 2 Interiors 02-01-01
  
4 Harry 38 Berkely CA 4 Sleeper 03-01-01

7 rows selected.

(b) 4 rows selected.

SQL>select * from customer C,reserves2 R where C.cid=R.cid and C.city='Oakland';

CID NAME AGE CITY STAT CID TITLE D_ATE
---------- -------- ---------- ------------ ---- ---------- ------ ---------
2 Bety 23 Oakland CA 2 Sleeper 01-01-01

2 Bety 23 Oakland CA 2 Annie Hall 02-01-01

2 Bety 23 Oakland CA 2 Sleeper 02-01-01
  
2 Bety 23 Oakland CA 2 Interiors 02-01-01

(c) 3 rows selected

SQL> select distinct title from customer C,reserves2 R where C.cid=R.cid and C.city='Oakland';

TITLE
---------------
Interiors
Sleeper
Annie Hall

(d) 4 rows selected

SQL> select title,count(*) from customer C,reserves2 R where C.cid=R.cid group by title;

TITLE COUNT(*)
--------------- ----------
Interiors 1
Sleeper 4
Annie Hall 1
Bananas 1

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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