SQL Basics

1.

Union

SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION (UNION ALL)

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

2.

SQL Constraint

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

In SQL, we have the following constraints:

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value when specified none for this column

2.1 SQL NOT NULL Constraint

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

P_Id int NOT NULL,

2.2 SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

P_Id int NOT NULL UNIQUE,

or

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

2.3 SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

or

P_Id int NOT NULL PRIMARY KEY,

2.4 SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

or

P_Id int FOREIGN KEY REFERENCES Persons(P_Id)

2.5 SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

ALTER TABLE Persons
ADD CHECK (P_Id>0)

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

ALTER TABLE Persons

DROP CONSTRAINT chk_Person

2.6 SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255) DEFAULT 'Sandnes'

)

CREATE TABLE Orders

(

O_Id int NOT NULL,

OrderNo int NOT NULL,

P_Id int,

OrderDate date DEFAULT GETDATE()

)

ALTER TABLE Persons

ALTER COLUMN City SET DEFAULT 'SANDNES'

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT

3. Indexes

Indexes allow the database application to find data fast; without reading the whole table.

An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

 ex;

CREATE INDEX PIndex

ON Persons (LastName, FirstName) with two colomns

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

DROP INDEX table_name.index_name

4. Other Commands  

4.1 TRUNCATE TABLE 

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name

4.2 ALTER TABLE 

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Change Data Type

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-digit or four-digit format.

DROP COLUMN 

ALTER TABLE Persons
DROP COLUMN DateOfBirth

4.3 AUTO INCREMENT

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)

VALUES ('Lars','Monsen')

5.SQL CREATE VIEW 

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

SELECT * FROM [Current Product List]

Update

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Drop

DROP VIEW view_name

5.SQL DATES

SQL Dates

 The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.

Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates.

SQL Server comes with the following data types for storing a date or a date/time value in the database:

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - format: a unique number

You can compare two dates easily if there is no time component involved!

If we use the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

we will get no result! This is because the query is looking only for dates with no time portion.

Tip: If you want to keep your queries simple and easy to maintain, do not allow time components in your dates!

6.What is the difference between varchar and nvarchar?

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer.

All modern operating systems and development platforms use Unicode internally. By using nvarcharrather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

but  unicodes need double capacity to store compared to non-unicodes.

7.SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• AVG() - Returns the average value
• 
  
• COUNT() - Returns the number of rows
•  SELECT COUNT(DISTINCT column_name) FROM table_name;
• 
  
• FIRST() - Returns the first value
•  SELECT FIRST(column_name) FROM table_name;
• SELECT TOP 1 column_name FROM table_nameORDER BY column_name ASC;
• 
  
• LAST() - Returns the last value
• SELECT LAST(column_name) FROM table_name;
• SELECT TOP 1 column_name FROM table_nameORDER BY column_name DESC;
• 
  
• MAX() - Returns the largest value
• 
  
• MIN() - Returns the smallest value
• 
  
• SUM() - Returns the sum

8.SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

• UCASE() - Converts a field to upper case
• SELECT UCASE(column_name) FROM table_name;
• 
  
• LCASE() - Converts a field to lower case
• SELECT LCASE(column_name) FROM table_name;
• 
  
• MID() - Extract characters from a text field
• SELECT MID(column_name,start[,length]) FROM table_name;
• start - Required. Specifies the starting position (starts at 1)
• length - Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text
• 
  
• LEN() - Returns the length of a text field
• SELECT LEN(column_name) FROM table_name;
• 
  
• ROUND() - Rounds a numeric field to the number of decimals specified
• SELECT ROUND(column_name,decimals) FROM table_name;
• decimals - Required. Specifies the number of decimals to be returned.
• 
  
• NOW() - Returns the current system date and time
• SELECT NOW() FROM table_name;
• 
  
• FORMAT() - Formats how a field is to be displayed
• SELECT FORMAT(column_name,format) FROM table_name;
• format - Required. Specifies the format.
• SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDateFROM Products;

9.SQL Group By

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

the number of orders sent by each shipper.

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

10.SQL Having

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;