SQL Server 2000

SQL Server 2000

Friday, April 9, 2010

Database Management System


  • Database management system are used when large quantities of data that needs to be tracked in an organization. Classical paper based data management systems cause inefficient retrieval and in accurate storage of valuable data.
  •  A database is an organized collection of data provides you with the software tool you need to organize and access that data in a flexible manner.




  • DBMS example

  • Access - Microsoft
  • DB/2 - IBM
  • Informix
  • MS SQL Server
  • MySQL - open source etc....
  • It include facilities to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents.




  • Tables

  • Tables comprise the fundamental building blocks of any database.
  • A table column is referred as a field which represents different characteristics of stored data.
  • Table Row is referred as a record which represents individual data items.
  • Each record is identified using a unique field called a Primary key.




  • Queries

  • Queries are used to retrieve data from the database as required.  
  • Queries are written using a language called Structured Query Language (SQL).
  • SQL dwfines the methods used to create and manipulate relational databases on all major platforms.
  • SQL comes in many flavors. Oracle databases utilize their proprietary PL/SQL. Microsoft SQL Server makes use of Transact-SQL. However, all of these variations are based upon the industry standard ANSI SQL. 






  • Structured Query Language

  • 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. (Tables, Procedures) etc...
  • 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.  




  • Data Definition Language

  • Create statement
              This statement is mainly used for creating databases and tables.
          e.g. 1 To create a database payroll
                           create database payroll  
     
          e.g. 2 To create a table employee with following fields
                        
Field Name
Data Type
Empid
Integer
Empname
Text
Age
Integer
Salary
integer

                        create table employee (Empid int,Empname varchar(50), age int, salary int);





       USE



  • USE Statement is used to select a database from the list of database management system.
      e.g.  we want to select payroll database
                USE payroll;
  
     Drop
  • DROP statement is used to remove a database / table/ procedure from the database available in database management system.






     Data Manipulation Language (DML)

  • The data Manipulation Language (DML) is used to retrieve, insert and modify database information.
  • INSERT
              Insert statement is used to insert records into a database.
       e.g. To insert a record into the above created table employee

                   insert into employee values (001,'Saman Silva', 34,12000);
  • SELECT
            Allow database users to retrieve the specific information they desire from an operational database. Consider the above create table with sample data 





Empid
Empname
Age
Salary
001
Saman Silva
34
12000
002
Kamal Perera
30
20000
003
Mala Fernando
26
10000
       
         Retrieve all the records
            select * from employee

       Retrieve employees whose salary is greater than 15000
            select * from employee where salary>15000

       Find the employee name whose empid is 003
               select empname from employee where empid=003

No comments:

Post a Comment