SQL SERVER 2008 – How does one count different data values in a column

Use this simple query to count the number of data values in a column:

select my_table_column, count(*)

from   my_table

group  by my_table_column;

A more sophisticated example…

select dept, sum(  decode(sex,’M’,1,0)) MALE,

sum(  decode(sex,’F’,1,0)) FEMALE,

count(decode(sex,’M’,1,’F’,1)) TOTAL

from   my_emp_table

group  by dept;

 

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

 

SQL SERVER 2008 – How does one generate primary key values for a table

To generate Primary Key values for a column in SQL SERVER table we have a Property IDENTITY( seed , increment )

seed

It is the value that is stored in the first row loaded into the database.

increment

Is the incremental value that gets added each time a row loaded after the first one.

You must specify both the seed and increment value or none of them.

If none of them is specified, then default is taken as  (1,1).

Here is the Script how we can use the Identity Property

Step 1 : Creating a table Customer table with a primary key

create table Customer(custId int Primary Key IDENTITY(1,1),

Name varchar(30))

Step 2: Insert values to the Customer table

insert into Customer values(‘Ravi’)

insert into Customer values(‘david’)

Output :

select * from Customer

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER 2008 – How does one eliminate duplicates rows from a table

Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:

Method 1:

SQL> DELETE FROM table_name A WHERE ROWID > (      2    SELECT min(rowid) FROM table_name B      3    WHERE A.key_values = B.key_values);

Method 2:

SQL> create table table_name2 as select distinct * from table_name1;    SQL> drop table_name1;    SQL> rename table_name2 to table_name1;    SQL> — Remember to recreate all indexes, constraints, triggers, etc on table…

Method 3:

SQL> delete from my_table t1    SQL> where  exists (select ‘x’ from my_table t2

SQL> where t2.key_value1 = t1.key_value1

SQL> and t2.key_value2 = t1.key_value2

SQL>and t2.rowid  > t1.rowid);

Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.

Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER 2008 – How does one escape special characters when building SQL queries

The LIKE keyword allows for string searches. The ‘_’ wild card character is used to match exactly one character, ‘%’ is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:

SELECT name FROM emp WHERE id LIKE ‘%\_%’ ESCAPE ‘\’;

Use two quotes for every one displayed. Example:

SELECT ‘Franks”s Oracle site’ FROM DUAL;

SELECT ‘A ”quoted” word.’ FROM DUAL;

SELECT ‘A ””double quoted”” word.’ FROM DUAL;

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER 2008 – What are the difference between DDL, DML and DCL commands

DDL is Data Definition Language statements. Some examples:

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – delete objects from the database
  • TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • GRANT – gives user’s access privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

  • SELECT – retrieve data from the a database
  • INSERT – insert data into a table
  • UPDATE – updates existing data within a table
  • DELETE – deletes all records from a table, the space for the records remain
  • CALL – call a PL/SQL or Java subprogram
  • EXPLAIN PLAN – explain access path to data
  • LOCK TABLE – control concurrency

DCL is Data Control Language statements. Some examples:

  • COMMIT – save work done
  • SAVEPOINT – identify a point in a transaction to which you can later roll back
  • ROLLBACK – restore database to original since the last COMMIT
  • SET TRANSACTION – Change transaction options like what rollback segment to use

SQL SERVER 2008 – What is SQL and where does it come from

Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL.

In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.

The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – 2008 – List of All Stored Procedure in Database

To get all the stored procedures in Database run the below code in SQL SERVER 2008 or 2005.

use AdventureWorksDW2008R2

GO

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type=‘P’

ALL Stored Procedure

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )