*Episode 3* MySQL (T) [ The SQL Structure ]

 

The SQL Structure

     In this episode you will learn the fundamental features of the SQL language and an overview of its programming aspect. In addition, you will be presented with a step-by-step instruction on where and how to download SQLite, a version of the SQL software that will be used all throughout the discussion of this e- Book.

 

SQL Fundamental Features :-

    SQL is a flexible computer language that you can deploy in different ways to communicate with relational databases. This software has some distinct features that differentiates it from other programming applications. First and foremost, SQL is a nonprocedural language. Most computer programs (e.g., C, C++ and Java) solve problems by following a sequence of commands that is called a procedure. In this case, one specific operation is performed after another until the required task has been accomplished. The flow of operation can either be a linear sequence or a looping one, depending on what the programmer had specified. This is not the same for SQL. In using this application, you will just have to specify the output that you want, not how you want to generate the output. From the CUSTOMER TABLE, if you want to create a separate list of contacts whose company are located in Texas then you have to retrieve the rows where the STATE column contains “TX” as its value. In writing the SQL command, you don’t have to indicate how the information should be retrieved. It is the primary role of the database management system to examine the database and decide how to generate the results you wanted.

     Learning the SQL syntax is like understanding the English language structure. Its command language, comprised of a limited number of statements, performs three primary data functions - definition, manipulation and control. The SQL programming language also includes reserved words that are only to be used for specific purposes. Thus, you cannot use these words as names for variables, tables and columns; or in any other way apart from their intended use.

     Below are some of the most common reserved words in SQL:2011.

ABS	ALL	ALLOCATE	ALTER	AND	ANY
ARE	ARRAY	AS	AT	AVG	BEGIN
BETWEEN	BINARY	BOOLEAN	BOTH	BY	CALL
CASCADED	CASE	CEILING	CHAR	CHARACTER	CHECK
CLOSE	COLLETTE	COLLECT	COLUMN	COMMIT	CONDITION
CONNECT	CONSTRAINT	CONVERT	COUNT	CREATE	CURSOR
CYCLE	DATE	DAY	DEALLOCATE	DEC	DECIMAL
DECLARE	DEFAULT	DELETE	DESCRIBE	DISCONNECT	DISTINCT
DOUBLE	DROP	DYNAMIC	EACH	ELEMENT	ELSE
END	ESCAPE	EVERY	EXCEPT	EXECUTE	EXIST
EXTERNAL	EXTRACT	FALSE	FETCH	FILTER	FLOAT
FLOOR	FOR	FOREVER	FREE	FROM	FULL
FUNCTION	FUSION	GET	GLOBAL	GRANT	GROUP
GROUPING	HAVING	HOLD	HOUR	HOURS	IDENTITY
IN	INNER	INOUT	INSERT	INT	INTEGER
INTERSECT	INTERVAL	INTO	IS	JOIN	KEEP
LANGUAGE	LARGE	LEAD	LEFT	LIKE	LOCAL
LOWER	MATCH	MAX	MEMBER	MERGE	METHOD
MINUTE	MOD	MODULE	MONTH	MULTISET	NATIONAL
NATURAL	NEW	NILL	NO	NONE	NORMALIZE
NOT	NULL	NUMERIC	OF	OFFSET	OLD
ON	ONLY	OPEN	OR	ORDER	OUT
OVER	OVERLAY	PARAMETER	PARTITION	POSITION	POWER
PRECISION	PREPARE	PRIMARY	PROCEDURE	RANGE	RANK
REAL	RECURSIVE	REF	REFERENCES	REFERENCING	RELEASE
RESULT	RETURN	REVOKE	RIGHT	ROLLBACK	ROLLUP
ROW	ROWS	SCOPE	SCROLL	SEARCH	SECOND
SELECT	SET	SIMILAR	SOME	SPECIFIC	SQL
START	STATIC	SUM	SYMMETRIC	SYSTEM	TABLE
THEN	TIME	TIMESPANT	TO	TRANSLATE	TREAT
TRIGGER	TRUNCATE	TRIM	TRUE	UNION	UNIQUE
UNKNOWN	UPDATE	UPPER	USER	USING	VALUE
VALUE	VARCHAR	VARYING	VERSION	WHEN	WHENEVER
WHERE	WINDOW	WITH	WITHIN	WITHOUT	YEAR

     If you think that an SQL database is just a collection of tables, then you are wrong. There are additional structures that need to be specified to maintain the integrity of your data, such as schemas, domains and constraints.

