Software Training and Placement Center
SQL Introduction and All Concepts
SQL (Structured Query Language) is a standardized language used to manage and manipulate
relational databases. It allows you to perform various operations like querying data, inserting records,
updating records, and deleting records from a database. SQL is the foundation for interacting with
relational database management systems (RDBMS).
Types of SQL Software in the Market
SQL software, also known as Database Management Systems (DBMS), can be broadly categorized into
two types: Open-source and Commercial. Here’s an overview of some popular SQL software:
Open-source SQL Software:
1. MySQL:
o One of the most popular open-source relational databases.
o Widely used for web applications, especially in combination with PHP.
o Known for its speed, reliability, and ease of use.
2. PostgreSQL:
o An advanced, open-source relational database system.
o Known for its robustness, extensibility, and compliance with SQL standards.
o Offers advanced features like support for JSON, full-text search, and custom data types.
3. MariaDB:
o A fork of MySQL, created by the original developers of MySQL.
o It aims to maintain compatibility with MySQL while adding more features, improved
performance, and security enhancements.
4. SQLite:
o A lightweight, serverless, self-contained SQL database engine.
o Often used in mobile applications, desktop software, and small projects.
o Does not require a separate server process, making it easy to integrate into applications.
5. CockroachDB:
o An open-source, distributed SQL database.
o Designed to be highly scalable and resilient, making it suitable for cloud-native
applications.
o It offers strong consistency and high availability.
Commercial SQL Software:
1. Microsoft SQL Server:
o A relational database management system developed by Microsoft.
o Provides a comprehensive suite of tools for data management, business intelligence,
and analytics.
o Available in various editions, including Enterprise, Standard, and Express (free version).
2. Oracle Database:
o A highly scalable and secure RDBMS developed by Oracle Corporation.
o Known for its performance, reliability, and extensive feature set, making it popular for
large enterprises.
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
o Offers advanced features like partitioning, compression, and in-memory processing.
3. IBM Db2:
o A family of data management products, including a relational database.
o Known for its strong support for high availability, scalability, and advanced analytics.
o Used in various industries, including banking, finance, and telecommunications.
4. SAP HANA:
o An in-memory, column-oriented relational database management system.
o Developed by SAP SE, it's designed for high-performance analytics and transactional
processing.
o Often used in conjunction with SAP’s ERP software.
5. Amazon RDS (Relational Database Service):
o A managed database service in the cloud, supporting several SQL database engines like
MySQL, PostgreSQL, SQL Server, and Oracle.
o Offers easy setup, operation, and scaling of a relational database in the cloud.
6. Google Cloud SQL:
o A fully-managed relational database service by Google Cloud.
o Supports MySQL, PostgreSQL, and SQL Server, offering features like automated backups,
replication, and monitoring.
7. Snowflake:
o A cloud-based data warehousing service that supports SQL for querying and managing
data.
o Known for its ability to scale compute and storage independently, making it suitable for
big data analytics.
Each of these SQL software solutions offers unique features and benefits, depending on the use case
and requirements of the project or organization.
Setup SQL and Running in Local :
Step 1 : download file from ([Link]
Step 2 : Double click and it will open the below screen Select “Custom” , click next
Select the Package , MYSQL Server
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Also Select MYSQL Workbench
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Also Select MYSQL Shell
Step 3 : Click on Next and it will open next window.
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Here if it warns the path already exists , then go to that folder in system explorer and delete that folder.
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
After installation, here we can click on NEXT
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Here we can set a password and confirm the password
Again Click next
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Click Next and Finish the installation.
SQL Overview :
SQL, which stands for Structured Query Language, is a standard programming language used for
managing and manipulating relational databases. It allows users to interact with databases by
performing tasks such as inserting, updating, deleting, and retrieving data.
SQL is divided into different categories of commands
Types of SQL Commands
1. Data Definition Language (DDL): It includes commands like CREATE, ALTER, DROP, and
TRUNCATE used to define or modify the structure of database objects like tables.
2. Data Manipulation Language (DML): Commands like INSERT, UPDATE, and DELETE are
used to manipulate data within the database.
3. Data Control Language (DCL): Commands like GRANT and REVOKE are used to control
access and permissions to the database objects.
4. Data Query Language (DQL): The SELECT command falls under this category and is used to
retrieve data from the database.
SQL provides powerful capabilities like filtering data, sorting, grouping, joining tables, and
aggregating data through functions like SUM, AVG, COUNT, etc.
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
In SQL, commands are categorized into different Data Definition Language (DDL), Data Manipulation
Language (DML), Transaction Control Language (TCL), Data Control Language (DCL), and Data Query
Language (DQL) based on their functions. Here are the explanations and examples for each category:
1. Data Definition Language (DDL):
- DDL commands are used for defining the structure of the database.
- Examples: CREATE, ALTER, DROP, TRUNCATE.
2. Data Manipulation Language (DML):
- DML commands are used for manipulating data within the database.
- Examples: SELECT, INSERT, UPDATE, DELETE.
3. Transaction Control Language (TCL):
- TCL commands are used for managing transactions within the database.
- Examples: COMMIT, ROLLBACK, SAVEPOINT.
4. Data Control Language (DCL):
- DCL commands are used for controlling access to data stored in the database.
- Examples: GRANT, REVOKE.
5. Data Query Language (DQL):
- DQL commands are used for retrieving data from the database.
- Example: SELECT.
Each type of command plays a crucial role in interacting with a database, whether it is defining the
structure (DDL), manipulating data (DML), managing transactions (TCL), controlling access (DCL), or
retrieving data (DQL).
1. Data Definition Language (DDL):
- DDL commands are used for defining the structure of a database.
- Examples: CREATE, ALTER, DROP, TRUNCATE.
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Data Definition Language (DDL) consists of commands used to define, manage, and alter the structure of
database objects. Here are some typical DDL commands along with examples:
1. CREATE
The CREATE command is used to create new tables, databases, indexes, or other objects in the
database.
Example: Creating a Table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
SignupDate DATE
);
2. ALTER
The ALTER command is used to modify an existing database object. It allows you to add, delete,
or modify columns in a table.
Example: Adding a Column
ALTER TABLE Customers
ADD PhoneNumber VARCHAR(15);
Example: Modifying a Column
ALTER TABLE Customers
ALTER COLUMN Email VARCHAR(150);
Example: Dropping a Column
ALTER TABLE Customers
DROP COLUMN SignupDate;
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
3. DROP
The DROP command is used to remove database objects. This command deletes the object and
all the data contained in it.
Example: Dropping a Table
DROP TABLE Customers;
4. TRUNCATE
The TRUNCATE command is used to remove all records from a table, but it does not delete the
table itself. It is faster than DELETE because it does not log individual row deletions.
Example: Truncating a Table
TRUNCATE TABLE Customers;
Important Considerations:
• CREATE: Used to create new tables or database elements. It's fundamental for initial
database setup.
• ALTER: Alter tables and other database objects to add/remove columns or constraints as
business requirements evolve.
• DROP: Permanently deletes objects from the database. Be cautious as this action cannot
be undone.
• TRUNCATE: Quickly removes all records from a table but keeps the table structure intact
for future use.
2. Data Manipulation Language (DML):
- DML commands are used for manipulating data within the database.
- Examples: INSERT, UPDATE, DELETE.
1. INSERT
SQL Code :
INSERT INTO Students (StudentID, Name, Age)
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
VALUES (1, 'John Doe', 25);
2. UPDATE
The UPDATE command is used to modify existing records in a table. You can update one or
multiple columns for one or multiple rows based on specified conditions.
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example: Updating Rows in a Table
Suppose you have a Products table and you want to update the price of a specific product.
UPDATE Products
SET Price = 19.99
WHERE ProductID = 101;
Example: Updating Multiple Rows
You can update multiple rows by adjusting the WHERE clause.
UPDATE Products
SET Price = Price * 0.9 -- Applying a 10% discount
WHERE Category = 'Electronics';
3. DELETE
The DELETE command is used to remove one or more records from a table. Care should be
taken when using this command, especially without a WHERE clause, as it can remove all
records from a table.
Syntax:
DELETE FROM table_name
WHERE condition;
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Example :
Deleting rows using DELETE can be selective based on the condition provided.
DELETE FROM Students WHERE Age < 18;
This example deletes rows from the "Students" table where the age is less than 18.
Example: Deleting a Specific Row
Suppose you want to delete a specific customer from the Customers table.
DELETE FROM Customers
WHERE CustomerID = 5;
Example: Deleting Multiple Rows
You can delete multiple rows by specifying a condition in the WHERE clause.
DELETE FROM Orders
WHERE OrderDate < '2023-01-01';
Important Notes:
• Without a WHERE clause, UPDATE and DELETE will affect all records in the table. To
avoid accidental updates or deletions, always double-check your WHERE conditions.
• For operations that can potentially affect a large number of rows, it might be beneficial
to perform a SELECT operation first to confirm the targeted records.
• Consider transaction management (BEGIN, COMMIT, and ROLLBACK) to safely execute
and undo DML operations in scenarios where database systems support transactions.
3. Transaction Control Language (TCL):
Transaction Control Language (TCL) commands are used to manage transactions in a
database. Transactions are sequences of operations performed as a single logical unit of work. TCL helps
ensure data integrity and consistency by controlling commit and rollback operations.
1. COMMIT
The COMMIT command is used to save all the changes made during the current transaction.
Once a COMMIT is issued, the changes become permanent and visible to other users.
Example: Using COMMIT
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
BEGIN; -- Start a transaction
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 123;
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 456;
COMMIT; -- Save the transactions
2. ROLLBACK
The ROLLBACK command is used to undo changes made during the current transaction. It
reverts the database to the last committed state.
Example: Using ROLLBACK
BEGIN; -- Start a transaction
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 123;
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 456;
-- Some condition to check errors
IF some_error_condition THEN
ROLLBACK; -- Undo the transactions if there's an error
END IF;
3. SAVEPOINT
The SAVEPOINT command is used to set a point within a transaction to which it can later be
rolled back. This allows for partial rollback operations.
Example: Using SAVEPOINT
BEGIN; -- Start a transaction
SAVEPOINT sp1; -- Create a savepoint
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
UPDATE Products
SET Price = Price - 10
WHERE Category = 'Electronics';
SAVEPOINT sp2; -- Create another savepoint
UPDATE Products
SET Price = Price - 5
WHERE Category = 'Books';
-- Rollback to the first savepoint
ROLLBACK TO sp1;
COMMIT; -- Save all changes made after sp1
Important Considerations:
• COMMIT: Use this to make all changes in a transaction permanent. After a commit, you
cannot roll back the transaction.
• ROLLBACK: Returns the database to the last committed state. Useful for error recovery
and ensuring data consistency.
• SAVEPOINT: Allows you to subdivide a transaction into smaller parts, enabling partial
rollbacks to savepoints.
Using TCL commands, you can manage complex transactions efficiently, ensuring robust error
handling and data consistency in your database applications.
4. Data Control Language (DCL):
Data Control Language (DCL) commands are used to control access to the data and manage privileges in
a database environment. These commands help database administrators define and manage user
permissions, ensuring security and access control.
1. GRANT
The GRANT command is used to provide specific privileges to users over database objects like
tables, views, sequences, and more.
Code Syntax:
GRANT privilege(s)
ON object_name
TO user_name;
Example: Granting Privileges
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
Suppose you want to grant a user named john_doe the ability to select and update a table
called Employees.
GRANT SELECT, UPDATE
ON Employees
TO john_doe;
You can also grant all privileges on a database object like so:
GRANT ALL PRIVILEGES
ON Employees
TO john_doe;
2. REVOKE
The REVOKE command is used to remove user access rights or privileges to database objects.
Syntax:
REVOKE privilege(s)
ON object_name
FROM user_name;
Example: Revoking Privileges
To revoke the update privilege from the user john_doe on the Employees table, use the
following command:
To revoke all privileges:
REVOKE UPDATE
ON Employees
FROM john_doe;
REVOKE ALL PRIVILEGES
ON Employees
FROM john_doe;
Important Considerations:
• GRANT: Used to assign specific rights to users, enabling them to perform specific
operations on database objects.
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]
Software Training and Placement Center
• REVOKE: Withdraws those granted privileges, revoking permissions to perform
operations previously allowed.
Privileges can include a variety of permissions like SELECT, INSERT, UPDATE, DELETE, and
more, depending on the database system and how fine-grained you want the permissions to be.
These DCL commands are essential for managing database security and ensuring that users have
appropriate access to perform their tasks without exposing sensitive data or operations
unnecessarily. They are typically used by database administrators to create a controlled and
secure database environment.
5. Data Query Language (DQL):
- DQL commands are used for retrieving data from the database.
- Examples: SELECT.
SELECT Name, Age FROM Students WHERE Age > 20;
Address : ByteSplash Training and Placement Center, 4th Floor, No 92/9, PR Layout, Marathahalli, Bengaluru, Karnataka, 560037
Mobile : +91 8660803099 / +91 6301062858 , Email : enquries@[Link]