List all table names of a particular database by SQL query?

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='dbName'

How to list all the tables in SQL Server for particular Schema.?

SELECT t.name FROM sys.tables AS t INNER JOIN sys.schemas AS sON t.[schema_id] = s.[schema_id] WHERE s.name = 'sales';

SQL Server Local and Global Variables

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.

In SQL Server they are two types of variables local variables and global variables .

Local Variables : A local variable is defined with a declare statement and assigned an initial value within the statement batch where it is declared with a set or select statement.

Declaring a Local Variable:

To declare a variable in T-SQL, you use the DECLARE statement:

DECLARE <@var_nam> <data_type>

For example, you would declare the variable @i as an integer by using the statement

DECLARE @i int

T-SQL also supports specifying the AS keyword between the variable's name and its data type, as in the following statement:

DECLARE @i AS int

The variable name must be preceded by the @ sign and conform to the rules for identifiers. The datatype can be any datatype except text, image, or sysname.

Assigning Values to Variables :

SET @i = 12;

Global variables: Global variables are SQL Server-supplied variables that have system-supplied values.The global variables are reserved for system-wide usage. You cannot explicitly create or modify global variables - SQL Server maintains them implicitly.

Predefined global variables are distinguished from local variables by having two @ signs preceding their names, for example, @@error, @@rowcount. Users cannot create global variables, and cannot update the value of global variables directly in a select statement.

List of first 10 Global Variables and their Usage are included below:

@@connections: The number of logins or attempted logins since SQL Server was last started.

 Example:

select getdate() as today , @@connections as login_attempts

Output:

today                   login_attempts

2015-04-03 14:41:15.687 5815906

@@CPU_BUSY     : The Number of milliseconds CPU has spent Working since SQL Server was last started.

Example:

select @@CPU_BUSY as cputime_utilized

@@CURSOR_ROWS: Number of rows currently in the last opened cursor (for the current connection).

Example:

select @@CURSOR_ROWS  AS  ROWS_CURSOR

@@DATEFIRST: First day of the week. NOTE: unlike what you'd expect @@DATEFIRST returns 1 for Monday, 2 for Tuesday, etc. Default is 7(Sunday).

Example:

select  @@DATEFIRST  AS  FIRSTDAY

@@DBTS: The current value of TIMESTAMP for the current database.

Example:

select @@DBTS AS TIMESTAMP_db

@@ERROR: The error number for the last T-SQL statement executed. If this value is zero than there        were no error.

Example:

select @@ERROR AS ERROR_NO

@@FETCH_STATUS: Fetch status of the last FETCH statement of the connection, executed against any cursor opened on the current connection.

Example:

select @@FETCH_STATUS

@@IDENTITY: Returns the last IDENTITY value inserted. If there haven't been any IDENTITY values inserted than this variable is NULL.

Example:

select @@IDENTITY AS LAST_IDENTITYVALUE

@@IDLE: Number of milliseconds SQL Server has been idle since it was last started.

Example:

select @@IDLE AS IDLE_MILLISECONDS

@@IO_BUSY: The amount of time, in ticks, that SQL Server has spent doing input and output operations since it was last started. i.e Number of milliseconds SQL Server has spent performing Input and Output (IO) operations since it was last started.

Example:

select @@IO_BUSY AS IOBUSY_MILLISECONDS

SQL Server BETWEEN Operator

BETWEEN: Specifies a range to test.

Example: - With Numeric

SELECT * FROM employees WHERE employee_id BETWEEN 25 AND 100;

This SQL Server BETWEEN example would return all rows from the employees table where the employee_id is between 25 and 100.

It is equivalent to the following SELECT statement:

SELECT * FROM employees WHERE employee_id >= 25

AND employee_id <= 100;

Example: - With Date

SELECT * FROM employees WHERE start_date BETWEEN '2015/03/01' AND '2015/03/31';

This SQL Server BETWEEN condition example would return all records from the employees table where the start_date is between March 1, 2015 and March 31, 2015.

Example: - Using NOT Operator

SELECT * FROM employees WHERE employee_id NOT BETWEEN 2000 AND 2999;

This SQL Server BETWEEN example would return all rows from the employees table where the employee_id was NOT between 2000 and 2900.

Queries :

1. Write a Query to get employee details  from employee table whose salary between 5000 and 10000.

