Please written by computer source 11 1. (Use AP) Write a SELECT statement that returns six columns from three tables, all using alias names: VendorName column named Vendor, InvoiceDate column named Date of Invoice (include the spaces), InvoiceNumber column named Number, InvoiceSequence column named #, InvoiceLineItemAmount column named Line Item Amount (include the spaces), and Tax (which is the line item amount multiplied by 6%). Also, assign the following table alias names to the tables: Vendors table named v, Invoices table named i, InvoiceLineItems table named ili. List only data for the Line Item Amounts greater than or equal to $50 but less than $1200 and Vendornames that begin with C-D or K-S. Sort the final result set by Vendor name ascending, and Line Item Amount descending,. 2. (Use AP) Create a query listing all of the vendors (show only vendorid, vendorname) from the state of Wisconsin (WI) or New Jersey (NJ) or Pennsylvania (PA) who have never been invoiced (they do not have an invoice associated with them). Sort the final result set by vendorname from Z-A. 3. (Use Sally’s Pet Store) Sally wants an inventory report that lists all of the inventory the store has ever carried (both animals and merchandise). Use the UNION operator to generate the result set consisting of five columns. The first column (called ID) should be the ID of the animal or item. The second column (called Description) should be the description of the item or a concatenation of category and breed (with a space dash space in between such as ‘Cat - Siamese’) for animals. The third column (called LPrice) should list the listprice of the animal or item. The fourth column (called Type) should be a text tag of “Animal” for animals and “Merch” for merchandise items. Sort the final result set by listprice descending. 4. (Use Sally’s Pet Store)Sally has a very fickle customer that would like to see if the store has ever carried certain types of animals. Write one query to answer this question: List the parrots or parakeets or lovebirds that have blue or red but not gold as part of their color, and also list the female registered cats with a price from 130 to 300 born in June, July or August of the year 2004, but not black cats. Return all columns in the animal table, and sort the result set by listprice with the largest value first. 5. Write the code to create 3 tables: SALESAGENT, AGENTAUTO, and AUTOMOBILE. The SaleAgent table should have a surrogate key named SID with automatic sequential numbering, and these fields (SFN variable text max of 30 and required, SLN with an appropriate data type, SPhone with an appropriate data type). AUTOMOBILE should have field named VIN which is a unique number that comes on every car and should be used as the primary key. The table AGENTAUTO table should have the fields startdate and enddate with appropriate data types, a primary key of your choosing, and the necessary two foreign keys with referential integrity constraints enforced.
Please written by computer source
1. (Use AP) Write a SELECT statement that returns six columns from three tables, all using alias names: VendorName column named Vendor, InvoiceDate column named Date of Invoice (include the spaces), InvoiceNumber column named Number, InvoiceSequence column named #, InvoiceLineItemAmount column named Line Item Amount (include the spaces), and Tax (which is the line item amount multiplied by 6%). Also, assign the following table alias names to the tables: Vendors table named v, Invoices table named i, InvoiceLineItems table named ili. List only data for the Line Item Amounts greater than or equal to $50 but less than $1200 and Vendornames that begin with C-D or K-S. Sort the final result set by Vendor name ascending, and Line Item Amount descending,.
2. (Use AP) Create a query listing all of the vendors (show only vendorid, vendorname) from the state of Wisconsin (WI) or New Jersey (NJ) or Pennsylvania (PA) who have never been invoiced (they do not have an invoice associated with them). Sort the final result set by vendorname from Z-A.
3. (Use Sally’s Pet Store) Sally wants an inventory report that lists all of the inventory the store has ever carried (both animals and merchandise). Use the UNION operator to generate the result set consisting of five columns. The first column (called ID) should be the ID of the animal or item. The second column (called Description) should be the description of the item or a concatenation of category and breed (with a space dash space in between such as ‘Cat - Siamese’) for animals. The third column (called LPrice) should list the listprice of the animal or item. The fourth column (called Type) should be a text tag of “Animal” for animals and “Merch” for merchandise items. Sort the final result set by listprice descending.
4. (Use Sally’s Pet Store)Sally has a very fickle customer that would like to see if the store has ever carried certain types of animals. Write one query to answer this question: List the parrots or parakeets or lovebirds that have blue or red but not gold as part of their color, and also list the female registered cats with a price from 130 to 300 born in June, July or August of the year 2004, but not black cats. Return all columns in the animal table, and sort the result set by listprice with the largest value first.
5. Write the code to create 3 tables: SALESAGENT, AGENTAUTO, and AUTOMOBILE. The SaleAgent table should have a surrogate key named SID with automatic sequential numbering, and these fields (SFN variable text max of 30 and required, SLN with an appropriate data type, SPhone with an appropriate data type). AUTOMOBILE should have field named VIN which is a unique number that comes on every car and should be used as the primary key. The table AGENTAUTO table should have the fields startdate and enddate with appropriate data types, a primary key of your choosing, and the necessary two foreign keys with referential integrity constraints enforced.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
