SQLite - SPLessons

SQLite Tables

Chapter 7

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQLite Tables

SQLite Tables

shape Description

Logical stockpiling containers for segments are called as SQLite Tables. The database objects are segments, like index and tables, and the storage space that it consumes. At the physical stage, information in one or more data files will be stored in the table space.

Permanent table space

shape Description

Persistent schema objects are permanent table space groups. The SQLite Tables Space contains segments for objects that are stored in the information files.

A default permanent table space is assigned to each client in the database. The default SYSTEM and SYSAUX table spaces are used only by small databases. Hence, at least one table space for application data and to store user will be created by Oracle. By using the table spaces succeeding goals can be accomplished.

  • Allots an amount to a database clients.
  • Control disk space designation for database information.
  • Make a transportable table space that can be duplicated or moved from one database to another, even across the stages.
  • Perform reinforcement and recuperation of single tables paces.
  • Take individual table spaces online or offline without affecting the availability of the whole database.
  • Import or fare application information by utilizing the Oracle Data Pump utility.

Creating SQLite Tables

shape Description

By using create command new table can be defined in the SQLite Database Server and the table name can be any character followed by size. Create command will access the create privilege for the database. And the table name should be unique in the database.

shape Syntax

Create Table database_name.table_name(<column1>datatype primary key,<column2> datatype,<column3> datatype,…..<columnN> datatype);

table_name => Any accurate table.

column_name => The operation that can be performed on a column in the table.

shape Examples

By viewing the below example, new table can be created by using create  command.

sqlite> create table employee12(emp_id int,name varchar(10),salary int);
Table successfully created.

In the above example a table employee12 is defined with column names emp_id,name and salary.

Drop table

shape Description

Drop command can be performed on existing database The following are the actions performed by drop command. Dropping a column from the table and dropping a table from the database.

shape Syntax

DROP TABLE database_name.table_name;

Drop table => Is a DDL command.

table name => Name of the table in the database server.

shape Examples

The below example describe the dropping of a table.

sqlite> .tables
dept24      employee12
sqlite> Drop table dept24;
sqlite> .tables
employee12

In the above example, table dept24 has been dropped from the SQLite database.

Insert query

shape Description

By using insert command new record can be inserted in the existing database table. i.e, inserting field value like emp_id,emp_name and salary can be inserted.

shape Syntax

Insert into <table_name> values(<value1>,<value2>,<value3>…….);

table name =>The accurate table.

values => values are the column values.

shape Examples

By viewing the below example, the concept of insert command can be understood easily.

sqlite> create table employee12(emp_id int,name varchar(10),salary int);
sqlite> insert into employee12 values(10,'Mike',25000);
sqlite> insert into employee12 values(20,'Malik',20000);
sqlite> insert into employee12 values(30,'Nalek',30000);
sqlite> select * from employee12;
10|Mike|25000
20|Malik|20000
30|Nalek|30000

The above example tells that, when insert command is performed on the table employee24 then it will accept the values and stores in the table database.

Select query

shape Description

Select proclamation is utilized to retrieve information from the SQLite database table and gives the output in the form of tables.And these accumulation of tables is called as yield sets.

shape Syntax

Select <column_name1>,<column_name2>,…….<column_nameN> from table_name;

Select => Which retrieves the entire list from the database.

Column_name => The operation that  can be performed on a column in the table.

shape Examples

By viewing the below example, the concept of select command can be understand easily.

sqlite> select * from employee12;
10|Mike|25000
20|Malik|20000
30|Nalek|30000
sqlite> select emp_id,salary from employee12;
10|25000
20|20000
30|30000

The above example tells that, when select command is performed on the table employee12 then it will display all the employee details from the database.

Summary

shape Key Points

  • Table – Is a logical stockpiling container.
  • Permanent table space – Contain object and schemas.
  • Create table – Is a DDL statements which are used for creating tables in the database.
  • Drop table – Is used to drop the existing table object from the database.
  • Insert query – Is a DML statement which is used to insert the values insides the database tables.
  • Select query – Is used to retrieve the data from the database server.