[Show all top banners]

newbie69
Replies to this thread:

More by newbie69
What people are reading
Subscribers
:: Subscribe
Back to: Computer/IT Refresh page to view new replies
 sql question
[VIEWED 11486 TIMES]
SAVE! for ease of future access.
Posted on 06-14-17 6:32 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

DROP TABLE CUSTOMER_t CASCADE CONSTRAINTS ;
DROP TABLE VEHICLE_t CASCADE CONSTRAINTS ;
DROP TABLE PARTS_t CASCADE CONSTRAINTS ;
DROP TABLE SERVICE_TICKET_t CASCADE CONSTRAINTS ;
DROP TABLE TECHNICIAN_t CASCADE CONSTRAINTS ;
DROP TABLE INVOICE_t CASCADE CONSTRAINTS ;

CREATE TABLE CUSTOMER_t
(Customer_ID VARCHAR(15) NOT NULL,
First_Name VARCHAR(15),
Last_Name VARCHAR(15),
Address_Line1 VARCHAR(30),
CITY VARCHAR(30),
STATE CHAR(2),
ZIP_CODE CHAR(10),
PHONE_NUMBER CHAR(10),
INSURANCE_COMPANY VARCHAR(30),
POLICY_NUMBER VARCHAR(15),
CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID));

CREATE TABLE VEHICLE_t
(VIN_NUMBER VARCHAR(17) NOT NULL,
Year VARCHAR(17),
Make CHAR(4),
Model VARCHAR(15),
Trim_Package VARCHAR(15),
Color VARCHAR(15),
CONSTRAINT VEHICLE_PK PRIMARY KEY (VIN_NUMBER));

CREATE TABLE PARTS_t
(Part_Number number NOT NULL,
Part_Name VARCHAR(30),
Part_Deskription VARCHAR(30),
Wholesale_Price NUMBER(8,2),
Retail_Price NUMBER(8,2),
CONSTRAINT PARTS_PK PRIMARY KEY (Part_Number));

CREATE TABLE SERVICE_TICKET_t
(Ticket_ID CHAR(5) NOT NULL,
VIN_Number VARCHAR(17),
CUSTOMER_ID VARCHAR(15),
DATE_RECEIVED DATE,
Notes VARCHAR(150),
CONSTRAINT SERVICE_TICKET_PK PRIMARY KEY (Ticket_ID),
CONSTRAINT SERVICE_TICKET_FK1 FOREIGN KEY (Ticket_ID) REFERENCES VEHICLE_t(VIN_NUMBER),
CONSTRAINT SERVICE_TICKET_FK2 FOREIGN KEY (Ticket_ID) REFERENCES CUSTOMER_t(Customer_ID));

CREATE TABLE TECHNICIAN_t
(Tech_ID CHAR(3) NOT NULL,
Last_Name VARCHAR(15),
First_Name VARCHAR(15),
CONSTRAINT TECHNICIAN_PK PRIMARY KEY (Tech_ID));

CREATE TABLE INVOICE_t
(Invoice_Number CHAR(5) NOT NULL,
DATE_of_Payment DATE,
VIN_Number VARCHAR(17),
Customer_ID VARCHAR(15),
CONSTRAINT INVOICE_PK PRIMARY KEY (Invoice_Number),
CONSTRAINT INVOICE_FK1 FOREIGN KEY (VIN_number) REFERENCES Vehicle_t(VIN_Number),
CONSTRAINT INVOICE_FK2 FOREIGN KEY (Customer_ID) REFERENCES Customer_t(Customer_ID));

INSERT INTO CUSTOMER_t (Customer_Id, First_Name, Last_Name, Address_Line1, City, State, Zip_Code, Phone_Number, Insurance_Company, Policy_Number)
VALUES (1, 'Tony', 'Mao', '1355 S Hines Blvd', 'Gainesville', 'FL', '32601', '9199928891');
INSERT INTO CUSTOMER_t (Customer_Id, First_Name, Last_Name, Address_Line1, City, State, Zip_Code, Phone_Number, Insurance_Company, Policy_Number)
VALUES (2, 'Jones', 'Clegg', '15145 S.W. 17th St.', 'Plano', 'TX', '75094', '9723487765');
INSERT INTO CUSTOMER_t (Customer_Id, First_Name, Last_Name, Address_Line1, City, State, Zip_Code, Phone_Number, Insurance_Company, Policy_Number)
VALUES (3, 'John', 'Homer', '1900 Allard Ave.', 'Albany', 'NY', '12209', '9701235678');


INSERT INTO VEHICLE_t (VIN_Number, Year, Make, Model, Trim_Package, Color)
VALUES (1234567, '2016', 'Toyota', 'Prius', 'Hybrid', 'Silver');
INSERT INTO VEHICLE_t (VIN_Number, Year, Make, Model, Trim_Package, Color)
VALUES (9876542, '2011', 'Honda', 'Pilot', 'Hybrid', 'Black');
INSERT INTO VEHICLE_t (VIN_Number, Year, Make, Model, Trim_Package, Color)
VALUES (5684394, '2010', 'Nissan', 'Maxima', 'Non-hybrid', 'Blue');

INSERT INTO Parts_t (Part_Number, Part_Name, Part_Deskription, Wholesale_Price, Retail_Price)
VALUES (1001, 'Pads', 'Brake_Pads', '59.99', '79.99');
INSERT INTO Parts_t (Part_Number, Part_Name, Part_Deskription, Wholesale_Price, Retail_Price)
VALUES (1002, 'Filter', 'Oil_Filter', '29.99', '39.99');
INSERT INTO Parts_t (Part_Number, Part_Name, Part_Deskription, Wholesale_Price, Retail_Price)
VALUES (1003, 'Belt', 'Timing_Belt', '79.99', '99.99');

INSERT INTO TECHNICIAN_t (Tech_ID, Last_Name, First_name)
VALUES (1, 'Henny', 'Doug');
INSERT INTO TECHNICIAN_t (Tech_ID, Last_Name, First_name)
VALUES (2, 'Lewis', 'Robert');
INSERT INTO TECHNICIAN_t (Tech_ID, Last_Name, First_name)
VALUES (3, 'Strong', 'William');

INSERT INTO Invoice_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES ();
INSERT INTO Invoice_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES ();
INSERT INTO Invoice_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES ();


The Invoice table has FK VIN_Number and Customer ID
How do I go about inserting or pulling record from Vehicle table?
 
Posted on 06-14-17 7:17 PM     [Snapshot: 35]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 
 
Posted on 06-14-17 7:49 PM     [Snapshot: 58]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

its long form of SQL case statement...just for an example ( short)

SELECT Employee_Name, CASE Location
WHEN 'nepal ' THEN Bonus * 2
WHEN 'everest' THEN Bonus *, 5
ELSE Bonus
END
"New Bonus"

Syntax for STUFF function in an SQL server

STUFF (String1, Position, Length, String2)
String1 - String to be overwritten
Position - Starting location for overwriting
Length - Length of substitute string
String2- String to overwrite.

pull your data in following syntax ( ur data is too long ) n try to solve this in following syntax

UPDATE STATISTICS table_or_indexed_view_name
[
{
{ index_or_statistics__name }
| ( { index_or_statistics_name } [ ,...n ] )
}
]
[ WITH
[
FULLSCAN
| SAMPLE number { PERCENT | ROWS }
| RESAMPLE
[ ON PARTITIONS ( { | } [, …n] ) ]
| [ ,...n ]
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
[ [ , ] INCREMENTAL = { ON | OFF } ]
] ;

similar weather example to your question is given below try to solve this way

#include
#include
EXEC SQL BEGIN DECLARE SECTION;

long station_id;
long mon;
float temp;
float rain;
char city_name[21];
long SQLCODE;
EXEC SQL END DECLARE SECTION;
main()
{
/* the CONNECT statement, if needed, goes here */
strcpy(city_name,"Denver");
EXEC SQL SELECT ID INTO :station_id
FROM STATION
WHERE CITY = :city_name;
if (SQLCODE == 100)
{
printf("There is no station for city %s\n",city_name);
exit(0);
}
printf("For the city %s, Station ID is %ld\n",city_name,station_id);
printf("And here is the weather data:\n");
EXEC SQL DECLARE XYZ CURSOR FOR
SELECT MONTH, TEMP_F, RAIN_I
FROM STATS
WHERE ID = :station_id
ORDER BY MONTH;
EXEC SQL OPEN XYZ;
while (SQLCODE != 100) {
EXEC SQL FETCH XYZ INTO :mon, :temp, :rain;
if (SQLCODE == 100)
printf("end of list\n");
else
printf("month = %ld, temperature = %f, rainfall = %f\n",mon,temp,rain);
}
EXEC SQL CLOSE XYZ;
exit(0);


 
Posted on 06-14-17 7:52 PM     [Snapshot: 72]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

i don't get your examples
 
Posted on 06-14-17 7:57 PM     [Snapshot: 78]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

INSERT INTO SERVICE_TICKET (Ticket_ID, VIN_Number, Customer_ID, Date_Received, Notes)
VALUES (00987, ‘1234567’, ‘1’, ’03/20/2016’, ‘ ’);
INSERT INTO SERVICE_TICKET (Ticket_ID, VIN_Number, Customer_ID, Date_Received, Notes)
VALUES (00986, ‘9876542’, ‘2’, ’02/21/2015’, ‘ ’);
INSERT INTO SERVICE_TICKET (Ticket_ID, VIN_Number, Customer_ID, Date_Received, Notes)
VALUES (00985, ’5684394’, ‘3’, ’05/17/2016’, ‘ ’);



INSERT INTO INVOICE_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES (3456, ’03/20/2016’, ‘1234567’, ‘1’);
INSERT INTO INVOICE_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES (4567, ’02/21/2015’, ‘9876542’, ‘2’);
INSERT INTO INVOICE_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES (5678, ’05/17/206’, ’5684394’, ‘3’);



Tried this but all 6 insert error out
 
Posted on 06-14-17 9:14 PM     [Snapshot: 131]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Not quite sure what your question is but you can use INSERT with select subquery to pull the data from other tables to insert into INVOICE_t table.

see the link for more help.

https://www.techonthenet.com/oracle/insert.php
 
Posted on 06-14-17 10:14 PM     [Snapshot: 170]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Not sure I get your question but I suggest you try using to_date function as shown below.
What error are you getting?

INSERT INTO INVOICE_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES (3456, to_date ('03/20/2016’,'mm/dd/yyyy'), ‘1234567’, ‘1’);
 
Posted on 06-15-17 10:35 AM     [Snapshot: 328]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Hi newbie69,

Write the column name DATE_of_Payment instead of Date
Yours:-
INSERT INTO INVOICE_t (Invoice_Number, Date, VIN_Number, Customer_ID)
VALUES (3456, ’03/20/2016’, ‘1234567’, ‘1’);
Mine:-
INSERT INTO INVOICE_t (Invoice_Number, DATE_of_Payment, VIN_Number, Customer_ID)
VALUES (3456, '03/20/2016', '1234567', '1');
 
Posted on 06-15-17 12:23 PM     [Snapshot: 382]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

INSERT
INTO invoice_t
(
Invoice_Number, DATE_OF_PAYMENT,VIN_Number,Customer_ID
)
VALUES
(
'1234',to_date('03/20/2017','mm/dd/yyyy'),'1234567', '1'
);
1 row inserted.

-------------------------------------------------
INSERT
INTO invoice_t
(
Invoice_Number,DATE_OF_PAYMENT,VIN_Number,Customer_ID
)
VALUES
(
'1234','03/20/2017','1234567', '1'
);

SQL Error: ORA-01843: not a valid month
--------------------------------------------------------------------------------------------------
Remember to use different values for invoice_number or you might bump into unique constraint error since there is pk set on invoice_number.
--------
I doubt your constraints are set up correctly as all of your tables contain trailing _t but your constraint definition ignore 'em.
I would use something like this:
CONSTRAINT "INVOICE_T_PK" PRIMARY KEY ("INVOICE_NUMBER")
-----------
you do not need to write column names when writing insert statements, it is just for the purpose of ease, especially when there are large number of columns in a table.
INSERT
INTO invoice_t
VALUES
(
'1234',to_date('03/20/2017','mm/dd/yyyy'),'1234567', '1'
); will work just fine.

 
Posted on 06-15-17 9:34 PM     [Snapshot: 502]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

bro use a website called stackoverflow.com
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 7 days
Recommended Popular Threads Controvertial Threads
मन भित्र को पत्रै पत्र!
TPS Work Permit/How long your took?
Another Song Playing In My Mind
Does the 180 day auto extension apply for TPS?
Travelling to Nepal - TPS AP- PASSPORT
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters