Database

MIS582 Course Project Week 2 SQL Queries Using MySQL

The Course Project will consist of three tasks, each worth 50 points due in Weeks 2, 4, and 6. Successful completion of the iLabs will help you in completing the project tasks.

Week 2: Task1: SQL Queries Using MySQL (50 Points)
Using the same tables that you created by running the script file provided in Doc Sharing for iLab1, write
the following 10 queries each worth 5 points.

Name Task
Query1: Write a SQL statement to display the course numbers of classes taught by Hammett Glenn.
Query2: Write a SQL statement to display the course names of the courses taught by an instructor whose id is 415.
Query3: Write a query to display the name and office number of all instructors who teach Psy101.(USE NATURAL JOIN)
Query4: Write a query to display the semester and the number of courses offered in that semester. Display only the semesters that offer more than five courses.
Query5: Write a SQL statement to display the name of each campus and the total number of rooms in that campus.
Query6: Write a SQL statement to display the list of instructors who are approved as well as those who are not approved. (OUTER JOIN)
Query7: Write a SQL statement to display the start date and start time of all classes taught by instructor Hammett Glenn.
Query8: Write a SQL statement to display the number of students in each major with a status of failing.
Query9: Write a SQL statement to display the credit hours of all courses that Hammett Glenn is approved to teach.
Query10: Get the campus name and phone number of all rooms with numbers greater than 25.

Deliverables
Create and save your SQL queries and script in a text file named Lastname_Task1_SQL_Script.sql.
Capture the output and paste it into Word Document. Save it as Lastname_Task1_SQL_Result.txt.
When you are done, submit your documents zipped together in a single file to the Week 2: Course Project Week 2 Task 1 Dropbox.

Rubrics
Queries that are correct will be awarded the number of points shown below:
4 points: Query 1
5 points: Query 2–9
6 points: Query 10
The following rubrics will be used for incorrect queries.
0 points: Query was not turned in with the assignment.
-4 points: Query will not run.
-3 points: Query runs but is incorrect because query required a WHERE clause to meet requirements which was not included.
-2 points: Query runs but is incorrect because the WHERE clause contained errors, gives popup for user input, or only meets partial requirements.
Tutorials to Consult With M10C_KROE8352_13_SE_WC10C.pdf in the folder titled My SQL Documents in Doc Sharing for all things to do with MySQL including example SQL.
Login MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Doc Sharing for how to log into the Omnymbus Environment.
Creating a Database in MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Doc Sharing for how to create a Database in the Omnymbus Environment. Remember, all work must be done under a database you first create.
Running SQL Scripts in MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Doc Sharing for how to create and run your SQL Scripts.

MIS582 iLab 1 SQL Queries Using MySQL

iLab Overview
Scenario/Summary
The purpose of this iLab is to prepare for the first phase of the Course Project. This week, you will learn to create and run SQL SELECT queries from a script in the MySQL database. You will need to create a database in MySQL via Omnymbus, run a SQL script to create tables and insert data, create and execute a script containg SQL SELECT queries using the STUDENT table.
Please ensure that you can connect to MySQL/Omnymbus via the account your Professor has emailed to you. Please consult with the document titled MySQLOmnymbusSupport.docx located in the Doc-Sharing folder titled Omnymbus Tutorial Files for instructions on how to get help for any issues that you are having with the MySQL/Omnymbus Environment.
Due Midnight Mountain Time attached to the Week 1 DropBox. OR let me know if you are going to be delayed!
SQL file named Lab1_Query.sql containing SELECT statements
Text document named Lab1_Output.txt containing labeled output
Upload files to the Week 1 iLab Dropbox.
Please do not zip the files

Required Software
Omnymbus – MySQL at the edupe database server.
Access the software at https://devry.edupe.net:8300
If you have any problems, connection, password, technical, etc ONLY your instructor can assist. Using any other Help facility will delay the resolution of the problem as the request is forwarded in all cases to the instructor.

STEP 1: Logging in to Omnymbus
Look at your email account to obtain the MySQL/Omnymbus account and password that your Professor has emailed to you.
To help you log into MySQL Omnymbus environment, download the tutorial Login MySQL Omnymbus Environment in the Doc-Sharing folder titled "Omnymbus Tutorial Files".