Ans: Select * from employee where salary between 5000 and 10000.

SQL Server IN Operator

IN : Determines whether a specified value matches any value in a subquery or a list.

Example: - With string

SELECT * FROM employees WHERE last_name IN ('Smith', 'Anderson', 'Johnson');

This SQL Server IN condition example would return all rows from the employees table where the last_name is either 'Smith', 'Anderson', or 'Johnson'.

Example: - With Numeric

SELECT * FROM employees WHERE employee_id IN (1, 2, 3, 4, 10);

This SQL Server IN condition example would return all employees where the employee_id is either 1, 2, 3, 4, or 10.

Example: - Using NOT operator with IN

SELECT * FROM employees WHERE first_name NOT IN ('Sarah', 'John', 'Dale');

This SQL Server IN condition example would return all rows from the employees table where the first_name is not 'Sarah', 'John', or 'Dale'.

The above IN example is equivalent to the following SELECT statement:

SELECT * FROM employees WHERE first_name <> 'Sarah' AND first_name <> 'John'

AND first_name <> 'Dale';

Example: - With Subquery

SELECT * FROM employee WHERE dept_no IN(SELECT dept_no FROM department WHERE location = 'Dallas')

This SQL Server IN condition example would return all rows from the employee table where the departments belongs to Dallas location.

Queries :

Write a Query to get employee details with firstname "Sunil", "Santosh", "Himaja" from employee table.

Ans: Select * from employee where firstname in ('Sunil','Santosh','Himaja').

SQL Server LIKE Operator and WildCards

LIKE :The LIKE operator is used to search for a specified pattern in a column. Pattern can include regular characters or wildcard characters. Below are the possible wildcard characters to make pattern.

Wildcard character	Description	Example
%	Compares 0 or more characters in a string.	WHERE ProductName LIKE '%chai%' displays all products where productname includes word 'chai'.
_ (underscore)	Compares any single character in a string.	WHERE ProductName LIKE '_hai' finds all four-letter first names that end with ‘hai’.
[ ]	Compares any single character within the specified range or set of characters like range [a-c] or set [abc].	WHERE ProductName LIKE '[a-c]hai' displays product name ending with hai and first character between a and c.
[^]	Compares any single character not within the specified range [^a-c] or set [^abc].	WHERE ProductName LIKE '[^a-c]%' displays all product name not starting with character range a,b and c.

Examples of LIKE Operator:

Example 1 : Using like operator in where clause

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'paul henriot'

Output:

ContactName        CompanyName

Paul Henriot          Vins et alcools Chevalier

Above query compares table rows with pattern ‘paul henriot’ and returns rows having same value in contactname column.

Example 2 : Using % wildcard character in like operator

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'paul%'

Output:

ContactName        CompanyName

Paula Wilson         Rattlesnake Canyon Grocery

Paul Henriot          Vins et alcools Chevalier

Paula Parente        Wellington Importadora

LIKE ‘paul%’  pattern returns all contactname having clumn value paul followed by zero or more characters. You can also use NOT operator to find rows that doe not match with pattern. For example  NOT LIKE ‘paul%’.

Example 3 : Using [] square brackets wildcard charaters in like operator

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'pa[lut]%'

Output:

ContactName          CompanyName

Patricio Simpson     Cactus Comidas para llevar

Patricia McKenna    Hungry Owl All-Night Grocers

Paula Wilson            Rattlesnake Canyon Grocery

Palle Ibsen                Vaffeljernet

Paul Henriot             Vins et alcools Chevalier

Paula Parente           Wellington Importadora

As above example shows how can you use wildcard character as literal in pattern matching.  Like ‘pa[lut]%’ searches for rows having column value as first 2 characters are ‘pa’ and third can be any of  characters ‘l’,’u’,’t’ follwed by zero or more characters.

Example 4 : Using wildcard charaters as literals in like operator

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'pa[%]'

Above example serches for rows having column value ‘pa%’, here % sign is taken as charater to search from rows of table.

Query's and Interview Questions :

1.  Write a query to get all the details  from employee table whose firstname start with letter 'a'.

Ans : SELECT * FROM EMPLOYEE where FirstName like 'a%'.

2.  Write a query to get all the details  from employee table whose firstname contains letter 'K'.

Ans: SELECT * FROM EMPLOYEE where FirstName like '%k%'.

