Intoduction to SQL & SQL Function

Assalamualaikum…
Good morning, Now we will study about SQL Function…ready to study?lets join us.

SQL Function
Data Definition Language

SQL includes:

  • Command to create database object such as tables, indexes and view.
  • Command to define access right to those database objects.

Data Manipulation Language
Includes commands to:

  1. insert,
  2. update,
  3. delete,
  4. and retrieve data within the database tables.

Table Creation

  • SQL CREATE TABLE
    • Used to describe Layout of tables
  • Typical restrictions placed by DBMS
    • Names cannot exceed 18 characters
    • Names must start with a letter
    • Names can contain only letter, numbers and underscore ( _ )
    • Names cannot contain spaces

Typical Table Creation
Typical Data Types

  • INTEGER
    • Numbers without a decimal point
  • SMALLINT
    • Uses less spaces than INTEGER
  • DECIMAL(p,q)
    • P = number of digits, Q = number of decimal places
  • CHAR(n)
    • Character string, N = places long
  • DATE
    • Dates in DD-MON-YYYY or MM/DD/YYYY

Table Creations
Data Types

  • Data type selection is usually dictated by the nature of the data and by the intended use
  • Pay close attention to the expected use of attributes for sorting and data retrieval purposes

SQL Constraint

  • NOT NULL constraint
    • Ensures that a column does not accept nulls
  • UNIQUE constraint
    • Ensures that all values in a column are unique

SELECT Quiries
Comparison Operators

  1. Equal to ( = )
  2. Less than ( < )
  3. Less than or equal to ( <= )
  4. Greater than ( > )
  5. Greater than or equal to ( >= )
  6. Not equal to (<>) or ( != )

SELECT Queries
Special Operators

  • BETWEEN
    • Used to check whether attribute value is within a range
  • IS NULL
    • Used to check whether attribute value is null
  • LIKE
    • Used to check whether attribute value matches a given string pattern
  • IN
    • Used to check whether attribute value matches any value within a value list

SELECT Queries
Computed Fields

  • A computed fields is a fields not physically stored in the Database.
  • Can use +, -, * and /

Sorting

  • Clause SQL: ORDER BY
  • Always listed last in the SELECT command sequence.
  • ASC (by default) or DESC (to specify to sort from the highest value to the lowest one)

Advanced Select Queries
Some Basic SQL Aggregate Function

  • COUNT (the number of rows containing “non null” values)
  • MIN (the minimum attribute value encountered in a given column)
  • MAX (the maximum attribute value encountered in a given column)
  • SUM (the sum of all column for a given column)
  • AVG (the arithmetic means (average) for the specified column)

Advanced Select Queries
GROUP BY

GROUP BY allows the ‘grouping’ of record to determine the average, sum, count, min or max.

Semoga Bermanfaat…
Salam Tutotialite…
Wassalam….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s