RDBMS

Document Sample
RDBMS Powered By Docstoc
					                 SQL
• SQL is a language based on the relational model,
  designed for querying and managing data in an
  RDBMS.
• In the early 1970s, IBM developed a language called
  SEQUEL (Structured English QUEry Language) for
  their RDBMS product called Sytem R.
• The name of the language was later changed from
  SEQUEL to SQL because of trademark dispute.
• SQL first became an ANSI standard in 1986.
   Categories of SQL

• DDL (Data Definition Language)
   • Deals with object definition such as CREATE, ALTER &
     DROP
• DML (Data Manipulation Language)
   • Allow you to query and modify data such as SELECT,
     INSERT, UPDATE, DELETE
• DCL (Data Control Language)
   • Deals with permission such as GRANT & REVOKE
   Categories of SQL

• DDL (Data Definition Language)
   • Deals with object definition such as CREATE, ALTER &
     DROP
• DML (Data Manipulation Language)
   • Allow you to query and modify data such as SELECT,
     INSERT, UPDATE, DELETE
• DCL (Data Control Language)
   • Deals with permission such as GRANT & REVOKE
          Data Types
Integer Data type
            Data type   Storage
            bigint      8 Bytes
            int         4 Bytes
            smallint    2 Bytes
            tinyint     1 Byte

Float data types
           Data type    Storage
           float        4 Bytes/8 Bytes
           real         4 Bytes
Bit Data Type
           Data type    Storage
           bit          1 bit
char and varchar

Are character data types of either fixed length or variable length.

char [ ( n ) ]

   Fixed-length, non-Unicode character data with a length of n bytes. n must be
a value from 1 through 8,000.

varchar [ ( n | max ) ]

   Variable-length, non-Unicode character data. n can be a value from 1
through 8,000. max indicates that the maximum storage size is 2^31-1 bytes.
nchar and nvarchar

Character data types that are either fixed-length, nchar, or variable-length,
nvarchar, for Unicode data.

nchar [ ( n ) ]

   Fixed-length Unicode character data of n characters. n must be a value
from 1 through 4,000. The storage size is two times n bytes.

nvarchar [ ( n | max ) ]

   Variable-length Unicode character data. n can be a value from 1 through
4,000. max indicates that the maximum storage size is 2^31-1 bytes. The
storage size, in bytes, is two times the number of characters entered + 2
bytes.
        DDL Commands

•   CREATE DATABASE
•   DROP DATABASE
•   CREATE TABLE
•   ALTER TABLE
•   DROP TABLE
•   TRUNCATE TABLE
CREATE DATABASE
In order to create a SQL Server database, CREATE DATABASE
statement is used.
Syntax:
CREATE DATABASE <database>
for eg.
CREATE DATABASE EMPLOYEE

DROP DATABASE
In order to delete a SQL Server database, DROP DATABASE
statement is used.
Syntax:
DROP DATABASE <database>
for eg.
DROP DATABASE EMPLOYEE
CREATE TABLE
In order to create a table CREATE TABLE statement is used.

Syntax:
CREATE TABLE <table_name> (<column1> <data_type>, <column2> <data_type> …..)

for eg.
CREATE TABLE EMPLOYEE
(Emp_Id int,
EName varchar(50),
Dept varchar(50),
Contact varchar(50),
Salary int)
                 ALTER
ALTER TABLE
Once a table is created in the database, there are many occasions where one may wish
to change the structure of the table.

Syntax:
ALTER TABLE <table_name> [alter specification]

[alter specification] is dependent on the type of alteration we wish to perform.
Common specifications are given below

ALTER TABLE Add Column
ALTER TABLE Rename Column
ALTER TABLE Drop Column
ALTER TABLE Add Constraint
ALTER TABLE Drop Constraint
ALTER TABLE Add Column
To add new column(s) in table

Syntax:

ALTER TABLE <table_name> ADD <column1> <datatype>

For Eg.

ALTER TABLE Employee ADD Age int

ALTER TABLE Employee ADD City varchar(50), Country varchar(50)
ALTER TABLE Drop Column
To delete column(s) in Table

Syntax:

ALTER TABLE <table_name> DROP COLUMN <column1>

For Eg.

ALTER TABLE Employee DROP COLUMN Age

ALTER TABLE Employee DROP COLUMN City, Country
sp_Rename
This stored procedure is used to rename a table or column.

To rename a table
sp_rename 'employee','employee1'

To rename a column
sp_rename 'dbo.employee.Contact', 'ContactNo', 'COLUMN‘

Note: use full qualified name when change column.
dbo means Database Owner
          DROP TABLE
