This lab helps you learn how to create reports from data stored within a databas

Question

This lab helps you learn how to create reports from data stored within a database system. It does not matter where the database is stored whether the database is local or in a cloud system somewhere, the concepts in this lab are still the same when compared to other types of databases. As you gain more experience working with table records then using and managing the database itself, eventually working with queries will become easier. Using the existing database that were built in a previous lab, you will write basic SQL queries to retrieve the data. Create a new Word lab document for this week's lab objectives then take screenshots of all your work and paste into the document for submission.

Lab Objectives Checklist

Write an SQL Select Statement

Apply a Wildcard to the Select Statement

Apply a Filter to a Select Statement

Apply Wildcards to a Filter

Login to the phpMyAdmin Client

Utilize the MySQL Database Management Tool

Retrieve Data from a Specific Database Table

Objective #1 Write a Basic Select Query for Reporting Purposes

In this objective, write a basic select query to get (read) records from the cloud database table using the Azure phpMyAdmin web client. By now, you should already know how to login to the phpMyAdmin web client. Once you logged in, select your database table and click on the SQL menu option at the top. This will give you a developer window that can process SQL commands against the table you clicked on. Modify the Select query statement in the developer window, so it returns specific column names and separate each column with a comma "," and make sure you write the from statement which selects a specific table (the square brackets represent a placeholder, do not use them in your actual SQL statement) then press the Go button.

Sample of Displaying Multiple Columns to the Screen:

Select Column1, Column2, Column3

From [tablename]

Objective #2 Write an SQL Query with a Filter Use this objective to write another SQL statement and expand the previous select statement. Apply a filter to the select statement by adding a where clause. The Where clause lets you return specific records that you need to read which filters out all other records.

Sample of a Where Clause Filter:

Select Column1, Column2, Column3

From [tablename] Where Column1 = 1

Objective #3 Write an SQL Query with Wildcards In this objective you will update the existing SQL statement and implement a wildcard to filter and search for specific criteria. The wildcard are special characters that the MySQL server will recognize as a keyword to search through the data and look for matching query details. There are two types of wildcards, one can be applied in the select clause and other is applied in the where clause. The where clause wildcard uses the "%" and you can do specific searches either left or right of the string searching criteria. Modify your SQL query with a wildcard in the where clause statement.

Sample of a Select Wildcard

Select * From [tablename]

Sample of a Where Clause Wildcard

Select Column1, Column2, Column3

From [tablename]

Where Column1 LIKE '%Rass%'

Submit your completed assignment

Explanation / Answer

Objective #1 Write a Basic Select Query for Reporting Purposes

SELECT E.EMP_ID, E.FIRTS_NAME, E.LAST_NAME, E.JOIN_DATE FROM EMPLOYEE E;

Objective #2 Write an SQL Query with a Filter Use this objective to write another SQL statement and expand the previous select statement.

SELECT E.EMP_ID, E.FIRTS_NAME, E.LAST_NAME, E.JOIN_DATE FROM EMPLOYEE E WHERE E.JOIN_DATE BETWEEN TO_DATE('01-JAN-2017','DD-MON-YYYY') AND TO_DATE('31-DEC-2017,'DD-MON-YYYY') ORDER BY E.JOIN_DATE ASC;

Objective #3 Write an SQL Query with Wildcards In this objective you will update the existing SQL statement and implement a wildcard to filter and search for specific criteria

SELECT E.EMP_ID, E.FIRTS_NAME, E.LAST_NAME, E.JOIN_DATE FROM EMPLOYEE E WHERE E.JOIN_DATE BETWEEN TO_DATE('01-JAN-2017','DD-MON-YYYY') AND TO_DATE('31-DEC-2017,'DD-MON-YYYY') ORDER BY E.JOIN_DATE ASC AND E.FIRST_NAME LIKE 'John%';

Please let me know in case of nay clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.