SQL Overview

SQL is a language for the easy retrieval of data from databases. It stores data in various data tables and can store various data types.

To retrieve the first five rows from the table customers you would use:

SELECT TOP 5 * FROM customers
SQL Joins

There are various types of SQL joins with various effects.

Here are some of the joins:

  • INNER JOIN - the most basic type of join to retrieve data from both tables on the join
  • LEFT OUTER JOIN - this returns all records on the left side and the right side when there is data there
  • RIGHT OUTER JOIN - this returns all records on the right side and the left side when there is data there
  • FULL OUTER JOIN - this returns all data from both sides of the join regardless of whether there is data there
  • CROSS JOIN - this will join every individual record from the left table with every individual record on the right

For example:

SELECT * FROM T1
INNER JOIN T2 ON T1.id=T2.id
SQL Views

SQL Views are a way to store a SQL Query and then after that it will be treated as a table.

CREATE VIEW dbo.view1
AS
SELECT * FROM table1
GO

Stored Procedures work in a similar way, but they can run all sorts of other SQL commands as well. But they are slightly harder to use in SQL views, on the other hand they're faster as they are cached on the SQL server.

CREATE PROCEDURE dbo.p1
AS
BEGIN
SELECT * FROM dbo.t1
Creating databases and tables

You can create a database to hold data using the following code:

CREATE DATABASE testDB

Or alternatively use SQL Server management studio to create the database.

You can create tables in the same way. Either by using SSMS's table editor or by using:

CREATE TABLE t1 (
column1 datatype,
column2 datatype
...
)
Inserts and updates

You can insert data into the database using:

INSERT INTO dbo.t1(c1,c2,...)
VALUES ('hi',1,...)

You can update data in the database using:

UPDATE dbo.t1
SET c1='hi'
,c2=1
WHERE c3=id