Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Structured Query Language (SQL) – Basic Concepts

By Exforsys | on November 8, 2006 |
Concepts

Structured Query Language (SQL) – Basic Concepts

Having query about what a SQL is? It is Structured Query language. It is a non procedural language and it is a database language.


Categories in SQL commands

There are 3 broad categories in SQL commands. They are namely

  • Data Definition language (DDL)
  • Data Manipulation Language (DML)
  • Transaction Control Commands

Under each category there are many SQL commands. Let us see each in brief.

Commands under Data Definition language (DDL)

This category include creating, dropping and altering table commands

CREATE TABLE is used in creation of table(s).
SYNTAX:  CREATE TABLE tablename (colname col definition, colname col definition…);
ALTER TABLE: If you want to modify a table definition we can use this command. This command is used to alter column definition in table.
SYNTAX:  ALTER TABLE tablename MODIFY (Column definition);

DROP TABLE: This command is used to remove an existing table permanently from database.
SYNTAX:  DROP TABLE tablename;

Commands under Data Manipulation Language (DML)

INSERT: This command is used to insert rows into the table.
SYNTAX:  INSERT INTO tablename VALUES (value,value,….);

SELECT: This is used to select values or data from table
SYNTAX:  SELECT column name1, column name2, …. FROM tablename;

If we want to select all column values from a table then SQL command use is
SELECT * from tablename ;

DELETE: In order to delete rows from a table we use this command
SYNTAX:  DELETE FROM tablename WHERE condition;

Based on the condition specified the rows gets fetched from the table and gets deleted in table. Here the WHERE clause is optional.

UPDATE: This SQL command is used to modify the values in an existing table.
SYNTAX: UPDATE tablename SET columnname = value, columnname = value,….. WHERE condition;

The rows which satisfies the WHERE condition are fetched and for these rows the column values we placed in command above in SET statement gets updated.

Commands under Transaction Control Statements

ROLLBACK – This SQL command is used to undo the current transaction

SYNTAX: ROLLBACK;

SAVEPOINT: This is used to identify a point in a transaction to which we can later rollback.

SYNTAX: SAVEPOINT savepoint_identifier;

COMMIT: This command is used to make all changes permanent in database and also marks the end of transaction.

SYNTAX: COMMIT;

Always it is better to commit changes to database at regular intervals since it will help loss of data or loss of work done when computer shuts due to unavoidable reasons.

Having known about the broad categories of SQL commands let us see some more SQL important terminologies.

Constraints Handling in SQL:

Constraint Handling can be done in two levels namely:

  • Column Level
  • Table Level

Some of the Constraints used in SQL are:

The keyword NOT NULL marks a column that the particular column cannot contain null. By default column contains null unless we define the constraint NOT NULL. This is a column level constraint as it can be defined for columns only.

PRIMARY KEY
A column or a group of columns together can be defined as PRIMARY KERY. If a column or a group of columns are defined as a PRIMARY KEYS then the value of the primary key cannot appear more than once in the table. Also those columns defined as primary keys cannot have null values in it.

REFERENTIAL INTEGRITY
The table on which the column or a combination of columns is defined by this constraint is called the foreign key is called child table. When this constraint is defined a relation is defined between this table which has the foreign key and a table that has the primary key in relationship with this. This is called as referenced key. The table which has the primary key or in other words the referenced key is called as parent table.

SQL Functions to Handle Arithmetic Operations

All these SQL arithmetic functions described below operate on numerical values only and are used to get data as per users required format.

Some of the functions are:

  • To find the absolute value of a number we can use the SQL function ABS (number);
  • To round a number to a specified number of decimal places SQL command used is
    ROUND (number, number of decimal places to be rounded);
  • To convert a char to a number SQL function used is TO_NUMBER (char value);

VIEW
This is an important concept in SQL. A View is nothing but a window of an existing table. In other words it is a logical representation that is created from one or existing base table. After creation of view it can be used just as a base table. That is one can query or select values of data’s from views and it also possible to update a view.

Views are created by suing CREATE VIEW command in SQL

SYNTAX: CREATE VIEW viewname AS SELECT statement

One of the important points to consider while a user uses views is that we have seen that vies are created from a base table and if suppose the base table is deleted after which if a user tries to use or access the view error will occur.

SYNONYMS:
It is possible to refer a table with a different name and this done by using CREATE SYNONYM. It is possible to create synonym for tables as well as views.

SYNTAX: CREATE synonym name FOR username.tablename;

GROUP Functions
We have seen before arithmetic functions which operated on numerical value and returned a single value for each single row taken ass input. But there may be occasions where one might require several rows to be grouped and the result of the grouped rows might be needed. To handle such situations GROUP functions in SQL helps. There are number of group functions available in SQL. Let us see some of them:



Average:
To calculate the average of a group of values SQL defines the function AVG (column name)

Counting number of values:
If we want to count the number of values in a particular column then we can use the SQL function COUNT (column name)

Maximum:
To find the maximum among the column values SQL function MAX (column name) can be used

Minimum:
To find the minimum among the column values SQL function MIN (column name) can be used

There are many more SQL commands and terminologies in SQL but the above gives only an overview of the basics of SQL.

« « Introduction to JCL
HR Interview – Behavioral HR Interviews » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 391 Followers
  • Popular
  • Recent
  • Important Terminologies used in Database

    October 16, 2006 - 0 Comment
  • Semaphore in UNIX – An Overview

    October 16, 2006 - 0 Comment
  • ISPF – Know about it

    October 17, 2006 - 0 Comment
  • Features of OOP

    October 22, 2006 - 0 Comment
  • Multiple Virtual Storage (MVS)

    October 22, 2006 - 0 Comment
  • Debugging – An Important Aspect in Programming

    August 31, 2006 - 0 Comment
  • Quality Aspects to Check While Writing COBOL Program

    October 23, 2006 - 0 Comment
  • Design Documents in Programming Methodology

    September 17, 2006 - 0 Comment
  • Pros and cons of client/server computing

    October 25, 2006 - 0 Comment
  • Feasibility Study – Why needed before programming

    September 17, 2006 - 0 Comment
  • Pros and cons of client/server computing

    October 25, 2006 - 0 Comment
  • Quality Aspects to Check While Writing COBOL Program

    October 23, 2006 - 0 Comment
  • Multiple Virtual Storage (MVS)

    October 22, 2006 - 0 Comment
  • Features of OOP

    October 22, 2006 - 0 Comment
  • ISPF – Know about it

    October 17, 2006 - 0 Comment
  • Important Terminologies used in Database

    October 16, 2006 - 0 Comment
  • Semaphore in UNIX – An Overview

    October 16, 2006 - 0 Comment
  • Software Development Life Cycle

    October 12, 2006 - 0 Comment
  • Table space – Important concept of Database

    October 12, 2006 - 0 Comment
  • Types and Levels of Testing in Programming

    October 2, 2006 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Pros and cons of client/server computing
  • Quality Aspects to Check While Writing COBOL Program
  • Multiple Virtual Storage (MVS)
  • Features of OOP
  • ISPF – Know about it

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT