create database project;
use project;

CREATE TABLE employee(
                        emp_id varchar(20) PRIMARY KEY,
                        emp_name varchar(50) NOT NULL,
                        emp_addr varchar(500),
                        emp_phone varchar(200),
                        emp_email varchar(40) NOT NULL);


CREATE TABLE customer(
                        cus_id varchar(20) PRIMARY KEY,
                        cus_name varchar(50) NOT NULL,
                        cus_addr varchar(500),
                        cus_phone varchar(200),
                        cus_email varchar(40) NOT NULL);

CREATE TABLE supplier(
                        supp_id varchar(20) PRIMARY KEY,
                        supp_name varchar(50) NOT NULL,
                        supp_addr varchar(500),
                        supp_phone varchar(200),
                        supp_email varchar(40) NOT NULL);


CREATE TABLE hardware(
                        hw_id varchar(20) PRIMARY KEY,
                        supp_id varchar(20),
                        category varchar(50) NOT NULL,
                        model varchar(100) NOT NULL,
                        hw_specs varchar(1500) NOT NULL,
                        hw_stk int(11) NOT NULL,
                        hw_price varchar(11),
                        FOREIGN KEY(supp_id) REFERENCES supplier(supp_id));

CREATE TABLE purchase(
                        purc_id varchar(20) PRIMARY KEY,
                        cus_id varchar(20),
                        hw_id varchar(20) NOT NULL,
                        qty varchar(10) NOT NULL,
                        purc_desc varchar(500),
                        price varchar(11) NOT NULL,
                        FOREIGN KEY(cus_id) REFERENCES customer(cus_id),
                        FOREIGN KEY(hw_id) REFERENCES hardware (hw_id));


show tables;

//inserting into customer
INSERT INTO customer VALUES ('1001','Aditi Karmakar','Kafrul,Dhaka','01754454914','adi.k@gmail.com');
INSERT INTO customer VALUES ('1002','Ziaul Hoque','Uttora,Dhaka','01654141910','ripon@gmail.com');
INSERT INTO customer VALUES ('1003','Atik Jawad','Bonani,Dhaka','01554454613','atik@gmail.com');
INSERT INTO customer VALUES ('1004','Rana Hossain','Gulsan,Dhaka','01854450911','rana@yahoo.com');
INSERT INTO customer VALUES ('1005','Ebrahim Rian','Mirpur,Dhaka''01750455719','rian@hotmail.com');
INSERT INTO customer VALUES ('1006','Rezia Sultana','Kafrul,Dhaka','01951454816','kona@gmail.com');
INSERT INTO customer VALUES ('1007','Khondokar Shuvo','Nurpur,Comilla','01554589613','shuvo@gmail.com');
INSERT INTO customer VALUES ('1008','Samiul Shorif','Darkin Sarta,comilla','01159850911','samiul545@yahoo.com');
INSERT INTO customer VALUES ('1009','Asif Khan','Sujanagar,Chittagong','01750495739','one_asif@hotmail.com');
INSERT INTO customer VALUES ('1010','Shadin Ahmed','Villasor,Coxs Bazar','01951447896','bdshadin54@gmail.com');

//inserting into employee
INSERT INTO employee VALUES ('2001','Ahmed Sojib','Motijil,Dhaka','01754789614','sojib.k@gmail.com');
INSERT INTO employee VALUES ('2002','Jahid Hoque','Kandirpur,Comilla','01654429520','jahid@gmail.com');
INSERT INTO employee VALUES ('2003','Jawad Khan','Bontoli,Chittagong','01554447891','jwaKhan@gmail.com');
INSERT INTO employee VALUES ('2004','Arif Hossain','Gulsan,Dhaka','01854198536','arif@yahoo.com');
INSERT INTO employee VALUES ('2005','Jamal Buiya','Mirpur,Dhaka','01750448969','jmbuiya@hotmail.com');
INSERT INTO employee VALUES ('2006','Jerin Sultana','Mohakhali,Dhaka','01945894816','jeri45@gmail.com');
INSERT INTO employee VALUES ('2007','Khondokar Ibrahim','Housing Estate,Comilla','01554589487','kmibrahim@gmail.com');

