Create Table-Exercise

View All Scripts Login to Run Script

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


ID OE# PRICE MODEL
1 62150A3278          168 HILUX               
2 62150B3278          70 CAMRY               
3 62150C3278          9999 COMMODORE           
4 86001-7800          175 HILUX               
5 86000-7900          19 X-TRAIL             

5 rows selected.

ID	OE#	PRICE	MODEL
1	62150A3278	168	HILUX
2	62150B3278	70	CAMRY
3	62150C3278	9999	COMMODORE
4	86001-7800	175	HILUX
5	86000-7900	19	X-TRAIL

Statement 8

Update AUTOPARTS set Price=350 where ID=3

1 row(s) updated.

Statement 9

Select * from AUTOPARTS


ID OE# PRICE MODEL
1 62150A3278          168 HILUX               
2 62150B3278          70 CAMRY               
3 62150C3278          350 COMMODORE           
4 86001-7800          175 HILUX               
5 86000-7900          19 X-TRAIL             

5 rows selected.

ID	OE#	PRICE	MODEL
1	62150A3278	168	HILUX
2	62150B3278	70	CAMRY
3	62150C3278	350	COMMODORE
4	86001-7800	175	HILUX
5	86000-7900	19	X-TRAIL

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


ID YEAR_RANGE PART_TYPE DESCRIPTION
1 2007-2010           BUMPER              2WD                 
2 2015-2017           GUARD               RZ With FLARE       
3 2005-2009           BONNET              SS-V                
4 2015-2017           BUMPER              4WD                 
5 2003-2006           LIGHT               2.5L PETROL         

5 rows selected.

ID	YEAR_RANGE	PART_TYPE	DESCRIPTION
1	2007-2010	BUMPER	2WD
2	2015-2017	GUARD	RZ With FLARE
3	2005-2009	BONNET	SS-V
4	2015-2017	BUMPER	4WD
5	2003-2006	LIGHT	2.5L PETROL

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


ID OE# PRICE MODEL YEAR_RANGE PART_TYPE DESCRIPTION
2 62150B3278          70 CAMRY               2015-2017           GUARD               RZ With FLARE       
1 62150A3278          168 HILUX               2007-2010           BUMPER              2WD                 
3 62150C3278          350 COMMODORE           2005-2009           BONNET              SS-V                

3 rows selected.

ID	OE#	PRICE	MODEL	YEAR_RANGE	PART_TYPE	DESCRIPTION
2	62150B3278	70	CAMRY	2015-2017	GUARD	RZ With FLARE
1	62150A3278	168	HILUX	2007-2010	BUMPER	2WD
3	62150C3278	350	COMMODORE	2005-2009	BONNET	SS-V

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database