How do you create database 'objects' in SQLite using command line?

In this post,

You will create a SQLite database and persist in your file system.
You will find the directory where the SQLite database is saved.
You will relaunch SQLite3 and access the persisted SQLite database file.
You will create a table.
You will test for tables in your computer/folder.
You will populate the table with some values.
You will run a SELECT query to find the table contents.

I created a desktop shortcut from the file location of sqlite3.exe in one of my folders and launched it. It opens with a command prompt, sqlite>.

The version is at the beginning of the prompt as shown.
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
Creating a database is easy. As shown here it creates a database (file) called 'aloha'.
---------
sqlite> .open aloha
sqlite>
-----------
It creates the database file ("aloha") in the directory which can be seen by the next command. The command .databases without any space after the C: prompt.
------------
sqlite> .databases
main: C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha
sqlite>
-------------------
Creating a table is easy as well. The following creates a table 'flower' in the database.
-----------------
sqlite> CREATE TABLE flower ( Id integer PRIMARY KEY, Ename varchar(20), Fname varchar(20));
-------------
Just check if you have any tables using .tables command as shown.
------------------------------------
sqlite> .tables
flower
---------------
Now exit sqlite3.
Launch sqlite3 again.

We will access the saved (persisted) database 'aloha'. Note that double quotes ("") spawns an error as shown. Filename within single quote ('') opens the database
----------------
sqlite> .open "C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha"
Error: unable to open database "C:UsersOwnerDesktopBlog2017MSSS2017SQLite3_DBSloha": unable to open database file
sqlite> .open 'C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha'
sqlite>
--------------------------------------
Populating the table is also SQL based.
-------------------
sqlite> INSERT INTO flower Values ('1','Hyacinth', 'Jasinth')
   ...> ;
--------------
Now you can run a SELECT query as shown.
--------------
sqlite> SELECT * FROM flower;
1|Hyacinth|Jasinth
sqlite>
-------------------------------

It is continued in the next post tomorrow.

What is a snippet and how do you insert a snippet in SQL Server?

A snippet is a template of TSQL code that you can use as a basis for writing your TSQL Statements. There are lots of canned templates that makes it easy to insert statements and modify them to suit your needs.

Steps to insert a snippet:

1. Right click the database (here Northwind) in which you want to create snippet and choose New query and New Query window opens(the pane on the right)

2. Right click inside the SQL Query pane and choose Insert Snippet (CTRD+X, CTRL+K) to display the following:

There are many different snippets you can insert and they are categorized as shown in the above to screen shots.

3. Let us say we want to create a table. In this case we insert a statement for creating a table. Click on (or double click) Table

4. Click on Create Table and a code snippet will be inserted. It takes a little while and the following code will be inserted.

This code has all the required items for the syntax. Albeit a simple table with two columns, it is a beginning. You may want to add more data types, more columns, add constraints etc.

How do I create a simple table in a database on SQL Server 2008 and populate it?

Let us say you want to create a table such as the one shown in this example (PrincetonTemp2) and populate it with some values. There is more than one way to create a table in SQL Server 2008.This one uses Create Table query to create a table. You then edit the table to insert the values. Values can also be inserted using code. In here, the query creates the table and you then manually insert the data.

Step 1: Create table using the query
In SQL Server 2008 right click the database in which you want to create a table and choose New Query

Type in the Query window as shown in the next figure. Check the syntax of the query by clicking the icon at the far right of the figure. Execute the query by clicking the (!)Execute.

You will have created a table called PrincetonTemp2. You can see it in the Tables node after you refresh the database by right clicking the database node and choosing Refresh from the list.

Step 2: Populate the table by editing the table :
Right click the table in the Management Studio after expanding the database node.










From the drop-down click on Edit Top 200 Rows.
In the table that shows up fill in the data you want to insert. The data has to match the query you used to create the table. One line inserted is shown in the next figure.