Question

In: Computer Science

Use adventure works 2014 database to solve the following questions in SQL management studio. 1. Create...

Use adventure works 2014 database to solve the following questions in SQL management studio.

1. Create a stored procedure that will receive parameters (all non-nullable fields should be supplied except the primary key which should be auto created still) to add a customer, single email, and single phone. The procedure should run it's code in a transaction and if any part of the add fails then the transaction should rollback.

2. Provide this query from previous assignments written with an isolation level that won't see your procedure transaction above until it's complete. Using joins List the orders customer name, order status, date ordered, count of items on the order, and average quantity ordered where the count of items on the order is greater than 300.

the data tables for second question are: sales.customer, sales.salesOrderHeader, Person.Person, sales.salesOrderDetail

Solutions

Expert Solution

***********************************************************************************************************************************

In case of any doubt do ask in the comment section


Related Solutions

Database questions: USE THE FOLLOWING SQL CODE TO SOLVE NEXT QUESTIONS: CREATE TABLE ROBOT ( Serial_no...
Database questions: USE THE FOLLOWING SQL CODE TO SOLVE NEXT QUESTIONS: CREATE TABLE ROBOT ( Serial_no INT NOT NULL, Model VARCHAR(20) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, Price INT NOT NULL, PRIMARY KEY (Serial_no) ); INSERT INTO ROBOT VALUES (1, 'Scara','Epson', 23200); INSERT INTO ROBOT VALUES (2, 'ASSISTA','Mitsubishi', 17500); INSERT INTO ROBOT VALUES (3, 'Lego Mindstorm','NXT', 650); INSERT INTO ROBOT VALUES (4, 'Yumi','ABB', 40000); INSERT INTO ROBOT VALUES (5, 'Pepper','Foxconn', 1600); INSERT INTO ROBOT VALUES (6, 'Humanoid','Honda', 30000); SELECT *...
This is t a relational database please write SQL queries to solve the listed questions. The...
This is t a relational database please write SQL queries to solve the listed questions. The database is a variation of the “Movie Database” . There are several differences in it, so look it over carefully before writing your SQL queries Notes: TheaterNum, MovieNum, and ActorNum are numeric primary key fields in their respective tables. Movie and actor names are not assumed to be unique unless specified otherwise in a question. In the THEATER table, Capacity is the number of...
Use the following information to create SQL commands to retrieve data from Henry Books database :...
Use the following information to create SQL commands to retrieve data from Henry Books database : For each book, list the book code, book title, publisher code, and publisher name. Order the results by publisher name. For each book published by Plume, list the book code, book title, and price. List the book title, book code, and price of each book published by Plume that has a book price of at least $14. List the book code, book title, and...
Please write the SQL statement for the following. I am using the Adventurework2014 database. 1. Create...
Please write the SQL statement for the following. I am using the Adventurework2014 database. 1. Create a login for AdventureWorks employees. An employee login should be composed of the first letter of a person's first name combined with their last name. In addition, the login should be all lower case characters. All the required information is located in Person.Person table. Employees can be identified by "EM" value in the PersonType field. The output should include BusinessEntityID, first name (FirstName), last...
Create the following SQL queries using the lyrics database below 1. List the first name, last...
Create the following SQL queries using the lyrics database below 1. List the first name, last name, and region of members who do not have an email. 2. List the first name, last name, and region of members who do not have an email and they either have a homephone ending with a 2 or a 3. 3. List the number of track titles that begin with the letter 's' and the average length of these tracks in seconds 4....
Please create the SQL queries using the lryics database under question 4 and use "select *...
Please create the SQL queries using the lryics database under question 4 and use "select * from..." after each query to show the effects of your data manipulation query. Thanks for help 1. The title 'Time Flies' now has a new track, the 11th track 'Spring', which is 150 seconds long and has only a MP3 file. Insert the new track into Tracks table (Don’t hand-code any data for insert that can be looked up from the Titles table). 2....
Using your downloaded DBMS (MS SQL Server), create a new database. Create the database tables based...
Using your downloaded DBMS (MS SQL Server), create a new database. Create the database tables based on your entities defining The attributes within each table The primary and foreign keys within each table *****Show your database tables, tables attributes, primary and foreign keys***** Do not forget to check the lesson slides and videos that show you how to convert an ER/EER into a database schema, and how to create a database and tables using MS SQL Server.
SQL This assignment aligns with the following objectives: Create a relational database containing at least three...
SQL This assignment aligns with the following objectives: Create a relational database containing at least three tables Populate the database using the INSERT command Create integrity constraints on the tables created Deli has hired you as a consultant to design a database for the deli. They have provided you with the following information: Every employee has a social security number, name, salary, and date of hire. The deli is organized into several departments. Each department has a unique name. Each...
Create the actual database using SQL syntax. This is completed using a Database Application (i.e Microsoft...
Create the actual database using SQL syntax. This is completed using a Database Application (i.e Microsoft Access, Oracle, or MySQL) as indicated by your professor. After creating the database – populate it with some data (could be made up). SQL syntax and the DB application will be discussed and taught in class. This is the final deliverable of the group project. Assignment is due by the due date as indicated by your professor. *Make sure to submit the completed database...
Database - SQL - Operations CS 203 Discrete Structure 2 Create a Microsoft Access Database consisting...
Database - SQL - Operations CS 203 Discrete Structure 2 Create a Microsoft Access Database consisting of the following two tables: Part_needs and Parts_Inventory Part_needs Supplier Part_number Project 23 1092 1 23 1101 3 23 9048 4 31 4975 3 31 3477 2 32 6984 4 32 9191 2 33 1001 1 Parts_Inventory Part_number Project Quantity Color_code 1001 1 14 8 1092 1 2 2 1101 3 1 1 3477 2 25 2 4975 3 6 2 6984 4 10...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT