1 / 226

Leading Software Technologies Chennai

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

gudrun
Download Presentation

Leading Software Technologies Chennai

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Leading Software Technologies Chennai

  2. 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

  3. INTRODUCTION

  4. INTRODUCTION • What is Database? • Basic Database Concepts • Introduction to DBMS. • Data Model. • Introduction to RDBMS. • DBMS Vs RDBMS

  5. 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

  6. 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.

  7. 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

  8. Basic Database Concepts Example of Table : Fields Records

  9. 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

  10. 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

  11. 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.

  12. 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

  13. 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.

  14. 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.

  15. 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

  16. SQL SERVER BASICS

  17. SQL SEVER BASICS • Introduction • Data Type • Working with Query Analyzer • SQL Components

  18. 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

  19. 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.

  20. 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.

  21. 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

  22. 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.

  23. 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.

  24. 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.

  25. 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.

  26. 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

  27. 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

  28. 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.

  29. 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.

  30. 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.

  31. 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)]

  32. 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.

  33. Working with Query Analyzer To start SQL SERVER Start Programs MicrosoftSQLSERVER Enterprise Manager Query Analyzer Query analyzer

  34. Working with Query Analyzer Query Analyzer

  35. Working with Query Analyzer Server name To log

  36. Working with Query Analyzer Create a new database named as Ebidding

  37. Working with Query Analyzer Select the Query and Press F5 to run the query

  38. Working with Query Analyzer Use command • The USE command selects a database to use for future processing. Syntax Use <databasename>

  39. 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

  40. DATA DEFINITION LANGUAGE

  41. DATA DEFINITION LANGUAGE • CREATE • ALETR • DROP

  42. 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

  43. 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 )

  44. 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

  45. 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

  46. 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

  47. 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

  48. DATA MANIPULATION LANGUAGE

  49. DATA MANIPULATION LANGUAGE • INSERT • UPDATE • DELETE • SELECT

  50. 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

More Related