Suresh
1
Data: Data: Data is the most important component in any work that we do; Data means collection of information or collection of raw facts. Ex: 1. Text files 2. Facts 3. Figures (photos) 4. Audios 5. Videos etc... Database: collection of related data. DBMS :( Database Management System) Data Base Management Management System is the management management system system or maintenance maintenance system on databases. (Or) It is a suit of software program for creating, maintaining & manipulating the data in database. (Or) It allows the users to insert the data, to retrieve the data, to modify the data and to delete the data. Properties: 1. Create 2. Insert 3. Update 4. Delete 5. Select (query) 6. Low level security etc... Ex: FoxPro, ims (information management system)
The first databases of kind existed in the form of files. A file is nothing but collection of records. A Record is nothing but collection of information or data. But the file system was not very efficient .it was crippled by slow data search speed. It includes the following Drawbacks. 1.Security 2.Data Redundancy 3.Data Integrity 4.Concurrency Control 5.Slow in Process 6. File system: in a dbms database, data must be stored in the form of files only Note: basic property of file system is independent and individual, means each and every file is unique AND relation is not possible between data files 7. Low level security, No Login name No Password.
RDBMS (Relational Database Management System):
Suresh
• •
2
Data must be stored in the form of tables only Relation between Tables is possible
Table: Table: It is collection of columns and rows, X axis directions are rows and Y and Y axis directions are columns Relation: Relation: Creating internal dependencies between Database Tables Ex of RDBMS: 1. Sqlserver 2. Oracle 3. Db2 (Database 2000, IBM) 4. Mysql (open source) 5. ms-access 6. Postgresql (open source) 7. Sybase 8. Tera data Etc..... Terminology: 1. Table -Entity 2. Column (field) -Attribute 3. Row (record) -Tupple 4. Duplicate data (Repeated data) – Data Redundancy Metadata: Metadata: Data about data, it describes characteristics of data Ex: Description of a table Syn: sp_help Ex: sp_help EMP Note: stands for Table name Data models: 1. Hierarchy data model 2. Network data model 3. Relational data model Note: Currently all RDBMS Databases following RELATIONAL DATA MODEL Adv of Relational Data model: •
• •
Relation Perfect solution for duplicate data and null values Normalization
Normalization: Normalization: It is a process of eliminating duplicate data from the database. EF CODD RULES:
Suresh
• •
2
Data must be stored in the form of tables only Relation between Tables is possible
Table: Table: It is collection of columns and rows, X axis directions are rows and Y and Y axis directions are columns Relation: Relation: Creating internal dependencies between Database Tables Ex of RDBMS: 1. Sqlserver 2. Oracle 3. Db2 (Database 2000, IBM) 4. Mysql (open source) 5. ms-access 6. Postgresql (open source) 7. Sybase 8. Tera data Etc..... Terminology: 1. Table -Entity 2. Column (field) -Attribute 3. Row (record) -Tupple 4. Duplicate data (Repeated data) – Data Redundancy Metadata: Metadata: Data about data, it describes characteristics of data Ex: Description of a table Syn: sp_help Ex: sp_help EMP Note: stands for Table name Data models: 1. Hierarchy data model 2. Network data model 3. Relational data model Note: Currently all RDBMS Databases following RELATIONAL DATA MODEL Adv of Relational Data model: •
• •
Relation Perfect solution for duplicate data and null values Normalization
Normalization: Normalization: It is a process of eliminating duplicate data from the database. EF CODD RULES:
Suresh
3
Sqlserver 2005: 1. It is RDBMS database 2. It is a product of Microsoft 3. It is case insensitive 4. It is platform dependant-- it is compatible for windows OS 5. User friendly History: Introduced in 1994 •
Sqlserver 4.0
•
Sqlserver 5.0
•
Sqlserver 6.0
•
Sqlserver 6.5
•
Sqlserver 7.0
•
Sqlserver 8.0(2000)
•
Sqlserver 9.0(2005)
•
Sqlserver 10.0(2008)
Editions (flavors): 1. Enterprise edition 2. Standard edition 3. Developer edition 4. Mobile edition 5. Personal edition (express edition) free Software Requirements: 1. OS •
Windows xp sp2 or sp3
•
Windows server 2000
•
Windows server 2003
•
Windows server 2008
•
Windows Vista
•
Windows 7
Suresh
4
2. Windows installer: It is a default component of Windows; we can download it from www.microsoft.com
3. IIS (internet information services): •
It is an application server from MICROSOFT
Hardware requirements: 1. Processor: It supports both 32 bit, 64 bit
Ex:
1. Pentium 4 2. Amd athlon 2. Hard disk: •
500 mb (MICROSOFT)
•
1 GB (Mine)
3. Ram: •
512mb(MICROSOFT)
•
1 GB(Mine)
SQL -- structured query language Sequel-- structured English query language FEATURES OF SQL SERVER: 1. 2. 3. 4. 5. 6. 7.
Security: Provides login and password to interact with data in database Data Redundancy: Less Data reputation Data Integrity: Data validation/Data Checking Process facility Backup and Recovery(Restore) Introducing support for XML User defined function are introduced. OLAP (online analytical process) services available in SQL server 7.0 are now called as SQL server 2005 analysis services
Things to Observe:
1. While writing the Queries using T-SQL in SQL Server Management Studio we need not to follow any particular case. Because T-SQL is case insensitive language. 2. After writing the Query, we need to select that query using either mouse or keyboard. 3. Now Press F5 (Execute Key). 4. Then the results are displayed in a separate window called Result window or Result Pane.
Suresh
5
5. Use Ctrl+R to Hide/Show the Result window or Result Pane. 6. Use F8 for Object Explorer Note: 1. 2. 3. 4.
SQL SERVER can handle nearly 32767 Databases Each Database can handle nearly 2 billion Database Objects. Each Table can handle nearly 1024 columns Each Table can handle nearly 1 million Rows
SQLSERVER is two parts, they are
1. Tsql :( Transact sql) 2. Tsql programs Tool: 1. Management studio: It contains two parts, they are 1. Cui (Character user interface): •
Sqlscripting can be written in *newqury* option
•
By using execute button or f5 we can execute the script
2. Gui (Graphical user interface): •
It is Navigations part of the management Studio.
•
We can connect to GUI by using *summary* option.
Path: start
programs
sqlserver 2005
Management Studio (click)
Inside management studio window provide following information 1. Server type-- database engine 2. servername-- database server name 3. authentication-A. Windows’s authentication •
This user must be an OS user
B. Sqlserver authentication UN: sa (sysadmin-- DBA) Pw: ******** (Depends Upon Installation)
Databases: 1. System databases:
Suresh
6
•
These are inbuilt or default databases
•
Along with installation of Sqlserver 2005 we can get system databases
•
System databases monitors entire database engine
Note: User can’t create and delete a system database A. master B. model C. msdb D. tempdb E. MS resources Description master Database
Records all the system-level information for an instance of SQL Server.
msdb Database
It is used by SQL Server Agent for scheduling alerts and jobs.
model Database
Template database
Resource Database
It is a read-only database that contains system objects that are included with SQL Server 2005. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdb Database
It is a workspace for holding temporary objects or intermediate result sets.
2. User databases: •
These database created and maintained by the users explicitly
Ex: CREATE DATABASE suresh ON PRIMARY (NAME=suresh, FILENAME="C:\MSSQL\vadde\suresh.mdf", SIZE=100, MAXSIZE=200, FILEGROWTH=25%) Log on ( NAME=suresh_log, FILENAME="C:\MSSQL\vadde\suresh_log.ldf", SIZE=100, MAXSIZE=200, FILEGROWTH=25%)
Suresh
7
SQL Server Data Types: Character strings: Data type char(n) varchar(n) varchar(max) text
Description Fixed-length character string. Maximum 8,000 characters Variable-length character string. Maximum 8,000 characters Variable-length character string. Maximum 1,073,741,824 characters Variable-length character string. Maximum 2GB of text data
Storage n
Description Fixed-length Unicode data. Maximum 4,000 characters Variable-length Unicode data. Maximum 4,000 characters Variable-length Unicode data. Maximum 536,870,912 characters Variable-length Unicode data. Maximum 2GB of text data
Storage
Description Allows 0, 1, or NULL Fixed-length binary data. Maximum 8,000 bytes Variable-length binary data. Maximum 8,000 bytes Variable-length binary data. Maximum 2GB Variable-length binary data. Maximum 2GB
Storage
Description Allows whole numbers from 0 to 255 Allows whole numbers between -32,768 and 32,767 Allows whole numbers between -2,147,483,648 and 2,147,483,647 Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 Fixed precision and scale numbers.
Storage 1 byte 2 bytes 4 bytes 8 bytes
Unicode strings: Data type nchar(n) nvarchar(n) nvarchar(max) ntext Binary types: Data type bit binary(n) varbinary(n) varbinary(max) image Number types: Data type tinyint smallint int bigint decimal(p,s)
Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p
5-17 bytes
Suresh
8
must be a value from 1 to 3 8. Default is 18.
numeric(p,s)
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 Fixed precision and scale numbers. 5-17 bytes Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 3 8. Default is 18.
small money money float(n)
real
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 Monetary data from -214,748.3648 to 214,748.3647 4 bytes Monetary data from -922,337,203,685,477.5808 to 8 bytes 922,337,203,685,477.5807 Floating precision number data from -1.79E + 308 to 1.79E + 308. 4 or 8 bytes The n parameter indicates whether the field should hold 4 or 8 bytes. Float (24) holds a 4-byte field and float (53) holds an 8-byte field. Default value of n is 53. Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes
Date types: Data type datetime datetime2 smalldatetime date time datetimeoffset timestamp
Description From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute Store a date only. From January 1, 0001 to December 31, 9999 Store a time only to an accuracy of 100 nanoseconds The same as datetime2 with the addition of a time zone offset
Storage 8 bytes 6-8 bytes 4 bytes 3 bytes 3-5 bytes 8-10 bytes
Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable
Other data types: Data type sql_variant
Description Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
Suresh
9
uniqueidentifier xml cursor table
Stores a globally unique identifier (GUID) Stores XML formatted data. Maximum 2GB Stores a reference to a cursor used for database operations Stores a result-set for later processing
TSQL: This is a 4th generated intermediate language between user and SQL Server. Whenever user wants to interact with SQL Server, he has to interact with SQL Server through T-SQL. It includes the following Sub Languages Sublanguages: 1. DDL (data definition language) 2. DML (data manipulation language) 3. DRL (data retrieval language) or QL (query language) 4. TCL (transaction control language) 5. DCL (data control language) -- DBA 1. DDL: Commands: 1. Create 2. Truncate 3. Drop 4. Alter 1. Create: used to create database, tables and other database objects syn: create table (col1 dt(l),col2 dt(l),col3 dt(l)) Ex: create table tab1 (id int, ename varchar (20), DOB datetime) Note: •
Max length of a table name and column na me is 30; it may include alphabets, digits 0 to 9 and special character '_' (Underscore).
•
It is known as "naming conventions".
•
Max of 1024 columns can be included into a table.
2. Truncate: used to delete entire data from a table and its not possible to delete selected or specific rows from a table by using truncate command. Ex: Assume that Tab1 is a table, contains 10 rows of data, now Entire data of Tab1 table= 10rows Selected or specific data of Tab1 table
Suresh
Syn: truncate table Ex: truncate table tab1 3. Drop: used delete a table from the database Syn: drop table Ex: drop table tab1 4. Alter: Alter+add: used to add a column to the table Syn: alter table add column_name DT (l), column_name DT (l) Ex: alter table tab1 add sal numeric (7, 2), comm Numeric (5, 2), address varchar (50)
Alter+drop: used delete columns from the table Syn: alter table drop column column_name Ex: alter table tab1 drop column address
Alter+alter : used change the data type and length of data type of a column Note: Column is empty then only we can change data type Syn: alter table alter column column_name DT (l) Ex: alter table tab1 alter column id varchar (20)
Miscellaneous: System stored procedures: Directly we can execute them when ever required 1. Getting description of a table (metadata) Syn: sp_help Ex: sp_help tab1 2. Getting description of a database: Syn: sp_helpdb Ex: sp_helpdb Suresh 3. Displaying list of tables in a database Syn: sp_tables
10
Suresh
Ex: sp_tables 4. Changing name of a table Syn: sp_rename , Ex: sp_rename tab1, employ 5. Retrieving list of Databases Syn: sp_databases
DML: Commands: 1. Insert 2. Update 3. Delete 1. Insert: Used to insert (add) data into tables Syn: insert into values (col1, col2, col3) Ex: insert into employ values ('a12','ramesh','12-apr-07', 3000, 30) Id name DOB sal comm Note: while working with char and date time data we should use single quotes Inserting null values into table: Student table contains 5 columns; now insert null value into comm column Ex: insert into employ (id, name, DOB) values ('a13','ravi','21-jul-06', 2000) Ex: insert into employ values ('a14 ','ramesh','12-apr-06', 5000, null)
2. Update: used to update (replace) old data with new data, it is two types they a re A. Updating entire data in a column: Syn: update set column_name=new_value Ex: update employ set sal=5000 B. Updating selected data in a column: Syn: update set column_name=new_value where Ex: update employ set sal=3000 where id='a12' 3. Delete: used to delete entire data and selected data from a table A. Deleting entire data from a table:
11
Suresh
12
Syn: delete from Ex: delete from employ B. Deleting selected data from a table: Syn: delete from where Ex: delete from employ where id='a14'
DRL: Commands: 1. Select: used to retrieve or display data from table or tables Note: Retrieving data from single table is 4 types 1. Retrieving entire data (all columns and all rows) from a table Syn: select*from Ex: select*from employ Note: * stands for entire columns of the table 2. Retrieving all columns and selected rows from a table Syn: select*from where Ex: select*from employ where id='a12' 3. Retrieving entire data from selected columns Syn: select col1, col2 from Ex: select ename, fees from employ 4. Retrieving selected rows from selected columns Syn: select col1, col2 from where Ex: select name, fees from employ where id='a13'
DCL: It is used to control the data between different user accounts. It includes the following statements. 1. GRANT Statement 2. REVOKE Statement Grant Statement: This statement is used to grant the permissions (INSERT, SELECT, UPDATE, DELETE) on a specific table to different user accounts.
Suresh
13
Syntax: GRANT {ALL/SPECIFIC PERMISSIONS} ON TABLENAME TO USER ACCOUNT (S) [WITH GRANT OPTION]
WITH GRANT OPTION: When any user got the p ermissions on a specific table from other user with this option, then that user can grant the permissions on that same table to another user account. At that time sub user acts as owner. Ex: GRANT ALL ON EMP TO RAMESH WITH GRANT OPTION From the above statement RAMESH user account got all permissions on EMP table from SA user account. Mean time RAMESH can give the permissions on EMP to another user account because he got the permissions WITH GRANT OPTION. Ex: GRANT INSERT, SELECT ON EMP TO SURESH Now SURESH can perform select and insert operations on EMP table. But SURESH cannot perform update and delete operations on EMP table because he does not have the corresponding permissions.
Revoke Statement: This statement is used to revoke the permissions (INSERT, SELECT, UPDATE, DELETE) on a specific table from different user a ccounts. Syntax: REVOKE {ALL/SPECIFIC PERMISSIONS} ON TABLENAME FROM USER ACCOUNT (S) [CASCADE]
CASCADE: Using this option we can destroy the communication link between user accounts more over from the main user it self we can revoke the permissions from all sub users. Ex: REVOKE ALL ON EMP FROM SUBBU CASCADE The above statement revokes the permissions on EMP table from SUBBU and SURESH. Now SUBBU and SURESH users cannot access EMP table. TCL: Transaction is nothing but a unit of work. We can control these transactions using the following statements ROLLBACK Statement COMMIT Statement SAVE TRAN [SACTION]
Suresh
14
ROLLBACK Statement: This statement is used to cancel a particular performed transaction. To perform this statement in SQL Server we have to follow any one of the below 2 approaches. Approach 1: SET IMPLICIT_TRANSATIONS ON This approach is only to cancel a single recently performed operation. Ex: SET IMPLICIT_TRANSATIONS ON SELECT * FROM EMP DELETE FROM EMP SELECT * FROM EMP ROLLBACK SELECT * FROM EMP Approach 2: Explicit Transactions To approach is to cancel recently performed multiple operations. Syntax: BEGIN TRAN ------------GO ------------GO ------------ROLLBACK TRAN Note: GO is query separator Ex: BEGIN TRAN INSERT INTO DEPT VALUES (50,’TRA’,’AUS’) GO UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11 GO DELETE FROM STUDENT WHERE SNO=101 Select the entire transaction and press F5 for one time ROLLBACK TRAN The ROLLBACK statement cancels INSERT on Dept, UPDATE on EMP and DELETE on Student tables. COMMIT Statement: This statement makes a transaction permanent. It is not possible to rollback the committed transaction. Ex: SELECT * FROM EMP DELETE FROM EMP SELECT * FROM EMP COMMIT
Suresh
15
SELECT * FROM EMP The COMMIT statement deletes the data from EMP permanently. It is not possible to ROLLBACK the delete operation. Ex: BEGIN TRAN INSERT INTO DEPT VALUES (50,’TRA’,’AUS’) GO UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11 GO DELETE FROM STUDENT WHERE SNO=101 Select the entire transaction and press F5 for one time COMMIT TRAN The above COMMIT TRAN makes all three transactions permanent. We cannot ROLLBACK the transactions. SAVE TRAN Statement: This statement is used to COMMIT/ROLLBACK a particular performed transaction from the set of transactions. It is associated with alphabets in order to save the transactions. BEGIN TRAN SAVE TRAN A INSERT INTO DEPT VALUES (50,’TRA’,’AUS’) SAVE TRAN B UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11 SAVE TRAN C DELETE FROM STUDENT WHERE SNO=101
ROLLBACK TRAN C (The delete operation will be cancelled) COMMIT TRAN B (The update ope ration performed permanently we cannot rollback)
TABLES FOR PRACTICALS:
1.CREATE TABLE EMP (EMPNO NUMERIC(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4), HIREDATE DATETIME, SAL NUMERIC(7, 2), COMM NUMERIC(7, 2),
Suresh
16
DEPTNO NUMERIC(2)); DATA:
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,'17-DEC-1980', 800, NULL, 20); INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,'20-FEB-1981', 1600, 300, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,'22-FEB-1981', 1250, 500, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,'2-APR-1981', 2975, NULL, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,'28-SEP-1981', 1250, 1400, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,'1-MAY-1981', 2850, NULL, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,'9-JUN-1981', 2450, NULL, 10); INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,'09-DEC-1982', 3000, NULL, 20); INSERT INTO EMP VALUES
Suresh
17
(7839, 'KING', 'PRESIDENT', NULL,'17-NOV-1981', 5000, NULL, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,'8-SEP-1981',1500, 0, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,'12-JAN-1983', 1100, NULL, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,'3-DEC-1981', 950, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,'3-DEC-1981', 3000, NULL, 20); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,'23-JAN-1982', 1300, NULL, 10); 2.CREATE TABLE DEPT (DEPTNO NUMERIC(2), DNAME VARCHAR(14), LOC VARCHAR(13) ); DATA:
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
Suresh
18
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); 3.CREATE TABLE (GRADE LOSAL HISAL
SALGRADE NUMERIC, NUMERIC, NUMERIC);
DATA:
INSERT 1200); INSERT 1400); INSERT 2000); INSERT 3000); INSERT 9999);
INTO SALGRADE VALUES (1,
700,
INTO SALGRADE VALUES (2, 1201, INTO SALGRADE VALUES (3, 1401, INTO SALGRADE VALUES (4, 2001, INTO SALGRADE VALUES (5, 3001,
Operators: 1. Arithmetic operators: +, -,*, / 1. +: Select sal, sal+1000 from EMP 2.-: Select sal, sal-100 from EMP 3.*: Select sal, sal*12 from EMP 4. /: Select sal, sal/2 from EMP
Suresh
2. Assignment operator’s :( relational operators) =, <> or ! =,>,>=,<,<= 1. =: Select*from EMP where deptno=20 2. <> or! =: Select*from EMP where deptno<>20 3.>: Select*from EMP where sal>3000 4.>=: Select*from EMP where sal>=3000 5. <: Select*from EMP where sal<3000
6. <=: Select*from EMP where sal<=3000
3. Logical operators: and, or, not Ex: in maths two conditions are there, condition A and condition B A and B-- both conditions must be satisfied A or B-- if anyone satisfied then we can get output 1. And: Select*from EMP where sal>3000 and deptno=20 2. Or: Select*from EMP where sal>3000 or deptno=20
Between operator :( Range operator) Ex: select*from EMP where sal between 2000 and 5000 Not between: Ex: select*from EMP where sal not between 2000 and 5000
19
Suresh
20
In operator :( Minimizes searching time) Select*from EMP where ename in ('Scott', 'Turner’, ‘Suresh') Not in: Select*from EMP where ename not in ('scott','turner','suresh')
Like operator :( Pattern matching) Ex: select*from EMP where ename like’s%' S%-- ename starts with s %s-- ename ends with s %s%-- somewhere s in ename _s%-- second character is s from beginning %s_ -- second from ending is s in ename Note: _ (underscore) denotes one character Not like operator: Select*from EMP where ename not like’s%' Alias: •
Second or duplicate name, can be assigned to a table or column
•
Alias is temporary
•
Select sal*12 from EMP
•
Select sal*12 annual Sal from EMP
Ex:
Note: annual Sal is alias for sal*12 Inbuilt functions: 1. Aggregate functions :( Group functions) 1. Sum (): Select sum (sal) from EMP 2. Average (): Select avg (sal) from EMP 3. Max (): Select max (sal) from EMP
Suresh
21
4. Min (): Select min (sal) from EMP 5. Count (): it counts number of rows Select count (*) from EMP Select count (ename) from EMP Select count (comm) from EMP Note:
1. Count Function doesn’t consider null as a value. 2. COUNT function does not count the rows of the column whose data type is BIGINT, In such situations we have to use COUNT_BIG function 6. Standard deviation (): Select stdev (sal) from EMP 7. Variance (): Select var (sal) from EMP DISTINCT ( ): This function displays the different values available for a specific column. It considers one NULL value. Ex: SELECT DISTINCT (DEPTNO) AS DIFF_VALUES FROM EMP DIFF_VALUES 10 20 Ex: SELECT COUNT (DISTINCT( DEPTNO)) AS NO_OF_ROWS FROM EMP NO_OF_ROWS 2
2. Numeric functions: 1. Absolute: Select abs (-9.5) O/p: 9.5 2. Ceiling: Select ceiling (9.01) O/p: 10
Suresh
22
3. Floor: Select floor (9.9) O/p: 9 4. Square root: Select sqrt (144)
O/p: 12
5. Power: Select power (3, 3) O/p: 27(3*3*3) 6. Square: Select square (3) O/p: 9(3*3) 7. Pi (): Select pi () O/p: 3.14(22/7) 8. Log: Select log (2) O/p: 0.693147180559945 9. Exp: Select exp (2) O/p: 7.38905609893065 10. ROUND (M, N): IT WILL ROUND THE VALUE OF M TO NEAREST WHOLE NUMBER OF IT WILL AROUND.
SELECT ROUND (15.143) 15
SELECT ROUND (16.513) 16
SELECT ROUND (16.816)
Suresh
23
17
SELECT ROUND (21.132,1) 21.1
SELECT ROUND (25.143) 25
3. Character functions :( String functions) 1. Length: Select Len ('Hyderabad') O/p: 9 2. Upper: Select upper ('Hyderabad') O/p: HYDERABAD 3. Lower: Select lower ('HYDERABAD') O/P: hyderabad 4. Reverse: Select reverse ('Malayalam') O/p: Malayalam Select reverse ('school') O/p: loohcs 5. Replace: Select replace ('hyderabad','hyd','cyb') O/p: cyberabad 6. Substring: Select substring ('hyderabad', 3, 5) O/p: derab 7. ASCII (Character): It gives ASCII value of a character Select ASCII (‘A’)
Suresh
O/p: 65 Select ASCII (‘a’) O/p: 97 8. Char (n): It gives character for given ASCII value Select char (98) O/p: b Date time functions: 1. Getdate (): Select getdate () O/p: 2009-07-17 18:11:20.000
2. Getutcdate (): Select getutcdate () O/p: 2009-07-17 12:42:09.263 3. Dateadd: Select dateadd (dd, 7, getdate ()) O/p: 2010-04-27 15:24:15.827 4. Datediff: Select datediff (mm,'03/26/04', getdate ()) O/p: 73 5. Date part: Select datepart (yy, getdate ()) O/p: 2010 Select datepart (mm, getdate ()) O/p: 4 Select datepart (dd, getdate ()) O/p: 20 6. Date name: Select datename (DW, getdate ())
24
Suresh
25
O/p: Tuesday Set operators: a= {1, 2, 3} b= {3, 4, 5} 1. A union B= {1, 2, 3, 4, 5} 2. A union all B= {1, 2, 3, 3, 4, 5} Note: union all retrieves duplicate data 3. A intersect B= {3} 4. A except B = A minus B= {1, 2}
1. Union: SELECT EMPNO FROM EMP UNION SELECT DEPTNO FROM DEPT 2. Union all: SELECT EMPNO FROM EMP UNION ALL SELECT DEPTNO FROM DEPT 3. Intersect: SELECT EMPNO FROM EMP INTERSECT SELECT DEPTNO FROM DEPT 4. Except: SELECT EMPNO FROM EMP EXCEPT SELECT DEPTNO FROM DEPT Group by clause: •
Used to group similar data in the output
•
Conjunction of the similar data
•
Used to divide the table into number of subgroups based on a specific column.
•
Minimum single group or aggregate function is mandatory
•
If column contains similar data then only we can use it into group by clause
Suresh
26
Display the number of employee working in different jobs of EMP table
Ex: Select job, count (job) from EMP Group by job
Display max and min salaries of employees who are working in different deptno's of EMP table
Ex: Select deptno, max (sal), min (sal) from EMP Group by (deptno) Ex: SELECT DEPTNO, MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL, SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO
Having clause: •
•
•
It is an arithmetical expression It can be used as an extension of group by clause and we can’t use where clause after group by clause This clause is used to evaluate a condition with group by clause. Generally for evaluating conditions we will use WHERE clause, but where clause does not support by group by clause.
Ex: Select job, count (deptno) from EMP Group by job Having count (deptno)>2 Ex: SELECT DEPTNO, MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL, SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO HAVING AVG (SAL)>10000
Order by clause: •
It is temporary sorting of the data either in ascending order or in Descending order
•
Default is ascending order
Ascending:
Ex: Select*from EMP order by sal Descending: Ex: Select*from EMP order by sal de sc Clause precedence:
Suresh
27
1. Where clause 2. Group by clause 3. Having clause 4. order by clause
Joins: •
Used to retrieve data from multiple tables
•
Joins plays major role in performance tuning
•
Joins comes under temporary relations
•
We need a common column or common data column in multiple tables
•
Two tables are there, they are EMP and DEPT am creating joins between them
•
Both tables contains a common column i.e. deptno, so that we can create Joins between EMP, DEPT tables
•
Formula: no. of joins=no. of tables-1
Types of JOINS: 1. Inner join :( Equi join) We are retrieving data from EMP, DEPT tables Ex: Select empno, ename, sal, d.deptno, dname, loc from EMP e Inner join DEPT d on e.deptno=d.deptno Note: Query retrieves equal number of rows from both tables 2. Outer join: This is three types 1. Left outer join: Select empno, ename, sal, d.deptno, dname, loc from EMP e Left outer join DEPT d on e.deptno=d.deptno It retrieves condition satisfies and non satisfied data from left side table of the condition i.e. EMP, it retrieves only condition satisfied data from right side table of the condition i.e. DEPT 2. Right outer join: Select empno, ename, sal, d.deptno, dname, loc from EMP e Right outer join DEPT d on e.deptno=d.deptno It retrieves condition satisfied from left side table of the condition i.e. EMP, it retrieves condition satisfied data and non satisfied data from right side table of the condition i.e. DEPT
Suresh
3. Full outer join: This is combination of both right and left outer joins. Select empno, ename, sal, d.deptno, dname, loc from EMP e Full outer join DEPT d on e.deptno=d.deptno It retrieves condition satisfied and non satisfied data from both tables. 3. Non-equi join: EMP table contains a column sal, in SALGRADE table, we got two columns losal and hisal so that we can define a join condition. Select empno, ename, sal, grade from EMP e, SALGRADE s where e.sal between s.losal and s.hisal By using above query we can map employees and their grades. 4. Cross join :( Cartesian product) a= (1, 2, 3) b= (4, 5, 6) A*B=3*3=9 EMP table contains 14 rows Dept table contains 4 rows Cross join of EMP, DEPT tables produces 56 rows
Ex: Select*from EMP cross join DEPT
Creating a table by copying data and structure from another table: Syn: Select*into from Ex: Select*into EMP1 from EMP Ex: (Oracle) Create table EMP1 as select*from EMP; Creating a table by copying sturucture from another table: In this by defining false condition we can avoid copying of data. Syn:
28
Suresh
29
Select*into from where Ex: Select*into EMP2 from EMP where 1=2
SUB-QUERIES: •
It is a Combination of multiple queries or select statements.
•
Query with in a query is known as Sub-Query
Syn: Select statement (Select statement) Outer query inner query
Types: 1. Simple or single row sub query: •
It returns only single row to the user
Ex: Retrieve second highest sal from EMP table
Select max (sal) from EMP where sa l< (select max (sal) from EMP)
2. Nested sub query: •
It is also simple sub query
•
It returns single row
Ex: Retrieve third highest sal from EMP table
Select max (sal) from EMP where sa l< (select max (sal) from EMP where Sal< (select max (sal) from EMP)) Distinct: It eliminates duplications temporarily. Ex:
A. Select sal from EMP B. Select distinct (sal) from EMP
Suresh
30
Formula for retrieving n highest salaries from EMP table:
Select max (sal) from EMP e where n= (select count (distinct (sal)) from EMP b where e.sal
N is a number which indicates position of the sal
N starts from zero
n=n-1 Formula for Min sal:
Select min (sal) from EMP e where n= (select count (distinct (sal)) from EMP b where e.sal>b.sal) 3. Co-related sub query: It can return single and multiple rows to the user
I want to retrieve deptno 20 data from EMP table, for that I want Satisfy the condition in dept table Ex: Select*from EMP where deptno= (select deptno from DEPT where dname='accounting') Note: If a sub query sends multiple values to its nearest main query then we have to use IN operator between Main query and Sub query. Ex: WAQ to display employee details, who are working under RAM dept. SELECT * FROM EMP WHERE DEPTNO= (SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’) Ex: WAQ To display employee details, whose salary is greater than highest salary of 10th department SELECT * FROM EMP WHERE SAL> (SELECT MAX (SAL) FROM EMP WHERE DEPTNO=10) Ex: WAQ To display employee details, whose salary is greater than average salary of RAM department SELECT * FROM EMP WHERE SAL> (SELECT AVG (SAL) FROM EMP WHERE DEPTNO= SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)
Constraints: Set of rules which are used to improve functionality of tables
Suresh
31
These are inbuilt rules We can impose constraints on columns
Adv: 1. Creating permanent relations between tables 2. We can avoid duplications and null values 3. We can create permanent conditions Note: We can create a constraint along with table creation this is of two types: 1. Column level Constraint: It can be created on single column 2. Table level Constraint: It can be created on multiple columns Note: We can add a constraint to the existing table Types: 1. Primary key 2. Unique constraint 3. Check constraint 4. Not null 5. Foreign key (Referential integrity)
1. Primary key: Properties: •
It is unique
•
It is not null Column level Primary Key:
Syn: create table (col1 DT (l) constraint con_name primary key, col2 DT (l), col3 DT (l)) Ex: create table t1 (id int constraint p_key primary key, name varchar (20), DOB datetime) •
In this we can create a constraint on single column only Table level Primary key:
syn: create table (col1 dt(l),col2 dt(l),col3 dt(l), constraint con_name primary key(col1,col2)) Ex: create table t2 (id int, name varchar (20), DOB datetime, constraint p_key3 primary key (id, name))
•
We can create single constraint on multiple columns
Suresh
32
•
Max of single primary key can be created on a table
•
If single primary key is working on multiple columns then it is known as "composite primary key"
2. Unique constraint: Properties: •
Unique
•
It accepts single null value
Syn: create table (col1 DT (l) constraint con_name unique, col2 DT (l), col3 DT (l)) Ex: create table t3 (id int constraint u_con unique, dname varchar (20), loc varchar (20)) 3. Check constraint: used to impose permanent conditions on columns Syn: create table (col1 dt(l),col2 dt(l) constraint con_name check(condition),col3 dt(l)) Ex: create table t4 (id int, sal numeric (7, 2) constraint check_con check (sal>3000), commission numeric (5, 2)) 4. Not null: it doesn’t accept null values Syn: create table (col1 DT (l) constraint con_name not null) Ex: create table t5 (id int constraint n_null not null)
5. Foreign key: •
It always references values from either primary key or unique constraint and there are no properties for foreign key.
•
It also known as referential integrity
•
This reference will be stored into database permanently, so that
•
It is comes under permanent relation
•
Generally primary key and foreign are a part of different tables
•
Primary key table is known as parent or master table
•
Foreign key table is known as child or detail table
•
We can create primary key and foreign key in single table, that is known as "self referential integrity"
Note:
Suresh
33
1. Primary key and foreign key columns data types must be same. 2. If the value existing in primary key then only we can insert same value into foreign key. 3. Foreign key accepts null values when it is referencing from unique constraint syn: create table (col1 dt(l),col2 dt(l),col3 dt(l), constraint con_name foreign key(col1)references (col1)) Ex: create table t6 (id int, address varchar (50), job varchar (20), constraint f_key foreign key (id) references t1 (id)) Adding constraint to a table: Syn: alter table add constraint con_name type_constraint (col_list) Ex: alter table t2 add constraint p_key3 primary key (id) Dropping constraint: Syn: alter table drop constraint con_name Ex: alter table t2 drop constraint p_key3 Information regarding constraints of a table: Syn: sp_helpconstraint Ex: sp_helpconstraint t1
Normalization: Normalization is process of splitting the base table into multiple tables based on the theory of Functional Dependency. OR Normalization is repetitive process in order to identify the functional dependencies among the columns and to remove them. If any functional dependency is occurred after the no rmalization process again we have to start the same process until all functional dependencies have been removed.
To do this Normalization we have to follow rules or conditions called Normal Forms.
Un-Normalized Table
EMPNO PROJNO ENAME PNAME
SAL BUD DEPTNO DNAME LOC
11
(P1, P2)
----
(Pn1, Pn2) ----- -----
10
--------
-----
22
(P2, P3)
----
(Pn2, Pn3) ----- -----
10
--------
-----
33
(P1, P3)
----
(Pn1, Pn3) ----- -----
20
--------
-----
Suresh
34
EMPNO and PROJNO are Primary Keys called ‘COMPOSITE PRIMARY KEY’
FIRST NORMAL FORM (1NF): According to first normal form table should contain only single values columns. But in the above un-normalized table the columns PROJNO and PNAME contains multiple values.
To make the table into first normal form we should have to split the multiple values into single values. EMPNO PROJNO ENAME PNAME
SAL BUD DEPTNO DNAME LOC
11
P1
----
Pn1
----- -----
10
--------
-----
11
P2
----
Pn2
----- -----
10
--------
-----
22
P2
----
Pn2
----- -----
10
--------
-----
22
P3
----
Pn3
----- -----
10
--------
-----
33
P1
----
Pn1
----- -----
20
--------
-----
33
P3
----
Pn3
----- -----
20
--------
-----
SECOND NORMAL FORM (2NF): According to second normal form table should be in 1NF and we should have to remove Partial Functional Dependency.
In the above table DEPTNO non-key column dependent part of the Primary key column i.e. EMPNO. It means there existed Partial functional dependency.
To make the table into second normal form we have to divide the table into multiple tables.
PROJ-INFO PROJNO
PNAMEBUD
P1
Pn1
------
P2
Pn2
------
P3
Pn3
------
EMP-INFO EMPNO ENAME SAL DEPTNO DNAME LOC 11
----
----
10
------
-----
22
----
----
10
------
-----
33
----
----
20
------
-----
Suresh
35
THIRD NORMAL FORM (3NF): According to second normal form table should be in 2NF and we should have to remove Transitive Functional Dependency.
In the above EMP-INFO table non-key column DNAME dependent part on the other non- key column i.e.DEPTNO. It means there existed Transitive functional dependency. To make the table into third normal form we have to divide the table into multiple tables.
PROJ-INFO PROJNO
PNAMEBUD
P1
Pn1
------
P2
Pn2
------
P3
Pn3
------
EMP-INFO
DEPT-INFO
EMPNO ENAME SAL
DEPTNO DNAME LOC
11
----
----
10
------
-----
22
----
----
20
------
-----
33
----
----
Views: A View is nothing but an image table or virtual table, which is created for a base table. A view can be created by taking all values from the base table or by taking only selected values from base table. There are two types’ views available in SQL Server. 1. Simple Views 2. Complex Views Note: If we perform any modifications in base table, then those modifications automatically effected in view and vice-versa. 1. Simple Views: Creating View by taking only one single base table. Syntax: CREATE VIEW VIEWNAME [WITH ENCRYPTION] AS SELECT * FROM TABLENAME [WHERE CONDITION] [WITH CHECK OPTION] Ex: CREATE VIEW V1 AS SELECT * FROM EMP INSERT INTO V1 VALUES (55,’RAVI’, 10000, 10) The above insert statement inserts the values into base table EMP as well as into view V1.
Suresh
36
Ex: CREATE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO=10 INSERT INTO V2 VALUES (66,’BABBU’, 25000, 10) The above insert statement inserts the values into base table EMP as well as into view V2. INSERT INTO V2 VALUES (77,’AMAR’, 15000, 20) The above insert statement inserts the values into only base table EMP but not into view V2 because according to the definition of V2 user supplied values are invalid values. It means invalid values are inserting into base table EMP. To stop this kind of operations we have to create the view with ‘WITH CHECK OPTION’. Ex: CREATE VIEW V3 AS SELECT * FROM EMP WHERE DEPTNO=10 WITH CHECK OPTION INSERT INTO V3 VALUES (88,’TEJA’, 25000, 20) The above insert statement cannot insert the values into base table EMP as well as into view V3.
SP_HELPTEXT: This stored procedure is used to display the definition of a specific view. Syntax: SP_HELPTEXT
VIEWNAME
Ex: SP_HELPTEXT
V1
Output: CREATE VIEW V1 AS SELECT * FROM EMP
WITH ENCRYPTION: Once we create any view with ‘WITH ENCRYPTION’ then we cannot find the definition of that particular view using SP_HELPTEXT stored procedure because this encryption option hides the definition. Ex: CREATE VIEW V4 WITH ENCRYPTION AS SELECT * FROM EMP WHERE DEPTNO=20 SP_HELPTEXT V4 Output: The text for object v4 is encrypted To decrypt the definition of view V4 we have to follow the below approach 1. Replace CREATE with ALTER 2. Remove WITH ENCRYPTION keyword Ex:
Suresh
37
ALTER VIEW V4 AS SELECT * FROM EMP WHERE DEPTNO=20 SP_HELPTEXT V4 CREATE VIEW V4 AS SELECT * FROM EMP WHERE DEPTNO=20 2. Complex Views: Creating View by taking multiple base tables. Ex: CREATE VIEW EMP_DEPT_VIEW AS SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO Syntax to create view based on another views: SQL SERVER enables users to create views based on another view. We can create view based on another view up to 32 levels Syntax: CREATE VIEW VIEWNAME [WITH ENCRYPTION] AS SELECT * FROM VIEWNAME [WHERE CONDITION] [WITH CHECK OPTION] Ex: CREATE VIEW V5 AS SELECT * FROM V1 WHERE DEPTNO=10 Syntax to Drop the Views: DROP VIEW VIEWNAME […N] Ex: DROP VIEW V1, V2, V3, V4, V5
Indexes: Indexes in SQL server is similar to index in text book... Indexes are used to improve the performance of queries. •
INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS 1. Primary key column 2. Foreign key column: frequently used in join conditions. 3. Column which are frequently used in where clause 4. Columns, which are used to retrieve the data in sorting order. •
INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS 1. The columns which are not used frequently used in where clause. 2. Columns containing the duplicate and null values 3. Columns containing images, binary information, and text information.
Suresh
38
TYPES OF INDEXES: • •
CLUSTERED INDEX NON-CLUSTERED INDEX
CLUSTERED INDEX: only one clustered index is allowed per table. The order of values in a table order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table. Syntax: CREATE [UNIQUE] CLUSTERED INDEX INDEXNAME ON TABLENAME (COLUMN) Ex: CREATE CLUSTERED INDEX CI ON EMP (EMPNO) Note: If we want to maintain unique values in clustered/non clustered indexed column then specify UNIQUE keyword along with CLUSTERED INDEX/NONCLUSTERD INDEX
NONCLUSTERED INDEX: It is the default index created by the server the physical order of the data in the table is different from the order of the values in index. Max no. Of nonclustered indexed allowed for table is 249 Syntax: CREATE [UNIQUE] NONCLUSTERED INDEX INDEXNAME ON TABLENAME (COLUMN1…) Ex: CREATE NONCLUSTERED INDEX NCI ON EMP (ENAME, SAL) Ex: CREATE UNIQUE NONCLUSTERED INDEX UI ON DEPT (DNAME) COMPOSITE INDEX: If a Unique NonClustered index is created on more than one column then that concept is called composite index. CREATE UNIQUE NONCLUSTERED INDEX COI ON DEPT (DEPTNO, DNAME)
DEPTNO 10 20 30 10 20 30
DNAME SALES HR IR HR (Accepted) SALES (Accepted) IR (Repeated, Not accepted)
SP_HELPINDEX: This stored procedure is used to display the list of indexes, which have been placed on different columns of a specific table. Ex: SP_HELPINDEX EMP Syntax to drop the index:
Suresh
39
DROP INDEX TABLENAME.INDEXNAME Ex: DROP INDEX DEPT.UI
Synonym: •
It is a database object
•
It is a second or duplicate name which can be assigned to a table
•
It will not occupies any space, just it is a second name of the table
•
Trough synonyms we can impose securities i.e. we can hide actual table names
Syn: create synonym syn_name for Ex: create synonym syn_1 for EMP Retrieving data: Syn: select*from syn_name Ex: select*from syn_1 Dropping: Syn: drop synonym Ex: drop synonym syn_1 RULES AND DEFAULTS: CREATING DEFAULT: Default is one of the database objects used to declare default values globally. Syntax: CREATE DEFAULT DEFAULTNAME AS NUMERICVALUE/STRING VALUE Ex: CREATE DEFAULT D1 AS ‘UNKNOWN’ BINDING THE DEFAULT TO COLUMN: Syntax: SP_BINDDEFAULT DEFAULTNAME, ‘TABLENAME.COLUMN’ Ex: SP_BINDDEFAULT D1, ‘DEPT.LOC’ SP_BINDEFAULT D1, ‘EMP.ENAME’
