Filter Date of birth for same age and particular month

create table ##age (id int,dob datetime)
insert into ##age values(1,'1990-02-25 09:18:58.740')
insert into ##age values(2,'1990-07-01 09:18:58.740')
insert into ##age values(3,'1998-05-25 09:18:58.740')
insert into ##age values(4,'1990-08-25 09:18:58.740')

create table #temp (id int,dob datetime,age int,month int)

insert into #temp (id,dob,age,month)
select id,dob,DATEDIFF(yy, dob, GETDATE()) - CASE WHEN
(MONTH(dob) > MONTH(GETDATE())) OR (MONTH(dob) = MONTH(GETDATE()) AND DAY(dob) > DAY(GETDATE())) THEN 1 ELSE 0 END as age,month(dob) as [month] from ##age

select * from #temp
select * from #temp where age =26 and [month] in (1,2,3,4,5,6,7,8)

How to calculate accurate age from date of birth in sql

DECLARE @date datetime, @tmpdate datetime, @age int
SELECT @date = '02/25/91'
SELECT @tmpdate = @date
SELECT @age = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @age

What are the Operators used in SQL ?

  

1. Comparison Operator :  < , > , < > , = , ! = , <= , >= , !< , !> 

          Comparison operators are used to compare the column data with specific values in a      condition. Comparison Operators are also used along with the SELECT statement to filter      data based on specific conditions.
  
         Example : Select Column_name(s) from table_name where 
                         column_name != some_value

    2. Range Operator : Between , Not Between

         A range search is one that returns all values between two specified values. Inclusive ranges return any values that match the two specified values. Exclusive ranges do not return any values that match the two specified values. 

        Example : Select Column_name(s) from table_name where 
                         column_name Between value1 AND value2
         
    3. List Operator : In , Not IN

         The IN keyword allows you to select rows that match any one of a list of values. For example, without IN, if you want a list of the products in the Frames subcategory, you would need this query. 

        Example : Select Column_name(s) from table_name where 
                         column_name IN (value1 ,value2)

  
   4. String Operator : Like , Not Like

         The LIKE keyword searches for character string, date, or time values that match a specified pattern. The LIKE keyword uses a regular expression to contain the pattern that the values are matched against. The pattern contains the character string to search for, which can contain any combination of four wildcards.

        Example : Select Column_name(s) from table_name where 
                         column_name Like a%. 

      

Wildcard	Meaning
%	Any string of zero or more characters.
_	Any single character.
[ ]	Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
[^]	Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

          

   5. Logical Operators : And , OR , NOT

          The logical operators are AND, OR, and NOT. AND and OR are used to connect search conditions in WHERE clauses. NOT reverses the result of a search condition.

        Example 1 : Select Column_name(s) from table_name where 
                         column_name1 = SOMEVALUE and column_name2 =somevalue 
  
        In this Example only if both the conditions are true , it will return the result.

          Example 2 : Select Column_name(s) from table_name where 

                          column_name1 = SOMEVALUE or column_name2 =somevalue 

        In this Example , if any one of the conditions are true , it will return the result.

        Example 3 : Select Column_name(s) from table_name where 

                         (column_name1 = SOMEVALUE or column_name2 =somevalue)
                          and column_name3 = SOMEVALUE   

         
                

CREATE Statement in SQL

Creating a Database :

    Query: 
   Create database databasename. 

   Example : create database office . 

   How To View All the databases ? 
   Query : SP_HELPDB 

   How To View All the databases Tables ? 
   Query : SP_TABLES 

   What are the datatypes available  ?
   INT , CHAR , VARCHAR , NVARCHAR , FLOAT , LONG , MONEY ....

Creating a Table :

    Creating a Table :

   Query : create table table name (data1 datatype1 ,..........) 
  
   Example : create table empdetails (id int , Name Varchar (20) , Designation Varchar          (20),    Salary int , MobileNo bigint) 

INSERT Command in SQL