• Schema – This is also called the conceptual view or the complete logical view that defines the entire database structure and provides overall table organization. Such schema is considered a metadata – stored in tables and part of the database (just like tables that consist of regular data).

• Domain – This specifies the set of all finite data values you can store in a particular table column or attribute. For example, in our previous CUSTOMER TABLE the STATE column can only contain the values “TX”, “NY”, “CA” and “NV” if you only provide products and services in the states of Texas, New York, California and Nevada respectively. So these four state abbreviations are the domain of the STATE attribute.

• Constraint – Often ignored but one of the important database components, this sets down the rules that identify what data values a specific table attribute can contain. Incorporating tight constraints assures that database users only enter valid data into a particular column. Together with defined table characteristics, column constraints determine its domain. Using the same STATE column as an example with the given constraint of only the four values, if a database user enters “NJ” for New Jersey, then the entry will not be accepted. The system will not proceed until a valid value is entered for the STATE attribute, unless the database structure needs to be updated due to sudden business changes.

 SQL Command Types :-

     Before you start programming in SQL, you need to understand its basic command categories in performing various functions – database creation, object manipulation, data population and update, data deletion, query submission, access control and database administration, among others. The following are the main categories:

Data Definition Language (DDL) :-

     Data Definition Language (or simply DDL) enables you to create, change or restructure, and even destroy the basic elements that are contained in a relational database. DDL focuses only on the structure, not the data contained within the elements. These basic elements or data objects include tables, schemas, views and more. Having no independent physical existence, a view is regarded as a virtual table in which its definition only exists in the metadata. However, the view’s data comes from the table (or tables) where you will derive the view. Stated below are some of the most common DDL commands:

• CREATE – This command statement is responsible for building the database structure. Its syntax is:

CREATE TABLE

CREATE VIEW

• ALTER – This command statement is in charge of changing the database structure after it has been created. Its syntax is:

ALTER TABLE

ALTER VIEW

• DROP – This command is the reverse of the CREATE statement, which destroys the database structure. Its syntax is:

DROP TABLE

DROP VIEW

Data Manipulation Language (DML) :-

     Data Manipulation Language (or simply DML) consists of SQL commands that handle data maintenance functions. This means that you are able to manipulate the data contained within the relational database objects. The command statements, which read like normal English sentences, will allow you to enter, change, remove or retrieve data. The following are the DML statements commonly used:

• INSERT – This command statement is used to insert new data values into a certain table. To add values into a table with two columns, use the following syntax:

INSERT INTO TABLE_NAME

VALUES (‘value1’, ‘value2’);

TABLE_NAME is the name of the table where you will be adding the new values. The number of items inside the VALUES parenthesis represents the number of columns of the table, which are arranged in the same order as the said columns. If the values are of character or date/time data types, they need to be enclosed by single quotation marks. This is not required for numeric or null values (the null value should be written as NULL).

• UPDATE – This command statement is used to modify or alter pre-existing data values in a table, not add or remove records. The update is done one table at a time or multiple rows/columns of one table within a database. To change a single column, use the following syntax:

UPDATE TABLE_NAME

SET COLUMN_NAME = ‘value’

[WHERE CONDITION]; 

As long as the given WHERE clause is satisfied, then the value of the COLUMN_NAME will be updated. This could be within one or multiple records of the given TABLE_NAME.

• DELETE – This command statement deletes certain records or even the entire table, not data values from specific columns. To remove a single row or multiple records from a table, use the following syntax:

DELETE FROM TABLE_NAME

[WHERE CONDITION]; 

The WHERE clause is an important part of this command if you want to delete selected rows from the TABLE_NAME.

Data Query Language (DQL) :-

     Data Query Language (or simply DQL) consists of commands that perform data selection, which is the main focus of relational database users in the world of SQL. The statement used is SELECT that can be accompanied by other clauses or options so that your extracted results will be in an organized and readable format. You can submit a query to the database using a separate application interface or just a single command-line. The following is a syntax for a simple SELECT statement:

SELECT [* | ALL | DISTINCT COLUMN1, COLUMN2]

FROM TABLE1 [, TABLE2]; 

Using the asterisk (*) means that all columns of the given table are included in the output and will be displayed. The ALL option extracts and displays all values, even duplicates, for a column. On the other hand, using the keyword DISTINCT prevents duplicate rows from being included and displayed in the output. What follows the FROM keyword is a list of one or more tables where you want to get the data. The columns and tables specified in the syntax are all separated by commas.