//inserting into Supplier
INSERT INTO supplier VALUES ('3001','Sony','Minato, Tokyo, Japan','017544586614','support@sony.com');
INSERT INTO supplier VALUES ('3002','Asus','Taipei, Taiwan','016544297854','info@asus.com');
INSERT INTO supplier VALUES ('3003','Acer','New Taipei City, Taiwan','01556392891','acer@acer.com');
INSERT INTO supplier VALUES ('3004','Gigabyte','New Taipei City, Taiwan','01854149836','more@gigab.com');
INSERT INTO supplier VALUES ('3005','Dell','Round Rock, Texas, United States','01714598969','support@dell.com');
INSERT INTO supplier VALUES ('3006','Microsoft','Redmond, Washington, United States','01947391816','info@microdoft.com');
INSERT INTO supplier VALUES ('3007','Apple','Cupertino, California, United States','015545852587','apple@appleinc.com');
INSERT INTO supplier VALUES ('3008','Lenovo','Morrisville, North Carolina, United States','01755896969','more@lenovo.com');
INSERT INTO supplier VALUES ('3009','A4tech','Taipei, Taiwan','019459836816','support@sapphire.com');
INSERT INTO supplier VALUES ('3030','Sapphire','Hong Kong','01554547857','info@saphare.com');
INSERT INTO supplier VALUES ('3031','Transcend','Hong Kong','01557457857','info@trs.com');
INSERT INTO supplier VALUES ('3032','Adata','Hong Kong','01559847857','info@adat.com');




//insert into hardware
INSERT INTO hardware VALUES ('4001','3003','Laptop','A315-51 7th','Processor - Intel Core i3 7130U, Generation - 7th Gen, Processor Clock Speed - 2.7GHz, CPU Cache - 3MB, Display Size - 15.6", Display Type - LED Display, Display Resolution - 1366 x 768, RAM - 4GB, RAM Type - DDR4, RAM Slot - 2, Max RAM Support - 12GB, HDD - 1TB, Graphics Chipset - Intel HD Graphics, Graphics Memory - Shared, Networking - LAN, WiFi, Bluetooth, Card Reader, WebCam, Display Port - HDMI, Audio Port - Combo, USB Port - 1 x USB3.0, 2 x USB2.0, Battery - 2 Cell Li-Ion, Operating System - Free Dos, Weight - 2.10Kg, Color - Black, Part No - NX.GNPSI.006, Warranty - 2 year (Battery, Adapter 1 year)','50','34000');
INSERT INTO hardware VALUES ('4002','3005','Laptop','INSPIRON 15-5567 7th Gen Intel Core i7','Processor - Intel Core i7 7500U, Generation - 7th Gen, Processor Clock Speed - 2.70GHz, CPU Cache - 4MB, Display Size - 15.6", Display Type - LED Display, Display Resolution - 1366 x 768, RAM - 8GB, RAM Type - DDR4, RAM Slot - 2, Max RAM Support - 16GB, HDD - 1TB HDD, Graphics Chipset - AMD RADEON R7 M445, Graphics Memory - 4GB, Optical Device - DVD RW, Networking - LAN, WiFi, Bluetooth, Card Reader, Webcam, Display Port - HDMI, Audio Port - Combo, USB Port - 2 x USB3.0, 1 x USB2.0, Battery - 3 Cell Prismatic, Backup Time - Up to 4 Hrs., Operating System - Windows 10 Home, Weight - 2.30Kg, Color - Black, Warranty - 2 year (Battery, Adapter 1 year), Country of Origin - USA','35','69000');
INSERT INTO hardware VALUES ('4003','3003','Laptop',' Aspire A515-51 7th Gen Intel Core i5','Model - Acer Aspire A515-51, Processor - Intel Core i5 7200U, Generation - 7th Gen, Processor Clock Speed - 2.5-3.1GHz, CPU Cache - 3MB, Display Size - 15.6", Display Type - LED Display, Display Resolution - 1366 x 768, RAM - 8GB, RAM Type - DDR4, HDD - 2TB, Graphics Chipset - Intel HD Graphics, Graphics Memory - Shared, Networking - LAN, WiFi, Bluetooth, Card Reader, WebCam, Display Port - HDMI, Audio Port - Combo, USB Port - 1 x USB3.1 Gen 1, 1 x USB3.0, 2 x USB2.0, Battery - 4 Cell Li-Ion, Backup Time - Up to 7 Hrs., Operating System - Free Dos, Weight - 2.2Kg, Color - Steel Gray, Part No - NX.GS2SI.002, Warranty - 2 year (Battery, Adapter 1 year)','28','50000');
INSERT INTO hardware VALUES ('4004','3005','Laptop','Aspire A515-51 7th Gen Intel Core i3','Processor - Intel Core i3 7100U, Generation - 7th Gen, Processor Clock Speed - 2.4GHz, CPU Cache - 3MB, Display Size - 15.6", Display Type - LED Display, Display Resolution - 1366 x 768, RAM - 4GB, RAM Type - DDR4, HDD - 1TB, Graphics Chipset - Intel HD Graphics, Graphics Memory - Shared, Networking - LAN, WiFi, Bluetooth, Card Reader, WebCam, Display Port - HDMI, Audio Port - Combo, USB Port - 1 x USB3.1 Gen 1, 1 x USB3.0, 2 x USB2.0, Keyboard Back-lit - Yes, Battery - 4 Cell Li-Ion, Operating System - Free Dos, Weight - 2.2Kg, Color - Steel Gray, Part No - NX.GS2SI.001, Warranty - 2 year (Battery, Adapter 1 year)','65','34000');
INSERT INTO hardware VALUES ('4005','3007','Laptop','MacBook Pro Mid 2017','Dual Core Intel Core i5, Processor Clock Speed - 2.3-2.6GHz, CPU Cache - 64MB eDRAM, Display Size - 13.3", Display Type - Retina Display, Display Resolution - 2560 x 1600, RAM - 8GB, RAM Type - LPDDR3 2133MHz, HDD - 128GB SSD, Graphics Chipset - Intel Iris Plus Graphics 640, Graphics Memory - Shared, Networking - WiFi, Bluetooth, HD Webcam, Display Port - DisplayPort via Thunderbolt port, Audio Port - 3.5mm Headphone Jack, Stereo Speakers, Dual microphones, USB Port - 2 x Thunderbolt 3 via USB Type-C, Battery - Li-Polymer, Backup Time - Up to 7 Hrs., Operating System - macOS Sierra, Weight - 1.37Kg, Color - Space Gray, Others - Non-Touchbar, Part No - MPXQ2LL/A, Warranty - 1 ','12','125000');
INSERT INTO hardware VALUES ('4006','3004','Mother Board','GA-H110M DS2V WP','CPU Sockets - LGA1151, Chipset - Intel H110 Express, Supported CPU - 6th Generation Intel Core i7 / i5 / i3 / Pentium / Celeron processors, Memory Type - DDR4, Memory MHz - 2133/2400MHz, Memory Channel - Dual, Memory Slot - 2, Memory Max. - 32GB, Graphic - Intel HD, PCI Express Slot - 1 x PCI Express x16 slot, running at x16, Storage Interface - 6Gb/s, LAN Chipset - Realtek GbE, LAN Speed (Mbps) - 10/100/1000Mbps, Audio Chipset - Realtek ALC887, Audio Channel - 2/4/5.1/7.1-Channel, USB Port - 4 x USB3.0/2.0, 6 x USB2.0/1.1, VGA Port - 1, DVI Port - 1, SATA Port - 4 x SATA, BIOS Type - 1 x 64 Mbit flash, BIOS Model - AMI UEFI BIOS, BIOS Version - PnP 1.0a, DMI 2.7, WfM 2.0, SM BIOS 2.7, ACPI 5.0, Supported Operating System - Windows 7 / 8.1 / 10, Form Factor - Micro ATX, Warranty - 3 year','44','5600');
INSERT INTO hardware VALUES ('4007','3002','Mother Board','ROG STRIX B250G GAMING','CPU Sockets - LGA1151, Chipset - Intel B250, Supported CPU - Intel 7th Gen Core i7, i5, i3, Pentium, Celeron Processors, Memory Type - DDR4, Memory MHz - 2400/2133 MHz, Memory Slot - 4, Memory Max. - 64GB, Graphic - Intel HD, PCI Express Slot - 1 x PCIe 3.0/2.0 x1, 2 x PCIe 3.0/2.0 x1, Storage Interface - 6Gb/s, LAN Chipset - Intel I219V, Audio Chipset - SupremeFX, Audio Channel - 8-Channel, USB Port - 6 x USB 3.1, 6 x USB 2.0, DVI Port - 1, HDMI Port - 1, SATA Port - 6 x SATA, BIOS Type - 1 x 128 Mb Flash ROM, BIOS Model - UEFI AMI BIOS, BIOS Version - PnP, DMI 3.0, WfM 2.0, SM BIOS 3.0, ACPI 6.1, Supported Operating System - Windows 10, 8.1, 7, Form Factor - Micro ATX, Special Features - ROG CPU-Z, Overwolf, Others - 1 x M.2, Warranty - 3 Year','45','8800');
INSERT INTO hardware VALUES ('4008','3002','Mother Board','PRIME B250M-K DDR4','Sockets - LGA1151, Supported CPU - 7th/6th Generation Core i7/Core i5/Core i3/Pentium/Celeron Processors, Chipset - Build in Intel B250, RAM Type - DDR4, RAM Bus (MHz) - 2400/2133 MHz Non-ECC, RAM Max. (GB) - 32GB, RAM Slot - 2, PCI Express x16 Slot - 1 x PCIe 3.0/2.0 x16, SATA Port - 6 x SATA 6Gb/s, Audio Chipset - Realtek ALC887, Audio Channel - 8-Channel, LAN Chipset - Realtek RTL8111H, 1 x Gigabit LAN, USB Interface - USB3.0/2.0 / USB2.0/1.1, USB Port - 6 x USB3.0/2.0 (Type-A), 6 x USB2.0/1.1, Specialty - key points: 1 x M.2, VGA Port - 1 x D-Sub, DVI Port - 1 x DVI-D, Warranty - 3 Year','50','7000');
INSERT INTO hardware VALUES ('4009','3004','Mother Board','GA-AB350M-Gaming3 DDR4','CPU Sockets - AM4, Chipset - AMD B350, Supported CPU - AMD Ryzen, 7th Generation A-series, Athlon Processors, Memory Type - DDR4, Memory MHz - 3200(O.C.)/ 2933(O.C.)/ 2667/ 2400/ 2133MHz, Memory Channel - Dual, Memory Slot - 4, Memory Max. - 64GB, Graphic - Integrated Graphics, PCI Express Slot - 1 x PCI Express x16 slot (running at x16, x4), 1 x PCI Express x1, Storage Interface - 6Gb/s, LAN Chipset - Realtek GbE LAN, LAN Speed (Mbps) - 10/100/1000Mbps, Audio Chipset - Realtek ALC887 codec, Audio Channel - 2/4/5.1/7.1-channel, USB Port - 2 x USB3.1 Gen 2 Type-A, 2 x USB3.1 Gen 1, 6 x USB2.0, VGA Port - 1, DVI Port - 1, HDMI Port - 1, SATA Port - 6 x SATA, BIOS Type - 2 x 128 Mbit flash, BIOS Model - AMI UEFI BIOS, BIOS Version - PnP 1.0a, DMI 2.7','50','9000');
INSERT INTO hardware VALUES ('4010','3031','HDD','J25D3 1TB USB 3.0 ','Transcend J25D3, Storage - 1TB, Type - SATA External, RPM - 5400, Buffer (MB) - 8, From Factor (Inch) - 2.5, Interface - USB 2.0/ 3.0, Transfer Rate (MB/s) - USB 2.0/ 3.0, Warranty - 3 year','78','5200');
INSERT INTO hardware VALUES ('4011','3032','HDD','HC660 1TB USB 3.0 Gray','A Data HC660, Storage - 1TB, Type - External HDD, From Factor (Inch) - 2.5 Inch, Interface - USB 3.0, Color - Gray, Others - Operating system: Windows XP or later, Mac OS X 10.6 or later, Linux Kernel 2.6 or later, Warranty - 1 year','43','5300');
INSERT INTO hardware VALUES ('4012','3002','SSD','SU800S 128GB 2.5','Storage (GB) - 128GB, Type - SATA SSD, From Factor (Inch) - 2.5", Interface - SATA 6Gb/s, Transfer Rate (MB/s) - 560MB/s(Read) / 520MB/s (Write), Others - Shock resistance: 1500G/0.5ms, Sequential R/W , Color: Black, Warranty - 3 Year','222','5500');
INSERT INTO hardware VALUES ('4013','3001','SSD','TS120GSSD220S 120GB 2.5','Storage (GB) - 120GB, Type - SATA SSD, From Factor (Inch) - 2.5", Interface - SATA-III 6Gb/s, Transfer Rate (MB/s) - 550MB/s, Others - Weight : 63gm, Dimensions: 100mm x 69.85','63','8500');
INSERT INTO hardware VALUES ('4015','3008','RAM','512MB DDR 400 BUS','512MB, Type - DDR, Bus Speed(MHz) - 400, CAS Latency - 3, Voltage - 2.6, Warranty - Product Lifetime','87','500');
INSERT INTO hardware VALUES ('4016','3009','RAM','4GB DDR3 1600 BUS','Capacity(MB) - 4GB, Type - DDR 3, Bus Speed(MHz) - 1600, Number of Pin - 240, Warranty - Product Lifetime','15','3800');
INSERT INTO hardware VALUES ('4017','3008','RAM','Core RED 4GB DDR4 2400 BUS','Capacity(MB) - 4GB, Type - DDR4, Bus Speed(MHz) - 2400MHz, CAS Latency - 16-16-16-36, Voltage - 1.2V, Warranty - Product lifetime','43','4200');
INSERT INTO hardware VALUES ('4018','3031','RAM','Hyper Fury 4GB DDR4 2400','Capacity(MB) - 4GB, Type - DDR4, Bus Speed(MHz) - 2400MHz, Number of Pin - 288-pin, Voltage - 1.2V, Warranty - product Lifetime','37','4000');
INSERT INTO hardware VALUES ('4019','3032','RAM','Ripjaws V 4GB DDR4 2400 BUS','Long DeModel - G.Skill Ripjaws V, Capacity(MB) - 4GB, Type - DDR4, Bus Speed(MHz) - 2400MHz, Number of Pin - 288-Pin, CAS Latency - 15-15-15-35-2N, Voltage - 1.20v, Warranty - Product lifetimescription','67','4600');
INSERT INTO hardware VALUES ('4020','3002','Graphics Card','GT730-FML-2G 2GB DDR5','Graphics Engine Model - Asus GT730-FML-2G, Interface Bus - PCI Express 2.0, Engine Clock - Base-902MHz, Memory Clock - 1250 MHz, Memory Size - 2 GB, Memory Type - GDDR5, Memory Bus - 64-bit, Digital Max Resolution - 2560x1600, DirectX - 12, OpenGL - 4.4, VGA Port - 1, DVI Port - 1, HDMI Port - 1, HDCP Support - Yes, Cooling Fan - Single, Warranty - 2 Year','54','6200');
INSERT INTO hardware VALUES ('4021','3004','Graphics Card','NVIDIA GeForce N1030','Chipset - NVIDIA GeForce, Graphics Engine Model - Gigabyte NVIDIA GeForce N1030, Engine Clock - Base-1290MHz / Boost-1544MHz (OC), Base-1265MHz / Boost-1518MHz (Gaming), Memory Clock - 6008 MHz, Memory Size - 2GB, Memory Type - DDR5, Memory Bus - 64-bit, Digital Max Resolution - 4096x2160, DirectX - 12, OpenGL - 4.5, DVI Port - 1, HDMI Port - 1, Recommended PSU - 300W, Multi Display Capability - Dual Display, Cooling Fan - Single, PCB Form - ATX, Part No - GV-N1030OC-2GI, Warranty - 2 year','30','7000');
INSERT INTO hardware VALUES ('4022','3004','Graphics Card','GeForce GTX 1070','Chipset - GeForce GTX 1070, Graphics Engine Model - Gigabyte GeForce GTX 1070, Interface Bus - PCI-Express 3.0, Engine Clock - Base-1620MHz / Boost-1822MHz (OC), Base-1594MHz / Boost-1784MHz (Gaming), Memory Clock - 8008 MHz, Memory Size - 8 GB, Memory Type - GDDR5, Memory Bus - 256-bit, Digital Max Resolution - 7680x4320, DirectX - 12, OpenGL - 4.5, DVI Port - 1, HDMI Port - 1, Displayport - 3, Recommended PSU - 500W, Power Connectors - 8-pin x 1, Multi Display Capability - Quad Display, Cooling Fan - Triple, PCB Form - ATX, Warranty - 2 year','55','44000');



ALTER TABLE hardware MODIFY



//inserting into purchase

INSERTINTO purchase  VALUES('10000','1001','4001','1','paid','34000');
INSERT INTO purchase VALUES('10001','1002','4005','3','paid','375000');
INSERT INTO purchase VALUES('10002','1003','4003','2','paid','100000');
INSERT INTO purchase VALUES('10003','1001','4009','2','paid','18000');
INSERT INTO purchase VALUES('10004','1004','4020','1','paid','6200');