SQL

1.To Add a column: (column  word is not required) but data type+field size required.

ALTER  TABLE CUSTOMER ADD DOB DATETIME

2. To drop a column:Word column is required

ALTER  TABLE CUSTOMER drop column DOB

3. To delete the table completely

DROP TABLE customer

4.To create a new table

CREATE TABLE customer(id char(20),name varchar(20),age int);

5.To insert the data in the  same order as the  field names

INSERT INTO customer values('eoo1','rakesh',23)

6. To insert the data in the   discrete order of the  field names

Insert into customer (name,age)values('raman','45')

7.To view all fields

SELECT * FROM customer

8.To view specific fields

select name,id from customer

9.To  Alter the column field size

ALTER TABLE customer ALTER COLUMN name varchar(100)

10.To Add multiple columns somultaneously

 alter table customer add salary int,designation varchar(10)

11.To Delete  entire row from table

 DELETE FROM CUSTOMER WHERE name='rakesh'

12. Update the null values

UPDATE emp SET designation='Developer' WHERE designation IS NULL and ename='sami'

13.Updating a field value

 UPDATE emp1  SET empid='109' WHERE ename='vinu'

14.Renaming table name

SP_RENAME 'emp','emp1'

15. Renaming table columns

SP_RENAME 'emp1.designation','desig'

16. Logical operator

a)Between:

SELECT  * FROM emp1 WHERE salary between 1000 and 1500

b)Not

SELECT  * FROM emp1 WHERE not ename='rakesh'

c)AND

SELECT  * FROM emp1 WHERE salary=1300 and ename='rakesh'

d)OR

SELECT  * FROM emp1 WHERE ename=’sona’ or ename='rakesh'

17.Not Between

SELECT  * FROM emp1 WHERE salary  not between 1000 and 1200

18.In checks the equality

SELECT  * FROM emp WHERE emp_id IN(101,102)

20. Order By clause(ascending order)

SELECT  *

FROM emp

ORDER  BY ename

21.(Descending order)

SELECT  *

FROM emp1

  ORDER BY ename DESC

22.Agreegate  functions

a) SELECT max(price) max_price FROM books

b) SELECT avg(price) avg_price FROM books

c)SELECT COUNT(title) show FROM books

d) SELECT MIN(price) FROMbooks

e)SELECT sum(price) total_price FROMbooks

23. Common types of constraints include the following:

1. NOT NULL Constraint: Ensures that a column cannot have NULL value.
2. DEFAULT Constraint: Provides a default value for a column when none is specified.
3. UNIQUE Constraint: Ensures that all values in a column are different.
4. CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
5. Primary Key Constraint: Used to uniquely identify a row in the table.
6. Foreign Key Constraint: Used to ensure referential integrity of the data.

1)NOT NULL Constraint

With create statement

1.create table product(p_id char(12) not null,pname char(10))

2setting not null field after creating table(not done)

2)default(with create statement)

create table product(p_id char(12) ,pname char(10),salary money default 4000 )

b) setting default  after table creation

By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value.

For example, in the following statement,

CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));

Columns "SID" and "Last_Name" cannot include NULL, while "First_Name" can include NULL.

An attempt to execute the following SQL statement,

INSERT INTO Customer (Last_Name, First_Name) values ('Wong','Ken');

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

2 i)The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. 

CREATE TABLE Student

(Student_ID integer ,

Last_Name varchar (30),

First_Name varchar (30),

Score int DEFAULT 80);

ii).To add the default value of a new column in an existing table

 alter table emp1 add score int default 200

3) UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are distinct.

For example, in the following CREATE TABLE statement,

CREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));

column "SID" has a unique constraint, and hence cannot include duplicate values. Such constraint does not hold for columns "Last_Name" and "First_Name". So, if the table already contains the following rows:

SID	Last_Name	First_Name
1	Johnson	Stella
	James	Gina
3	Aaron	Ralph

Executing the following SQL statement,

