Category Archives: Statements SQL

Java Video Tutorial, Statements SQL, Web Design

Java Video Tutorial 38

39 Comments

Java JTables Writing to a DatabaseIn my previous JTable Video Tutorial I showed you how to pull information from a database and work with it in a JTable.

In this tutorial I show you how to add and delete rows in a JTable that will then effect the database. I also cover allowing the user to change the database on a cell basis.

After this tutorial, you’ll be able to do anything with Java JTables.

Continue reading Java Video Tutorial 38 →

Statements SQL, Web Design

New Riders MySQL Code

3 Comments

New Riders MySQL CodeWell over a decade ago, a book came out simply titled MySQL by Paul DuBois. Everyone I know learned about MySQL from this book.

I guess I mentioned in the past that I liked this book and ever since I get requests for the code from the book. I guess it isn’t available online anymore?

So, here I gather together all of the code in one place. So, I can just point people to it from now on 🙂

Continue reading New Riders MySQL Code →

Java Video Tutorial, Statements SQL

Baseball Sabermetrics

4 Comments

baseball sabermetricsPreviously I made a couple of Baseball Sabermetrics videos and it was a lot of fun. In this Sabermetrics tutorial I’ll cover a few of the Sabermetrics formulas I missed.

One question I received from a few of you was which team uses Sabermetrics most effectively. You’ll find that answer in the first query below named TTRC for Teams.

I also show how to perform many Sabermetric Pitching formulas below.

Continue reading Baseball Sabermetrics →

Java Video Tutorial, Statements SQL, Web Design

Sabermetrics 3

5 Comments

Sabermetrics BaseballI continue covering some neat Sabermetrics formulas in this video. If you missed part 1 of my Sabermetrics Tutorial, check it out first.

I’m going to cover a few new complicated formulas including: Theoretical Team Runs Created, Batting Average on Balls in Play, Weighted On Base Average and more. I’m also going to show you how to easily save queries using views.

This tutorial has been a great deal of fun. All formulas follow the video.

Continue reading Sabermetrics 3 →

Statements SQL

Sabermetrics 2

5 Comments

Sabermetrics BaseballHere I continue my Sabermetrics Baseball tutorial. If you missed part one, check it out first because in it I show you how to get all of your tools. It is here Sabermetrics.

I’m going to introduce a few new SQL functions being CONCAT, COALESCE and HAVING.

We will also calculate the top players based on RBIs, HRs, Batting Average, On Base Percentage, and Slugging Percentage. And, I’ll show you how to pull in data on the players position and their salary.

Continue reading Sabermetrics 2 →

Statements SQL, Web Design

Sabermetrics

13 Comments

SabermetricsIn this article I introduce you to the world of Sabermetrics. Sabermetrics is the analysis of baseball statistics to measure player performance, in game probabilities and much more.

With Sabermetrics you’ll be able to do most anything from decide who was the greatest hitter of all time to analyze the probability that your team will win.

But, to get started with Sabermetrics, you need to first get your tools being MySQL and a Baseball Stat Database. Both are free! More info follows the video below.

Continue reading Sabermetrics →

How to Code PHP, Statements SQL, Web Design

Web Design and Programming Pt 19

5 Comments

Help Web Design and ProgrammingIn this web design and programming video tutorial I will review a lot of what you have been taught previously. I’ll review many topics by showing you how to make drop down boxes that are populated from data stored in a database. If you don’t completely understand everything don’t worry about it. That’s why it is a review.

All of the code from the video follows the video. I included additional comments in the code that you can better understand what is going on. You also should print out the code and refer to it as you watch the video for better comprehension.

Here is a dump file for the database Database Dump File.

If you have any questions or comments leave them below, otherwise enjoy the video 🙂

Code From the Video

