Insert the following data in the tables usinginsert into statements: customer: 10010, Johnson, Albert, A, 612,
Question:
Insert the following data in the tables using insert into statements:
- customer:
10010, Johnson, Albert, A, 612, 8442573
10011, Edwards, Leona, K, 763, 8941238
10012, Smith, Walter, W, 612, 8942285
10013, Roberts, Paul, F, 612, 2221672
10014, Orlando, Myla, NULL, 615, 2971228
- invoice:
1001, 10011, 2008-08-03
1002, 10014, 2008-08-04
1003, 10012, 2008-03-20
1004, 10014, 2008-09-23
- vendor:
232, Bryson, Smith, 615, 2233234
235, Walls, Anderson, 615, 2158995
236, Jason, Schmidt, 651, 2468850
- product:
12321, hammer, 189 ,20, 232
65781, chain, 12, 45, 235
34256, tape, 35, 60, 236
12333, drill, 200 ,10, 232
- line:
1001, 12321, 1
1001, 65781, 3
1002, 34256, 6
1003, 12321, 5
1002, 12321, 6
Question 3: Checking entity and referential integrity:
Jot(Write) an SQL statement to do each of the following tasks in the given order (try 1, 2, 3, 4, then 5). Explain whether and why the statement is correctly executed or not
- Insert the following entry in CUSTOMER
10012, 'Juan', 'Rodriguez', 'J', 612, 7788776
- Insert the following entry in INVOICE
1005, 10017, '2008-11-30'
- Insert the following entry in PRODUCT
12322, 'hammer', 189, 20, 231
- Insert the following entry to the VENDOR table
231,'Adam', 'Eric', 615, 2158995
- Insert the following entry in PRODUCT
12322, 'coil', 189, 20, 231
Question 4: SQL Queries
Write SQL statements to answer each of the following questions.
- List the Customer Code, Last name, and Customer phone for all customers.
- List the invoice number and invoice date for all invoices of customer number 10014.
- List the product code, product description and product quantity for products with invoice number 1001.
- List all product description and product price supplied by vendor whose vendor contact is 'Somebody'.
- Produce a list of product description, vendor name, and vendor phone for all products with quantity less than or equal to 60.
- For each product bought by a customer, list product description, customer's first name and last name. Make a database using your name. Then create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in your "create table" statements.
Customer | Invoice | Vendor | ||
cus_code : integer | inv_number :integer | vend_code : integer | ||
cus_lname : varchar(20) | cus_code :integer | vend_name : varchar(30) | ||
cus_fname : varchar(20) | inv_date :date | vend_contact : varchar(30) | ||
cus_initial : char(1) | foreign key cus_code | vend_areacode : integer | ||
cus_areacode : integer | references customer table | vend_phone : integer | ||
cus_phone : integer | ||||
Product | Line | |||
prod_code : integer | inv_number : integer | |||
prod_desc : varchar(50) | prod_code : integer | |||
prod_price : integer | line_units : integer | |||
prod_quant : integer | foreign key inv_number | |||
vend_code : integer | references Invoice table | |||
foreign key vend_code | foreign key prod_code | |||
references Vendor table | references Product table |
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel