FairCom Control Center SQL tutorials

SQL tutorials for the FairCom Control Center

These tutorials show how to perform actions in FairCom's Control Center using SQL commands and the user interface.

Requirements:

 

Create a table

Both Create table procedures will create a table named "athlete". You can use either of these procedures to create this table.

 

Create a table using FairCom's Control Center

  1. Start the FairCom Control Center.
  2. Navigate to the Data tab.
  3. Select Tables from the Data menu.
  4. Click the Add Table button in the top right corner.
  5. Fill out the table information. For this tutorial, name the table "athlete".
  6. Add at least one field by defining the name and value and clicking the Add Field button. 
  7. Click the Save button in the bottom right corner.

 

Create a table using SQL statements

  1. Start the FairCom Control Center.
  2. Navigate to the Data tab
  3. Select SQL Commands from the Data menu.
  4. Paste this code into the SQL Script Request editor:
    1. create table "admin"."athlete" (
      	"id" bigint identity (1,1),
      	"name" varchar(30),
      	"ranking" smallint not null,
      	"birthdate" date,
      	"playernumber" numeric(32,6),
      	"livedpast2000" bit,
      	"earnings" money(32),
      	"favoritesaying" varchar(500)
      );
  5. Click the Run button.
  6. Observe the Script results in the Execution Log.
  7. Select Tables from the Data menu.
  8. Observe the new table in the Tables list.

 

Insert records in a table

Insert records in a table using FairCom's Control Center

  1. Navigate to the Data tab.
  2. Select Tables from the Data menu.
  3. Click on the table name. For this tutorial, click the "athlete" table.
  4. Click the Add Record record button on the right side.
  5. Fill out the new record by defining the values of each field.
  6. Click the Add Record record button at the bottom of the window.
  7. Observe your new record in the table.  

 

Insert records in a table using SQL statements

  1. Navigate to the Data tab
  2. Select SQL Commands from the Data menu.
  3. Paste this code into the SQL Script Request editor:
    1. INSERT INTO "admin"."athlete" VALUES
        ('Michael Jordan', '1', '02/17/1963', '23', 1, '1700000000', 'There is no i in team but there is in win.'),
        ('Babe Ruth', '2', '02/06/1895', '3', 0, '800000', 'Every strike brings me closer to the next home run.'),
        ('Muhammed Ali', '3', '01/17/1942', '1', 1, '60000000', 'Float like a butterfly, sting like a bee.'),
        ('Pele', '4', '10/23/1940', '10', 1, '115000000', 'Everything is practice.'),
        ('Wayne Gretzky', '5', '01/26/1961', '99', 1, '1720000', 'You miss 100 percent of the shots you never take.'),
        ('Michael Schumacher', '6', '01/03/1969', '1', 1, '990000000', 'Once something is a passion, the motivation is there.');
  4. Click the Run button.
  5. Observe the Script results in the Execution Log.

 

Create an index on the "earnings" field

Create an index using FairCom's Control Center

  1. Navigate to the Data tab.
  2. Select Tables from the Data menu.
  3. Click on the table name. For this tutorial, click the "athlete" table.
  4. Click the Configuration button.
  5. Select the Indexes tab. 
  6. Click the Add Index button on the right side.
  7. Fill out the Index Information including the Index Name and Index Keys.
  8. Click the Save and Close button. 
  9. Observe the new index.

 

Create an index using SQL statements

  1. Navigate to the Data tab
  2. Select SQL Commands from the Data menu.
  3. Paste this code into the SQL Script Request editor:
    1. create index "admin"."athlete_earnings_idx" on "admin"."athlete" ("earnings");
  4. Click the Run button.
  5. Observe the Script results in the Execution Log.
  6. Select Tables from the Data menu.
  7. Click on the table name. For this tutorial, click the "athlete" table.
  8. Click the Configuration button.
  9. Select the Indexes tab. 
  10. Observe the new index.

 

View records

View records using FairCom's Control Center

  1. Navigate to the Data tab.
  2. Select Tables from the Data menu.
  3. Click on the table name. For this tutorial, click the "athlete" table.
  4. Observe the records. 

 

View records using SQL statements

  1. Navigate to the Data tab
  2. Select SQL Commands from the Data menu.
  3. Paste this code into the SQL Script Request editor:
    1. SELECT * FROM athlete;
  4. Click the Run button.
  5. Observe the Script results in the Execution Log.

 

Drop a table from a list

Both Drop table procedures will drop a table from a list. You can use either of these procedures to drop a table.

 

Drop a table using FairCom's Control Center

  1. Navigate to the Data tab.
  2. Select Tables from the Data menu.
  3. Click the three dot menu and click the Delete button.

 

Drop a table using a SQL statement

  1. Navigate to the Data tab
  2. Select SQL Commands from the Data menu.
  3. Paste this code into the SQL Script Request editor:
    1. DROP TABLE athlete;
  4. Click the Run button.
  5. Observe the Script results in the Execution Log.
  6. Select Tables from the Data menu.
  7. Observe that the athlete table is no longer listed.

 

Clone a table using FairCom's Control Center

  1. Navigate to the Data tab.
  2. Select Tables from the Data menu.
  3. Click the three dot menu and click the Duplicate button.
  4. Edit the table information for the new Copy of the table.
  5. Click the Save button.