INSERT INTO Customer values ('3','Lee','Grace');

will result in an error because '3' already exists in the SID column, thus trying to insert another row with that value violates the UNIQUE constraint.

Please note that a column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key. In addition, multiple UNIQUE constraints can be defined on a table.

4) Check constraint

a)The CHECK constraint ensures that all values in a column satisfy certain conditions. Once defined, the database will only insert a new row or update an existing row if the new value satisfies the CHECK constraint. The CHECK constraint is used to ensure data quality

For example, in the following CREATE TABLE statement,

CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));

Column "SID" has a constraint -- its value must only include integers greater than 0. So, attempting to execute the following statement,

INSERT INTO Customer values ('-3','Gonzales','Lynn');

will result in an error because the values for SID must be greater than 0.

b)Add check and default constraint to the existing table

5).i)Setting primary key at the time of creating the table

Syntax: CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

Ex:create table book(book_id int,bname char(10),price money,primary key (book_id))

or

ex:create table author(author_id int primary key,aname char(10))

ii)Setting primary key after creating the table

Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.

So, first set the field as not null as below ,then set the primary key

create table emp1(emp_id int not null,ename char(10),salary money);

 alter table emp1 add primary key (emp_id)

6. SQL Foreign Key

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

The structure of these two tables will be as follows:

Table CUSTOMER

column name	characteristic
SID	Primary Key
Last_Name
First_Name

Table ORDERS

column name	characteristic
Order_ID	Primary Key
Order_Date
Customer_SID	Foreign Key
Amount

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.

Below we show examples of how to specify the foreign key when creating the ORDERS table:

MySQL:

CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));

SQL Server:

CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

Below are examples for specifying a foreign key by altering a table. This assumes that the ORDERS table has been created, and the foreign key has not yet been put in:

SQL Server:

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

a)

25.To rollback all transactions:

Begin transaction

Create table emp(empid char(12),ename char(14))

Insert into emp values(‘eoo1’,’mona’)

Insert into emp values(‘eoo2’,’riya’)

Rollback transaction

It will undo the  table content again by rolling back all the transaction

26  To rollback few transactions

Begin transaction

Create table emp(empid char(12),ename char(14))

Insert into emp values(‘eoo1’,’mona’)

Insert into emp values(‘eoo2’,’riya’)

Insert into emp values(‘eoo3’,’roma’)

Save transaction A

Insert into emp values(‘eoo4’,’mina’)

Rollback transaction A

Commit transaction

It will rolling back one transaction

27. To change the length size of the field

alter table emp alter column ename char(10)

28 String functions

1.REVERSE : Returns reverse of a input string.

Syntax: REVERSE(string)

SELECT REVERSE('STRING FUNCTION')->NOITCNUF GNIRTS

2. REPLICATE : Repeats a input string for a specified number of times.

Syntax: REPLICATE (string, integer)

SELECT REPLICATE('FUNCTION', 3)->FUNCTIONFUNCTIONFUNCTION

3PATINDEX : PATINDEX function works very similar to CHARINDEX function.PATINDEX function returns the starting position of the first occurrence of a pattern in a specified string, or zeros if the pttern is not found.

Using PATINDEX function you can search pattern in given string using Wildcard characters(%).The % character must come before and after pattern.

Syntax: PATINDEX('%pattern%',string)

SELECT PATINDEX('%SQL%','Useful SQL String Function')->8

SELECT PATINDEX('Useful%','Useful SQL String Function')->1

SELECT PATINDEX('%Function','Useful SQL String Function')->19

If pattern is not found within given string,PATINDEX returns 0.

4LEFT : Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.

Syntax: LEFT(string,integer)

SELECT LEFT('STRING FUNCTION', 6)->STRING

5.RIGHT : Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.

Syntax: RIGHT(string,integer)

SELECT RIGHT('STRING FUNCTION', 8)->FUNCTION

6.LTRIM : Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)

Syntax: LTRIM(string)

SELECT LTRIM('   STRING FUNCTION')->STRING FUNCTION

7.RTRIM : Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)

Syntax: RTRIM( string )

SELECT RTRIM('STRING FUNCTION   ')->STRING FUNCTION

8.LOWER : Convert character strings data into lowercase.

Syntax: LOWER(string)

SELECT LOWER('STRING FUNCTION')->string function

9.UPPER : Convert character strings data into Uppercase.

Syntax: UPPER(string)

SELECT UPPER('string function')->STRING FUNCTION

Setting the forein key syntex:

1.create table order2 (o_id char(10) primary key,oname char(10),c_id char(10),foreign key(c_id) references customer(c_id))

or 

create table order1 (o_id char(10) primary key,oname char(10),c_id char(10) references customer(c_id))

setting the foreign key afterwards

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Setting the primary  key after wards syntex:

First create not null field then set the primary key

create table customer2 (c_id char(10) not null,cname char(10),desig char(10))

alter table customer2 add primary key (c_id)

setting the check constraint

create table customer2 (c_id int check (c_id>1000),cname char(10),desig char(10),salary money)

setting the unique constraint

create table customer2 (c_id int ,cname char(10) unique,desig char(10),salary money)

Setting the forein key syntex:

1.create table order2 (o_id char(10) primary key,oname char(10),c_id char(10),foreign key(c_id) references customer(c_id))

or 

create table order1 (o_id char(10) primary key,oname char(10),c_id char(10) references customer(c_id))

setting the foreign key afterwards

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Or

alter table order1 add constraint ccc foreign key(c_id) references customer(c_id)

Setting the primary  key after wards syntex:

First create not null field then set the primary key

create table customer2 (c_id char(10) not null,cname char(10),desig char(10))

alter table customer2 add primary key (c_id)

or

alter table customer1 add  constraint vvv primary key(c_id)

remove the  primary key

alter table customer1 drop constraint vvv

setting the check constraint

create table customer2 (c_id int check (c_id>1000),cname char(10),desig char(10),salary money)

setting the unique constraint

create table customer2 (c_id int ,cname char(10) unique,desig char(10),salary money)

setting the unique constraint afterwaords

alter table order1 add constraint ddd unique (oname)

setting the check constraint afterwards

alter table order1 add constraint eee check (o_id>3)

To  remove the  constraints (foreign key)

alter table  order1 drop constraint ddd

if u r not setting the name to a constraint ,then you have to copy the name of the system  generated name  from the table structure and put in the alter table statement for removing the constraint.

SQL LIKE

FirstName	LastName	Email	DOB	Phone
John	Smith	John.Smith@yahoo.com	2/4/1968	626 222-2222
Steven	Goldfish	goldfish@fishhere.net	4/4/1974	323 455-4545
Paula	Brown	pb@herowndomain.org	5/24/1978	416 323-3232
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

The SQL LIKE clause is very useful when you want to specify a search condition within your SQL WHERE clause, based on a part of a column contents. For example if you want to select all customers having FirstName starting with 'J' you need to use the following SQL statement:

SELECT * FROM Customers WHERE FirstName LIKE 'J%'

Here is the result of the SQL statement above:

FirstName	LastName	Email	DOB	Phone
John	Smith	John.Smith@yahoo.com	2/4/1968	626 222-2222
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

If you want to select all Customers with phone numbers starting with '416' you will use this SQL expression:

SELECT * FROM Customers WHERE Phone LIKE '416%'

The '%' is a so called wildcard character and represents any string in our pattern.

You can put the wildcard anywhere in the string following the SQL LIKE clause and you can put as many wildcards as you like too.

Note that different databases use different characters as wildcard characters, for example '%' is a wildcard character for MS SQL Server representing any string, and '*' is the corresponding wildcard character used in MS Access.

Another wildcard character is '_' representing any single character.

The '[]' specifies a range of characters. Have a look at the following SQL statement:

