SQL SERVER: commonly used command

SQL SERVER: commonly used command

 Commands to create, modify, and extract data from databases. The following table lists several of the most commonly-used command.

Command	Use To
CREATE DATABASE	Create a new database. CREATE DATABASE EmpMaster;
USE	Specify the database to work in. USE EmpMaster;
SHOW TABLES	Displays a list of tables in a database. SHOW TABLES EmpMaster;
CREATE TABLE	Add a table to the database and create the attributes for records to be added to the table. CREATE TABLE EmpDetails...
INSERT INTO	Add records to the table. A value must be specified for each attribute in the table. To leave an attribute blank, place two commas together. INSERT INTO EmpDetails VALUES ...
DESCRIBE	See a description of a database object. DESCRIBE EmpDetails;
SELECT	Retrieve information from a table. Clauses include: SELECT Name,Address, Salery from EmpDetails...
	WHERE filters using data from a specified field.
	SORT BY sorts displayed data based on an attribute name.
	GROUP BYdetermines how information in a list is grouped.
UPDATE	Change the values in a record. UPDATE EmpDetails Set Name='Anil' where EmpId=15;
DELETE FROM	Remove records from a table. DELETE FROM  EmpDetails WHERE EmpId=15;
ALTER TABLE	Add, change or remove attributes from tables. ALTER TABLE EmpDetails  ADD COLUMN PostCode;
DROP TABLE	Delete an existing table. DROP TABLE EmpDetails;

SQL Server: Global Temporary tables

This SQL Server tutorial explains how to use the GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.

Description

GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) are tables that are created distinct within the SQL Server sessions.

Syntax

The syntax for CREATE GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL) is:

CREATE TABLE ##table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

);

Parameters or Arguments

table_name

The name of the global temporary table that you wish to create. The name of the global temporary table starts with ## characters.

column1, column2

The columns that you wish to create in the global temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Note

• The name of GLOBAL TEMPORARY TABLES are prefixed with ## characters (ie: ##employees).

Example

Let's look at an example of how to create a GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL).

For example:

CREATE TABLE #EmpDetails

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(200) NOT NULL,

  first_name VARCHAR(200),

);

This example would create a GLOBAL TEMPORARY TABLE called ## EmpDetails in SQL Server which has 4 columns.

• The first column is called employee_id which is created as an INT datatype and cannot contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The primary key for the #employees table is the employee_id column.

This ## EmpDetails table is stored in tempdb and SQL Server will automatically delete this table when all users referencing the table have disconnected from the SQL Server session.

SQL Server: LOCAL TEMPORARY TABLES

This SQL Server tutorial explains how to use the LOCAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.

Description

LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within SQL Server sessions. LOCAL TEMPORARY TABLES are stored in tempdb and SQL Server automatically deletes these tables when they are no longer used.

Syntax

The syntax to create a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL) is:

CREATE TABLE #table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

);

Parameters or Arguments

table_name

The name of the local temporary table that you wish to create. The name of the local temporary table starts with the # character.

column1, column2

The columns that you wish to create in the local temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Note

• The name of LOCAL TEMPORARY TABLES are prefixed with the # character (ie: #employees).

Example

Let's look at an example of how to create a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL).

For example:

CREATE TABLE #EmpDetails

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(200) NOT NULL,

  first_name VARCHAR(200),

);

This example would create a LOCAL TEMPORARY TABLE called #EmpDetails in SQL Server which has 4 columns.

• The first column is called employee_id which is created as an INT datatype and can not contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The primary key for the #employees table is the employee_id column.

This #EmpDetails table is stored in tempdb and SQL Server will automatically delete this table when the SQL Server session no longer requires it

SQL GROUP Functions

SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

For Example: If you want the number of employees in a particular department, the query would be:

SELECT COUNT (*) FROM EmpDetails WHERE dept = 'HR';

If you want the total number of employees in all the department, the query would take the form:

SELECT COUNT (*) FROM EmpDetails

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from EmpDetails table, the query would be:

SELECT DISTINCT dept FROM EmpDetails

To get the count of employees with unique name, the query would be:

SELECT COUNT (DISTINCT name) FROM EmpDetails;

SQL MAX(): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an EmpDetails, the query would be:

SELECT MAX (salary) FROM EmpDetails;

SQL MIN(): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an EmpDetails, he query would be:

SELECT MIN (salary) FROM EmpDetails;

SQL AVG(): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM EmpDetails;

SQL SUM(): This function is used to get the sum of a numeric column

To get the total salary given out to the EmpDetails,

SELECT SUM (salary) FROM EmpDetails;

SQL - ALTER TABLE Command

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

You would also use ALTER TABLE command to add and drop various constraints on an existing table.

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:

ALTER TABLE table_name ADD column_name datatype;

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows:

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows:

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows:

ALTER TABLE table_name

ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows:

ALTER TABLE table_name

DROP CONSTRAINT MyUniqueConstraint;

The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows:

ALTER TABLE table_name

DROP CONSTRAINT MyPrimaryKey;