SQLITESTUDIO QUERY EXAMPLES INSTALL
SQLITESTUDIO QUERY EXAMPLES UPDATE

The following example creates a view called “empdept”, which combines fields from both employee and dept table.

SQLITESTUDIO QUERY EXAMPLES UPDATE

# sqlite3 company.db update employee set title='Sales Manager' where empid=104 Update employee set updatedon = datetime('NOW') where rowid = new.rowid Ĭreate the trigger on the company.db database as shown below. # vi employee_update_trg.sqlĬreate trigger employee_update_trg after update on employee The following trigger will update the “updatedon” date column with the current timestamp whenever you perform an update on this table. Next, create a file that has the trigger definition. sqlite> alter table employee add column updatedon date Create a Triggerįor this example, first add a date column called “updatedon” on employee table. sqlite> insert into employee values (101,'James Bond','Secret Agent',1) Įrror: constraint failed 8. Once an unique index is created, if you try to add another record with an empid that already exists, you’ll get an error as shown below. sqlite> create unique index empidx on employee(empid) The following example creates an unique index called empidx on the empid field of employee table. The folowing example shows that there are two tables in the current database. View all Tables in a DatabaseĮxecute the following command to view all the tables in the current database. sqlite> select * from employee ġ05|Rita Patel|DBA|2 6. Verify that the deptid is updated properly in the employee table. Update employee set deptid=2 where empid=105 Update employee set deptid=1 where empid=104 Update employee set deptid=2 where empid=103 Update employee set deptid=2 where empid=102 update employee set deptid=3 where empid=101 Update the department id for the employees using update command as shown below. The following examples adds deptid column to the existing employee table sqlite> alter table employee add column deptid integer sqlite> alter table department rename to dept 5. The following example renames department table to dept using the alter table command. The following will execute all the commands from the insert-data.sql in the company.db database # sqlite3 company.db select * from employee ģ|Marketing|Los Angeles 4. Insert into department values(3,'Marketing','Los Angeles') Insert into department values(2,'Technology','San Jose') Insert into department values(1,'Sales','Los Angeles') Insert into employee values(105,'Rita Patel','DBA') Insert into employee values(104,'Jane Smith','Sale Manager') Insert into employee values(103,'Jason Bourne','Developer') Insert into employee values(102,'Raj Reddy','Sysadmin') Insert into employee values(101,'John Smith','CEO') You can execute all the insert statements from the sqlite command line, or you can add those commands into a file and execute the file as shown below.įirst, create a insert-data.sql file as shown below. The following example populates both employee and department table with some sample records. Note: To exit from the SQLite commandline “sqlite>” prompt, type “.quit” as shown above.Ī SQLite database is nothing but a file that gets created under your current directory as shown below. Sqlite> create table department(deptid integer,name varchar(20),location varchar(10)) Sqlite> create table employee(empid integer,name varchar(20),title varchar(10)) We’ve purposefully missed the deptid column in the employee table. This also creates an employee table with 3 columns (id, name and title), and a department table in the company.db database. The following example creates a database called company.db. Create a SQLite Database (and a Table)įirst, let us understand how create a SQLite database with couple of tables, populate some data, and view those records.

SQLITESTUDIO QUERY EXAMPLES INSTALL

You can also install SQLite database from source to get the latest version. If you don’t have sqlite installed, execute “yum install sqlite” to install it.

This article explains all the basic SQL commands that you need to know to use the SQLite database effectively. While most of the commands in the SQLite are similar to SQL commands of other datbases like MySQL and ORACLE, there are some SQLite SQL commands that are different. This is a self-contained serverless database engine, which is very simple to install and use. SQLite3 is very lightweight SQL database which focuses on simplicity more than anything else.

Sqlitestudio query examples