![]() 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. 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. In our example, we first sorted the result by salary in descending order (higher salaries to lower ones) and then by last name in ascending order within those already sorted records.SQLite3 is very lightweight SQL database which focuses on simplicity more than anything else. If you want to use descending order, put the DESC keyword after the appropriate column (in the example, we used descending order for the salary column). If you want to use ascending (low to high) order, you can use the ASC keyword this keyword is optional, though, as that is the default order when none is specified. You can modify the sorting order (ascending or descending) separately for each column. Then, after a comma, add the second column (in our example, last_name). This clause comes at the end of your SQL query.Īfter the ORDER BY keyword, add the name of the column by which you’d like to sort records first (in our example, salary). If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY. This query returns sorted records according to two columns: salary and last_name. Let’s display all information for each employee but sort the records according to salary in descending order first and then by last name in ascending order. Our database has a table named employee with the following columns: id, first_name, last_name, and salary. You need to display records from a given table sorted by two columns.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |