sql question

Archived from the original Sajha.com — preserved as posted, replies can no longer be added here.

Archived Post

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?

newbie69 · Jun 14, 2017 6:32 PM · 24,942 views

9 Replies

plz help

newbie69 · Jun 14, 2017 7:17 PM

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);

Ma Nepali · Jun 14, 2017 7:49 PM

i don't get your examples

newbie69 · Jun 14, 2017 7:52 PM

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

newbie69 · Jun 14, 2017 7:57 PM

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/o...

latinokiller · Jun 14, 2017 9:14 PM

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’);

gandharba · Jun 14, 2017 10:14 PM

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');

SumOne1 · Jun 15, 2017 10:35 AM

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.

gandharba · Jun 15, 2017 12:23 PM

bro use a website called stackoverflow.com

raijayant · Jun 15, 2017 9:34 PM

This conversation is preserved exactly as it was on the original Sajha.com and can't accept new replies.

Start a New Discussion

sql question

You might be interested in...