programming 6

______________________________________________________________________________________________________________________________________________________________________________________________________________________

CTI110 Page 3

______________________________________________________________________________________________________________________________________________________________________________________________________________________

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 set expected at the end of the lab

· Paste the required snip shots into ONE Word document.

· Naming Convention: “lastname-cmd-sql”

· Upload your 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 mydatabase using the tables TIMESHEET and PERSONNEL.

· 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

image5.png

image6.png

image7.png

image8.png

image9.JPG

image10.png

image11.png

image2.png

image3.png

image4.png