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 nowThis is a popular solution!
Step by stepSolved in 2 steps
- The Department summary table should look up the department abbreviation and return all of the data listed. It needs to be filled out. To begin, next to the cell listed "Department", use a function to search the staff table data (not including the row of headings and the total row) and return the department name based on the abbreviation above. The formula will be copied, so make sure it always references the abbreviation. Copy the formula down to complete the Department summary table, and edit the copied formulas to return the values from the headings listed in the left-hand column. In the appropriate cell below the staff table, use a function to display the current date without displaying the time. The averages will not be needed, so delete column O. The date for the next update isn't certain, so hide row 18. Switch to the Budget worksheet, which contains the department budgets from 2021 to 2025. There's already a pie chart showing 2021 budget information, and Carmelo wants another…arrow_forwardTask 3: The Car Maintenance team wants to insert and store the following maintenance types to the MAINTENANCE_TYPES table: ID: 1; Description: Tire Change; Price: 50 ID: 2; Description; Oil Change; Price: 45 ID: 3; Description; Full Cleaning; Price: 100 ID: 4; Description; Gas Pump Change; Price: 145 Add the four maintenance types to the MAINTENANCE_TYPES table.arrow_forwardWrite a SELECT statement that uses the ranking functions to rank products by the total quantity sold. Return these columns: The product_name column from the Products table A column named total_quantity that shows the sum of the quantity for each product in the Order_Items table A column named rank that uses the RANK function to rank the total quantity in descending sequence A column named dense_rank that uses the DENSE_RANK function to rank the total quantity in descending sequencearrow_forward
- Jump to level Complete the following statement to create a table named Country. Choose data types based on the following requirements: ISOCode3 stores the country's code, consisting of one to three letters. • IndepDate stores the country's independence date. CREATE TABLE Country ); ( ISOCode 3 /* Your code goes here */ IndepDate /* Your code goes here */ Enter a statement to delete the above table. /* Your code goes here */ ;arrow_forwardWhat is the most efficient way to access data when you have multiple tables? a) Use the VLOOKUP function to create formulas to add data from other tables b) Use an application like Microsoft Query to consolidate the data into one table c) Create relationships between tables d) Use Power Query to consolidate the data into one tablearrow_forward8. Use the Form Wizard to create a new form for inputting sales data. Include all the fields from the PopcornSales table. a. b. Include the Item and Quantity fields from the SaleDetails table. c. View the form data by records in the PopcornSales table with related records in the SaleDetails table displayed in a subform. d. The subform should be displayed as a Datasheet. e. Name the main form: PopcornSales Form and name the subform: SaleDetailsSubform (Hint: Be sure to remove the space between ure.com/courses/79665/assignments/1378308?module_item_id=3226669 f. g. Access 365/2021 Capstone - SIMnet SaleDetails and Subform in the subform name suggested by Access.) Open the form in Form view to review your work. Navigate to the record in the main form for SaleID 3 and enter sale details in the subform as follows: Item: Truffle, Quantity: 3 Item: Old Bay, Quantity: 2 h. Close the form.arrow_forward
- The Horse table has the following columns: ID - integer, primary key RegisteredName - variable-length string Breed - variable-length string Height - decimal number BirthDate - date Write a SELECT statement to select the registered name and height for only horses that have an above average height. Order the results by height (ascending).arrow_forwardThe Horse table has the following columns: ID - integer, primary key RegisteredName - variable-length string Breed - variable-length string Height - decimal number BirthDate - date Write a SELECT statement to select the registered name and height for only horses that have an above average height. Order the results by height (ascending). Hint: Use a subquery to find the average height.arrow_forwardWhich of the following statements is valid? (refer to the tables in the JustLee Books database.)a. SELECT title FROM booksWHERE retail<(SELECT cost FROM booksWHERE isbn = '9959789321');b. SELECT title FROM booksWHERE retail = (SELECT cost FROM booksWHERE isbn = '9959789321' ORDER BY cost);c. SELECT title FROM booksWHERE category IN (SELECT cost FROM orderitemsWHERE isbn = '9959789321');d. none of the above statementsarrow_forward
- wrire a select statement to select year and the total numbers of the movie that year by using count() function and Group by clause.arrow_forwardModify the SELECT statement to select the title and release date of PG-13 movies that are released after February 1, 2008. Run your solution and verify the result table shows just the titles and release dates for The Dark Knight and Crazy Rich Asians. CREATE TABLE Movie ( ID INT AUTO_INCREMENT, Title VARCHAR(100), Rating CHAR(5) CHECK (Rating IN ('G', 'PG', 'PG-13', 'R')), ReleaseDate DATE, PRIMARY KEY (ID)); INSERT INTO Movie (Title, Rating, ReleaseDate) VALUES ('Casablanca', 'PG', '1943-01-23'), ('Bridget Jones\'s Diary', 'PG-13', '2001-04-13'), ('The Dark Knight', 'PG-13', '2008-07-18'), ('Hidden Figures', 'PG', '2017-01-06'), ('Toy Story', 'G', '1995-11-22'), ('Rocky', 'PG', '1976-11-21'), ('Crazy Rich Asians', 'PG-13', '2018-08-15'); -- Modify the SELECT statement:SELECT *FROM MovieWHERE ReleaseDate < '2000-01-01';arrow_forwardTask 12: When deleting a customer, subtract the balance multiplied by the sales rep’s commission rate from the commission for the corresponding sales rep.arrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education