Understanding SQL
What is SQL
SQL stands for Structured Query Language. Structured Query Language, or SQL, is a special-purpose programming language used to manage data within a relational database mangement system (RDMS).
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 SQL language is standardized with specific extensions for each database management software.
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.
Database
A database is a collection of information that is organized so that it can be easily accessed, managed and updated. Data is organized into rows, columns and tables, and it is indexed to make it easier to find relevant information.
Table
Data in the database is stored in a table. It is comprised of rows and columns. A table is usually used to store unique information e.g a customer’s table, purchases, sales, student information and so on.
Data stored in the table is of one type. You would never store a list of customers and a list of orders in the same database table. Doing so would make subsequent retrieval and access difficult. Rather, you’d create two tables, one for each list. A join would be used to relate the two datasets.
Customer’s table
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Schema
Tables have characteristics and properties that define how data is stored in them. These include information about what data may be stored, how it is broken up, how individual pieces of information are named, and much more. This set of information that describes a table is known as a schema
Column
A column is used to store a single attribute or field in a table. E.g in a Customer’s table, one column would store the customer first name, and another the customer last name. Each column is associated with a data type, e.g the customer name is a set of characters, and the customer’s age is a number. The data type will restrict the type of data entered into that column.
Row
A row in the database table represents a record. E.g. in table the complete information about a customer will be a row.
Primary Key
A primary key is the column or set of columns that uniquely identifies a row in a table.