Creating a sql file, PL-SQL Programming

Creating a SQL file

1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL comment to the top of the script file with your name. You will be adding SQL commands to this script file for the remainder of this lab.

2. Next, add to the script file, the command (use statement) to select the classicmodels database. Save the file (but leave it open). Your script now has one comment and one SQL command in it. We will continue to add SQL commands to this file in the next part of the lab.

3. Next, add to the script file, the SQL query that will return the order number, quantity ordered, price each and total cost of an order (quantity * price each) from the order details table. Label the calculated column as "Total Cost" and sort by the total cost.

If you do NOT see any data in your order details table, it means that you did not successfully complete the insert of data into that table as part of lab 4.  Please go back to the course content area in D2L and re-download the file MyLastName_Lab_Module4.txt.  When you open this file, you will see the insert command and all the data for the order details table.  Please review the instructions from lab 4 on how to run this insert statement and populate the order details table before you proceed with this lab. 

1. Now, we want to run the script and test it to see if our select statement is working. Save the lab8script.sql file (but you can leave it open in NotePad++).

2. Open a command prompt on your computer and get into the MySQL monitor. Execute the script file and check your output. If the output is correct, move on to the next step. If the output is wrong: edit the SQL statement in the script file, save the script file, re-run the script from the mysql prompt and check the output.

3. Due to an increase in overhead costs, the buying price of all items needs to be increased. Management wants to see a report before deciding how much each product will go up. Add to the script file, the SQL query that will run against the products table and return the product code, quantity in stock, current buying price and columns to show 3%, 5% and 10% over current buying price. Be sure to label the column headings for the calculated buying price as "3 Percent", "5 Percent" and "10 Percent."You will have a total of 6 column headings.

4. Next, add to the script file, the SQL query that will return the employee number and a concatenation of the first and last names of employees from the employee table. The names should be concatenated and display lastname first as "lastname, firstname"

a. Be sure all these queries are in your lab8script.sql file and that they run successfully and produce the correct results.

Then -

  1. Browse to your notepad++ window with Lab8Script.sql open
  2. Add to the script file, the SQL query that will return the number of offices in each country (Using group by and count)
  3. Be sure all these queries are in your lab8script.sql file and that they run successfully and produce the correct results

Go to

1. Browse to your notepad++ window with Lab8Script.sql open

2. Next, add to the script file, the SQL query that will return the average quantity of products ordered from the order details table

3. Next, add to the script file, the SQL query that will return the minimum quantity of products ordered from the order details table

4. Next, add to the script file, the SQL query that will return the maximum quantity of products ordered from the order details table

5. Be sure all these queries are in your lab8script.sql file and that they run successfully and produce the correct results.

Finally

1. Browse to your notepad++ window with Lab8Script.sql open

2. Next, add to the script file, the SQL query that will retrieve the number of customers in each city in which there are at least 2 customers. (Hint: use the count() function)

Posted Date: 2/18/2013 12:04:46 AM | Location : United States







Related Discussions:- Creating a sql file, Assignment Help, Ask Question on Creating a sql file, Get Answer, Expert's Help, Creating a sql file Discussions

Write discussion on Creating a sql file
Your posts are moderated
Related Questions
RETURN Statement The RETURN statement instantly completes the execution of a subprogram and returns control to the caller. The Execution then resumes with the statement below t

Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

Structure of an Object Type: Similar to package, an object type has 2 parts: the specification and the body. The specification is the interface to your applications; it declar

TTITLE and BTITLE are commands in Pl-SQL to control report headings and footers. This Ttitle & Btitle are mainly used on creating SQL*PLUS report. Ttitle is used for toptitle headi

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

Advantages of Invoker Rights The Invoker-rights routines centralize the data retrieval. They are particularly helpful in applications which store data in various schemas. In su

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables without the use of a Cartesian product Query: SELECT E.LAST_NAME, E.FIRST_NAME, S.BUILDING, S.BRAN

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

Parameter and Keyword Description:   table_reference: This identifies a table or view which should be available when you execute the INSERT statement, and for that you sho