Create Table-Exercise

• Script Name Create Table-Exercise
• Description Step1:Create AUTOPARTS table; Step2:Insert Values; Step3:View the AUTOPARTS table; Step4:Change Pirce from 18 to 81 and View the AUTOPARTS table again; Step5:Only Keep OE# start with '62150'; Step6:Create PARTSINFO table and give values; Step7:Combine Two Tables by mathcing ID and create a newtable called "Newtable"; Step8:View the Newtable, order by Price from Low to high.
• Area SQL General
• Contributor Michael Di Su
• Created Wednesday May 17, 2017
• Statement 1
``Create table AUTOPARTS (ID Integer Primary Key, OE# Char(20), Price Integer, Model Char(20))``
`Table created.`
• Statement 2
``Insert into AUTOPARTS values (1,'62150A3278',168,'HILUX')``
`1 row(s) inserted.`
• Statement 3
``Insert into AUTOPARTS values (2,'62150B3278',70,'CAMRY')``
`1 row(s) inserted.`
• Statement 4
``Insert into AUTOPARTS values (3,'62150C3278',9999,'COMMODORE')``
`1 row(s) inserted.`
• Statement 5
``Insert into AUTOPARTS values (4,'86001-7800',175,'HILUX')``
`1 row(s) inserted.`
• Statement 6
``Insert into AUTOPARTS values (5,'86000-7900',19,'X-TRAIL')``
`1 row(s) inserted.`
• Statement 7
``Select * from AUTOPARTS``
```
IDOE#PRICEMODEL162150A3278          168HILUX               262150B3278          70CAMRY               362150C3278          9999COMMODORE           486001-7800          175HILUX               586000-7900          19X-TRAIL
5 rows selected.```
• Statement 8
``Update AUTOPARTS set Price=350 where ID=3``
`1 row(s) updated.`
• Statement 9
``Select * from AUTOPARTS``
```
IDOE#PRICEMODEL162150A3278          168HILUX               262150B3278          70CAMRY               362150C3278          350COMMODORE           486001-7800          175HILUX               586000-7900          19X-TRAIL
5 rows selected.```
• Statement 10
``Delete from AUTOPARTS where OE# not like '62150%'``
`2 row(s) deleted.`
• Statement 11
``Create table PARTSINFO (ID Integer Primary Key, Year_Range Char(20),Part_Type Char(20),Description Char(20))``
`Table created.`
• Statement 12
``Insert into PARTSINFO values (1,'2007-2010','BUMPER','2WD')``
`1 row(s) inserted.`
• Statement 13
``Insert into PARTSINFO values (2,'2015-2017','GUARD','RZ With FLARE')``
`1 row(s) inserted.`
• Statement 14
``Insert into PARTSINFO values (3,'2005-2009','BONNET','SS-V')``
`1 row(s) inserted.`
• Statement 15
``Insert into PARTSINFO values (4,'2015-2017','BUMPER','4WD')``
`1 row(s) inserted.`
• Statement 16
``Insert into PARTSINFO values (5,'2003-2006','LIGHT','2.5L PETROL')``
`1 row(s) inserted.`
• Statement 17
``Select * from PARTSINFO``
```
IDYEAR_RANGEPART_TYPEDESCRIPTION12007-2010           BUMPER              2WD                 22015-2017           GUARD               RZ With FLARE       32005-2009           BONNET              SS-V                42015-2017           BUMPER              4WD                 52003-2006           LIGHT               2.5L PETROL
5 rows selected.```
• Statement 18
``````Create table Newtable as
Select AUTOPARTS.ID, AUTOPARTS.OE#, AUTOPARTS.Price, AUTOPARTS.Model,PARTSINFO.Year_Range, PARTSINFO.Part_Type, PARTSINFO.Description
from AUTOPARTS inner join PARTSINFO on AUTOPARTS.ID=PARTSINFO.ID``````
`Table created.`
• Statement 19
``Select * from Newtable order by Price ASC``
```
IDOE#PRICEMODELYEAR_RANGEPART_TYPEDESCRIPTION262150B3278          70CAMRY               2015-2017           GUARD               RZ With FLARE       162150A3278          168HILUX               2007-2010           BUMPER              2WD                 362150C3278          350COMMODORE           2005-2009           BONNET              SS-V
3 rows selected.```