Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    DATABASES

    Uploaded by

    ellen deus
    Database notes

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DATABASES

    Uploaded by

    ellen deus
    4 views29 pages
    Database notes

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Database notes

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    4 views29 pages

    DATABASES

    Uploaded by

    ellen deus
    Database notes

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 29

    WORKING WITH DATABASES

    A database is a separate application that stores a collection of data.


    Each database has one or more distinct APIs for creating, accessing,
    managing, searching and replicating the data it holds.

    Other kinds of data stores can also be used, such as files on the file
    system or large hash tables in memory but data fetching and writing
    would not be so fast and easy with those type of systems.
    RDBMS
    • Nowadays, we use relational database management systems (RDBMS)
    to store and manage huge volume of data.
    • This is called relational database because all the data is stored into
    different tables and relations are established using primary keys or
    other keys known as Foreign Keys.
    A Relational DataBase Management System (RDBMS)
    is a software that −
    • Enables you to implement a database with tables,
    columns and indexes.
    • Guarantees the Referential Integrity between rows of
    various tables.
    • Updates the indexes automatically.
    • Interprets an SQL query and combines information
    from various tables.
    Terminology
    Database- is a collection of tables with related data

    Table − A table is a matrix with data. A table in a database looks like a simple
    spreadsheet.

    Column − One column (data element) contains data of one and the same
    kind, for example the column postcode.

    A row (= tuple, entry or record) is a group of related data, for example the
    data of one subscription.

    Redundancy − Storing data twice, redundantly to make the system faster.

    Primary Key − A primary key is unique. A key value can not occur twice in one table.
    With a key, you can only find one row.

    Foreign Key − A foreign key is the linking pin between two tables.
    Referential Integrity- making sure that a foreign key value points to an
    existing row.
    DBMS languages
    • Database languages are used to read, update and store data in a
    database.
    • There are several such languages that can be used for this purpose;
    one of them is SQL (Structured Query Language).
    • DDL – Data Definition Language:
    (CREATE,DROP,ALTER,TRUNCATE,COMMENT,
    RENAME)
    • DML – Data Manipulation Language: (INSERT,
    UPDATE,DELETE)
    • DCL – Data Control Language: (GRANT,REVOKE)
    • TCL-Transaction Control Language: (COMMIT,ROLLBACK)
    DDL(Data Definition Language)
    • Actually consists of the SQL commands that can be used to define the
    database schema.
    • It simply deals with descriptions of the database schema and is used
    to create and modify the structure of database objects in the
    database.
    i) CREATE
    • it is used to create the database or its objects (like table, index, function,
    views, store procedure and triggers).
    There are two CREATE statements available in SQL:

    • CREATE DATABASE
    Syntax: CREATE DATABASE database_name;
    Eg.
    CREATE DATABASE Employee;
    In order to get the list of all the databases, you can use
    SHOW DATABASES statement.
    CREATE TABLE
    The CREATE TABLE statement is used to create a table
    in SQL.
    Syntax:
    • CREATE TABLE table_name ( column1
    data_type(size), column2 data_type(size), column3
    data_type(size), .... );
    eg
    CREATE TABLE Students ( ROLL_NO int(3), NAME
    varchar(20), SUBJECT varchar(20), );
    ii) DROP:
    DROP is used to delete a whole database or just a table.
    Syntax: DROP object object_name;
    Examples: DROP TABLE table_name;
    DROP DATABASE database_name;
    iii) TRUNCATE
    • It is used to remove all records from a table, inluding all spaces
    Syntax: TRUNCATE TABLE table_name;
    Eg
    TRUNCATE TABLE Student_details;
    iv) ALTER (ADD, DROP, MODIFY) ALTER TABLE
    • is used to add, delete/drop or modify columns in the existing table.
    • It is also used to add and drop various constraints on the existing
    table.
    ALTER TABLE – ADD:

    • ADD is used to add columns into the existing table.


    Syntax:
    ALTER TABLE table_name ADD (Columnname_1 datatype,
    Columnname_2 datatype, … Columnname_n
    datatype);
    ALTER TABLE – DROP column
    • is used to drop column in a table. Deleting the unwanted columns
    from the table.
    Syntax:
    ALTER TABLE table_name DROP COLUMN column_name;
    ALTER TABLE-MODIFY

    It is used to modify the existing columns in a table. Multiple columns


    can also be modified at once.
    ALTER TABLE table_name MODIFY column_name column_type(SIZE)
    Eg
    • QUERY:
    To ADD 2 columns AGE and COURSE to table Student.
    • ALTER TABLE Student ADD (AGE number(3),COURSE varchar(40));
    MODIFY column COURSE in table Student
    • ALTER TABLE Student MODIFY COURSE varchar(20);
    v) Comments
    Comments can be written in the following three formats:
    • Single line comments.
    • Multi line comments
    • In line comments
    b). DML(Data Manipulation Language) :
    • The SQL commands that deals with the manipulation of data present
    in the database belong to DML or Data Manipulation Language and
    this includes most of the SQL statements.
    i)SELECT Statement
    select statement is used to fetch data from from one or more than one
    tables in a relational database.
    Syntax
    SELECT column_name_1, column_name_2, ... FROM table_name;
    • To fetch the entire table or all the fields in the table:
    SELECT * FROM table_name;
    ii). INSERT INTO Statement
    is used to insert a new row in a table.
    There are two ways of using INSERT INTO statement for inserting rows:
    a)Only values:
    INSERT INTO table_name VALUES (value1, value2, value3,…);
    eg
    INSERT INTO Student VALUES (‘5′,’HARSH’,’dodoma’,’XXXXXXXXXX’,’19’);
    b)Column names and values both:
    INSERT INTO table_name (column1, column2,
    column3,..) VALUES ( value1, value2, value3,..);
    Eg
    INSERT INTO Student (ROLL_NO, NAME, Age) VALUES
    (‘5′,’PRATIK’,’19’);
    iii).UPDATE Statement

    is used to update the data of an existing table in database. We can


    update single columns as well as multiple columns using UPDATE
    statement as per our requirement.
    Basic Syntax:
    UPDATE TableName SET column_name1 = value, column_name2 =
    value.... WHERE condition;
    • EX1: SQL> UPDATE EMPLOYEES SET EMP_SALARY = 10000 WHERE
    EMP_AGE > 25;

    • EX2; SQL> UPDATE EMPLOYEES SET EMP_SALARY = 120000 WHERE


    EMP_NAME = 'Apoorv';
    iv). DELETE Statement
    is used to delete existing records from a table. We can delete a single
    record or multiple records depending on the condition we specify in
    the WHERE clause.
    Basic Syntax:
    DELETE FROM table_name WHERE some_condition;
    eg
    DELETE FROM Student WHERE NAME = ‘duke';
    DELETE FROM Student WHERE Age = 20
    c). DCL(Data Control Language) :

    DCL includes commands such as GRANT and REVOKE which mainly


    deals with the rights, permissions and other controls of the database
    system.
    • i). GRANT-gives user’s access privileges to database.
    • ii). REVOKE-withdraw user’s access privileges given by using the
    GRANT command.
    d). TCL(transaction Control Language) :
    TCL commands deals with the transaction within the database.
    Examples of TCL commands:
    i). COMMIT– commits a Transaction.
    ii). ROLLBACK– rollbacks a transaction in case of any error occurs.
    ii). SAVEPOINT–sets a savepoint within a transaction.
    iv). SET TRANSACTION–specify characteristics for the transaction.

    You might also like