Data Control Language (DCL) :-

     Data Control Language (or simply DCL) consists of commands that allow you to manage data access within the database. Furthermore, the database is protected from accidental or intentional misuse by controlling user privileges. DCL concentrates on transactions, which capture all SQL statements that perform database operations and save them in a log file. The following are the common DCL command statements:

• GRANT – This statement provides you with certain privileges, like giving you the permission to access the database. Its syntax is:

GRANT PRIVILEGE1, PRIVILEGE2, … TO

 USER_NAME

• REVOKE – This statement revokes your privileges, like removing your permission to access the database. Its syntax is:

REVOKE PRIVILEGE1, PRIVILEGE2, … TO

USER_NAME

Transactional Control Commands :-

     Transactional control commands allow users to manipulate various transactions in maintaining database integrity. In SQL, transactions begin when applications are executed. The very first transaction is started at the onset of the SQL application, while the last transaction is ended when the application is terminated. The following are the common transactional control commands:

• COMMIT – This statement completes a transaction by making the changes you made to the database permanent, or simply saving the transactions. Its syntax is:

COMMIT [WORK]; 

In the previous command line, the keyword WORK is optional.

• ROLLBACK – This statement’s primary function is to restore the database system to its previous state or undo all the actions that took place in the transaction log. Its syntax is:

ROLLBACK [WORK]; 

In the previous command line, the keyword WORK is optional.

• SAVEPOINT – This statement works with the ROLLBACK command, wherein it creates sections or points within groups of transactions in which you will be performing the ROLLBACK command. Its syntax is:

SAVEPOINT SAVEPOINT_NAME;

SQLite Installation Instructions and Database Features Before you start overwhelming yourself with various database solutions and SQL command lines, you need to determine first your purpose why you are creating a database. This will further determine other database design considerations such as size, complexity, type of machine where the application will run, storage medium and more. When you start thinking of your database requirements, you need to know up to what level of detail should be considered in your design. Too much detail will result to a very complex design that further wastes time and effort, and even your computer’s storage space. Too little will lead to a poor performing, corrupt and worthless database. Once you are done with the design phase, then you can decide which database software you can download to start your SQL experience.

     For the sake of this e-Book’s discussion, SQLite, a simple software library, will be used as a starter database engine to design, build and deploy applications. A free and stand-alone database software that is quick to download and easy to administer, SQLite was developed by Richard Hipp and his team of programmers. It is was designed so that it can be easily configured and implemented, which does not require any client-server setup at all. Thus, SQLite is considered as one of the most widely used database software applications in the world.

     Stated below are some of the major features of SQLite:

• Transactions are atomic, consistent, isolated and durable.
• Compilation is simple and easy.
• System crashes and power failures are supported.
• Full SQL implementation with a stand-alone command-line interface client.
• Code footprint is significantly small.
• Adaptable and adjustable to larger projects.
• Self-contained with no external dependencies.
• Portable and supports other platforms like Windows, Android, iOS, Mac, Solaris and more.

     In using SQLite, you need to download SQLiteStudio as your database manager and editor. With its intuitive interface, this software is very light yet fast and powerful. You don’t even need to install it, just download, unpack and run the application. Follow these simple steps in downloading SQLiteStudio on a Windows 10 computer:

     1.) Go to http://sqlitestudio.pl/?act=about. You should get the following page:

     2.) Check the version of your computer’s operating system then click the appropriate link to start downloading the software.

     3.) After downloading the software, go to the folder where the application was saved (usually the Downloads Folder in Windows). Click on the Extract tab on top then choose the Extract all option.

     4.) You will get the Extract Compressed (Zipped) Folders dialog box. Change the destination folder to C:\SQL then click the Extract button. This will be the folder where all your SQLite files will be saved.

     5.) Once all the files have been extracted, you will have the SQLiteStudio subfolder.

      6.) Find the application program named SQLiteStudio inside the subfolder. To create a shortcut on your desktop (so you can quickly launch the application), right-click the filename, select Send to option then choose Desktop (create shortcut).

     7.) When you double-click the SQLiteStudio icon on your desktop,

     8.) you should get the following screen:

      The Database Navigator (left pane) shows all the logical units of the database such as tables and views. The gray pane at the right is the SQL Work Area where you will write your query statements. You will have a better understanding of this program’s graphical user interface in the succeeding episode.

      In this episode you have learnt the fundamental features of the SQL database language, which includes program flow, syntax characteristic, reserved words list, schema, domain, constraint and more. The main categories of SQL commands were also introduced, that govern the various functions of the programming language. You were also given a guide on how to download and install SQLite and SQLiteStudio for your application software. 

👈Episode 2(MT)

Share This Post

PRINT THIS POST