SQL Is Used Often To Query, Insert, Update, and Modify Data. at A Basic Level SQL Is A Method For Communicating Between You and The Database

BASICS OF SQL
SQL was developed by IBM in 1970s. It was initially called SEQL (Structured
English Query Language)
SQL - Structured Query Language - SQL is used often to query, insert, update,
and modify data. At a basic level SQL is a method for communicating between
you and the database.
DBMS - A DBMS is software that manages the database. Some of the commonly
used DBMS (software) are MS ACCESS, MySQL, Oracle, and others. You use a
Database Management System (DBMS) to store the data you collect from
various sources, and SQL to manipulate and access the particular data you want
in an efficient way.
Types of DBMS
There are two types of DBMS.
Relational Databases (RDBMS) - In these types of databases, data is stored

in the format of tables by the software. In an RDBMS, each row consists of data
from a particular entity only.
Some of the RDBMS commonly used are MySQL, MSSQL, Oracle, and others.
Non-Relational Databases - Some of the Non-Relational DBMSs commonly

used are MongoDB, Amazon, Redis, and others.
In every Database, we have some objects
 Tables – where we store data

 Views – virtual tables, so we can combine data from multiple tables
 Stored Procedures –
TABLES in SQL
Rows are called records and columns are called fields

DATA WAREHOUSE VS DATA MART
Data warehouse occupies large memory and has multiple subject data. Data
mart is a subset of data warehouse and has department wise data
DATA MODELLING
Different headers in a table are called dimensions and the id is called the
primary key that doesn’t allow duplicates.
Eg: Data model for a particular online portal
Customer Cust_id Cust_name Mobile Address City Country

Employee
Accounts
Product
Region
Merchant
DATABASE NORMALIZATION
Transaction- logical group of task/data commands that change the values or

data stored in a database
Eg
BEGIN TRANSACTIONS
UPDATE employee(table) set e_age =50 where e_name =Sam
To Roll back the transaction (or to undo the changes in the database)
ROLLBACK TRANSACTION
Commit statement permanently puts the values in the database
COMMIT TRANSACTION
SQL Constraints
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This
ensures the accuracy and reliability of the data in the table. If there is any
violation between the constraint and the data action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to
a column, and table level constraints apply to the whole table.
The following constraints are commonly used in SQL:
 NOT NULL - Ensures that a column cannot have a NULL value
 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely

identifies each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified
 CREATE INDEX - Used to create and retrieve data from the database very
quickly
ACID PROPERTIES
Atomicity, Consistency, Isolation, Durability is a notion that Database

Professionals often look for when analysing datasets and system topologies. All
four of these characteristics should be present in a credible database. When
designing any platform or server, make sure to use a database that adheres to
these principles, since this will allow you to produce better applications for your
organisation.
ATOMICITY simply signifies that the transaction that one is going to commit is
either complete or not complete.
CONSISTENCY simply means that changes to the values of an instance are
consistent with the values in another instance
ISOLATION property in SQL server states that concurrent transactions do not

interfere with each other’s implementation.
DURABILITY: Once the transaction is completed, then the changes it has made

to the database will be permanent. Even if there is a system failure or any
abnormal changes also, this property will safeguard the committed data.
DIFFERENT KEYS IN RELATIONAL DATABASE
Keys are an integral element of Relational database model. These are used in
identifying and establishing relationships between tables. Moreover, keys are
necessary in recognizing any row of data or record in a table. These can either
be a group of aspects or a single aspect wherein the combination of the same
would act as a key.
Primary Key
This is the first key that helps in identifying a single instance of an entity, in a
distinct manner. There can be numerous keys present in a table, which can be
seen in the “Personal Information” table. That one key that is most suitable
among other key attributes in a table becomes the Primary Key.
For instance, if we take the Personal Information table, we can see that along
with the Social_Security_Number, the License_Number too can become the
Primary Key as both these attributes are unique to respective individuals. The
selection of any primary key depends on the developer and the specific needs.
Candidate Key
This is a set of columns or a single column which helps in uniquely recognizing

any database record without referring to any other information. Any attribute
apart from the Primary Key is known as the Candidate Key. It is as strong as the
Primary Key. For instance, in the Personal Information table, the
Social_Security_Number is the Primary Key whereas the License Number or the
Passport Number are Candidate Keys.
Foreign Key
This is that attribute of a table that is used in identifying a Primary Key of