INSERT into a Database :

    Query: 
   insert into tablename values (column1,column2,column3,.........) 

(or)

   insert into tablename(column1, column2....) values (data1 , data2......). 

   Example : 
   insert into tablename values (1,'jeeva','Junior S/W Engg',11000,999999999) 

(or)

   insert into tablename (id,name,Designation,salary,mobile)values(1,'jeeva','Junior S/W          Engg',11000,999999999) . 

INSERTING into  a Particular Column in a Database :

    Query: 
   insert into tablename (column1) values ('data'). 

   Example : insert into tablename (name) values ('john') 

   // executing this query will insert only john in second row ,rest of the columns will be  set      as  NULL by default  //

SELECT in SQL

Selecting a Table :

    Query: 
   select * from tablename 

(or)

   select from tablename. 

   example : select * from empdetails . 

Selecting a Table using Where Clause :

   Query: 

   select * from tablename where Columnname1 = value1

(or) 

   select from tablename where Columnname1 = value1

   example : select * from empdetails where empid = 1105

What are the default Databases in SQL ?

The Default Dabases -SQL

1) Master DB :
                    The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

2) Model DB :
                  The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system. Newly created user databases use the same recovery model as the model database. The default is user configurable.

3) MS DB :
             The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb. This information includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored. SQL Server Management Studio uses this information to propose a plan for restoring a database and applying any transaction log backups. Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic application that calls SQL Server Management Objects (SMO) objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows application log, and the SQL Server error log. To help your protect the information that is stored in msdb, we recommend that you consider placing the msdb transaction log on fault tolerant storage. By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

4) Temp DB :
               The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server.Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

5) Resource (Readonly DB) :
                      The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
   

The SQL LIKE Operator

The SQL LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

SQL LIKE Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Examples

SELECT * FROM tbsample WHERE EmployeeName LIKE '%'                          --  selects all EmployeeName

SELECT * FROM tbsample WHERE EmployeeName LIKE 's%'           --  selects all from tbsample  with a EmployeeName Starting with the letter "s"  

SELECT * FROM tbsample WHERE EmployeeName LIKE '%m'         --  selects all from tbsample  with a EmployeeName ending with the letter "m"

SELECT * FROM tbsample WHERE EmployeeName LIKE '_a%'         --  selects all from tbsample  with a EmployeeName Starting with any letter and the next letter must be "a" followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '[a-k]%'    --  selects all from tbsample  with a EmployeeName Starting from "a"  to "k" followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '_[a-k]%'   --  selects all from tbsample  with a EmployeeName Starting from any letter ,next letter should be between "a"  to "k" followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '[^a-k]%'   -- selects all  from tbsample  excluding ( EmployeeName Starting from "a"  to "k" ) followed by any letters.

SELECT * FROM tbsample WHERE EmployeeName LIKE '_[^a-k]%'  -- selects all from tbsample  with a EmployeeName Starting from any letter ,next letter should exclude ( EmployeeName Starting from "a"  to "k" ) followed by any letters.

The ALTER TABLE Statement

The ALTER TABLE Statement

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

To add a column in a table

Syntax : 

alter tablename add column columnname datatype 

Example: 

alter empdetails add column city varchar (50)

To delete a column in a table

Syntax : 

alter table tablename drop column columnname

Example: 

alter table empdetails drop column city 

To change the data type of a column in a table

Syntax : 

alter table tablename alter column columnname datatype

Example: 

alter table empdetails alter column salary varchar(50)

Note:

 We cannot change a character datatype of a column to int , varchar , nvarchar after the table has been inserted with values.

SQL UPDATE Statement

The SQL UPDATE Statement

          The UPDATE statement is used to update existing records in a table.

Syntax:

update tablename set columnname1 = value where columnname = value.

(or)

update tablename set columnname1 = value,columnname2 = value,.... where columnname = value.

Example :

update empdetails set name = peter where id = 1

The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Deleting and Truncating a Table

Deleting a Table 

