STEP 1: Logging in to Omnymbus
Back to Top •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
Back to Top 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 WeekOneiLabScript.sql file from Doc Sharing in the folder titled iLab Documents
STEP 3: Running script file in MySQL, create SQL SELECT Queries
Back to Top 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:
•Using 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 Documents.
- Save each query with a screen shot of the output in a MS Word document named Lastname_Lab1_Output.
Name Task
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.
STEP 4: Save and Upload to Dropbox
Back to Top When you are done, zip the files Lastname_Lab1_Query.sql and Lastname_Lab1_Query_Output file and upload them to the Week 1: iLab Dropbox.
Rubric
Back to Top 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.