General SQL Commands

SQL

The SQL command structure is in the form of instruction, field, table name, filter. Basic instructions are select, insert into, update, and delete. The select instruction is used to display information based on the cryteria provided. You may select a single field or serveral fields to display. You may also choose all fields by using the astrics (*).

Select statement

select first_name from people where last_name = 'Smith';
The command above displays the first names of all the people with the last name of Smith.

select first_name,last_name,phone from people where company = 'MyBusiness';
This command displays the firt name, last name, and phone number feilds from the people database who has MyBusiness in the company field.

select * from people;
Though I do not recommend executing this command, especially on a large database, this one displays the entire contents of the people table form the selected database to the screen. It is essentially a dump of the database.

Insert statement

The insert into instruction is used to add records to a table in the selected database. The simplest form lists the values in order that the fields occur. I recommend this version for small tables where the data structure is well known. The more complex version lists the field names and then the vaules to be applied to the fields. This version is useful when dealing with large table structures where ald fields may not be entered. The instruction takes the form of insert into tablename values (val1,val2,val3,...); or for the more complex form, insert into tablename (col1, col2, col4, col3...) values (val1, val2, val4, val3...);

insert into people values ('Smith','John','MyBusiness','john.smith@MyBusiness.com','(214)555-1212');
This first form adds the record for John Smith, his company name, email address and phone number; assuming they occur in that order; to the people table.

insert into people (last_name,fist_name,email,business,phone) values ('Smith', 'John', 'john.smith@mybusiness.com', 'MyBusiness', '(214)555-1212');
This second form adds a record to the people table. It lists the field names and then the values that are associated with those field names. This is useful especially there may be many fields left blank.

Update statement

The update instruction directs SQL to change data in selected fileds of the described table based on a filter. The command structure takes the form of
update tablename set col1=val1, col2=val2, col3=val3 where col4='val4';

update people set email='john.smith@mybusiness.com' where fist_name='John' and last_name='Smith';
In this example we update the email address in the people table if the person's fist name is John and the last name is Smith.

delete statement

This is a good time to mention the filter clause, where. The where clause filters out data based on the specified criterion. We saw a good use of the where in the previous update example.

The next basic SQL instruction is delete. As you might guess this is used to remove record from the selected table. The syntax is
delete from tablename where col1=val1;

delete from people where fist_name='John' and last_name='Smith';
In this example we remove the record(s) from the people table if the first name matches John and the last name is Smith.

Import statement

Now that we discussed how to get data into the database manually then let us talk about how to perform a bulk load of the database. Once of the simplest ways is to import a comma separated values (CSV) file into the database. This is a two step process after the database and tables are created. You must set your field delimiter and issue the import command. In the below example I will create a test database and table, set the field delimiter, then import the data into the database.

C:\Users\geen\bin>sqlite3 test.db
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test (firstname text, lastname text, loginname text, email text);
sqlite> .separator ","
sqlite> .import test.csv test
sqlite> select * from test;
firstname,lastname,loginname,email
Glen,Geen,geen,glen.d.geen@hp.com
Brett,Hornick,hornickb,brett.hornick@hp.com
Terry,Copland,capelant,terry.copeland@hp.com
sqlite>

Find data and log files

If you open the database instance in SQL Server Manager Studio you can submit a select query to return the location of the data and log files. In this example I opened the IDF database. I also did the same with the TMSM database instance. This command returns the physical locatoin where the data is stored. 
select name, physical_name as current_file_location from sys.master_files
master	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\master.mdf
mastlog	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\mastlog.ldf
tempdev	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\tempdb.mdf
templog	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\templog.ldf
modeldev	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\model.mdf
modellog	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\modellog.ldf
MSDBData	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\MSDBData.mdf
MSDBLog	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\MSDBLog.ldf
IDF	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\IDF.mdf
IDF_log	c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\DATA\IDF_log.LDF

Full backup of database

Full backup of database example. This script can be saved and run as a recurring task within SQL. 
BACKUP DATABASE [IDF] TO  DISK = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\Backup\IDF' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'IDF-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'IDF' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'IDF' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''IDF'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\Backup\IDF' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Differential Backup of a database

BACKUP DATABASE [IDF] TO  DISK = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\Backup\IDF' WITH  DIFFERENTIAL ,  RETAINDAYS = 21, NOFORMAT, NOINIT,  NAME = N'IDF-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CONTINUE_AFTER_ERROR
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'IDF' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'IDF' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''IDF'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.IDF\MSSQL\Backup\IDF' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Test Connection

  1. Open a command prompt on the computer
  2. Create an empty file with a .UDL extention. Example: notepade testsql.udl
  3. Double click on the .UDL file and enter the information such as SQL server name, login name, password, and table name.
  4. Click the Test Connection button