Summary -

What is table?

Table is an organized collection of data contained in rows and columns. A table is an array of data items that are organized into rows and columns. In a database, usually data stored in the form of tables. Table has limited number of columns but can have unlimited number of rows. Table seems like a grid structure in some aspects. Table name is unique in the entire database and it identifies with its unique name in database.

Elements of table

  • Column - Column is used to store specific set of data like Date, numeric, and text. Column is related to specific part of table. Column is defined and referred using its name. Column name is used for selecting the particular column data and ordering the data. Column name should be unique. Two columns in one table will not have a same name. The same column name may use in different tables to make relation between two tables.
    In each table, at least one column should be unique. It is known as primary key. With the help of primary key, we can fetch the data uniquely from the rows. The primary key column values do not allow duplicates. We will discuss more about this in the further chapters.
  • Row – Each row of a table is an instance of the subject of the table, which is one instance of that entity. Every row contains related information of all columns. All rows in table have same structure.
  • Fields/Columns – Each column of a table contains one attribute, which is one characteristic, feature, or fact that describes the subject of the table. The value of nth field of every row in a table is the value of nth column of that row.
  • Key field - Each table has one key field that specifies each value is unique in the particular column. The key field may be used in another one or more tables, to maintain a relation between them.

Example-

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

eid ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000
002 Employee2 Director 2019-11-07 40000.00 2000
003 Employee3 Manager Employee1 2019-11-07 25000.00 1000
004 Employee4 Manager Employee2 2019-11-07 24000.00 2000
005 Employee5 Analyst Employee3 2019-10-08 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 20000.00 1000
007 Employee7 Clerk Employee3 2019-10-08 15000.00 1000
008 Employee8 Salesman Employee4 2019-10-08 15000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 15000.00 2000

Where eid, ename, designation, manager_id, date_of_hire, salary and dept_id represents column name.

eid represents primary key. It is different for every row in table. By knowing eid, user can retrieve every row data individually.

001, employee1, director, , 209-11-07, 45000.00, 1000 represents as row, because it has all columns single values.

001, 002, 003, 004, 005, 006, 007, 008, 009 represents the single column values of employee_details table.

What are the operations can perform on tables in Database using SQL?

SQL can perform various operations on tables in database that includes creating, renaming, deleting, updating and many more. The mostly performed Operations on tables are listed as shown below -

  • CREATE Table
  • DROP Table
  • RENAME Table
  • ALTER Table
  • TRUNCATE Table
  • DELETE Table

We will discuss the each one detailed in the further chapters.