Introduction to SQL

"Om Swastiatu"


Hello everybody, today I'm gonna describe a bit of SQL language for a dummy. (If you don't know why I always put words "Om Swastiastu" and "Om Santhi, Santhi, Santhi, Om" in every first and last line of my post, here I tell you. "Om Swastiastu" is a greeting words for Hinduism in Bali, and "Om Santhi, Santhi, Santhi, Om" is a farewell words for Hinduism in Bali. Try to visit Bali, you will be able to find words like that). Okay, back to the topic. I will start from the SQL function.

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.

Okay, that's all I can tell about the Introduction of SQL. Hope you like it. If there's any critical  or suggestion, please write in the box below. Thank you very much.

"Om Santhi, Santhi, Santhi, Om"