another table. To understand this type of Key in depth, we will consider the
following tables-
Employee
Name
Employee_ID
Social_Security_Number
License_Number
Department_ID
Department
Department_ID
Department_Head
In these two tables, we see that every employee is from a separate department,
which is again an individual entity and therefore cannot be added to the same
table of “Employee”. Therefore, it has been added separately to “Department”
table. So, to link both these tables, the Primary Key of one table is added to
another. So, in the Department table, Department_ID is the Primary Key, which
has been added as a Foreign to the Employee table that helps in relating these
two tables.
CLAUSE
1. Select
2. From
3. Distinct – Keyword use to remove the duplicates
4. 4. WHERE
5.
Comparative operators on MySQL
>, <, >=, <=, !=, =
Logical Operators
AND, OR, NOT ,
IN Operator – to include different values
Ex: SELECT * FROM Customers WHERE state IN ( ‘FL’, ‘VA’, ‘GA’)
LIKE OPERATOR
= retrieves rows with certain string character
% any no of characters
_ single character
Eg: SELECT * FROM customers WHERE address LIKE ‘%trail%’ OR WHERE address LIKE ‘%avenue%’
Frequent commands used in SQL
1. DDL commands
DDL stands for Data Definition Language. It includes the set of commands that you use to perform
various tasks related to data definition. You use these commands to specify the structure of the
storage and methods through which you can access the database system .
You use DDL commands to perform the following functions :
 To create, drop, and alter.

 To grant and revoke various roles and privileges.
 Maintenance commands
 Example DDL commands include CREATE, ALTER, DROP, and TRUNCATE.
2. DML commands
DML stands for Data Manipulation Language. As the name suggests, it consists of commands which
you use to manipulate the data.
You use these commands for the following actions:
 Deletion
 Insertion
 Retrieval
 Modification
Example DML commands are SELECT, INSERT, UPDATE, and DELETE.
3. TCL commands
TCL stands for Transaction Control Language. As the name says, you use these commands to control
and manage transactions.
One complete unit of work that involves various steps is called a transaction. You use these
commands for the following purposes:
 To create save points

 To set properties of the transaction going on
 To undo the changes to the database (permanent)
 To make changes in the database (permanent)
 Example TCL commands include COMMIT, ROLLBACK, and SAVE TRANSACTION.

SQL Is Used Often To Query, Insert, Update, and Modify Data. at A Basic Level SQL Is A Method For Communicating Between You and The Database

Uploaded by

Copyright:

Available Formats

SQL Is Used Often To Query, Insert, Update, and Modify Data. at A Basic Level SQL Is A Method For Communicating Between You and The Database

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Is Used Often To Query, Insert, Update, and Modify Data. at A Basic Level SQL Is A Method For Communicating Between You and The Database

Uploaded by

Copyright:

Available Formats

BASICS OF SQL

Relational Databases (RDBMS) - In these types of databases, data is stored

Non-Relational Databases - Some of the Non-Relational DBMSs commonly

In every Database, we have some objects

 Tables – where we store data

Rows are called records and columns are called fields

Eg: Data model for a particular online portal

Customer Cust_id Cust_name Mobile Address City Country

Transaction- logical group of task/data commands that change the values or

UPDATE employee(table) set e_age =50 where e_name =Sam

The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different

 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely

 CHECK - Ensures that the values in a column satisfies a specific condition

 DEFAULT - Sets a default value for a column if no value is specified

Atomicity, Consistency, Isolation, Durability is a notion that Database

ISOLATION property in SQL server states that concurrent transactions do not

DURABILITY: Once the transaction is completed, then the changes it has made

DIFFERENT KEYS IN RELATIONAL DATABASE

This is a set of columns or a single column which helps in uniquely recognizing

This is that attribute of a table that is used in identifying a Primary Key of

Comparative operators on MySQL

>, <, >=, <=, !=, =

AND, OR, NOT ,

IN Operator – to include different values

Ex: SELECT * FROM Customers WHERE state IN ( ‘FL’, ‘VA’, ‘GA’)

= retrieves rows with certain string character

You use DDL commands to perform the following functions :

 To create, drop, and alter.

You use these commands for the following actions:

Example DML commands are SELECT, INSERT, UPDATE, and DELETE.

 To create save points

You might also like