ADBMS

• Data


  RAW FACTS
• Information


  • PROCESSED DATA
  • Produced by processing data
  • Rebuild the meaning of data
• Data Management


  Focuses on proper generation, storage and retrieval of data
• Database


  • REPOSITORY of data
  • Can be ELECTRONIC and DIGITALLLY MANIPULATED
  • Like a FILING CABINET
• SQL


  • Is a standards language for STORING, MANIPULATING and RETRIEVING data inside a Relational Database Management System (RDBMS)
  • Stands for STRUCTURED QUERY LANGUAGE
• Types of SQL Commands

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Data Query Language (DQL)
  • Transaction Control Language (TCL)
• Data Definition Language (DDL)


  • Make/ Perform CHANGES on the PHYSICAL STRUCTURE of any table residing inside a database
• DDL Commands

  • CREATE – Used to CREATE tables or database
  • ALTER – Used to MODIFY /ADD the VALUES in the tables
  • RENAME – Used to RENAME the table or database name
  • DROP – DELETES THE table from the database
  • TRUNCATE – Used to delete A table from the database
• Data Manipulation Language (DML)


  • MANIPULATION inside those tables and databases is done using DML commands
  • The advantage of using DML commands is, if in case any wrong changes or values are made, they can be changes and rolled back easily
• DML Commands

  • INSERT – Used to INSERT NEW ROWS in the table
  • DELETE- Used to DELETE A ROW or the ENTIRE TABLE
  • UPDATE – Used to UPDATE VALUES of existing rows of tables
• Data Control Language (DCL)


  • Manages the matter and issues related to the DATA CONTROL in any databases
  • DCL Commands mainly provides SPECIAL ACCESS to the users and also used to specify the roles of the users accordingly
• DCL Commands


  • GRANT – Used to PROVIDE ACCESS to the users
  • REVOKE – Used to TAKE BACK the access privileges from the users
• Data Query Language (DQL)


  • Consists of ONLY ONE COMMAND over which DATA SELECTION in SQL relies
• DQL Commands

  • SELECT – Used to FETCH DATA from tables/databases
• Transaction Control Language (TCL)


  • Manages the issues and matter related to the TRANSACTIONS in any databases
• TCL Commands

  • ROLL BACK – Used to CANCEL or UNDO the changes made in the database
  • COMMIT - Used to DEPLOY or APPLY or SAVE the changes in the database
  • SAVEPOINT – Used to SAVE the data on TEMPORARY BASIS of database
• SQL Syntax is governed by AMERICAN NATIONAL STANDARDS INSTITUTE (ANSI) and INTERNATIONAL ORGANIZATION FOR STANDARDIZATION (ISO)
• Statements


  • Are VERY SIMPLE and pretty STRAIGHTFORWARD like PLAIN ENGLISH but with specific syntax
  • Composed of a sequence of KEYWORDS, IDENTIFIERS and etc. and terminated by SEMICOLON ( ; )
• Semicolon


  It TERMINATES the statement or SUBMITS the statement to the database server
• SQL Comments

  • Single line comment = 2 consecutive hypens (--)
  • Multi Line Comment = Slash, double asterisk and slash ( /**/ )
• SQL Data Types

  • CHAR(size)
  • VARCHAR(size)
  • BINARY(size)
  • VARBINARY(size)
  • TEXT(size)
  • BLOB(size)
  • BIT(size)
  • BOOL
  • BOOLEAN
  • INT(size)
  • FLOAT(size, d)
  • FLOAT(p)
  • DOUBLE(size, d)
  • DOUBLE PRECISION(size, d)
  • DECIMAL(size, d)
  • DATE
  • DATETIME(fsp)
  • TIMESTAMP(fsp)
  • TIME(fsp)
  • YEAR
• SQL Arithmetic Operator

  • + (ADD)
  • - (SUBTRACT)
  • * (MULTIPLY)
  • / (DIVIDE)
  • % (MODULO)
• SQL Logical Operators

  • ANY
  • BETWEEN
  • EXISTS
  • IN
  • LIKE
  • NOT
  • OR
  • SOME
• SQL Expression


  • A COMBINATION of one or more values, operators and SQL functions that evaluate to a value
  • Used to QUERY THE DATABASE for a specific set of data
• Boolean Expression


  SQL Boolean Expressions fetch the data BASED ON MATCHING A SINGLE VALUE
• Numeric Expression


  Used to perform ANY MATHEMATICAL OPERATION in any query
• Date Expressions


  Return current system date and time values
• Constraint


  • A RESTRICTION placed on one or more columns of a table to LIMIT THE TYPE OF VALUES that can be stored in that column
  • Constraints provide a STANDARD MECHANISM to maintain the ACCURACY AND INTEGRITY OF THE DATA inside a database table
• Examples of Constraints

  • NOT NULL – Do not accept NULL VALUES
  • PRIMARY KEY - UNIQUE number
  • DEFAULT - AUTOMATICALLY assign value if the user DIDN'T PUT ANY VALUE
  • AUTO_INCREMENT - AUTOMATICALLY assign a value by INCREMENTING the previous value by 1
  • UNIQUE - Must NOT have the SAME VALUE/ UNIQUE characters
  • FOREIGN KEY - is a column or combination of columns that is used to establish and ENFORCE A RELATIONSHIP between the DATA IN TWO TABLES
  • CHECK – Used to RESTRICT THE VALUES that can be placed in a column
• SQL Clauses


  • Clause means "CONDITIONS or PART or SECTION"
  • Used to EXTRACT/ UPDATE/ MANIPULATE data in/from the database in order to GENERATE MINIMAL UNIQUE RECORDS
• FROM Clause


  • The most basic clause and is widely used in almost all the SQL queries
  • USED TO FETCH DATA IN FORM OF RESULTS from the database or tables
• WHERE Clause


  • An abstract form/type of FROM clause
  • Added along with FROM clause in order to GENERATE MOST FILTERED/SUMMARIZED RESULT
• GROUP BY Clause


  • RETURN THE SUMMARIZED DATA INTO POSSIBLE CATEGORIES according to the fixed query
  • Can also be used with aggregate functions
• HAVING Clause


  • Used with SQL aggregate functions such as MIN, MAX, AVG, SUM AND COUNT
  • HAVING clause is used with GROUP BY clause always
• ORDER BY Clause


  • Used to SORT THE RESULT IN EITHER ASCENDING OR DESCENDING ORDER
  • Can be done using ASC (ASCENDING)and DESC (DESCENDING)
• SQL AND Operator


  A logical operator that combines two conditions and returns TRUE only if BOTH CONDITIONS evaluate to TRUE
• SQL OR Operator


  A logical operator that combines two conditions, but it returns TRUE when EITHER OF THE CONDITIONS IS TRUE
• SQL IN Operator


  A logical operator that is used to CHECK whether a particular VALUE EXISTS within a set of values OR NOT
• SQL BETWEEN Operator


  A logical operator that allows you to SPECIFY A RANGE TO TEST
• SQL Bitwise Operators

  • & (Bitwise AND)
  • | (Bitwise OR)
  • ^ (Bitwise Exclusive OR)