3. Write a query to get all the details  from employee table whose firstname ends with letter 'h'.

Ans: SELECT * FROM EMPLOYEE where FirstName like '%h'.

4. Write a query to get all the details  from employee table whose firstname start with any single character between 'a-p'.

Ans: SELECT * FROM EMPLOYEE where FirstName like '[a-p]%'.

SQL Server Constraints

Constraints : Constraints are some rules that enforce on the data to be enter into the database table. Basically constraints are used to restrict the type of data that can insert into a database table.Constraints can be defined in two ways:

Column Level : The constraints can be specified immediately after the column definition with the CREATE TABLE statement. This is called column-level constraints.

Table Level : The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Types of SQL Constraints :

Primary Key Constraints : Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.

Create Table Statement  to create Primary Key :

1. Column Level

USE AdventureWorks2008

GO

CREATE TABLE Products

(

ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,

ProductName VARCHAR(25)

);

GO

2. Table Level

CREATE TABLE Products

(

ProductID INT,

ProductName VARCHAR(25)

CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

);

GO

Alter Table Statement to create Primary Key :

ALTER TABLE Products

ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

GO

Alter Statement to Drop Primary key:

ALTER TABLE Products

DROP CONSTRAINT pk_products_pid;

GO

Foreign Key Constraints : Foreign Key is a field in database table that is Primary key in another table. It can accept multiple nulls, duplicate values.

Create Table Statement  to create Foreign Key :

1. Column Level :

USE AdventureWorks2008

GO

CREATE TABLE ProductSales

(

SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,

ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),

SalesPerson VARCHAR(25)

);

GO

2. Table Level :

CREATE TABLE ProductSales

(

SalesID INT,

ProductID INT,

SalesPerson VARCHAR(25)

CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),

CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)

);

GO

Alter Table Statement to create Foreign Key :

ALTER TABLE ProductSales

ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)

GO

Alter Table Statement to Drop Foreign Key :

ALTER TABLE ProductSales

DROP CONSTRAINT fk_productSales_pid;

GO

UNIQUE Constraint : The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Create Table Statement  to create UNIQUE constraint:

CREATE TABLE Persons

(

P_Id int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

Alter Table Statement to create UNIQUE constraint:

ALTER TABLE Persons

ADD UNIQUE (P_Id)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

Alter Table Statement to Drop UNIQUE constraint:

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

NOT NULL Constraint : The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. By default, a column can hold NULL.

Example Using NOT NULL Constraint :

Create table student(s_id int NOT NULL,Name varchar(60),age int);

The above query will declare that the s_id field of student table will not take NULL value.

An attempt to execute the following SQL statement,

INSERT INTO student (Name, age) VALUES ('Smith', 25);

will result in an error because this will lead to column "S_ID" being NULL, which violates the NOT NULL constraint on that column.

Following example demonstrates both the way to create NOT NULL constraints.

USE AdventureWorks

GO

-- NOT NULL Constraint when Table is created

CREATE TABLE ConstraintTable

(ID INT, ColSecond INT NOT NULL)

GO

-- NOT NULL Constraint after Table is created

ALTER TABLE ConstraintTable

ALTER COLUMN ID INT NOT NULL

GO

--Clean Up

DROP TABLE ConstraintTable

GO

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

Default Constraint: Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.

Create Table Statement to create Default Constraint :

1. Column Level

USE AdventureWorks2008

GO

CREATE TABLE Customer

(

CustomerID INT CONSTRAINT pk_customer_cid PRIMARY KEY,

CustomerName VARCHAR(30),

CustomerAddress VARCHAR(50) CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN'

);

GO

2. Table Level : Not applicable for Default Constraint

Alter Table Statement to Add Default Constraint :

ALTER TABLE Customer

ADD CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' FOR CustomerAddress

GO

Alter Table to Drop Default Constraint :

ALTER TABLE Customer

DROP CONSTRAINT df_customer_Add

GO

Example:

CREATE TABLE Student (Student_ID integer Unique, Last_Name varchar (30), First_Name varchar (30),

Score Integer DEFAULT 80);

and execute the following SQL statement,

INSERT INTO Student (Student_ID, Last_Name, First_Name) VALUES (10, 'Johnson', 'Rick');

Output :

The table will look like the following:

Student_ID         Last_Name         First_Name        Score

10                       Johnson                 Rick                         80