SQL server 1. Write SELECT INTO statements to create two test tables named VendorCopyand InvoiceCopy that are complete copies of the Vendors and Invoices tables.If VendorCopy and Invoice Copy already exist, first code two DROP TABLEstatements to delete them.2. Write an INSERT statement that adds a row to the InvoiceCopy table with thefollowing values:VendorID: 32Invoice Total: $434.58TermsID: 2InvoiceNumber: AX-014-027Payment Total: $0.00InvoiceDueDate: 05/8/2020InvoiceDate: 4/21/2020Credit Total: $0.00PaymentDate: null3. Write an INSERT statement that adds a row to the VendorCopy table foreach non-California vendor in the Vendors table. (This will result in duplicatevendors in the VendorCopy table.)4. Write an UPDATE statement that modifies the VendorCopy table. Changethe default account number to 403 for each vendor that has a default accountnumber of 400. 5. Write an UPDATE statement that modifies the InvoiceCopy table. Changethe PaymentDate to today's date and the Payment Total to the balance due foreach invoice with a balance due. Set today's date with a literal date string, oruse the GETDATE() function.6. Write an UPDATE statement that modifies the InvoiceCopy table. ChangeTermsID to 2 for each invoice that's from a vendor with a Default TermsID of2. Use a subquery.7.Solve exercise 6 using a join rather than a subquery.8. Write a DELETE statement that deletes all vendors in the state of Minnesotafrom the VendorCopy table.9. Write a DELETE statement for the VendorCopy table. Delete the vendors thatare located in states from which no vendor has ever sent an invoice.Hint: Use a subquery coded with "SELECT DISTINCT VendorState"introduced with the NOT IN operator.

“Since you have posted a question with multiple sub-parts, we will solve first three sub- parts for…

1. Write SELECT INTO statements to create two test tables named VendorCopy and Invoice Copy that are complete copies of the Vendors and Invoices tables. If VendorCopy and InvoiceCopy already exist, first code two DROP TABLE statements to delete them. 2. Write an INSERT statement that adds a row to the InvoiceCopy table with the following values: VendorID: 32 Invoice Total: $434.58 TermsID: 2 InvoiceNumber: AX-014-027 Payment Total: $0.00 InvoiceDueDate: 05/8/2020 InvoiceDate: 4/21/2020 Credit Total: $0.00 PaymentDate: null 3. Write an INSERT statement that adds a row to the VendorCopy table for each non-California vendor in the Vendors table. (This will result in duplicate vendors in the VendorCopy table.)

1. Write SELECT INTO statements to create two test tables named VendorCopy and Invoice Copy that are complete copies of the Vendors and Invoices tables. If VendorCopy and InvoiceCopy already exist, first code two DROP TABLE statements to delete them. 2. Write an INSERT statement that adds a row to the InvoiceCopy table with the following values: VendorID: 32 Invoice Total: $434.58 TermsID: 2 InvoiceNumber: AX-014-027 Payment Total: $0.00 InvoiceDueDate: 05/8/2020 InvoiceDate: 4/21/2020 Credit Total: $0.00 PaymentDate: null 3. Write an INSERT statement that adds a row to the VendorCopy table for each non-California vendor in the Vendors table. (This will result in duplicate vendors in the VendorCopy table.)

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

