Question

Insert the bottom two rows to each table we made in class and update the two in gray in stock and product tables. Provide your SQL code in notepad or word. You can include a screen shot of your select statements showing database populated with your name in ure showing your account Product Product Name Manufacturer 1 102 103 509 Product Product Description Ergonomic keyboard with rest pad HP Model 3456 5x7 inch monitor with stylus Series 5 Pink 32G memory 8 core intel processor Printer Ipad version 25 Apple Watch MacBook Pro 2 878 2 Manufacturer Manufacturimo Manufacturer Apple Acor Toshiba Stock O ntity 100 Product 101 102 103 509 876 100 500 600

Answer 1

First we create the database by using command:-

mysql> create database manufacturing;
Query OK, 1 row affected (0.00 sec)

mysql> use manufacturing;
Database changed.

Now we create the tables product,manufacturer and stock respectively as follows:-

mysql> create table product(productID int(3)not null,productName varchar(20),productDescription varchar(50), manufacturerID int(1), primary key (productID));

mysql> create table manufacturer(manufacturerName varchar(20)not null,manufacturerID varchar(1), primary key (manufacturerID));

mysql> create table stock(productID int(3)not null,quantity int(5),foreign key (productID) references product (productID));

Now we insert the records in each tables as follows:-

mysql> insert into product values(101,'keyboard','economic keyboard with rest pad',1);

mysql> insert into product values(102,'printer','HP model 3456',1);

mysql> insert into product values(103,'lpad version 2.5','5*7 inch monitor with stylus',2);

mysql> insert into manufacturer values('Dell',1);

mysql> insert into manufacturer values('Apple',2);

mysql> insert into stock values(101,100);

mysql> insert into stock values(102,400);

mysql> insert into stock values(103,300);

Now we display the tables with the records as follows:-

mysql> select * from product;
+-------------+---------------------+---------------------------------------------+---------------------+
| productID | productName | productDescription | manufacturerID |
+-------------+---------------------+---------------------------------------------+---------------------+
| 101 | keyboard    | economic keyboard with rest pad | 1 |
| 102 | printer | HP model 3456 | 1 |t
| 103 | lpad version 2.5 | 5*7 inch monitor with stylus | 2 |
+-------------+---------------------+---------------------------------------------+---------------------+

mysql> select * from manufacturer;
+--------------------------+---------------------+
| manufacturerName | manufacturerID |
+--------------------------+---------------------+
| Dell | 1 |
| Apple | 2 |
+--------------------------+----------------------+

mysql> select * from stock;
+-------------+------------+
| productID | quantity |
+-------------+------------+
| 101 | 100 |
| 102 | 400 |
| 103 | 300 |
+-------------+------------+

Now we insert the new records(2 tuples) in each tables as follows:-

mysql> insert into product values(509,'Apple Watch','Serious 5 pink',2);

mysql> insert into product values(876,'Macbook pro','32G memory 8 core intel processor',2);

mysql> insert into manufacturer values ('Acer',3);

mysql> insert into manufacturer values ('Toshiba',4);

mysql> insert into stock values (509,500);

mysql> insert into stock values (876,600);

Now we update the tables according to the question as follows:-

mysql> update product set productDescription="Dell model 6543" where productID=102;
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update stock set quantity=400 where productID=102;
Rows matched: 1 Changed: 0 Warnings: 0

Now the new tables after adding new records and updating the existing records will become as follows:-

mysql> select * from product;
+-------------+---------------------+------------------------------------------------+---------------------+
| productID | productName | productDescription | manufacturerID |
+-------------+---------------------+------------------------------------------------+---------------------+
| 101 | keyboard | economic keyboard with rest pad | 1 |
| 102 | printer | Dell model 6543 | 1 |
| 103 | lpad version 2.5 | 5*7 inch monitor with stylus | 2 |
| 509 | Apple Watch | Serious 5 pink | 2 |
| 876 | Macbook pro | 32G memory 8 core intel processor | 2 |
+-------------+----------------------+------------------------------------------------+--------------------+
mysql> select * from manufacturer;
+--------------------------+---------------------+
| manufacturerName | manufacturerID |
+--------------------------+----------------------+
| Dell | 1 |
| Apple | 2 |
| Acer | 3 |
| Toshiba | 4 |
+---------------------------+---------------------+

mysql> select * from stock;
+-------------+-----------+
| productID | quantity|
+-------------+-----------+
| 101 | 100 |
| 102 | 400 |
| 103 | 300 |
| 509 | 500 |
| 876 | 600 |
+-------------+------------+

The screensot of all the steps in mysql in ubantu operating system ase as follows:-

untu Running Machine View Orade VM VirtualBox Input Devices Help mysql> create database manufacturing: Query OK, 1 row affected (0.00 sec), mysql> use manufacturing; Database changed mysql> create table product (productID int(3) not null, productName varchar(20), productDescription varchar(50), manufacturerID int(1) primary key (productID)); Query OK, 0 rows affected (0.10 sec) mysql> create table manufacturer(manufacturer Name varchar(20) not null, manufacturerID varchar(1), primary key (manufacturerID)) _Query OK, Orowsattected (0.05 sec) mysql> create table stock(productID int(3) not null, quantity int(5), foreign key (productID) references product (productID Query OK, Orows affected (0.06 sec) mysql> insert into product values (101,'keyboard', 'economic keyboard with rest pad', 1); Query OK, 1 row affected (0.04 sec) mysql> insert into product values (102, 'printer', 'HP model 3456',1); Query OK, 1 row affected (0.01 sec) mysql> insert into product values (103, 'lpad version 2.5','5*7 inch monitor with stylus', 2); Query OK, 1 row affected (0.01 sec) mysql> select from product; | productID | productName | productDescription manufacturerID | 101 keyboard 102 Irinter | economic keyboard with lest pad Hemodel 3456 Plain Text Tab Width: 14 t h 1. COLI N S

-------- ------------------ --- - - - - - - - - - ------+- ------------ ----- ---- productID | productName productDescription | manufacturerID | ----+---- 101 | keyboard economic keyboard with rest pad 1 | 102 | printer HP model 3456 1 | 103 | Ipad version 2.5 | 5*7 inch monitor with stylus ------------------- ----------------+ 13 rows in set (0.00 sec), mysql> insert into manufacturer values('Dell',1); Query OK, 1 row affected (0.01 sec) mysql> insert into manufacturer values ('Apple',2); 'Query Ok, 1 row affected (0.01 sec) mysql> select * from manufacturer; -----------t ... -------+ manufacturerName manufacturerID | Dell Apple 1 2 A 2 rows in set (0.01 sec) mysql> insert into stock values (101,100); Query OK, 1 row affected (0.00 sec) mysql> insert into stock values (102,400); Query OK, 1 row affected (0.00 sec)

mysql> insert into stock values (103,300); Query OK, 1 row affected (0.01 sec) 'mysql> select * from stock; --- -- - - -- - - - -- - - + productID / quantity | ------ ----------+ 101 100 400 103 300 102 13 rows in set (0.00 sec) mysql> insert into product values (509, 'Apple Watch', 'Serious 5 pink', 2); Query OK, 1 row affected (0.01 sec) mysql> insert into product values (876, 'Macbook pro', '32G memory 8 core intel processor',2); Query OK, 1 row affected (0.00 sec) mysql> update product set productDescription="Dell model 6543" where productID=102; Query OK, 1 row affected (0.08 sec) A Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from product; productID | productName productDescription | manufacturerID | 101 keyboard 102 | printer economic keyboard with rest pad Dell model 6543

IKOWS matcnea: 1 unangea: 1 warnings: U mysql> select * from product; ------- productID | productName ------ --- ----- product Description ----+---------------- | manufacturerID | - - - - - 11 101 | keyboard economic keyboard with rest pad 102 | printer Dell model 6543 103 | lpad version 2.5 | 5*7 inch monitor with stylus 509 | Apple Watch Serious 5 pink 876 | Macbook pro 32G memory 8 core intel processor 1 2 2 2 | 5 rows in set (0.00 sec) a mysql> insert into manufacturer values ('Acer',3); Query OK, 1 row affected (0.00 sec) mysql> insert into manufacturer values ('Toshiba', 4); Query OK, 1 row affected (0.04 sec) mysql> select * from manufacturer; manufacturerName | manufacturerID Dell Apple Acer Toshiba rows in set (0.00 sec)

Dell Apple Acer Toshiba WNE -----+---- '4 rows in set (0.00 sec) 'mysql> insert into stock values (509,500); Query OK, 1 row affected (0.00 sec) 'mysql> insert into stock values (876, 600); Query OK, 1 row affected (0.01 sec) A а) mysql> update stock set quantity=400 where productID=102; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: © Warnings: 0 mysql> select * from stock; | productID | quantity | 101 102 103 1 509 876 | 100 400 300 500 600 5 rows in set (0.00 sec)
These are the complete photos of all the steps shown in the pictures.