SQL

Getting Started with SQL

  • SQL Statements are not case sensitive. However SQL keywords are written in all upper case.
  • SQL statements should be terminated with semi colon ‘;’ , to define the end of each statement.
  • How to create a blank database in MS SQL?
 CREATE DATABASE my_db;
  • How to create tables in the database?
 CREATE TABLE Persons

 (

 PersonID int,

 LastName varchar(255),

 FirstName varchar(255),

 Address varchar(255),

 City varchar(255)

 );

In above example table named persons has been created and each column (PersonID, LastName, FirstName, Address &City) has been defined with type and maximum length.

  • How to insert data in SQL tables?
INSERT INTO Persons (PersonID, LastName, FirstName, Address &City) VALUES(1,’Divate’,’Kedar’,’1276/35, Apartment’,’Pune’);

Above Statement will insert data (Highlighted in yellow) into Persons table. Notice the order of columns and VALUES, they should be in the same order.

  • How to filter data from SQL Table
SELECT * FROM Persons
WHERE LastName=’Divate’;

 

In above statement WHERE keyword is used to filter and show the specific data.

  • How to Update data in SQL table?
SELECT Persons
SET LastName=’Sharma’,FirstName=’Kiran’
WHERE LastName= ‘harma’;

Above mentioned statement will use the table Persons and search for LastName “herma” Notice “S” is missing from LastName and update the LastName and FirstName using SET keyword.

  • How to delete some data from SQL table?
DELETE FROM Persons
WHERE LastName=’Sharma’;

Above statement will use table Persons and delete row containing LastName “Sharma”

  • How to use AND & OR?
SELECT * FROM Persons
 WHERE City=’Pune’
AND LastName=’Divate’;

Above Statement will use table Persons and list only rows whose city is Pune and Last name is “Divate”

  • How to sort data?
SELECT * FROM Persons
ORDER BY LastName ASC;

Above Statement display the data in ascending format.

  • How to Limit the search output?
 SELECT TOP 5 * FOM Persons;

In above statement keyword “TOP”  is used to limit the search result and number 5 will show only top 5 entries.

  • How to use wildcards?
 SELECT * FROM Persons
WHERE City LIKE ‘P%’;

In above statement keyword “LIKE” is used to specify the search pattern and % is used to define wildcard character.

SELECT * FROM Persons
WHERE City LIKE ‘[pbm]%’;

In above statement regex is being used, this will filter the records based on City name starting with either “P” or “B” or “M” and then using % character to find remaining charactersIf  “!” is used in front of any character it negates the meaning of the character. Meaning if “!” is used it will exclude search results starting with “P” or “B” or “M” .

  • How to use Specify multiple values in WHERE keyword?
 SELECT * FROM Persons
 WHERE City IN (‘Pune’, ‘Bangalore’);

In above statement keyword “IN” is used to specify multiple values in round brackets.

  • How to use range values in WHERE statement?
 SELECT * FROM Persons
 WHERE PersonID BETWEEN 2 AND 4;

In above statement BETWEEN keyword is used to specify a range of values to be searched.

  • What is NOT NULL?
    It is the keyword which enforces the column to not accept null values.
 CREATE TABLE PersonsNotNull
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 );

Above statement will create a table with LastName Column which cannot be null.

  • What are constraints?
    Constraints are the rules used to store data in a table. Constraints can be set during tables creation or later using alter statements.
NOT NULL
UNIQUE
PRIMARY KEY
  • What does ALTER Keyword do?
ALTER TABLE Persons
 ADD DateOfBirth date

ALTER keyword is used to alter the database tables. In Above statement we are adding DateOfBirth column to the persons table. Similarly ALTER keyword can be used to remove columns from the table.

Similar to left join above statement is used to return all rows from the right table with the matching rows in the left table.

  • What is Right JOIN?
 SELECT Orders.OrderID, Employees.FirstName
 FROM Orders
 RIGHT JOIN Employees
 ON Orders.EmployeeID=Employees.EmployeeID
 ORDER BY Orders.OrderID;

Above statement will return the output by putting the first selected table values on left and next table values on right side of the output.

  • What is LEFT JOIN?
 SELECT Customers.CustomerName, Orders.OrderID
 FROM Customers
 LEFT JOIN Orders
 ON Customers.CustomerID=Orders.CustomerID
 ORDER BY Customers.CustomerName;
  • What is Inner JOIN?
 SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
 FROM Orders
 INNER JOIN Customers
 ON Orders.CustomerID=Customers.CustomerID;

Above statement will return the matching results from both tables if the “Orders.CustomerID=Customers.CustomerID” field matches to each other.

  • What is JOIN keyword?

Join keyword is used to combine rows from two or more tables based on a common field between them.

  • What is UNIQUE?

A unique constraint uniquely identifies each record in a database table. There can be many Unique constraints per table

CREATE TABLE Persons
 (
 P_Id int NOT NULL UNIQUE,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 );

In above example p_ID is defined as a unique constant.

ALTER TABLE Persons
ADD UNIQUE (P_Id);

Above statement alters existing database tables to set the unique constraint.

  • What is PRIMARY KEY constraint?

A PRIMARY KEY  constraint uniquely identifies each record in a database table. There can be only one PRIMARY KEY constraints per table. Primary key columns must contain UNIQUE values. Primary key column cannot contain NULL values.

CREATE TABLE Persons
 (
 P_Id int NOT NULL PRIMARY KEY,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 );

Above statement P_ID as PRIMARY KEY and it is defined as not null.

ALTER TABLE Persons
 ADD PRIMARY KEY (P_Id);

Above statement alters the table to set the primary key on P_ID column.

  • What is a FOREIGN KEY?

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

CREATE TABLE Orders
 (
 O_Id int NOT NULL PRIMARY KEY,
 OrderNo int NOT NULL,
 P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
 );

Above statement defines P_ID column as foreign key and it refers to the persons table P_ID column.

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

Above statement uses alter statement to define foreign key and it refers to the persons table P_ID column.

  • What is 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.

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

Above statement will define CHECK constraint on P_ID and its set to accept values greater than 0.

ALTER TABLE Persons
 ADD CHECK (P_Id>0);

Above statement defines a CHECK constraint on P_ID column.

  • What is DEFAULT constrain?

Default constraint is used to define default values in to 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'
 );

Above statement will set default value of ‘Sandnes’ in Ciy column if no information if provided.

ALTER TABLE Persons
 ALTER COLUMN City SET DEFAULT 'SANDNES';

Above Statement will use ALTER keyword to set DEFAULT constraint on column named CIty

  • What is AUTO INCREAMENT?

AUTO INCREAMENT creates a unique record in a sequence. This is often used with primary key field to be created automatically every time a new record is inserted.

CREATE TABLE Persons
 (
 ID int IDENTITY(1,1) PRIMARY KEY,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 );

Above statement uses IDENTITY keyword to define AUTO INCREAMENT on column “ID”, then it uses 1 to define the starting number then a comma and again number 1 to define that it will increment by 1.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s