The DELETE statement is used to delete rows in a table.

Delete All Data 

Query :

delete from tablename  

Example :

delete from empdetails

Delete Particular Row - using Where clause

Query :

delete from tablename where columnname = value

Example :

delete from empdetails where id = 1

Be very careful when deleting records. You cannot undo this statement!

Tuncating a Table

The TRUNCATE statement is used to removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

Query :

truncate table tablename  

Example :

truncate table empdetails.

What is SQL and types of SQL Statements ?

What is Sql ?

     It is specific type of Structured query Language , used to manage the DataBase , by retrieving , updating and modifying the database.. 

           

    Types of SQL Statements : 

   

    DML : Data Manipulation Language is used for managing data with in schema Objects. 

            > Select , Insert , Update , Delete.

    DDL : Data Definition Language is used to define db structure or schema. 

           > Alter , Create , Truncate , Drop.Comment,Rename. 

    DCL : Data Control Language is used to control access to data stored in the database. 

          

           > Grant , Revoke. 

     TCL : Transition Control Language is used to control set of actions called transactions                   like insert,update,delete. 

          > Commit , Rollback . 

Sql Server Concepts.

SQL - RDBMS Concepts

 <-----------SQL SERVER--------------------->

    SQL is a standard language for accessing and manipulating databases.

'What is SQL?'

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard.

'What Can SQL do?'

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

'Semicolon after SQL Statements?'

 *Some database systems require a semicolon at the end of each SQL  statement.

*Semicolon is the standard way to separate each SQL statement in

 database systems that allow more than one SQL statement to be  executed in the same call to the server.

 *We are using MS Access and SQL Server 2000 and we do not    have to put a semicolon after each SQL statement,but some  database programs force you to use it.

 <---------------------------------------------------------------->

'Character or Strings'

   The characters or strings must enclosed only in single Quatation.

    ex:'siva','sakthi','Imrankhan','Ranbhirkapoor'

<----------------------------------------------------->

'Not case sensitive'

     SQL is not case sensitive. SELECT is the same as select.

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is table?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

Remember, a table is the most common and simplest form of data storage in a relational database. Following is the example of a CUSTOMERS table:

What is field?

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record or row?

A record, also called a row of data, is each individual entry that exists in a table. For example there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table:

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would consist of the following:

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

• NOT NULL Constraint: Ensures that a column cannot have NULL value.
• DEFAULT Constraint: Provides a default value for a column when none is specified.
• UNIQUE Constraint: Ensures that all values in a column are different.
• PRIMARY Key: Uniquely identified each rows/records in a database table.
• FOREIGN Key: Uniquely identified a rows/records in any another database table.
• CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
• INDEX: Use to create and retrieve data from the database very quickly.

Below are the Constraints we are going to study :

SQL Select
SQL Distinct
SQL Where
SQL And & Or
SQL Order By
SQL Insert Into
SQL Update
SQL Delete
SQL Injection
SQL Select Top
SQL Like
SQL Wildcards
SQL In
SQL Between
SQL Aliases
SQL Joins
SQL Inner Join
SQL Left Join
SQL Right Join
SQL Full Join
SQL Union
SQL Select Into
SQL Into Select
SQL Create DB
SQL Create Table
SQL Constraints
SQL Not Null
SQL Unique
SQL Primary Key
SQL Foreign Key
SQL Check
SQL Default
SQL Create Index
SQL Drop
SQL Alter
SQL Auto Increment
SQL Views
SQL Dates
SQL Null Values
SQL Null Functions
SQL Data Types
SQL DB Data Types

SQL Functions
SQL Functions
SQL Avg()
SQL Count()
SQL First()
SQL Last()
SQL Max()
SQL Min()
SQL Sum()
SQL Group By
SQL Having
SQL Ucase()
SQL Lcase()
SQL Mid()
SQL Len()
SQL Round()
SQL Now()
SQL Format()

SQL Quick Ref
SQL Hosting