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