Question

DBMS Create/Insert/Update SQL

I need the create, insert, and update SQL statement for this table as if it were being added to MySQL (please give explanations for each line of SQL code and a copy of the code as it would be entered into the query by itself:

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

1) Sql Query for creating vendor table:-

create table Vendor(vendor_ID int primary key , vendor_name varchar(100))

primary key synatx is used to create primary key .

2) Sql Query for creating Customer table:-

create table Customer(Customer_ID varchar(100) primary key, Phone_Number  int,name varchar(100),address_ID int,email   varchar(100),vendor_ID int , CONSTRAINT FK_PersonOrder FOREIGN KEY(vendor_ID) REFERENCES vendor(vendor_ID))

Code CONSTRAINT FK_PersonOrder FOREIGN KEY(vendor_ID) REFERENCES vendor(vendor_ID) is used to create foreign key vendor_ID refering to primary key of vendor_ID in vendor table.

3) Sql Query to insert data into vendor table:-

INSERT INTO `vendor` (`vendor_ID`, `vendor_name`) VALUES ('1', 'mahendra'), ('2', 'rahul');

4) Sql Query to insert data into customer table:-

INSERT INTO `customer` (`Customer_ID`, `Phone_Number`, `name`, `address_ID`, `email`, `vendor_ID`) VALUES ('1', '2222222', 'tom', '1', '[email protected]', '1');

5) Update query to update data in customer table:

UPDATE customer SET Phone_Number =111111 WHERE Customer_ID=1

6)Update query to update data in vendor table:

UPDATE vendor SET `vendor_name`='mohan' WHERE vendor_ID = 2