Database Tutorial

A DBMS uses programs to manage its databases and tables, and many other structures that help make the data easier to create and access. All DBMS engines provide a SQL (Structured Query Language) implementation that lets you manage databases and data. Each implementation of a SQL is a dialect, which can be like speaking a language with an accent.

SQL should contain five major groups of commands:

• Data Definition Language (DDL):
  • The CREATE statement lets you create new objects in a database. The CREATE statement writes the information for new objects to the data dictionary. The CREATE statement also can leverage default storage clauses that govern the organization of the object inside the physical space of database files.
  • The ALTER statement lets you modify the structure of existing objects in a database.
  • The DROP statement lets you remove the structure of existing objects from the database provided there are no dependent object types.
  • The RENAME statement lets you rename a structure in the database. You can only rename objects with names not already found in the schema or database.
  • The TRUNCATE statement lets you remove the data from a table without writing the changes to a logging file.
  • The COMMENT statement lets you add text comments to a table or columns in a table.
• Data Manipulation Language (DML): Commands that let you transact with data by allowing you to INSERT, UPDATE, MERGE, and DELETE rows from tables.
• Data Control Language (DCL): Commands that let you give and remove privileges to work with databases and tables, like the GRANT and REVOKE commands.
• Transaction Control Language (TCL): Commands that let you group activities into transactional units, which are necessary when you want a set of things to occur without fail or fail together. The three commands are: COMMIT (or save changes), ROLLBACK (or undo changes), and SAVEPOINT (or a bookmark that sets where to start the changes).

Some SQL dialects are imperfect and the vendor may do little to change that over many years, like Microsoft SQL Server. Microsoft SQL Server has repeatedly failed to add SQL commands for certain basic operations; and as a result you have stored programming commands, like the sp_rename, sp_renamedb, and so forth. These commands take the place of ANSI-compliant SQL Data Definition Language (DDL) commands.