sql

SQL is one kind of DBMS (Database Management System). For the purpose of data analyze, database (DB) is a collection of data stored in some organized fashion. The simplest way to think of it is to imagine a database as a container. The container is simply a physical location to store data, regardless of what that data is or how it is organized. SQL means Structured Query Language which designed specifically for communicating with databases.

Basic concepts of DB

1. Table

When we store data in a container, we first need to create files within the container, then we put related data into specific files, which is called “Table”. Every table in a database has a name that identifies it. That name is always unique.

We usually create a Schema file to store characteristics and properties that define how data is stored in tables. These include information about what data may be stored, how it is broken up, how individual pieces of information are named, and much more. Normally, schemas are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any).

2. Rows,Columns and Datatypes Field

Columns are different fields in a table. All tables are made up of one or more columns. Data in a table is stored in rows; each record saved is stored in its own row.

Tip:
It is extremely important to break data into multiple columns correctly. For example, city, state, and ZIP code should always be separate columns. By breaking these out, it becomes possible to sort or filter data by specific columns (for example, to find all customers in a particular state or in a particular city).

Each column in a database has an associated datatype. A datatype defines what type of data the column can contain. Datatypes also help sort data correctly and play an important role in optimizing disk usage. As such, special attention must be given to picking the right datatype when tables are created.

3. Primary keys

Primary key is a column (or set of columns) whose values uniquely identify every row in a table.

Tip:
Although primary keys are not actually required, most database designers ensure that every table they create has a primary key so that future data manipulation is possible and manageable.

Any column in a table can be established as the primary key, as long as it meets the following conditions:

  • No two rows can have the same primary key value.
  • Every row must have a primary key value. (Primary key columns may not allow NULL values.)
  • Values in primary key columns should never be modified or updated.
  • Primary key values should never be reused. (If a row is deleted from the table, its primary key may not be assigned to any new rows in the future.)

Multiple columns may be used together as a primary key. When multiple columns are used, the rules listed above must apply to all columns, and the values of all columns together must be unique (individual columns need not have unique values)

Retrieving data with SQL

Single column:
select column_name from table
Multiple columns:
select column_1, column_2, column_3 from table
All columns:
select * from table
Retrieving Distinct values in a Column (return only distinct (different) values.):
select distinct column_name from table
Limiting retrieved results:
select prod_name from Products limit 5
select prod_name from Products limit 5 offset 3

Sorting Retrieved Data

Sorting data of a certain column:
select column_name from table order by column_name
Sorting multiple columns:
select column_1, column_2, column_3 from table order by column_2, column_3
(if values in column_2 are equal, then sort column_3)
Sorting by column position:
select column_1, column_2, column_3 from table order by 2, 3
here order by 2, 3 means sort by column_2 and then by column_3. The result is same as the one given by the command above.

Specifying Sort Direction

The default sort order is ascending (from A to Z). To sort by descending order, the keyword DESC must be specified.
select column_1, column_2, column_3 from table order by column_2 desc, column_3 desc

REF: SQL in 10 mins by Ben Forta

visitors