GBB6Q

              Sams Teach Yourself SQL in 10 Minutes
              by Pearson Education, Inc.

CRUCIFIXION
===========

         DDL         Read this:
          ^
          I          DML, DDL, DCL and TCL Statements in SQL with ..
  DML < - + - > DCL
          I          http://www.tech-recipes.com/rx/55356
          I          /dml-ddl-dcl-and-tcl-statements-in-sql-
          v          with-examples/
         TCL

However, it's a matter of taste, as given crux can easily be converted into a pentacle by separating SELECT from DML into DRL (Data Retrieval Language).

First we create structure for our data -- this is what we call SQL database schema (Objects: database, table, view, cursor, index, trigger, stored procedure, query, comment. Relations: primary and foreign keys, constraints)

Let's spend some time to satisfy several tastes on how to create schema.

  -- Ortodoxal: terminal, ed text editor.

  -- Visual: wwwsqldesigner (https://github.com/ondras/wwwsqldesigner), wine + MS Access 2003, etc..

  -- Mixed: See review of different "Tools to visualize database schemas" at

    http://www.codediesel.com/data/5-tools-to-visualize-database-schemas/

Schema Visualization
--------------------

SchemaSpy
---------

  # apt-get install graphviz
  $ wget -c https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.40.tar.gz
  $ tar xf mysql-connector-java-5.1.40.tar.gz

Download and extract

  1. schemaspy-gui from
       http://www.joachim-uhl.de/projekte/schemaspygui/
  2. schemaspy from
       http://schemaspy.sourceforge.net/
  3. mysql connector driver from
       https://dev.mysql.com/downloads/connector/j/

Run GUI as ./schemaSpyGUI.sh, fill the Parameters/Flags Tab:

  DB Type: mysql
  DB Host: localhost   DB Port: 3306
  DB Name: school
  DB User: root        Password: root

  Path to DB Driver:
    /home/virtu/Downloads/mysql-connector-java-5.1.40-bin.jar
  Path to SchemaSpy:
    /home/virtu/schemaSpy/schemaSpy_5.0.0.jar
  Path properties file:
    /home/virtu/schemaSpy/properties/mysql.properties

Now press the button [Start SchemaSpy]. Results in browser.

wwwsqldesigner
--------------

  $ git clone https://github.com/ondras/wwwsqldesigner
  # cp -R wwwsqldesigner /var/www/html/

Now read  this:

  http://stackoverflow.com/questions/117776
       /loading-an-existing-database-into-www-sql-designer

>> ..I know this is really old stuff, but I found the solution
>> (if people are looking for it) : you have to edit the file
>>
>>   backend/php-mysql/index.php
>>
>> and fill the connection settings. All you need is then to click on
>> Import From DB, and then Load with the name of your database.
>>
>>   function setup_import() {
>>       define("SERVER","localhost");
>>       define("USER","");
>>       define("PASSWORD","");
>>       define("DB","information_schema");
>>   }

More
----

  1. This takes a SQL structure (SQL dump) and shows a diagram
      https://code.google.com/archive/p/database-diagram/

Annex A. Installing Oracle Java on Ubuntu 16.10
-----------------------------------------------

  https://www.digitalocean.com/community/tutorials
  /how-to-install-java-with-apt-get-on-ubuntu-16-04

  http://tipsonubuntu.com/2016/07/31
  /install-oracle-java-8-9-ubuntu-16-04-linux-mint-18/

  # add-apt-repository ppa:webupd8team/java
  # apt update
  # apt install oracle-java<V*>-installer
  # find /usr -name "java-<V>-oracle"
  # vi /etc/environment
  JAVA_HOME="/usr/lib/jvm/java-<V>-oracle"
  # source /etc/environment  # reload environment
  # echo $JAVA_HOME          # .. and test

  *) V -- 6,7,8,9

Annex B. Sample Databases
-------------------------

  https://github.com/datacharmer/test_db
  http://sportsdb.org/modules/sd/assets/downloads
       /sportsdb_sample_mysql.zip

Review:

  https://www.ntu.edu.sg/home/ehchua/programming/sql/SampleDatabases.html

Actual Stuff:

  http://dev.mysql.com/doc/employee/en/index.html
  http://dev.mysql.com/doc/sakila/en/index.html
  http://code.google.com/p/northwindextended
  http://www.mysqltutorial.org/

May work:

  $ cat getdb.sh

` #!/bin/sh
` wget -c https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/northwindextended/Northwind.MySQL5.sql
` wget -c https://raw.githubusercontent.com/datacharmer/test_db/master/sakila/sakila-mv-data.sql
https://raw.githubusercontent.com/datacharmer/test_db/master/sakila/sakila-mv-schema.sql
` wget -c https://raw.githubusercontent.com/datacharmer/test_db/master/employees.sql
` wget -c http://www.mysqltutorial.org/wp-content/uploads/downloads/2016/05/mysqlsampledatabase.zip
` unzip mysqlsampledatabase.zip
` rm mysqlsampledatabase.zip

Annex C. DML, DDL, DCL and TCL Statements in SQL with ..
--------------------------------------------------------

Posted May 28, 2015 by Vishwanath Dalvi

SQL language is divided into four types of primary language statements: DML, DDL, DCL and TCL. Using these statements, we can define the structure of a database by creating and altering database objects, and we can manipulate data in a table through updates or deletions. We also can control which user can read/write data or manage transactions to create a single unit of work.

The four main categories of SQL statements are as follows:

  1. DML (Data Manipulation Language)
  2. DDL (Data Definition Language)
  3. DCL (Data Control Language)
  4. TCL (Transaction Control Language)

DML (Data Manipulation Language)

DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.

DML statements include the following:

  SELECT* -- select records from a table
  INSERT  -- insert new records
  UPDATE  -- update/Modify existing records
  DELETE  -- delete existing records

*) Gaurav Pandey:
   SELECT do not comes under DML as its requires explicit commit to the transaction; it comes under DRL

DDL (Data Definition Language)

DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.

  CREATE -- create a new Table, database, schema
  ALTER  -- alter existing table, column description
  DROP   -- delete existing objects from database

DCL (Data Control Language)

DCL statements control the level of access that users have on database objects.

  GRANT -- allows users to read/write on certain database objects
  REVOKE -- keeps users from read/write permission on database objects

TCL (Transaction Control Language)

TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.

  BEGIN Transaction    -- opens a transaction
  COMMIT Transaction   -- commits a transaction
  ROLLBACK Transaction -- ROLLBACK a transaction in case of any error
