“They can conquer who believe they can.”
- Rita Rudner
CREATE [ [LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
( { column_name data_type [column_attributes] [ column_constraints ]
| table_constraints
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ] )
[table_attribute]
where column_attributes are:
[ DEFAULT default_expr ]
[ IDENTITY ( seed, step ) ]
[ ENCODE encoding ]
[ DISTKEY ]
[ SORTKEY ]
and column_constraints are:
[ { NOT NULL | NULL } ]
[ { UNIQUE | PRIMARY KEY } ]
[ REFERENCES reftable [ ( refcolumn ) ] ]
and table_constraints are:
[ UNIQUE ( column_name [, ... ] ) ]
[ PRIMARY KEY ( column_name [, ... ] ) ]
[ FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ]
and table_attributes are:
[ DISTSTYLE { EVEN | KEY | ALL } ]
[ DISTKEY ( column_name ) ]
[ SORTKEY ( column_name [, ...] ) ]
Creates a new table in the current database. The owner of this table is the issuer of the CREATE TABLE command.
When you create a temporary table, it is visible only within the current session. The table is automatically dropped at the end of the session. Above, we use the pound sign (#) at the front of the table name to automatically make the table a temporary table. We then populate the table with an Insert/Select
When you create a temporary table, it is visible only within the current session. The table is automatically dropped at the end of the session. A derived table only lasts the life of a single query, but a temporary table last the entire session. This allows a user to run hundreds of queries against the temporary table. A temporary table can have the same name as a permanent table, but I don't recommend this. You don't give a temporary table a schema because it is automatically associated with the users session. Once the session is over, the table and data are dropped. If the user tries to query the table in another session, the system won't recognize the table. In other words, the table doesn't exist outside of the current session it was created in.
When you create a temporary table, it is visible only within the current session. The table is automatically dropped at the end of the session. Above are some examples that allow you to define a different distkey, diststyle and sortkey. Users (by default) are granted permission to create temporary tables by their automatic membership in the PUBLIC group. To remove the privilege for any users to create temporary tables, revoke the TEMP permission from the PUBLIC group and then explicitly grant the permission to create temporary tables to specific users or groups of users.
• 9,900 permanent tables.
• The maximum number of characters for a table name is 127.
• The maximum number of columns you can define in a single table is 1,600.
CREATE TABLE
Student_Table_Backup
AS
SELECT *
FROM Student_Table;
“To have everything is to possess nothing.”
--Buddha
The resulting table inherits the distribution and sort key from the Student_Table (STUDENT_ID). Buddha might have been wrong here. "To have everything is to possess 9,900 permanent tables."
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table. If a table has a large unsorted region, a deep copy is much faster than a vacuum. You can choose one of four methods to create a copy of the original table
1) Use the original table DDL. This is the best method for perfect reproduction.
2) Use CREATE TABLE AS (CTAS). If the original DDL is not available, you can use CREATE TABLE AS to create a copy of current table, then rename the copy. The new table will not inherit the encoding, distkey, sortkey, not null, primary key, and foreign key attributes of the parent table.
3) Use CREATE TABLE LIKE. If the original DDL is not available, you can use CREATE TABLE LIKE to recreate the original table. The new table will not inherit the primary key and foreign key attributes of the parent table. The new table does, though, inherit the encoding, distkey, sortkey, and not null attributes of the parent table.
4) Create a temporary table and truncate the original table. If you need to retain the primary key and foreign key attributes of the parent table, you can use CTAS to create a temporary table, then truncate the original table and populate it from the temporary table. This method is slower than CREATE TABLE LIKE because it requires two insert statements.
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table. If a table has a large unsorted region, a deep copy is much faster than a vacuum. The difference is that you cannot make concurrent updates during a deep copy operation which you can do during a vacuum. The next four slides will show each technique with an example.
1) Use the original table DDL. This is the best method for perfect reproduction.
1. Create a copy of the table using the original CREATE TABLE DDL.
2. Use an INSERT INTO ... SELECT statement to populate the copy with data from the original table.
3. Drop the original table.
4. Use an ALTER TABLE statement to rename the copy to the original table name.
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
2) Use CREATE TABLE AS (CTAS). If the original DDL is not available, you can use CREATE TABLE AS to create a copy of current table, then rename the copy. The new table will not inherit the encoding, distkey, sortkey, not null, primary key, and foreign key attributes of the parent table.
1. Create a copy of the original table by using CREATE TABLE AS to select the rows from the original table.
2. Drop the original table.
3. Use an ALTER TABLE statement to rename the new table to the original table.
The following example performs a deep copy on the Sales_Table using a duplicate of the Sales_Table named Sales_Table_Copy.
CREATE TABLE Sales_Table_Copy as (select * from Sales_Table) ;
DROP TABLE Sales_Table ;
ALTER TABLE Sales_Table_Copy rename to Sales_Table ;
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
2) Use CREATE TABLE LIKE. If the original DDL is not available, you can use CREATE TABLE LIKE to recreate the original table. The new table will not inherit the primary key and foreign key attributes of the parent table. The new table does though inherit the encoding, distkey, sortkey, and not null attributes of the parent table.
1. Create a new table using CREATE TABLE LIKE.
2. Use an INSERT INTO ... SELECT statement to copy the rows from the current table to the new table.
3. Drop the current table.
4. Use an ALTER TABLE statement to rename the new table to the original table.
The following example performs a deep copy on the Sales_Table using a duplicate of the Sales_Table named Sales_Table_Copy.
CREATE TABLE Sales_Table_Copy (like Sales_Table);
INSERT INTO Sales_Table_Copy (select * from Sales_Table);
DROP TABLE Sales_Table;
ALTER TABLE Sales_Table_Copy RENAME to Sales_Table;
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
Create a temporary table and truncate the original table. If you need to retain the primary key and foreign key attributes of the parent table, you can use CTAS to create a temporary table, then truncate the original table and populate it from the temporary table. This method is slower than CREATE TABLE LIKE because it requires two insert statements.
1. Use CREATE TABLE AS to create a temporary table with the rows from the original table.
2. Truncate the current table.
3. Use an INSERT INTO ... SELECT statement to copy the rows from the temporary table to the original table.
4. Drop the temporary table.
The following example performs a deep copy on the Sales_Table using a duplicate of the Sales_Table named Sales_Table_Copy.
CREATE Temp Table Sales_Table_Copy as select * from Sales_Table ;
TRUNCATE Sales_Table ;
Insert Into Sales_Table (select * from Sales_Table_Copy);
DROP Table Sales_Table_Copy;
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
• Exists only within a query
• Materialized by a SELECT Statement inside a query
• Space comes from the User’s Spool space
• Deleted when the query ends
The SELECT Statement that creates and populates the Derived table is always inside Parentheses.
A derived table will always have a SELECT query to materialize the derived table with data. The SELECT query always starts with an open parenthesis and ends with a close parenthesis. |
|
The derived table must be given a name. Above, we called our derived table TeraTom. |
|
You will need to define (alias) the columns in the derived table. Above, we allowed Dept_No to default to Dept_No, but we had to specifically alias AVG(Salary) as AVGSAL. |
Every derived table must have the three components listed above.
In the example above, TeraTom is the name we gave the Derived Table. It is mandatory that you always name the table or it errors.
AVGSAL is the name we gave to the column in our Derived Table that we call TeraTom. Our SELECT (which builds the columns) shows we are only going to have one column in our derived table and we have named that column AVGSAL.
Our example above shows the data in the derived table named TeraTom. This query allows us to see each employee and the plus or minus avg of their salary compared to the other workers in their department.
The first five columns in the Answer Set came from the Employee_Table. AVGSAL came from the derived table named TeraTom.
TeraTom |
||||
|
Dept_No |
|
AVGSAL |
|
|
? |
|
32800.50 |
|
|
10 |
|
64300.00 |
|
|
100 |
|
48850.00 |
|
|
200 |
|
44944.44 |
|
|
300 |
|
40200.00 |
|
|
400 |
|
48333.33 |
|
The derived table |
In a derived table, you will always have a SELECT query in parenthesis, and you will always name the table. You have options when aliasing the columns. As in the example above, you can let normal columns default to their current name.
When using the WITH Command, we can CREATE our Derived table before running the main query. The only issue here is that you can only have 1 WITH.
Now, the lower portion of the query refers to TeraTom Almost like it is a permanent table, but it is not!
The following example shows the simplest possible case of a query that contains a WITH clause. The WITH query named TeraTom selects all of the rows from the Student_Table. The main query, in turn, selects all of the rows from TeraTom. The TeraTom table exists only for the life of the query.
with Budget_Derived as
(SELECT Max(Budget) as Max_Budget
FROM Department_Table),
Emp_Derived as
(SELECT Dept_No, AVG(Salary) as Avg_Sal
FROM Employee_Table
GROUP BY Dept_No)
select E.*, Max_Budget – Budget as Under_Max, Avg_Sal
FROM Employee_Table as E
INNER JOIN
Emp_Derived
On E.Dept_No = Emp_Derived.Dept_No
INNER JOIN Department_Table as D
ON E.Dept_No = D.Dept_No;
“The most important thing a father can do for his children is to love their mother.”
-Anonymous
The following example shows two tables created from the With statement. "Sometimes the most important thing a WITH clause can do is to have multiple children."
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROM Employee_Table
GROUP BY Dept_No) as TeraTom(Depty, AVGSAL)
ON Dept_No = Depty ;
1) What is the name of the derived table? __________
2) How many columns are in the derived table? _______
3) What is the name of the derived table columns? ______
4) Is there more than one row in the derived table? _______
5) What common keys join the Employee and Derived? _______
6) Why were the join keys named differently? ______________
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROM Employee_Table
GROUP BY Dept_No) as TeraTom(Depty, AVGSAL)
ON Dept_No = Depty ;
1) What is the name of the derived table? TeraTom
2) How many columns are in the derived table? 2
3) What’s the name of the derived columns? Depty and AVGSAL
4) Is their more than one row in the derived table? Yes
5) What keys join the tables? Dept_No and Depty
6) Why were the join keys named differently? If both were named Dept_No, we would error unless we full qualified.
WITH T (Dept_No, AVGSAL) AS
(SELECT Dept_No, AVG(Salary) FROM Employee_Table
GROUP BY Dept_No)
SELECT T.Dept_No, First_Name, Last_Name,
AVGSAL, Counter
FROM Employee_Table as E
INNER JOIN
T
ON E.Dept_No = T.Dept_No
INNER JOIN
(SELECT Employee_No, SUM(1) OVER(PARTITION BY Dept_No
ORDER BY Dept_No, Last_Name Rows Unbounded Preceding)
FROM Employee_Table) as S (Employee_No, Counter)
ON E.Employee_No = S.Employee_No
ORDER BY T.Dept_No;