Tag: Murach’s SQL for SQL Server

Murach’s SQL for SQL Server – Exercise 18 – Guaranteed 100% score Murach’s SQL for SQL Server – Exercise 18 P.S: Kindly drop me a message if you want to confirm the problem statement for this week.

Murach’s SQL for SQL Server – Exercise 12 – Guaranteed 100% Score 1. Write a CREATE VIEW statement that defines a view named InvoiceBasic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P. 2. Create a view named Top10PaidInvoices that returns three columns for each vendor: VendorName, LastInvoice (the most recent invoice date), and SumOfInvoices (the sum of the InvoiceTotal column). Return only the 10 vendors with the largest SumOfInvoices and

Murach’s SQL for SQL Server – Exercise 9 – Guaranteed 100% Score 1. Design a database diagram for a product orders database with four tables. Indicate the relationships between tables and identify the primary key and foreign keys in each table. Explain your design decisions. OrderID CustomerID OrderDate ShipAddress ShipDate . . . CustomerID CustomerName CustomerAddress CustomerPhone . . . Customers Orders OrderID OrderSequence ProductID Quantity UnitPrice OrderLineItems ProductID ProductName QtyPerUnit UnitPrice InStock OnOrder . . . Products 2. Add the two tables below into the design for exercise 1. Create additional tables and columns, if necessary. Explain your design

Murach’s SQL for SQL Server – Exercise 6 – Guaranteed 100% Score 1. Write a SELECT statement that returns the same result set as this SELECT statement. Substitute a subquery in a WHERE clause for the inner join. SELECT DISTINCT VendorName FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID ORDER BY VendorName 2. Write a SELECT statement that answers this question: Which invoices have a PaymentTotal that’s greater than the average PaymentTotal for all paid invoices? Return the InvoiceNumber and InvoiceTotal for each invoice. 3. Write a SELECT statement that answers this question: Which invoices have a PaymentTotal that’s greater

Murach’s SQL for SQL Server – Exercise 14 – Guaranteed 100% score 1. Create a stored procedure in the AP database named spWhichTable that accepts a column name and returns the name of the table or tables that have a column by that name. Code a statement that calls the procedure. 2. Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance for each invoice with a balance due, sorted with largest balance due first. The parameter @VendorVar is a mask that’s used with a LIKE operator

Murach’s SQL for SQL Server – Exercise 11 – Guaranteed 100% score 1. Using the diagram and grid panes of the Query Designer, query the Vendors table for vendors in California. The results pane should look like this: 2. Using the diagram and grid panes of the Query Designer, create a query that joins the Vendors, Invoices, InvoiceLineItems, and GLAccounts tables. Filter the result set for rows with AccountNo 553, and sort the result set with the most recent InvoiceDate first. 3. Using the diagram and grid panes of the Query Designer, create a summary query that joins Vendors and