100% developed

Structured Query Language/Introduction to SQL

From Wikibooks, open books for an open world
Jump to navigation Jump to search

| Relational Databases → The main drive behind a relational database is to increase accuracy by increasing the efficiency with which data is stored. For example, the names of each of the millions of people who immigrated to the United States through Ellis Island at the turn of the 20th century were recorded by hand on large sheets of paper; people from the city of London had their country of origin entered as England, or Great Britain, or United Kingdom, or U.K., or UK, or Engl., etc. Multiple ways of recording the same information leads to future confusion when there is a need to simply know how many people came from the country now known as the United Kingdom.

The modern solution to this problem is the database. A single entry is made for each country, for example, in a reference list that might be called the Country table. When someone needs to indicate the United Kingdom, he only has one choice available to him from the list: a single entry called "United Kingdom". In this example, "United Kingdom" is the unique representation of a country, and any further information about this country can use the same term from the same list to refer to the same country. For example, a list of telephone country codes and a list of European castles both need to refer to countries; by using the same Country table to provide this identical information to both of the new lists, we've established new relationships among different lists that only have one item in common: country. A relational database, therefore, is simply a collection of lists that share some common pieces of information.

Structured Query Language (SQL)

[edit | edit source]

SQL, which is an abbreviation for Structured Query Language, is a language to request data from a database, to add, update, or remove data within a database, or to manipulate the metadata of the database.

SQL is a declarative language in which the expected result or operation is given without the specific details about how to accomplish the task. The steps required to execute SQL statements are handled transparently by the SQL database. Sometimes SQL is characterized as non-procedural because procedural languages generally require the details of the operations to be specified, such as opening and closing tables, loading and searching indexes, or flushing buffers and writing data to filesystems. Therefore, SQL is considered to be designed at a higher conceptual level of operation than procedural languages because the lower level logical and physical operations aren't specified and are determined by the SQL engine or server process that executes it.

Instructions are given in the form of statements, consisting of a specific SQL statement and additional parameters and operands that apply to that statement. SQL statements and their modifiers are based upon official SQL standards and certain extensions to that each database provider implements. Commonly used statements are grouped into the following categories:

Data Query Language (DQL)
  • SELECT - Used to retrieve certain records from one or more tables.
Data Manipulation Language (DML)
  • INSERT - Used to create a record.
  • UPDATE - Used to change certain records.
  • DELETE - Used to delete certain records.
Data Definition Language (DDL)
  • CREATE - Used to create a new table, a view of a table, or other object in database.
  • ALTER - Used to modify an existing database object, such as a table.
  • DROP - Used to delete an entire table, a view of a table or other object in the database.
Data Control Language (DCL)
  • GRANT - Used to give a privilege to someone.
  • REVOKE - Used to take back privileges granted to someone.