STEP 2: Create a Database and modify your script to reference your Database
Create a MySQL database:
Download the tutorial Creating a Database in MySQL Omnymbus Environment from the folder in Doc-Sharing titled Omnymbus Tutorial Files. Follow the steps to create a database in MySQL, especially paying attention to the database naming conventions specified in the tutorial.
Download the Wk1LoadDatabaseScript.sql file from Doc Sharing in the folder titled iLab Documents.

STEP 3: Running script file in MySQL, create SQL
Download the tutorial Running SQL Scripts in MySQL Omnymbus environment from the folder in Doc-Sharing titled Omnymbus Tutorial Files. Follow those steps and execute the Wk1LoadDatabaseScript.sql file to create the tables and to insert data into them; and then download and run the script file with the queries iLab1QueryScript.sql. This script has the first few queries and it is intended that you should finish it. A demonstration in the Week 1 Live Lecture will be provided.

Create SQL SELECT Queries:
ALWAYS and only use a plain text editor such as Notepad! All word processors will insert hidden tags that confuse database processors.
Using the data in the Student table in the database, expand the supplied starter SQL script file named iLab1QueryScript.sql, containing queries to execute each of the tasks below.
To reference, learn and apply MySQL's own dialect of the SQL language to this iLab, browse through the file M10C_KROE8352_13_SE_WC10C.pdf in the Doc-Sharing folder titled My SQL Documents.
Save the completed results by copying and pasting to a file named iLab1_Results.txt
An incomplete example is provided in the Doc Sharing.

Query1
Write a SQL statement to display Student’s First and Last Name.

Query2
Write a SQL statement to display the Major of students with no duplications. Do not display student names.

Query3
Write a SQL statement to display the First and Last Name of students who live in the Zip code 82622

Query4
Write a SQL statement to display the First and Last Name of students who live in the Zip code 97912 and have the major of CS.

Query5
Write a SQL statement to display the First and Last Name of students who live in the Zip code 82622 or 37311. Do not use IN.

Query6
Write a SQL statement to display the First and Last Name of students who have the major of Business or Math. Use IN.

Query7
Write a SQL statement to display the First and Last Name of students who have the Class greater than 1 and less than 10. Use the SQL command BETWEEN.

Query8
Write a SQL statement to display the First and Last Name of students who have a last name that starts with an S.

Query9
Write a SQL statement to display the First and Last Name of students having an a in the second position of their first names.

Query10
Write a SQL expression to display each Status and the number of occurrences of each status using the Count(*) function; display the result of the Count(*) function as CountStatus. Group by Status and display the results in descending order of CountStatus.
Note: there are examples very similar to these in the Kroenke text

Rubric
Queries that are correct will be awarded the number of points shown below:
4 points: Query 1
5 points: Query 2 – 9
6 points: Query 10
The following rubrics will be used for incorrect queries:
0 points: Query was not turned in with the assignment.
-4 points: Query will not run.
-3 points: Query runs but is incorrect because query required a WHERE clause to meet requirements which was not included.
-2 points: Query runs but is incorrect because WHERE clause contained errors, gives popup for user input, or only meets partial requirements.

Please Make sure all requirements are met!

DBM/380 Week 4 Normalization of the Smith Consulting ERD

The following assignment is based on the database environment chosen and created in the Week Three Individual Assignment.
Your database project must meet the following assessment requirements:

Design and develop a database using professional principles and standards.
Provide a logical and physical design of the database.
Use relational database software application to develop database.   
Provide an entity relationship diagram.
Normalize the database.
Generate and provide test data.

Use a Microsoft® Visio® diagram to normalize the ERD to third normal form (3NF).

Use the Microsoft® Access® database created in Week Three to create a minimum of 10 rows of test data in each table. Also, create at least one query that joins two tables and returns values from both tables.

Note. Only the Microsoft® Visio® diagram must be normalized to the 3NF. The 3NF is not required for a Microsoft® Access® database.

Submit the ERD and final database to the appropriate Assignment link.

CMIS420 Project 2 Star Schema for OVS Database

In this assignment you will complete the creation and population of the Star Schema tables for the OVS, Inc. data warehouse database. In the Authentic Assessment (i.e. Final Project) you will be performing a number of decision support queries against this database.
1) Complete Homework #5 by ensuring that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIME (or TIMES if you desire) dimension tables are created and populated.

