Question

DBMS Create/Insert/Update SQL

I need the create, insert, and update SQL statement for this table:

Customer
PK	Customer ID	Text
	Phone Number	int
	name	text
	address ID	int
	email	text
FK	vendor ID	int

Vendor is the name of the table the FK comes from.

Answer 1

Create command

create table customer(customer_id varchar(10) primary key, phone_number int,
name varchar(15), address_id int, email varchar(20), vendor_id int references vendor(vendor_id));

output:

Table created.

SQL> desc customer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NOT NULL VARCHAR2(10)
PHONE_NUMBER NUMBER(38)
NAME VARCHAR2(15)
ADDRESS_ID NUMBER(38)
EMAIL VARCHAR2(20)
VENDOR_ID NUMBER(38)

Text datatypes can be termed as varchar type in oracle. So we are using varchar types for text attributes in oracle. for the successful creation of the customer table Vendor table must be created first as vendor_id is a foreign key referencing vendor table;

Insertion command for customer table:

SQL> insert into vendor(vendor_id) values(1234);

output:

1 row created.

SQL> insert into customer values('C001', 8297435672, 'Scott', 707, '[email protected]', 1234);

output:

1 row created.

SQL> select * from customer;

CUSTOMER_I PHONE_NUMBER NAME ADDRESS_ID EMAIL VENDOR_ID
---------- ------------ --------------- ---------- -------------------- ----------
C001 8297435672 Scott 707 [email protected] 1234

For inserting a row into customer table we need to provide value for the vendor_id attribute which is a foreign key referencing vendor table. So the value of the vendor_id must exist in the vendor table before we insert it into customer table. Therefore in the above insert statement we first inserted a value into the vendor_id attribute of vendor table then insert a new row into the customer table with that vendor_id;

update statement:

Suppose we want to update the name of a customer in customer table:

SQL command

SQL> update newcustomer set name='Bob' where name='Scott';

1 row updated.

SQL> select * from customer;

CUSTOMER_I PHONE_NUMBER NAME ADDRESS_ID EMAIL VENDOR_ID
---------- ------------ --------------- ---------- -------------------- ----------
C001 8297435672 Bob 707 [email protected] 1234

update command updates the row/rows in which the where condition satisfies and sets the attribute mentioned in the query to a value provided.

[ N.B: if you face any difficulty in understanding please contact through comments.]