SQL-Delete Drop Scripts

Let me introduce you to the SQL-Delete Drop Scripts.

DROP DATABASE

Just that simple.

and likewise its that simple for the Table.

Except, you need to use the correct name, right?

DROP DATABASE database-name;

and to really make your system robust, and to error proof your mindset, you should check to see if it already is in place.

DROP DATABASE IF EXISTS database-name;

and likewise out create table is thus;

DROP TABLE IF EXISTS table-name;

So there you have it, the SQL-Delete Drop Scripts. Soon I will share the rest of the broad strokes of working with Structured Query Language aka SQL. Next up is Exploring Scripts.

USING SQL

  1. Creating database tables (check)
  2. Inserting data into tables
  3. Retrieving data from tables
  4. Sorting retrieved data
  5. Simple data filtering
  6. Complex data filtering
  7. Generating calculated fields
  8. Manipulating data
  9. Grouping table data
  10. Making complex queries
  11. Joining database tables

 
BTW, here is what a good SQL-Delete Drop Scripts package looks like:

# Use the "my_database" database
 USE my_database;

# create a table called "cats" with 2 columns
 CREATE TABLE IF NOT EXISTS cats (
      id INT,
      breed TEXT
 );

# show that the table has been created
 SHOW TABLES;

# confirm the "cats" table format
 EXPLAIN cats;

# delete the "cats" and "cars" tables
 DROP TABLE IF EXISTS cats, cars;

# show that the tables have been deleted
 SHOW TABLES;

SQL-Create Scripts

I could have broken up the scripts into Database and then Table scripts; like create and delete, but I went with Create first. Let me introduce you to the SQL-Create Scripts.

CREATE DATABASE

Just that simple.

and likewise its that simple for the Table.

Except, you want to name it, correct?

CREATE DATABASE database-name;

and to really make your system robust, and to error proof your mindset, you should check to see if it already is in place.

CREATE DATABASE IF NOT EXISTS database-name;

and likewise out create table is thus;

CREATE TABLE IF NOT EXISTS table-name;

So there you have it, the SQL-Create Scripts. Soon I will share the rest of the broad strokes of working with Structured Query Language aka SQL. Next up is DELETE Scripts

USING SQL

  1. Creating database tables (check)
  2. Inserting data into tables
  3. Retrieving data from tables
  4. Sorting retrieved data
  5. Simple data filtering
  6. Complex data filtering
  7. Generating calculated fields
  8. Manipulating data
  9. Grouping table data
  10. Making complex queries
  11. Joining database tables

 
BTW, here is what a good SQL-Create Scripts package looks like:

# Reveal existing databases
SHOW DATABASES;

/* Create a new database called "my_database" only if
a database does not already exist with that name  */

CREATE DATABASE IF NOT EXISTS my_database;

-- Reveal all databases
SHOW DATABASES;

Writing SQL

Whatever kind of programming you do, web development, C++, C#, Java, PHP, it will include writing SQL. All interacting with the database is done with a query. Create this, delete that, what is this value, how many of these are there, add this to that, are all forms of queries. Which can be written with nothing more than a text editor, like notepad.  You will learn to not just write a query, but whole scripts. You will also need to test if the query gives you the results you were seeking. 

Dr. Chandra, shall we start?

One of the first queries in writing SQL you will want to is the show command.

SHOW DATABASES;

And another is adding comments to your scripts. Comments come in single line, or multi-line style. Add # as a way of starting or documenting a section. Whereas I have always just used a dash dash — as a way of making a small programing note. The more formal way, which should be used for multi-line is starting with a /* and ending with a */

Here is an example of writing SQL in script form;

# Reveal existing databases
SHOW DATABASES;

/* Create a new database called "my_database" only if
a database does not already exist with that name  */

CREATE DATABASE IF NOT EXISTS my_database;

-- Reveal all databases
SHOW DATABASES;

Lets continue with telling your database tool which database to use

USE database-name;

which you should then follow up with a show tables command.

SHOW TABLES;

Another command that is helpful is the explain command

EXPLAIN table-name;

Hopefully this can get you started with writing SQL. I suppose I should write about data types, field modifiers, primary keys, and relationships or leave them for another post. So for now back to USING SQL.

Another good location to learn from, and use an online editor is the W3Schools.

Learning standard SQL ~ Structured Query Language

