Purchase Solution

Subquery and Join

Not what you're looking for?

Ask Custom Question

1. Stored procedures in SQL 2005 can be written in a variety of languages. Why is this important in the development of database systems? Provide an example of a stored procedure that a database designer might write to demonstrate why this capability is important. Please include references.

2. Which is better to use, a sub query or a join? Why? Please include example of each to support your reasoning. Also, please include references.

Purchase this Solution
Solution Preview

1. Stored procedures in SQL 2005 can be written in a variety of languages. Why is this important in the development of database systems? Provide an example of a stored procedure that a database designer might write to demonstrate why this capability is important. Please include references.

Benefits of Stored Procedures [1]:
* Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
* Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
* Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
* Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Example
======
Assume we have the table shown below, named ...

Purchase this Solution
Free BrainMass Quizzes
Basic Networking Questions

This quiz consists of some basic networking questions.

Javscript Basics

Quiz on basics of javascript programming language.

Word 2010: Tables

Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.

Inserting and deleting in a linked list

This quiz tests your understanding of how to insert and delete elements in a linked list. Understanding of the use of linked lists, and the related performance aspects, is an important fundamental skill of computer science data structures.

Basic Computer Terms

We use many basic terms like bit, pixel in our usual conversations about computers. Are we aware of what these mean? This little quiz is an attempt towards discovering that.

View More Free Quizzes
Related BrainMass Solutions

  • Structured Query Language (SQL) Preliminaries

    Discuss what SQL is and its importance. 2. Name and describe the SQL statements used for data modification. 3. Compare the join and the SQL subquery. 4. Name and briefly describe the five SQL built-in functions. 5.

  • SQL Management Studio Database

    .* from summary as s inner join employee as e on e.employee_no=s.employee_no where s.job is null and s.promotion_no = 'P2' 5. Using a subquery, get the first and last names of the employees who work in the Orange Appeal store.

  • Explicit SQL Statement

    Hint: A JOIN should not be used; A subquery should be used instead. /*1- Provide an explicit JOIN SQL statement that returns the store ID, store name, manager employee first and last name fields, manager employee grade, the city and state fields of

  • Subquery vs. Join.

    501804 Subquery vs. Join. Please help me with this question: Which is better to use, a subquery or a JOIN? Why? Show an example of each to support your reasoning.

  • Database, subqueries, and joins

    The subquery would note finding the previous customers of this product and when they purchased it and if any comments about improvements were given. •What example could you offer that would demonstrate using a join?

  • Comparison of two select queries

    Here's a query: SELECT orders.order_date, orders.customer_numb FROM orders INNER JOIN order_lines ON orders.order_numb = order_lines.order_numb WHERE order_lines.isbn = '0-131-4966-9'; and here it is rewritten using a subquery

  • SQL Commands

    Use a subquery. 6. Write a SQL statement to show the Warehouse and average QuantityOnHand of all items stored in a warehouse managed by 'Smith'. Use subquery. 7.

  • Subqueries and correlated queries

    For a multiple-row subquery, the common operators used are IN, Notin, All or AnY. Correlated subquery has a different method of execution than a single/multiple-row subquery and more useful to deal with complex scenarios.

  • Database Re-Engineering and Redesign

    83380 Database Re-Engineering and Redesign 1. Explain why database re-engineering and redesign is necessary. 2. What is the difference between a correlated subquery and a regular subquery? 3.

  • SQL Server Database

    Query 8 (SELECT SUBQUERY): Write a SELECT query that utilizes a SUBQUERY to determine if the EEO-Classification for an employee is invalid. This means that you need to use the NOT EXIST for the SUBQUERY.

View More Related Solutions