Basic Introduction & Overview of SQL
                      
iFouBar sCicosn osfu SltQaLncy
 
https://www.ifourtechnolab.com/
Introduction
  What is SQL?  
 Standard language for accessing databases
 SQL stands for Structured Query Language
 Lets you access and manipulate databases
 Nonprocedural language
 It is an ANSI (American National Standards Institute) standard
https://www.ifourtechnolab.com/
Database
 A database is an organized collection of data, typically stored in electronic format:
 It allows you to input, manage, organize, and retrieve data quickly
 Traditional databases are organized by records (rows), fields (columns) stored in tables which 
are stored in the database files
 It supports storage and manipulation of data
 It makes easy data management
https://www.ifourtechnolab.com/
Table in SQL
 A database table is a collection of rows and columns that is used to organize information 
about a single topic
 Each row within a table corresponds to a single record and contains several attributes that 
describe the row
 These tables are stored in databases
https://www.ifourtechnolab.com/
SQL Statements
 DML (Data Manipulation Language)
These statements affect records in a table. These are basic operations we perform on data such 
as selecting a few records from a table, inserting new records, deleting unnecessary records, and 
updating/modifying existing records
 It include the following:
SELECT – select records from a table
 INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records
https://www.ifourtechnolab.com/
SQL Statements 
Data Definition Language(DDL)
• This statements are used to alter/modify a database or table structure and schema. These 
statements handle the design and storage of database objects
• It include the following:
• CREATE – create a new Table, database, schema
• ALTER – alter existing table, column description
• DROP – delete existing objects from database
      
     
https://www.ifourtechnolab.com/
SQL Statements 
Data Control Language(DCL)
These statements control the level of access that users have on database objects
It includes the following:
GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from read/write permission on database objects
Transaction Control Language(TCL)
These statements allow you to control and manage transactions to maintain the integrity of data within SQL 
statements
BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACKs a transaction in case of any error
https://www.ifourtechnolab.com/
 
SQL (Cont.)
 
 SQL Data Definition Commands:
COMMAND OR OPTION DESCRIPTION
CREATE SCHEMA AUTHORIZATION Creates a database schema
CREATE TABLE Creates a new table in the user’s database schema
              NOT NULL Ensures that a column will not have null values
        UNIQUE Ensures that a column will not have duplicate values
             PRIMARY KEY  Defines a primary key for a table
        FOREIGN KEY  Defines a foreign key for a table
       DEFAULT      Defines a default value for a column (when no value is given)
       CHECK        Constraint used to validate data in an attribute
CREATE INDEX     Creates an index for a table
CREATE VIEW      Creates a dynamic subset of rows/columns from one or more tables
ALTER TABLE      Modifies a table's definition (adds, modifies, or deletes attributes or constraints)
CREATE TABLE AS  Creates a new table based on a query in the user's database schema
DROP TABLE       Permanently deletes a table (and thus its data)
DROP INDEX       Permanently deletes an index
DROP VIEW Permanently deletes a view
https://www.ifourtechnolab.com/
 
SQL (Cont.)
 
 SQL Data Manipulation Commands:
COMMAND OR OPERATION DESCRIPTION
INSERT Inserts row(s) into a table
SELECT Selects attributes from rows in one or more tables or views
    W  HERE Restricts the selection of rows based on a conditional expression
GROUP BY Groups the selected rows based on one or more attributes
    H AVING Restricts the selection of grouped rows based on a condition
ORDER BY Orders the selected rows based on one or more table’s rows
UPDATE Modifies an attribute’s values in one or more table’s rows
DELETE Deleted one or more rows from a table
COMMIT Permanently saves data changes
ROLLBACK Restores data to their original values
https://www.ifourtechnolab.com/
 
SQL Operators
 
 COMPARISON OPERATION              =,,=, Used in conditional expressions
LOGICAL OPERATION
              AND/OR/NOT Used in conditional expressions
ARITHMETIC OPERATION Used in conditional expressions
                      +,-,*,/,%
     SPECIAL OPERATION Used in conditional expressions
              BETWEEN Checks whether an attribute value is within a range
              IS NULL Checks whether an attribute value is null
              LIKE Checks whether an attribute value matches a given string pattern
              IN Checks whether an attribute value matches any value within a value list
              EXISTS Checks whether a subquery returns  any rows
              DISTINCT Limits values to unique values
