ZeePedia

Rules of SQL Format, Data Types in SQL Server

<< Vertical Partitioning, Replication, MS SQL Server
Categories of SQL Commands, >>
img
Database Management System (CS403)
VU
Lecture No. 25
Reading Material
"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,
McGraw-Hill
Overview of Lecture
o Structured Query Language (SQL)
In the previous lecture we have studied the partitioning and replication of data. From
this lecture onwards we will study different rules of SQL for writing different
commands.
Rules of SQL Format
SQL, at its simplest, is a basic language that allows you to "talk" to a database and
extract useful information. With SQL, you may read, write, and remove information
from a database. SQL commands can be divided into two main sub languages. The
Data Definition Language (DDL) contains the commands used to create and destroy
databases and database objects. After the database structure is defined with DDL,
database administrators and users can utilize the Data Manipulation Language to
insert, retrieve and modify the data contained within it. Following are the rules for
writing the commands in SQL:-
·  Reserved words are written in capital like SELECT or INSERT.
·
User-defined identifiers are written in lowercase
·
Identifiers should be valid, which means that they can start with @,_
alphabets ,or with numbers. The maximum length can be of 256. The reserved
words should not be used as identifiers.
·
Those things in the command which are optional are kept in [ ]
·
Curly braces means required items
·
| means choices
·
[,.....n] means n items separated by comma
Consider the following example:-
SELECT [ALL|DISTINCT]
{*|select_list}
FROM {table|view[,...n]}
196
img
Database Management System (CS403)
VU
Select * from std
Data Types in SQL Server
In Microsoft SQL ServerTM, each column, local variable, expression, and parameter
has a related data type, which is an attribute that specifies the type of data (integer,
character, money, and so on) that the object can hold. SQL Server supplies a set of
system data types that define all of the types of data that can be used with SQL
Server. The set of system-supplied data types is shown below:-
Integers:
·
Biggint
63
Integer  (whole number) data from ­2
(-9,223,372,036,854,775,808)
through 263-1 (9,223,372,036,854,775,807).
·
Int
Integer  (whole number) data from -231 (-2,147,483,648) through 231 - 1
(2,147,483,647).
·
Smallint
Integer data from -215 (-32,768) through 215 - 1 (32,767).
·
Tinyint
Integer data from 0 through 255.
bit
Integer data with either a 1 or 0 value.
Decimal and Numeric
·
Decimal
Fixed precision and scale numeric data from -1038 +1 through 1038 ­1.
·
Numeric
Functionally equivalent to decimal.
Text:
It handles the textual data. Following are the different data types.
·
Char:
By default 30 characters, max 8000
197
img
Database Management System (CS403)
VU
·
Varchar:
Variable length text, max 8000
·
Text:
Variable length automatically
·
nchar,
nvarchar, ntext
Money:
It is used to handle the monetary data
·
Small money: 6 digits, 4 decimal
·
Money:
15 digits, 4 decimal
Floating point:
·
Float
·
Real
Date:
·
Smalldatetime
·
datetime
198
img
Database Management System (CS403)
VU
Examination System Database
Examination
We will now transfer this conceptual database design into relational database design
as under:-
PROGRAM (prName, totSem, prCredits)
(crCode, crName, crCredits, prName)
COURSE
SEMESTER (semName, stDate, endDate)
(crCode, semName, facId)
CROFRD
(facId, fName, fQual, fSal, rank)
FACULTY
STUDENT (stId, stName, stFName, stAdres,stPhone, prName, curSem, cgpa)
(stId, crCode, semName, mTerm,sMrks, fMrks, totMrks, grade, gp)
ENROLL
SEM_RES (stId, semName, totCrs, totCrdts, totGP, gpa)
It is used to specify a database scheme as a set of definitions expressed in a DDL.
DDL statements are compiled, resulting in a set of tables stored in a special file called
199
img
Database Management System (CS403)
VU
a data dictionary or data directory. The data directory contains metadata (data about
data) the storage structure and access methods used by the database system are
specified by a set of definitions in a special type of DDL called a data storage and
definition language
Data Manipulation is retrieval, insertion, deletion and modification of information
from the database. A DML is a language, which enables users to access and
manipulate data. The goal is to provide efficient human interaction with the system.
There are two types of DML.First is Procedural: in which the user specifies what data
is needed and how to get it Second is Nonprocedural: in which the user only specifies
what data is needed
The category of SQL statements that control access to the data and to the database.
Examples are the GRANT and REVOKE statements.
Summary:
In today's lecture we have read about the basics of SQL. It is used to communicate
with a database. According to ANSI (American National Standards Institute), it is the
standard language for relational database management systems. SQL statements are
used to perform tasks such as update data on a database, or retrieve data from a
database. Some common relational database management systems that use SQL are:
Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database
systems use SQL, most of them also have their own additional proprietary extensions
that are usually only used on their system. However, the standard SQL commands
such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to
accomplish almost everything that one needs to do with a database. In the end we
have also seen the different types of SQL commands and their functions.
Exercise:
Practice the basic commands of SQL like SELECT, INSERT and CREATE.
200
Table of Contents:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules