I had to write a valid TSQL query that does all the following in a transaction and will rollback if any system error is thrown.
• Create the following tables Customer, Order and Address where Customer can have many Orders and many Addresses. Order can only have one Address. Customer will have a column called CustomerNum of type NVARCHAR(3).
• Develop a SQL function that provides a unique, incremented CustomerNum in the form “001”, “002”, “010” etc. Using that function, insert 10 new Customer rows. Note: the function needs to be as efficient as possible. Add any indexes that might be needed.
• Insert 1 Address per Customer.
• Insert 2 Orders per Customer.
• Ensure each Order has a valid Address from the corresponding Customer.
• Ensure that if the tables already exist that they are dropped from the database.
• Ensure all tables use primary keys and are of type GUID.
Any quick response is really appreciated.
• Create the following tables Customer, Order and Address where Customer can have many Orders and many Addresses. Order can only have one Address. Customer will have a column called CustomerNum of type NVARCHAR(3).
• Develop a SQL function that provides a unique, incremented CustomerNum in the form “001”, “002”, “010” etc. Using that function, insert 10 new Customer rows. Note: the function needs to be as efficient as possible. Add any indexes that might be needed.
• Insert 1 Address per Customer.
• Insert 2 Orders per Customer.
• Ensure each Order has a valid Address from the corresponding Customer.
• Ensure that if the tables already exist that they are dropped from the database.
• Ensure all tables use primary keys and are of type GUID.
Any quick response is really appreciated.
Comment