SELECT * FROM Customers WHERE Phone LIKE '[4-6]_6%'

This SQL expression will return all customers satisfying the following conditions:

• The Phone column starts with a digit between 4 and 6 ([4-6])
• Second character in the Phone column can be anything (_)
• The third character in the Phone column is 6 (6)
• The remainder of the Phone column can be any character string (%)

Here is the result of this SQL expression:

FirstName	LastName	Email	DOB	Phone
John	Smith	John.Smith@yahoo.com	2/4/1968	626 222-2222
Paula	Brown	pb@herowndomain.org	5/24/1978	416 323-3232
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

Date functions

1.dateadd() function

(a) SELECT DATEADD(mm,5,getdate()) AS NewDate

o/p

(b)to a table

SELECT DATEADD(mm,5,dob) AS NewDate

FROM emp

o/p

2. DATEDIFF() Function

Definition and Usage

The DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF ( datepart , startdate , enddate )

Example:

Arguments

datepart

Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart	Abbreviations
Year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Example

The following SELECT statement:

1.

SELECT DATEDIFF(dd,'2008-11-29','2008-11-30') AS DiffDate

will result in this:

DiffDate

1

2. SELECT DATEDIFF(mm,'2008-11-29',getdate()) AS DiffDate

2.to a table

SELECT DATEDIFF(yy,'12/4/2008',dom) AS DiffDate from  product1

o/p

4. SELECT DAteNAME(m,'2008-12-15') as "monthname"

5. SELECT DAtepart(d,'2008-12-15') as "monthname"

6. SELECT DAteNAME(w,'2008-12-15') as "dayname"

3. getdate function

SELECT GETDATE() as today;

o/p

4. SQL Year():

SELECT YEAR(GETDATE()) as "Year";

SELECT YEAR('8/14/04') as "Year";

SELECT DAtepart(yy,'2008-12-15') as "year"

o/p

5. SQL Month():

SELECT MONTH(GETDATE()) as "Month";

SELECT MONTH('8/14/04') as "Month";

o/p

6. SQL Day():

SELECT DAY(GETDATE()) as "Day";

SELECT DAY('8/14/04') as "Day";

SELECT DAtepart(dd,'2008-12-15') as "day"

SELECT DAtepart(w,'2008-12-15') as "week"

o/p

SQL SELECT INTO

The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.

The general SQL SELECT INTO syntax looks like this:

SELECT Column1, Column2, Column3, INTO Table2 FROM Table1

The list of column names after the SQL SELECT command determines which columns will be copied, and the table name after the SQL INTO keyword specifies to which table to copy those rows.

If we want to make an exact copy of the data in our Customers table, we need the following SQL SELECT INTO statement:

SELECT * INTO Customers_copy FROM Customers

select * into product4 from product1

create table cust(cust_id varchar(4),name1 varchar(50),address varchar(100))

create table ord(o_id varchar(4),cust_id varchar(4),item varchar(10),amount money)

insert into cust values('4','gsdhgj','hjdkhjksd')

insert into ord values('2','2','ghjastyrq',7890)

select * from cust

select * from ord

create trigger t11 on cust

            for delete

as

if (select count(*) from ord join deleted on ord.cust_id=deleted.cust_id)>0

begin

            rollback transaction

            print 'cannot delete'

           

end

delete from cust where cust_id='1'

select * from customer

select datename(weekday,getdate())

select week(getdate())

create trigger t11_chkday

            on customer for update

as

if datename(weekday,getdate()) in ('Thursday')

begin

rollback transaction

print 'can not transact'

end

update customer set cname='llll' where  cname='amar'

create trigger t11_2 on cust for delete

as

begin

delete from ord from ord,deleted where ord.cust_id=deleted.cust_id

print 'done'

end

Change Column Size of a Table

ALTER TABLE tablename ALTER COLUMN columnname datatype

e.g

ALTER TABLE Movie_Master ALTER COLUMN Production varchar(500)