Learning standard Structured Query Language or ANSI-SQL enables you to interact with almost every major database out there. There are many companies who make a database product that uses SQL as an interface, and each implements their own feel and flavor on it. Such as Microsoft calls their T-SQL and Oracle calls theirs PL-SQL. In order to better understand SQL, so it could be implemented universally, aspects of SQL were defined in the late 80’s by the American National Standards Institute (ANSI) in a standard specification known as SQL-89.
This was expanded three years later with publication of the SQL-92 specification by a joint committee of ANSI and the International Standards Organization (ISO). A third standard specification, SQL-99, was introduced in 1999 to address issues of advanced SQL syntax and has been subsequently updated with the SQL:2008 standard. So my take on it all is learn SQL, and then remember once you are working on a certain flavor, to create a cheat-sheet of how this one is different. How you go about learning standard Structured Query Language is up to you.

SQL Queries

There are lots of ways that SQL queries may be sent to a database. Here is a list of some

  • Directly input through an integral SQL-client application that is part of the DBMS package – this is generally the most straightforward method.
  • Input through a third-party SQL-client application – this method communicates with the database via an intermediate software “driver”. On Windows systems these are typically Open DataBase Connectivity (ODBC) data source drivers. There is also each programing language version, like JDBC for Java.
  • From a script – often found on web servers to dynamically communicate with a database using a scripting language such as PERL or PHP. PERL scripts are still around, having been very popular back in the early web days when the CGI-BIN ruled the world.
  • From an Integrated Development Environment (IDE) – programmers using IDEs, such as Microsoft Visual Basic, can build programs that incorporate SQL queries to a database

The cool thing is you can install MySQL on your system for free, along with a Microsoft SQL trial. The Microsoft Query tool can be used to make SQL queries to a database. It is installed on many Windows systems without the
user even knowing because it is included with other Microsoft products such as the Excel spreadsheet program in Office. So while you are working on learning SQL find a tool-set that works for you. Best wishes on your journey towards learning standard Structured Query Language

Another good location to learn from, and use an online editor is the W3Schools

Types of Databases

I want to briefly explain a little about each of the different types of databases I have used over the years.

Access is the popular database program supplied as part of the Microsoft Office suite. It is popular with Office users who understand that Excel is not a database. The SQL View allows you to enter SQL queries to be executed when you click the !Run button. Excel is a good spreadsheet tool, which can use data directly out of Access, or SQL server.

Microsoft has several popular SQL Server databases for systems running Windows, typically called MS-SQL Server, or just SQL Server. SQL Server Express is a free, lightweight and feature-rich database for data-enabled web and Windows applications. SQL queries can be executed from the SQL Server Management Studio. Visual Studio can be used to create computer programs that make queries against a database via an ODBC Data Source. Simply select the Connect to Database item on the Tools menu to launch the Add Connections dialog, then choose a data source from the “Use user or system data source name” drop-down list. SQL queries can then be made from the program code.

IBM also has several databases. I first came across IBM’s DB2 DBMS, a powerful multi-platform database system, when I was at College Fund. DB2-Express is a free full function DB2 data server. SQL queries can be executed from its db2 => command prompt. I had installed the basic DB2 on my windows development system when I was getting ready to work with WebSphere. Then I went to my boss, who was in charge of Data Processing, the old-time guys on AS/400, and told him, I think we need to install DB2 onto the main server, our AS/400. Ya, that was funny, he laughed, and helped me understand, the AS/400 IS a box built around DB2/400, with an operating system. I knew they had used RPG as a language, not understanding that RPG is a report programing language to because the AS/400 is nothing more than a super fast batch processing report generating computer. All the data, and some rules, and these guys had created an amazing insurance management program.

Following College Fund and our transformation to WebSphere, I later moved over to run servers for ADP, and first learned a little about Oracle DBMS, popular and widely used in commerce. Oracle Database Express Edition (Oracle Database XE) is a free entry-level, small-footprint database that is simple to administer. SQL queries can be executed from its SQL> command prompt. Don’t ask me how I once crashed a live FEMA database, quite by accident. I have worked with 9i, and 10g, and have no current idea about the newest latest and greatest Oracle changes.

I have since figured out, that companies build with certain stack features. Such as if your a Visual Studio shop with ASP.NET and C#, then most likely you have a SQL Server behind it. Likewise if your a Java shop, then you most likely have Oracle in your stack.  My next stack I work with is MySQL with works well with PHP. Most Content Management Systems(CMS) like WordPress, Joomla, and Drupal use this PHP/MySQL stack. SharePoint, being Microsoft’s CMS product is SQL Server based.

The world’s most popular open-source database server is the freely available MySQL DBMS product that is supplied with an integral SQL-client from which to execute SQL queries from its mysql> command prompt. MySQL is used throughout this book to demonstrate the SQL language. You can install MySQL on both Windows and Linux platforms. The common term LAMP stack stands for Linux, the operating system, Apache, the web server, MySQL is the database, and PHP.

Well that is my short, in a nutshell story of the types of databases and full-stacks I have used over the years.