<?php
DEFINE (‘DBUSER’, ‘mysqladm’);
DEFINE (‘DBPW’, ‘Turtle2Dove’);
DEFINE (‘DBHOST’, ‘localhost’);
DEFINE (‘DBNAME’, ‘hamdb’);
if ($dbc = mysql_connect (DBHOST, DBUSER, DBPW)) {
if (!mysql_select_db (DBNAME)) { // If it can’t select the database.
// Handle the error.
trigger_error(“Could not select the database!<br />MySQL Error: ” . mysql_error());
exit();
} // End of mysql_select_db IF.
} else {
// Print a message to the user, and kill the script.
trigger_error(“Could not connect to MySQL!<br />MySQL Error: ” . mysql_error());
exit();
}
?>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml” lang=”en” xml:lang=”en”>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=ISO-8859-1″ />
<title>Products</title>
<script language=JavaScript>
// This function is called to reload the page and pass the values chosen by the user
function reload(form)
{
// Get the value of the dropdown named cat
var val=form.cat.options[form.cat.options.selectedIndex].value;
// Loads the new page with the stored variable values
self.location=’hamtest.php?cat=’ + val ;
}
function reload2(form)
{
var val=form.cat.options[form.cat.options.selectedIndex].value;
var val2=form.subcat.options[form.subcat.options.selectedIndex].value;
self.location=’hamtest.php?cat=’ + val + ‘&cat3=’ + val2 ;
}
</script>
</head>
<body>
<?php
// Getting the data from Mysql table for first list box
$quer1=mysql_query(“SELECT DISTINCT Prod_Type,PT_ID FROM Product_ID
WHERE PT_ID IN (0,1,2,3,4,7,8,14,15,16,31,32,33,34,35,36,37) order by PT_ID”);
// For second drop down list we will check if category is selected else we will display all the subcategory
$cat=@$_GET[‘cat’]; // This line is added to take care if your global variable is off
if(isset($cat) and strlen($cat) > 0){
$quer=mysql_query(“SELECT DISTINCT Manufacturer_ID.Manufacturer,Manufacturer_ID.Man_ID FROM Manufacturer_ID, Model_Numbers
where ((Manufacturer_ID.Man_ID = Model_Numbers.Man_ID) AND (Model_Numbers.PT_ID = $cat)) order by Manufacturer”);
}else{$quer=mysql_query(“SELECT DISTINCT Manufacturer,Man_ID FROM Manufacturer_ID order by Manufacturer”); }
// end of query for second subcategory drop down list box
//  For Third drop down list we will check if sub category is selected else we will display all the subcategory3
$cat3=@$_GET[‘cat3’]; // This line is added to take care if your global variable is off
if(isset($cat3) and strlen($cat3) > 0){
$quer2=mysql_query(“SELECT DISTINCT Model_Num, Model_Num FROM Model_Numbers where ((Model_Numbers.Man_ID=$cat3) AND (Model_Numbers.PT_ID = $cat)) order by Model_Num”);
}else{$quer2=mysql_query(“SELECT DISTINCT Model_Num, Model_Num FROM Model_Numbers order by Model_Num”); }
//  End of query for third subcategory drop down list box
echo “<form method=post name=f1 action=’confirm.php’>”;
//  Starting of first drop downlist
echo “<select name=’cat’  onchange=\”reload(this.form)\”><option value=”>Select one</option>”;
while($results = mysql_fetch_array($quer1)) {  // Fetch a row of data based on a query
if($results[‘PT_ID’]==@$cat){echo “<option selected value=’$results[PT_ID]’>$results[Prod_Type]</option>”.”<br />”;}
else{echo  “<option value=’$results[PT_ID]’>$results[Prod_Type]</option>”;}
}
echo “</select>”.”<br />”;
//  Starting of second drop downlist
echo “<select name=’subcat’  onchange=\”reload2(this.form)\”><option value=”>Select one</option>”;
while($results2 = mysql_fetch_array($quer)) { // Fetch a row of data based on a query
if($results2[‘Man_ID’]==@$cat3){echo “<option selected value=’$results2[Man_ID]’>$results2[Manufacturer]</option>”.”<br />”;}
else{echo  “<option value=’$results2[Man_ID]’>$results2[Manufacturer]</option>”;}
}
echo “</select>”.”<br />”;
//  This will end the second drop down list
echo “<select name=’subcat3′ ><option value=”>Select one</option>”;
while($results3 = mysql_fetch_array($quer2)) {
echo  “<option value=’$results3[Model_Num]’>$results3[Model_Num]</option>”;
}
echo “</select>”.”<br />”;
?>
<input type=submit name=Submit value=Submit>
</form>
</body>
</html>
Statements SQL, Web Design

Web Design and Programming Pt 18

Leave a comment

MySQL StatementsIn this video tutorial I cover how to do numerous things with MySQL. I show you how to:

  • Join Data in Tables
  • Create Indexes
  • Delete Indexes
  • Change Column Data Types
  • Change Table Names
  • Create Full Text Indexes
  • Change Data in Columns
  • Perform Full Text Searches

