School of Computer Scienceswestern Illinois University

School of Computer SciencesWestern Illinois University

IS 342 Amaravadi

Assignment#6Worth: 20 Pts

SQL SERVER WITH MYSQL

OVERVIEW

My SQL is an SQL server which is essentially a DBMS engine that processes SQL commands. It does not have the usual interface of desktop DBMS products. So it is called from what is commonly known as a “command line” interface. MySQLWorkbench provides a user friendly interface to this command line. You will need to download the software and install on your home computer. You will use the Workbench to connect to the DBMS, login, andselect a database (“schema”). Once connected to a database, SQL commands can be entered. You will create a table, insert records and update them. The name of the table should correspond with yourlastname_prod_table as in amar_prod_table. There is a 5 point penalty for incorrect naming.

DOWNLOAD AND INSTALL MYSQL WORKBENCH

Go to

and down load the installer program and run it. It will install itself, but make sure to specify that you need a desktop shortcut. Please be warned that it is intended to be used by developers and is not the world’s most user friendly package. If it is not available in the labs, you need to register, download the software and install it on your own PC. The version is not very important.

ENTER PROFILE (FROM ON-CAMPUS AND ALREADY LOGGED INTO ECOM ACCOUNT)

Start MySQL workbench and go to “Database” and “Manage Connections”, and select “new connection”

Type a name for the connection (e.g. amaravadi_connection), enter “mysql.wiu.edu” for hostname and “3795” for the port. Your user name is your last name in lower case (e.g. amaravadi). Your password is your last name, followed by the last three digits of your WIU id# (e.g. amaravadi123). Enter user name and store password in vault.

Test the connection and press OK. You should go back to the main menu.

ENTER PROFILE (FROM OFF-CAMPUS)

Start MySQL workbench and go to “Database” and “Manage Connections”, and select “new connection”

Type a name for the connection, select connection method as “Standard TCP/IP over SSH,” enter “uxb3.wiu.edu” for SSH Hostname, your ecom user name for “SSH Username,” and enter ecom password for “SSH password”for storing in the vault. For MYSQL, enter “mysql.wiu.edu” for hostname and “3795” for the port. Your mysqluser name is your last name in lower case (e.g. amaravadi). Your mysqlpassword is your last name, followed by the last three digits of your WIU id# (e.g. amaravadi123). Enter user name and store password in vault.

Test the connection and press OK. It then says whether or not the test was successful. You should go back to the main menu. If uxb3 does not work, try uxb.

CONNECT TO MYSQL/DATABASE

The main menu will have your connection, in this case, “test_connection” as shown below. Double click on it.

CREATE PRODUCT TABLE

You first need to select “prod_db” from “Schemas” (see below left) and right click on it and make it the “default schema.” Then you can create a table by selecting “Create New table” icon from the menu on top.

1

1

Next you need to enter a name for the table. Enter “your last name_prod_table” as shown below.

Incorrect names have a five point penalty, since it is important to know who created the table.

Next click under “column names” and type the schema definition for the table as shown below.

Select “Apply”

When you select “Apply” you should get confirmation of the message. You should see the new table that you created as shown below.

INSERT RECORDS INTO YOUR_LAST_NAME_PROD TABLE

Once the table is created, SQL commands can be typed into the space as shown below. You need to insert only the first four records shown in the table below. The record’s values have to correspond with those shown below.

Insert these values

Once you have typed in the insert statements run them with the “lightning bolt” icon below.

The only way to see inserted records is to type “Select * from…..” into the SQL area.

UPDATE PRICES IN YOUR_LAST_NAME_PROD TABLE

Once the data is entered, update prices to 1.5 times the existing prices.

ADD VCODE

You need to add the vendor code attribute to your table

UPDATE THE VCODE VALUES

Update the values of thevcode attribute to 88 in your_last_name_prod_table.

SELECT RECORDS FROM amaravadi_prod_table

Research online for the format of Select…into SQL statement. Use it to retrieve records from amaravadi_prod_table into the table that you created for all records where the vcode = 99;

After this you are done.

Congratulations! You do not need to turn in anything.

1