iLab 1 of 4: SQL Queries Using MySQL (75 points)
This 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 thesestep-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 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.
• 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 WeekOneiLabScript.sql file 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:
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.
• 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.
STEP 4: Save and Upload to Dropbox
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.
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.
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at [email protected].
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at [email protected] and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at [email protected].
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Incase you face any issues with the tutorial, please free to contact us on [email protected]
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.