Lesson 11
Lab–Work with SQL at the Command Line
Objectives
In this lab assignment, students will demonstrate the abilities to:
- Start MySQL and Apache server
- Access MySQL using the Command Line
- Use the Command Line for SQL Commands
- Change database and list tables using the Command Line
- Use a Database
- Describe a Database
- Create simple queries from a table
Lab Task Checklist
- Review:
- The cmd_lecture presentation
- Videos or Text Based Tutorials for Command Line (Windows) and Terminal (MAC) found in “Additional Materials”.
- You will be using the ‘mydatabase’ database created in lab 10. If you were unsuccessful in Lab 10 talk to your instructor who may have an optional way to get the database created.
- Complete the SQL Exercises and record work with snip shots
- Make sure your snip shot includes both the SQL statement used and the result set
- Check your work using the result setexpected at the end of the lab
- Paste the required snip shots into ONE Word document.
- Naming Convention: “lastname-cmd-sql”
- Uploadyour file to Blackboard
Instructions
- Launch your command line interface. You may press Windows logo key plus r key (i.e. hold down Windows logo key + r key) and type cmd.
- Your Windows command line window pops up.
- Create the required queries from the database mydatabaseusing the tables TIMESHEETand
- Take a snapshot of the completed process/query with the command line.
- Example:
- Queries Required:
| Query | Task Required | Table to Use |
| 1 | describes the data structure | timesheet |
| 2 | show all the data and records | timesheet |
| 3 | show only the lastname and hourlyWage | personnel |
| 4 | show all the data and records where the order is in ascending (default) based on the hoursWorked | timesheet |
| 5 | show all data and records WHERE employees worked less than or equal to 30 OR equal to 40 hours | timesheet |
| 6 | show all data and records where the last digit in the empID is 8 | timesheet |
| 7 | show hourlyWage and lastName for records where the hourlyWage is equal to or more than $8.00 and less than or equal to $12.00 using the BETWEEN operator | personnel |
| 8 | show the empID and hoursWorked where the last digit in empID is 7 AND hoursWorked = 35 | timesheet |
| 9 | show jobTitle and lastname where the order is first based on jobTitle (ascending) and then hourlyWage (descending) order | personnel |
Result Set Expected:
Query 1:
Query 2:
Query 3:
Query 4:
Query 5:
Query 6:
Query 7:
Query 8:
.
Query 9:
Rubrics:
Submittal Requirement met: 9
Each Query: 11.11