Programming 6

by


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