2) Create the SALES_FACTS fact table via SQL. Ensure that you have declared foreign keys of Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID to reference your TIME(S), VEHICLES, FINANCING_PLANS, and DEALERSHIPS tables, respectfully. Ensure that you have a primary key for the SALES_FACTS table that is a composite of the Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID columns. Do a DESC (i.e. DESCRIBE) of your SALES_FACTS table after it’s created.

3) Using PL/SQL populate the SALES_FACTS table. One way to do this is to use a cursor to go through all of your SALES table rows, joined with other tables as necessary, to determine each combination of Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID. Then for that combination of the four columns, calculate the total Vehicles_Sold and the Gross_Sales_Amount. If these values are zero then don’t INSERT a row into the SALES_FACT table. Only insert rows for combinations of the four foreign key columns where there were some vehicles sold. After populating your SALES_FACTS table execute the query “SELECT COUNT(*) FROM sales_facts;” to show the row count. Also execute the query “SELECT SUM(vehicles_sold) FROM sales_facts;” to ensure that you have included all of your 500 or more sales.

All SQL and PL/SQL should be executed via one or more SQL script files. The best approach is to have separate SQL script files for each step and then an SQL script file that calls all of them in sequence. Submit SQL*Plus SPOOL files produced by your SQL and PL/SQL showing all your SQL and PL/SQL code and the results, or if using iSQL*Plus or other GUI (e.g. SQL Developer), a single Word or PDF file of screen snapshots showing both your SQL and PL/SQL and the results. Do NOT submit your SQL script file.

ITSE 2309 LAB 2 More SQL Queries and Modification

Oracle 11g SQL–Chapters- 3,6,8,9,11,12,
You will continuing using items created in Lab 1

Lab 2a -- Problems 1–4,
1. For each customer, list each stock item ordered,
1) the manufacturer,
2) the quantity ordered, and
3) the total price paid.
Include the following columns in the order given below:
- From Customer Table: Company
- From Stock Table: Description
- From the Manufact Table: Manu_Name
- From the Items Table: Quantity, Total Price
Order the output by Company and Description.
Submit/hand in Output from SQL query

2. List all orders with a shipping date between December 25, 1999 and January 5, 2000
Include
1) the Order Number,
2) Order Date,
3) Customer company name, and
4) Shipping Date.
Order by
Customer Company Name and Order Number.
Submit/hand in Output from SQL query

3. Count the number of customers who do not have any orders placed.
Submit/hand in Output from SQL query

4. List all customers –
I) Who are ordering equipment whose description begins with 'tennis' or 'volleyball'.
II )Include
1) Customer number,
2) Stock number, and
3) Description.
Submit/hand in Output from SQL query
Do not repeat any rows.

Lab 2b Problems 5, 6, 7 and 8
5. Use the following SQL CREATE commands to CREATE the following tables in your
CREATE TABLE Professor
(Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,
Prof_Lname VARCHAR2(15) NOT NULL,
Prof_Hiredate DATE,
Prof_Sal NUMBER(8,2),
Prof_Dept CHAR(6),
);
CREATE TABLE Student
(Stu_ID NUMBER(4) Constraint pk_Student Primary Key,
Stu_Lname VARCHAR2(15) NOT NULL,
Stu_Major CHAR(6),
Stu_CredHrs NUMBER(4),
Stu_GradePts NUMBER(5),
Prof_ID NUMBER(3),
CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)
REFERENCES Professor
);
Submit/Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables created.

6. Insert the following data into the tables created above using SQL INSERT commands.
Professor Table:
Prof_ID Prof_Lname Prof_Hiredate Prof_Sal Prof_Dept
123 Hilbert 20-MAY-1992 58000.00 MATH
243 Newell 15-JUL-1997 65500.00 CMPSCI
389 Lessing 04-APR-1988 40250.00 ENG
Student Table:
Stu_ID Stu_Lname Stu_Major Stu_CredHrs Stu_GradePts Prof_ID
2001 Parker CMPSCI 52 160 243
2166 Smith ENG 30 75 389
3200 Garcia MATH 62 248 123
4520 Smith CMPSCI 45 157 NULL
BE SURE TO ISSUE A COMMIT AFTER TABLE MODIFICATION COMMANDS HAVE BEEN RUN SUCCESSFULLY.
Submit a
Listing of each INSERT command,
The systems response and the resulting tables after the INSERTS are completed
(Example: SELECT * FROM Student;).

