Saturday, June 15, 2019

CHAPTER 1: Microsoft SQL Sever

Getting started with Microsoft SQL Sever

Microsoft SQL Server - is a Relational Database Management System. It use to store and retrieve data request by other applications.

Version                             Release Date
SQL SERVER 2017         2017-10-01
SQL SERVER 2016         2016-06-01
SQL SERVER 2014         2014-03-18
SQL SERVER 2012         2011-10-11
SQL SERVER 2008 R2  2010-04-01
SQL SERVER 2008         2008-08-06
SQL SERVER 2005         2005-11-01
SQL SERVER 2000         2000-11-01



CHAPTER1. INSERT/SELECT/UPDATE/DELETE: the basics of Data Manipulation Language

Date Manipulation Language (DML for short) includes operations such as INSERT, UPDATE AND DELETE:

  --Create a table HelloWorld
  CREATE TABLE HelloWorld(
  id INT IDENTITY,
  Description VARCHAR (1000)
  )

  --DML Operation INSERT, inserting a row into the table
  INSERT INTO HelloWorld(Description) VALUES('Hello World')

  --DML Operation SELECT, displaying the table
  SELECT * FROM HelloWorld

  --Select a specific column from table
  SELECT Description FROM HelloWorld

 --Select a specific column from table
 SELECT Description FROM HelloWorld

  --Display number of records in the table
  SELECT Count (*) FROM HelloWorld

  --DML Operation UPDATE, updating a specific row in the table
  UPDATE HelloWorld SET Description = 'Hello World!' WHERE Id=1

If the database table contains columns which are named like reserved word. e.g. DATE , you need to enclose the column name in brackets, like this:

-- descending order
SELECT TOP 10 (Date) FROM dbo.MyLogTable
ORDER BY (Date) DESC

The same applies if the column name contains spaces in its name (which not recommended). An alternative syntax is to use double quotes instead of square brackets,e.g:

-- descending order
SELECT TOP 10 "Date" from dbo.MyLogTable
where Userid='johndoe'
order by "Date"=desc

JOINS
Joins are useful if you want to query fields which don't exist in one single table, but in multiple tables. For example:

You want to query all columns from the Regions table in the Northwind database. But you notice that you require also the RegionDescription, which is stored in a different table, Region. However, there is a common key. RegionID which you can use to combine this information in a single query as follows (top 5 just return the first 5 rows, omit it to get all rows):

SELECT TOP 5 Territories.*,
  Regions.RegionDescription
FROM Territories
INNER JOIN Region
  ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription.



TABLE ALIASES

When your query requires a refference to two or more tables, you may find it useful to use a table Alias. Table aliases are shorthand references to a tables that can be used in place of a full table name, and can reduce typing and editing. The syntax for using an alias is:

<tablename> [as] <alias>

Where as in optional keyword. For example, the previous query can be rewritten as:

SELECT TOP 5 t.*,
  r.RegionDescription
FROM Territories t
INNER JOIN Region r
  ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

Aliases must be unique for all tables in a query, even if you use the same table twice. For example, if your Employee table included a SupervisorId field, you can use this query to return an employee and his supervisor's name:

SELECT e.*,
  s.Name as SupervisorName --Rename the field for output
FROM Employee e
INNER JOIN Employee s
  ON e.supervisorId = s.EmployeeId
WHERE e.EmployeeId=111

By: Princess Joy Aubrey Belecina

1 comment: