Display structure , Alter command , Drop coloumn table , IN , EXIST , TEMPORARY TABLES , HAVING , UNION

 Display  structure 

select *

from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME='student'

or

sp_help products

Alter  command

·      Add coloumn

 

ALTER TABLE student

ADD Email varchar(255);

Drop coloumn table

 

ALTER TABLE student

DROP COLUMN Email;

Change  datatype using alter

 

ALTER TABLE student

ALTER COLUMN city nvarchar(500);

 

IN OPERATOR

SELECT * FROM student

WHERE city IN ('pune', 'ngp');

The following SQL statement selects all customers that are from the same countries as the suppliers:

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

SELECT *

FROM employees

WHERE employee_id IN (1, 2, 3, 4, 10);

Not in

 

SELECT *

FROM student

WHERE city NOT IN ('ngp', 'pune');

 

 

SELECT *

FROM student

WHERE city <> 'pune'

 

 

SELECT *

FROM student

WHERE city IS not NULL;

 

SELECT roll, name, city

FROM student

WHERE name NOT LIKE 's%';

 

SELECT *

FROM employees

WHERE employee_id NOT BETWEEN 200 AND 250;

Exist with not

 

SELECT *

FROM student

WHERE NOT EXISTS (SELECT *

                  FROM student1

                  WHERE student.name = student1.name);

 

 

Null condation

Description

The SQL Server (Transact-SQL) IS NULL condition is used to test for a NULL value.

 

SELECT *

FROM employees

WHERE last_name IS NULL;

 

Insert null record

           

INSERT INTO student3       new table same coloumn

(roll, name, city)

SELECT roll, name, city

FROM student1               old table

WHERE name IS NULL;

 

Update   statemewnt using null

 

UPDATE employees

SET first_name = 'Unknown'

WHERE first_name IS NULL;

 

Delete  statement using null

 

DELETE FROM employees

WHERE last_name IS NULL;

SQL Server: IS NOT NULL Condition

This SQL Server tutorial explains how to use the IS NOT NULL condition in SQL Server (Transact-SQL) with syntax and examples.

 

UPDATE employees

SET status = 'Active'

WHERE last_name IS NOT NULL;

DELETE FROM employees

WHERE status IS NOT NULL;

SQL Server: EXISTS Condition

This SQL Server tutorial explains how to use the EXISTS condition in SQL Server (Transact-SQL) with syntax and examples.

The SQL Server (Transact-SQL) EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

 

STUDENT1  ( ROLL , NAME , CITY )

 

Student 3  (  roll , name , city )

 

 

SELECT *

FROM student3  

WHERE EXISTS (SELECT *

              FROM student1

              WHERE student3.name  = student1.name);

 

 

SELECT *

FROM student3

WHERE NOT EXISTS (SELECT *

              FROM student1

              WHERE student3.name  = student1.name);

 

 

INSERT INTO contacts

(contact_id, contact_name)

SELECT supplier_id, supplier_name

FROM suppliers

WHERE EXISTS (SELECT *

              FROM orders

              WHERE suppliers.supplier_id = orders.supplier_id);

 

 

DELETE FROM contacts

WHERE EXISTS (SELECT *

              FROM employees

              WHERE employees.last_name = contacts.last_name);

 

 

UPDATE suppliers

SET supplier_name = (SELECT customers.name

                     FROM customers

                     WHERE customers.customer_id = suppliers.supplier_id)

WHERE EXISTS (SELECT customers.name

              FROM customers

              WHERE customers.customer_id = suppliers.supplier_id);

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.

 

 

CREATE table #stu(name varchar(300));

 

 insert  into #stu values('mohan')

 

 select *  from #stu

 

 

 

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.

 

This ##employees 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.

        

 CREATE table ##stu2(name varchar(300));

 

 insert  into ##stu2 values('mohan')

 

 select *  from ##stu2

 

comment  in sql server

/* Author: TechOnTheNet.com */

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

 

SELECT SUM(rate)

FROM inventa  HAVING sum(rate) > 500;

 

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

SQL Server: UNION Operator

Description

The SQL Server UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements.

Each SELECT statement within the UNION operator must have the same number of columns in the result sets with similar data types.

 

SELECT product_id

FROM products

UNION

SELECT product_id

FROM inventory;

SQL Server: UNION ALL Operator

This SQL Server tutorial explains how to use the UNION ALL operator in SQL Server (Transact-SQL) with syntax and examples.

 

The SQL Server UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements.

Each SELECT statement within the SQL Server UNION ALL operator must have the same number of fields in the result sets with similar data types.

Example - Return single field

Let's look at an example of the UNION ALL operator in SQL Server (Transact-SQL) that returns one field from multiple SELECT statements (and both fields have the same data type).

 

create table inventa(pid int , pname varchar(200));

create table productsu(pid int , pname varchar(200));

 

SELECT pid

FROM productsu

UNION all

SELECT pid

FROM inventa;

SQL Server: INTERSECT Operator

This SQL Server tutorial explains how to use the INTERSECT operator in SQL Server (Transact-SQL) with syntax and examples.

Note

• There must be same number of expressions in both SELECT statements.
• The corresponding columns in each of the SELECT statements must have similar data types.
• The INTERSECT operator returns only records in common between the SELECT statements.

create table inventa(pid int , pname varchar(200));

 

create table productsu(pid int , pname varchar(200));

SELECT pid

FROM productsu

INTERSECT

SELECT pid FROM inventa;