The Driver Relationship team realized that maintaining driver IDs is difficult and requested an automatic way of incrementing the value when a new driver is added. You need to make the changes on the table to automatically increment the Driver ID. After the change, you need to insert the following driver: First Name: Nursin Last Name: Yilmaz Driving License ID: 4141447 Start Date: 2019-12-28 Driving License Checked: True Rating: 4.0 THIS IS IN SQL FORMAT FOR CENGAGE MINDTAP, PLEASE HELP I AM STUCK
MS SQL SERVER Write a statement to create a stored procedure named Find_Contact based on the Contacts table in question 1 A, which does the following: Accepts an input parameter named @FindMobile that has the data type varchar. IF MobileNo exists: Print the message “Mobile No for:” concatenated with the input parameter. IF MobileNo do not exists: Print the message “Do Not Exist” concatenated with the input parameter. Executes a SELECT statement on the table Contacts, passing the input parameter as part of the WHERE clause. Return the contact details that match the input parameter value.
3. Write a SELECT statement that joins the Departments, Courses, and Instructors tables. This statement should return these columns: DepartmentName, CourseDescription, FirstName, and LastName. Use aliases for the tables and return only those courses with three units. Each course description should only be listed once. Sort the result set by DepartmentName and then by CourseDescription in ascending sequence.
with it, paste your code in a text file named Student.txt and save it in your task folder. Write the SQL code to create a table called Student. The table structure is summarised in the table below (Note that STU_NUM is the primary key): STU_NUM Attribute Name STU_SNAME STU_FNAME STU_INITIAL STU_STARTDATE COURSE_CODE PROJ_NUM STU STU_S STU_F STU_INITIAL NUM NAME NAME 01 02 Snow John E CHAR(6) After you have created the table in question 1, write the SQL code to enter the first two rows of the table as below: Stark Arya с VARCHAR(15) VARCHAR(15) CHAR(1) DATE CHAR(3) INT(2) Data Type STU_STARTDATE COURSE_ PROJ_ CODE NUM 05-Apr-14 12-Jul-17 201 305 6 11 Assuming all the data in the Student table has been entered as shown below, write the SQL code that will list all attributes for a COURSE_CODE of 305.
9. Write a SELECT statement that uses aggregate window functions to calculate the total due for all vendors and the total due for each vendor. Return 3 Columns: The vendor id from the Invoices table. The balance due (invoice_total-payment_total-credit_total) for each invoice in the invoices table with a balance greater than 0. The total balance due for all vendors in the Invoices table. Modify the column that contains the balance due for each vendor so it contains a cumulative total by balance due.
USE NORTHWIND DATABASE Write a SELECT statement using the CASE function. The statement returns two columns basedon how many orders the customer has in the database. The first column is the customer name andthe second column, Membership Type, is based on which case applies to the customer:If the customer has more than 25 orders they are a Diamond Customer.If the customer has more than 15 orders they are a Gold Customer.If the customer has more than 10 orders they are a Silver Customer.If the customer has more than 5 orders they are an Iron Customer.Otherwise the customer is a Valued Customer.
THIS MODULE IS ABOUT SUBQUERIES, SO YOU MUST USE SUBQUERIES INSTEAD OF TABLE JOINS (a) From which two fields in which table can you calculate the enrollment of a section?(b) From which table can you get both course and section numbers?(c) Which field in table SECTION uniquely identifies a section, section id or section no?(d) Write a SQL statement to show sections and their enrollment.(e) Write a SQL statement to show sections with enrollment greater than five. Display both course andsection numbers
31-Create a SQL statement to Display the number of clients for each CType with having less than 2 clients in the CLIENTS table.
You have been instructed to add a new student information to the students table. Because the student is new, he has no marks before, you should not add an amount to the CGPA. The Students table contains these columns: Student_id NUMBER(10) Student_name VARCHAR2(30) Student phone NUMBER(10) CGPA NUMBER(2,2) Which INSERT statements will accomplish your objective? O a. INSERT INTO students VALUES (365, 'Khalil',99554721, 0 ) O b. INSERT INTO students VALUES (365, 'Khalil',99554721, 'NO CGPA' ) O c. INSERT INTO students VALUES (365, 'Khalil',99554721, NULL ) O d. INSERT INTO students VALUES (365, 'Khalil',99554721) Which line of code has an error? 1. CREATE TABLE FRUITS VEGETABLES 2. FRUIT TYPE VARCHAR2, 3. FRUIT NAME CHAR (20), 4. QUANTITY NUMBER (4)); O a. 1 O b. 2 O c. 3
Write a SELECT statement that returns the following columns from the PRODUCT_INFORMATION table. Sort the results by PRODUCT_ID in ascending order. 1. PRODUCT_ID 2. PRODUCT_NAME 3. LIST_PRICE ○ Use the TO_CHAR function to convert LIST_PRICE to a character string with the currency formatting Give this field an alias of PRICE
Execute the following statements using MYSQL: 1.Create a Trigger "trigger regno" for the salesman table (Before INSERT) set the value as O for the experience > 7 and evaluate it. Also write the syntax to drop the Trigger "trigger regno* 2. Insert a value for Customer table (#Last 3 digit of your register no, #Your name, Vellore, 55, 010). Insert a value for salesman table ("10", #Your name, 5, 4000). 3. List out the number of customers in each city by arranging them from low to high. ( 4. Select all the salesman who's work experience is between 4 and 8.
Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns. In addition, you must calculate the order total from the columns in the OrderItems table. Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer�s email address and the largest order for that customer. To do this, you can group the result set by the EmailAddress column.