And a bunch more…

All of the MySQL statements follow the video. If you have any questions or comments leave them below.

In the next tutorial I’ll be getting into how to use MySQL with PHP.

SQL Statements from the Video

select prod_type,pt_id from product_id;

describe manufacturer_id;
describe model_numbers;

describe manufacturer_id;describe model_numbers;

SELECT DISTINCT Manufacturer_ID.Manufacturer,Manufacturer_ID.Man_ID FROM Manufacturer_ID, Model_Numbers

WHERE ((Manufacturer_ID.Man_ID = Model_Numbers.Man_ID) AND (Model_Numbers.PT_ID = 45)) order by Manufacturer;

use customer;

show tables;

describe customer;

alter table customer add index state_id (state_id);

describe customer;

alter table customer add index phone_id(phone_id);

drop index state_id on customer;

alter table customer add UNIQUE state_id(state_id);

drop index state_id on customer;

alter table customer add index state_id (state_id);

describe customer;

alter table customer add index order_id(order_id);

create index index_name on tbl_name(columnsToIndex);

alter table customer add column comments varchar(40);

describe customer;

alter table customer change column comments comments text;

describe customer;

alter table customer rename as newTableName;

alter table customer add fulltext index (comments);

show create table customer \G

select * from customer;

describe phone;

insert into phone values (NULL, ‘412-876-7878′,’Work’);

insert into customer values(‘Sally’,’Smith’,’234 Main St’,’Happy Town’,3,15234,SHA(‘Monkey2′),2,NULL,NULL,’Really nice lady who loves cats’);

update customer set comments=’Crazy man who has 2 cats named Peekaboo and Sophie’ where cust_id=1;

insert into customer values(‘Paul’,’Smith’,’234 Main St’,’Happy Town’,3,15234,SHA(‘pw1234′),2,NULL,NULL,’Sally Smiths husband he wants a dog’);

select first_name, comments from customer where match (comments) against (‘crazy’);

insert into customer values(‘Mark’,’Marks’,’194 South St’,’Happy Town’,3,15234,SHA(‘abc1234′),3,NULL,NULL,’Craziest man I ever met. Loves dogs’);

select first_name, last_name, comments from customer where match(comments) against(‘craz*’ in boolean mode);

select first_name, last_name, comments from customer where match(comments) against(‘+craz* -cat*’ in boolean mode);

How to Code PHP, Statements SQL, Web Design

Web Design and Programming Pt 16

2 Comments

MySQL LogoIn this video tutorial I show you the many ways to insert information into MySQL. I specifically show you how to enter the information:

  • Directly through the MySQL shell
  • Through PHP code
  • From a CSV file

I then go into some other basic queries you need to understand to use MySQL. All of the code used follows the video.

Continue reading Web Design and Programming Pt 16 →

Statements SQL

Complete SQL Statements Video Tutorial

26 Comments

Today I present the whole SQL Statements Tutorial Video Series. I did my best to make sure, after you watch this series of videos, you will be an expert at programming with SQL. Please leave any comments and questions below.

[youtube]http://www.youtube.com/watch?v=WV04VAaQVRA[/youtube]

MySQL SQL Statements Tutorial 1

Here I explain what SQL is & show many statements in SQL. I explain:

  • What a Database is
  • Why you should use MySQL
  • What SQL is
  • Explain what a Relational Database Management System (RDMS) is
  • Show you how to Create a Table
  • Explain every SQL Data Type (Numeric, Character, Date, etc.)
  • Give you an Overview of what you’ll Learn in this Video Series

Continue reading Complete SQL Statements Video Tutorial →

Blog How To Set Up, Statements SQL

MySQL & Statements in SQL

9 Comments

There are many misconceptions when it comes to databases:

  • Some people think they are hard to develop (Not True)
  • Many people think they are expensive (There are Free Versions Available)
  • Most people don’t know how to start using them (You’re in the right place)

In the next series of articles I will show you how to use mySQL. A completely free database server that many consider to also be the best. Heck, NASA uses MySQL.

So do you need one?

If you answer any of these questions affirmative, the answer is yes:

  • Do you have trouble managing your data, because it is unorganized
  • Do you have massive amounts of data that is too complicated to store in a spreadsheet
  • Would you like to provide access to your data on the internet
  • Would you like to be able to sort through and analyze your data

Continue reading MySQL & Statements in SQL →