SQL: Data Definition Language

Structured Query Language (SQL) commands are instructions written that instruct the database on the actions it should perform. Data storage is the primary function of a database; therefore, SQL commands provide mechanisms to facilitate storage and retrieval of data when required. The SQL commands can be roughly classified into three categories depending on their functionalities (Oppel & Sheldon, 2009). The first category of SQL code is responsible for creating the database environment where data will be stored and is known as data definition language (DDL). The commands in DDL include those that are used in creating database objects such as tables and views. The other category is known as data manipulation language (DML). SQL commands DML are used for manipulation of data objects in the database. The last category is known as data control language (DCL) which provide functionalities that allow for allocation of access rights to various database features. There are other minor categories which are derived from the two DML and DDL queries comprising of SQL relational set which are complex and advanced.

Data Definition Language

DDL is a subset of the SQL which that is crucial in creating the database structure and design as it is responsible for the logical arrangement and the actual storage of data (Bhattacharya, 2016). The key statements in DDL include CREATE, ALTER, and DROP. The CREATE command is used in the creation of schemas and table. Sample code for creating a table is: CREATE TABLE table_name. DROP command is used for deleting the whole database and sample code is: DROP TABLE table_name. While working with the database, one may need to change the structure of the databases such table name. The ALTER statement is used for this purpose. The following code will show how you modify the structure of the database by changing table name: ALTER TABLE table_name RENAME TO new_table_name.

Data Manipulation Language

DML is instructions set responsible for manipulating data in tables which may include performing actions related to retrieval, modification, addition and deleting of data objects in the database.  Some of SQL statement that offers these functionalities are the SELECT, INSERT, UPDATE, and DELETE commands. The name of the statements are self-explanatory, and their implementation depends on the type of database management system used.

Data Control Language

As the name suggests, this are SQL commands that provide access control of database to users or applications. They are crucial in protecting the integrity of data stored as they allow for confidentiality and protection of information in the database. The SQL commands that provide strict access are GRANT to give access and REVOKE to deny access to various objects in the database.

Advanced SQL

The individual SQL statements are crucial but are not that powerful on their own. That where relational algebra comes in with relational set operators such as UNION and INTERSECT. These relational set operators combine different SQL statements using logical operation to make maximum use of DBMS capabilities.

Union

UNION operator combines two or more rows from different table to form a new table while that exclude duplicate rows found in both sets. However for UNION to work the tables used must be compatible with each other. Compatibility in database tables means that individual tables should have the same attributes and characteristics which means they should have equal numbers of columns. Such tables are known as union-compatible.

The table below show use of UNION

      UNION          

                        =             

The following code will help get the above results:

SELECT P_CODE, SUBJECT FROM SCIENCE

UNION

SELECT P_CODE, SUBJECT FROM LANGUAGES;

The UNION operator in SQL uses the same approach as that of mathematics to ensure that there is no duplication of rows. When the query is executed only those rows that are in both tables will be included. However, when the same row are in both sets, only one copy will be included.

Intersect

As with UNION, INTERSECT command allows for database operation in more than one table. INTERSECT command combines rows from different tables including only those that are in both sets. Just as with UNION compatibility is a must for this operation to succeed. The table below show use of UNION

      INTERSECT    

                                    =      

The code to get the above result is shown below

SELECT P_CODE, SUBJECT FROM SCIENCE

INTERSECT

SELECT P_CODE, SUBJECT FROM TUTORS;

The INTERSECT operator is essential in sorting as it allows for selection of information that is common in two tables.

Except/Minus

EXCEPT command is another statement that is used to join rows in two tables but only returns the data that is in the first set and not the second. The table below demonstrates the concept of EXCEPT.

    EXCEPT     

                        =     

The implementation of EXCEPT depends on the DBMS vendor. In the above example, one may be interested in finding subjects that are in the second table and not in the first. They can use the code below.

SELECT P_CODE, SUBJECT FROM TUTORS MINUS

SELECT P_CODE, SUBJECT FROM SCIENCE;

Since the implementation might be different from other vendors, offer alternative syntax which is known as IN and NOT IN but produces the same results as EXCEPT (minus).

Join

Apart from the above ways of joining rows, there is also another command known as JOIN that can accomplish almost similar results. JOIN is classified into two. The inner join statement which selects rows that meet a specific requirement. If the condition for selection is the equality of both rows, the join is known as a natural join. The other JOIN is called outer join which returns the matching rows and joins those that are unmatched to form a new table Coronel & Morris, 2016).

References

Bhattacharya, J. (2016). Rudiments of Modern Computer Application Part II. Academic

Publishers.

Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.  

Cengage Learning.

Oppel, A., & Sheldon, R. (2008). SQL: A BEGINNER'S GUIDE 3/E. McGraw-Hill, Inc.