Question

1. Write a script to create the following tables with attributes as specified(SQL)
   1. Customer table with Customer’s id, name, address, city as varchar, customer’s date of birth as date type and zip code number where the customers id is the primary key in the table, name and date of birth are mandatory. Id has a 10-character limit, name, address and city have a 50-character limit, zip has a 5-character limit
   2. Product Table with Product id, description and finish as varchar, price as float with id as the primary key in the table. Id and description are mandatory and should have character length of 15 and 125 respectively. Price is mandatory and should have a character length of 5. Customer id from the customer table is a foreign key in the product table. A product can exist without a customer.
2. Insert the following records in the tables created above. (YOU HAVE ALL THE INFORMATION YOU NEED)
   1. Customer table: John Smith, 123 Main St, Buffalo 14214 born on 10/8/1997. Customer id = 894132 Product Table: Description = Coffee Table, costs $635.00.

Answer 1

Solution:

Customer table:

1.Creating a customer table:

- To create a table, the following syntax is followed.

- Mandatory fields can be specified using NOT NULL.

create table customer(
customer_id varchar(10) primary key,
name varchar(50) NOT NULL,
address varchar(50),
city varchar(50),
date_of_birth date NOT NULL,
zip_code number(5)
);

2. Inserting values in the table:

insert into customer values("Customer ID : 101","Name : John","Address : 123 Main St","City : NewYork","DOB : 10/08/1997","ZipCode : 50006");

3. Displaying the inserted values of customer table:

- To display all the inserted values of the table, we can use * operator.

select * from customer;

Product Table:

1.Creating a product table:

create table product(
product_id varchar(15) NOT NULL primary key,
customer_id varchar(10),
description varchar(125) NOT NULL,
finish varchar(10),
price float(5) NOT NULL,
foreign key(customer_id)
references customer(customer_id)
  
);

2. Inserting values in the table:

insert into product values("Product ID : 500","Customer ID : 101","Description : coffee","Finish : no","Price : 635.5");

3. Displaying the inserted values of Product table:

select * from product;

Output:

Customer ID : 101|Name : John|Address : 123 Main St|City : NewYork|DOB : 10/08/1997|ZipCode : 50006
Product ID : 500|Customer ID : 101|Description : coffee|Finish : no|Price : 635.5