7. Perform the following SQL DELETE statements. Be sure to do them in order.
Issue a COMMIT command after all DELETEs have run.
a. Try to delete Professor 389. What message do you get? ___________________________
b. Delete Student 2166.
c. Now Delete Professor 389. Explain why the first attempt in a. was unsuccessful, and this time the DELETE was successful.
Submit/hand in : A listing of the DELETE statements.
The answers to questions a. b. and c.
A listing of the two tables after the deletes have run.

8. Perform the following UPDATE commands.
Issue a COMMIT command after all UPDATEs have run.
a. Replace the value of the Prof_ID for Student 4520 with 243.
b. Add 10% to the salary for each professor
Submit/hand in : A listing of the UPDATE statements
A listing of the two tables after the UPDATEs have run.

ITSE 2309 LAB 1 Database and SQL Queries

Submit: Query Statements Query Output
Chapters 2,3,4,5 11g SQL book
Objectives: Be able to access ORACLE/SQL Plus Be able to do simple SQL operations

Steps:
- Log onto the sample Oracle 11g database created.
- See Chapter 7 in "Database Systems" for information on coding, saving, and executing your queries.
- Save the output of the queries by using the SPOOL option ( see chpt 14 in 11g SQL in lab resources tab)
- Save both the queries code and the output of the queries as "Lab1_LastName.txt and submit via Blackboard—using the - Attach file – Upload function

Do the following queries:
1. List all columns and rows in the stock table.
2. List the last name, first name, and company of all customers (List the columns in that order). Place the list in alphabetical order by company name.
3. List the company names for all customers from Sunnyvale, Redwood City, or San Francisco.
4. List all orders that were placed between the dates 12/31/1999 and 01/03/2000. List order number, order date, customer number, ship date, and paid date. (Hint: Specify year in single quotes 'DD-MMM-YYYY')
5. List the order number, order date, and shipping charges for all orders that are not on backlog and for which the shipping charge is over $15.00.
6. List all stock items which are baseball items which have a unit price greater than $200.00 and a manufacturer code which starts with 'H'. (Hint: use LIKE)
7. List the company name for all customers who have orders. Don not list a company more than once.
8. List the customer number and the description (from the stock table) of all items ordered by customers with customer numbers 104-108. Order the output by customer number and description. (There should be no duplicate rows in your output).
9. List the number of (distinct) customers having an order. Label the column "Total_Customers_with_Orders".
10. For each customer having an order, list the customer number, the number of orders that customer has, the total quantity of items on those orders, and the total price for the items. Order the output by customer number. (Hint: You must use a GROUP BY clause in this query).

Student Database Oracle DDL and Queries

Create the following tables.
STUDENT
Student Number (PK)
Student Last Name
Student Major
Department ID (FK)
Student GPA
Student Hours
Student Class
Advisor ID (FK)

ADVISOR
Advisor ID (PK)
Advisor Last Name
Advisor Office
Advisor Building
Advisor Phone

DEPARTMENT
Department ID (PK)
Department Code
Department Name
Department Phone

*NOTE* You will have to decide on how to handle the Department and Advisor ID foreign keys in terms of a numbering system as well as appropriate field widths and types for the fields.
The business rules which govern this database are: A student may have one advisor while an advisor may advise multiple students. A student belongs to only one department but each department can have many students.
Populate the table with data from Table P6.4 (p. 217).

There is an error in the book! Change Ortiz’s student number to be 200888. In addition, add these three students to your database.
STU_NUM 123984 995133 367181
STU_LNAME Freeman Wilder Green
STU_MAJOR CIT CIT BIS
DEPT_CODE CS CS IS
DEPT_NAME Computer Science Computer Science Business Informatics
DEPT_PHONE 5234 3951 3951
COLLEGE_NAME Informatics Informatics Informatics
ADVISOR_LNAME Strand Zhang Goh
ADVISOR_BLDG Griffin Griffin Griffin
ADVISOR_OFFICE 5132 3451 5612
ADVISOR_PHONE 1603 3512 7922
STU_GPA 2.5 3.9 2.3
STU_HOURS 97 58 63
STU_CLASS Senior Junior Junior

Part 1
Provide all DDL-related code. This includes table definition and creation, fk/pk creation, and populating the tables with data. Please include the output from Oracle that shows that everything was created correctly.

Part 2
Queries – For each query provide 1) What the output, specifically, should be based upon eyeballing the data 2) the SQL code used to generate the query, and 3) the output from Oracle. Please include any relevant fields you think the user of the query would need to interpret the output.
1. Advisors need the capability to generate a query that returns *all* student information based upon a student number. This will help them in the advisement process. Create a query that returns all information for student Freeman from each table.
2. Each year, college administrators need to know how many students are in each major. Create a query that counts the number of students in each major while displaying the major name.
3. Kroger has approached NKU with an internship opportunity! The business department chair needs to generate a mailing list to inform great students about a job opportunity. Create a query that shows all the student numbers, last names, majors, department names, and advisor’s last name for students in Business Admin. Students should have a minimum GPA of 2.5 or greater to be on this mailing list. List in ascending order of GPA.
4. To get an idea of the adequacy of admission standards, NKU needs to have an idea of the breakdown of students. By each college, show the number of students in each classification (freshmen, sophomore, junior, senior).
5. Due to a fire, Griffin Hall has burnt down. Write a SQL statement that updates all faculty who had offices in Griffin Hall to now be housed in the University Center.
6. New funding may be able to pay for an advising center. The dean would like to get an idea of how many students each faculty member currently advises. Create a query that shows the names and majors of students for each advisor along with the advisor’s name.
7. (Extra credit) Create a query that counts the number of students who are eligible for the Dean’s list (GPA >= 3.5) in each department.

Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

Problem: The management of ECO Clothesline wants to learn more about SQL and has determined a number of questions it wants SQL to answer. You must obtain answers to the questions posed by management.

Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Find all customers where the customer type is SAL. Include the Customer Number, Customer Name, and Sales Rep Number fields in the result. Save the query as Lab 7-1 Step 1 Query.
2. Find all customers located in Tennessee (TN) with a paid amount greater than $1,500.00. Include the Customer Number, Customer Name, and Amount Paid fields in the result. Save the query as Lab 7-1 Step 2 Query.
3. Find all customers whose names begin with the letter, C. Include the Customer Number, Customer Name, and City fields in the result. Save the query as Lab 7-1 Step 3 Query.
4. List all cities in descending order. Each city should appear only once. Save the query as Lab 7-1 Step 4 Query.
5. Display the customer number, name, sales rep number, first name, and last name for all customers. Sort the results in ascending order by sales rep number and customer number. Save the query as Lab 7-1 Step 5 Query.
6. List the average balance amount grouped by sales rep number. Name the average balance as Average Billed. Save the query as Lab 7-1 Step 6 Query.
7. Find the customer number and name for every pair of customers who are located in the same city. Save the query as Lab 7-1 Step 7 Query.
8. Find the customer numbers, names, and sales rep numbers for all customers that have open orders. Use the alias O for the Open Orders table and C for the Customer table. Each customer should appear only once. Save the query as Lab 7-1 Step 8 Query.
9. Use a subquery to find all sales reps whose customers are located in Pineville. Save the query as Lab 7-1 Step 9 Query.
10. Find the average balance amount for sales rep 44. Save the query as Lab 7-1 Step 10 Query.
11. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 7 Make It Right Correcting Errors in the Query Design

Analyze a database, correct all errors, and/or improve the design.

Instructions: Start Access. Open the College Pet Sitters database. See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the files required in this book.

College Pet Sitters is a database maintained by a small pet-sitting business owned by college students. The queries shown in Figure 7 – 37 contain a number of errors that need to be corrected before the queries run properly. The query shown in Figure 7 – 37a displays the Enter Parameter Value dialog box, but this is not a parameter query. Also, the owners wanted to assign the name, Total Amount, to the Balance + Paid calculation. Save the query with your changes.

When you view the results for the query shown in Figure 7 – 37b, you get 30 records. You know this is wrong. Also, the query did not sort correctly. The query results should be sorted first by sitter number and then by descending balance. Correct the errors and save the query with your changes.

Change the database properties, as specified by your instructor. Submit the revised database in the format specified by your instructor.