Aggregate Functions Used with SELECT to return mathematical summaries on columns
              COUNT Returns the number of rows with non-null values for a given column
              MIN Returns the minimum attribute value found in a given column
              MAX Returns the maximum attribute value found in a given column
              SUM Returns the sum of all values for a given column
              AVG Returns the average of all values for a given column
https://www.ifourtechnolab.com
 
Data Types
 
  It defines what kind of value a column can contain
  Selection is usually dictated by nature of data and by intended use
  Pay close attention to expected use of attributes for sorting and data retrieval purposes
      
     
https://www.ifourtechnolab.com/
 
Data Types (Cont.)
 
Character CHAR(L) Fixed-length character data for up to 255 characters. If you store strings that 
are not as long as the CHAR parameter value, the remaining spaces are left 
unused. Therefore, if you specify CHAR(25), strings such as "Smith" and 
      "Katzenjammer" are each stored as 25 characters. However, a U.S. area 
     code is always three digits long, so CHAR(3) would be appropriate if you 
wanted to store such codes.
VARCHAR(L) or Variable-length character data. The designation VARCHAR2(25) will let you 
VARCHAR2(L) store characters up to 25 characters long. However, VARCHAR will not leave 
unused spaces. Oracle users may use VARCHAR2 as well as VARCHAR. 
Date DATE Stores dates in the Julian date format
https://www.ifourtechnolab.com/
 
SQL Constraints
 
 NOT NULL - Indicates that a column cannot store NULL value
 CHECK - Ensures that the value in a column meets a specific condition
 DEFAULT - Specifies a default value when specified none for this column
      
     
https://www.ifourtechnolab.com/
 
SQL Keys
 
 Unique - Ensures that each row for a column must have a unique value
 Primary Key - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination 
of two or more columns) have an unique identity which helps to find a particular record in a table 
more easily and quickly, each table can have only ONE primary key
 Foreign Key - Ensure the referential integrity of the data in one table to match values in another 
table, A FOREIGN KEY in one table points to a PRIMARY KEY in another table
 Composite Key - Combination of two or more columns in a table that can be used to uniquely 
identify each row in the table when the columns are combined uniqueness is guaranteed, but when it 
taken individually it does not guarantee uniqueness
 Candidate Key- It is candidate for primary key of a table, In simple words we can understand that 
such type of keys which full fill all the requirements of primary key which is not null and have unique 
records is a candidate for primary key
https://www.ifourtechnolab.com/
      
     
 
SQL Indexes
 
Clustered
• Clustered indexes sort and store the data rows in the table or view based on their key values. These are 
the columns included in the index definition 
• There can be only one clustered index per table, because the data rows themselves can be sorted in only 
one order
Non clustered
• Non clustered indexes have a structure separate from the data rows
• A non clustered index contains the non clustered index key values and each key value entry has a pointer 
to the data row that contains the key value
      
 https://www.ifourtechnolab.com/     
 
SQL Functions
 
SQL has many built-in functions for performing calculations on data
• SQL Aggregate functions
• SQL Scalar Functions
      
     
https://www.ifourtechnolab.com/
 
SQL Aggregate Functions
 
 It returns a single value, calculated from values in a column
 Useful aggregate functions:
• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum
      
     
https://www.ifourtechnolab.com/
 
SQL Scalar functions
 
 It returns a single value, based on the input value
 Useful scalar functions:
• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed
      
     
https://www.ifourtechnolab.com/
Practical
 Create Table for Employee with fields Id, Firstname, Lastname, City, Mobile Number, Gender and 
Department, Email, Address, Age, Username, Password and Confirm Password
 Where Department is Master table having fields : Id, Name, Description
 Create Table for Exam having fields : Id, EmployeeId, Title, Description, Marks, ExamDate
 Use SQL Keys (Primary Key, Foreign Key and Unique key)
 Use Not null and null attributes
 Use SQL Statements
 DML (Data Manipulation Language) : Select, Insert, Update, Delete
 Data Control Language(DCL) : Create, Alter, Drop
 Data Definition Language(DDL) : Grant, Revoke
 Transaction Control Language(TCL) : Begin, Commit, RollBack
 Alter column size, drop and add column from employee table
https://www.ifourtechnolab.com/