Leading Software Technologies Chennai
CONTENTS • Introduction • SQL Server Introduction • Data Definition Language • Data Manipulation Language • Data Control Language • Constraints • Functions • Joins • Sub Queries • Views & Indexes • Stored Procedures • Triggers • Cursors • User-defined Data types
INTRODUCTION • What is Database? • Basic Database Concepts • Introduction to DBMS. • Data Model. • Introduction to RDBMS. • DBMS Vs RDBMS
What is a Database? • A structured collection of related data • An filing cabinet, an address book, a telephone directory, a timetable, etc. • In Access, your Database is your collection of related tables
What is a Database? Data vs. Information • Data – a collection of facts made up of text, numbers and dates: • Menaka 50000 5/22/82 • Information - the meaning given to data in the way it is interpreted: • Menaka is a Programmer whose annual salary is $50,000 and whose date of birth is May 22, 1982.
Name: Rahul College: SSNA Tel: 9942131251 Basic Database Concepts • Field Name: Rahul • A single item of data common to all records • Record Name: Rahul College: SSNATel: 9942131251 • A collection of data about an individual item • Table • A set of related records
Basic Database Concepts Example of Table : Fields Records
Application DBMS Database Data Base Management System (DBMS) A set of generalized system software for creating and manipulating large databases, whose interfaces provide a broad range of languages to aid all users
Data Model • Database model is the process of organizing the data into related record types. • Types of Data models: • Hierarchical • Network • Relational • object oriented model
Data Model Hierarchical Database Data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting. A record type can be owned by only one owner. Network Database In network databases, a record type can have multiple owners.
Data Model Relational Database models Relational databases do not link records together physically, but the design of the records must provide a common field to allow for matching. Often, the fields used for matching are indexed in order to speed up the process
Data Model Object Oriented Database An "object oriented database" can be employed when hierarchical, network and relational structures are too restrictive. Object oriented databases can easily handle many-to-many relationships.
Introduction to RDBMS • RDBMS is a Relational Data Base Management System Relational DBMS. • This adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. DBMS are for smaller organizations with small amount of data, where security of the data is not of major concern and RDBMS are designed to take care of large amounts of data and also the security of this data.
DBMS RDBMS 1.Set of data and tools to manage those data. - Will not support RELATION SHIP between data. - Ex : - Foxpro data files and earlier Ms Access. 1.Same as DBMS - Will Support RELATION SHIP between Tables. - Ex : - ORACLE,SQL 2000,DB 2 ... 2.In DBMS only one user can access the same database, at the same time 2.In RDBMS many users simultaneously access the same database 3.No relationship between tables 3. The main advantage of an RDBMS is that it checks for referential integrity (relationship between related records using Foreign Keys). You can set the constraints in an RDMBS such that when a paricular record is changed, related records are updated/deleted automatically. DBMS Vs. RDBMS
SQL SEVER BASICS • Introduction • Data Type • Working with Query Analyzer • SQL Components
SQL • SQL stands for Structured Query Language • SQL allows to access a database • SQL is an ANSI standard computer language • SQL can execute queries against a database • SQL can retrieve data from a database
SQL Sql used for….. • SQL can insert new records in a database • SQL can delete records from a database • SQL can update records in a database • SQL is easy to learn • SQL is a standard computer language for accessing and manipulating databases.
SQL • SQL is a Standard - BUT.... • SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. • SQL statements are used to retrieve and update data in a database. • SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
Data Types Binary data types Date and Time data types Text and image data types Special data types Unicode Character data types Numeric data types Character data types Integer data types User-Defined data types Monetary data types
Data Types CHARACTER DATA TYPES • Character data types are used to store any combination of letters, symbols, and numbers. Enclose character data with quotation marks, when enter it.There are two character data types: • 1) CHAR(N) 2)VARCHAR(N) // n Specifies the Length • Char(n) data type • Store up to 8000 bytes of fixed-length character data. • Varchar(n) data type • Store up to 8000 bytes of variable-length character data. • Variable-length means that character data can contain less than n bytes, and the storage size will be the actual length of the data entered. • Use varchar data type instead of char data type, when you expect null values or a variation in data size.
Data Types DATE AND TIME DATA TYPES • There are two datetime data types: • DATETIME • SMALLDATETIME • Datetime • It is stored in 8 bytes of two 4-byte integers: 4 bytes for the number of days before or after the base date of January 1, 1900, and 4 bytes for the number of milliseconds after midnight. Smalldatetime It is stored in 4 bytes of two 2-byte integers: 2 bytes for the number of days after the base date of January 1, 1900, and 2 bytes for the number of minutes after midnight.
Data Types NUMERIC DATATYPES • DECIMAL[(P[, S])] // Storage Size10^38 - 1 through - 10^38 - 1. ] • NUMERIC[(P[, S])] P - is a precision, that specify the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The maximum precision is 28 digits. S - is a scale, that specify the maximum number of decimal digits that can be stored to the right of the decimal point, and it must be less than or equal to the precision.
Data Types NUMERIC DATATYPES (Cont.) • FLOAT(N) • REAL • Float[(n)] datatype • It is stored in 8 bytes and is used to hold positive or negative floating-point numbers.It can store positive values from 2.23E-308 to 1.79E308 and negative values from -2.23E-308 to -1.79E308. • Real datatype • It is stored in 4 bytes and is used as float datatype to hold positive or negative floating-point numbers. It can store positive values from 1.18E-38 to 3.40E38 and negative values from -1.18E-38 to -3.40E38.
Data Types INTEGER DATATYPES • There are four integer data types: • TINYINT • SMALLINT • INT • BIGINT • TINYINT : It is stored in 1 byte and is used to hold integer values from 0 through 255. • SMALLINT : It is stored in 2 bytes and is used to hold integer values from -32768 through 32,767. • INT : It is stored in 4 bytes and is used to hold integer values from -2147483648 through 2147483647. • BIGINT : It is stored in 8 bytes and is used to hold integer values from -9223372036854775808 through 9223372036854775807
Data Types MONETARY DATATYPES • Monetary datatypes are usually used to store monetary values. • There are two monetary datatypes: • MONEY • SMALLMONEY MONEY It is stored in 8 bytes and is used to hold monetary values from -922337203685477.5808 through 922337203685477.5807. SMALLMONEY It is stored in 4 bytes and is used to hold monetary values from - 214748.3648 through 214748.3647
Data Types SPECIAL DATATYPES • BIT • SQL_VARIANT • TIMESTAMP • UNIQUEIDENTIFIER BIT : It is usually used for true/false or yes/no types of data, because it holds either 1 or 0. All integer values other than 1 or 0 are always interpreted as 1. One bit column stores in 1 byte, but multiple bit types in a table can be collected into bytes. Bit columns cannot be NULL and cannot have indexes on them. SQL_VARIANT : It is used to store values of various SQL Server supported data types, except text,ntext,timestamp, and sql_variant. The maximum length of sql_variant datatype is 8016 bytes. Store in one column of type sql_variant the rows of different data types, for example int, char, and varchar values.
Data Types SPECIAL DATATYPES (Cont.) TIMESTAMP : It is stored in 8 bytes as binary(8) datatype. The timestamp value is automatically updated every time a row containing a timestamp column is inserted or updated. Timestamp value is a monotonically increasing counter whose values will always be unique within a database and can be selected by queried global variable @@DBTS. UNIQUEIDENTIFIER : It is a GUID (globally unique identifier). A GUID is a 16-byte binary number that is guaranteed to be unique in the world.This datatype is usually used in replication or as primary key to unique identify rows in a table. Get the new uniqueidentifier value by calling the NEWID function. Note You should use IDENTITY property instead of uniqueidentifier, if global uniqueness is not necessary, because the uniqueidentifier values are long and more slowly generated.
Data Types TEXT AND IMAGE DATATYPES • Text and image data are stored on the Text/Image pages. There are three datatypes in this category: • TEXT • NTEXT • IMAGE TEXT : It is a variable-length datatype that can hold up to 2147483647 characters. This datatype is used when you want to store the character values with the total length more than 8000 bytes. NTEXT : It is a variable-length unicode datatype that can hold up to 1073741823 characters. This datatype is used when you want to store the variable-length unicode data with the total length more than 4000 bytes. IMAGE : It is a variable-length datatype that can hold up to 2147483647 bytes of binary data.This datatype is used when you want to store the binary values with the total length more than 8000 bytes. It is also used to store pictures.
Data Types UNICODE CHARACTER DATATYPES A column with unicode character datatype can store all of the characters that are defined in the various character sets, not only the characters from the particular character set, which was chosen during SQL Server Setup. Unicode datatypes take twice as much storage space as non-Unicode datatypes. The unicode character data, as well as character data, can be used to store any combination of letters, symbols, and numbers. Enclose unicode character data with quotation marks, when enter it. There are two unicode character datatypes: • NCHAR[(N)] • NVARCHAR[(N)]
Data Types BINARY DATA TYPES • Binary data is similar to hexadecimal data and consists of the characters 0 through 9 and A through F, in groups of two characters each.Specify 0x before binary value when input it. • There are two binary datatypes: • BINARY[(N)] //Specify the maximum byte length with n. • VARBINARY[(N)] • BINARY[(N)] • Store up to 8000 bytes of fixed-length binary data. • VARBINARY[(N)] • Store up to 8000 bytes of variable-length binary data. • Variable-length means that binary data can contain less than n bytes, and the storage size will be the actual length of the data entered. • Use varbinary datatype instead of binary datatype, when you expect null values or a variation in data size.
Working with Query Analyzer To start SQL SERVER Start Programs MicrosoftSQLSERVER Enterprise Manager Query Analyzer Query analyzer
Working with Query Analyzer Query Analyzer
Working with Query Analyzer Server name To log
Working with Query Analyzer Create a new database named as Ebidding
Working with Query Analyzer Select the Query and Press F5 to run the query
Working with Query Analyzer Use command • The USE command selects a database to use for future processing. Syntax Use <databasename>
SQL Components SQL DDL DML DCL Data I/O RDBMS Structure DBA Activities Create Record Create/Delete DBs Create Users Read Record Create/Delete Tables Delete Users Grant privileges Update Record Alter Tables Implement AccessSecurity Delete Record
DATA DEFINITION LANGUAGE • CREATE • ALETR • DROP
Data Definition Language (DDL) • The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. • The most important DDL statements in SQL are: • CREATE TABLE - creates a new database table • ALTER TABLE- alters (changes) a database table • DROP TABLE - deletes a database table
DDL - CREATE CREATE Table using Constraints • Syntax : • Create table <table name >( • column name1 data type , • column name2 data type • ……. • ) Table 1: Employee Eno varchar(10) Ename varchar(100) Dateofbirth varchar(100) Salary int • Example : • Create table Employee ( • Eno varchar(10), • Empname varchar(100), • Dateofbirth varchar(100), • Salary Numeric )
DDL - ALTER Modifies a table definition by altering, adding, or dropping columns and constraints. Table 1 : Employee • Syntax1: Alter a table to add a new column • ALTER TABLE <table name > • ADD column name1 data type Example :Add “Age” column to Employee table ALTER TABLE Employee ADD age INT Table 1 : Altered table Employee Table 1: Employee Eno varchar(10) Ename varchar(100) Dateofbirth varchar(100) Salary int Age int
DDL - ALTER Table 1 : Employee • Syntax1: Modify Existing Column • ALTER TABLE <table name > • ALTER column name1 data type Table 1: Employee Eno varchar(10) Ename varchar(100) Dateofbirth varchar(100) • Example :Modify “DateofBirth” data type to DATETIME • ALTER TABLE Employee • ALTER COLUMN DateofBirth DateTime Salary int Table 1 : Altered table Employee Table 1: Employee Eno varchar(10) Ename varchar(100) Dateofbirth DateTime Salary int
DDL - ALTER Table 1 : Employee Syntax : Alter table to drop column ALTER TABLE <Table Name> DROP COLUMN <Columnname> • Example: Remove “Age” from Employee Table • ALTER TABLE Employee • DROP COLUMN Age Table 1 : Altered table Employee
DDL - DROP Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Syntax : DROP TABLE <table name > Example : DROP TABLE Employee Drop should destroy the values and structure of the table
DATA MANIPULATION LANGUAGE • INSERT • UPDATE • DELETE • SELECT
DML - Data Manipulation Language • Data manipulation language (DML) statements access and manipulate data in existing schema objects. DML Statements includes : • SELECT - extracts data from a database table • UPDATE- updates data in a database table • DELETE - deletes data from a database table • INSERT INTO - inserts new data into a database table