DROP TABLE
This statement is used to drop a table.

Syntax
DROP TABLE <tablename>

For eg.

DROP TABLE Employee
       COPY TABLE
If we want to create a copy of existing table.

Syntax
Select * into <new_table> from <existing_table>

For eg.
Select * into employee1 from employee
  TRUNCATE TABLE
Sometimes we wish to get rid of all the data in a table. One way of doing
this is with DROP TABLE, which we saw in the last slide. But what if we
wish to simply get rid of the data but not the table itself? For this, we can
use the TRUNCATE TABLE command.

Syntax
TRUNCATE TABLE <tablename>

For eg.
TRUNCATE TABLE Employee
DML Commands
 INSERT Command
Insert command is used to insert values in table.

Syntax
Insert into <tablename>(<col1>,<col2>,…) values(<val1>,<val2>,……..)

Insert into <tablename> values(<val1>,<val2>,……..)

For eg.

Insert into Employee values(201,'Anurag','Sales','9922445566',10000)

Insert into Employee(Emp_Id, Ename, Dept) values(201, 'Anurag', 'Sales‘)
 SELECT Command
This command is use to select records from table.

Syntax
SELECT <Col1, Col2, ….> from <Tablename>

For eg.

SELECT * FROM Employee
(To select all the columns from table, here * represents all columns)

SELECT Emp_Id, Ename from Employee
 SELECT - DISTINCT
If there are redundant rows in table and if want to show only distinct rows
then we use the DISTINCT with SELECT.

Syntax
SELECT DISTICNT <colname> from <Tablename>

For eg.

SELECT DISTINCT City FROM Employee
   SELECT - WHERE
If we want to select rows from table on some conditions then we use
WHERE with SELECT.

Syntax
SELECT <colname> from <Tablename> WHERE <condition>

For eg.

Select * from Employee where Dept='Sales‘

Select * from Employee where Dept<>'Sales‘

Select * from Employee where Salary>5000
SELECT - AND OR NOT
 If we want to select rows on the base of more than one conditions.

 For eg.

 Select * from Employee where Dept='Sales‘ AND Salary>5000

 Select * from Employee where Dept='Sales' AND Salary<>5000

 Select * from Employee where Dept='Sales' OR DEPT='Purchase‘

 Select * from Employee where NOT Dept='HR'
        SELECT - IN
The IN operator allows you to specify multiple values in a WHERE clause.

Syntax
SELECT column_name
FROM table_name
WHERE column_name IN (value1,value2,...)

For eg.
Select * from Employee where Dept IN ('Sales‘,’HR’)
SELECT - BETWEEN
Whereas the IN keyword help people to limit the selection criteria to one or
more discrete values, the BETWEEN keyword allows for selecting a range.

Syntax
SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2

For eg.
Select * from Employee where Salary BETWEEN 10000 AND 20000

Select * from Employee where Salary NOT BETWEEN 10000 AND 20000
        WILDCARDS
There are times when we want to match on a string pattern. To do that, we
will need to employ the concept of wildcard. In SQL, there are two
wildcards:
% (percent sign) represents zero, one, or more characters.
_ (underscore) represents exactly one character.

Wildcards are used with the LIKE keyword in SQL.

Below are some wildcard examples:
• 'A_Z': All string that starts with 'A', another character, and end with 'Z'.
For example, 'ABZ' and 'A2Z' would both satisfy the condition, while
'AKKZ' would not (because there are two characters between A and Z
instead of one).
• 'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and
'ABCABC' would both satisfy the condition.
      SELECT - LIKE
LIKE is another keyword that is used in the WHERE clause. Basically,
LIKE allows you to do a search based on a pattern rather than specifying
exactly what is desired (as in IN) or spell out a range (as in BETWEEN).

Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE {PATTERN}

For eg.
Select * from Employee WHERE EName LIKE 'A%‘

Select * from Employee WHERE EName LIKE 'A__T'
SELECT – ORDER BY
Often, we need to list the output in a particular order. This could be in
ascending order, in descending order, or could be based on either numerical
value or text value. In such cases, we can use the ORDER BY keyword to
achieve our goal.
Syntax
SELECT column_name FROM table_name
[WHERE <condition>]
ORDER BY column_name [ASC,DESC]
ASC – ASCENDING ORDER
DESC – DESCENDING ORDER
By default order is ascending

For eg.
Select * from Employee ORDER BY Ename
Select * from Employee ORDER BY Salary DESC
Select * from Employee ORDER BY Ename, Salary
SELECT – FUNCTIONS
Since we have started dealing with numbers, the next natural question to
ask is if it is possible to do math on those numbers, such as summing them
up or taking their average. The answer is yes! SQL has several arithmetic
functions, and they are:
• AVG: Average of the column.
• COUNT: Number of records.
• MAX: Maximum of the column.
• MIN: Minimum of the column.
• SUM: Sum of the column.

Syntax-
SELECT <Function_Type> <column_name> FROM <table_name>
      SELECT – AVG
SQL uses the AVG() function to calculate the average of a column. The
syntax for using this function is,

Syntax:
SELECT AVG("column_name“) FROM "table_name"

For eg.
SELECT AVG(Salary) from Employee
  SELECT – COUNT
Another arithmetic function is COUNT. This allows us to COUNT up the
number of row in a certain table.

Syntax:
SELECT COUNT("column_name") FROM "table_name"

For eg.
SELECT COUNT(*) from Employee
     SELECT – MAX
SQL uses the MAX function to find the maximum value in a column.

The syntax for using the MAX function is,

SELECT MAX("column_name“) FROM "table_name"

For eg.
SELECT MAX(Salary) from Employee
      SELECT – MIN
SQL uses the MIN function to find the minimum value in a column.

The syntax for using the MIN function is,

SELECT MIN("column_name") FROM "table_name"

For eg.
SELECT MIN(Salary) from Employee
     SELECT – SUM
The SUM function is used to calculate the total for a column.

The syntax is,
SELECT SUM("column_name“) FROM "table_name"

For eg.
SELECT SUM(Salary) from Employee
SELECT – Group By
Now we return to the aggregate functions. Remember we used the SUM
keyword to calculate the total salary for all departments? What if we want
to calculate the total salary for each department? Well, we need to do two
things: First, we need to make sure we select the department name as well
as total salary. Second, we need to make sure that all the salary figures are
grouped by departments.

Syntax :-
SELECT "column_name1", Arithmetic_Function("column_name2")
FROM "table_name"
GROUP BY "column_name1“

For eg.
Select Dept, SUM(Salary) from employee group by Dept
Select Dept, AVG(Salary) from employee group by Dept
Select Dept, MIN(Salary) from employee group by Dept
  SELECT – HAVING
Another thing people may want to do is to limit the output based on the
corresponding sum (or any other aggregate functions). For example, we
might want to see only the dept. with salary over 20000. Instead of using
the WHERE clause in the SQL statement, though, we need to use the
HAVING clause, which is reserved for aggregate functions. The HAVING
clause is typically placed near the end of the SQL statement
The syntax for HAVING is,
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)

For eg.
Select Dept, SUM(Salary) from employee group by Dept having
sum(salary)>40000
        SELECT – AS
The keyword AS is used to assign an alias to the column or a table. It is
insert between the column name and the column alias or between the table
name and the table alias. The syntax for using AS is as follows:

SELECT "table_alias"."column_name1" AS "column_alias"
FROM "table_name" AS "table_alias“


SELECT E.Emp_Id AS EmployeeID FROM Employee AS E
                 JOIN
STORES                                REGIONS
Store_Name         Sales              Region_Name         Store_Name
New Delhi          80000              NORTH               New Delhi
Ludhiana           120000             NORTH               Ludhiana
Trichi             60000              SOUTH               Trichi
Chennai            80000              SOUTH               Chennai
If we want to find out sales by region. We see that table Regions includes
information on regions and stores, and table Stores contains sales information for
each store. To get the sales information by region, we have to combine the
information from the two tables. Examining the two tables, we find that they are
linked via the common field, "store_name".

Select A1.Region_Name REGION, SUM(A2.Sales) SALES
From Regions A1, Stores A2
Where A1.Store_Name=A2.Store_Name
Group By A1.Region_Name
  UPDATE Command
We use UPDATE command to update rows in table.

We can give UPDATE with or without using any condition.

Syntax

UPDATE <table_name> SET <column_name>=<value>

UPDATE <table_name> SET <column_name>=<value> WHERE <condition>

For Eg.

UPDATE Employee SET Company=‘XYZ Ltd.’

UPDATE Employee SET Dept=‘Media’ WHERE Dept=‘Advertisement’
  DELETE Command
We use DELETE command to delete rows from table.

We can give DELETE with or without using any condition.

Syntax

DELETE FROM <table_name>
DELETE FROM <table_name> WHERE <condition>

Caution:
If we give DELETE command without condition then it will delete all records from
table.

For Eg.
DELETE from Employee (Delete all records)

DELETE from Employee WHERE Dept=‘SALES’

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:11/30/2011
language:English
pages:58