Coursera_Managing Big Data with MySQL_Note Teradata

Teradata

  • Get a list of columns in a table
1
HELP TABLE + Table_Name;

Same function in MySQL, using SHOW or DESCRIBE

  • Get information about a single column in a table
1
HELP COLUMN + Column_Name;

Most of the synatx we used to look at our data in Teradata is the same we use in MySQL.

  • Get information whether or not a column is a primary or foreign key
1
SHOW table + Table_Name

Operator

  • TOP

Terdata uses a TOP operator instead of a LIMIT operator to restrict the length of a query output.

1
2
SELECT TOP 10 *
FROM strinfo

Function

  • SAMPLE

The SAMPLE function allows we to select a random sampling of the data in a table.

  1. The following query would retrieve 10 random rows from the strinfo table:
1
2
3
SELECT *
FROM strinfo
SAMPLE 10
  1. The following query would retrieve a random 10% of the rows from the strinfo table:
1
2
3
SELECT *
FROM strinfo
SAMPLE .10

The Other important differences between MySQL and Teradata

1.

  • DISTINCT and LIMIT can be used in the same query statement in MySQL, but DISTINCT and TOP cannot be used together in Teradata

  • MySQL accepts either double or single quotation marks around strings of text in queries, but Teradata will only accept single quotation marks

  • MySQL will accept the symbols “!=” and “<>” to indicate “does not equal” but Teradata will only accept “<>”. (other operators, like “IN”, “BETWEEN”, and “LIKE” are the same: http://www.teradatawiki.net/2013/09/Teradata-Operators.html)

2.

GROUP BY

The syntax for standard aggregate functions, HAVING clauses, and joins is the same in Teradata as it is in MySQL.

GROUP BY clauses in Teradata differ form GROUP BY clauses in MySQL.

In Teradata (and many other database systems): Any non-aggregated column in the SELECT list or HAVING list of a query with a GROUP BY clause must also listed in the GROUP BY clause.

Make sure any non-aggregate column in your SELECT list is also listed in your GROUP BY clause.