SQL (STRUCTURED QUERY LANGUAGE)

 Data: Raw facts or figures

 Database:
 May be defined as a collection of interrelated data stored together to serve
multiple application
 It is computer based record keeping system.
 It not only allows to store but also allows us modification of data as per
requirements
 DBMS:
 A DBMS refers to Database Management System
 It is a software that is responsible for storing, manipulating, maintaining and
utilizing database.
 A database along with a DBMS is referred to as a database system.
 There are various DBMS software available in the market like :- Oracle, MS
SQL Server, MySQL, Sybase, PostgreSQL, SQLite
 Need of using Database:
 Helps to store data in a structured manner
 Produce Flexible Reports
 Reduce data redundancy
 Control inconsistency
 Facilitates sharing of data
 Relational Data Model:
 Data is organized in two-dimensional tables called relations. The tables or
relations are related to each other.
 Basic Terminologies related to a Relational Data Model:-
 Relation: Collection of data organized in rows and columns where each cell
has atomic value. (same as Table)
 Tuple: Row of a table (same as Record)
 Attribute: Column of a table (same as Field)
 Domain: It defines the kind of data represented by the attribute. Or Range
of values (data types) allowed for an attribute
 Degree: No. of attributes/columns/fields in a table
 Cardinality: No. of tuples/rows/records in a table
 Referential Integrity: Property of database that requires every value of
one attribute of a Relation must be present in another attribute (same
datatype) in a different (or the same) relation.
 Concept of Keys:
o In relation each record must be unique i.e. no two identical records are
allowed in the Database.
o A key attribute identifies the record and must have unique values. There are
various types of Keys:
o Primary key:
 A set of one or more attribute that can identify a record uniquely in the
relation is called Primary Key.
 There can be only 1 primary key in a table
 Allows only distinct (no duplicate) values and also forces mandatory
entry (NOT NULL) i.e. we cannot left it blank.
o Candidate Key:
  In a table there can be more than one attribute which contains unique
values. These columns are known as candidate key as they are the
candidate for primary key.
 Among these database analyst select one as a primary key based on
requirement like must contain unique value, compulsory entry and
where maximum searching is done etc.
o Alternate Key:
 In case of multiple candidate keys, one of them will be selected as
Primary Key and rest of the column will serve as Alternate Key
 A Candidate Key which is not a primary key is an Alternate Key.
o Foreign key:
 Used to create relationship between two tables.
 It is a non-key attribute whose value is derived from the Primary key of
another table.
 Foreign key column will for the value in Primary key of another table, if
present then entry will be allowed otherwise data will be rejected.
 Primary Key column table from where values will be derived is known
as Primary Table or Master Table or Parent Table and Foreign key
column table will be Foreign Table or Detail Table or Child table.
 Structured Query Language:
o It is a language that enables you to create and operate on relational
databases
o It is the standard language used by almost all the database s/w vendors.
o Pronounced as SEQUEL
o It is not a case sensitive language.
o It is very easy to learn.
 Classification of SQL:
o DDL (Data Definition Language)
o DML (Data Manipulation Language)
 Data Definition Language(DDL):
o Data Definition Language (DDL) defines the different structures in a
database like table, view, index etc.
o DDL statements are used to create structure of a table, modify the existing
structure of the table and remove the existing table.
o e.g. - CREATE, ALTER, DROP
 Data Manipulation Language(DML):
o It allows to perform following operation on data in the table
o Retrieval of information stored in table
o Insertion of new data in table
o Modification of existing data in table
o Deletion of existing data from table
 Brief history of MySQL:
o MySQL is freely available open source RDBMS
o It can be downloaded from www.mysql.org
o In MySQL information is stored in Tables.
o MySQL database system refers to the combination of a MySQL server
instance and MySQL database.
 o It operates using Client/Server architecture in which the server runs on the
machine containing the database and client connects to server over a
network
o MySQL is a multiuser database system, meaning several users can access
the database simultaneously.

 Query:
o Query is a type of SQL commands which accepts tables (relations), columns
(fields or attributes) and conditions or specifications if any and display the
output by means of a temporary table which consists of data represented
through fields and records.
 Order of execution of a query:
o Step 1: Identify table(s) with FROM clause
o Step 2: Filter records using WHERE clause
o Step 3: Form group if any using GROUP BY clause
o Step 4: Filter groups using HAVING clause only if GROUP BY is used
o Step 5: Arrange the output records in ascending or descending order using
ORDER BY
o Step 6: Display the fields mentioned in SELECT clause.
 JOINS:
o A relational database consists of multiple related tables linking together
using common columns, which are known as foreign key columns.
o It is used retrieve data from multiple tables.
 Types of Join:
o Cartesian Product or Cross join:
 The cross join makes a Cartesian product of rows from the joined
tables.
 The cross join combines each row from the first table with every row
from the right table to make the result set.
 If Table1 has degree d1 and cardinality c1 and table2 has degree d2
and cardinality c2, their Cartesian Product has degree d=d1+d2 and
cardinality c=c1*c2;
 Syntax: SELECT * FROM table1, table2
o Equi Join:
 Equi join is a join operation which works on the equality condition of
values in two
 columns from two tables having similar data type.
o Natural Join:
 A natural join is a type of join operation that creates an implicit join by
combining tables based on columns with the same name and data
type.
 It makes the SELECT query simpler with minimal use of conditions.
 There is no need to specify the name of common column in the
SELECT statement
 Common column is present only once in the output.
 Syntax: SELECT * FROM Table1 NATURAL JOIN TABLE2;
 Difference between Equi-Join vs Natural Join:
Equi-Join Natural Join
  Join performed on equality  Join is performed on column
of value of the columns having common name.
 Where clause is used to  There is no need to use
specify the condition where clause
 Both columns from tables  Common column is
are displayed in the result. displayed only once
 Difference between CHAR & VARCHAR :
Char varchar
 Fixed length string  Variable length string
 Fast, no memory allocation  Slow, as it take size
every time according to data so every
time memory allocation is
done
 It takes more memory  It takes less space
 GROUP BY:
o GROUP BY clause is used if statistical records of a table are to be displayed
based on a field. Once the group is formed individual records cannot be
accessed in that query. Several clusters or groups are formed based on the
number of different values in the GROUP BY column present in the table.
o For example, if GROUP BY is applied on TYPE field of ITEM table 3 groups are
formed – Crops have 2 records, Leaves and Pulses have one record each
 HAVING:
o It is a conditional statement used along with group by clause only. It
compares the values with the outcome of aggregate functions belonging to
each group already formed by GROUP BY clause.
 Difference between WHERE and HAVING: