week, you will learn to create and run SQL SELECT queries 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 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.
Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these .equella.ecollege.com/file/8ff9f27a-3772-48cf-9855-4bec4e6706bf/1/Dropbox.html">step-by-step instructions.
(See the Syllabus section "Due Dates for Assignments & Exams" for due dates.)
- SQL file named LastName_Lab1_Query.sql containing SELECT statements
- Word document named LastName_Lab1_Output containing queries and output
- Zip the files, and upload the zipped file to the Week 1: iLab Dropbox.
Omnymbus – MySQL
Access the software at .edupe.net:8300">https://devry.edupe.net:8300
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.
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 Environmentfrom 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 WeekOneiLabScript.sqlfile from Doc Sharing in the folder titled iLab Documents
STEP 3: Running script file in MySQL, create SQL SELECT Queries
Run scripts files in MySQL:
- Download the tutorial Running SQL Scripts in MySQL Omnymbus environment from the folder in Doc-Sharing titled Omnymbus Tutorial Files. Follow those steps to help execute the WeekOneiLabScript.sql file needed to create the needed tables and to insert data into them.
Create SQL SELECT Queries:
the data in the Student table in the database, create a SQL script file
named LastName_Lab1_Query.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
- Save each query with a screen shot of the output in a MS Word document named Lastname_Lab1_Output.
|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.|
Write a SQL statement to display the First and Last Name of students who have the major of Business or Math. Use IN.
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
|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.|
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.
STEP 4: Save and Upload to Dropbox
When you are done, zip the files Lastname_Lab1_Query.sqland Lastname_Lab1_Query_Output file and upload them to the Week 1: iLab Dropbox.